How to write and read files from client machine SQL Server 2019

A SQL Server instance has no concept of another machine other than it's own. When you connect to it with SSMS (or other means) you're specifically initiating the connection to that server, not the other way around. So out of the box I don't believe there is a way with pure SQL to accomplish this.
These are a couple ideas of what you can do:

xp_cmdshell essentially is a way to execute commands on the server that the SQL Server instance is running on. If you can setup a file share to the local machine from the server that the SQL Server instance is running on first, then you can probably leverage xp_cmdshell to write the files to that share.

CLR might be another solution you can use to create a procedure that pipes the data from your server to a web service that has access to the local machine. Then that web service can easily write files to the local machine using C# code (or really any code of your choice that supports web services).

Last but certainly not least (probably the most standard way to go about this) is SSIS. You can create an SSIS package that exports your data and then writes actual files to your local machine (though you probably need to add your local machine as a connection in the package).


How does max_allowed_packet affect the backup and restore of a database?

I saw an interesting answer to a question about the biggest blob you may have. Here is the statement I saw in ServerFault : innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones.

Based on that ServerFault post from Nils-Anders Nøttseter, you should query the table and find out which BLOB is the biggest, multiply that number by 11 or more, and use that answer as the max_allowed_packet going forward.

It's funny that I addressed another question where I suggested sizing the max_allowed packet to hopefully solve the issue.

CAVEAT

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large.

In the past, I have had issues like this with MySQL Replication where the Master had a smaller value for the max_allowed packet than the Slave. It required me to bump up max_allowed_packet to 256M and restart mysql on the Master. In 99% of issues I have had, this would solve it. A few times I had to set to 512M.


You can do this using an Oracle External Table.

First, create a directory pointing at the directory holding the .csv file:

CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/path/to/yourdirectory';

Grant read access to your user:

GRANT READ ON DIRECTORY MYDIRECTORY TO YOURUSER;

Create a table that'll read from your CSV file:

CREATE TABLE mytable
(
  id NUMBER
)
ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY MYDIRECTORY
    ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
        FIELDS (id NUMBER)
      )
    LOCATION ('yourfile.csv')
 );

Your query would then be:

SELECT * FROM TABLE WHERE ID IN (SELECT id FROM MYTABLE);


Is there a list what datatype corresponds to the system_type_id in Sys.columns

Yes you can look in sys.types for such a list or use the type_name function to look up an individual type_id.


Hits to Insert Ratio and Insert to Prune Ratio

Hit:Insert     0.47:1

means "every inserted entry was requested 0.5 times, before it was removed". (Yes, that's not good.)

Insert:Prune    1.96:1

means "for every second insert, I had to prune/remove one existing entry". An old rule of thumb states "a value between 10 and 20 percent is acceptable", while your value is 50%.

Another helpful number is the query cache hit rate, which is

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

in your case thats roughly 9%, meaning only 9% of the queries can be satisfied by the query cache.

Recommendations reach from "give the query cache more RAM" over "play around with query_cache_min_res_unit" to "give the query cache less RAM"... which is not a really helpful advice.

Since you have set query_cache_size=8M I would recommend to raise this value. 8 Megabytes seems very low to me.


Restoring .bak file SQL Server 2005 in SQL Server 2008

All restore operations in SQL Server are backward compatible. You can restore a 2005 backup on a 2008 instance just like you would restore a 2008 backup on that same instance.

There are some limitations to older SQL Server versions, but a backup that was taken on a 2005 or newer instance can currently be restored on any supported SQL Server version.

However, the backups themselves are not backwards compatible. So on a 2005 instance you cannot restore a backup that was taken on a 2008 instance.


can't set batched_key_access=on in optimizer_switch on mariadb 5.5

There is no batched_key_access key in MariaDB -- the functionality was released in MariaDB 5.3, long before MySQL 5.6, so it naturally had its own naming, from which MySQL diverged. What you might be looking for is join_cache_bka, but please read the page about block-based algorithms in full, as you also need to choose a suitable value for join_cache_level, depending on your needs.


Getting Batch Requests Per Second On a Particular Database

'Batch Requests/Sec' is a server-level counter, not available for individual
database instances. Keep in mind that single batch can access multiple databases.

You can get an aggregated count of batches and RPCs executed per database with an XE trace with a histogram target like the below example. This will count only the context database where the batch was executed, not all databases touched by the batch.

CREATE EVENT SESSION [sql_batch_completed_by_database] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.sql_batch_completed',source=N'sqlserver.database_name')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

CREATE EVENT SESSION [rpc_completed_by_database] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_name))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.rpc_completed',source=N'sqlserver.database_name')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

--get snapshot of aggregated counts
WITH
      sql_batch_completed_by_database AS (
        SELECT CAST(xet.target_data AS xml) AS event_data
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'sql_batch_completed_by_database'
    )
    , rpc_completed_by_database AS (
        SELECT CAST(xet.target_data AS xml) AS event_data
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'rpc_completed_by_database'
    )
    , requests_by_database AS (
        SELECT
              slot.value('(./value)[1]', 'sysname') AS DatabaseName
            , slot.value('@count', 'bigint') AS RequestCount
        FROM (SELECT event_data FROM sql_batch_completed_by_database) AS xe(event_data)
        CROSS APPLY event_data.nodes('/HistogramTarget/Slot') AS histogram_target(slot)
        UNION ALL
        SELECT
              slot.value('(./value)[1]', 'sysname') AS DatabaseName
            , slot.value('@count', 'bigint') AS RequestCount
        FROM (SELECT event_data FROM rpc_completed_by_database) AS xe(event_data)
        CROSS APPLY event_data.nodes('/HistogramTarget/Slot') AS histogram_target(slot)
    )
SELECT DatabaseName, SUM(RequestCount) AS RequestCount
FROM requests_by_database
GROUP BY DatabaseName;
GO


Change Data Capture missing changes for a specific record

The problem was that the retention period was set to the default of 3 days.

I have extended it to one year using this script:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 525600; --set data retention for 1 year


Retaining earliest instances of duplicate entries from a table

DELETE t1
FROM people t1
JOIN people t2 USING (name,age,phrase)
WHERE t1.id > t2.id;

fiddle

PS. None column is indexed now - so on 60kk table this query may be infinite with high probability. Create an index at least by the most variable column (I'd prefer phrase) including id, i.e.
CREATE INDEX idx_name
ON people (phrase, id);

Of course the best option is to create an index by (phrase, name, age, id).

Alternatively you may gather id values to be deleted into temporary table and then delete them:
CREATE TEMPORARY TABLE tmp (id INT PRIMARY KEY)
SELECT t1.id
FROM people t1
JOIN people t2 USING (name,age,phrase)
WHERE t1.id > t2.id;

DELETE people
FROM people
JOIN tmp USING (id);

This may be faster then the former method. And the same index creation will improve.
fiddle


How inaccurate is sys.dm_db_partition_stats?

If you're looking for unused or abandoned tables, the number of rows won't be enough. You'd better look into index usage stats:

select database_id, object_id, index_id,
    last_user_seek, last_user_scan, last_user_lookup, last_user_update
from sys.dm_db_index_usage_stats

If you are interested in several methods for tracking table usage, I wrote about it some months ago.


How to implement composite partition (Range list / Hash range) partition in SQL server?

My requirement is first do the list based on state then do the range based on year

That's not a requirement: that's a design.  SQL Server doesn't do that.

Instead, you need to look to the data structure to provide the efficient access pattern within each partition.

If you use a clustered index, then within each partition SQL can perform efficient range scanning by the leading index columns.

And if you use a clustered Columnstore index SQL can use Rowgroup Elimination


Update does not working while select returns right results

Please try this:

UPDATE(
  SELECT Ord.OrderID, Ord.Completed
  FROM Order1 Ord
  WHERE EXISTS(
          SELECT *
          FROM Product P
            INNER JOIN Delivery D
              ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
          WHERE Ord.OrderID = P.OrderID
        )
)
SET Completed = 'NO' ;

or without a derived table:

UPDATE Order1 Ord
SET Completed = 'NO'
WHERE EXISTS(
        SELECT *
        FROM Product P
          INNER JOIN Delivery D
            ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
        WHERE Ord.OrderID = P.OrderID
      );


sql server - Restoring Windows Database Backup File on a Linux Server Instance

Depending on what build you are using .. if you are getting an error then it might be a bug that you need to report to Microsoft.

You can still give the undocumented - sp_restore_filelistonly a try but it is just a wrapper as Brent says in his answer :

EXEC sp_restore_filelistonly
    @device_type = 'DISK',
    @backup_path = '/var/tmp/(redacted).bkp';

If above does not work, then for sure open a bug report with Microsoft.

It works for me - I have tested (Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64)   Feb  9 2018 19:39:09    Copyright (C) 2017 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)) and for me it works using both (restore filelistonly and using undocumented sp)

restore filelistonly from disk = '/var/opt/mssql/backup/dbname_(FULL)_20170108_020026.bak'


A rough estimate for bare tables is pretty simple. The number of bytes in a row is roughly

  Total number of bytes for all columns
+ dbms overhead

dbms overhead might include a fixed or variable number of bytes per row or per column. The number of bytes of overhead often depends on the datatype of the column. The dbms might also apply padding to make bit columns, for example, align with a machine word boundary.

Multiply the bytes per row by the expected number of rows one year, two years, or five years from now. Repeat for every table, or for only a statistically representative sample of the tables.

You can often find leads to the details for your dbms by searching for "your_dbms row format". If you searched for

sql server row format

you'd probably find this blog article.

Your dbms might require you to take into account compression, compact storage formats, and indexes.  Compression and compact storage formats reduce the amount of storage space you need; indexes usually increase it.

If you want a really good estimate, build a testbed. Fill it with statistically representative sample data. Do the arithmetic based on what that tells you.  Include estimates for new tables to implement new features.


Unable to add records to query in Access 2007

Microsoft Access has a setting in the Query Properties window called Recordset Type.  You need to set this to Dynaset (Inconsistent Updates) to allow your query to be updatable.


Filtering a table based on multiple values of a single column

Sure, you are looking for the IN condition.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

SELECT UserID, Attributes, UserPrivileges, 1stName, 2ndName
FROM User
WHERE 1stName IN ('John', 'Jane', 'Joseph', 'Laura');


How to implement an effective ACL/auth in a relational db?

Your many-many table is a good idea. In this table, the existence of a row can mean "no access" or "has access" depending on your need.

I'd aim to store least data and make "no row" mean the most common state by implication.

Looking at your requirement 2, I'd have it so that "row exists = has access". However, the opposite can be be read from item 1. Ditto for 3 and 4. So you need to explain this more

I'd also look at grouping users. Do you really permission users individually? Ditto for items: can thes be grouped somehow?


"Unrelated" INSERT and UPDATE blocking each other

Lets take a look on the execution plans.

1st query - Insert

BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);

And its execution plan

We see that sql server is doing Clustered Index Insert operation.

Now lets take a look on update

BEGIN TRANSACTION;
UPDATE test
SET exp_date = GETDATE()
WHERE exp_date IS NULL AND mykey = 'A' -- <-- Blocking

And its execution plan

SQL Server scans the Clustered Index of the table, and put U lock on it, even if it can choose another index to find the matching rows. The reason is, because we have only 1 row in the table, and SQL Server Optimizer find easier to scan the Clustered Index instead of searching data in the non-clustered index.

But what if we force the sql server to use non-clustered index?

BEGIN TRANSACTION;
UPDATE test
SET exp_date = GETDATE()
FROM test WITH(INDEX = not_expired_keys)
WHERE exp_date IS NULL AND mykey = 'A' -- <-- No Blocking!!!

And its execution plan

I think if we put more rows in the table SQL Server will choose the non-clustered index for finding the rows that must be updated, and there will be no blocking.


How to retrieve Oracle Key Performance Indicators in 5 minute intervals within a time range

This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page which requires the diagnostics package. There is a certain irony to running this script through SQL Server reporting services which is beyond the scope of this question.

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '1'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY,
'Application' AS  APPLICATION,
'Commit' AS  COMMIT,
'Configuration' AS CONFIGURATION,
'Administrative' AS   ADMINISTRATIVE,
'Network' AS  NETWORK,
'Queueing' AS   QUEUEING,
'Cluster' AS   CLUST,
'Other' AS  OTHER))
ORDER BY 1

This query is not as good as what Grid displays but it's close.


For me the wording "Distributed transactions are not supported for temporary tables" is clear: In a distributed transaction you should not use statements that involve temporary tables. This means you should not even insert into an already created temporary table in an XA transaction. Of course it can happen that it works but nevertheless it is not supported so you should not use it.

I found Note 1051543.1 "Known issues with Global Temporary Tables and Distributed or XA transactions"  on Oracle Metalink stating

the usage of temporary tables is not supported in distributed transactions
there are a lot of ORA-00600 errors known when using global temporary tables in XA transactions. Even block corruption ca happen
there are some types of XA transaction where the usage of temporary tables will work. One of such situations is described in the article:

  A global temporary table can be safely used if there is only single branch transaction at the database using it, but if there are loopback database links or XA transactions involving multiple branches, then problems can occur including block corruption (...)
  this restriction on temporary table is documented in the 10g manual . It is not documented in the 11g manual but this is considered as a bug.

  This restriction on temporary table is contained in the 12c manual (you supplied the link).


Can I disable the spyware in SQL Server?

I don't think Microsoft refers to it as "CEIP" in the official docs. I believe Microsoft is refering to "Customer Feedback for SQL Server on Linux". You can disable it like this,

sudo /opt/mssql/bin/mssql-conf set telemetry.customerfeedback false
sudo systemctl restart mssql-server.service

Additionally you can block the Telemetry Servers at the network layer

You can verify this by checking the conf file.


Conceptual Design of a data warehouse (declaring the grain)

To quote Ralph Kimball, "declaring the grain" is:

  saying exactly what a fact table record represents

You need to choose which entity from you list above represents the grain. You could choose any of them, however claim_line_items looks the best candidate to me as this is at the lowest level (it is the only table with no children).

If you make claims your grain, then it won't be possible to report on what the individual line items are. This may be what the business wants to track (and they don't care about line items). However, it'll be very difficult to change the grain to line items at some point in the future if they change their mind. Whereas you can make the grain line items and provide aggregations which show summaries at the claim level.

For a more detailed discussion about this (again by Kimball) go here.


I want to create a mirror in SQL Server 2008, but the database is in the status "restoring ..."

You appear to be attempting to access the database on the mirror machine.  You can only access it from the Principal.

If the Principal has a problem or shuts down or whatever, the mirror will become the Principal and you then use that machine to access the database.


How to convert sql server table data into MS Access(.mdb) file

Please have a look at the SQL Server Import and Export Wizard

SQL Server Import and Export Wizard


when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables.

Yes if the query is simple enough to prove with constraint exclusion. See the manual.

  How does the query optimizer do the partition pruning

By proving that the query cannot access those partitions using CHECK constraints. See the documentation link above.

Because the constraint values must be known at planning time there are some cases where the planner cannot exclude partitions because it doesn't know the values of predicates early enough in query execution. Sometimes these queries land up needing to be split into two parts manually.

You can see which partitions are being scanned by using EXPLAIN.


Have you looked into using a Cross Apply?  This should do the trick for you, but I was unable to test a there is no data in your sample.  For each Domain in the @Latest, it gives you the latest 5 results from the domainDetailDataHistory table.

SELECT  h.DomainId
    , c2.[Date]
    , c2.Passed
FROM    @Latest h
        CROSS APPLY ( SELECT TOP ( 5 )
                                c.DomainI
                              , c.[Date]
                              , c.Passed
                      FROM      DomainDetailDataHistory c
                      WHERE     c.DomainID = h.DomainId
                                AND c.[Date] != h.[date]
                      ORDER BY  c.[Date] DESC
                    ) AS c2;


How to keep MySQL general_log after I restart my computer?

Add this to my.ini

[mysqld]
log

If you have a specfic place for the log, such as C:\mysql_general.log

[mysqld]
log=C:/mysql_general.log
log="C:\mysql_general.log"

It's one of those. I haven't done mysql in Windows in a while.

Then restart mysql

net start mysql

Give it a Try !!!

CAVEAT

Prior to MySQL 5.6, you can use log.

In MySQL 5.6, log is deprecated. Use general-log instead.

UPDATE 2011-12-09 22:18 EDT

If you do not have my.ini defined, then you must define one.

To keep things simple, just create my.ini in the folder where my-huge.ini, my-large.ini, my-medium.ini, and my-small.ini appear. Just open up notepad on my.ini in that folder.

Then add the lines I mentioned before:

[mysqld]
log

or

[mysqld]
log=C:/mysql_general.log
log="C:\mysql_general.log"

UPDATE 2011-12-09 22:26 EDT

In the worst case, try putting my.ini in C:\


SQL Cannot patch the latest CU4 for SQL 2014 SP3

I am not 100% sure this is the cause of your issue, but this is too long for a comment.

This can occur if SQL Server components are installed similar to the following:

Install SQL Server Database Engine
Install a service pack
Install Reporting Services

At this point, the installer will refuse to install a post-SP CU due to the RTM version of Reporting Services.  Based on my investigation, a certain registry value gets reset to the RTM version when Reporting Services is installed, and this is what the installer is referring to.

The solution is to install the service pack again (SP3, in your case), which will update Reporting Services, and then you will be able to install the post-SP3 CU.

I would assume this would be the case for other components such as Analysis Services, but I have only run into this issue with Reporting Services.


Query processor ran out of internal resources and could not produce a query plan” - MariaDB

OR does not optimize well; don't use that form.
One of these indexes will be optimal; add both:

INDEX(company_id, period_code, paydate)
INDEX(period_code, company_id, emp_id)

{d '2020-05-01'} can be simply '2020-05-01'
You might find this easier to deal with

    paydate >= '2020-05-01'
AND paydate  < '2020-05-01' + INTERVAL 1 MONTH

COUNT(loan_payment) checks for NULL; perhaps you want simply COUNT(*)?


Why straight SQL query run faster than stored procedure?

Assign the value of parameter to the local variable in the procedure then used that variable.


a) Pretty much anything takes a schema stability lock. You don't want something else changing the structure of the table while you are updating your statistics. According to this, update statistics takes schema stability and modification locks.

b) If something tries to change the table's structure, it will be blocked. IIRC, update stats does dirty reads, so it shouldn't block connections that are merely reading or writing.

c) If you use FULLSCAN, it will read the entire table because that is what you told it to do. I don't see how that can be seen as anything but 'causing heavy i/o'. Normally the default of 'sampling' works well enough, but I have seen it cause problems with data with non-homogenous distributions. Often, it's also easier to just reindex the whole table (especially if you can do it online) because reindexing is parallelizable where as update statistics isn't. (AFAIK, MS did not fix that in sql 2008.)


Multidimensional pivot in postgres without crosstabs

I think you're after something like this:

  SELECT id,
         start_of_week,
         sum(hours) as "Overall",
         sum(hours) filter (where "dow" = 'sunday   ') as "sunday   ",
         sum(hours) filter (where "dow" = 'monday   ') as "monday   ",
         sum(hours) filter (where "dow" = 'tuesday  ') as "tuesday  ",
         sum(hours) filter (where "dow" = 'wednesday') as "wednesday",
         sum(hours) filter (where "dow" = 'thursday ') as "thursday ",
         sum(hours) filter (where "dow" = 'friday   ') as "friday   ",
         sum(hours) filter (where "dow" = 'saturday ') as "saturday "

    FROM newtable

   WHERE id is not null

GROUP BY id,
         start_of_week

ORDER BY start_of_week;

I added an overall sum as well.


Yes, the data pages of a used index that are cached in the buffer pool will be taking up space in the data cache.  But don't let that turn you away from using indexes (first off, a clustered index is the actual table data so keep that in mind as well).  The use of indexes (properly designed and implemented, of course) is a good thing.

Your memory issues are most likely not from having indexes on your tables.  Dive into the memory issues, what exactly are the problems?  Are you having a low Page Life Expectancy?  How is your memory configured on the server?  Is the max server memory to low restricting the size of the buffer pool?

To get a breakdown of the index pages in your data cache, you can run the below query:

select
    count(*) as total_page_count,
    count(*) * 8 as total_consumption_kb,
    sum(row_count) as total_row_count
from sys.dm_os_buffer_descriptors
where page_type = 'INDEX_PAGE'
group by page_type

To get these stats by database:

select
    db_name(database_id) as database_name,
    count(*) as total_page_count,
    count(*) * 8 as total_consumption_kb,
    sum(row_count) as total_row_count
from sys.dm_os_buffer_descriptors
where page_type = 'INDEX_PAGE'
group by database_id
order by total_consumption_kb desc


Try running the following from sqlplus and posting the results.

select username, account_status, lock_date, expiry_date from dba_users where username='<your user name>';
select instance_name, host_name from v$instance;

set term off
set serveroutput on
set long 10000000
set trimspool on
set tab off
set linesize 190
set pagesize  50
set feedback  on
set editfile "/tmp/_USER_CONNECT_IDENTIFIER_PRIVILEGE.buf"
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
set timing on
set term on


Connecting to AWS RDS DBInstance with MySQL workbench

If you have instantiated a MySQL RDS Instance, you want to create a DB Security Group

First Create a DB Security Group
Under "Security Group Details", set one of the following connection types

CIDR/IP
EC2 Security Group

Assign Address/Mask
Make sure you do not have a proxy/firewall causing interference


Is there any drawback of MIXED replication mode?

Here are some disadvantages

For you MyISAM users out there, concurrent inserts are prohibited.
Data changes cannot be read unless you use mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.
BLOB data is written "as is" rather than the SQL that created the BLOB
For more information, see the MySQL Documentation


How can I resolve a database name to a directory?

I couldn't find a better way of doing this than rolling my own routine. Currently, I am using this perl code:

s/(\W)/sprintf('@%04x', ord $1)/eg;


Trying to add permission to a Role is failing

One thing to note is that RESTORE is a command, not really a permission that can be granted.  Which is why your GRANT failed.

In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely:

RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database ... members of the db_owner
fixed database role do not have RESTORE permissions.

If the dbo, not db_owner, seems confusing it is like this.   One login owns the database, it may be sa or MyDomain\MyLogin.   That login maps to the dbo user and should have rights to restore the database.   Inside the
database, many users may be in the db_owner role, but since those are inside
the database to be restored, they do not get the RESTORE permission.

So, you can make logins members of the dbcreator fixed server role or you
can make one user the owner of a database and he should then be able to do a
restore of that database.

To test the consequences I created a login TestLogin that is not a sysadmin for these tests.

TEST 1: Granted TestLogin the dbcreator server role and the public role
in MyDatabase.  (It is necessary that TestLogin have some rights inside
the database to be restored.)

RESTORE DATABASE MyDatabase ... was successful.

NOTE:  If the restored database did not already have TestLogin as a user,
TestLogin lost access to the database once the restore was complete since
the sysusers data was restored with the contents from the backup.  So,
TestLogin needs to exist in the source backup if this user is to retain some internal rights after the restore.

TEST 2: Revoked TestLogin from the dbcreator server role and the publicrole in MyDatabase.  Made TestLogin the owner of the database.

USE MyDatabase
exec sp_changedbowner 'TestLogin'

RESTORE DATABASE MyDatabase ... was successful.

In other words, dbcreator can create NEW databases, but can only restore a
database where it is already a user and still retain rights to that database.  Hope that this helps you decide what to do.


How to download or place a copy of original tables with locked-permissions via remote connection in Oracle SQL Developer?

Evidently, this is how you copy tables from databases with permissions via remote connection to a local file-folder on Oracle SQL Developer (Mac OS X Yosemite):

Target database and expand to view tables.
Select desired table and right-click.
Hover-over 'Table' and select 'Copy'
A pop-up window will prompt to save copy table-name.

I've attached a screenshot below for clarification.  Thanks!


What situation RDBMS need to vm or docker virtualization?

Personally, I really like the idea of putting a golden set of data in a VM or container for QA testing. Spin up a new VM/container for each project and you can test it in isolation. When testing is complete, it goes away cleanly. You can do the same thing for development: Think git branches.
I can't tell you how many times I've had projects look like they were failing because someone mucked up the data in the dev/qa environment to test a different project.


The issue is most likely that you haven't configured your Service Principal Names for the Report Server, so it is encountering an error trying to utilise Kerberos Authentication.
Firstly, you need to confirm your SPNs are configured correctly for the Report Server. You can use Kerberos Configuration Manager for SQL Server to identify any issues with your SPNs and fix those issues.
Once your SPNs are corrected, add the address (http://servername/) to your trusted sites to enable your Windows identity to be passed through automatically and facilitate SSO.


Select command returning different column values

The query you wrote is basically asking the database to give you any two rows of data (any two orders). If you wrote the query with an ORDER BY clause then your results should be the same each time.

SELECT TOP 2 OrderID FROM Sales.Orders ORDER BY OrderID ASC;

This would give you the two rows with the lowest OrderID and you will still get the same two rows even when you add other fields to the SELECT list.

You might be thinking that TOP means that the database will give you the first two, but it actually means that it should stop returning rows after it has returned two rows. And since you didn't have an ORDER BY it could just return whichever two rows are most convenient.


Separate databases for front-end and back-end

From your comments, one solution might be to make a Master-Slave replication setup (link to mysql replication here)

I would make the backend the Master, and the front-end the slave. If your front-end needs to write (contact forms, tracking etc) you would update your code in the Front-end to read from the slave, and write to the master.

The downside is, depending on the load, your backend-writes might be delayed a few seconds or more. But as an anecdote, I've got a server that handles 120 commands /second (averaged, as reported by munin) and the slave server isn't behind by more than 3 seconds.


MSSQL database physical design: RAID or spreading filegroups across different physical drives?

I believe all 3 options are very much correlated and can be implemented together:
Depending upon the table structure and such a size of database and tables you can have them on individual file groups especially if they are partioned.

Also it's always good to have them on good storage. We have in our scenario most of the data and log files of user dbs and temp dbs on SSD's

There are lot of factors if you are considering performance. I believe you should be on sql server 2016 + to make use of all new improved features like CE's.

Also consider other various factors like fibre cables that SAN is being used followed by good network bandwidth. All I can say per my experience is test the system or hardware with all sorts of load testing and see how well it suites your requirements.


Designing a database for a scenario with multiple user types

I don't believe there is one best design that fits all scenarios. It all comes down to the business rules that you are trying to enforce. If all types share the same attributes, it's probably best to use a single table with a classifier attribute. At the other end of the spectra there is the situation where the types share no attributes (unlikely in your case).

Typically though there are a set of common attributes that can be represented in a common table, say:

CREATE TABLE ORGANISATIONS -- I just invented a name here
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, ORGANISATION_TYPE ... NOT NULL
, <other attributes>
, CONSTRAINT ... CHECK ORGANISATION_TYPE IN (...)
);

Then a set of tables that represent attributes that are not common to all types:

CREATE TABLE CONTRIBUTORS
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, <other attributes>
, CONSTRAINT ... FOREIGN KEY (ORGANISATION_ID)
                     REFERENCES ORGANISATIONS (ORGANISATION_ID)
);

SQL99 (might even be SQL92, not sure) allows selects in CHECK constraints so we can add a CHECK constraint with a select that guarantees that the information corresponds with the organisation_type in organisations. However, not many vendors implements this so a common approach is to "inherit" the type attribute. First add a super key in ORGANISATIONS:

ALTER TABLE ORGANISATIONS ADD CONSTRAINT AK1_ORGANISATIONS
    UNIQUE (ORGANISATION_ID, ORGANISATION_TYPE);

Then we add the type attribute to the "sub-tables":

CREATE TABLE CONTRIBUTORS
( ORGANISATION_ID ... NOT NULL PRIMARY KEY
, ORGANISATION_TYPE ... NOT NULL
, <other attributes>
, CONSTRAINT ... FOREIGN KEY (ORGANISATION_ID,ORGANISATION_TYPE)
                     REFERENCES ORGANISATIONS (ORGANISATION_ID,ORGANISATION_TYPE)
, CONSTRAINT ... CHECK ORGANISATION_UNIT_TYPE IN (...) -- subset of types in ORGANISATIONS
)

The check constraint and the foreign key guarantees that the correct type is used.

Note that a table such as CONTRIBUTORS may be used for two or more types if they have a set of attributes in common. For adding additional attributes to such types the same technique can be used.

Yet another approach is to use nullable attributes for not applicable. In my experience it soon gets messy, and I would only use it if there are few such  attributes.


Joining two ltree based tree tables recursively

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates.

        select
            array_agg(distinct p2.name) permissions
        from
            permissions p1
        join
            permissions p2
            on  p2.path @> p1.path

Then I've used it for each role that match the path company_employee.warehouse_employee

select r.name,
       (select array_agg(distinct p2.name) permissions
        from   permissions p1
        join   permissions p2 on  p2.path @> p1.path
        join   roles_permissions rp on rp.permission = p1.id
        where  rp.role in (select distinct r2.id
                           from   roles r1
                           join   roles r2 on r2.path @> r1.path
                           where  r1.id = r.id)) permissions
from   roles r
where  r.path @> 'company_employee.warehouse_employee';

| "company employee"   | "{"building access","break room access"}"                         |
|----------------------|-------------------------------------------------------------------|
| "warehouse employee" | "{"building access","break room access","warehouse access","warehouse stock access"}" |

rextester here

As Evan Carroll has pointed out

If a role X.Y.Z inherits permissions from X.Y and hence from X
And a permission A.B.C inherits from A.B and from A

Your bridge table should avoid to assign those rows twice.

Given next data:

ID | Roles    ID | Perm
---+-----     ---+------
1  | X        1  | A
2  | X.Y      2  | A.B
3  | X.Y.Z    3  | A.C
              4  | A.C.D
              5  | A.C.E

Your bridge table joins (1,1) and (1,2):

X => A            <-- Redundant
X => A.B ~> A

In this case (1,1) is redundant because A permission is given by inheritance on A.B

And same for (2,3), (2,4):

                           Inherits from role
X.Y => A.C                 ~> X ~> A.B ~> A     <-- Redundant
X.Y => A.C.D ~> A.C ~> A   ~> X ~> A.B ~> A


Option 1

The correct answer is:

git export

In other words, the DDLs should exist in a code repository.

Option 2

Second option, use SQL*Developer's cart feature.

You can drag+drop the VIEWS of interest and export out all of the DDLs at once.

Option 3

I believe the cart feature uses DBMS_METADATA.GET_DDL() behind the scenes.

You'd have to roll your own code that loops through all of the relevant VIEWS.

Alternatives

  The fastest way to do something is to not do it at all

  -- Thomas Kyte

In this case:  Don't migrate off of Oracle.

Most Oracle licenses are bought on a perpetual basis.  You can run Oracle until the end of time, you just can't upgrade/patch it after you stop paying "maintenance and support".

I would have upgraded the database to 18c (latest) and stop paying the "maintenance and support".

"long term access" would still be a problem.  I would at least investigate if 18c XE (not yet out) can accommodate all of the static data.  It is supposed to include Advance Compression.

Final Thoughts

Since you said this is for READ ONLY archive purpose, I'll only remind you:
 "Database agnostic code is a myth".


Postgres10 picks very slow query plan, when faster plans seem available

Which seems to suggest that its best bet is to load 1.4 mil rows, and take 771 seconds? Am I reading this wrong?

You are reading it wrong in two ways.  It doesn't think it will read 1.4 mil rows.  It thinks it will start a index scan which if completed will read 1.4 mil rows, but that due to the LIMIT 1 it will get to stop very early on. Apparently it is wrong about how early it can stop, but that is what it thinks.

Also, cost estimates are in arbitrary units, not in milliseconds, so it doesn't think it will take 771 seconds.  And the cost at that level is again assuming the index scan runs to completion.  The cost of the top LIMIT node, 5784.38 arbitrary units, has been scaled back for the expected benefit of stopping early, while the costs of the child nodes have not been.

Presumably patient 100 has not had any claims in a long time (like maybe they died or moved away a couple decade ago), while PostgreSQL thinks their claims will be evenly interspersed through time.

It is hard to say more than that, without seeing the output of EXPLAIN (ANALYZE) rather than just EXPLAIN.

  Am I missing something obvious - a multiple-index doesn't seem to work - that could help Postgres see a better plan faster?

What multiple-index did you try?  I would think one on sfe_procedure (patient_id, code) would have a reasonable chance to work.

But you can just hit the planner with a big hammer by rewriting it to:

ORDER BY "sfe_claim"."from_date" + interval '0 days' DESC LIMIT 1

This will preclude use of the falsely appealing index "sfe_claim_from_da_3d3cc6_idx"


How can I perform this database backup/sanitization task?

Setup a slave.

Once everything is sync'd up and good.

Stop the slave (sql thread).
mysqldump slave data to orig.sql
run your queries to modify the data
mysqldump again to dev.sql
Restore the data using orig.sql
start slave to resume collection from the master.

This will take some time with a large db of course but it seems pretty straight forward to me.  It won't lock your master cluster at all (at least until you create the slave).  If you are modifying only a few tables you can tweak the mysqldump.

You can cron the operation and upload it to s3 or something for the devs.  Use some fast SSDs.


Comparing Two Values when one or more could be NULL

Equivalent and simpler:

IF  NEW.value IS DISTINCT FROM OLD.value  THEN
...
END IF;


How to delete and create user remotely on SQL Server 2000

I found a workaround

To change the permissions of the user to where it can't write

EXEC [Server].Database.dbo.sp_droprolemember N'db_owner', N'User'
EXEC [Server].Database.dbo.sp_addrolemember N'db_denydatawriter', N'User'

After I am done, change it back

EXEC [Server].Database.dbo.sp_droprolemember N'db_denydatawriter', N'User'
EXEC [Server].Database.dbo.sp_addrolemember N'db_owner', N'User'

Just tested it in my lab environment and it does the trick. Can't wait to get rid of all of our SQL Server 2000 databases! Just a few more months and we will be rolling 120 servers into one SQL Server 2012


Invalid connection error on SQL Server 2008

I found a similar question with an answer, this one https://superuser.com/questions/314206/cannot-connect-to-sql-server-from-excel-2007 on superuser. It turned out that for some reason, on the MS SQL Server 2008 I had to put a named instance and not just the name of the server, e.g. if the server name is "yoda", on the application configuration I have just to put "yoda" if the backend is a MS SQL Server 2005, if I use MS SQL Server 2008 I have to put "yoda\sqlexpress,1433". I do not know if an alternative solution could be updating some libraries.


Paged query against timestamp populated by trigger missing items

I think you found the main culprit alright. There are more ways to miss rows with this setup. Consider D3 I added to your time diagram:

D1 |st1....................|
D2       |st2......|
D3          |st3......|
R                |.......|
                   ^ -- read query starts here

Transaction D3 starts after D2, but since it's not committed when your read query in transaction R starts, it is invisible as well. It will be included in the next page, but it may still be confusing that it's missing this time. That's how the default transaction isolation level Read Committed is defined. You seem to be aware of it, but for the general public:

  When a transaction uses this isolation level, a SELECT query (without
  a FOR UPDATE/SHARE clause) sees only data committed before the query
  began; it never sees either uncommitted data or changes committed
  during query execution by concurrent transactions. In effect, a SELECT
  query sees a snapshot of the database as of the instant the query
  begins to run.

There is more. Read the manual.

With concurrent access to the DB, various conflicts might stall a DELETE query - which will patiently wait until it can get an exclusive lock on the rows to delete. 10 minutes of missed rows would indicate a (possibly unrelated) problem, I agree with your suspicion. Transactions that remain "idle in transaction" are prime suspects. To diagnose, look for log long running statements or transactions. Some pointers:

Set log_min_duration_statement in postgresql.conf.
Some basics on Logging Difficult Queries in the Postgres Wiki.
The statistics view pg_stat_activity. Related:

Postgres - estimate end time for a long update

check_postgres provides helpful tools.
Related blog.

Database design

You seem to be using data type timestamp. Your INSERT with statement_timestamp() AT TIME ZONE 'UTC' should work correctly for your purpose, but you shouldn't rely on INSERT statements to get this right. Rather move the logic to the table itself. Use the data type timestamptz (timestamp with time zone) for tombstone.delete_timestamp to begin with. This rules out any discrepancies with time zones or DST (daylight saving time). Details:

Ignoring timezones altogether in Rails and PostgreSQL

Also don't rely on the INSERT statement to provide the correct time. Put the logic in the column default (or even in a trigger BEFORE INSERT to overwrite any provided timestamp).

CREATE TABLE tombstone (
  id int NOT NULL PRIMARY KEY  -- assuming PK
, delete_timestamp timestamptz DEFAULT statement_timestamp()
);

Since timestamps are not visible until the transaction is committed anyway, the cheaper current_timestamp (transaction timestamp) may be just as useful as statement_timestamp() - depends on unknown details.

The simpler INSERT statement in the trigger to go with the above:

INSERT INTO tombstone (id) VALUES (OLD.id);

Query

Your current SELECT query can be faster comparing row values, which is also index-backed in Postgres:

SELECT id, delete_timestamp
FROM   tombstone
WHERE  (delete_timestamp, id) > (@sinceUTC, @id)
ORDER  BY delete_timestamp, id
LIMIT  @count;

Details:

Optimize query with OFFSET on large table

Secure alternative

All this aside, timestamps are just not completely reliable for queuing or paging in a multi-user environment. If you need it to be completely reliable, consider a different approach: Lock unread rows before retrieving and mark them as read. The new FOR UPDATE SKIP LOCKED in Postgres 9.5 may prove useful for this:

Postgres UPDATE ... LIMIT 1

Or, if the reading is done by a single user, you don't need locks. Just mark rows you have read. A partial index for unmarked rows makes reading fast.

Upgrade to current Postgres

Postgres 9.2 is getting old. Upgrading to the current version might help for various reasons.


A simple OR condition will do this:

select *
from dtsc_search_data
where c1 = 2
  or (c1 <> 2 and c2 = 2)
  or (c1 <> 2 and c2 <> 2 and c3 = 2);

SQLFiddle example: http://sqlfiddle.com/#!15/d1ca1/1


DROP database doesn't appear to drop all database objects

You can't drop the database you are currently connected to.  You are connected to some_db, not mydb.  You drop mydb, then create mydb agsin, but you don't change your connection from some_db to mydb. Creating a database does not automatically change your connection to the newly created database.  So the table you created is attempted to be created in some_db, where it evidently already exists.

You can't create objects in a database you are not connected to, so fully qualifying the object name will not help.  You can use the psql meta-command \c to change your connected database in the .sql script.  Of course you have to go through authentication again.


There is no mysql option to ignore a table in binary log.

If you could move the table to a separate database you might use binlog-ignore-db option, but this has its gotchas. You should understand how MySQL evaluates replication rules.

If you can write a wrapper script to load the data instead of mysqlimport (the manpage says that it's a command line interface to LOAD DATA INFILE), and the user you are importing the data with can have the SUPER privilege you might use sql_log_bin session variable like:

    SET @@session.sql_log_bin=0;
    LOAD DATA INFILE 'file_name'
      -- other LOAD DATA INFILE options ;
    SET @@session.sql_log_bin=1;

This will disable binary logging for the current session, load the data and enable binary logging again.


First thing is that SQL_CALC_FOUND_ROWS is evil. Just do not use it as indexes can't always be used together with it.

As for the scond part of the issue, mysql can not use an index for two ordered queries. And I got 3 of them here - lat and lon range scans and a time sorting.

See https://stackoverflow.com/questions/4083632/can-mysql-use-index-in-a-range-query-with-order-by


pg_dump of table and its transitive dependencies

uses a few custom enums or other types.

No. That's because those enum types can be used in different tables and such. PostgreSQL doesn't have a dependency tracker to make this work. The only exception to this that I know is sequences, -t will recreate sequences.

That said, if you recreate the database once, you only ever have to run pg_dump -t employees -c, the -c flag will drop the table and recreate it. This will unfortunately not work with foreign key constraints pointing to employees


Symmetric difference operation in Transact-SQL?

All set operators are translated to joins or join-like operators. You can witness that in the query plan.

For that reason the full outer join that you have there is the most efficient you can do. Disregarding, of course, the hopefully rare situation in which the optimizer picks a bad plan and a rewrite happens to perform better by luck. This can always happen.


Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect.  To see this, remove the SUMs and GROUP BY and stare at the output.  You may see, for example, the same mghours.hourstime showing up multiple times.  Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps.  Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;


Copy from one MySQL table to another MySQL table of same database

Suppose you have mydb.mytb and you want to create mydb.mytbcopy

I have five(5) approaches to doing this copy

APPROACH #1

In the mysql client, run the following

USE mydb
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

APPROACH #2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb | mysql ${MYSQL_CONN} -Dtest
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

APPROACH #3

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dtest < ${DUMPFILE}
rm -f ${DUMPFILE}
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

APPROACH #4

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

APPROACH #5

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dmydb < ${DUMPFILE}
rm -f ${DUMPFILE}

ANALYSIS

APPROACH #1 is the easiest in terms of steps, but requires pushing 40 million rows into one transaction. This will be the most taxing on the InnoDB Storage Engine.
For the other approaches, mysqldump will send 40 million row in chucks of thousands of rows

APPROACH #2 and APPROACH #3 will mysqldump the table into the test database. After creating the table in the test database, it is subsequently renamed and moved into the original database
APPROACH #4 and APPROACH #5 rename the table using sed against the stream coming from the mysqldump as it echoes the INSERT commands
APPROACH #2 and APPROACH #4 use pipes instead of an output file
APPROACH #3 and APPROACH #5 use an outpuit file for subsequent reload

If you want to copy mydb.mytb to an already existing table mydb.mytbcopy, and the two tables have identical structures:

APPROACH #6

INSERT INTO mytbcopy SELECT * FROM mytb;

Like #APPROACH 1, #APPROACH 6 would have a single transaction of 40 million rows

APPROACH #7

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

This approaches does not drop the table. It simply generates the INSERTs

EPILOGUE

I cannot give you a time estimate since I do not know the make up of the DB Server, table structure, index layout, and things like these.

GIVE IT A TRY !!!


MySQL code to show specific rows based on another...?

Given this test data: (this is a hint on how to make a more readable question in the future):

create table inventory(
  id int unsigned primary key auto_increment,
  item int unsigned,
  warehouse int unsigned,
  qty int unsigned,
  price int unsigned,
  weight int unsigned,
  other int unsigned);

insert into inventory(item, warehouse, qty, price, weight, other) values
  (1,1,1,3,4,5),
  (1,2,6,3,4,5),
  (1,3,3,3,4,5);

> select item,warehouse,qty,price,weight,other from inventory;
+------+-----------+------+-------+--------+-------+
| item | warehouse | qty  | price | weight | other |
+------+-----------+------+-------+--------+-------+
|    1 |         1 |    1 |     3 |      4 |     5 |
|    1 |         2 |    6 |     3 |      4 |     5 |
|    1 |         3 |    3 |     3 |      4 |     5 |
+------+-----------+------+-------+--------+-------+

You can have the desired output with the following query:

> select distinct
  i.item,
  COALESCE(w1.qty,0) as w1,
  COALESCE(w2.qty,0) as w2,
  COALESCE(w3.qty,0) as w3,
  i.price,
  i.weight,
  i.other
from inventory i
  left join inventory w1 on (i.item = w1.item AND w1.warehouse=1)
  left join inventory w2 on (i.item = w2.item and w2.warehouse=2)
  left join inventory w3 on (i.item = w3.item and w3.warehouse=3);

+------+------+------+------+-------+--------+-------+
| item | w1   | w2   | w3   | price | weight | other |
+------+------+------+------+-------+--------+-------+
|    1 |    1 |    6 |    3 |     3 |      4 |     5 |
+------+------+------+------+-------+--------+-------+

COALESCE function and LEFT JOINs are for the case that there is no row for item, warehouse combination


Deleting duplicate subscribers using max uuid postgresql

Here is a generic method that works as long as there is a lower-than operator on the column used to discriminate  (which is the case of uuid).

WITH dups as (
  select email, id FROM...< complete this with the duplicates detection>
)
DELETE FROM subscribers USING dups
WHERE dups.email = subcribers.email
  AND dups.id < subscribers.id;

You may replace id by a creation timestamp if these timestamps are guaranteed to be different. Even if they're not different, meaning there are (email,timestamp) duplicates, they will just be not touched by this DELETE. You can then do a second pass with another DELETE, this time with the uuid as the differentiator as in the query above. If the timestamps were all different this 2nd pass will delete nothing, otherwise it will finish the job.

Also you may do this in a transaction and check if there are still duplicates and the number of deleted rows seem good before doing a COMMIT or ROLLBACK.


MySql show content of ibdata1 file, block rising exponentially, why is it growing so fast?

InnoDb ruby is a great tool which does exactly what you want.

Troubleshoot

If you don't use innodb_file_per_table this can also be normal data growth so confirm this first.

If you use innodb_file_per_table then it is most likely indeed the undo history. You can confirm that by running:

mysql root@192.168.99.100:(none)> pager grep History
PAGER set to grep History.
Time: 0.000s
mysql root@192.168.99.100:(none)> show engine innodb status\G
History list length 0
Time: 0.001s

You can also check it with innodb ruby:

innodb_space -s ibdata1 undo-history-summary

This command will give you the number of pages used by undo history:

 innodb_space -s ibdata1 undo-history-summary |grep -v Page | awk '{print $1}' | sort | uniq | wc -l

Any of these will only reports current data. Unless you have a consistent high load with many long open transaction where the purge thread is unable to keep up you will mostly have free pages. It may be beneficial to plot this over time so you have an understanding of what your server is doing.

Fix

Shrinking ibdata is not possible. The only thing you can do is to dump and restore your data via mysqldump (or any other equivalent tool of your choice).

Other considerations

Since 5.6.3 it's also possible to separate your undo tablespace from the main ibdata file (https://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html). There may be some interesting thing for you.

In 5.7.5 innodb_max_undo_log_size parameter was introduced.


Incurs_seek_penalty column of sys.dm_os_volume_stats

This is now officially documented, with the following values:

Indicates the type of storage supporting this volume. Possible values are:
0: No seek penalty on this volume, typically when the storage device
is PMM or SSD
1: Seek penalty on this volume, typically when the storage device is
HDD
2: The storage type can't be determined when the volume is on a UNC
path or mounted shares
NULL: The storage type can't be determined on Linux operating system
Applies to: SQL Server (starting with SQL Server 2019 (15.x))

It looks like this is related to the low level DEVICE_SEEK_PENALTY_DESCRIPTOR structure (thanks, LowlyDBA!) - possibly in an attempt to check if a drive is an SSD or not (that approach is also discussed on Stack Overflow).
It would still be interesting to know if this information about the storage is (or will be) used by SQL Server (e.g., for updated I/O cost estimates, or performance optimizations inside the storage engine).


Hourly Transaction Log files size not consistent - is this Differential backup file?

It's more likely that the log backup occurs after some processes that generate a lot of log records, like index maintenance or any ETL-ish tasks.
You may find some benefit in increasing the frequency of your log backups to every 5-15 minutes so that internal truncation occurs more frequently, since that will likely make the backup size smaller.


How to run pg_dump so that it doesn't slow down other connections?

Little hard to say, because we don't know your database setup.

does your database use schemas with views in it. you can use the -n option to dump only the data schemas and rebuild you views after the restore.

maybe you can do it on a quit moment on your server, with -j you can parallelize the dump. It will take less time, but uses more memory.

Otherwise the slave option is a good practice


Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

First, to answer the questions implied in the comments, that the assignment of row numbers with the ROW_NUMBER() aggregate seems inefficient because we already have the COUNT(violation) numbers:

This is needed because the COUNT numbers can be different for each partition (class). Since we want the 2 top numbers (for each class), we can't find a useful condition for that. With the row numbers, we can use the WHERE v < 3 which gives us the top 2.

In version 9.3, the LATERAL joins were added in Postgres, which are similar to the CROSS and OUTER APPLY of SQL-Server. With this new kind of join, you can write a query that uses the COUNT numbers and a TOP 2 for each partition. Whether it is more or less efficient, you can test:

WITH classes AS
  ( SELECT DISTINCT class
    FROM people
  )
                             -- if you have a "classes" table, skip the above lines
SELECT
    v.id, v.name, c.class,
    v.violations
FROM
    classes AS c,
  LATERAL
    ( SELECT p.id, p.name,
             COUNT(d.violation) AS violations
      FROM people AS p
        INNER JOIN discipline AS d
          ON d.people_id = p.id
      WHERE p.class = c.class
      GROUP BY p.id, p.name
      ORDER BY violations DESC
      LIMIT 2
  ) AS v
ORDER BY
    c.class, v.violations DESC ;

Tested at SQL-Fiddle


Before and After update trigger on same table

There is no BEFORE trigger in SQL Server. An INSTEAD OF trigger can be used to provide similar functionality but the trigger code would need to perform the UPDATE.

However, an AFTER trigger can be used here by using the INSERTED (new) and DELETED (old) virtual tables to get the values needed for the calculation. The example below assumes a primary key column named StockID with a value that cannot be changed.

CREATE TRIGGER [dbo].[Price_Modified]
    ON [dbo].[STOCK]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Qty)
BEGIN
    UPDATE s
    SET value = new.price * (new.Qty - old.Qty)
    FROM STOCK AS s
    JOIN inserted AS new ON new.StockID = s.StockID
    JOIN deleted AS old ON old.StockID = s.StockID
    WHERE new.Qty <> old.Qty;
END;

GO


How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

The SELECT ANY DICTIONARY privilege (or, in earlier versions the SELECT_CATALOG_ROLE role) gives a user privileges to select from any data dictionary table.

The SELECT ANY DICTIONARY privilege would give a developer privileges to write whatever queries they'd like against DBA_SOURCE to see the source for any object, DBA_VIEWS to see view definitions, etc. But there is no guarantee that a particular front-end would actually leverage those privileges correctly.  Far too many GUIs simply select from the ALL_* data dictionary tables to display the objects that a user has object privileges on rather than recognizing that the user has permission to select from the DBA_* data dictionary tables.  Personally, I'm more than happy to query the DBA_SOURCE view (or use the DBMS_METADATA package) when I want to look at object source in a production database but lots of folks get antsy without the GUI schema browser.


Many-to-one Subselection in single query

You could have two separate exists clauses:

SELECT *
FROM cars AS T1
WHERE
    EXISTS
    (
        SELECT * FROM Imperfections as T2
        WHERE T1.uid = T2.uid AND imperfection = 1
    )
    AND EXISTS
    (
        SELECT * FROM Imperfections as T2
        WHERE T1.uid = T2.uid AND imperfection = 2
    );

This means two left semi joins to your Imperfections table, but if you have an index on UID and Imperfection, those should be trivial.

There are two advantages to using WHERE EXISTS over JOIN in this case.  The first is that a left semi-join stops after the first match, so it can be quicker than a join (which needs to go through all records).  The second is that if you somehow don't have good data constraints and have two records in Imperfections with the same UID and Imperfection, you could end up with duplicate rows back in your main query.


How to find Sum of dynamically generated time attribute in sql server 2012

Use your current query as a CTE (Common Table Expression) and select form that with aggregate functions, like:

WITH TimeDiffs AS (<your query>)
SELECT   taskid
       , SUM(diff)
FROM     TimeDiffs
GROUP BY taskid

(assuming you want the total per task, your question is not very clear on the details).

You could also use the existing query as a derived table:

SELECT taskid
     , SUM(diff)
FROM   (<your query>) AS TimeDiffs
GROUP BY taskid

which will produce the same result, though the CTE arrangement usually ends up being more readable and so easier to maintain going forward.


AWS DMS - How to select different databases for source and target points for a task

I got the Solution! yes it(AWS DMS) allows to move tables from database D1 to D2. just need to configure transformation rule section properly in JSON section. Plese refer below link within section "Example Rename a Schema"

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.html


Query Optimization - CTE Is Slow With OPENQUERY

While I haven't used OPENQUERY, I'll add some - more or less common sense - notes:

You are not doing any joins between the remote (Postgres) server tables and the SQL Server ones. I suggest you move the whole query inside the Openquery call.
The COALESCE() suggests that the 2 columns are nullable? Are they?
If not, remove the COALESCE() calls which prohibit the use of indexes. If they are nullable, you can try to modify the predicate to something like (if they can be 0, it'd have to be more complex):

AND ( c2."MCN" = c1.cc2  OR  c2."MCN" IS NULL AND c1.cc2 IS NULL )

The CAST(Saledate As Date) >= '01/01/2017' looks like a killer, too. There is no reason to use CAST. The expression can be written as (the cast may not be needed at all in Postgres, depending on the column type):

Saledate >= CAST('2017-01-01' AS date)

After pushing the whole query to the linked server, there is no reason to have the WHERE and GROUP BY in the external query. You can collapse the query into one level.
After the rewrite, and if it is still slow, check the execution plan in the remote server. Besides indexes, examine the plan regarding the CTE. CTEs in Postgres are optimization fences, and their use restricts the optimizer into certain options - which may be good or bad for efficiency - depending on too many variables.

The query, in Postgres syntax:

SELECT *
FROM OPENQUERY(192.155.10.22, '
    WITH MaxNumber AS
      (
        SELECT
            MAX(cc2) AS "MCN"
            Customernumber
        FROM employeeinfo
        GROUP BY Customernumber
      )
    SELECT
        c1.employeename           AS "Employee Name",
        c1.Customernumber         AS "Customer Number",
        AVG(eq.quantityordered)   AS "AverageQty",
        AVG(eq.price)             AS "AveragePrice",
        CAST(c1.Saledate AS Date) AS "Sale Date"
    FROM
        MaxNumber c2
      INNER JOIN Customer c1
        ON  c2.Customernumber = c1.Customernumber
        AND COALESCE(c2."MCN", 0) = COALESCE(c1.cc2, 0)
      INNER JOIN orderpart ep
        ON  ep.Customer = c1.Customerid
      INNER JOIN orderquantity eq
        ON  eq.employeename = ep.employeename
      INNER JOIN employee cm
        ON  c1.employee = cm.employee
    WHERE
        Saledate >= CAST('2017-01-01' AS date)
    GROUP BY
        c1.customername,
        c1.Customernumber,
        CAST(c1.Saledate AS date)
')

(will still need fixing, there is a mismatch between the openquery which has select employeename and the external query which has group by customername)


I am upgrading from 11.1.0.7.0 to 11.2.0.3 with R12.1.3 while pre upgrade/catupgrd.sql erroring out ORA-00600

This problem is related to timezone stuff. Did you execute the pre-upgrade (utlu112i.sql) scripts? And did you follow the instructions?


Try this for DBMS_METADATA.GET_DLL:

set long 200000 pages 0 lines 150
spool vw.sql
select
   dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner))
from
   all_views u
where
   owner IN ('HR','SCOTT')
order by owner,view_name ;
spool off

You can either include the schemas you are interested in or exclude the schemas you do not want in the "owner IN" clause.


Can someone explain why left joining two views in mysql is so slow?

According to the MySQL Documentation on Views

  Views (including updatable views) are available in MySQL Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

The first thing that must be realized about a view is that it produces a result set. The result set emerging from query invoked from the view is a virtual table because it is created on-demand. There is no DDL you can summon afterwards to immediately index the result set. For all intents and purposes, the result set is a table without any indexes. In effect, the LEFT JOIN you were executing is basically a Cartesian product with some filtering.

To give you a more granular look at the JOIN of two views, I will refer to a post I made last year explaining the internal mechanisms MySQL uses to evaluate JOINs and WHEREs (Is there an execution difference between a JOIN condition and a WHERE condition?). I will show you the mechanism as published in Understanding MySQL Internals (Page 172):

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

OK, it seems like indexes should be used. However, look closer. If you substitute word View for Table, look what happens to the mechanism's execution:

MECHANISM MODIFIED

Determine which keys can be used to retrieve the records from views, and choose the best one for each view.
For each view, decide whether a view scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the view scan becomes faster.
Determine the order in which views should be joined when more than one views is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused views from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

Every table (view) has no index. Thus, working with virtual tables, temp tables, or tables with no indexes really becomes indistinct when doing a JOIN. The keys used are just for JOIN operations, not so much for looking things up faster.

Think of your query as picking up two phone books, the 2014 Yellow Pages and the 2013 Yellow Pages. Each Yellow Pages book contains the White Pages for Residential Phone Numbers.

In late 2012, a database table was used to generate the 2013 Yellow Pages.
During 2013

People changed phone numbers
People received new phone numbers
People dropped phone numbers, switching to cell phone

In late 2013, a database table was used to generate the 2014 Yellow Pages.

Obviously, there are differences between the two Phone Books. Doing a JOIN of database tables to figure out the differences between 2013, and 2014 should pose no problem.

Imagine merging the two phone books by hand to locate differences. Sounds insane, doesn't it? Notwithstanding, that is exactly what you are asking mysqld to do when you join two views. Remember, you are not joining real tables and there are no indexes to piggyback from.

Now, let's look back at the actual query.

SELECT DISTINCT
viewA.TRID,
viewA.hits,
viewA.department,
viewA.admin,
viewA.publisher,
viewA.employee,
viewA.logincount,
viewA.registrationdate,
viewA.firstlogin,
viewA.lastlogin,
viewA.`month`,
viewA.`year`,
viewA.businesscategory,
viewA.mail,
viewA.givenname,
viewA.sn,
viewA.departmentnumber,
viewA.sa_title,
viewA.title,
viewA.supemail,
viewA.regionname
FROM
viewA
LEFT JOIN viewB ON viewA.TRID = viewB.TRID
WHERE viewB.TRID IS NULL

You are using a virtual table (table with no indexes), viewA, joining it to another virtual table, viewB. The temp table being generated intermittently would be as large as viewA. Then, you running an internal sort on the large temp table to making it distinct.

EPILOGUE

Given the internal mechanisms of evaluating JOINs, along the transient and indexless nature of the result set of a view, your original query (LEFT JOIN of two views) should be getting running times that are orders of magnitude. At the same time, the answer you got from StackOverflow should perform well, given the same JOIN algorithm I just described.

I hope the gory details I just posted answers your question as to why.


ERROR: operator does not exist: text[] ~~ text

The Postgres manual suggests you might have a design issue:

  Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

You can make your operator more efficient — it's better if it stops checking after the first match:

CREATE OR REPLACE FUNCTION like_in_array( arr text[], pattern text )
RETURNS bool AS $$
  SELECT coalesce(( SELECT true
                    FROM unnest(arr) AS u(n)
                    WHERE n LIKE pattern
                    LIMIT 1),false);
$$ LANGUAGE sql
IMMUTABLE;

dbfiddle here


Troubleshooting extremely long running query (SQL Server 2012)

After some excellent help from Kin, wBob, and Aaron Bertrand, I finally found my root cause: Table1 statistics needed updating.

I had already confirmed that statistics were updated 2 days prior during our weekly index maintenance, so didn't really think anything of it.  However, I gave it a try, and that made the query run time go from 2hrs 30min down to 2 seconds!

Command run was:

UPDATE STATISTICS Table1;

Thanks for all the help, particularly in the article "The "Runaway" Query"


Read Concern Majority when majority nodes are down

Using a read concern of "majority" does not cause the read operation to be submitted to multiple nodes.
When replication.enableMajorityReadConcern is true (default in MongoDB 3.6+) each node will note the most recent write operation that has been acknowledged by a majority of the voting nodes, and keep a snapshot of the data at that point in time.
A request using "majority" read concern instructs the mongod to read from that snapshot instead of the most recent write.
In your scenario, reads will continue normally with 3 nodes down.
In addition to keeping the snapshot at the latest majority commit, each node must also accept writes.  Keeping track of the not-yet-majority writes in addition to the majority snapshot requires some additional usage of cache.  If a cluster gets into a state where there is a primary and is able to accept writes, but is unable to replicate those writes to a majority of the voting nodes, this cache usage can increase to the point of causing system instability.
That can only occur when there is a voting member that does not contain data, i.e. an arbiter.
Since your scenario does not contain an arbiter, that cluster should not have the RCM-induced cache pressure issue.


Is it possible to modify an existing trigger definition in MySQL?

It sounds like you're asking whether it's possible to alter a trigger in an atomic operation, where, if the new definition fails, you don't lose the old one... similar to CREATE OR REPLACE VIEW, which replaces the view definition if the new definition is valid, but leaves the old one in place, if you can't replace it.

Unfortunately, there's no ALTER TRIGGER or CREATE OR REPLACE TRIGGER in MySQL.

I would suggest that the best practice is to lock the table where the trigger lives, so no rows are impacted with the trigger absent.  Dropping and adding triggers while a table is locked is allowed.

mysql> LOCK TABLES t1 WRITE; -- the next prompt appears once you've obtained the lock
mysql> DROP TRIGGER t1_bi;
mysql> DELIMITER $$
mysql> CREATE TRIGGER ti_bi BEFORE INSERT ON t1 FOR EACH ROW
       BEGIN
       ...
       END $$
mysql> DELIMITER ;
mysql> UNLOCK TABLES;

Update: MariaDB, in version 10.1.4, added support CREATE OR REPLACE TRIGGER to their drop-in replacement for MySQL.

https://mariadb.com/kb/en/mariadb/create-trigger/

Oracle's MySQL as of 5.7 still relies on the solution above.


Oracle 11g installation stuck at installation

I've struggled the same problem and I think the problem comes from gnome failing to pop up the confirmation dialog. That's why there is nothing in the error logs.
So what I did is changing the window manager from gnome to xfce. That solved the problem.
It would probably work with another manager or there may be a fix for gnome but here is how to install xfce:

yum -y install epel-release #adding epel repository
sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/epel.repo # using it only when needed
yum --enablerepo=epel -y groups install "Xfce" #installing xfce

echo "exec /usr/bin/xfce4-session" >> ~/.xinitrc
startx

If you prefer to install oracle via vnc then skip the last two lines and change the /home/vnc-user/.vnc/xstartup ( vnc-user is oracle at my box ) like this:

#unset DBUS_SESSION_BUS_ADDRESS
#exec /etc/X11/xinit/xinitrc
/usr/bin/xfce4-session

If you don't know how to install vnc server on your CentOS 7 or Fedora box here is a guide: https://www.howtoforge.com/vnc-server-installation-on-centos-7


Oracle SQL Creating Copy of Table Using DBMS_metadata

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1:

create table x.table1 (id number, name varchar2(100), primary key(id));

You want to recreate it as Y.TABLE2, you could do the following:

set serveroutput on
declare
  metadata_handle number;
  transform_handle number;
  ddl_handle number;
  result_array sys.ku$_ddls;
begin
  metadata_handle := dbms_metadata.open('TABLE');
  transform_handle := dbms_metadata.add_transform(metadata_handle, 'MODIFY');
  dbms_metadata.set_remap_param(transform_handle, 'REMAP_NAME', 'TABLE1', 'TABLE2');
  dbms_metadata.set_remap_param(transform_handle, 'REMAP_SCHEMA', 'X', 'Y');

  ddl_handle := dbms_metadata.add_transform(metadata_handle, 'DDL');

  dbms_metadata.set_filter(metadata_handle, 'SCHEMA', 'X');
  dbms_metadata.set_filter(metadata_handle, 'NAME', 'TABLE1');

  loop
    result_array := dbms_metadata.fetch_ddl(metadata_handle);
    exit when result_array is null;
      for i in result_array.first..result_array.last loop
      dbms_output.put_line(result_array(i).ddltext);
    end loop;
  end loop;
  dbms_metadata.close(metadata_handle);
end;
/

Which results in:

CREATE TABLE "Y"."TABLE2"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(100),
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

Obviously you can pass it to execute immediate, or just spool it.


Could database with compat level 100 support columnstore index?

Compat level is there to provide a higher degree of backwards compatibility. We can put this into a few categories:
Language elements that has been removed in a higher version of SQL server can still be available in that version of you run with a lower compat level. There aren't many of those nowadays.
Behavior changes can keep the old behavior with the lower compat level.
New language elements that in a higher version now are keywords/reserved words. I.e., if you have an object named this way ("identifier", formally), you will in this higher version have to [delimit] when you reference that identifier. In order to provide backwards compatibility, in a lower compat level, those new language elements won't be available. Note, however, that there are plenty of new language elements that do not introduce new keywords, and don't have to be blocked in the lower compat level.
Performance. Use a lower compat level, and you are likely to have the same execution plans as if you were running in that version. I.e., no performance surprises when you upgrade the engine version assuming that you keep the old db compat level. Ideally.
There is no need to block new features if allowing them don't introduce backwards compatibility issues. Your columnstore index is such an example.
As for exactly what each compat level do, this is well documented here: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level


How to back out of mass update in SQL Replication

There is no supported way to achieve what you want to do, aside from complete reinitialization. That said, you might be able to achieve a shortcut and retain a supported configuration, but only under direction of a Microsoft support engineer. Attempting to modify the system replication tables without that guidance risks compounding your current problems.

My advice would be to open a case under your support contract, or take the hit of reinitializing and change operating practices to prevent a recurrence of the problem in future.


ORA-00227: corrupt block detected in control file

Move control.dbf to control.dbf.damaged.20140706

connect / as sysdba
show parameter control

Verify here you have had only a single control file and not two or three of them.
If you have had more than one control file, then this instruction is not for you.

STARTUP FORCE NOMOUNT;

CREATE CONTROLFILE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\log_file_1' SIZE 50M,
GROUP 2 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\log_file_2' SIZE 50M
DATAFILE
'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',
'C:\ORACLEXE\ORADATA\XE\UNDO.DBF',
'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF',
'C:\ORACLEXE\ORADATA\XE\USERS.DBF'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN;


I want to add a user/login to role in another database without switching databases

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


Can't enable binary logging ("index not found")

It turns out the problem was caused by apparmor.  I had to add the following two lines to /etc/apparmor.d/usr.sbin.mysqld:

  /var/log/mysqld/ r,
  /var/log/mysqld/* rw,

and then run apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld before restarting MySQL.


Which changes to a table in SSMS cause a DROP/CREATE and can this always be avoided with TSQL?

I doubt Microsoft has anywhere published the logic that is used to determine how SSMS will script a table change. It should be noted that the way SSMS has handled these has evolved over the years - I swear in earlier versions every single change would drop & re-create. Now it definitely tries to make much targeted adjustments.

I can't think of many instances where the only option is to DROP and CREATE. There are many scenarios where it would be the easiest for a human or SSMS to script, but most any situation can be solved by judicious uses of ALTER or dropping and re-creating constraints only. The one absolute exception to this is changing the order of columns - that does require dropping and re-creating the table.

One way to always be sure of what your change is going to do is, in the table designer, once you've made your changes, but before you've saved them, go to the "Table Designer" menu, and select "Generate Change Script." This will tell you exactly what SSMS is going to execute and can allow you to decide before you hit "Save" whether or not that's how you want it done.


How to terminate all DB usage of a PostgreSQL user

pg_cancel_backend takes a process id as its argument.
It doesn't care if that comes from you typing it in, or a from a query.

That makes it possible, albeit quite risky, to combine these two into one:

SELECT pg_cancel_backend( pid )
FROM pg_stat_activity
WHERE usename = 'foo_user'
AND state = 'active' ;

OK, this doesn't give you option to escalate the "cancel" to a "terminate", but it might be enough.

Obviously, inappropriate use of this could cause huge amounts of Trouble.
Use with caution.

A better solution might be to not let them into the database in the first place, but that's a little Draconian and rarely a Real World option (no matter how satisfying it is to "slam the door" on a troublemaker).

The best solution would be to find out why the User is doing what they're doing and improve their process/query to "play" better with everything else that your database is doing.


SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)

By the time that you whip out dbcc page and try to look at the page, it's possibly/probably being used for something else anyway. It is tempdb, after all.

I've been at this since SQL 6.5 and I have only ever seen these sorts of page timeouts caused by slow or misconfigured storage. Every incident I can remember offhand, it's been a SAN. If you have a SAN, you should definitely talk to your SAN guy/gal and find out if they have been seeing unusual load on the system or if they have changed anything lately. I've seen problems with flaky fiber switches and cables, for example, or maybe they moved your data onto slower disks lately.

Beyond that, Have a hard look at your drives, SAN or otherwise. Look at the "Avg. Disk Sec/Read" and  "Avg. Disk Sec/Write" numbers for the drive that the tempdb MDF file is stored on while stuff is happening, preferably while you are getting errors. It's helpful to look at the bytes/second numbers as well, to get a feel for what the drives are being asked to do (a drive with 1000 ms latency numbers that is being asked to push only 1 MB/sec is one thing, a drive with 50 ms latency when pushing at 10 MB/s is another.)

If the drive is overwhelmed, you may need to reduce the I/O going to the drive. Tactics would include moving tempdb files to another device, creating additional tempdb data files on other devices, tuning queries (any unnecessary GROUP BY or DISTINCT?), moving any data files that are also on that storage device to somewhere else, etc.


Is Database Mail worth the risk / a standard practice

While your question would probably be borderline duplicate of this one, I'm not real fond of the answer to the other question as it does not go into to much detail of the security risk.

Database mail is configurable to include who can use the profiles/accounts created. You have the following security measures when you enable Database Mail:

(Reference this answer by Thomas Stringer)

A public profile is only available to users in msdb database
Users of msdb database must also be member of DatabaseMailUserRole in order to execute `sp_send_dbmail

The one thing to consider outside of the above is with your application and the data you are allowing to be sent via email. For example, if you are making a generic function that will allow users to send a report either via Database Mail or your application; you need to ensure there is no sensitive data being sent (especially user data). It all depends if there are any security standards for your company or the industry you are in, if any extra measures need to be put in place. I know for some if a report is being sent it simply has to have disclosure notification on the report that maybe the user acknowledges whether there is sensitive data included.

You may also add in security measures on your email system or Exchange that has policy where any email sent from the SQL Server instance or your application, can only go to internal email. It would not be allowed to send email to an outside domain name.

Is it worth the risk?

I would not consider it a risk at all based on the above information. It is a common tool used for any DBA (e.g. job failure notification, alerting, etc.) and can be a vital part of monitoring. I enable on an instance based on the need for support. Some environments it may not be preferred to setup on EVERY instance in your network (if large) and in that you may put it on an instance utilized for central monitoring.


WITH position unexpected position using Mysql 8.0 Date Generation

Recursive CTE implemented in the MySQL 8+ requires the next syntax:

WITH RECURSIVE tbl (a,b,c) AS
(
  SELECT ...  -- initial portion of rows
  UNION ALL
  SELECT ...  -- recursively added rows
)
SELECT *
  FROM tbl
   ...
;

Refer to the documentation/primers for the proper recursive CTE using.


Using EXCEPT before WITH RECURSIVE in SQLite 3

The WITH goes first in a query. You can use this:

WITH RECURSIVE ct AS (...)
SELECT x
FROM table
EXCEPT
SELECT y
FROM ct ;

For the record, CTEs can be (in standard SQL) inside subqueries but not many DBMS have implemented this syntax:

SELECT x
FROM table
EXCEPT
SELECT y
FROM
  ( WITH RECURSIVE ct AS (...)
    SELECT y
    FROM ct
  ) AS c ;

or this simpler one:

SELECT x
FROM table
EXCEPT
( WITH RECURSIVE ct AS (...)
  SELECT y
  FROM ct
) ;


Order by 1 shortens the result set to one row

Is this a bug in SQL Server?

No.

From PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

  The correct behavior for an aggregate concatenation query is
  undefined.

See also this excellent answer by Martin Smith over at stackoverflow.
nvarchar concatenation / index / nvarchar(max) inexplicable behavior

Sometimes it works and sometimes it does not work and that is dependent on the execution plan. The magic works if the compute scalar doing the concatenation is positioned as the first operator below the select operator.

declare @T table(Name nvarchar(5));
insert into @T values (N'aaaa3'),(N'bbbb2'),(N'cccc1');
declare @S nvarchar(18) = N'';

select @S += ',' + T.Name
from @T as T;

But if you are unlucky you could end up with a different plan. This one has a sort operator between the select and the compute scalar.

select @S += ',' + T.Name
from @T as T
order by reverse(T.Name);

The best way to solve this (in my opinion) is to use for xml path to do the concatenation instead.

select @S  = (
             select ',' + T.Name
             from @T as T
             order by reverse(T.Name)
             for xml path(''), type
             ).value('text()[1]', 'nvarchar(max)');

Some version of SQL Server in the future will have a built in string aggregate function STRING_AGG (Transact-SQL).


Should I stop transaction log backups during a full backup?

As you can see in this table, there is no conflict between Full backup and Log backup. They can occur at same time, and, when log backup completes, log will not be cleaned until full backup (or diff backup) completes.

  Not all administrative tasks are allowed to run concurrently. In the table below, a black circle indicates two operations that cannot run in a database at the same time.

References:

Concurrent Administrative Operations


Update various tables depending on survey answer

I would say this is a pretty reasonable solution.  You might consider adding an "active" flag or something similar so you can deactivate certain rows from your AffectedData table without deleting them.  Also you will need to make sure that any solution you have takes into account the possibility of multiple entries in the metadata table.  Either by putting a primary key on it that restricts them or by using a loop or batch solution when you are creating your dynamic SQL.


Detecting and fixing database corruption on SQL Server mirrored database

It is highly unlikely what you are seeing is corruption.

The far more likely cause of this issue is the "app" is not issuing the DELETE FROM command correctly, or in the way that you think it is.  Perhaps the DELETE is being rolled back for some reason, or perhaps the WHERE statement is preventing rows being deleted that you think should be deleted.  If you can view the code in the app, add the code that is performing (or not performing) the DELETE to your question via the edit link.   If you don't have access to the code, you could use SQL Server Profiler to run a trace so you can "see" the statements your app is issuing to the server to determine for certain what the app is doing.

It's possible there are some application-level data problems preventing the application from performing the delete.  For instance, if there are no primary/foreign key relationships enforcing referential integrity, it's possible a DELETE FROM statement is using a join in the delete that is effectively excluding rows from being deleted as a result of those rows being orphaned.  By way of an example, take a look at this:

USE tempdb;

IF OBJECT_ID(N'dbo.Child', N'U') IS NOT NULL
DROP TABLE dbo.Child;
IF OBJECT_ID(N'dbo.Parent', N'U') IS NOT NULL
DROP TABLE dbo.Parent;

CREATE TABLE dbo.Parent
(
    parent_id int NOT NULL
        CONSTRAINT Parent_pk
        PRIMARY KEY CLUSTERED
    , parent_data varchar(50) NOT NULL
);

CREATE TABLE dbo.Child
(
    child_id int NOT NULL
        CONSTRAINT Child_pk
        PRIMARY KEY CLUSTERED
    , parent_id int NOT NULL
    , child_data varchar(10) NOT NULL
);

INSERT INTO dbo.Parent (parent_id, parent_data)
VALUES (1, 'A')
    , (2, 'B');

INSERT INTO dbo.Child (child_id, parent_id, child_data)
VALUES (1, 1, 'C')
    , (2, 1, 'D')
    , (3, 2, 'E')
    , (4, 2, 'F')
    , (5, 3, 'G')
    , (6, 3, 'H');

SELECT *
FROM dbo.Child;

╔══════════╦═══════════╦════════════╗
║ child_id ║ parent_id ║ child_data ║
╠══════════╬═══════════╬════════════╣
║        1 ║         1 ║ C          ║
║        2 ║         1 ║ D          ║
║        3 ║         2 ║ E          ║
║        4 ║         2 ║ F          ║
║        5 ║         3 ║ G          ║
║        6 ║         3 ║ H          ║
╚══════════╩═══════════╩════════════╝

Now, if we want to delete all rows from dbo.Child, we might use this delete statement that deletes all child rows that have a parent in the parent table:

DELETE FROM dbo.Child
FROM dbo.Child c
INNER JOIN dbo.Parent p ON c.parent_id = p.parent_id;

If we had a properly defined foreign-key in the dbo.Child table, the 5th and 6th rows would never be able to exist, however since we don't have good relational integrity, rows can exist in the child table that have no parent, as you can see from this SELECT statement ran after the above delete runs:

SELECT *
FROM dbo.Child;

╔══════════╦═══════════╦════════════╗
║ child_id ║ parent_id ║ child_data ║
╠══════════╬═══════════╬════════════╣
║        5 ║         3 ║ G          ║
║        6 ║         3 ║ H          ║
╚══════════╩═══════════╩════════════╝

When SQL Server detects corruption in the primary database (the one that is currently on-line and serving clients), it will transfer the page that is corrupted from the mirror database, via Automatic Page Repair, in an attempt to fix the database automatically.  Automatic Page Repair has been available since SQL Server 2005 Service Pack 1, when mirroring officially became a supported option.

Since you've ran DBCC CHECKDB with no corruption reported, I'd say with near 100% certainty that you're not experiencing corruption.


Why not just use:

SELECT id FROM Conversations
INNER JOIN (
    SELECT conversation_id FROM Chats  WHERE Chats.character_id= '#{sender_id}'
) AS sterling
    ON sterling.conversation_id = Conversations.id
INNER JOIN (
    SELECT conversation_id FROM Chats WHERE Chats.character_id = '#{recipient_id}'
) AS archer
    ON archer.conversation_id = Conversations.id;

Since those are inner join, it has to be in both sender and recipient. It will only return conversation which are in both 3 tables.

The count does not seem useful here unless you are trying to get something like people with more than 1 conversation together.

It should also work with this simpler version although it does not check for the existence of conversation_id in Conversations:

SELECT conversation_id
FROM Chats AS sterling
INNER JOIN  Chats AS archer
    ON sterling.conversation_id = archer.conversation_id
WHERE sterling.character_id = '#{recipient_id}'
    AND archer.character_id = '#{sender_id}';

Exit with count:

SELECT cv.conversation_id
FROM (
    SELECT conversation_id
    FROM Chats AS sterling
    INNER JOIN  Chats AS archer
        ON sterling.conversation_id = archer.conversation_id
    WHERE sterling.character_id = '#{recipient_id}'
    GROUP BY conversation_id
    HAVING count(distinct character_id) = 2
) ce
INNER JOIN  Chats AS ch
        ON ce.conversation_id = ch.conversation_id
    WHERE ch.character_id = '#{sender_id}';


sqlplus command not found after installing oracle 12c

export ORACLE_HOME=/home/christian/app/christian/product/12.1.0/dbhome_2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

Alternatively:

. oraenv

And provide orcl as ORACLE_SID.