If anyone's curious, I ended up doing this. It gives me something, but I'm not really convinced that the data is good enough to be better than no data.

-- Set up table
-- Put this somewhere other than TempDB if you want it to persist across restarts
USE TempDB
DROP TABLE Sessions
CREATE TABLE Sessions (last_request_end_time datetime, login_name varchar(100), reads int,
writes int, cpu_time bigint)

-- Insert info about sessions from sys.dm_exec_sessions
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time
FROM sys.dm_exec_sessions

/*
In a loop, every 100ms insert data from sys.dm_exec_sessions where
last_request_end_time is greater than what has already been written
to the sessions table.

This data will be inaccurate in that very short-lived
sessions might never get counted, and long-running sessions will get
activity counted multiple times. As a result, you might get very different results
depending on what the WAITFOR delay is set to.

Optionally run this part as an Agent job.
*/

WHILE (2 > 1)
BEGIN
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time
FROM sys.dm_exec_sessions
WHERE last_request_end_time > (SELECT MAX(last_request_end_time) FROM Sessions)
WAITFOR DELAY '00:00:00.100';
END

-- After exiting the loop (stop Agent job, manually abort, etc) report on results
SELECT login_name,
SUM(reads) AS reads,
SUM(writes) AS writes,
SUM(cpu_time) AS cpu_time,
COUNT (login_name) AS session_count
FROM TempDB.dbo.Sessions
GROUP BY login_name


Performance of Query where time captured from SP statement completed differs by large from RPC completed

Generally speaking rpc_completed includes network time, while sp_statement_completed does not.  This is very likely the case for your query, since it only returns one row.

So one explanation is the extra time is spent sending data over the network to the client.

You could try measuring wait statistics for this query to confirm there are 24 seconds of ASYNC_NETWORK_IO waits that explain the difference.

If the server has SSMS installed, you could remote desktop into it and run the query there, which should have very little network wait time.  In which case you'd see very little difference in the time on those two events for this statement.


In this case, I wanted to restore my data on another database rather than the live one.
First of all you should take all of your old data from the following location on your old server and move them to your new VPS.

old destination would be:
/var/lib/mysql/your_db_name

so I created another database called new_db
 then start to creating the tables by the CREATE TABLE statement that I had from my live database, maybe you can find them in your codes.
by doing these, you will have all your tables but empty ones.
let’s assume that you placed the old data files in the:
/location/to/old_files/

and
you created a new empty database called new_db.
this is time to restoring your old data, please follow next steps for each one of your tables:

discarding new data file from its structure by executing
following command on the mysql  where has access to your new
database new_db:
ALTER TABLE my_table DISCARD TABLESPACE;

coping the old data files to the same table structure but in
the new database location:

CP /location/to/old_files/my_table.ibd /var/lib/mysql/new_db/my_table.ibd

change the ownership of the ids file which recently copied in the
new database should be changed to mysql:mysql:

sudo chown mysql:mysql /var/lib/mysql/new_db/my_table.ibd

finally, you can execute this command where you executed the previous one:

ALTER TABLE my_table IMPORT TABLESPACE;

it works for me.


First of all, you can switch to another database without leaving psql at all, use \c (or \connect) commad.

From psql's help (always available via \? command):

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

So just type \c another_db to switch to "another_db".

Next, you see "[Process completed]" because you start psql via some shortcut your Postgres package (brew? postgres.app?) provided.

You can launch Terminal or iTerm2 (not default app in MacOS, but highly popular substitution for default Terminal), and then run psql inside it. In this case, you will not lose the window after \q command.


If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

DESCRIBE, alias EXPLAIN, estimates the number of rows.  In your case (frequent changing of status and deletion), the number are very likely to be very far off.  Do not trust it.

EXPLAIN gets its information from "statistics" about the distribution of the data in the table.  The statistics are updated at certain times.  Version 5.7 does the update at quite different times than 5.5.  (What version are you running?)

You have INDEX(status)?  Perhaps you should not; it is adding to the problem and not helping the solution in a consistent way.  How often are the status changes?  The DELETEs?  There may be workarounds.

The Optimizer will look at the statistics for status and incorrectly decided that status=0 was too common for using the INDEX, so it did a table scan instead.  On the other hand, the stats said that status=2 was rare, so it did use the index and 'instantly' discovered zero rows.

Alas, running an EXPLAIN, even immediately after running the equivalent SELECT does not necessarily give you the explain plan for that select.  (I suspect this explains the mysterious results you got.  I have almost never seen such happen.)

Your test case is not quite correct -- You have only 2 columns, the secondary key and the PRIMARY KEY, so you got Using index;  This is unlikely to happen in the 'real' table -- don't you have other fields?

Please try one more thing to help get to the root of this.  Rerun the tests with

FLUSH STATUS;
SELECT ... ;    -- (returning emptyset)
SHOW SESSION STATUS LIKE 'Handler%';

This will show whether it actually did an index (or table) scan, and was not just a caching problem, as hinted by @jkavalik .


Is it necessary to ANALYZE a table after an index has been created?

If the index is just on simple columns like in your case, it is not necessary to ANALYZE the table after you create the index.
That is because statistics on the value distribution of the table columns are always collected, no matter if there is an index on the column or not.

However, if you are indexing an expression like upper(some_column) or (CAST(some_column AS date)), you should run ANALYZE after creating the index.
PostgreSQL will then also collect statistics on the value distribution of the indexed expression. This happens automatically whenever autoanalyze runs, but it is a good idea to do it manually right after creating the index so you have good statistics right away.


T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar

This is by design.
When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it goes through to do this is to generate the InfoSet. When it does this, it removes anything that it determines to not be necessary, in your example, whitespace:

The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.

When you select the entire contents of the field (such as when you are converting it to NVARCHAR(MAX) it rebuilds the XML document before returning it. This document may not be an identical copy of the document that you inserted. For example, if you have used self-closing elements, Sql Server may return opening and closing elements instead.
The documentation also continues on to say:

Example: Retaining Exact Copies of XML Data
For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. For example, these could include signed documents, legal documents, or stock transaction orders. You may want to store your documents in a [n]varchar(max) column.

So, if you want to store the exact copy of your document, then NVARCHAR(MAX) or VARCHAR(MAX) is the best option. You can then convert it to XML to query it later on (though this can be costly).
For more information, see the documentation on XML Data Type and Columns (SQL Server) and also Define the Serialization of XML Data which outlines the rules that Sql Server applies when converting XML to a string type.


Number of Worker threads available in PostgreSQL

PostgreSQL does not use threads, it uses coordinated single-thread processes.  It will not use more than max_parallel_workers, system-wide, at one time.  If it would exceed that, the queries will not error out, they simply run with fewer worker processes than they "wanted".

If the number of connections you have trying to run queries at the same time is always greater than your number of CPUs, then you probably might as well turn parallelism off.  OLTP usually are not great candidates for parallel execution anyway.


Can SQL Server 2016 validate JSON on insert?

Add this constraint:

ALTER TABLE [dbo].[Resource]
 ADD CONSTRAINT [RESOURCE_ExtJSON_VALID]
   CHECK ( ISJSON( ExtJSON )> 0 )


How to get syntax and logical ORA in a trace file?

You can trace specific errors like below:
SQL> alter session set events '1722 trace name errorstack level 1';

Session altered.

SQL> SELECT TO_NUMBER('asdf') from dual;
SELECT TO_NUMBER('asdf') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

In the trace file:
*** 2020-10-15T18:56:23.811818+02:00
*** SESSION ID:(18.12475) 2020-10-15T18:56:23.811837+02:00
*** CLIENT ID:() 2020-10-15T18:56:23.811841+02:00
*** SERVICE NAME:(SYS$USERS) 2020-10-15T18:56:23.811845+02:00
*** MODULE NAME:(SQL*Plus) 2020-10-15T18:56:23.811849+02:00
*** ACTION NAME:() 2020-10-15T18:56:23.811853+02:00
*** CLIENT DRIVER:(SQL*PLUS) 2020-10-15T18:56:23.811856+02:00

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffdfe31ce40 placed dbkda.c@296
ORA-01722: invalid number
----- Current SQL Statement for this session (sql_id=bkusfcu38gbjr) -----
SELECT TO_NUMBER('asdf') from dual

To log them, you can use a servererror trigger as shown here:
Is it possible in Oracle to trace SQL statements that result in errors?


Sargability of queries against a view with a case expression

Is it possible to still have my ShipmentId output go through the case
  expression, but also have a sargable query when ShipmentId is used in
  the where clause?

No.  SQL Server doesn't do that for you.

This is where you might use an inline Table-Valued Function, sometimes called a "parameterized view".  EG

CREATE function GetShipments ( @ShipmentId int )
returns table
AS return

    SELECT  CASE
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId,

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
    where  SHIPMENT_ID = @ShipmentID

Or add an extra column to your view.


Big jump in search time for Postgres Index query for results with high selectivity

As I understand your question you are asking why highly selective index scans might be much slower after a certain number of records are returned or after the table reaches a certain size.  As it turns out your query plans provide most of the information needed.  Understanding of course is the first step in trying to figure out how to solve the problem.  It looks to me like your slow query is hitting a much more heavily used db than the fast query.

As I look at your query plans, I see two immediate pieces of bad news.

The first piece of bad news is in the buffer usage. Your rows returned are less than double, but you hit about six times the number of buffer pages.  This is bad news because it means that PostgreSQL, following the index scan, is scanning through at least six times more information to find the rows to retrieve.  Significantly worse, you shift from the rarely used db of mostly read buffers (fast, but few db-specific services) to shared buffers which require more overhead.

The second piece of bad news is that the recheck condition in the slow query weeds out about three times as many records as are returned.  This tells me you need to vacuum and/or reindex this table.

I would recommend vacuum analyze and reindex first, followed if necessary by slightly lowering the shared buffer settings and see if this improves performance.


So at this point in the code:

isnull((processeddate),' ') as 'Null'

The column processeddate is not visible to SQL Server, because you are selecting available fields from the result of the following query:

SELECT EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth
  FROM [dbo].[Invoices] where Inv_Number not in ('1035','1039')

...along with the pivoted columns [1] through [12].

You either need to include ProcessedDate in the above query, or exclude it from the outer SELECT.


How to add a constraint to check if the date entered (column) is today's date

In all MySQL versions

  The CHECK clause is parsed but ignored by all storage engines.

MySQL Reference Manual, CREATE TABLE Syntax

The only way to solve Your problem seems to be a trigger pack checked constraint You need and discarded wrong data insertion. But this solution is not complete - there is a lot of situations when inserting/altering data do not fire the trigger action.


Query 2 tables one with 1 row and the other with multiple rows

You can use the old fashioned PIVOT approach as you need to aggregate two different columns.

WITH Pivoted AS
(
SELECT DOCUMENTID,
       REVISION = MAX(CASE WHEN ID = 'UDF-000021' THEN STRING_VAL END),
       [REVISION DATE] = MAX(CASE WHEN ID = 'UDF-000022' THEN DATE_VAL END),
       NOTES = MAX(CASE WHEN ID = 'UDF-000023' THEN STRING_VAL END),
       [MARKUP DATE] = MAX(CASE WHEN ID = 'UDF-000024' THEN DATE_VAL END),
       IM = MAX(CASE WHEN ID = 'UDF-000025' THEN STRING_VAL END)
FROM UserDefinedFields
GROUP BY DOCUMENTID
)
SELECT p.ID,
       p.Description,
       pv.REVISION,
       pv.[REVISION DATE],
       pv.NOTES,
       pv.[MARKUP DATE],
       pv.IM
FROM   Part p
       LEFT JOIN Pivoted pv
         ON p.ID = pv.DOCUMENTID

Online Demo


Percona Xtradb Cluster : How to speed up insert?

By its Nature in a Galera Cluster a DML statement is expected to run a bit slower than a DML statement on a normal MySQL node because the response time of the DML statements includes not only the commit time on the local node but also sending the the write-set (ws) to the Group and receiving back the GTID of the ws.

So the total response time is: query time + 2 x round-trip to the group.

A simple insert will run possibly within 1 ms. And the round-trip to the group is possibly about 400 - 600 us. So 5 instead of 3 minutes I am not surprised to see...

Now, what can we improve?

a) Reduce query time:
Write performance is influenced by: innodb_buffer_pool_size (big enough to cache all data in memory), innodb_log_file_size (as bigger as better) and innodb_flush_log_at_trx_commit (0 or 2 are faster than 1). With your setting above you have not followed the Codership recommendation (innodb_flush_log_at_trx_commit=0).

b) Reduce number of round trips. When you batch your DML statements into transactions you have bigger but less write-sets which should cause less round trips. So your time spent in the network should become smaller.

c) Make your network faster. Use smallest possible network latency: dedicated network. 1 or 10 Gbit. Not much hardware in between (firewall, switches, routers, etc).

d) Parallelize your inserts?

Regards,
Oli

PS: There are still some places free in our Galera Cluster trainings in 2 weeks!


SQL Group by MAX then get the MIN of all the groups

Depending on the DBMS, you can use LIMIT 1 or TOP 1 or FETCH FIRST 1 ROWS ONLY - in combination with ORDER BY MaxLogOut:

-- standard SQL, Postgres, SQL Server 2012+
SELECT
    MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut
    OFFSET 0 ROWS
    FETCH FIRST 1 ROWS ONLY ;

-- SQL Server
SELECT TOP (1)
    MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut ;

-- MySQL
SELECT
    MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut
    LIMIT 1 ;


How to read all metadata without select permissions to actual data?

The possible solution depends on exactly which objects you are interested in.

Tables

The way I see is half suggested by the documentation:

  Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

So, you can set your information_schema_reader role as owner of everything, revoking the privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER).  The backside is:

  The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.

So, this is possibly not what you want - the role cannot see the data but can drop the table.

On the other hand, in the above privilege list there is one which seems really innocent (and can be used for checking data in tables only in a very cumbersome way): REFERENCES.  You can try to grant this to an otherwise powerless user.

Other objects

Unfortunately, not every object type has such 'weak' privileges.  A function, for example, is either executable or not by a user - there is no other privilege that can be granted.  Depending on your needs (the exact list of types you want to 'hide'), this may be acceptable.


PostgreSQL WAL recovery not working due to missing WAL

I've been kicking PostgreSQL backup around the last couple of weeks, because of a new job, and I find the implementation of PostgreSQL's backup quite hard to grasp.

What I have learned however, is that the a missing WAL file is the worst situation you can have. A missing WAL file will not allow you to continue your restore past the missing file, because the PostgreSQL database relies on the WAL file to be ACID.

You could try to retrieve some of the information from the non-restored WAL files, but you wouldn't have any guarantee, that the resulting database/instance is consistent.


Best way to find out read/write distribution in MySQL?

An application being read/write heavy is subject to interpretation based on the following:

How many SELECTs are launched : Com_select
How many UPDATEs, DELETEs, INSERTs are launched

Com_insert
Com_delete
Com_delete_multi
Com_update
Com_update_multi

Prepared Statements (could be a bottleneck if prepared statements are not parameterized)

Com_stmt_prepare
Com_stmt_execute
Com_stmt_fetch
Com_stmt_send_long_data
Com_stmt_reset
Com_stmt_close

How much client byte traffic mysqld sees

Bytes_received
Bytes_sent

How many bytes have been read

Innodb_data_read
Innodb_data_reads

How many bytes have been written

Innodb_data_writes
Innodb_data_written

Perhaps a combination of these

These things are meant to measure SQL command counts and the amount of data passing here and there. There are other metrics within the InnoDB storage engine that should be monitored and tuned so that any write heavy behavior is improved.

Rather than name those features of InnoDB, please go to the  Server Status Variables page and look for any status variable mentioning the Redo Log Writes and Buffer Pool read/writes.


As pointed above by Jeremy, permissions can almost always be found on the corresponding reference document within Books Online for SQL Server.  On these reference documents, there is typically a section titled Permissions (typically towards the bottom of the document) which contains your desired data.
CREATE LOGIN (copied directly from the reference)

Requires ALTER ANY LOGIN or ALTER LOGIN permission on the server.
If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server.

CREATE USER (copied directly from the reference)

Requires ALTER ANY USER permission on the database.


How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings

If you have a lot of CXPACKET waits it is possible that reducing setting the MaxDOP is appropriate and will help. You should not change the cost threshold for parallelism setting assuming you have a NUMA machine (which you most likely do).

MaxDOP: https://support.microsoft.com/en-us/kb/2806535

...and I read somewhere that cost threshold needn't be changed but am still trying to track that link down :(

In any case, focus on MaxDOP.


Does SSMS v17 allow user input in Python scripts?

I cannot see any possible way for this to work given that the script is being submitted through a back-end service where there is no user-context (i.e. display, etc). SSMS is not executing anything. It is merely submitting those statements to SQL Server which handles the execution (even if SQL Server then passes it off to something else).

The only potential possibility of getting around this, and it is would have been a long-shot and not likely to be a practical work-around even if it does did work, is to run this statement against SQL Server Express LocalDB. Because LocalDB runs in user mode (not an NT service), it does have access to the console / display. I know this because I have been able to pop-up a Windows dialog / confirm box via SQLCLR when using LocalDB :-D.

HOWEVER, I just tried and couldn't even get the print to work. So, I checked the supported features list ( Editions and supported features of SQL Server 2017 ) and neither R nor Python are available in Express Edition, and hence not in LocalDB. :-(


Adding LIMIT changes PostgreSQL plan from good to a bad one

Tell PostgreSQL not to use the index by using

ORDER BY contacts__aggregated_1.value + 0 ASC

The problem is that PostgreSQL thinks it will be fastest if it scans contacts__aggregated in the ORDER BY order using the index, uses a nested loop to add rows from the other tables and filters out values that don't satisfy the conditions.

This is often a good strategy if not many values get filtered out.

It produces a bad plan, however, if

there are fewer rows that satisfy the filter condition than PostgreSQL estimates
the rows that satisfy the filter condition all have high value

One of these things must have happend. Look for high Rows removed by filter in the EXPLAIN (ANALYZE, BUFFERS) output.


Oracle - Can't login with "/ as sysdba", but can with "sys as sysdba"

Thanks to @BalazsPapp who pointed this out in the comment to my question.

To use conn / as sysdba, I had to be logged in as the user oracle instead of root.

Switching to the oracle user and attempting to login again solved the problem.


What are the pros and cons of having more data-bearing members in a MongoDB replica set?

There are a few key aspects to fault tolerance in a replica set:

write availability: a majority of voting replica set members is required to maintain or elect a primary
data redundancy: writes should be acknowledged by multiple members of a replica (ideally a majority, to avoid rollbacks).

A three member Primary-Secondary-Arbiter (PSA) replica set supports the first aspect: if any single member of the replica set is unavailable, a primary can still be maintained or elected.

However, an arbiter does not support the second aspect of fault tolerance since it does not store any data.

When a PSA configuration is degraded to P_A (one secondary unavailable), there are some significant operational consequences:

Until you have a second data-bearing member online, data redundancy is compromised and writes can only be acknowledged by the current primary.
You no longer have active replication. If your secondary is offline for too long, it may fall off the oplog and need to be re-synced.
Applications and internal processes can no longer use a majority write concern. If you haven't planned for this, writes requesting majority acknowledgement may block until a majority of data bearing members are available or a timeout is reached (if set for the write command). If your replica set is part of a sharded cluster, this can also prevent successful chunk migrations between shards.
Applications and internal processes will not see the newest data on the primary using a majority read concern. Some features (for example, change streams in MongoDB 3.6+) rely on reading majority committed data to avoid the chance that changes may be rolled back. If the majority commit point cannot be advanced, there will also be increased pressure on the WiredTiger cache.

The above consequences can be avoided by having a Primary-Secondary-Secondary (PSS) configuration.

It is worth keeping in mind that fault tolerance doesn't only apply to failure modes. There are normal maintenance tasks such as software or hardware upgrades that may require a member to be briefly unavailable.

  What is a situation all members having a copy of the data makes sense?

If you are running a three member replica set in production with MongoDB 3.6 or newer (or any version of MongoDB where this replica set is backing a shard): always.

If you are running an older version of MongoDB in production: always, unless you are prepared to accept the above operational consequences.


Efficient predictable ordering in PostgreSQL

Do you have INDEX(timestamp DESC, id ASC)?

Or, try ORDER BY timestamp DESC, id DESC .


(Link accessible only by registration to Oracle Support):

Bug 21266085 - ORA-600 [kksgaItLvl_Setup: asc] from a query against an interval partitioned table (Doc ID 21266085.8)

The fix for 21266085 is first included in

12.2.0.1 (Base Release)
12.1.0.2.161018 (Oct 2016) Database Patch Set Update (DB PSU)
12.1.0.2.160719 (Jul 2016) Database Proactive Bundle Patch
11.2.0.4.160719 Exadata Database Bundle Patch (Jul 2016)
12.1.0.2.160419 (Apr 2016) Bundle Patch for Windows Platforms


Get the most recently completed step for each SQL job

Unfortunately because there can be ties (the second-to-last step can take 0 seconds and therefore finish at "exactly" the same time as the last step), this is really not pretty in SQL Server 2000. Rather than calculate all of the datetime stuff multiple times I think the easiest way is to stuff it into a #temp table. You may consider creating a permanent view with the datetime conversion.

SELECT job_id, step_name, run_date, run_time, step_id,
    run_datetime = CONVERT(DATETIME, RTRIM(run_date) + ' ' +
    STUFF(STUFF(RIGHT('00000' + RTRIM(run_time), 6),3,0,':'),6,0,':'))
  INTO #t
      FROM msdb.dbo.sysjobhistory;

SELECT j.job_id, j.name, t.step_name, t.run_date, t.run_time,
       t.step_id, t.run_datetime
FROM #t AS t
INNER JOIN
    (
      SELECT job_id, run_datetime = MAX(run_datetime)
      FROM #t GROUP BY job_id
    ) AS m
    ON m.job_id = t.job_id
    AND m.run_datetime = t.run_datetime
INNER JOIN
    (
      SELECT job_id, run_datetime, step_id = MAX(step_id)
      FROM #t GROUP BY job_id, run_datetime
    ) AS s
    ON s.job_id = m.job_id
    AND s.run_datetime = m.run_datetime
    AND s.step_id = t.step_id
INNER JOIN
    msdb.dbo.sysjobs AS j
    ON t.job_id = j.job_id
    AND m.job_id = j.job_id
    AND s.job_id = j.job_id;

DROP TABLE #t;


Why does a JSON element return text when it's not text?

It isn't currently possible to access the internal JSON types. The quoted documents above mention only how they're stored. There is a pseudo-type in PostgreSQL anyelement,  but you can not return that type. Functions are polymorphic in that they accept different types, but they must return a specified type.

The operator could be overloaded for different types, but it isn't currently that way. Currently ->> is defined as

Operator    Right Operand Type  Description
->>         text                Get JSON object field as text

This means no matter how the type is stored, it'll have to go through text to get access to it. All of the jsonb operators return jsonb or text.

Consider the ambiguity even if the type was overloaded, how would this be processed.

SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'),
  ('{"a":true}'::jsonb, 'true'::jsonb)
) AS t(x1,x2);

If that makes sense.. then what does this do..

SELECT sum(x1->>'a')
FROM ( VALUES
  ('{"a":5}'::jsonb, '5'),
  ('{"a":true}'::jsonb, 'true'::jsonb)
) AS t(x1,x2);

While overloading ->> may make the system more efficient, it would also make it far more complex.


There should be more efficient methods if you are doing this for a lot of data, but one way to do this could be by using LEFT(), RIGHT() and PATINDEX()

DECLARE @string varchar(255) = '~~abc~~';

SELECT RIGHT(@string,LEN(@String)-PATINDEX('%[^~]%',@string)+1); -- Remove leading ~
SELECT LEFT(@string,LEN(@String)-PATINDEX('%[^~]%',REVERSE(@string))+1);-- Remove trailing ~

Results

(No column name)
abc~~

(No column name)
~~abc


MySQL InnoDB cluster relies on Group Replication, which provides the mechanism to replicate data within InnoDB clusters with built-in failover, and not on the asynchronous replication. However, whenever you join an instance to a cluster the distributed recovery process kicks-in, and this process uses the asynchronous master-slave replication until the instance finishes joining the group.

As stated on the manual: "Group Replication distributed recovery can be summarized as the process through which a server gets missing transactions from the group so that it can then join the group having processed the same set of transactions as the other group members. During distributed recovery, the server joining the group buffers any transactions and membership events that happen while the server joining the group is receiving the transactions required from the group. Once the server joining the group has received all of the group's transactions, it applies the transactions that were buffered during the recovery process. At the end of this process the server then joins the group as an online member.".

Concluding, as soon as the distributed recovery process is complete you can (if you really need) delete the bin logs on the primary safely.

For further information, please take a look a the InnoDB cluster userguide.

Cheers,

Miguel


Mahout with HBase is probably a good option for very large datasets.

It is designed to run on many cheap commodity servers.

Also, monetDB is an open-source column-wise database which is very fast for data warehouses.


Assuming there are no LOB fields or other specialties:

select sum(blocks) as blocks, count(*) as extents
from dba_extents
where owner = '&table_owner' and segment_name = '&table_name';


Enforcing that a user should have one or more email addresses

Add a column to the users table; email_primary.  Prevent the new column from accepting NULL values.

When you want to authenticate a user, simply use a UNION query, similar to:

SELECT id, email_primary
FROM users
UNION ALL
SELECT user, email
FROM email_addresses;


clustered and covering index ignored on delete statement. Table scan occurs

The optimizer may find a scan more appropriate based on statistics on the duplicate index instead of statistics on the PK. You didn't define the duplicate index as UNIQUE, so getting the "good" or "bad" plan could be just a matter of which index metadata is used by the optimizer to produce the plan.
Very hard to tell without the actual execution plan, though.


How to get psql to prompt for a password with the superuser postgres?

why when I log as a normal user it asks for a password and doesn't when i log as superuser?

  it just doesn't seem safe at all.

The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials.  Without these, "logging in as postgres, with or without a password" simply won't be a option for them.

Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!


I've managed to simplify it a bit:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT transdate, transamount AS amount, CategID, SubCategID
    FROM checkingaccount_v1 AS "transaction"

    UNION ALL

    SELECT transdate, splittransamount AS amount, splittransaction.CategID, splittransaction.SubCategID
    FROM splittransactions_v1 AS splittransaction
    JOIN checkingaccount_v1 AS "transaction" USING (TransID)
)
JOIN Category_v1 AS category USING (CategID)
JOIN SubCategory_V1 AS subcategory USING (SubCategID)
WHERE transdate BETWEEN date('2012-08-11') AND date('now')
    AND categname = 'Food'
    AND subcategname = 'Groceries'


What is the correct way to enforce unique "IS-A" relationship?

Relation Application that refer to the Person and Position relations should have unique personID attribute and should have the single attribute positionID that can have one and only one value at once.


What is the effect on primary keys when moving a CLUSTERED index to a new filegroup?

No.

From an "on disk" organisation perspective the fact it is a constraint doesn't matter. That is, the physical and logical table layouts are separate

And PK when not clustered is just a unique non-clustered index with some rules


Msg 15404, Level 16, State 10, Procedure xp_logininfo - missing permissions

after checking the error log

xp_readerrorlog 0, 1, "error" , NULL, NULL, NULL, N'desc'

finding no errors related to the above

then checking the error message:

BEGIN TRY
EXEC xp_logininfo 'gplondon';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
          ,ERROR_MESSAGE()
END CATCH;

  15404
  Could not obtain information about Windows NT group/user
  'gplondon',  error code 0xffff0002.

investigate:
15404 with Active Directory

with a quick select on sys.server_principals

select * from sys.server_principals
where name = 'gplondon'

I found something obvious: gplondon does not exist in the active directory, because it is a sql login

from the remarks on the documentation of xp_logininfo

  If account_name is a valid Windows user or group that is not
  associated with a SQL Server login, an empty result set is returned.
  If account_name cannot be identified as a valid Windows user or group,
  an error message is returned.


Remove last character in selected column?

"The first shall be last, and the last shall be first"

REVERSE(STUFF ( REVERSE(lastname), 1, 1, ''))


How to improve performance of SP with many optional filtering parameters?

Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it.

Since you will be sending many varying queries to the server, it is best used in combination with the Optimize for Ad Hoc Workloads server setting. This prevents any plan from being fully cached and taking up valuable memory until the same query text has been submitted twice. And if you are seeing wide deltas in performance due to parameter sniffing, you may consider adding OPTION (RECOMPILE) to the query in all cases anyway (or at least in the cases that end up with huge plan skews). For example, you probably won't see any skew when the parameter is the PK and the query ends up being an exact match using a seek. But you might see greater skew when a datetime range is being used, or an exact match to a column with its own asymmetrical skew, and particularly when stats are not being religiously maintained (or no indexes support those specific queries).


How can I enable distributed transactions for a linked server?

In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru.

When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft.

  XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.

In my case, for the Local DTC Properties, I only need these:


Limit root (and any other SUPER-privileged user) to

GRANT ... TO root@localhost ...

That prevents access from other servers.  You should hand out SUPER to very few people, and they should be aware of their responsibility.  The application should not have SUPER.

Limit application logins to the one database it uses:

GRANT ... ON dbname.* ...

That way, someone who hacks into the application code can't get past dbname.  This can be further refined via either of these:

GRANT SELECT ON dname.* ...    -- "read only"
GRANT ... ON dname.tblname ... -- "just one table"

The readonly may also need 'safe' things like

GRANT SELECT, CREATE TEMPORARY TABLE ON dname.* ...    -- "read only"

As you say, there is no absolute security.  My point here is there you can do a few things to slow hackers down.  (Same goes for honest people goofing.)

In rare cases, you may need the application to do something available only to root.  this can be done via a "Stored Procedure" that has SECURITY DEFINER (and root defines it).  That will expose only what the SP does, which might, for example, be one particular action on one particular table.


How to prevent read replica restarts during high replication lag

I think this is what they would call "working as designed". It is stated in the documentation for Aurora MySQL (emphasis mine):

  The tradeoff with having multiple Aurora Replicas is that replicas become unavailable for brief periods when the underlying database instances are restarted. These restarts can happen during maintenance operations, or when a replica begins to lag too far behind the master. Restarting a replica interrupts existing connections to the corresponding database instance.

The same I expect holds true for Aurora PostgreSQL, because the replication implementation is likely very similar, if not identical.

The reason for this behaviour I believe is explained by the way the changes are propagated from the write instance to the read replicas: the redo records are sent, and the replicas are expected to apply them to the appropriate locally cached pages in the right sequence -- see slide 27 of this presentation.

While the cache update processing should be fast, it can still overwhelm the replica, particularly if it's running on a less capable AWS instance. Once the replica loses track of what cache pages are stale and what aren't, it has no other choice than to start from scratch. I presume the original solution designers elected to use the existing instance startup process for that, instead of developing a new cache invalidation and reload mechanism, especially given that the restart can happen relatively quickly.

In other words, the answer to is there a way to prevent the read replica from restarting seems to be "no".

As to are there any recommended tweaks for reducing replication lag, since the lag depends entirely on the replica's CPU and memory capacity (as no I/O is involved), try scaling up your replica, permanently or at least during these heavy updates.

If you have control over the client application code, you could modify it to handle broken connections more gracefully and retry when needed. If you don't, you could try setting up a proxy (e.g. pgpool) between your clients and the read replica, it might relieve the pain to some extent by proactively testing and reëstablishing connections.

If you find neither of these suggestions workable, you can also contact the AWS support and see if they have any better ideas.


MySQL: summary query for date activity by last 7 days, week and all year totals

SELECT * FROM
(
    Status,Date, Count(*) RowCount FROM Customer
    WHERE Date > (DATE(NOW()) - INTERVAL 7 DAY)
    GROUP BY Status,Date
    UNION
    Status,WEEK(Date) WK,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,WK
    UNION
    Status,'YearToDate' YTD,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,YTD
) A ORDER BY Status,Date;


Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans?

Anyone know why this task is costed at 0.172434 in the first plan but 3.01702 in the second?

Generally speaking, an inner side seek below a nested loops join is costed assuming a random I/O pattern. There is a simple replacement-based reduction for subsequent accesses, accounting for the chance that the required page has already been brought into memory by a previous iteration. This basic assessment produces the standard (higher) cost.

There is another costing input, Smart Seek Costing, about which little detail is known. My guess (and that is all it is at this stage) is that SSC attempts to assess inner side seek I/O cost in more detail, perhaps by considering local ordering and/or the range of values to fetch. Who knows.

For example, the first seeking operation brings in not just the requested row, but all rows on that page (in index order). Given the overall access pattern, fetching the 1000 rows in 1000 seeks requires only 2 physical reads, even with read-ahead and prefetching disabled. From that perspective, the default I/O costing represents a significant overestimate, and the SSC-adjusted cost is closer to reality.

It seems reasonable to expect that SSC would be most effective where the loop drives an index seek more or less directly, and the join outer reference is the basis of the seeking operation. From what I can tell, SSC is always attempted for suitable physical operations, but most often produces no downward adjustment when the seek is separated from the join by other operations. Simple filters are one exception to this, perhaps because SQL Server can often push these into the data access operator. In any case, the optimizer has pretty deep support for selections.

It is unfortunate that the Compute Scalar for the subquery outer projections seems to interfere with SSC here. Compute Scalars are usually relocated above the join, but these ones have to stay where they are. Even so, most normal Compute Scalars are pretty transparent to optimization, so this is a bit surprising.

Regardless, when the physical operation PhyOp_Range is produced from a simple selection on an index SelIdxToRng, SSC is effective. When the more complex SelToIdxStrategy (selection on a table to an index strategy) is employed, the resulting PhyOp_Range runs SSC but results in no reduction. Again, it seems that simpler, more direct operations work best with SSC.

I wish I could tell you exactly what SSC does, and show the exact calculations, but I don't know those details. If you want to explore the limited trace output available for yourself, you can employ undocumented trace flag 2398. An example output is:

Smart seek costing (7.1) :: 1.34078e+154 , 0.001

That example relates to memo group 7, alternative 1, showing a cost upper bound, and a factor of 0.001. To see cleaner factors, be sure to rebuild the tables without parallelism so the pages are as dense as possible. Without doing that, the factor is more like 0.000821 for your example Target table. There are some fairly obvious relationships there, of course.

SSC can also be disabled with undocumented trace flag 2399. With that flag active, both costs are the higher value.


Mysql max connections and event scheduler

the running events are not part of the connections limit. Then, what is the limit? Is there one?

Connection limit means outer connections, event scheduler is not counted. But there is a lot of another limited resources - limited by some settings or by some physical conditions. And it is too problematic to find what resource limits the server.

  there is no direct limitation of the number of currently running events?

Yes, such limitation not exists. MySQL treates an event as a trigger which fires on a timer event - and there is no limitation on the number of triggers in the system.

In general the event procedure must check does its previous instance have already finished its work... by using some flag field/value in a service table, for example, or by some another mark.


Installing Patches on MS SQL Server Tips

Continuing to troubleshoot I did a search for sql repair perf-mssql13.mssqlserver-sqlagtctr.dll being used by another process and found the following article: Knowledgebase article: File in use by another process
The repair continued after I applied the same advice to the file in question.
When repair completed I was not able to start the agent service. I did a search for the following from the "Event Viewer": failed to initialize sql agent log (reason access is denied) and found the following article: MSSQLTIPS: SQL Server Agent Service Won't Start Due to Access Denied
What I found out: when SQL is installed, it places a security lockdown on the log directory. It changes owners and restricts access to only the agent service account. The DBA that installed the system used default settings to install SQL Server, and then changed the service accounts to domain accounts. In this case, I found it curious the Engine Service account had access to the directory, but not the Agent Log files.
Solution: Check the permissions on the Log Directory and look for errors regarding the files.

Rename the file from the error during repair.
Added the Agent Service Account permission to the log in question and log directory.


Simplifying/Improving inner join (self joining a table)

When you need to retrieve rather many attributes from an EAV structure, you can try pivoting it with grouping & conditional aggregation as an alternative to multiple self-joins:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
GROUP BY
  PersonID
;

That avoids multiple table scans and, as a bonus, makes your query shorter. But how much the former will affect the performance can ultimately be determined only by testing.

Still, a minor (or not – depends on the total number of attributes) improvement to the above could be to reduce the underlying dataset to just the attributes you are actually retrieving, like this:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
  AND QuestionID IN ('year', 'education', 'country', 'age')
GROUP BY
  PersonID
;

Logically, the above is still not completely equivalent to the series of inner joins because the latter would give you only entries that have all specified attributes. The method in this answer can return entries that do not have some of the attributes requested. If you would like to exclude those, add a HAVING filter that makes sure the number of attributes matches the WHERE filter:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
  AND QuestionID IN ('year', 'education', 'country', 'age')
GROUP BY
  PersonID
HAVING
  COUNT(*) = 4
;

Your query also has a filter on an attribute that in this case is an aggregated column. Such filters would need to be implemented in the HAVING clause. For your example, the filter on age would look like this:

SELECT
  PersonID,
  MAX(CASE QuestionID WHEN 'year'      THEN answer END) AS year,
  MAX(CASE QuestionID WHEN 'education' THEN answer END) AS education,
  MAX(CASE QuestionID WHEN 'country'   THEN answer END) AS country,
  MAX(CASE QuestionID WHEN 'age'       THEN answer END) AS age
FROM
  answer
WHERE
  DatasetID = 'test'
  AND QuestionID IN ('year', 'education', 'country', 'age')
GROUP BY
  PersonID
HAVING
  COUNT(*) = 4
  AND CAST(MAX(CASE questionID WHEN 'age' THEN answer END) AS numeric) > 25
;

Again, you will need to test the final query in your environment to see how much, if at all, these additional modifications affect the performance.


SQL Profiler - Scripting a trace with HostName filter

When the script is generated in Profiler 2005, the default value saved for the logical  operator is 1='OR', so the trace will capture more information than we initially intended.

exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME1'    -- OR
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3'
set @bigintfilter = 10 -- reads >= 10
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'DB'          -- OR

In Profiler >=2008, the script has 0(AND) for the first occurrence of the column being filtered.

exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HOSTNAME1'    -- AND
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3'
exec sp_trace_setfilter @TraceID, 35,0, 6, N'DB'           -- AND
set @bigintfilter = 10 -- reads
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND

This looks like a bug in Profiler 2005.


Which of the following pieces of SQL is most efficient

You could use DATETIMEFROMPARTS (Transact-SQL)

select datetimefromparts(
                        datepart(year, getdate()),
                        datepart(month, getdate()),
                        datepart(day, getdate()),
                        datepart(hour, @testTime),
                        datepart(minute, @testTime),
                        datepart(second, @testTime),
                        datepart(millisecond, @testTime)
                        )

Update:

The first two of your queries.

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

require that the database compatibility level is SQL Server 2008(100). If they are executed in compatibility level (110) or (120) you will get

  Msg 402, Level 16, State 1, Line 3 The data types datetime and time
  are incompatible in the add operator.

so you should not consider those at all.

The last query

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

works just fine.

I did a test over 1000000 rows in SQL Server 2014 and the datetimefromparts version took 650 ms and dateadd/datediff took 350 ms.


Use the GREATEST() function on all the columns

SELECT
GREATEST
(bangla_1st,bangla_2nd,english_1st,english_2nd,math,social_science,religious,science)
HighestValue
FROM class6A
WHERE roll = 4;

If any of the columns are NULL, then make the NULL the value 0

SELECT
GREATEST
(
    IFNULL(bangla_1st,0),
    IFNULL(bangla_2nd,0),
    IFNULL(english_1st,0),
    IFNULL(english_2nd,0),
    IFNULL(math,0),
    IFNULL(social_science,0),
    IFNULL(religious,0),
    IFNULL(science,0)
) HighestValue
FROM class6A
WHERE roll = 4;

GIVE IT A TRY !!!

In the future, you may add another subject to or remove a subject from the student record.

Here is another method using Dynamic SQL that figures out the subjects present in the table

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('IFNULL(',column_name,',0)')) INTO @grades
FROM information_schema.columns
WHERE table_schema=DATABASE() AND table_name='class6A'
AND column_name NOT IN ('roll','student_name');
SET @sql = CONCAT('SELECT GREATEST(',@grades,') HighestValue FROM class6A WHERE roll=4');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


Using NULL values for foreign key with normalization

Many designers use a NULL  in a foreign key when there is an optional relationship, and this particular row is not participating.
There's nothing terribly wrong with this.  When a join is done on this FK and the corresponding PK,  the rows with NULL will just drop out.
Other designers avoid this like the plague.


Have full SQL Server restore but losing 1 day of data

First things first, before you restore anything - back up your log with no_truncate.

If your database is offline, then take a copy of your LDF file first, then bring your database online, ignoring any errors, and then do your log backup.

This log backup is then the last one in your recovery process, which involves a full database backup which doesn't have corruption, and all the log backups since.

But also, consider what kind of corruption it is. If it's in a non-clustered index only, then you could drop and recreate the index and remove your corruption that way.


Get the matching condition in the SELECT list

Possible realization. Fulltext search replaced with common LIKE substring search.

Source table:

CREATE TABLE test (val VARCHAR(16));

INSERT INTO test VALUES
('1 chaining'),
('2 fullfill'),
('3 full chain'),
('4 no pattern');

Patterns table (can be temporary or table-type variable):

CREATE TABLE patterns (pattern VARCHAR(8));

INSERT INTO patterns VALUES
('%chain%'),
('%full%');

The query:

SELECT test.val, STUFF( ( SELECT ',' + pattern
                          FROM ( SELECT test.val, patterns.pattern
                                 FROM patterns
                                 WHERE test.val LIKE patterns.pattern
                               ) tmp
                          FOR XML PATH('')
                        ), 1, 1,''
                      ) matched_patterns
FROM test;

Query result:

    val      | matched_patterns
------------ | ----------------
1 chaining   | %chain%
2 fullfill   | %full%
3 full chain | %chain%,%full%
4 no pattern |

fiddle


reuse of temp table getting more time to execute

I would add a second column to the clustered index statement, make it be

CREATE CLUSTERED INDEX IX_Temp_OBJECT_CD ON #Temp (OBJECT_CD, CD)

That may help, depending on how many CD's there are to OBJECT_CD's.


What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?

What is the relation between MySQL pages and .frm & .ibd files? Is .frm files consists of many pages coming together?

.frm used to contain metadata about tables- that means, the structure (think of the output of SHOW CREATE TABLE- columns and data types) in its own format. Now a days (Since MySQL 8.0) that data is stored on InnoDB tables on the mysql database to allow transactional schema changes. .ibd contained mostly data (not that innodb also had its own internal data dictionary, supporting MySQL one, but different), organized in namespaces>extents>pages, and inside pages, records "rows".

There is other stuff (structures) related to InnoDB for it to work, but the main take away is that there are 2 different planes, the SQL layer, which takes care of connection handling, query parsing and optimization for execution, and the Engine layer (InnoDB, MyISAM, MyRocks, ...) which decides how data is actually stored. Both layers communicate with common API called Handler API, which contains very basic row-level actions like "Search the first record in Primary Key order" or "get the following record".

.frms were an SQL-level functionality (so found for every engine), .ibds were an InnoDB-only structure. Because InnoDB also had its own internal dictionary, aside from the MySQL one, if you deleted a .frm improperly (from the filesystem), InnoDB got confused as the table was still there from its point of view.

  If so then does the .frm files have any type of info on the pages that are inside them? Like the location of each page and stuff like that?

No, the MySQL data dictionary, be it on .frm or in its new form only contains basic SQL-level information (what is the table definition, which columns are there, which types, etc.). InnoDB information is stored on InnoDB related files- ibdata1, .ibds, transactiona logs. When InnoDB engine receives, through the Handler API the need to read a particular row, it does that independently of the SQL later- data may be on cache (buffer pool, or it may be on disk only, or it may be on disk but not available because it is hidden for the given session). To find a particular page, it implements a series of memory and disk structures (headers on files, tree-like structures, lookup tables, hashes that will let InnoDB access the right page). This is very little space to give an proper overview of InnoDB internals, but think of it like a very complex set of pointers and references that allow accessing quickly data while maintaining consistency to disk and access control.

  Where can i read more about this?

I already shared with you the InnoDB: A journey to the core which describes more in detail how it works. Aside from that, probably the best way is the source code itself- MySQL source code is available and you can check the details of how InnoDB implements its own data structures. This is not simple- and almost impossible to summarize here. For a very simplistic approach, each table creates a B+tree structure organized in 16K pages ordered by the primary key (there is always a primary key, even if one has not been explicitly created). Other indexes are implemented as separate tree structures that record the value of the PK of the row they index. If you just want a simplified approach about the logical view of this, without the technical details, you can read my slides on Query Optimization for MySQL/InnoDB.

  And what about tablespaces? Is every .frm file a tablespace?

No, as we said, .frm is a MySQL table structure. However, InnoDB usually creates a different tablespace per table (there are exceptions, like partitioned tables), and each tablespace normally is on a separate file (.ibd). However, this is not necessarily true, there could be several tables on the tablespace0 (it's complicated :-)).


SELECT SERVERPROPERTY('ProductUpdateLevel') SHOWING NULL

As per https://msdn.microsoft.com/en-us/library/ms174396.aspx the property ProductUpdateLevel

  Applies to: SQL Server 2012 through current version in updates beginning in late 2015.

SQL Server 2012 SP1 CU7 is from November 2013, and so not sufficiently current.

There is no SP1 cumulative update that would support pulling that information. You would need to move to Service Pack 2 with CU9 at the least.

As a side note, SQL Server 2012 SP1 was no longer supported as of July 2015. Service Pack 2 will stop being supported in January of 2017 (as detailed in the Microsoft Lifecycle Policy). You should look to upgrade to Service Pack 3 as soon as possible to remain in support.


TempDB uses a lot of space in buffer pool

That looks like a data page (page type=1) from a table variable or temp table (negative object id of -1948083318) in the leaf of a clustered index (indexid=1).

From the allocation information and the fact that this does not show up in your second query presumably the underlying object has been dropped. You may be able to get additional information about it, such as original object name, by looking for that allocation unit id in sys.fn_dblog if you're lucky.

If it belongs to a dropped object then one would certainly hope these pages are first candidates for putting on the free list.

However the article TempDB memory leak? and   associated connect item indicate that there might be a problem in this area...


Query taking more time to execute due to lengthy column size

Even if all 6,000 rows are packed full of 4,000 Unicode characters (8,000 bytes), that's only 45 MB of data.  There's just no way it should take 10 minutes to read that much, even with a cold cache.

You're likely getting blocked by something, as Randi Vertongen mentioned in the comments.  The answer here isn't going to be indexing.  Run sp_WhoIsActive while your query is executing to see what's going on.  That should show you:

what the query is waiting on
if it's blocked (and what's blocking it)


Database design for products with bundles of products

The pattern you are describing is often called a "parts explosion" or "bill of materials."  It is part of the graphs and trees portion in the study of data structures.  The essence of the solution is to realize that any given "product" can be made up of other "products."  The design is then a network structure where there is a Product table that has a row for each product - whether it is made up of other products or not, and then a Product Component table that has a row for each product that is made up of other products and each corresponding product that is a component of that product.  In your case, each product has a price.  So you would have something like this

Product
-----------------------------------
|Name             |Price          |
-----------------------------------
|Orange           |1             |
|Apple            |1.20          |
|Fruit Package    |3.80          |
-----------------------------------

Product Component
----------------------------------------------------------
|Product               |Contains                |Quantity|
----------------------------------------------------------
|Fruit Package         |Orange                  |2       |
|Fruit Package         |Apple                   |2       |
----------------------------------------------------------

This design is preferable to a single table with a recursive association as it cleanly separates what are really two entity types - nodes and links.  In our case, the products are the nodes, and the product components are the links.

While the network design is a common structure, querying it is problematic as when completely filled it is a recursive structure of varying depth.  Industrial strength DBMS' such as Oracle and SQL Server have special language elements (Oracle's CONNECT BY and SQL Server's recursive CTE) to aid in making the query declarative.  Given you are using File Maker Pro, which I know little about, you may not have such language constructs to help and may have to write procedure code to traverse the network.  This issue can be eased however if the network turns out to be of fixed depth - say every product has either no components, or one level of components.  Here are some references with regard to network structures in database design:

Practical Issues in Database Management - Fabian Pascal.  Chapter 7 provides the best and most understandable explanation I have found.
Joe Celko's Trees and Hierarchies in SQL for Smarties, Second Edition.  This is an entire book on the topic specific to the SQL standard.
Enterprise Model Patterns - David Hay.  A book about patterns common to all organizations (unfortunately the ER Diagrams are presented in UML but that can be overcome) there are several examples of network structures.


From here, replace TYPE=MYISAM with ENGINE=MYISAM.

This used to be acceptable syntax, but has been deprecated and removed in 5.5 (from the same thread).


dbcc memorystatus - buffer pool target on VM higher than max memory

Value Buffer pool target is not in amount of 8 KB pages but the Is actually number of pages which you were correct to assume . So as per output the target buffer pool memory is would be 8*32751616 which comes to 249G. Which is really absurd

Reason:

Now for reason why Bpool target is showing value 250 G in DBCC MEMORYSTATUS and  memory manager is showing 12 G as Target committed is because the Counter SQL Server Buffer Manager: target Pages is DEPRECATED from SQL Server 2012 onwards. Microsoft did not specifically mentioned this anywhere but I found it from SQL Server Buffer Manager Object BOL Document. If you read this article you wont find counter target pages because its deprecated. You would also see the same counter present in SQL Server 2008 R2.

Other reason may be I assume your system is NUMA system with NUMA nodes. I am sure if you see MEMORYSTATUS output it would have memory allocation breakdown for various nodes like Node0, Node 1...Node 64. So target includes target memory allocation for all NUMA nodes. If you note Buffer pool in MEMORYSTATUS output, it has single entry and is not restricted to particular node. Plus this value is calculated during startup and does not actually shows correct value after a period of time so you should not refer to it. Instead refer to memory manager output

Edit:

As per question regarding buffer pool target pages on NUMA node believe me its not showing correct value for buffer pool. For my system with 4 G of RAM target pages are 7979008 with almost nothing running on the system. This is totally absurd and I would request you not to look at this counter. I had a chat with one of the experts on this field to confirm the behavior and he told me you should avoid looking at buffer pool output and target pages specially on NUMA system its calculation( way of calculation) is not correct. I can raise a connect Item but it would take few months to get response from MS team.

Not every thing in DBCC MEMORYSTATUS output matters to normal users many things are internal to MS and they would just not give any information regarding the same. If you want to monitor SQL Server memory usage please use below query.

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If you really want to monitor SQL Server target memory I would suggest you to look at

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

There are other counters as well you can get from This Link.

Few other interesting counters are

Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design

Free List Stalls/sec – Number of requests per second that had to wait for a free page

PS: Please let me know if I have missed anything or if something is not clear

Edit: Here is the Connect Item for your issue lets wait what MS folks have to say about it. Please vote it


Select the oldest records in a table in Oracle

Oracle does not, itself, track the date a row is inserted into a table. So unless you yourself design the table with something like a DATE_INSERTED column, and reigorously enforce the proper population of that column, then the answer to "how can I select the oldest records in a table in oracle?" is "you can't".


MySQL: Why is auto_increment limited to just primary keys?

Why would you want to have an auto_increment column that is not the primary key?

If you want a column to be an auto_increment, by definition, you are not storing meaningful data in that column.  The only case where storing non-meaningful information makes sense is the special case that you want to have a synthetic primary key.  In that case, the lack of information is a benefit because there is no risk that someone will ever come along in the future and want to change the data because some attribute of some entity changed.

Having multiple auto_increment columns in the same table seems even odder.  The two columns would have the same data-- they're being generated by the same algorithm and being populated at the same time after all.  I suppose you could come up with an implementation where it is possible for them to be slightly out of sync if there were enough concurrent sessions.  But I can't imagine how that would ever be useful in an application.


@A_V, I think you can find what you're looking for here. It has explanations of 1NF - 4NF, including BCNF.

From the page:

  First normal form (1NF) sets the very basic rules for an organized database:

  Eliminate duplicative columns from the same table.

  Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

  Second normal form (2NF) further addresses the concept of removing duplicative data:

  Meet all the requirements of the first normal form.
  Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  Create relationships between these new tables and their predecessors through the use of foreign keys.

  Third normal form (3NF) goes one large step further:

  Meet all the requirements of the second normal form.
  Remove columns that are not dependent upon the primary key.

  The Boyce-Codd Normal Form, also referred to as the "third and half (3.5) normal form", adds one more requirement:

  Meet all the requirements of the third normal form.
  Every determinant must be a candidate key.

  Finally, fourth normal form (4NF) has one additional requirement:

  Meet all the requirements of the third normal form.
  A relation is in 4NF if it has no multi-valued dependencies.

  Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.


How to get the last non-null value that came before the current row

You can use a subquery:
select
  t1.ts,
  case when a is null then
    (select a
     from tab t2
     where t2.ts < t1.ts and a is not null
     order by a desc
     limit 1)
  else a end as a
from
  tab t1;

ts |  a
-: | -:
 1 |  1
 2 |  2
 3 |  2
 4 |  2
 5 |  3

db<>fiddle here


Copy tables selected from sys.tables to new DB

If you are looking for a quick one-off solution, you can try this.

In the source database, run this command (making the necessary changes for TrgDb for your target database and SrcDb for your source database.  Also change the where clause to pick your tables.

SELECT 'select * into TrgDb.S1.' + t.NAME + ' from  SrcDb.S1.' + t.NAME + ';'
FROM sys.tables t
WHERE NAME LIKE 'blah%';

Take the results and paste the commands into a new SSMS window that points to the target database.


You can use a combination of tools. I'll give you an abridged list of the ones I use.

First, I assume you have database mail configured.

Second, you can trigger a lot of run-of-the-mill alerts using SQL Server Alerts. You can also run a stored procedure using a scheduled job (say every five minutes or some interval like that) and fire the user counters which will trigger SQL Server Alerts. When an alert happens, you can also execute a job in addition to notifying operators. If you know WMI, you can use Alerts to get to PerfMon counters.

Third, you can use SQL Server Extended Events, but they are more complicated and require a good understanding of the database engine and hand coding because there is very little in the way of GUI.

Finally, you can just schedule a stored procedure to run on a regular basis, allow it to collect metrics from the DMVs (database management views) and then send the email directly from the stored procedure.

Specifically for your situation, I might suggest you run sys.dm_os_performance_counters on a regular basis (scheduled job/stored procedure) and cache it in a metadata table, then use that for analysis.

Also, getting CPU usage information in SQL Server is not the easiest thing in the world because CPU stats can change on such a finely-granular timescale. Try this article for a good overview of how it works. Perhaps look at "free pages" to get you 70% RAM figure.


IN MariaDB

MariaDB has a SEQUENCE Storage Engine. So, if you are using MariaDB, all your sequence troubles are over (or have just begun).

Sequence of 10 numbers

select * from seq_1_to_10;

NOT USING TABLES

Sequence of 10 numbers

select * from
(select 0 x union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A;

Sequence of 100 numbers

select (t*10+u+1) x from
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B
order by x;

Sequence of 1000 numbers

select (h*100+t*10+u+1) x from
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C
order by x;

Sequence of 10000 numbers

select (th*1000+h*100+t*10+u+1) x from
(select 0 th union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) D
order by x;

USING TABLES

Sequence of 10 numbers

use test
drop table if exists seq10;
create table seq10
(x int not null auto_increment primary key);
insert into seq10 values (),(),(),(),(),(),(),(),(),();
select * from seq10;

Sequence of 100 numbers

use test
drop table if exists seq100;
create table seq100
(x int not null auto_increment primary key);
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 values (),(),(),(),(),(),(),(),(),();
insert into seq100 select x + 50 from seq100;
select * from seq100;

Sequence of 1000 numbers

use test
drop table if exists seq1000;
create table seq1000
(x int not null auto_increment primary key);
insert into seq1000 values ();
set @p= -1;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
select * from seq1000;

Sequence of whatever numbers (Example: 3.5 million)

use test
drop table if exists seq;
create table seq
(x int not null auto_increment primary key);
insert into seq values ();
set @maxseq = 3500000;
set @p = -1;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
select max(x),count(x) from seq;

YOUR ACTUAL QUESTION

Using the sequence 0..9, you left join the sequence against the table

select A.number,1-ISNULL(B.number) present from
(select 0 number union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A
left join numbers B using (number);

Your sample data with the new query

mysql> drop table numbers;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists numbers;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table numbers (number int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into numbers values (3), (2), (9);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select A.number,1-ISNULL(B.number) present from
    -> (select 0 number union select 1 union select 2 union select 3 union select 4 union
    -> select 5 union select 6 union select 7 union select 8 union select 9) A
    -> left join numbers B using (number);
+--------+---------+
| number | present |
+--------+---------+
|      0 |       0 |
|      1 |       0 |
|      2 |       1 |
|      3 |       1 |
|      4 |       0 |
|      5 |       0 |
|      6 |       0 |
|      7 |       0 |
|      8 |       0 |
|      9 |       1 |
+--------+---------+
10 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


I was trying to connect to a secondary instance on the server.  If I use the primary instance this appears to work.  Is there a way to use the secondary instance?  I have the secondary instance port open but it is not of course using 1433.


SQL Server: what additional permissions for EXECUTE for implicit result codes?

What has happened here is that you have inadvertently included the GRANT statement in your stored procedure definition.

Most people assume that the BEGIN/END block contains the definition of the stored procedure, but it is actually through the end of the batch in which the CREATE PROCEDURE statement is executed (i.e.: the next GO or the end of the script, whichever comes first.)

In my experience, it is common to omit a GO between the stored procedure definition and the following GRANT statements in build scripts. I've done it myself! And it often goes unnoticed because a lot of people run their apps with db_owner role membership, and db_owner can execute the GRANT with no problem.

So, simply remove the GRANT from the procedure definition, and handle permissions in a separate batch or script:

CREATE PROCEDURE [dbo].[sp_fakeProcedureName]
AS
BEGIN
  SET NOCOUNT ON;

  SELECT    fakeTablePKIdentity, column2, column3, column4
  FROM fakeTable
  ORDER BY column2
END
GO


What are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?

Based on the research below, I feel confident that I can recommend to change the Cursor Behavior to Preserve, though at a potential performance hit.  I believe the performance loss with Preserve is minimal compared to the potential performance hit on a large recordset with code that performs a .MoveLast/.MoveFirst to populate the recordset.

Useless info From IBM:
Flag for cursor behavior when a commit or rollback transaction is called
Possible values are:
•   0=close cursor
•   1=preserve cursor
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm

Performance risks From DataDirect for ODBC:
Purpose
Determines whether cursors will be preserved or closed at the end of transactions.
Valid Values
0 | 1
Behavior
If set to 1 (Enabled), cursors are held at their current position when transactions end. This value may slow the performance of your database operations.
If set to set to 0 (Disabled), cursors are closed at the end of transactions.

http://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/cursor-behavior.html

Performance risks From Pace University for connecting to Informix for administering ColdFusion Server:
Preserve cursors at the end of each transaction -- Determines whether cursors will be preserved or closed at the end of each transaction. Enable this attribute if you want cursors to be held at the current position when the transaction ends. Enabling this option may impact the performance of your database operations.
http://csis.pace.edu/~ctappert/cs615-02/support/Help/Administering_ColdFusion_Server/admin0416.htm

Required Preserve For ArcGIS (popular software):
Cursor Behavior must be set to 1 - Preserve because ArcGIS expects Informix cursors to remain open and active following a database COMMIT operation.
Setting the Cursor Behavior to 0 - Close will have undesirable effects during data insertions and updates.
http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-informix/set-odbc-informix.htm

Required Preserve For TOTVS DBACCESS (unknown software):
… determines the behavior of the database in relation to the automatic closing of cursors when instructions COMMIT WORK or ROLLBACK WORK are submitted.
If the configuration is not proper [during Fetch operations], Cursors may return EOF before the end of data recovery

http://tdn.totvs.com/display/tecen/DBAccess+-+Informix+-+ODBC+-+Cursor+Behavior


Do we create one (1) table for users or a table for each user type (parents, teachers, students, admins)?

You are correct - both will work.

The former could make for very wide table, containing many null values where those fields are not applicable.

The latter is, perhaps, a "better" model (from a Purist" point of view) but it does make the database structure more complicated.

A compromise might be to have a "core" Users table that contains the really basic, core information - like Name - and then additional tables for each Type if User that has other fields that are specific to that Type.

Bear in mind that one person may be more than one Type of User, either over time or even at the same time (e.g. a Teacher who is also a parent of a Student)!

  is a course (like math, calculus, history) linked to a room (a location)? or is a class (a student can be in grade 1, class 1) linked to a room?

Will a Course always run in the same Room?
Probably not (if only on the principle that "Stuff Happens").

A session - a Course, being run in a particular Room at a particular Time on a particular Day - represents the intersection of all three of these "parent" Entities - Course, Room and DateTime.

You almost certainly need a Calendar table that defines when classes can be run.

  do we assign homework to a class or to a student? I'm leaning towards student, seems to make sense, but not everyone agrees with me. And if link to a class why?

You distribute homework to an entire Class but you expect each Student to "do" their own (I hope!), so you care about which ones were given the assignment, which completed it (and, possibly, when), what mark they were given, and so on.

So, whilst your Logical process might be to give homework to a class, the underlying, Physical process has to be to "unbundle" all the students in that class, and link the homework assignment to each one of them.
The association of Student within Class can be used to perform any aggregated reporting that you require, at class level.


the problem is - use 0 as CASE, 0 == false
so query compare:

TRUE - not, go forward
'true1222' - FALSE == 0, OK

try to change query:

SELECT
    CASE 3
WHEN TRUE THEN
    'c'
WHEN 'true1222' THEN
    'a'
ELSE
    'b'
END;

and it return expected - 'b'


We did the same thing a while ago. The change went smoothly, we just had to change the various service accounts. We stopped all services before the move, then changed the accounts, then started everything without a hitch.


Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?

I thought that second transaction must wait until first is finished

Basically, this is not true. Your (or anyone's) database wouldn't be very efficient if only one person could do something with it at a time.

  Why does it happen?

Simply:

A SELECT query is taking shared locks on any rows it's looking at, so although it's not going to conflict with another SELECT query, it definitely will conflict with exclusive locks from a DELETE. As the SELECT query is not updating anything, it's usually going to be the one to be the deadlock victim, as it'll likely be the least expensive to rollback.

Why it happens specifically in the case you're talking about is not possible to say without further information. I'll add some links to deadlock detection at the end but I suspect this isn't the main purpose of the question so...

What to do?

It's difficult to know what the best approach is going to be for you (without a lot more information). It is possible that you should have a look at your isolation levels.

You could use READ_COMMITTED_SNAPSHOT, but this could result in further overheads, or use READ_UNCOMMITED/use a NOLOCK hint - I'm not a fan of those options usually as the data returned might not be actually what you want, but they may be suitable in your case. It's hard to say exactly what would be best, as it'll depend on a lot of different variables about your system and it's use.

Best thing you can do in that regard though is read up about isolation levels, these are some similar posts/sources that will start you off and go into things in more detail.

MSDN - How can a select statement result in being chosen as deadlock victim?, SQL Server central ,  MSDN - Understanding Isolation Levels

But...

Don't rush to make a change just so you can get your queries to finish. A more appropriate avenue to explore may be your queries themselves.

Why is the SELECT statement taking so long to run? Why are things being deleted from the table so frequently? Maybe some simple query tuning will get them all to complete quicker and reduce the chance of a deadlock. Perhaps you need to do some general performance tuning on your database server (I won't go into detail on this here though as it's getting way off topic).

You've got to make sure your queries are fit for business purposes, rather than queries just 'completing at all costs', databases are a business tool first a foremost and need to work around that (however much we might not like that)!

Just going ahead and changing the isolation level, or even just using NOLOCK, can have big impacts on your database and also what exact results queries return - which could have business impacts.

Also...

To aid in deadlock detection, have a look at this post on StackOverflow and check out the Deadlock Information Tools  on the MSDN site which will explain how to use trace flags to provide more information about your deadlocks.

Finally...

More specific advice can be offered if you put the SQL Server version in your post, and some more details about what exactly the SELECT query is (to see if there's anything that may be causing it to take so long and be open for being locked).


Error code 1054: Unknown column when inserting data into a table

You need to use JOIN by ID instead of the UNION.
Using JOIN there is no need of the sub-query.
Also ( before SELECT is not required after the INSERT INTO.

So the working query is:

INSERT INTO Company (Year, Company, Revenue, Profits)
SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

More explanation:

What you have tried with UNION is the wrong approach. Let me explain with a small example.

I'm creating the Comp_Info and Comp_Financials are the temporary table for the example

CREATE TEMPORARY TABLE Comp_Info (Id INT, Year INT, Comp_Name VARCHAR (200));

INSERT INTO Comp_Info (Id, Year, Comp_Name) VALUES
(1, 2005, 'Wal-Mart'), (2, 2005, 'Exxon Mobil'), (3, 2005, 'General Motors');

CREATE TEMPORARY TABLE Comp_Financials (Id INT, Revenue INT, Profits INT);

INSERT INTO Comp_Financials (Id, Revenue, Profits) VALUES
(1, 288189, 10267), (2, 270772, 25330), (3, 193517, 2805);

Using the sample data the UNION will result as:

SELECT Year, Comp_Name FROM Comp_Info
UNION
SELECT Revenue, Profits FROM Comp_Financials;

|Year      | Comp_Name       |
|----------|---------------- |
|2005      | Wal-Mart        |
|2005      | Exxon Mobil     |
|2005      | General Motors  |
|288189    | 10267           |
|270772    | 25330           |
|193517    | 2805            |

But with the same sample data, the JOIN will result as:

SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

| Year  | Company           | Revenue   | Profits
|------ |------------------ |---------- |---------
| 2005  | Wal-Mart          | 288189    | 10267
| 2005  | Exxon Mobil       | 270772    | 25330
| 2005  | General Motors    | 193517    | 2805


PostgreSQL: changing data directory location (sshfs)

I finally solved the problem using -o allow_other when mounting the /mnt/titanBDD with sshfs.

The error message was still :

  Error: could not exec

/usr/lib/postgresql/9.4/bin/pg_ctl /usr/lib/postgresql/9.4/bin/pg_ctl
start -D /var/lib/postgresql/9.4/redirect_TITAN
-l /var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf"

(which repeat the command twice?!)

when executing:

usr/lib/postgresql/9.4/bin/pg_ctl
start -D /var/lib/postgresql/9.4/redirect_TITAN
-l /var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf"

but I noticed the user postgres didn't have access to the mounted point which put me on the track.

Now PostgreSQL starts without any problem.


What is difference between Lease Timeout and HealthCheck Timeout in AlwaysON?

Google took me straight here :-
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-lease-healthcheck-timeout?view=sql-server-ver15


Oracle - Session still holding the locks

My suggestion will be to add timeout if response is taking time, and if any thing fails use until successful of mule.


Cross Database Query between Azure SQL Database and SQL Server Database

No. In fact, a traditional cross database query isn't possible. You can set up a mechanism for querying between two Azure SQL Databases, but it's not the traditional mechanism. I have a blog post on how that works and there are links there to the documentation. However, even with that, you can't combine it to a query with on-premises SQL Server.


Prevent a user from making changes to some columns in a MS SQL Server database

You can revoke update on that column from a particular user:

REVOKE UPDATE ON OBJECT::Employees.Salary FROM theUser;

See: https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-object-permissions-transact-sql?view=sql-server-2017


How to restore only functions in windows

A dump generated with the -Fc option of pg_dump is in the custom format, which implies that it has a Table of Contents, which can be used to extract contents selectively.

As explained in pg_restore manual, the option to output the TOC is -l:

pg_restore -l db.dump > db.list

This produces a file with one line per object, including functions, that can be edited manually to keep only the objects you're interested in.

Then a restore into another database could be done with:

  pg_restore -L db.list db.dump | psql -d otherdatabase [otheroptions]

If you were interested in extracting only a couple of functions, a quicker way could be to use the -P option to pg_restore:

  -P function-name(argtype [, ...]), --function=function-name(argtype [, ...])

  Restore the named function only. Be careful to spell the function name and arguments
      exactly as they appear in the dump file's table of contents.


Check value when I have a list of elements separated by commas in a column?

You can use the LIKE clause.

SELECT
    Office
FROM
    typology
WHERE
    NAME='Jones' AND
    OWNER='Lasi' AND
    TYPE='TKT' AND
    LIST_OF_VALUES LIKE '%AUD%'


Assuming your pager is less, try LESS=-FX; export LESS in your shell environment.

From man less:

      -F or --quit-if-one-screen
              Causes less to automatically exit if the entire file can be dis‐
              played on the first screen.

      -X or --no-init
              Disables sending the termcap initialization and deinitialization
              strings to the terminal.  This is  sometimes  desirable  if  the
              deinitialization  string does something unnecessary, like clear‐
              ing the screen.

To have this only in psql, set it with a meta-command in .psqlrc:

\setenv LESS -FX


Optimal data type for storing numbers in SQL Server

You can use row compression Row Compression Implementation that in designed to solve exactly this problem.
And you can also think about page compression that first applies row compression and then tries to achieve space savings using dictionary
Update: available also in Standard started with SQL Server 2016 SP1


Parsing a section of a pipe-delimited string

Call me insane, but this is what I came up with:

CREATE TABLE #SomeTable (
    id INT,
    content VARCHAR(MAX))

INSERT INTO #SomeTable VALUES
    (1, 'a|b|c|d|e|f|Aloha|'),
    (2, 'a|b|c|d|e|f|From|'),
    (3, 'a|b|c|d|e|f|Hawaii|')

SELECT
    REVERSE(SUBSTRING(REVERSE(LEFT(content, LEN(content)-1)), 0,CHARINDEX('|',REVERSE(left(content, LEN(content)-1)),0)))
FROM #SomeTable

Explanation (from the inside out):

Strip the last pipe (|)
Reverse the whole string
Find the next pipe (ex last)
Extract the characters up until the next pipe.
Reverse the string again.

Here's another take:

SELECT
    REPLACE(REVERSE(SUBSTRING(REVERSE(content), 0, CHARINDEX('|',REVERSE(content),2))),'|','')
FROM #SomeTable

Reverse the string.
Get the position of the next pipe (start looking from the second position to skip the first (ex last) pipe.
Reverse the string.
Replace the last pipe.


How can I retrieve the image which is stored in bytea column of a pg_toast schema?

Certain graphical DB clients will show you the image when going to the table data view.  In plain query output (for example in psql), though, you cannot expect images to appear.  Otherwise, you have to 'decode' the bytea.

Here is an example solution (written in PHP) on gis.stackexchange.com:

$conn = pg_connect("...");
$res = pg_query($conn, "SELECT chunk_data AS photo FROM photo WHERE ...");
$raw = pg_fetch_result($res, 'photo');
header('Content-type: image/jpeg');
echo pg_unescape_bytea($raw);

Note:

pg_toast is not usually a schema you query directly from.  What is stored there has a 'parent' table, do your queries against it instead.  Then you can possibly use sane column names in your queries, and so on.