You have three(3) options to go about looking at what is running in MySQL
OPTION #1 : General Log
You could simply activate the general log and look for the timestamp between 3:00-3:30 AM. You could either have the general log as a text file or a MyISAM table. Here are my posts on using the general log:

How to enable MySQL general log? (Jan 08, 2012)
How to keep MySQL general_log after I restart my computer? (Dec 10, 2011)
How do I output MySQL logs to syslog? (Jun 30, 2011)

OPTION #2 : Slow Log
Same setup procedure as the General Log. Here are my past posts on this:

MySQL slow log configuration (Mar 30, 2012)
Create a custom log table in MySQL (Nov 18, 2011)

OPTION #3 : Query Digest
The problem with Options 1 and 2 is that they record only completed queries. If you are trying to catch long running queries in the act of running long between 3:00-3:30AM, even before they get recorded in the slow log or general log, you have to go with setting up a crontab.
I usually use MAATKIT's mk-query-digest (most prefer the most current pt-query-digest). Here are my posts on how to script a crontabed Query Digest:

Investigate peak in MySQL throughput (Apr 24, 2012)
MySQL general query log performance effects (Nov 24, 2011)


Is having SQL Server express database on client better than querying data on a remote server?

Do you think that this is a good approach?

How does "totally rotten" sound?

Seriously - there MAY be some reasons to do that (distributed systems on slow links for example) but this is the first time I hear someone trying to replicate a database to every client to avoid overloading a server. Totally out of whack unless specific circumstances call for it.

One example - one that we have:
* A trading application on a remote location reporting it's trades to central processing via replication

Reason: the link can go down and the trading app still works. It is isolated from corporate central.

ANother example we had:
* A CRM - installed on laptops and needed to work without permanent internet connectivity.

But normally this is not a decent solution. YOu may have specific circumstances, but you totally avoid naming them.

Go with a decent central database machine and it should be fine.


mongodump failing to connection replica set

Try to put "" round --host string... And you don't need --port parameter, because your --host string have port information.
/usr/bin/mongodump -u backup02 -p backup02_password --host "p1dummy:8133,p2dummy:8133,drdummy:8133" --authenticationDatabase admin --out /tmp --db mydbname --ssl --sslAllowInvalidCertificates --excludeCollectionsWithPrefix=system --verbose=5


Dropping multiple indexes all in one SQL command is way better. Why ?
SCENARIO #1
ALTER TABLE mytable DROP INDEX ndx1;
ALTER TABLE mytable DROP INDEX ndx2;
ALTER TABLE mytable DROP INDEX ndx3;

This is what would happen

Creates an empty temp table
Removes the index from the empty temp table
Copies the data into the temp table
Swaps temp table with original table
Drops original table

In #SCENARIO #1, this would happen three times.
SCENARIO #2
ALTER TABLE mytable
    DROP INDEX ndx1,
    DROP INDEX ndx2,
    DROP INDEX ndx3
;

Would you believe MySQL used to do this transformation 3 times back in 2006 under MySQL 4.1 ? In other words, SCENARIO #2 would be handled like SCENARIO #1. I actually wrote a post in the  MySQL AB General Discussion Forum requesting this be changed (Why does mysql drop index very very slow in a large table?).
Someone asked in the DBA StackExchange if MySQL still did this. I wrote a post here saying this is no longer the case (Does MySQL still handle indexes in this way?) and that question references my original MySQL AB post.
What MySQL does today is this:

Creates a temp table
Runs the 3 ALTER TABLE commands against the empty temp table
Copies the data into the temp table ONCE
Swaps temp table with original table
Drops original table

Therefore, you can trust that MySQL will do the right thing today.
Your should go with SCENARIO #2.
GIVE IT A TRY !!!
UPDATE 2015-01-02 13:06 EST
You just asked

What will happen to new operations to the table? Where will new records gets stored at the time of copying? Would it take table lock or metadata lock ?

The table will be locked the entire time. Of course, SCENARIO #1 will be locked three times as long as SCENARIO #2. If you cannot take downtime for this, you only have one option : pt-online-schema-change. All your INSERTs, UPDATEs, and DELETEs will be handled properly during the change.
UPDATE 2015-01-03 08:33 EST
@eroomydna just posted a comment.

@rolando, had you considered the effect of dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html and is default on 5.6. Table should not be rebuilt with the above grouped ddl. No need to use pt-osc for this operation.

@eroomydna is right to point this out. The URL he supplied gives insight into this.
For your particular question, @eroomydna says do not need pt-online-schema-change, and he is right in this instance because you are dropping three indexes. Note the Implementation Details of Fast Index Creation, Paragraph 2:

Dropping a secondary index is simple. Only the internal InnoDB system tables and the MySQL data dictionary tables are updated to reflect the fact that the index no longer exists. InnoDB returns the storage used for the index to the tablespace that contained it, so that new indexes or additional table rows can use the space.

You have a 900 million table and probably a lot of index pages for the three indexes. Dropping the index is being done in a logical manner and must invalidate all those pages to make the space reusable. This will also create lots of shared locks allowing reads and blocking writes. So, this operation should be relatively fast. How long it takes I cannot tell you. Since you said you cannot take the downtime, it could seconds, minutes, maybe even hours for a live table with lots of shared locks and blocked writes.
If you have a copy of the table on a DEV or test server, run the DROP KEY A,DROP KEY B,DROP KEY C just so you can see how fast it runs. Then, you can consider whether downtime would be worth it. Nevertheless, If you want the table live and really cannot do the downtime, you should still go with pt-online-schema-change.
As for my answer, it would apply only if

you drop an index and create an index with the same name
drop/add the PRIMARY KEY


Country specific fields in database schema


The PostgreSQL database cannot start after modifying the IP

When restarting the server with a specific logfile (see your restart command: /usr/pgsql-12/bin/pg_ctl -D /postgres -l logfile start) you need to look at that specific logfile, not at postgresql-Sat.log.
Moreover, this line in the previous log shows that you modified the data directory:

2020-07-04 09:23:14.943 EDT [1832] LOG:  parameter "data_directory" cannot be changed without restarting the server

Presumably log_directory is relative to the data directory, so after this change you need to look at the new location now to find the new logs.
From the documentation:

log_directory (string)
When logging_collector is enabled, this parameter determines the directory in which log files will be created. It can be specified as
an absolute path, or relative to the cluster data directory. This
parameter can only be set in the postgresql.conf file or on the server
command line. The default is log.


How do you Audit Login Failures? Solutions?

For SQL Server, Brian Kelly did a great write up at MSSQLTips.com. You can find it here: https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/

You can enable login auditing (and specify the level of logging) on the Server Properties screen under 'Security.'

To view the logs, execute 'EXEC SP_ReadErrorLog.' There are some parameters that can be added to filter, and look at older than current logs as well. You can also view events in Windows Event Log Viewer. The 'Source' will be the SQL Server instance name on the machine.


NO. You cannot create foreign key references on a different database.

Create the tables on the same database, if there is no specific reason to have them in separate databases.


Optimization by replacing subquery with named subquery having INLINE hint

TL;DR Rewriting the query is not going to get you anywhere here. Use functional indexes to cut the cost of accessing 200 rows out of your millions.

Ok, replacing subquery with CTE and INLINE hint has nothing to do with optimization. Increased readablity? Sure. Performance? Hardly.

The plans differ, because your queries are not really equivalent. First query performs join. Second query has separate subqueries in SELECT clause, and for optimizer that is obviously different.

To make them "sort of equivalent" you would have to join resultsets in Query 2.

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    y.val_a,
    y.val_b
from table_x x
left outer join table_y on y.x_id = x.x_id
where substr(x.x_id, 2, 1) = 'B';

No "code duplication" as well. The plan would probably match the one for the first query.

What happens in your case is you have two tables with table_x having 100 times fewer rows than table_y. By inlining the access to table_y in SELECT you leave optimizer no choice but use index. Accessing 1% of rows via index is usually faster than a fullscan. No surprises here.

However, you specified the scale of millions of rows in each table. I don't know how the cardinalities of X and Y are going to correlate, but we don't really need to know.

Fullscans do not scale well.

On the other hand, whether you only have 10 thousand rows or 10 milllion rows you always need only 200 that match the substr(x_id, 2, 1) = 'B' criterion, right?

Functional indexes will definitely work here.

create index table_x_2nd_char_of_pk_idx on table_x (
  substr(x_id, 2, 1) ASC);

Once you dropped the cardinality to these 200 rows, accessing table_y via its FK is a piece of cake.

Ok, I've added this part after "fullscan on x is not a problem". I'm sure it is, but you can deal with it separately.

Whether you index access predicates or not you can make sure access is isolated and cardinality is low enough when the bigger table is joined.

with
  table_x_filtered as (
    select x.x_id
    from   table_x x
    where  substr(x.x_id, 2, 1) = 'B'
    and    rownum > 0) -- well, better than hints, right?
select
    x.x_id,
    max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
    max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
  from table_x_filtered X
  left outer join table_y y on y.x_id = x.x_id
 group by x.x_id

If table_x is going to stay really small forever, the optimizer should choose to fullscan it first, apply filter and then join. If not, well, you can always use leading, as @panick suggests, but without proper indexing it's going to get slower and slower anyway.


`ts_rank` is returning a numeric/decimal/real that fails a "greater than" check

ts_rank_cd yields a real. If you cast this to double precision, the result becomes:
SET extra_float_digits = 3;

SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'))::double precision;

     ts_rank_cd
---------------------
 0.10000000149011612
(1 row)

That explains what you observe, because the real is cast to double precision if you compare it to a double precision number.
The solution is to explicitly cast 0.1 to real, so that real comparison is used:
SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) = 0.1::real;

 ?column?
----------
 t
(1 row)


Migrate database from SQL Server 2008 Enterprise to SQL Server 2014 Standard

I need move database with "data compression" from MS SQL 2008 Enterprise to MS SQL 2014 standard.

Downgrading is always a pain !

If you have a source control for your database, I would suggest you to create the database shell (schema, objects - tables, views, indexes) on the new 2014 standard server.

BCP out and BULK INSERT from the old server (Enterprise edition) to New 2014 (standard edition) server.

I would say, rely on T-SQL rather than GUI. From Disable Compression on a Table or Index

-- To disable compression on a table

USE AdventureWorks2012;
GO
ALTER TABLE Person.Person REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);
GO

-- To disable compression on an index

USE AdventureWorks2012;
GO
ALTER INDEX AK_Person_rowguid ON Person.Person REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
GO


Are 2 INSERT statements guaranteed to complete sequentially?

It is not guaranteed if they are separate requests that are not in the same database transaction. Given the architecture of modern web apps, if they are separate requests I can almost guarantee that they are not in the same transaction and so are in no way dependant on each other.

A connection-pooled application is likely to accept the two requests with different threads, so even if the requests are issued by the client in order, the second request could insert into the database before the first if execution of the first was slower.


This link is probably a good place to start.

Setting the MAXDOP at the server level depends on a lot of things like whether you're using a virtual or physical server, how many physical cores you have, hyperthreading being enabled or not and the number of NUMA nodes you have.  But you if you tinker with that, you should be made aware of increasing your cost threshold for parallelism to a much higher number than the default (5).  This will take a bit of research as to determine at what subtree cost your server typically handles under no parallelism (MAXDOP 1).


I suspect you are doing this:

select  Outer.ad_type,
GROUP_CONCAT( concat(' duration : ',duration, ' common :',common)
            ORDER BY  sorter ASC SEPARATOR ' , '
            )
    from  ad AS  Outer
    WHERE  IF( EXISTS ( SELECT  1 FROM  ad AS Inner
                    where  Inner.page='1494573224714' ) ,
                           Inner.page='1494573224714',
                           Inner.ad_timeout >= CURDATE()
             )
    group by  Outer.ad_type;

Try it; it should give you an error.

You are really doing

I suspect you are doing this:

select  Outer.ad_type,
GROUP_CONCAT( concat(' duration : ',duration, ' common :',common)
            ORDER BY  sorter ASC SEPARATOR ' , '
            )
    from  ad AS  Outer
    WHERE  IF( EXISTS ( SELECT  1 FROM  ad AS Inner
                    where  Inner.page='1494573224714' ) ,
                           Outer.page='1494573224714',
                           Outer.ad_timeout >= CURDATE()
             )
    group by  Outer.ad_type;

This explains your question??

More

Here might be the first step in getting rid of the UNIONs:

FROM ad AS ad1
JOIN ad AS ad2  ON ad2.page    = ad1.page
               AND ad2.ad_type = ad2.ad_type)  -- left/right/...
WHERE ( ad2.page='1494573224714'
     OR ad1.common )


How to search multiple values using LIKE

Do not try to do it all in a single query.  But first, I will digress...

If you said AND instead of OR, that query needs

INDEX(year, kode_fakultas)  -- in either order

Indexing name is useless because of the leading wildcard on the LIKE expression.

But you have OR, so no indexing is useful; the entire table will be scanned, checking each row for 3 conditions to see if any match.

But that is not really what your UI is asking for.  To rephrase your requirements:

  The user will enter one of part of a name, the year, or kode_fakultas.

Your code must then build a query with only one of the tests in it, then execute it.  At that point, you would need

INDEX(year), INDEX(kode_fakultas)

but be stuck with a table scan if you need the leading wildcard on the LIKE.


Capturing change deltas when database replication or CDC breaks

Assuming that there was a serious failure which causes you to need to 'reconstitute' the missing type 2 changes, you may be able to use your source database data and log backups. If you have preserved these backups you can do something like this process outline:

Restore a copy of the source database to a point just prior to when you experienced the failure. Name the database [dbnameEmergency].
Restore the logs incrementally to [dbnameEmergency], such as 1 minute, 5 minutes, 30 minutes, according to how finely you want to process the deltas in order to reduce the damage.
Reading and comparing the incremental restores will require either creating snapshots, if you have Enterprise edition, or restoring the log to STANDBY mode so as to read the data.
Compare the latest readable changes in [dbnameEmergency] against the state of the [dbnameReplica] (which is the database you are trying to set straight) and write code to apply the changes you are able to infer to [dbnameReplica].

Note: If your database is using a rowversion (or timestamp) column then finding the changed rows will be simpler.

This is not a perfect process and will doubtless miss some details.  But it would give you an approach to help you patch up the damage.

Having said that: It is up to you to decide whether this process is worth the effort.


What is an equivalent of EXCEPT(in PostgreSQL) in MySQL?

You can use a NOT EXISTS predicate. Assume you want to mimic:

select a.c1, a.c2 from a
except
select b.c1, b.c2 from b

This can be expressed as:

select distinct a.c1, a.c2
from a
where not exists (
    select 1 from b
    where b.c1 = a.c1
      and b.c2 = a.c2
)

Other options are to use a left join and check for null:

select distinct a.c1, a.c2
from a
left join b
    on a.c1 = b.c1
   and a.c2 = b.c2
where b.c1 is null

Note that these differ from EXCEPT if the same tuple exists in both relations and contains null. Let a = {(1,1),(2,2),(1,null)} and b = {(1,1),(1,null)}

select a.c1, a.c2 from a
except
select b.c1, b.c2 from b

(2,2)

In a sense, EXCEPT considers null to be equal to null, whereas the predicate a.c2 = b.c2 evaluates to null if either a.c2 or b.c2 is null and as a consequence NOT EXISTS evaluates to false.

select distinct a.c1, a.c2
from a
where not exists (
    select b.c1, b.c2 from b where a.c1 = b.c1 and a.c2 = b.c2
)

(2,2),(1,null)

LEFT JOIN behaves similarly to NOT EXISTS:

select distinct a.c1, a.c2
from a
left join b
    on a.c1 = b.c1 and a.c2 = b.c2
where b.c1 is null

(2,2),(1,null)


MS SQL Server, ask password before drop specific DB

You should, as the comment says, rely on permissions for this.

Strictly speaking, is not possible to "ask password". Where would the password dialog even be displayed?? SQL Server is a client server product that accept commands from any client that uses the TDS protocol. That may be Management Studio, sqlcmd.exe, a PowerShell script, a ADO.Net SqlClient application. Anything.

If you must, then you can deploy, as a last resort, a DDL trigger. A DDL trigger will not replace permissions, but it can prevent an accidental drop.

CREATE TRIGGER safety
ON SERVER
FOR DROP_DATABASE
AS
   PRINT 'You must disable Trigger "safety" to drop databases!'
   ROLLBACK;

You can enhance the logic to check for database name, my purpose is not to give you a copy-paste working code.


Group by Multiple columns and then count different value of same column

Remove gender from GROUP BY clause:
SELECT DistrictId
     , fb.DisabilityTypeId
     , SUM( CASE WHEN GenderId = 1 THEN 1 ELSE 0 END ) AS Male
     , SUM( CASE WHEN GenderId = 2 THEN 1 ELSE 0 END ) AS Female
FROM Districts d
LEFT JOIN FormAddresses a
    ON d.Id = a.DistrictId
JOIN PeopleForms pf
    ON a.PeopleFormId = pf.Id
JOIN FormBeneficiaries FB
    ON pf.Id = fb.PeopleFormId
JOIN FormPersonalInfos fp
    ON pf.Id = fp.PeopleFormId
WHERE a.IsDeleted = 0
  AND pf.FormTypeId = 2
  AND d.CityId = 3
GROUP BY DistrictId
       , fb.DisabilityTypeId


which way is better to design shopping Cart Table - SQL Server

Option 2 is the cannonical solution for the Order / OrderItem pattern, which a shopping cart is, in essence. It allows the cart to be treated as a whole and distinct from another cart that may be submitted by the same user an arbitrarily short time later. Fulfilment can be recorded against a cart rather than each item individually, if that is what is required.


how to return all the columns not just the updated columns when update in postgresql?

Your understanding of the documentation saying "you can only return columns you use" is incorrect
You can list all the columns you want to return. You can even list results of functions or use values that are nothing to do with the columns at all, like you would in a SELECT:
UPDATE person set age = age+1,location = 'us' WHERE id = 1234
RETURNING id, age, location, name, "first name", gender, "where", CONCAT("first name", ' ', name) as fullname, 1+2 as three
--                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--                "i have name, first name, gender and where not updated"

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5ad1bc7b45c4e73da351adedf987b0c3
And as HWNN points out, you can have RETURNING * .. check your original query for syntax errors


Validation warning in Data Flow Task in the SSIS

Your warning looks to stem from your output column(s) database mapping.  The 'TitleOfCourtesy' output column datatype/length needs to match whichever column in DimEmployee it is mapped to.

As far as the 4 records go, I'd see what the data is in the 5th one, since that seems to be where the error is.  Whenever I have your issue, it's always a datatype mismatch.


How can CREATE OR REPLACE fail with already exists?

The OR REPLACE clause in CREATE FUNCTION is not meant for seamless parallel execution, it's meant to avoid dropping the function when we just want to update the body. From the doc:

  If you drop and then recreate a function, the new function is not the
  same entity as the old; you will have to drop existing rules, views,
  triggers, etc. that refer to the old function. Use CREATE OR REPLACE
  FUNCTION to change a function definition without breaking objects that
  refer to the function.

Now if several sessions issue the same CREATE OR REPLACE FUNCTION in parallel, there is indeed a potential race condition that is dealt with by relying on the unique index on (proname, proargtypes, pronamespace), and comes back to the caller as an error.

The error mentioned in the question happens if, for instance:

session #1 starts CREATE OR REPLACE and finds the function does not exist, so it creates it, inserting into pg_proc and locking the corresponding (proname, proargtypes, pronamespace) entry in the index.
session #2 starts CREATE OR REPLACE, finds the function does not exist (because #1 has not committed yet) and is put to wait for the index lock.
session #1 commits.
session #2 tries to insert and fails.

Note that not using explicit BEGIN/COMMIT does not change the race condition: the BEGIN/COMMIT pairs are just implicit around each SQL statement.

To have this parallel sequence work seamlessly, OR REPLACE would need to be to CREATE FUNCTION what ON CONFLICT is to INSERT, but that's not the case.

To deal with conflicting or identical DDL that might run in parallel, probably you should use an explicit lock at the beginning of the sequence to put it as a whole in a critical section. Advisory locks might be used for that. Or you need to implement a "retry on failure" strategy with savepoints around individual statements.


What is the best relationship model for high performance?

You'll probably want to keep your transaction table as small as possible, so use an integer column as a foreign key to your customer table.


Why a SQL Server Table takes 1GB more after adding 55MB of data?

System procedure sp_spaceused tells you how the data is distributed, and is usually more than accurate enough. Run the following simple TSQL:

DECLARE @R TABLE (TabName sysname, [Row Count] bigint, Reserved varchar(128), Data varchar(128), [Idx Size] varchar(128), Unused varchar(128))

INSERT INTO @R
exec sp_spaceused 'tContactHistory'

SELECT TabName
    , [Row Count]
    , cast(round(cast(replace(Reserved, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Reserved MB]
    , cast(round(cast(replace(Data, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Data MB]
    , cast(round(cast(replace([Idx Size], ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Idx Size MB]
    , cast(round(cast(replace(Unused, ' KB','') as dec(32,2))/1024,2) as dec(32,2)) [Unused MB]
FROM @R

Tell us what you find. If there's a problem with fillfactor, datatypes, or just plain old overzealous indexing, this should give enough of a clue as to where to go next, at least.

Update: the result was:

Well your results are odd. It implies the data is actually used. I also see someone already asked you before how you verified the size of the data, and you replied that you did so by calculating it from the field size. Are you sure you did so correctly? Is it possible that empty strings or special characters were introduced that you can't see, but still take up space? Or that you're dealing with CHAR as opposed to VARCHAR fields with the same end result?

See DATALENGTH function, for instance, to check the length of your variable length columns.

-- For example:
SELECT DATALENGTH(myVarCharColumnName) DLen
FROM tContactHistory
ORDER BY DLen desc
-- Note by checking the column type and storage size from MSDN,
-- you can now use this information to calculate the storage size.

Also, have you tested this hypothesis by for instance copying over part of that data to a new, dummy table and see what happens? Like

SELECT TOP 1000 *
INTO myStorageSizeTest
FROM tContactHistory
WHERE ... -- Insert whatever condition here to only select the new rows where this space problem appeared

Etc. Don't be afraid to test and get creative.


Rights to grant for controlling an Availability Group

The Login must need following permissions to configure availability groups, if the login not already part of sys admin server role:

ALTER ANY AVAILABILITY GROUP
CONTROL AVAILABILITY GROUP
ALTER ANY DATABASE
CREATE ENDPOINT

For detailed information.. and Grant Server Permissions..


How do I send an array of strings to PG from PHP without the former becoming confused?

Use instead:
SELECT * FROM table WHERE label <> ALL ($1);

Then you can pass an actual array for $1 - while (NOT) IN expects a list of values. See:

How to use ANY instead of IN in a WHERE clause with Rails?

If NULL values can be involved on either side, either query is problematic. You may want the form:
SELECT * FROM table WHERE (label = ANY ($1) IS NOT TRUE);

See:

Why does my SELECT query not return null values?


MySQL: How to write a recursive function to show menu tree?

MySql doesn't support recursive functions. It supports recursive stored procedures but this feature is disabled by default. See manual.

Recursion uses stack space to store contexts and tends to put memory pressure into server. I strongly discourage to use stored procedures to do this kind of calculation, better to do them into the application code. Anyway to achieve your goal you must unroll recursion into an equivalent iterative loop. I tried to figure out what was the output of the function. I renamed function from hello_world to build_path:

DELIMITER $$
CREATE FUNCTION build_path(_typeId INT)
  RETURNS TEXT
BEGIN

    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    set @ret = '';
    while _typeId is not null do

      SELECT parent_type_id, title
      INTO  _parentId,  _typeContent
      FROM fcom_product_type
      WHERE id = _typeId;

      IF(found_rows() != 1) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'id not found in table fcom_product_type';
      END IF;

      set @ret := CONCAT('/', _typeContent, @ret);
      set _typeId := _parentId;

    end while;

    RETURN @ret;
END;
$$
DELIMITER ;

So you can call:

call build_path(2)

to get for example:

/root/child1/child2


RMAN validate incremental level 1 backups?

First, you need to discover which backup is your level 0 and your 1. Then you need to validate the level 0 and the level 1 backups, independently.

 LIST BACKUP SUMMARY;

From the list, find the key for your level 0 and your level 1 backups.
Then validate them via:

 VALIDATE BACKUPSET 370, 412;

Substitute your key values in that. You can also add in the key values for archive log backups, if any.

See
https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta053.htm#RCMRF162 Example 2-157 Validating a Backup Set, for more details.

This is beyond the scope of your original question, but may be of benefit to you:

I run RMAN backup configuration scripts as part of my "new Oracle instance" checklist. That script looks like this:

run {
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
    configure compression algorithm 'MEDIUM' optimize for load false;
    configure device type disk backup type to compressed backupset;
    configure device type disk parallelism 2;  }

Then I do my backups via this, with either a level = 0 or a level = 1, depending on the day of the week:

backup device type disk incremental level = 0 section size 1g database plus archivelog tag 'level 0';
backup current controlfile tag='Controlfile Level 0';
backup spfile tag='SPFile Level 0';

I then do the following. Probably not necessary, but...

allocate channel for maintenance type disk;
backup validate check logical database archivelog all;

crosscheck archivelog all;
crosscheck backupset ;
crosscheck backup device type disk;
release channel;


How can I enable/disable MySQL slow query logging on Amazon RDS without restarting

The slow_query_log variable is a dynamic variable.

Dynamic variables can be updated without a restart:

  When you change a dynamic parameter and save the DB parameter group,
  the change is applied immediately regardless of the Apply Immediately
  setting.

So just change it and you should see the effects immediately.


DBA Permissions on LOCAL SERVER containing SQL SERVER

There are a number of US government regulations that, depending on how your company interprets them, require this separation of duties.  Sarbanes–Oxley (SOX) is the big one for publicly traded companies.  HIPAA  for healthcare organizations, etc.

The first question I have is, of those tasks you outlined, how many are you doing directly from the server?  Arguably many of those tasks don't require local server access, but I understand this would be frustrating if you are used to performing this work locally on the server.

Regarding workarounds, my suggestion is to be an adopter of this policy.  Do this by making sure that your security team is properly locking down user accounts, but creates clearly defined policies for service accounts and that those accounts are setup appropriately.  I would even encourage you to look at Group Managed Service Accounts as this makes everyone's life easier if you can make them work in your environment.  Follow this up with moving whatever processes you can off of the servers.  There are real security concerns with users actively logging into servers regularly, and I would say you should avoid doing this unless absolutely necessary.  Argenis Fernandez, during one of the most entertaining PASS sessions I've attended, even showed that anyone with access to a local admin account on a server can get elevated access to the SQL Server regardless how "locked down" it is.  Honestly, you could even go so far as to request all of the servers get configured to run as Windows Server Core, which is about as secure a Windows server as you can have. Finally, document those processes that cannot be moved off or need to have exceptions created for them.  This will likely be your biggest headache in the short term, but proper documentation should allow your organization to bend the rules to fit the business needs.

Look at it this way.  If anything goes bad, such as a breach of information, and it's traced back to unauthorized access to a server, this policy may save your behind without you knowing.  I've worked for years in the healthcare and insurance industries and as much as a headache as this sort of policy may seem to be, it's really a good policy to adopt.

This process will be frustrating... at least it has been the number of times I walked a similar path, but don't make it a we vs. them issue, instead approach road blocks with the following statement "this needs to happen so the business can run, how do we accomplish that within these restrictions?"  With this approach, it's not about you or your job, it's about your company staying in business which makes it a we vs. the policy issue.


Understanding postgres txid_current and pg_locks

I found an answer.

There were two parts to this.

Firstly, what is important is virtualtransaction rather than virtualxid. Even though virtualxid and transactionid are null for everything I care about, they all share a virtualtransaction id.

Secondly, although txid_current() returns a transactionid, which seems like it wouldn't help because my locks all have null for that, we can still use it to get what we want.

What happens is when you call txid_current() postgres assigns a lock with the transactionid returned by that function, which we can see if we query pg_locks. This lock will share a virtualtransaction id with everything else in the current transaction.

For example:

> BEGIN;
BEGIN
> SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks;
  locktype  | transactionid | virtualtransaction |      mode
------------+---------------+--------------------+-----------------
 relation   |        [NULL] | 3/38672            | AccessShareLock
 virtualxid |        [NULL] | 3/38672            | ExclusiveLock
(2 rows)

> SELECT txid_current();
 txid_current
--------------
       355056
(1 row)

> SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks;
   locktype    | transactionid | virtualtransaction |      mode
---------------+---------------+--------------------+-----------------
 relation      |        [NULL] | 3/38672            | AccessShareLock
 virtualxid    |        [NULL] | 3/38672            | ExclusiveLock
 transactionid |        355056 | 3/38672            | ExclusiveLock
(3 rows)

So now from this we can get the virtualtransaction id shared by everything in the current transaction, and filter on that.

In other words:

SELECT
    *
FROM pg_locks pl
WHERE
    virtualtransaction=(
        SELECT virtualtransaction FROM pg_locks
            WHERE
                transactionid::text = (txid_current() % (2^32)::bigint)::text
                -- compare int to xid, see https://dba.stackexchange.com/a/123183/10371
                AND locktype='transactionid'
        LIMIT 1
    )

...will return only locks relevant to the current transaction.


Starting with PostgreSQL documentation about Negators:

36.13.2. NEGATOR
The NEGATOR clause, if provided, names an operator that is the negator of the operator being defined. We say that operator A is the negator of operator B if both return Boolean results and (x A y) equals NOT (x B y) for all possible inputs x, y. Notice that B is also the negator of A. For example, < and >= are a negator pair for most data types. An operator can never validly be its own negator.
[...]

The way the NEGATOR is supposed to behave does not match your intended usage. I guess this could result in some execution plans not giving the intended results.
Again, according to the docs:

Providing a negator is very helpful to the query optimizer since it allows expressions like NOT (x = y) to be simplified into x <> y. This comes up more often than you might think, because NOT operations can be inserted as a consequence of other rearrangements.

The way the text has been written, I understand that this implies that the planner might simpify a clause like NOT(a op b) into a negator_op b (or even the other way around, as commented out by @craig-ringer); but it also might not do it. The planner is allowed to make the simplification, but not coerced to or has an obligation to perform the conversion.
I suggest that, if you need different kind of comparisons, that you create other opeartors  (let's say <% >% <=% >=% or something like that), with corresponding negators if they make sense.


Though this question is 2 years old, seems like it deserves an answer.

Make sure to look at
MySQL documentation 21.3.1 Trigger Syntax and Examples
in order to understand the importance and context of the delimiter statement.

Create trigger tbi_new_ranking before insert on rankings
for each row

BEGIN
 DECLARE v_ranking INTEGER;
 DECLARE v_aggregate_ranking INTEGER;
 DECLARE v_object VARCHAR(<how many characters>);

 set v_object = NEW.object;
 /*
   Define the code you want to aggregate the ranking numbers.
   The inserting data is addressed by prefixing the triggering table's columns with
   NEW. This can be a little confusing so it is wise to set up variables
   to represent the inserted data rather than mussing about with them directly
   E.G.
     SELECT count(*) INTO v_count from Rankings where Object = NEW.Object;
     SELECT sum(Ranking) INTO v_sum from Rankings where Object = NEW.Object;
     set v_aggregate_ranking = v_sum/v_count;
 */

 update objects set rank = v_aggregate_ranking where object = v_object;
END


There has been a quick and dirty method in SQL Server for 20 plus years - you set the value of Query governor cost threshold to your arbitrary amount and then if the estimated cost of the execution plan exceeds that the query is rejected - I believe the end user receives some kind of error message but I haven't seen this enabled in the wild for a VERY long time so can't remember any details.
NB: more than just CPU costs is included from the link : CPU time, memory, and disk IO.


Both your queries are shorthand for this:

SELECT dept.deptno AS deptno,
       dept.deptname AS deptname,
       dept.deptsal AS deptname
  FROM dept AS dept;

SQL is sometimes forgiving and will imply the AS clauses and correlation names, other times it requires them for no logical reason e.g.

SELECT deptno
  FROM dept JOIN emp USING (deptno);

will complain that deptno is ambiguous

SELECT deptno
  FROM (SELECT deptno FROM dept);

will complain that the derived table has no correlation name.

To answer the question: would would include, omit or revise the default correlation name to make the code to aid the human coder -- easier to read and understand means it will be  easier to maintain -- but tastes vary (when the same table is involved in a query more than once then you need to used an alternative correlation name to disambiguate for the purposes of the parser, obviously).


How to create function using another functions mysql

Instead of DROP + CREATE, simply do TRUNCATE TABLE.  That should avoid the error about COMMIT.

Drawback:  Other code would need to originally do the CREATE and DROP.


Optimizing postgres row overlap constraints involving UUIDs and GIST

As of Postgres 10 the btree_gist extension (in contrib) supports UUIDs, so if you add that extension first, you'll be able to create indexes and exclusion constraints without casting to text. Pre-10, casting to text is the way to go.


Starting up sql server after hard-drive recovery

You can try attaching the MDF file to the new instance, but there is no guarantee that it will work. If the machine crashed while SQL Server was running, the files may actually be corrupt. If you don't have proper backups, you can try:

CREATE DATABASE tms ON
(
  name = tms_Data,
  filename = 'e:\path\tms_data.mdf' -- <-- move this file to the data location on new box
)
FOR ATTACH_REBUILD_LOG;

If that doesn't work, this will probably be a very expensive lesson about why we want to take backups regularly and store them in a different location.


SQL SERVER 2014 FCI Cluster taking too long to failover

The issue appears to be centered around shutting down the SQL instance prior to the failover.

You are correct, because that's exactly what is happening. You're shutting it down nicely on one node, moving resources, and starting it up on another node. The question actually doesn't have anything to do with clustering but with speeding up a clean SQL Server shutdown.

  When a manual failover is performed, we do the following to speed things up to this point.

When SQL Server cleanly shuts down, it flushes all of the buffers and asks internal systems to shut themselves down.

So, how do you make this faster? First, this is only really the case when you are cleanly shutting down - which most likely won't happen during a real failure. Secondly, you don't let SQL Server shut down - I would look into using Availability Groups and testing your manual failover times to compare and contrast the differences.

Additionally, no matter what you use, indirect checkpoints are much more effective than traditional.


ORA-00845 after deleting files accidentally at /dev/shm

As described in https://www.krenger.ch/blog/ora-00845-memory_target-not-supported-on-this-system/ and suggestion of MarcoBaldelli /dev/shm just needs to have enough space. So the solution was to increase /dev/shm:

mount -o remount,size=4G /dev/shm

When MEMORY_TARGET is set to 0 (as in my case) it seems to be as following (see Oracle Documentation):

For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step.


Why is this delete operation slower after a foreign key relationship has been added?

It's the check if the owner is still referenced by the cats table during the DELETE. The check is essentially done using a select * from cats where owner_id = ? for every owner that you delete.

You can speed up the check by creating an index on the foreign key column:

create index on cats (owner_id);


I want to join the left table with the "nearest" row in the right table.

This is a KNN problem. For it you'll likely want a real database. I highly suggest you migrate to PostgreSQL and use PostGIS, where this can be solved using an index. Your answer has 0 to do with your problem. Your question is,

  by the nearest match

Your answer is only looking at one way directionality, and it's only doing it for one row not a set: it's a correlated sub-query. You simply need to change course, or bounty a developer to include KNN in MySQL. MySQL is using Boost for GIS, so maybe someday someone will jump nearest(Point const &, unsigned) in the code base. I checked for ya, it's not there and there is no professional way to do what you want at any scale.

MySQL's GIS offering is the worst out of all the majors players. No one uses it. To give you an idea of scale,

MySQL has 225 questions on GIS.SE.
PostGIS has 5,771

It's a marketing point to say it supports a spatial index. See also

PostgreSQL vs. MySQL: spatial feature comparison

BTW, PostGIS also supports KNN on timestampg using btree_gist


You need to use the LEFT JOIN to connect a dataset with the list of values you want to see with another dataset with the values you want a count of.

If you make your final query something like:

SELECT
       ctr.[Sta3n]
      ,COUNT(cnt.[DisplayGroupSID]) AS '# Of RTC Requests'
  FROM (SELECT DISTINCT [Sta3n] FROM [CPRSOrder]) ctr
         LEFT JOIN #T cnt ON (ctr.[Sta3n] = cnt.[Sta3n])
 WHERE cnt.[DisplayGroupSID] IN
      ( 1200003559
       ,1200003561
       ,1200003558
       ,1200003557
       ,1200003560
       ,1200003562
       ,1200003556
       ,1200003519)
 GROUP BY ctr.[Sta3n]
;

(Alias ctr is short for "medical CenTeR"; cnt for "values to CouNT").

You should get something more like what you are looking for.

Note that it may make more sense to select the list of distinct [Sta3n] values from some other location - you presumably know more about your system than I do.

Similarly, the temp table you're using may not be necessary, and you might choose to store the distinct [Sta3n] values in their own temp table.

The key is, have the list of values you want to see even if your count is 0 in the table on the left side of the join, and the table with the data to be counted on the right side.

NOTE: Assumes your DBMS allows sub-queries as derived tables, and table name aliases. I think that's pretty generic, but you don't specify your DBMS.


You can synchronize Oracle and SQL Server in many ways. For example:

You can use sql server transactional replication with Oracle as a publisher:
https://msdn.microsoft.com/en-us/library/ms152481.aspx

You can use Oracle GOldengate (very expensive):
http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html

regards
Giova


looks like you need to use "join" syntax. Something like:

SELECT
u.name,
u.id,
m.id,
t.id,
p.id,
FROM User u
LEFT JOIN Message m ON m.owner_user_id = u.id
LEFT JOIN Thread  t ON t.owner_user_id = u.id
LEFT JOIN Project p ON p.owner_user_id = u.id
WHERE u.id = 12345

Of course, this will have to be modified to include the fields you actually want returned and whatever sorting and ordering you may want... but this should get you started. Good luck!


Try this

    SELECT AVG(cast(cast([varcharColumn] as decimal) as int))
      FROM View
     WHERE isnumeric([varcharColumn]) = 1
     AND cast(cast([varcharColumn] as decimal) as int) > 0
     AND CreatedDate > @StartDate AND CreatedDate < @EndDate


Making decision between Count() & Top queries in terms of performance

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs.

Correct, it will stop after finding two matching rows. But it doesn't necessarily scan the whole table. If there is a suitable index, on (TenantID), it will only need an index seek and a sequential scan on the index - and stop at the second row, whether it is a match or not. The index guarantees that the rows with same TenantID will be one after the other in the index structure.

  But in the second query, operation is done over all the records in the table.

Yes again. With the same note, if there is an index, it only has to do an index seek and a scan of the index, until it finds the first row with a different (than 1) TeanantID. If there is no index, it will have to do a full scan on the table.

  Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

Yes, that is a correct assumption. The first query will be (slightly or more) efficient than the second. The difference will probably be negligible though in most cases. Reading 2, 3 or 10 values from an index will not make any noticeable difference. If however, there are many rows with the same TenantID value, say tens of thousands or millions, then you'll start noticing.

To summarize, if you need to find whether there are N or more rows that match a condition, I suggest you always use the TOP (N) method, and then count:

...
WHERE (SELECT COUNT(*) FROM
        (select top (2) * from [dbo].[tblTenant] where TenantID = 1) AS t
      ) = 2 ;


Use max(coalesce(logincount, 0)) to avoid NULLs

According to Postgres docs (9.6):

  The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:


How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections.

Proto Recv-Q Send-Q Local Address   Foreign Address  State   PID/Program name
tcp        0      0 127.0.0.1:5432  0.0.0.0:*        LISTEN  3561/postgres

So it can only accept local tcp connections regardless of what permissions you've specified in pg_hba.conf. pg_hba.conf only specifies allowed connections, but does not specify what interfaces your service will listen to.

The addresses the server listens on is specified with the listen_addresses GUC in postgresql.conf.  If you want the server to listen for remote connections you should specify the ip(s) you want it to listen on or * to listen on all available interfaces on the host.

To have your postgresql server listen on all interfaces on the host, you should have the following line in postgresql.conf:

listen_addresses = '*'


oracle clob export/import across non linked instances

You can use the legacy (but still supported) EXP utility to export a table/schema/DB to your local computer, and then use IMP to import the data on another instance.


Cannot use "ON CONFLICT" with postgres updatable view and partial index

After debugging through the PostgreSQL code, I'd say that there is a problem in the infer_arbiter_indexes function in src/backend/optimizer/util/plancat.c. It compares the index predicate to the WHERE condition in ON CONFLICT, but seems to ignore that they are on different objects.
I'm not sure if PostgreSQL is willing to consider that as a bug, but you might report it.


Handling INSERT race condition for generated unique column value

You don't mention the isolation level, but if it's run under the REPEATABLE READ isolation level, the SELECT part will never see the new values that other transactions inserted concurrently, so this code may be stuck in its loop forever.

  Should I just return the conflict to the calling process and let the
  application handle retry?

Yes, it would make sense. The key is to retry in a different transaction, so it gets a fresh new snapshot, plus it releases whatever locks that may be blocking other transactions.

Another approach would be to not use max(slugindex)+1, but a sequence instead, if the number part of the slug is globally unique, not unique per suffix. The answer you linked is based on the premise that they want abc-1 and xyz-1 as opposed to abc-1 and xyz-2, which is why a sequence is not an option. But it's not obvious that you share that requirement, looking at your SELECT.


Identify root cause for why database is stuck in RESTORING state

Occam's razor suggests starting with the obvious:

If your script sometimes leaves a database in restoring state,
then debug the script.

Start by logging what you're doing in a table or a file. Then, when you end up with the database in restoring state, step back through your logs to see what went wrong. (If you want a second set of eyes from the community, try uploading your script as a Github Gist, but keep in mind that the bigger it is, the harder it might be for folks to spot bugs.)

If you don't wanna do that, then try running a Profiler or Extended Events trace to track the restore events, but be warned - it's way harder than it looks. (Read the comments on that post for even more reader ideas that they've tried and failed.)


SQL: Query to insert records into the table if there is not even one record with certain condition

Removed superfluous joins and criteria
WITH ts AS (
        SELECT ts.id
         FROM train_statuses ts
         WHERE ts.status IN ('queued','executed')
        EXCEPT
        SELECT ts.id
         FROM train_statuses ts
         WHERE ts.status Not IN ('queued','executed')
)
    INSERT INTO train_statuses (id, updated_at, status) SELECT id, now(), 'cancelled' FROM ts


Getting error "String or binary data would be truncated" even for 'text' data type

You are trying to put data into a column that is not wide enough for it, as the message suggets.

IIRC Postgre's text columns are open sized much like SQL Server's [N]VARCHAR(MAX) so I suspect there are some long values in there and the target column(s) in your table in SQL Server is a more limited character type (VARCHAR(50) or some such.

That is only a guess though. We can't be any more speicif without much more detail from you. What are the table definitions in Postgres and SQL Server? What tool(s) are you using for to migrate the data (SSIS, plain SQL, something else?)?


Use BIGINT and twice the storage space or INTEGER but with extra operations?

The third option for the reason you outline: you do not have to query agency every time.  It's not that the join/lookup would be particularly expensive for queries that return a small amount of rows, but:

Your database is easier to work with
Your code is more concise and easier to debug
It's easier to optimize queries that return more than a few rows
Avoids the overhead of the meaningless internal_id on the agency table.

That's certainly worth 4 bytes/row.  We're not storing data on tape any more, it's not as big of a consideration as it used to be.

You are right, but how about when the related tables contains trillions of rows. So for 1 trillion rows, it takes more 4TB data. Doesn't it affect the overall performance of database?

If you are reading the whole table, yes.  But most of the time we look for a few dozen/hundred rows at most.  And why extend to "trillions" of rows?  If you're dealing with that volume the hardware required isn't going to break due to an extra 4TB in the heap.


MySQL select data over time that matches pattern over several rows

I do something similar on my side, and I use a MySQL Stored Procedure / Cursor.

You loop through each line of the table and record the current datetime entry and plant_status into variables, and then move on to the next line.

At which point you compare the current datetime and plant_status with the last ones.

If it matches a set condition (in your case new_status != last_status) you add an entry to a temporary table.

And then at the end you simply select * from temporary_table_name to show you all the rows where the status changed.

You could easily convert this to check the datetime as well (changing the old_datetime variable each time the status changes), and comparing that to the current one to find out if there is a two hour difference).
(this probably should be a comment rather than an answer but I don't yet have enough points to comment - sorry)


Why does MATCH() give Score 0, if there are less rows in the database?

With only one line left all occurances are 100% of the search, and everything above 50% is considered a stop word. My guess is that a stop word gets scored 0. There's also a stop word list (the word 'database' is not in it though) found here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

From http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html you find:

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match.


Improving PostgreSQL cluster performance

I found an interesting solution which you might be interested in. Basically, it involves creating a temporary clone of the table, where the data is copied into (this does the clustering). This will keep your original table available. After the insert is completed; you use the copy as new source; and discard the original (unclustered) table.

  The table is partitioned into three child tables: static1, static2, and latest. The parent table has a rule that redirects all inserts into the "latest" partition. This serves as the data collection point, where new rows sit until they are migrated into one of the other two partitions. The "static1" and "static2" partitions serve as front and back buffers for the cluster operation. Only one partition will actually have any rows at any given time (the front buffer); the other will be empty (the back buffer).

You can also have a look at pg_repack; which claims to provide an alternative to VACUUM FULL without the exclusive lock. This plugin basically does the same as the blog above does manually:

  To perform full table repacks, pg_repack creates a work table in the "repack" schema and sorts the rows in this table. Then, it updates the system catalogs directly to swap the work table and the original one.

Alternatively, playing with maintenance_work_mem might be a simple fix; increasing that might be enough.


Connecting to an external database with pgAdmin III

This line:

 host    all             all             192.168.0.0/24          md5

will let through connections from IPs matching 192.168.0.X where X is any byte.

The IP address of your error message being 192.168.108.161, it does not match this pattern because 108 is not 0.

To enable addresses like 192.168.X.Y, you'd need a /16 instead of /24 meaning that the first 16 bits only are fixed.

Like this:

 host    all             all             192.168.0.0/16          md5

Don't forget to reload Postgresql. From the official docs, use pg_ctl reload. If that doesn't work, then there are other ways to do it listed in this question.


SQL Server 2016 not using index used in 2012

You see different plans because from SQL Server 2014 there is a new cardinality estimator in SQL Server. And then they added some new features to SQL Server 2016 for the the new CE.

First some test data to reproduce what you see.

create table dbo.T(C1 char(10) default '', C2 varchar(11));

go

insert into dbo.T(C2)
select top(800000) row_number() over(order by (select null))
from sys.columns as c1, sys.columns as c2, sys.columns as c3

go

create index IX_T_C2 on dbo.T(C2)

And the queries that will produce the two different plans for you so you can compare them in the same version of SQL Server.

-- Table scan version
select C1
from dbo.T with (index = 0)
where C2 = 100000
option (maxdop 1);

-- Index Scan version
select C1
from dbo.T with (index = IX_T_C2)
where C2 = 100000
option (maxdop 1);

The table scan version in SQL Server 2012 scans all rows and returns one. No surprise there.

The Index scan version of SQL Server 2012 scans all rows in the index and returns one row. There is something there that needs to be looked on further but for now you should take an extra look at the Estimated Number of Rows for the Index Scan operator.

The Table Scan version of SQL Server 2016 is no different than the version in 2012. It scans all rows returning one row.

The Index Scan version looks the same as in 2012 but the cost is much higher and that is because Estimated Number of Rows is much higher than in 2012

So SQL Server now thinks it has to do 80000 RID Lookup to return 1 row and that is why it chooses the Table Scan in SQL Server 2016 with the new cardinality estimator.

The new estimator sees the predicate where CONVERT_IMPLICIT(int,C2) = 100000 and gives up. It uses the standard guess of 10% selectivity for an equality predicate, where 10% of 800,000 rows = 80,000. The original estimator used more complex logic to produce a non-guess (accurate!) estimate of one row.

Now to the issue with the Index Scan. That is probably not what you want. You want SQL Server to do an Index Seek finding the row you are looking for. Currently SQL Server can't do that because you have a type mismatch in the where clause and you do get warnings about it in the query plan.
Fix that and you should see a plan with an Index Seek and a RID Lookup in both versions of SQL Server.

Note also that cost percentages in execution plans are always based on optimizer estimates, not real run time information.


How to create an "empty" partial index, or equivalent, in Postgres?

You are saying "rows" where I think you mean "columns".

It is not legal to specify an empty list of columns in an index.  Just pick a column, preferably one with a short data type.  If nothing else, just repeat the same column in both places.

CREATE INDEX mytable_cond ON mytable (cond) WHERE cond = TRUE;

Be careful how you formulate our index and queries.  A cond = true query cannot use a partial index defined where cond is true and vice versa, those are not considered equivalent operations.


When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

There is no need to drop and recreate the index.

Just use

ALTER TABLE dbo.production_data
  ALTER COLUMN serial NVARCHAR(32) NOT NULL;

This is a metadata only change.

Altering a column from NVARCHAR(16) to NVARCHAR(32) does not affect the storage at all.

Going the other way round (from  NVARCHAR(32) to  NVARCHAR(16)) would give you an error about objects being dependent on the column though so maybe Visual Studio just always generates that boiler plate code in lieu of checking whether it is actually required.


Avoiding a sort on an already clustered index for group by

As you have no WHERE clause, you (the query, that is) must scan the whole table to be able to compute the aggregate.  Once it has all the data, it is much easier to sort them directly than to go to an index and match the rows and return them in order.  This latter would involve further I/O which is slow compared to operation in the memory.

If you were on PostgreSQL 9.2 (or above) and you had an index on (pid, did) then the optimizer might have chosen an index scan instead.


Single DB shared across multiple servers

No, you can't have the same data from a single database shared across multiple servers, at least not in read/write mode. At the risk of ignoring that you might be prematurely optimizing here, there are some pretty common ways to scale SQL Server, but they aren't all as simple as you might expect, and they also might not meet all of your requirements (or budget, since for many of the options Enterprise Edition is required).

READ/WRITE

For read-write scaling you can "shard" data using federated servers and distributed partitioned views. But note that is not easy to set up. Another options is merge replication but it can also be a bear to set up and maintain.

READ ONLY

You can scale out reads only to multiple other servers by using Log Shipping (or simple backup / restore), transactional replication, Database Mirroring (though this feature is deprecated, so be careful about long-term projects) and Database Snapshots of the mirror, or in SQL Server 2012, AlwaysOn Availability Groups with readable secondaries. (Some good but admittedly biased comparisons with competing vendor solutions here.)

As an aside, I really don't like the term "active secondaries" as that implies to me that you can read/write to them, which you can't.

EDIT

Given the new requirements in the updated question, it seems the part that needs to scale is almost entirely read. One suggestion might be to have a small instance on each application server (could be Express so long as its resource limitations - most importantly 10GB data file - are sufficient). This instance would serve two main purposes: (1) queue updates to the primary server so that the application doesn't have to wait for them (and so that multiple updates from different users can be combined) and (2) serve as a data store for the read-only information so that this doesn't always have to ve retrieved from the primary server. With this idea there isn't really a simple "turn it on" feature that will help accomplish this, but the setup is pretty trivial in the grand scheme of things.


Transactional replication altering tables and adding stored procedures

You should be able to make DDL changes as described in this article .

Make Schema Changes on Publication Databases

make sure you dont use SSMS. Use T-SQL only.

How to: Replicate Schema Changes (Replication Transact-SQL Programming)
specify a value of replicate_ddl for @property and a value of 1 for @value.

EXEC sp_changepublication
@publication = N'publication_name',
@property = N'replicate_ddl',
@value = 0;

EDIT : Below is what you need to take care, if you want to avoid reinitializing  subscription(s).

@sync_method=N'character'

Script out the replication and look for sp_addpublication --- @sync_method=N'character'
If the sync_method is “character” and you add a column to the publication, it will generate a message and ask to reinitialize subscription(s).
Change the sync_method to “native”.

@replicate_ddl=0

By default, the @replicate_ddl is set to 0 “false”, meaning no schema changes are replicated.
Changing the value to 1 “True” will allow changes done on publisher to propogate to subscribers (as shown in below figure).

Using T-sql:

EXEC sp_changepublication @publication='kin_test_Trepl'
    ,@property='sync_method'
    ,@value='native'
-- sync_method = native ==> this will not reinitialize subscribers
-- sync_menthod = character ==> this will generate below message:
--DDL replication is forcing reinitialization because either publication'kin_test_Trepl' uses character mode bcp, or timestamp/identity column is being replicated as base type only for article 'numbers'.
--Reinitialized subscription(s).
GO

EXEC sp_changepublication @publication='kin_test_Trepl'
    ,@property='replicate_ddl'
    ,@value=1
-- 1 = allow ddl changes to be replicated to subscribers
-- 0 = DO NOT propogate ddl changes to subscribers
GO

Then we can add the column to table that is replicated  as below:

e.g: ALTER TABLE numbers ADD comment varchar(200)

Note that the column is nullable. If it is not null then it will generate below error:

  Msg 4901, Level 16, State 1, Line 1
  ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'comment' cannot be added to non-empty table 'numbers' because it does not satisfy these conditions.


I assume you're talking about me, and I'm not sure what post you mean, but here is my thought:

Patch your SQL Servers regularly.

I'm such a huge fan of patches that I wanted to make it easier for people to find the most current updates for SQL Server, so I built SQLServerUpdates.com.

If you ever want justification for why you should be patching, just go back through the list of hotfixes in each cumulative update that you've skipped. You'll find bugs that deliver incorrect query results, and that's usually all management needs to hear in terms of why we need to patch.

Here are just a few fixed SQL Server 2017 bugs for incorrect query results - not an old, dusty version of SQL Server, but the brand new one they just released:

All transactions reported as completed even though some fail while trying to commit in SQL Server 2017
Incorrect behavior when you use memory-optimized tables with "where exists" statement in SQL Server 2016 or 2017
Returns incorrect results when computed column is queried after installing hotfix that's described in KB 3213683 and enabling TF 176 in SQL Server 2016 or 2017
Queries against PolyBase external tables return duplicate rows in SQL Server 2016 and 2017
Incorrect MDX query results if a table cross join is run on a table hierarchy member in SSAS
A data mask on a floating points column is not applied in SQL Server 2017 and SQL Server 2016
Divide-by-zero error when a parallel query is forced to run in serial mode in SQL Server 2017

And all that was only the first cumulative update. Want your query results to be accurate? Get your patching on.


List ranges and the total count based on condition

You were close with what you had. Just needed a couple of changes.

Declare @VID INT  = 1

    ;WITH Tmp as
    (
      SELECT
            TM.CharToAdd as Prefix,
            sno,
            sno - ROW_NUMBER() OVER(PARTITION BY TM.CharToAdd ORDER BY sno) as grp
      FROM dbo.TblDetails TD
      LEFT JOIN dbo.TblMaster TM on TM.[SID] = TD.[SID]
      WHERE isIssued = 0 and isUsed = 0
            AND  (TM.VID = @VID OR TM.VID IS NULL)
    )
    SELECT  Prefix,
            MIN(sno) as RangeStart,
            MAX(sno) as RangeEnd,
            COUNT(*) as [Count]
    FROM Tmp
    GROUP BY grp, Prefix

You need to partition on TM.CharToAdd to make the counter restart for each prefix value and you need to change AND  TM.VID = @VID to AND  (TM.VID = @VID OR TM.VID IS NULL) if you want the rows from TblDetails that does not have a match on SID in TblMaster.


Why am I seeing these mongodb 3.6.5 server log messages in huge amounts?

Unauthorized: not authorized on admin to execute command { replSetGetStatus: "1", $db: "admin", $clusterTime...

The message indicates there is a user or application trying to run the replSetGetStatus command without having the correct privileges.

This command returns the current state of your replica set from the point of view of the server that processed the command. If  this error is logged frequently, it is likely the result of a monitoring script or agent.

You can track down the source by searching the MongoDB server logs for related connection history. The first few lines for a given connection ID will usually include client metadata (optional, so will vary by driver/application) as well as the username used with authentication enabled.

For example, using a Linux-like command line to find the first entries for conn95:

> grep conn95 mongod.log | head -2
2019-01-15T21:41:19.026+1100 I NETWORK  [conn95] received client metadata from 192.168.1.123:59354 conn95: { driver: { name: "PyMongo", version: "3.6.0" }, os: { type: "Darwin", name: "Darwin", architecture: "x86_64", version: "10.14.2" }, platform: "CPython 2.7.14.final.0", application: { name: "monitoring-agent v1.2.3-dev" } }
2019-01-15T21:41:19.027+1100 I ACCESS   [conn95] Successfully authenticated as principal monitoring-agent on admin

These log lines include the following info:

the connection originated from 192.168.1.123
the client application identified itself as monitoring-agent v1.2.3-dev
the application successfully authenticated as user monitoring-agent via the admin database

If this is an expected connection from a monitoring agent, the likely fix would be to grant a role which includes the required access for replSetGetStatus (for example, the built-in clusterMonitor role):

use admin;
db.grantRolesToUser("monitoring-agent", [{ role: "clusterMonitor", db: "admin" }]);

If the connection is unexpected, you could determine an appropriate action based on the source IP or other details. You may wish to shutdown a monitoring app that is no longer required or remove the associated user account and block connections via your firewall. See MongoDB Security Checklist for some available security measures to consider.


Importing schema to new or different tablespace

It's not actually possible to specify a different tablespace when importing using the oracle imp utility. However, as a workaround, you can pre-create the tables by doing a ROWS=N import into the USERS tablespace, then alter table mytable move tablespace BLOG_DATA; for each table to move them to the new tablespace, then do the import again with the IGNORE=Y parameter to ignore the table creation errors and import all of the data.

If the data was exported using Data Pump (expdp), (as an aside, everyone should be using this these days, rather than the old legacy exp/imp utilities) you can easily import into another tablespace using the REMAP_TABLESPACE parameter.

eg:

impdp scott/tiger@ZOMG file=blog_data.dmp directory=mydir remap_tablespace=USERS:BLOG_DATA


assign "all values" instead of "null" in Conditional Where clause

You can do something like this :

SELECT *
FROM vehicle_register
WHERE (vehicle_type = @param OR @param is null)

You are using parameterized queries and not just a query build by string concatination in the frontend I hope.


How to disable data type check while pushing data to Linked Server

I see you have asked the same question at: Performance improvement in linked server-Disable data type check

  I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer.

Highly recommend to use SSIS. Linked servers are not meant to push data. Also, depending on the amount of data you are pushing or pulling (direction matters!!), the performance of linked server will always be slow + the n/w bandwidth plays an important role as well.

Refer to my answer for : Which one is more efficient: select from linked server or insert into linked server?

  Few days back I cam across one article (I don't remember exact site name) which mentioned while inserting data in remote server you can disable data type check which can improve performance.He had mentioned one Set Command.

AFAIK, there is no such command in SQL Server to disable data type check which improves performance !!


Is it correct to use IP addresses (instead of hostnames) while configuring Mongo DB cluster? Does it cause any issues?

Generally it is not recommended to use IP addresses to configure a cluster. You have not mentioned your environment, but it is common for IP addresses to change in many environments with a reboot, or perhaps you will need to move one or more of the nodes to a different host in the future.

Should that happen, your IP address will change but if you use hostnames (even those in the hosts file rather than DNS) it gives you a layer of abstraction and means you can have the hostname remain the same, not have to alter your database configuration for such a move.

This becomes particularly important for config servers in a sharded environment. An altered IP address is procedurally the same as an altered host name, and as you can see from this procedure, that means that moving a config server will require downtime for your cluster.

With all that said, for testing or for an environment where you can tear everything down and start again (or similar), these concerns do not really apply and you can use IP addresses in your configuration. For any system that will run long term, or any system that will run in production and require minimal interruption, then the use of hostnames over IP addresses is certainly recommended.


Postgres: Dictionary to remove leading zeroes from alpha-numerical words

Postgres text search only supports prefix matching, not postfix matching like you could use:

Get partial match from GIN indexed TSVECTOR column

Depending on exact requirements a number of workarounds come to mind.

If all leading zeros are insignificant, you could remove those from the indexed column in an expression index like:

CREATE INDEX foo_idx ON tbl
USING GIN (to_tsvector('simple', regexp_replace(col, '\m0+', '', 'g')));

Or use a regular expression fitting your actual requirements:

dbfiddle here

Then match the index in queries like:

SELECT *
FROM   tbl
WHERE  to_tsvector('simple', regexp_replace(col, '\m0+', '', 'g'))
    @@ to_tsquery ('simple', '2ed2323 & Wunder:*');

Remove leading zeroes from the tsquery value as well - if those can be contained:

   ...
    @@ to_tsquery( 'simple', regexp_replace(my_querystring, '\m0+', '', 'g'));

Related:

PostgreSQL GIN index not used when ts_query language is fetched from a column


I would guess that because you're issuing a login your declaration of SIDS is not transferred to the shell started by the su command.  You can test this by checking the value of SIDS after you issue the "su - oracle"


How to merge multiple rows in one column with hierarchy Postgresql 12?

Its a bit lengthy,
So the steps are:

Old Table

create table test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));

New Table

create table newtest(id int, description varchar, parent_id int, cost decimal(10,2));

Create a sequence as you have mentioned in your question

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

Now Run below query

with cte(id, description, parent_id,cost) as
(select nextval('seq_parent_id'), service, null::bigint,0::decimal
from test group by 2
),
cte1(id, description, parent_id,cost) as
(select nextval('seq_parent_id'), subservice, t2.id,0::decimal
from test t1
inner join cte t2 on t1.service=t2.description and t2.parent_id is null group by 2,3
)

insert into newtest

select * from cte
union all
select * from cte1
union all
select t1.id,t1.description,t3.id,t1.cost from test t1
inner join cte t2 on t1.service=t2.description and t2.parent_id is null
inner join cte1 t3 on t1.subservice=t3.description and t3.parent_id=t2.id

DEMO
Explanation of above query is demonstrated in this fiddle
NOTE: Before running the query please ensure that counter value will not cross the already existing ID. If newly inserted rows are more than minimum existing count then set the start of sequence max(id)+1 before running this query.


How can I store function names to be executed on a subset of rows?

Any time you use a scaler function you have to run the function once for each row.  Complex CASE statements will usually be faster for SQL to process, even though there are painful to read.


Adding column into MySQL table size 150GB

For 5.6:  pt-online-schema-change is probably your best bet.  It creates a new table with the change, then gradually copies rows over.  Meanwhile, it uses a TRIGGER to keep the new copy updated.

For 8.0:  ADD COLUMN now has ALGORITHM=INSTANT.

Would you like to show us SHOW CREATE TABLE; there may be some improvements that would decrease the problem.


Implications of changing compatibility mode in SQL Server from 100 to 110

Have a look at the following link:

ALTER DATABASE Compatibility Level

Scroll down and you will see the section "Differences Between Lower Compatibility Levels and Level 110" and identify if any of these items will affect you or not.  If not, then just change the level to 110.


Are locks by default acquired at the beginning of a transaction, or just when they are needed?

Locks are acquired immediately before reading or writing occurs. Depending on the locking granularity selected by the storage engine, locks may be acquired at the row, page, partition, or object (table) level.

  If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?

Yes, it might be advantageous for concurrency, depending on the isolation level in use by concurrent transactions.

Related reading:

Lock Escalation (Database Engine) - Books Online
Understanding SQL Server Concurrency - Kalen Delaney (free ebook)


Creating/dropping MySQL users using "IF NOT EXISTS" when row based replication is in place

You are correct in that row based replication is solely for table changes. CREATE USER etc are replicated as statements.
CREATE USER IF NOT EXISTS does prevent a failure overwiting an existing user and a replication failure if the user exists. It doesn't guarantee that the new user definition is the same.
MariaDB's CREATE OR REPLACE USER ensures the user created can always replicatoin and that the user becomes the same.
Another technique that will work for MySQL is DROP USER IF EXISTS followed by CREATE USER.
The more thorough solution for consistency is to be strictly on how replicas are created and don't update replicas except through replication.


mysql privilege to kill processes of other users

From here

  If you have the PROCESS privilege, you can see all threads. If you
  have the SUPER privilege, you can kill all threads and statements.
  Otherwise, you can see and kill only your own threads and statements.

  You can also use the mysqladmin processlist and mysqladmin kill
  commands to examine and kill threads.

See here for privilege granting - search for SUPER.


I'm with @sa555. but to exemplify better, you can adda Person_Has_Address entity, that has a relation to a new AddressType table.

It would be like:

AddressType would have two records: "CurrentAddress" and "PermanetAddress". In the case more address type wouldd come up (like mailing address, or work address), your are covered-up


Relative cost of two similar queries involving XML columns

Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values.

SQL Fiddle

The extra cost for the second query comes from using a table valued function to shred the XML.

First query:

Second query:


Get value increment during an hour of a day

One possibility is to first row_number() the records to get the first and last value per video, day and hour. Then join the two sets of first and last values to get the respective differences. Group the result on video and hour and get the sum or the average per video per day.

SELECT first.video_id,
       first.timestamp_hour,
       sum(last.views_count - first.views_count) views_count_diff_sum,
       sum(last.likes_count - first.likes_count) likes_count_diff_sum,
       avg(last.views_count - first.views_count) views_count_diff_avg,
       avg(last.likes_count - first.likes_count) likes_count_diff_avg
       FROM (SELECT video_id,
             timestamp_day,
             timestamp_hour,
             views_count,
             likes_count
             FROM (SELECT video_id,
                          timestamp::date timestamp_day,
                          date_part('hour', timestamp) timestamp_hour,
                          views_count,
                          likes_count,
                          row_number() OVER (PARTITION BY video_id,
                                                          timestamp::date,
                                                          date_part('hour', timestamp)
                                             ORDER BY timestamp ASC) rn
                          FROM elbat) first
             WHERE rn = 1) first
            INNER JOIN (SELECT video_id,
                               timestamp_day,
                               timestamp_hour,
                               views_count,
                               likes_count
                               FROM (SELECT video_id,
                                            timestamp::date timestamp_day,
                                            date_part('hour', timestamp) timestamp_hour,
                                            views_count,
                                            likes_count,
                                            row_number() OVER (PARTITION BY video_id,
                                                                            timestamp::date,
                                                                            date_part('hour', timestamp)
                                                               ORDER BY timestamp DESC) rn
                                            FROM elbat) last
                               WHERE rn = 1) last
                       ON last.video_id = first.video_id
                          AND last.timestamp_day = first.timestamp_day
                          AND last.timestamp_hour = first.timestamp_hour
       GROUP BY first.video_id,
                first.timestamp_hour;


Large set of execution plans hangs up SSMS

Have you looked at SentryOne Plan Explorer? It can handle humongous plans that Management Studio chokes on. You can also generate both actual and estimated plans from within the tool, so you don't even have to bother with SSMS.


Data Synchronization process in Database Mirroring and Alwayson Groups SQL Server

Without any HA like DBM or AG, will there be any log buffer present in
  SQL Server WAL mechanism?

As SQLAuthority Blog Here  The book online definition of the LOGBUFFER seems to be very accurate. On the system where I faced this wait type, the log file (LDF) was put on the local disk, and the data files (MDF, NDF) were put on SanDrives. My client then was not familiar about how the file distribution was supposed to be. Once we moved the LDF to a faster drive, this wait type disappeared.

  Is there any log buffer and log capture which also present in Database
  Mirroring, and Is that same as in Always on Groups?

Comparing to Database Mirroring (DBM), AlwaysOn required some serious rework in the counters and in the structure of counters, despite the fact that the transfer of the data is more or less following the very same path as in DBM. Reason for this is:

The fact that we can handle more than one secondary replica
That an Availability Group can contain more than one database.

Another few new counters got added to SQL Server:Databases. These counters give information about the SQL Server Log Cache of each individual database which is playing an instrumental role when transferring data to the secondary replicas or on the secondary for redo operations.

The counters are named:

Log Pool Cache Misses/sec – showing the number of log blocks which could not be found in the Log Cache
Log Pool Disk Reads/sec – Number of Reads issued by the Log Cache to read log blocks from disks
Log Pool Requests/sec – Number of log block reads per sec

In fact Always On is a bit different than database mirroring (DBM) with respect to sending the log blocks to the secondary replica(s).   DBM flushes the log block to disk and once completed locally, sends the block to the secondary.

for more details you can find Here and Here

  What is log pool here?

As literally log pool used in AlwaysOn availability group in SQL server Database for system monitor.

The SQLServer:Databases object in SQL Server contains performance counters that monitor transaction log activities, among other things. The following counters are particularly relevant for monitoring transaction-log activity on availability databases:

Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec.
For you ref Here

  Why two different name Log flush and Log Hardening? Can any one
  provide a link for database mirroring sync process.

The primary replica makes the primary databases available for read-write connections from clients. The primary replica sends transaction log records of each primary database to every secondary database. This process - known as data synchronization - occurs at the database level. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database. Data synchronization occurs between the primary database and each connected secondary database, independently of the other databases. Therefore, a secondary database can be suspended or fail without affecting other secondary databases, and a primary database can be suspended or fail without affecting other primary databases.

Log is flushed on the secondary replica for hardening. After the log flush, an acknowledgement is sent back to the primary replica.

Once the log is hardened, data loss is avoided.

you can also monitor log harden through Dashboard Like right click on dashboard

for your ref Here and Here

  In DBM or AG if i am using Synchronous mode, How does application can
  know Acknowledge Commit is success on both replicas will relational
  engine take care of that or storage engine take care about that.

Through TSQL query in primary replica we know that synchronization is update in both replica or not.

select * from master.sys.dm_hadr_database_replica_states;
Go

I am also attaching the screen shot here

Here synchronization_state is 2.

2 = Synchronized. A primary database shows SYNCHRONIZED in place of SYNCHRONIZING. A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.


How to recover from two queries stuck in kill state causing all the database to not respond?

Please seem my answer to your other similar question involving innodb_force_recovery = 4 https://dba.stackexchange.com/a/53355/30545


How Can I clone a database in Microsoft SQL server using PHP?

Your best option is to take and then restore a backup of the database.  If your sample database is fairly static then I suggest making a gold standard backup and using that.  If it's under constant modification then taking a backup and then restoring it on the spot should be sufficient.

BACKUP DATABASE [Sample] TO DISK=N'<path to file>'

RESTORE DATABASE [TargetName] FROM DISK=N'<path to file>'
WITH REPLACE, RECOVERY
, MOVE 'file1' TO '<new path for data file>'
, MOVE 'log1' TO '<new path for log file>'

Just make those commands dynamic and grant whatever user you are using for this db_creator and db_backupoperator

However, this command assumes that [TargetName] doesn't already exist.  You can check for the existence of a database by querying sys.databases.  If you need to override an existing database, then you need to kick everyone out before starting the restore.

Both of the commands above should be run from [master] to avoid conflicts.

This command will set a database to single_user mode, which will kick everyone out.  However, the first connection to it will take the only allowed connection, so you should run this and then immediately run the restore command, preferably in the same transaction.

ALTER DATABASE [TargetName]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;


My performance_schema.setup_consumers was not properly set.

By default performance_schema.events_statements_history_long is not enabled.

update setup_consumers set enabled="YES" where name="events_statements_history";
update setup_consumers set enabled="YES" where name="events_statements_history_long";


I would compute the buckets by FLOOR(price/10).  Should 510 be in the 50 bucket?  Or the 51 bucket?  Or both?

"put each item into an appropriate bucket" sounds like an INSERT statement.  But
"will be summed" sounds like a SELECT statement.  Which do you mean?

Assuming you are selecting all 45M rows, here is the optimal query:

SELECT  10 * FLOOR(price/10) AS 'price bucket',
        category,
        subcategory,
        SUM(order_quantity) AS 'quantity'
    FROM tbl
    GROUP BY 1,2,3

(Or do you need item in the GROUP BY?)

The real performance problem is that you need to read all 45M rows.

There might be a way to speed it up.  What is the data flow?  Do you insert rows and then never change or delete them?  If so a Summary Table that you build and maintain (daily?) would be much smaller and faster.


File permissions for database backups and certificate backups

Whenever you run BACKUP CERTIFICATE or BACKUP MASTER KEY, SQL Server will modify the Access Control List (ACL) on each resulting filesystem file it creates1 so that no-one other than the following Windows principals have access to the file:

OWNER_RIGHTS has Full Control.  OWNER_RIGHTS is a well-known security identifier, S-1-3-4 that represents the current owner of the object.  When an Access Control Entry that carries this SID is applied to an object, the system ignores the implicit READ_CONTROL and WRITE_DAC permissions for the object owner.
Members of the local "Administrators" group have Full Control
The Virtual Account or Managed Service Account used to run SQL Server has Full Control.  Note this is not the SQL Server Service Account you configure in SQL Server Configuration Manager.  For a default SQL Server instance, the account is typically NT SERVICE\MSSQLSERVER.  For a named instance, the account is named NT SERVICE\MSSQL$INSTANCE_NAME.
Inheritance is removed from the object's ACL to prevent access by any other principal.

This behavior is by design, and is an attempt to limit access to the resulting certificate and its private key.  Allowing "everyone" to access the certificate and private key files allows everyone to restore a backup of the TDE encrypted database onto the server of their choice, and be able to view all the encrypted data as if it was not encrypted.  What you're proposing to do seems like a very bad idea to me since it essentially circumvents the encryption.

See my blog post for more details, and example code.

1 - assuming the SQL Server Service has "full control" rights to the target folder in the backup statement.


How do I get DBeaver to do a DB2 UPDATE via Cursor

As mustaccio points out, you need to change your statement terminator. In DBeaver you can do that by:

Right-click on the data source and choose "Edit connection"
In the leftmost frame at the bottom, there is SQL Processing
Check the Datasource checkbox

Now you can alter the statement delimiter


PostgreSQL: pg_rewind complains about wal_log_hints = on not been set, it is

I have wal_log_hints = on set on both servers.

But presumably it was set too late, that is, after the generation of at least some of the WAL files involved in the rewind (in the pg_wal directory).


Point in time Restore failed - Error 3456 'Could not redo log record'

From the docs:

  As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time.

Do you have an older full backup?  Contrary to popular belief, full backups do not break the "log backup chain."  So you should be able to restore an older full backup (that meets this requirement, and then do point in time recovery from the log backup that you took after the delete.

Another possible issue that comes to mind: maybe you have log backups running that you don't know about.  This seems unlikely at this point, but you could check for that by querying msdb.dbo.backupset:

USE [msdb];
GO

SELECT TOP (100)
    [name],
    first_lsn,
    last_lsn,
    checkpoint_lsn,
    backup_start_date,
    backup_finish_date,
    [type],
    [database_name]
FROM dbo.backupset
WHERE [database_name] = 'hydra1'
ORDER BY backup_start_date DESC;

If there any backups with type = 'L' after the full backup other than the one you created, then you'll need to restore those as well.

Since you've confirmed there is no corruption present in the FULL backup, it's possible that there is corruption in the LOG backup you're trying to use.  You could try running CHECKDB on the live database to see of the current log is corrupted.


SqlDeveloper: ora-12505 TNS listener does not currently know of SID given in connect descriptor

Did you:

configure listener properly with service-name...,
then start your listener with ~]# lsnrctl start <listener-name>;
and then check status of listener using ~]lsnrctl status <listener-name> ?


What is an Effective Way to Count the Number of Pages in Oracle?

If the typical use pattern is to page through all the data, then all the records will eventually need to be sent to the client.  In this scenario re-querying the database for every page is in-efficient.  You should consider running the query once and paging through a cache on the client side.

If on the other hand the typical use pattern is to page through a very small amount of the data, then this method may be appropriate.  Nick Chammas' comment on using ROW_NUMBER() is fleshed out by Tom Kyte in a Oracle Magazine article.

SELECT * FROM (
   SELECT /*+ first_rows(25) */
      your_columns,
      Row_Number() Over (Order By something unique) rn
   FROM your_tables
   )
WHERE rn BETWEEN :n AND :m
ORDER BY rn;

I recommend you read the whole article.  Note in particular the warning to sort by something unique so that the results are consistent between pages (given static data):

  You need to order by something unique for these pagination queries, so
  that ROW_NUMBER is assigned deterministically to the rows each and
  every time.


Understanding "Date Last Modified" for "System Stored Procedures"

According to BOL, the modify_date from sys.all_objects shows:

  Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or modified.

However, if you have a maintenance plan that recompiles stored procedures, using sp_recompile, or have system stored procedures that run with the WITH RECOMPILE option, this will also cause the modify_date to update.

There was a Connect Item raised about this, but it was closed as 'Won't Fix'.

This could potentially explain your Date Last Modified issue.


To turn that off, use set heading off

It then should result in:

SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "Expiring Password" from dba_users where lower(username) in ('gunner','gunnersa','gunnerx3');

alter user GUNNER account lock password expire;

You probably also want to use:

SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERM OFF
SET NEWPAGE 0

To get a "cleaner" SQL script when there are more than just three rows.


I think you want to do two different things in one query:

randomly choose one name out of all the ones `WHERE g_number=123
count all the rows WHERE g_number=123.

If that's what you want to do, do it with two subqueries:

SELECT
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
     LIMIT 1
   ) AS name
   , (
     SELECT count(*)
     FROM t
     WHERE g_number = 123
   ) AS count_of_123

That will give you as a result:

|    name | count_of_123 |
|---------|--------------|
|    Sara |            3 |

or

|    name | count_of_123 |
|---------|--------------|
| Michael |            3 |

or

|    name | count_of_123 |
|---------|--------------|
|    John |            3 |

You can check it at http://sqlfiddle.com/#!9/5342d2/8/0

This is not just MySQL, it is standard SQL (except, maybe, for the name of the rand() function, that might be DBMS-dependent). The query also reflects clearly the fact that you want to do two (related but different) things. You can check also at SQLFiddle the PostgreSQL version, and the SQLite one.

Within the realm of MySQL, you can use one non-standard query that will also work (and which, performance-wise, is probably better):

SELECT
   name, count(*) AS count_of_123
FROM
   (
     SELECT name
     FROM t
     WHERE g_number = 123
     ORDER BY rand()
   ) AS q0 ;

I think this is closer to what the original poster had in mind. Even so, I wouldn't rely on it. This is very far from standard SQL. MySQL might decide to change how this is implementd and behaves in the future. Also, IMHO, it is less clear what the intent of the query is.

Check it at http://sqlfiddle.com/#!9/5342d2/10/0


Find time of execution of old query in Oracle

It means you have the SQL_ID of the SQL statement and now you want to know when it was executed.

You can use the following query where you can supply the SQL_ID of the SQL query that you have in DBA_HIST_SQLTEXT.

SQL> SELECT timstamp FROM dba_hist_sql_plan WHERE sql_id='your sql_id';


For a correlated subquery you should use a CROSS APPLY, unfortunately MySql doesn't allow it, but you can simulate it on this way:

SELECT     `symbols`.`id`,
           `symbols`.`symbol`,
           `statistics`.
           `marketCapitalization`,
           `s`.`companyId`,
           `s`.`marketCapitalization`
FROM       `symbols`
INNER JOIN `company_key_statistics` `s`
ON         `s`.`pk` = (SELECT `pk`          -- the PK of company_key_statistics
                       FROM   `company_key_statistics`
                       WHERE  `companyId` = `symbols`.`id`
                       ORDER by `createdAt`
                       DESC LIMIT 1)
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

You can find some example on SO.