Represent time interval in mysql Database

If you want to store availability in a table, you should separate the start time and end time of shift in 2 columns.

Your table would then be:
create table support_staff_schedule
( id int not null,
name varchar(50),
time_start int,
time_end int)

alter table support_staff_schedule add index time_start_end (time_start desc,time_end);

The time is set as integer because it doesn't look like you care for the date. If date is included then you would have to create a new row for every schedule for a given person. I'm assuming your business rule is: "Same schedule every day unless modified"

I use time_start in descending order because technically you should be spending most of your time looking at the recent schedule rather than past schedule. If your business requirements are different, you may want to change that.


If we set autgrowth for log file as restricted then logs size will increase up to specified limit and then it will start discarding old logs and new logs will be there in logs.

This is wrong. If it has to grow it will grow until the max size and after which further growth request will result in failure. You are confusing things with log truncation, which is the process which allow log physical file to be reused (so it doesn't have to grow). I have an article explaining how log grows and what is the role of truncation in this, see How to Shrink SQL Server log.


I assume:

(n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <>
(t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del)

is what puzzles you. It means that if any of the columns differ the predicate is true. It is a shorter form of:

NOT ( (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) =
      (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del) )

which in turn can be rewritten as

NOT (  n.seq_scan = t.seq_scan AND
       n.idx_scan = t.idx_scan AND
       ...                          )

not sure if it makes it easier to grasp, but it can also be written using disjunctions

    (  n.seq_scan <> t.seq_scan OR
       n.idx_scan <> t.idx_scan OR
       ...                         )

EDIT:

Nulls makes logical reasoning difficult so I created a small example to verify the logical equivalence of the expressions above. I think it is sufficient to use only two variables (http://sqlfiddle.com/#!15/73c52/2):

create table s (x int);
insert into s(x) values (null),(0),(1);
create table t (x int, y int);
insert into t(x,y) select s1.x, s2.x from s s1 cross join s s2;

The following expressions corresponds to the ones I used above:

select (t1.x,t1.y)<>(t2.x,t2.y) as a
     , not( (t1.x,t1.y)=(t2.x,t2.y)) as b
     , not ( t1.x = t2.x and t1.y = t2.y ) as c
     , t1.x <> t2.x or t1.y <> t2.y as d
from t t1
cross join t t2;

a       b       c       d
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
false   false   false   false
true    true    true    true
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
false   false   false   false
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
false   false   false   false
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
true    true    true    true
true    true    true    true
(null)  (null)  (null)  (null)
true    true    true    true
false   false   false   false


Can I export a maintenance plan without using Integration Services?

Maintenance plans are stored in msdb.dbo.sysssispackages like any other SSIS packages that are stored to SQL Server.  I have a handy post on SSIS Package Extract from MSDB that should cure what ails you.


I changed the max memory setting but I don't see memory usage rise at OS level

That setting is not "go use a bunch of memory for whatever." It is a setting that dictates the maximum usage of memory for the buffer pool (data cache, plan cache, and a few other areas of memory reservation).

In order to actually use 36GB of memory, you need to run queries that will force the buffer manager to pull data off disk and put it into memory. Run the following query and you will see memory usage rise (also, in case it is where you are looking, do not use Task Manager for this).

SELECT * FROM dbo.YourBiggestTable AS ybt1
  CROSS JOIN dbo.YourBiggestTable AS ybt2;


Easy on the bigint and nvarchar(MAX)

Table Student:

ID int (primary key, identity)
Name nvarchar(800)

Table Question:

ID smallint (primary key, identity)
TextOfTheQuestion nvarchar(800)
CorrectAns tinyint FK (to AnswerNum.Num)
tested and can make a composite FK to Answer  QuestionID, AnsNum
I was not sure could do this but it would mean you have to create the question
Then populate Answer
Then come back and edit question for CorrectAns so may not be the way you want to go

Table Answer:

QuestionID smalling PK FK (to Question.ID)
AnswerNum tinyint   PK FK (to AnswerNum.Num)
TextOfTheAnswer nvarchar(800)

Table StudentChoice:

StudentID int       (primary key, foreign key to Student.ID)
QuestionID smallint (primary key)
AnswerNum tinyint
(QuestionID, AnswerNum) REFERENCES Answer (QuestionID, AnswerNum)

Table AnswerNum:

Num tinyint PK
-- just values 1, 2, 3
-- this way you can change number of questions in the future

This is how you score
When the primary query comes out clean it is sign of a good db design

  select Student.Name, count(*) as score
  from student
  left join StudentChoice
         on Student.ID = StudentChoice.StudentID
  left join Question
         on Question.QuestionID = StudentChoice.QuestionID
        and Question.CorrectAns = StudentChoice.AnswerNum
            -- CorrectAns in Question makes this a whole lot easier
  group by Student.Name


I've voted to close this, because it's essentially a poll question, and there isn't a possible "right answer." You should be evaluating the vendors' offerings to see which product best suits your requirements and budget. Nobody can tell you which product will do that best.

The major vendors you want to investigate are:

SQL Sentry
Quest FogLight / SpotLight
Confio Ignite
RedGate SQL Monitor
Microsoft System Center Operations Manager
Idera Diagnostic Manager

They all have evaluation versions so you can test them out. In addition to feature sets you'll want to baseline your workloads and check the additional overhead each monitoring solution adds - no monitoring is 100% free.


MySQL Synchronous Replication on Ubuntu 12.04 with DRBD

I would suggest you to use active-passive cluster with drbd. Active-Active leads to more problems then it solves especially if you use MySQL as database because you can't run 2 MySQL Daemons on the same Database!

Creating drbd cluster is simple. You have to do the following steps on both servers.

Create a partition for share
Create a drbd config with the partition, this brings you a new block device
Start the Cluster with one as Master and the other as slave
Create and Mount a filesystem (ext4, xfs or on the block device on the master
Copy the database to the new filesystem and start db and you are done.

Thats the concept.

Here a short overview of the commands to use:

Server1 and Server2:
Create a partition for the complete Harddisk sdb

fdisk /dev/sdb
n
p
<enter>
<enter>
w

Install drbd

apt-get install drbd8-utils

Create a config file at /etc/drbd.conf:

common {
  syncer {
    rate 100M;
  }
}

resource r0 {
  # Protokoll-Version
  protocol C;

  startup {
    wfc-timeout         0;
    degr-wfc-timeout  120;
  }
  disk {
    on-io-error detach;
  }
  on server1 {
    device     /dev/drbd0;
    disk       /dev/sdb1;
    address    <IPSERVER1>:7788;
    meta-disk  internal;
  }
  on server2 {
    device     /dev/drbd0;
    disk       /dev/sdb1;
    address    <IPSERVER2>:7788;
    meta-disk  internal;
  }
}

Create the drbd device on both servers

drbdadm create-md r0
drbdadm up r0

Check the state

cat /proc/drbd

There should be a line with "cs:Connected ro:Secondary/Secondary"
The first "Secondary" is the server you are logged in and the 2nd "Secondary" is the other server.

Now make the first server to the master and override all data on the server2

drbdadm primary --force r0

You could watch this process in /proc/drbd

No you can mount your new drbd device on the master:

mount /dev/drbd0 /mnt

if you like to switch master and slave you can use the commands:

drbdadm primary r0

or

drbdadm secondary r0

Based on the server where you are logged in. But remember only one server could be primary.

For Active-Active scenario you need a cluster file system like ocfs2.

Use this manual with caution because this could delete your data.

You will find more information at drbd.org: http://www.drbd.org/users-guide-8.4/

A complete alternative is to use the DRBD MC - the Management console created by LINBIT, its a java application running on a workstation and connects to the servers over ssh and make the configuration for you.
http://www.drbd.org/mc/lcmc/


Can moving tables to another MySQL database help performance?

Generally, no. A few tangentially related things to note though:

You could use a symlink to move the /<datadir>/db2/ directory to another storage/block device. Or you could use the CREATE TABLE ... DATADIR=/../ option to move only the support_chat table to another storage/block device. That could make a big difference, assuming that you're frequently disk bound.
With MySQL 5.6, using multiple databases/schemas can help to improve slave throughput (assuming you're using replication) because the parallelization was at the schema level:

Replication Slave Options and Variables --slave-parallel-workers

So with three databases/schemas, you would benefit from setting slave-parallel-workers=3.

For more info on the multi-threaded slave work in MySQL 5.6:

MySQL Replication High Performance: Multi-Threaded Slaves and
Group Commit (pdf)

Just FYI, the multi-threaded slave behavior has been greatly improved in MySQL 5.7 too, the biggest example being that you can now instead use (Lamport) logical clock based parallelization:

Replication Slave Options and Variables --slave-parallel-type


unfortunately my database ASM service is not running. I restarted and everything worked. thanks all for your help


Importing Multipe Trace Files To A SQL Server Table

It will be entirely dependend on where the bottleneck is. Which requires you to measure, and approach this just like any other performance problem.

If the bottleneck is the INSERT performance then you should make sure you achieve minimal logging. The The Data Loading Performance Guide is, of course, mandatory reading.
If the bottleneck is reading the traces fn_trace_gettable then... you have a problem. It could be a slow source disk  (make sure the access is serialized and the head does not get randomized) or fn_trace_gettable itself may be slow (is not, is the disk, but you should measure...). Remember that even if  reading the source TRC files is slow, reading many of them in parallel from non-overlapping input access paths (ie. separate disks) may achieve much better throughput.
If the bottleneck is... hmmm... I don't see what else could be the bottleneck. But of course, you should measure, get perf counters, identify where the time goes.

Ultimately your problem is rather esoteric so the gist of it is that you need to measure and adapt to your findings.


Indexing to improve performance of range queries

If you have an index on created then the planner will need to choose between an using that index or the PK (or a full table scan) - it will not benefit from both at the same time.

--EDIT

As pointed out by @jug in the comments below, this is not accurate at least since 8.1: the planner may choose to build two in-memory bitmaps and combine them to get the result set. This gets more expensive as the tables get bigger, so the planner may choose not to do this depending on the size of the table and the estimated cost of using one index and then filtering.

--END EDIT

The new index will only be helpful if in some cases using it is more efficient than access via the PK. The kind of things that could make this likely include:

A large number of (...) in SELECT * FROM foo WHERE foo_id IN (...) AND created > 1234 AND created <= 6789
A small range, eg created > 6780 AND created <= 6790

Unless one or both is likely to happen, you should not create the secondary index - if they might, it would be best to test each scenario with and without the index to see if any performance benefit is worth the cost (eg increased storage and overhead for insert and update operation)


Use it for system swap space.   Swapping doesn't hurt performance, running out of memory hurts performance. swapping is just a symptom.

You could also try putting unlogged temporary tables there but I'm not sure what would happen when they disappear.


Is Oracle Wallet available for use with Oracle XE 11gR1

According to the Oracle Database Licensing Information 11g Release 2 document

Oracle Wallet
An Oracle Wallet is a PKCS#12 container used to store
authentication and encryption keys. The Oracle database secure
external password store feature stores passwords in an Oracle Wallet
for password-based authentication to the Oracle database. The Oracle
Wallet may also be used to store credentials for PKI authentication to
the Oracle Database, configuration of network encryption (SSL/TLS),
and Oracle Advanced Security transparent data encryption (TDE) master
encryption keys. Strong authentication services (Kerberos, PKI, and
RADIUS) and network encryption (native network encryption and SSL/TLS)
are no longer part of Oracle Advanced Security and are available in
all licensed editions of all supported releases of the Oracle
database.

That last bit about strong authentication services and network encryption being available in all licensed and supported releases of the Oracle database would suggest that it might be.  It just a matter how licensed and supported are defined.  XE is licensed, but it's support is via Oracle's free discussion forum rather than via the paid for My Oracle Support site


Is it possible to step through TSQL that uses variables, in SSMS?

Every time you submit text from SSMS the batch will end when that finishes executing and any variables will go out of scope.

The only way to do this is using the debugger as illustrated below. Then you can step through line by line using the F10 or F11 keys. This should only be used in a development environment.

Also (as pointed out by @clifton_h) this feature has been removed from SSMS 18.x but is still available in the latest GA build (currently 17.9.1)


MS Access .mdb & .ldb database corrupted

Open in Safe Mode
Open the Access application with the following option:
C:\> "C:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe" /safe "C:\path to mdb file\your.mdb"

Open and compact/repair
Open the Access database with the /compact parameter:
C:\> "c:\program files\microsoft office\office12\msaccess.exe" /compact "C:\path to mdb file\your.mdb"

Reference: Command-line switches for Access
Open the database and repair

In Microsoft Access 2000 and later, compact and repair functionality is combined under /compact. The /repair switch is supported for backward compatibility

Open the Access database and repair it with the following parameter:
C:\> "c:\program files\microsoft office\office12\msaccess.exe" /repair "C:\path to mdb file\your.mdb"

Bypass Startup Options
Alternatively try opening the Access database while bypassing any startup options:

Hold down SHIFT key
Open Microsoft Access database with a double-click on the MDB file
If messages pop up, keep SHIFT depressed and confirm any messages
Wait for Access Database to open

Reference: Bypass startup options when you open a database
Last possibility
Buy a repair software


This is a typical error when configuring DB2 the first time. There are many docs about that in the web.

Check instance is up
Check service name (/etc/serices)
Check instance configuration (svcename)
Check ports (netstats -nato)
Check instance local connectivity (db2 attach to db2inst1)
Check database local connectivity (db2 connect to sample)
Check remote access (telnet db2server 50000)
Check instance remote connectivity (db2 attach to db2inst1)
Check database remote connectivity (db2 connect to sample)


How to handle uniqueness of many large columns in SQL Server?

The chance of a hash collision is pretty astronomical (as discussed elsewhere on  Stack Exchange: https://stackoverflow.com/a/4014407).  However, you can reduce it further by adding a second key:

...
FiltersHash  AS HASHBYTES('SHA2_256', /* Various fields */) PERSISTED,
KeyPrefixes  AS CAST(Key1 AS CHAR(1) + '|' + CAST(Key2 AS VARCHAR(10))
    + '|' + LEFT(Key3, 100) + '|' + LEFT(Key4, 100)
    + '|' + LEFT(Key5, 100) + '|' + LEFT(Key6, 100)
    + '|' + LEFT(Key7, 100) + '|' + CAST(Key8 AS VARCHAR(10)) PERSISTED
...
CREATE UNIQUE INDEX UQ_Docs_BizKey ON Documents (FiltersHash, KeyPrefixes)

Now two records must match on three fields and the first part of five more.  If your data commonly includes vertical pipes, consider an alternative separator.  INSERTs into the table will be a little slower, but at your data volumes it's probably not a concern.

As an aside, you're probably better off clustering on MonthID and leaving DocumentID a non-clustered PK, assuming people occasionally run searches by date range ("all documents from June") but rarely search for a range of document IDs.


Will SSRS be extinct soon, and PowerBI the new model?

Does this mean Microsoft will eventually try to deprecate SSRS?

Ref:
https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/19/sql-server-2017-reporting-services-release-candidate-now-available/

No.

  We moved Reporting Services installation from the SQL Server installer
  to a separate installer. This is a packaging change, not a product
  change; access to SQL Server Reporting Services is still included with
  your SQL Server license. The new installation process keeps our
  packages lean and enables customers to deploy and update Reporting
  Services with zero impact on your SQL Server deployments and
  databases.

Should our team try to build new reports in PowerBI and transition the previous SSRS reports?

If you need PowerBI you should start using it but not thinking SSRS being deprecated.


How to share a unique value between tables efficiently [postgres]

Use bigint values generated by a single sequence. Numbers are quite easy to say over the telephone.
Forget about the requirement of enforcing database-wide uniqueness. Unless someone manually messes with the data, the sequence will guarantee the requirement. The performance cost of enforcing such a requirement with database means would greatly outweigh its usefulness.
An alternative would be to have a unique alphabetic prefix for each table that you store in a "table of tables". Then each table has its own sequence, and you generate the primary key in a BEFORE INSERT trigger by concatenating the table's prefix with the sequence value. That would be slighly more expensive, but would lead to more pronouncable names.


JOIN FOR SYSTEM TIME with column as the effective system time

You can retrieve all historical rows from a temporal table using FOR SYSTEM_DATE ALL.  That allows a manual comparison of the OrderDate to the SysStart and SysEnd dates:

SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products FOR SYSTEM_TIME ALL as p_then
    on o.OrderDate >= p_then.SysStart and o.OrderDate < p_then.SysEnd
    and oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name


What's the data flow between Pgpool, Postgresql and client

Pgpool is a kind of a layer between client and postgresql. Pgpool receive a query from client and ask Postgresql for data. Postgresql answers to Pgpool and pgpool send data to client like on the picture below. There is not direct connection between client and Postgres when you use Pgpool to connect to database. By the way Pgpool provides connection pooling, replication, load balance, connection limits and paralell query so it's quite nice to use with PGSQL

Manual: http://www.pgpool.net/docs/latest/pgpool-en.html


When the data pattern is the same and the data is possibly related, such a setup is completely fine and even desirable as it makes certain tasks (such as supporting a search function) a lot easier. With proper indexing and solid queries speed should not be a concern.

This type of table should only be avoided if you find yourself trying to force data to fit in the pattern for the sake of less tables (such as deciding to save your user data as a string representation to fit in that table with a new type) or using types to indicate different key references for other tables sharing a single column, which would inhibit using Foreign Keys and break your relational model.


INSERT INTO statement with parameters for tableName, columns and values?

You can't parameterize table names or column names - you'll have to use dynamic SQL.
You should also be passing the table's schema.
You should be validating table names and column names. Table names are easy to check, but for column names we'll use this function:

CREATE FUNCTION dbo.ParseColumnList
(
  @List NVARCHAR(MAX)
)
RETURNS TABLE
AS
  RETURN
  (
    SELECT i,
      c = LTRIM(CONVERT(SYSNAME, SUBSTRING(@List, i,
          CHARINDEX(',', @List + ',', i) - i)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_columns) AS n(i)
    WHERE i <= LEN(@List) AND SUBSTRING(',' + @List, i, 1) = ','
  );

You should be using strongly-typed parameters for parameter values, to protect yourself from SQL injection vulnerabilities. Understanding where these parameter values come from will help to further refine this solution, but for now you'll just have to blindly append your values list and hope for the best:

CREATE PROCEDURE dbo.DealersChoice
  @TableSchema   sysname,
  @TableName     sysname,
  @ColumnList    nvarchar(max),
  @ParamValues   nvarchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS ( -- make sure table exists:
    SELECT 1 FROM sys.tables AS t
      INNER JOIN sys.schemas AS s
      ON t.[schema_id] = s.[schema_id]
      WHERE t.name = QUOTENAME(@TableName)
      AND s.name = QUOTENAME(@TableSchema)
  )
  BEGIN -- raise an appropriate error here
    RETURN;
  END

  IF EXISTS ( -- make sure columns exist:
    SELECT 1 FROM dbo.ParseColumnList(@ColumnList) AS f
      LEFT OUTER JOIN sys.columns AS c ON f.c = c.name
      WHERE c.[object_id] = OBJECT_ID(QUOTENAME(@TableSchema)
        + N'.' + QUOTENAME(@TableName))
    WHERE c.name IS NULL
  )
  BEGIN -- raise appropriate error
    RETURN;
  END

  DECLARE @sql nvarchar(max) = N'INSERT '
    + QUOTENAME(@TableSchema) + N'.' + QUOTENAME(@TableName)
    + N' (' + @ColumnList + N') VALUES(' + @ParamValues + N');';

  EXEC sys.sp_executesql @sql;
END

I am not sure how you are building the @ParamValues parameter but if you have string delimiters embedded in it (like 5, 'foo', '20160206'), you'll need to double those up somewhere so that it becomes 5, ''foo'', ''20160206'' - this becomes a challenge when you have strings like O'Brien - one of just many reasons why you should be using proper parameters instead of concatenating one big string with all your values.

See the following:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2


Permissions using a TYPE as a Table Valued Parameter

Where can I find information about the necessary permissions to use a TYPE as a parameter?

I've submitted a PR against the doc page to get the requirements documented.

https://github.com/MicrosoftDocs/sql-docs/pull/3351

Here's a simple repro:

create type dbo.tt as table(id int)

go

create procedure dbo.ptt @tt tt readonly
as
select * from @tt

go

create user joe without login

grant references on type::dbo.tt to joe
grant execute on dbo.ptt to joe

go

execute as user='joe'

  declare @t tt
  exec ptt @t
  --The EXECUTE permission was denied on the object 'tt', database 'a', schema 'dbo'.

revert

go

grant execute on type::dbo.tt to joe

go

execute as user='joe'

  declare @t tt
  exec ptt @t
  --no error

revert


Do you need Table Lock when you have multiple OLE DB connections inserting to the same table?

The table lock option facilitates minimal logging, reducing transaction log space requirements under certain circumstances.  Below are relevant excerpts from the Prerequisites for Minimal Logging topic in the SQL Server Books Online.

  The table is not being replicated.
  Table locking is specified (using TABLOCK).
  Table is not a memory-optimized table.
  If the table has no indexes, data pages are minimally logged.
  If the table has no clustered index but has one or more nonclustered    indexes, data pages are always minimally logged. How
  index pages are    logged, however, depends on whether the table is
  empty:

  If the table is empty, index pages are minimally logged.
  If table is non-empty, index pages are fully logged.

  If you start with an empty table and bulk import the data in multiple    batches, both index and data pages are minimally logged
  for the first    batch, but beginning with the second batch, only data
  pages are    minimally logged.
  If the table has a clustered index and is empty, both data and index    pages are minimally logged. In contrast, if a table has a
  clustered    index and is non-empty, data pages and index pages are
  both fully    logged regardless of the recovery model.

The table lock will block other processes using the table, including the other OLEDB Destinations within the SSIS package. You could set the Maximum Insert Commit Size to improve concurrency but with the consideration that file will be partially imported in the event of a failure.


Select all rows with same ID when the last record of that ID meets the criteria

try with this:
with
last_row as (
select
ID,
max(date) as date
from test
group by ID
),

last_row_open as (
select
ID
from test
where ID in (select ID from last_row)
  and status='Open'
)

select
*
from test
where ID in (select ID from last_row_open)


Why take full backups when you can do COPY_ONLY?

As long as you don't care that:

in the async model, the REDO queue may fall way behind, so the backups on your secondary may not represent the point in time that you think they would if they were taken on the primary.
you will not be able to use DIFFERENTIAL backups in this model.
in the event of a restore, you will have to manually determine which log backups contain the LSN ranges you'll need to restore on top of the chosen COPY_ONLY backup and, given the above, the timestamps on the bak/trn files may not be accurate enough to determine the set of files and sequence you need.
backup history will not be recorded centrally.

Also, I still think there are going to be scenarios where this ultra-long log chain, or relying on a COPY_ONLY backup taken in between log backups, are going to make it difficult to restore. Personally, I would be keen to perform a full, proper backup on the primary on some regular interval, say once a week. YMMV.


If I were to do this, I'd use SQL Server, with 3 tables.

USE tempdb; /* this is a test-bed */

IF COALESCE(OBJECT_ID('dbo.RequestStatusHistory'), 0) <> 0
BEGIN
    DROP TABLE dbo.RequestStatusHistory;
END
IF COALESCE(OBJECT_ID('dbo.Requests'), 0) <> 0
BEGIN
    DROP TABLE dbo.Requests;
END
IF COALESCE(OBJECT_ID('dbo.Statii'), 0) <> 0
BEGIN
    DROP TABLE dbo.Statii;
END

CREATE TABLE dbo.Statii
(
    StatusID INT NOT NULL
        CONSTRAINT PK_Statii
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusDescription VARCHAR(50)
);

INSERT INTO dbo.Statii(StatusDescription)
VALUES ('Pending')
    , ('Approved')
    , ('Rejected')
    , ('Cancellation Requested')
    , ('Cancellation Approved')
    , ('Cancellation Rejected');

CREATE TABLE dbo.Requests
(
    RequestID INT NOT NULL
        CONSTRAINT PK_Requests
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , RequestName VARCHAR(30) NOT NULL
    , CurrentStatusID INT NOT NULL
        CONSTRAINT FK_Requests
        FOREIGN KEY
        REFERENCES dbo.Statii(StatusID)
    , CurrentStatusDate DATETIME NOT NULL
        CONSTRAINT DF_Requests_CurrentStatusDate
        DEFAULT (GETDATE())
);

CREATE TABLE dbo.RequestStatusHistory
(
    RequestStatusHistoryID INT NOT NULL
        CONSTRAINT PK_RequestStatusHistory
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , RequestID INT NOT NULL
    , StatusID INT NOT NULL
    , StatusDate DATETIME NOT NULL
        CONSTRAINT DF_RequestStatusHistory_StatusDate
        DEFAULT (GETDATE())
);
GO

I'd create a couple of stored procedures, however you could easily do these in the application code if desired.  Both procedures use a single atomic statement that negates the need for an explicit transaction since either the entire transaction will commit, or it will entirely roll back.

I tend to recommend using stored procedures for DML operations wherever possible since this allows you to make database level DDL changes without the requirement of deploying new binaries everywhere.

The first proc simply inserts a new row into the dbo.Status table, and returns a resultset including the new RequestID, the CurrentStatusID (which is set to 1), and the CurrentStatusDate which is set to the current date and time using GETDATE():

IF COALESCE(OBJECT_ID('dbo.StatusInsert'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.StatusInsert;
END
GO
CREATE PROCEDURE dbo.StatusInsert
(
    @RequestName VARCHAR(30)
)
AS
BEGIN
    INSERT INTO dbo.Requests (RequestName, CurrentStatusID, CurrentStatusDate)
    OUTPUT inserted.RequestID, inserted.CurrentStatusID, inserted.CurrentStatusDate
    VALUES (@RequestName, 1, GETDATE()) ;
END;
GO

The second proc allows us to do 2 operations in one atomic unit.  First, it updates the CurrentStatusID and CurrentStatusDate columns in dbo.Requests.  Then, using the OUTPUT clause, it inserts the old values from dbo.Requests into dbo.RequestStatusHistory.

IF COALESCE(OBJECT_ID('dbo.StatusUpdate'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.StatusUpdate;
END
GO
CREATE PROCEDURE dbo.StatusUpdate
(
    @RequestID INT
    , @NewStatusID INT
)
AS
BEGIN
    UPDATE dbo.Requests
    SET Requests.CurrentStatusID = @NewStatusID
        , Requests.CurrentStatusDate = GETDATE()
    OUTPUT deleted.RequestID, deleted.CurrentStatusID, deleted.CurrentStatusDate
    INTO dbo.RequestStatusHistory(RequestID, StatusID, StatusDate)
    WHERE Requests.RequestID = @RequestID
END;
GO

Insert some sample data:

EXEC dbo.StatusInsert 'Request #1';
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusInsert 'Request #2';
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 1, 2;
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 1, 3;
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 2, 4;

Show the results:

SELECT *
FROM dbo.Requests;

SELECT *
FROM dbo.RequestStatusHistory;

When the client wants to see status history, you could provide those details using a reasonably flexible stored procedure such as:

IF COALESCE(OBJECT_ID('dbo.RequestHistory'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.RequestHistory;
END
GO
CREATE PROCEDURE dbo.RequestHistory
(
    @RequestID INT = NULL
    , @RequestName VARCHAR(30) = NULL
)
AS
BEGIN
    ;WITH ReqHistory
    AS
    (
        SELECT r.RequestID
            , r.RequestName
            , StatusDescription = s.StatusDescription
            , StatusDate = r.CurrentStatusDate
        FROM dbo.Requests r
            INNER JOIN dbo.Statii s ON r.CurrentStatusID = s.StatusID
        UNION ALL
        SELECT r.RequestID
            , r.RequestName
            , s.StatusDescription
            , rsh.StatusDate
        FROM dbo.Requests r
            INNER JOIN dbo.RequestStatusHistory rsh ON r.RequestID = rsh.RequestID
            INNER JOIN dbo.Statii s ON rsh.StatusID = s.StatusID
    )
    SELECT *
    FROM ReqHistory rh
    WHERE (rh.RequestID = @RequestID OR @RequestID IS NULL)
        AND (rh.RequestName = @RequestName OR @RequestName IS NULL)
    ORDER BY rh.RequestName
        , rh.StatusDate;
END
GO

Running the RequestHistory proc against the sample data:

EXEC dbo.RequestHistory @RequestName = 'Request #2';


Files copying to different machine not happening from batch file executed from SQL Jobs

For any interaction with the Operation System, insure that the user services of SQL Server has the privileges


Remove your PARTITION clause. The window functions are calculated within the PARTITION itself, and it sounds like you're trying to rank the data globally (i.e. uniquely rank the addresses globally).
Or adjust the PARTITION clause to reflect the window of what you want the rank to be calculated across. For example, if you wanted to rank the data within the field arcs_name1 then you'd use PARTITION arcs_name1 only (but leave your ORDER BY clause as is) and the ranking would only be within the grouping of each value of arcs_name1 (resetting it's counter between each unique value of arcs_name1).


How to refresh AD security group on Sql Server permissions

The user need to log off of their workstation and log back on.  That's why the changes appear to take effect the next day.  The reason for this is that when the user logs on the next day they get a new token from the domain controller and this token contains the list of domain groups that they are a member of.  This token with the list of domain groups is only updated when the user logs in to their computer, so if the user never logs out the token isn't ever updated.

There's also multi-site domain replication delays which need to be taken into account if your domain controllers are in different physical locations.


Adding Partitions to cube In SSAS via VS 2013

I've found a solution!!! :)

Thanks to Darren Gosbell(MCC, MVP) and bayFX BITALENT(Partner) on MSDN.

This appears to be caused by a missing dll. For VS2012 / SQL 2012 running the following command fixed this for me.

copy "c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbench.Interfaces.dll"
"c:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies"

For Visual Studio 2013 ( Version 12.0.21005.1 REL ) and SQL Server 2014 you can use this paths.

Copy "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbench.Interfaces.dll" file to "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies" folder.

Restart Visual Studio and Voila!


How to union tables into a new table in mysql?

I just loaded this into MySQL 5.5.37 on my Windows Laptop
use test
drop table if exists tbl1;
create table tbl1
(id int not null,
col1 int not null,
col2 int not null,
col3 int not null,
type int not null);
insert into tbl1 values
(1,  12,   12,   23,   1),
(2,  23,   33,   1,    0),
(3,  333,  2,    21,   1);

Then, I tried out the query
mysql> SELECT *
    -> FROM tbl1
    -> WHERE type=1 AND RAND() <= 0.01
    -> UNION
    -> SELECT *
    -> FROM tbl1
    -> WHERE type =0 ;
+----+------+------+------+------+
| id | col1 | col2 | col3 | type |
+----+------+------+------+------+
|  2 |   23 |   33 |    1 |    0 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql>

Your syntax is just fine !!!
Your error message has UNION SELECT * SELECT * as the problem. Take a closer look at the message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
SELECT *
SELECT *
FROM tbl1
WHERE type =0
)' at line 3

I see SELECT * twice and a trailing right parenthesis that you did not type in the query.
If your version of MySQL consistently produces that error message, then you must have a bug within in the Query Parser. You may need to upgrade MySQL as a last resort.
SUGGESTION
Your CREATE TABLE will not have any indexes.
Try creating the table with indexes and loading afterwards
create table tbl2 like tbl1;
insert into tbl2
    SELECT *
    FROM tbl1
    WHERE type=1 AND RAND() <= 0.01
    UNION
    SELECT *
    FROM tbl1
    WHERE type =0
;


Converting columns from NVARCHAR to VARCHAR

This database is only for US use and I don't think there are any foreign characters or letters in any of the columns so I wanted to look into ... Changing many of these columns over from NVARCHAR to VARCHAR

Before you go any further you need to research / determine the actual business requirements of these columns. If it's true that one or more of these columns are not currently holding any characters that wouldn't just as easily fit into VARCHAR, that doesn't mean that someone won't try to enter Unicode-only characters tomorrow, and that doing so would be outside of the intended design of the system. If end users / sales people / support people / the documentation believe that Unicode-only characters can be  entered into the system, then the system needs to allow for that, even if nobody has made use of that functionality yet. If this is the case and you feel it should be changed because you don't think there is a need to enter such characters, then you need to bring that up to a manager / Product Owner / etc.

Also, just because the intended user base is in the US doesn't mean that Unicode-only characters won't be used. At the very least in "name" columns, "description" columns, etc there could easily be a variety of accented characters, or who knows what. The discussion that you should have with the Product Owner and/or team should revolve around what is required for each of these columns / data points.

  I know for example that the max length of the data in some of these columns are only around 30 characters so NVARCHAR(MAX) seems like overkill.

Similar to the points made above regarding what types of characters should be allowed in these columns, you first need to determine how many characters the system allows people to send in. If a description field is capped at 500 - 1000 characters in the UI, and other parts of the code, and even the documentation (one can always hope, right?), agree, then absolutely yes, NVARCHAR(MAX) is absolutely overkill. BUT, if the column needs to store more than 4000 characters of a variety that is not represented by an 8-bit code page, then NVARCHAR(MAX) is not overkill (though you might need to consider a better design than storing too many of them in a single table). Either way, the fact that there are only 30 characters max in a particular NVARCHAR(MAX) column is good information to provide to a Product Owner / team so that a more informed decision can be made regarding the fate of that column. Perhaps a field that currently allows for 1000 characters but has a max record length of 30 - 50 should be updated to only allow maybe 75 - 100. But that needs to be a coordinated effort.

All of that said, if you want to determine if there are any characters in an NVARCHAR / NCHAR / NTEXT column that cannot be converted to VARCHAR, you need to convert to VARCHAR using the _BIN2 variation of the collation being used for that particular column. For example, if a particular column is using Albanian_100_CI_AS, then you would specify Albanian_100_BIN2 for the test. The reason for using a _BIN2 collation is that non-binary collations will only find instances where there is at least one character that does not have any mapping at all in the code page and is thus converted into ?. But, non-binary collations do not catch instances where there are characters that don't have a direct mapping into the code page, but instead have a "best fit" mapping. For example, the superscript 2 character, ², has a direct mapping in code page 1252, so definitely no problem there. On the other hand, it doesn't have a direct mapping in code page 1250 (used by the Albanian collations), but it does have a "best fit" mapping which converts it into a regular 2. The problem with the non-binary collation is that 2 will equate to ² and so it won't register as row that can't convert to VARCHAR. For example:

SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE French_100_CI_AS); -- Code Page 1252
-- ²
SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_CI_AS); -- Code Page 1250
-- 2

SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_CI_AS)
WHERE  N'²' <> CONVERT(NVARCHAR(MAX),
                       CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_CI_AS));
-- (no rows returned)

SELECT CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_BIN2)
WHERE  N'²' <> CONVERT(NVARCHAR(MAX),
                       CONVERT(VARCHAR(MAX), N'²' COLLATE Albanian_100_BIN2));
-- 2

Ideally you would convert back to NVARCHAR explicitly for the code to be clear on what it's doing, though not doing this will still implicitly convert back to NVARCHAR, so the behavior is the same either way.


Firstly, you must enable flashback. In SQL*Plus as SYSDBA:

shutdown immediate;
startup mount;
alter database flashback on;
shutdown immediate;
startup;

Next before your load operation, create a restore point:

create restore point before_load;

Now do you work, and when you're done:

shutdown immediate;
startup mount;
flashback database to restore point before_load;
shutdown immediate;
startup;

And your data is back as it was. You can repeat this as many times as you like, and when you are done, drop the restore point. HOWEVER the commenters are correct; you should leave the constraints enabled and just defer them.


Postgres: why did this trigram index slow down regexp queries?

I expected the trigram index to figure out immediately there's no second 0 string anywhere

'second' and '0' are separate words, so it cannot detect their joint absence as such.  It seems like it could detect the absence of '  0', but this comment from "contrib/pg_trgm/trgm_regexp.c" seems pertinent:

 * Note: Using again the example "foo bar", we will not consider the
 * trigram "  b", though this trigram would be found by the trigram
 * extraction code.  Since we will find " ba", it doesn't seem worth
 * trying to hack the algorithm to generate the additional trigram.

Since 0 is the last character in the pattern string, there will be no trigram of the form " 0a", either, so it just misses that opportunity.

Even if it were not for this limitation, your approach seems extremely fragile.


SQL Server - What access do I have during a transaction rollback?

The answer really depends largely on your isolation level, what type of locks are held by the transaction being rolled back, and what your non-rollback session is trying to do.

Step 1) What is locked?

Before the ROLLBACK was issued, that transaction had done work and acquired locks. You can see details on those locks by looking in sys.dm_tran_locks:

SELECT tl.resource_type, tl.resource_associated_entity_id,
       tl.request_status, tl.request_mode, tl.request_session_id,
       tl.resource_description
FROM sys.dm_tran_locks tl
WHERE tl.request_session_id = <ROLLBACK session id>;

The resource_type and resource_associated_entity_id will tell you what is locked. If the resource_type is "Object" then resource_associated_entity_id is an object_id. If resource_type is "Key" or "Page" then resource_associated_entity_id is a hobt_id. There are more types to decode, but those two cover most scenarios.

SELECT tl.resource_type, tl.resource_associated_entity_id,tl.resource_database_id,
       CASE WHEN resource_type = 'OBJECT'
                THEN object_name(tl.resource_associated_entity_id,tl.resource_database_id)
            WHEN resource_type IN ('KEY','PAGE')
                THEN object_name(p.object_id)
        END AS resource_name,
       tl.request_status, tl.request_mode, tl.request_session_id,
       tl.resource_description
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id
                           AND tl.resource_database_id = db_id()
WHERE tl.request_session_id = <ROLLBACK session id>;

Lastly, the lock mode gives information on how that lock is being used. BOL gives a rundown of all the different lock modes.

Step 2: What will be blocked?

If the transaction being rolled back does not have a lock on an object, then the ROLLBACK won't block other sessions. That part is easy.

Whether these locks cause blocking is going to depend on your isolation level, and what you're trying to do. There's a very in-depth series on isolation levels by Paul White, which you could read for lots of details.

Different combinations of reading, writing, and isolation levels will have different blocking outcomes. Generally speaking, writes always block writes. However, writes may or may not block reads. Under SQL Server's default READ_COMMITTED isolation level, writes will block reads. If you are using READ_COMMITTED_SNAPSHOT (aka "RCSI" aka "Read Committed with Row Versioning"), then writes will not block reads.

In all cases, if another session is blocked by the session in ROLLBACK, the waiting session would just wait until the ROLLBACK released the lock.


Slow MYSQL performance due to sub queries

Of course it is the subqueries' fault. For each row of table main, this query goes through the whole transactions table twice (once for each subquery). That's a lot of load.

How about a simple join? This should have the same results and take much less than 50% of your processing time even with no indexes.

SELECT main.BAL,main.NAME,SUM(transaction.DR),SUM(transaction.CR)
FROM main
LEFT JOIN transaction on transaction.TNAME = main.NAME and transaction.DATE < '2000-05-01'
GROUP BY main.MARK,main.NAME WITH ROLLUP


Import .bak file in Microsoft SQL Server 2008 Service Pack 3

Your backup is from a database that is using Enterprise Edition features. You are trying to restore it to an Express Edition server. That will not work.

The message in your error log is telling you exactly that:

  Database 'FieldopsDevDynamic07042013' cannot be started because some
  of the database functionality is not available in the current edition
  of SQL Server.

The error log even tells you which feature is causing the problem:

  Database 'FieldopsDevDynamic07042013' cannot be started in this
  edition of SQL Server because it contains a partition function...

Try restore to an Enterprise or Developer edition server. If it still fails update your question with the result of your attempt.


The issue related to JSONB columns is a known limitation of DMS. In particular, if you're using DMS to migrate from your EC2 instance to RDS Postgres, it will convert the type to varchar(765) and truncate any JSONB column longer than 756 chars.

https://forums.aws.amazon.com/message.jspa?messageID=735961

We heard during AWS Summit - San Francisco that the DMS team is planning a fix for this issue. For an update, please feel free to comment on the message itself.


Grant permissions to a user to grant select to specific tables in several schemas in Oracle

You probably want GRANT ANY OBJECT PRIVILEGE

AS DBA:

GRANT GRANT ANY OBJECT PRIVILEGE TO dwhmanager;

AS dwhmanager:

grant select on DWHFINANCE.SALES TO johndeveloper;

Presto!


Hash function and custom C type for PostgreSQL

The hash function in this context is used to tranform the set of all possible type values into a substantially smaller set of their hash values. The hash join works by separating values it needs to compare for equality into buckets based on their hash values. Values producing the same hash and therefore falling in the same bucket have a chance to be equal, while values in different buckets cannot be equal and thus can be removed from consideration.

One trivial example of a 10-bucket hash for integer numbers would be a function returning the least significant digit of its parameter. You'll probably be able to use the built-in hash_any function in your case, but read the caveats about HASHES in the manual.


How to determine the sweet spot between pool size and database connections in PostgreSQL

The short answer here is "trial and error guided by monitoring and performance metrics".

There are some general rules of thumb that should help you find the vague area you should start in, but they're very general. The broad guidelines "number of CPUs plus number of independent has disks" is often cited, but it's only an incredibly coarse starting point.

What you really need to do is get robust performance metrics in place for your application. Start recording stats.

There isn't much in the way of integrated tooling for this. There are things like the nagios check_postgres script, Cacti system performance counter logging, the PostgreSQL statistics collector, etc ... but there isn't much that puts it all together. Sadly, you'll have to do that bit yourself. For the PostgreSQL side, see monitoring in the PostgreSQL manual. Some third party options exist, like EnterpriseDB's Postgres Enterprise Monitor.

For the application-level metrics mentioned here you will want to record them in shared data structures or in an external non-durable DB like Redis and aggregate them either as you record them or before you write them to your PostgreSQL DB. Trying to log directly to Pg will distort your measurements with the overhead created by recording the measurements and make the problem worse.

The simplest option is probably a singleton in each app server that you use to record application stats. You probably want to keep a constantly updating min, max, n, total and mean; that way you don't have to store each stat point, just the aggregates. This singleton can write its aggregate stats to Pg every x minutes, a low enough rate that the performance impact will be minimal.

Start with:

What's the request latency? In other words, how long does the app take from getting a request from the client until it responds to the client. Record this in aggregate over a time period, rather than as individual records. Group it by request type; say, by page.
What's the database access delay for each query or query type the app executes? How long does it take from asking the DB for information / storing information until it's done and can move on to the next task? Again, aggregate these stats in the application and only write the aggregate info to the DB.
What's your throughput like? In any given x minutes, how many queries of each major class your app executes get serviced by the DB?
For that same time range of x minutes, how many client request were there?
Sampling every few seconds and aggregating over the same x minute windows in the DB, how many DB connections were there? How many of them were idle? How many were active? In inserts? Updates? selects? deletes? How many transactions were there over that period? See the statistics collector documentation
Again sampling and aggregating over the same time interval, what were the host system's performance metrics like? How many read and how many write disk IOs/second? Megabytes per second of disk reads and writes? CPU utilisation? Load average? RAM use?

You can now start learning about your app's performance by correlating the data, graphing it, etc. You'll start to see patterns, start to find bottlenecks.

You might learn that your system is bottle-necked on INSERT and UPDATEs at high transaction rates, despite quite low disk I/O in megabytes per second. This would be a hint that you need to improve your disk flush performance with a battery backed write-back caching RAID controller or some high-quality power-protected SSDs. You could also use synchronous_commit = off if it's OK to lose a few transactions on server crash , and/or a commit_delay, to take some of the syncing load off.

When you graph your transactions per second against the number of concurrent connections and correct for the varying request rate the application is seeing, you'll be able to get a better idea of where your throughput sweet spot is.

If you don't have fast flushing storage (BBU RAID or fast durable SSDs) you won't want more than a fairly small number of actively writing connections, maybe at most 2x the number of disks you have, probably fewer depending on RAID arrangement, disk performance, etc. In this case it isn't even worth trial and error; just upgrade your storage subsystem to one with fast disk flushes.

See pg_test_fsync for a tool that'll help you determine if this might be a problem for you. Most PostgreSQL packages install this tool as part of contrib, so you shouldn't need to compile it. If you get less than a couple of thousand ops/second in pg_test_fsync you urgently need to upgrade your storage system. My SSD-equipped laptop gets 5000-7000. My workstation at work with a 4-disk RAID 10 array of 7200rpm SATA disks and write-through (non-write-caching) gets about 80 ops/second in f_datasync, down to 20 ops/second for fsync(); it's hundreds of times slower. Compare: laptop with ssd vs workstation with write-through (non-write-caching) RAID 10. This laptop's SSD is cheap and I don't necessarily trust it to flush its write cache on power-loss; I keep good backups and wouldn't use it for data I care about. Good quality SSDs perform just as well if not better and are write-durable.

In the case of your application, I strongly advise you to look into:

A good storage subsystem with fast flushes. I cannot stress this enough. Good quality power-fail-safe SSDs and/or a RAID controller with power-protected write-back cache.
Using UNLOGGED tables for data you can afford to lose. Periodically aggregate it into logged tables. For example, keep games-in-progress in unlogged tables, and write the scores to ordinary durable tables.
Using a commit_delay (less useful with fast-flushing storage - hint)
Turning off synchronous_commit for transactions you can afford to lose (less useful with fast-flushing storage - hint hint)
Partitioning tables, especially tables where data "ages out" and is cleaned up. Instead of deleting from a partitioned table, drop a partition.
Partial indexes
Reducing the number of indexes you create. Every index has a write cost.
Batching work into bigger transactions
Using read-only hot standby replicas to take the read load off the main DB
Using a caching layer like memcached or redis for data that changes less often or can afford to be stale. You can use LISTEN and NOTIFY to perform cache invalidation using triggers on PostgreSQL tables.

If in doubt: http://www.postgresql.org/support/professional_support/


Cassandra definitely is more suitable for storage of metadata only, when you have big payloads, it's performance isn't very good. Similar stuff was for HBase when I did use it several years ago.

For storage of binary data itself, I maybe would go with something S3-compatible.


restoring db getting "no files are ready to rollforward" error

I recommend you to use different backup file names when you do log backup and full backup, so you can do backup like the following

-- first do the full backup
BACKUP DATABASE [ICT_IdealComputer]
TO DISK = 'C:\ICT\full_ICTBackup.bak'
WITH NAME = 'ICT-Full Database Backup',
SKIP, init
NOUNLOAD,
STATS = 10

-- 2nd do the log backup
BACKUP LOG [ICT_IdealComputer]
TO DISK = 'C:\ICT\log_ICTBackup.bak'
WITH NAME = 'ICT-Transaction Log Backup',
SKIP, init,
NOUNLOAD,
STATS = 10

Then you do restore as follows:

USE [master]
GO
-- first restore from full bkup
RESTORE DATABASE ICT_IdealComputer
FROM DISK = 'C:\ICT\full_ICTBackup.bak'
WITH REPLACE, NORECOVERY,
MOVE 'ICT_IdealComputer' TO   'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer.mdf',
MOVE 'ICT_IdealComputer_log' TO 'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer_log.ldf';

-- then restore from log backup

RESTORE LOG ICT_IdealComputer
FROM DISK = 'C:\ICT\log_ICTBackup.bak'
WITH RECOVERY;


You can use SQL Power doc for this .

SQL Power Doc was written by Kendal VanDyke. It is a command line PowerShell manifest consisting of several PowerShell modules that will help you build a solid foundation to Discover, Document and Diagnose SQL Server instances and Windows Operating system details. Additionally, you’ll gain some insight into what directions you can go for future exploration. It basically invokes PowerShell cmdlets and give you the necessary details front and center.

How to use has been described here

You can just filter out in the report for the objects you want or even before executing PS you can adjust the parameters as needed.


Is updating with value using a subquery bad in performance?

Assuming Sql Server as your DBMS:

The following two statements are NOT equivalent:

update table_a --will update some_column to null when no match to table_b on id
set some_column = (
    select very_complicated_expression_involving_multiple_columns_in_table_b
    from table_b
    where table_a.id = table_b.id
)

UPDATE a --will not update table_a rows that don't match table_b on id
SET a.some_column = very_complicated_expression_involving_multiple_columns_in_table_b
FROM table_a a
JOIN table_b b ON b.id = a.id

The first UPDATE will update some_column to NULL if there is no match between table_a and table_b on id.

The second UPDATE won't update any table_a rows where there is no match between table_a and table_b on id.

Having said that, and back to your original question about performance, I would think the optimizer would NOT choose any kind of LOOP JOIN when there are so many rows involved in the update (assuming your statistics are up to date).  Evaluating different estimated execution plans for different flavors of the update will help you decide which is the most efficient.

There are other things that also need to be considered when updating a large table.

An update against a large table will generated a lot of transaction log entries.  You should consider breaking up the update into smaller chunks to reduce the possibility of running out of log space.  If you're running in FULL recovery mode, make sure you are taking log backups in a timely manner.  You would need to determine a method of identifying rows that have already been updated in previous runs.  Break large delete operations into chunks is a post about breaking up large deletes, but I think the idea can be applied to updates.
If some_column is referenced in any indexes on table_a, you should consider dropping/disabling them.  Naturally, they will have to be rebuilt afterwards.
Check for any UPDATE triggers on table_a that might impact performance.


MySQL Syntax error when trying to create database trigger

ENDIF;
END$$
DELIMITER;

-->

END IF;
END $$
DELIMITER ;

END IF is 2 words.
Have a space before the delimiter.


Trim characters from a field but only if it doesn't result in a duplicate?

This will check to make sure the value you are writing doesn't already exist in that field:

UPDATE s
SET    ServerName = replace(ServerName, '.domain.com', '')
FROM serverdata S
WHERE  ServerName LIKE '%.domain.com'
AND NOT EXISTS (SELECT 1
                FROM serverdata s2
                WHERE s2.servername = <> replace(s1.ServerName, '.domain.com', ''))


How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server?

User Defined Types (UDT) in Oracle

Many 3rd party software cannot handle UDTs.  These include Oracle APEX, and SSIS.

This is only one, of many, problems with using a UDT for a Column's data type.

MS SQL

I don't know if MS-SQL has the same problem with UDTs.  I'm guessing "yes".

I'm guessing LOV is a TABLE OF ... data type.  If so, create a child table of the type's attributes along with a foreign key to the Listing table.

SSIS

To enable SSIS to see the data, I recommend you CREATE VIEW.

create or replace
view Listing_LOVs
as
select a.id, b.attribute_1, b.attribute_2, ...
from Listing a, a.List_of_values b;

Then:

Transfer all Listing columns (except List_of_Values) to the MS-SQL Listing table.
Transfer all the Listing_LOVs values the the child table.

XML Version

Per Laughing Vergil's comment:  XML Document could be another method.

create or replace
view Listing_VIEW
as
select a.id, a.values, a.id
  ,SYS_XMLGEN( a.List_of_values).GetCLOBVal() List_of_Values_XML
from Listing a;

The column List_of_Values_XML will appear as a CLOB to 3rd party apps (eg SSIS)


PgBouncer "no more connections allowed (max_client_conn)"

The point of using PGBouncer is to let it pool connections to the PG server.  If your PG max_connections = 100 and PGBouncer max_client_conn = 100 then you aren't pooling anything.  You should set your PGBouncer max_client_conn to a much higher number to allow it to marshal actual PG connections to service the clients.  I've seen max_client_conn set to default_pool_size * # databases * user count * some arbitrary number (10) to allow enough PGBouncer connections to service all the databases connected to by all the users with an arbitrary (10) connection multiplier.  For example, I have a PG server with one database, max_connections = 100, a PGBouncer default_pool_size of 20 and a PGBouncer max_client_conn = 1000.  That effectively allows PGBouncer to pool 20 connections to my single database while allowing my client 1000 connections to process it's data.  (I'm capturing a lot of data real time from a web api).


CONCAT for string concatination.

Note: PIVOT, SYSDATE also isn't a syntax in MySQL

MariaDB-10.3 has an ORACLE MODE which might make your transition easier.


What are the cons of having a database server in a Docker container for production purposes?

Docker containers are intended to isolate environments used by different users and/or versions of software to minimize spends during the development time. Production server shouldn't be the home for everyone and shouldn't have different versions of software "just for testing". Therefore there is no reasons to put services into containers unless you are not a VPS-hoster.


How to run a SELECT query within while loop in PHP?

This is for all intents and purposes, a Cartesian Join.

It is definitely out of the question to join every row and pass through the data a single time since you do not know the running time or the amount of temp table space needed.

Perhaps you could do the following:

If table1 is small and table2 is small

Run SELECT * FROM table1; and load it into an array in memory
Run SELECT * FROM table2; and load it into an array in memory
Iterate the outer and inner loop over the arrays only

If table1 is large and table2 is small

Run SELECT * FROM table2; and load it into an array in memory
The outer for loop could still pass through table1 but the inner loop reads the data from table2 from the array.

If table1 is large and table2 is large

Run SELECT * FROM table1; and load it into a local text file
Run SELECT * FROM table2; and load it into a local text file
Iterate the outer and inner loop over the text files

If the DB Server has 60G of RAM (RISKY)

Performs the Cartesian Join anyway
SELECT A.*,B.* FROM table1 A,table2 B;

I also suggest not using SELECT * but using SELECT col1,col2,... reading only needed columns. This would significant reduce round trip times for queries.

The point behind these suggestions is to avoid a process of opening and close threads of data that is fetch repeatedly.

UPDATE 2013-06-04 18:47 EDT

Perhaps you could write a stored procedure, run the SP to collect all the data on the server side, then simply read the data in one pass. Again, this will reduce rounds trips of calls, opening and closing DB Connections. All the PHP logic would thus be done is the SP.


How to convert a table into second normal form

1NF: Only the key
    Remove repeated data/data groups
    i.e. having a person and address table instead of single mega-table
2NF: The whole key
    Ensure that all nonkey columns are dependent on the whole key
    So with a table where employee name and location could be a primary key,
    and there is a skill attribute the skill attribute isn’t dependent
    on the whole of the key as
    it isn’t dependent on location therefore must be split into two tables
3NF And nothing but the key
    Should not have any dependencies on things other than the primary key

Now this seems like a bit of a homework question so I won't do it for you but make a module table, make a room table. If you want third normal form making a booking table with module code and room number as a compound primary key, and the fields relevant to a booking.

Remember normalisation is to make all the data related and remove repetition.


What specific tests need to be performed under Database migration from DB2 to Oracle?

Normally for a straight forward data migration it is enough to know that all data is complete, valid and accessible for the application. Typically subtle differences may pop-up with some data-types like those with date, time, binary fields and long text fields.

In a straight forward data migration, data from table a field a goes to the new system in the same table, the same field. A good check to validate this could be done by computing a MD5 check-sum on all fields of all tables, both in source and target tables. Any difference here shows there is a problem in the migration procedure or software. Make sure to have the same formats in place for the MD5 computation; for example, make sure that the date formats are the same on both sides.

The application has to be tested, that's for sure. What the strategy should be is hard to tell, every application has it's own challenges. If all logic is known and standardized, it could work by making a simple test application that accesses all different field types and see how the interaction goes. In the end a regression test will be needed.


Stats Maintenance after Restoring to Newer SQL Server

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version

As a side note, please don't use sp_MSforEachtable - Its not supported. There is a clever way of doing it.

Compatibility level matters - see What is the actual behavior of compatibility level 80? and Clinging to old compatibility levels form Aaron Bertrand


PostgreSQL: remaining connection slots are reserved for non-replication superuser connections, too many clients already

max_connections = 1024 ?! You need a connection pool.

Use PgBouncer in transaction-pooling mode if your app doesn't support built-in pooling.

You're running PostgreSQL on a tiny toy server. Keep the active connection count low and queue work up in series.

High max_connections introduces significant inefficiency, and having lots of actively working connections adds more.


Installed Oracle XE 11.2. How do I create a database?

Oracle 11 does not use containers so you already have a database and the default users such as sys and system and all the other oracle built in users.

Go to Computer Management/Local Users and Groups and verify that the group ora_dba exists.
Assuming this is a development project add your regular windows username to the ora_dba group. Now you can log on as sysdba using these steps:

open a command window
enter SET ORACLE_SID=XE
enter sqlplus / as sysdba

This should get you logged on as sys which you should not do normally.  For this one time create a user and grant them a role.

CREATE USER < your new user name > IDENTIFIED BY < your new password >;
GRANT DBA to < your new user name>;

Then enter exit to close the session.  Log on as your new user using Toad and you should be good to go.

I note that the operating system and database are out of support and you would be better off using the new 18xe on linux.


Sure you can, that's what I do with my projects. Have a look:

Create a new Project, I use SQL Server Database Project because I generally end up adding more than just scripts.

Add items. Scripts in this case.

Go the Team Explorer and select GitHub (or TFS if that's what you're going to work with)

I'm going to create a new Private Repository.

Done!

Now Push the changes...

Finish!


How to select multiple rows using one query

The first query is the fastest, with an iNDEX on order_number

CREATE TABLE mytable (col1 int, col2 int, order_number varCHAR(50))
GO

SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value1'
UNION
SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value2'

GO

col1 | col2
---: | ---:

SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value1' OR order_number = 'unique_value2'
GO

col1 | col2
---: | ---:

SELECT col1, col2
frOM mytable
WHERE order_number IN ( 'unique_value1' ,'unique_value2')
GO

col1 | col2
---: | ---:

db<>fiddle here


What is the meaning of different roles available(DB Admin etc.)

Roles and groups are not supported by MySQL Server. Groups are sets of users. The concept of roles is a group of permissions that can be assigned to a group or a user.

Workbench emulates roles by handling them at client side: Adding a role on workbench. You can access this tab on the Users and privileges tab.

  I want the user to simply create tables, must be able to perform
  delete and update operations but restrict him from able to change the
  password and view the complete database available (all the records).

In order to do what you want, you can add a new role on the previous mentioned tab with no administrative privileges and "all object rights" and "DDL rights" for a particular schema (Add entry...). You can fine-grain it more by not adding INSERT rights or DROP right, as you see it conveniently. If you restrict it to a single schema, it won't be able to see or modify any other table from others, except the administrative schema information_schema.

If you feel like creating the user by using SQL (when connected as an administrator):

CREATE USER <username>@<host> IDENTIFIED BY '<password>';
GRANT ALL ON <schema>.* TO <username>@<host>;

Obviously, change <username>, <host>, <password> and <schema> for the right values.

Please note that a user in MySQL can always change its own password, but not that of others (unless it is an administrator).


The ERD is simply a product of the database design phase. You can skip drawing the ERD if you can complete design in your head, but you cannot (should not) skip designing your database.


First, run your query and make a note of your SESSION_ID.

Then, run these two queries to see how much TempDB space your original query is using. Be sure to update them with your SESSION_ID.

-- TempDB session usage
select *
from sys.dm_db_session_space_usage
where session_id = 123
go

-- TempDB task usage
select *
from sys.dm_db_task_space_usage t
where session_id = 123
go

Also, run this query to show us the layout of your TempDB. Please post the results.

use tempdb
go
exec sp_helpfile
go


This can be easily checked. Here's code which should imitate your scenario:

ALTER DATABASE [playground] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO
CREATE SEQUENCE dbo.Seq1
    START WITH 5
    INCREMENT BY 5 ;
GO

CREATE SEQUENCE dbo.Seq2
    START WITH 5
    INCREMENT BY 5 ;
GO

DBCC FREEPROCCACHE()
GO

SELECT NEXT VALUE FOR dbo.Seq1;
GO
SELECT NEXT VALUE FOR dbo.Seq2;
GO

Now if you check plan cache:

SELECT text, plan_handle, qs.execution_count FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) as qt

You can clearly see there are two distinct plans for two sequences:

This is what I expected. It works only for parameters and not for object names.


How to disable ChunkManager logging for MongoDB? mongoconfig.log gets huge

I have not tested this myself.

Reference:

https://docs.mongodb.com/manual/reference/configuration-options/#systemlog-options
https://docs.mongodb.com/manual/reference/program/mongos/
https://docs.mongodb.com/manual/reference/command/

You can do this by:

  systemLog.quiet Type: boolean

  Run mongos or mongod in a quiet mode that attempts to limit the amount
  of output.

systemLog.quiet is not recommended for production systems as it may make tracking problems during particular connections much more difficult.

  --quiet

  Runs mongos in a quiet mode that attempts to limit the amount of output.

  This option suppresses:

  output from database commands
  replication activity connection accepted
  events connection
  closed events

All Sharding Commands are listed under database commands which I assume will suppress the logging you are referring to.


Mirroring Database tables and views on SQL Server Express

This is not a question of replication, nor mirroring. Is about updating the app.

You should have a deployment strategy for your app, including upgrade. Updating the 'demo' would simply mean running the normal deployment of you app. Read Version Control and your Database. Study Rails Migrations, they're a great example of deployment control. Read about EF Code First. Stop updating the DB directly. think in term of source files.


Following can be used to clean adhoc and prepared query plan cache . This is from Glenn Berry - Eight Different Ways to Clear the SQL Server Plan Cache

DBCC FREESYSTEMCACHE ('SQL Plans')

It also says:

  “I would argue that running DBCC FREEPROCCACHE does not cause that much distress with a modern processor, even on a very busy OLTP system. It will cause a pretty minor CPU spike for a few seconds on most systems as the query plans get recompiled as they are executed. “

Following query can give adhoc queries in the plan cache

SELECT  DISTINCT
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
        ,size_in_bytes
        ,qStat.creation_time
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Adhoc'
ORDER BY MultipleQ.PlanCount DESC

If you have Query Store you can use “Delete ad-hoc queries” as defined in
Monitoring performance by using the Query Store

Enabling optimize for adhoc workloads can be found in  Kimberly Tripp - Plan cache and optimizing for adhoc workloads


What are the limits on the amount of slaves per master?

There are no limits in terms of settings, but you need to be cognizant of some aspects.

You could use semisynchrnous replication as it uses a nice algorithm for shipping SQL to Slaves by making sure at least one server has all the latest SQL in the Slaves relay logs. (See my August 5, 2011 post Is MySQL Replication Affected by a High-Latency Interconnect?)

However, semisynchronous replication is only good for one out of N Slaves.

Whether you have semisynchronous replication or not, it would suffice to say that the more Slaves you have, the more CPU processing a Master has to do to keep its Slaves up-to-date.

For a Master with N Slaves, that's...

N SQL Statements to send to Slave
N Acknowledgments from Slaves 1-N that SQL was Executed

With semisynchrnous replication, it is not much better because For a Master with N Slaves, that's ...

N SQL Statements to send to Slave
Acknowledgments from Slave

1 Acknowledgment that a Slave 1 received the latest SQL
N-1 Acknowledgments from Slaves 2-N that SQL was Executed

If you have to have many Slaves, think of using a Distribution Master in a Star Topology

Best way to setup master to multi master replication
Is it a good idea to split MySQL DB into two servers
Infrastructure for Highly Concurrent, High Write DB
https://stackoverflow.com/a/5784578/491757

If you cannot setup a Distribution Master in a Star Topology, then here is my simple rule: Use Semisynchrnous Replication but use as few Slaves as possible.


Check out Analytical functions. Here's how you could solve your problem using the RANK() analytical function in Oracle (for simplicity I select from a table called "courses" and not your subquery; the table has the same structure as the one in your picture):

select * from (
    select coursedept,
           coursenum,
           numstudent,
           rank() over(partition by coursedept order by numstudent) num_s_rank
     from courses
 ) where num_s_rank = 1;

Note that in case of ties multiple rows get the same rank, which means you could get more than one course per department.


A script like this could help you:

isql -Usa -S${ASE_Servername} <<EOF
$password

set nocount on
go
select 'sp_depends '+name from <dbname>..sysobjects
where type='P' and name in ( <list_of_SPs> )
go

EOF


You need to change you GROUP BY and SELECT clauses. Also, I'd use Order_ID instead of description to sort.

Select a.userid ,
       SUM(a.orderqty) AS OrderQtyTotal ,
       SUM(a.orderprice) AS OrderPriceTotal ,
       SUM(a.orderqty*a.orderprice) AS OrderTotal ,
       a.orderdesc ,
       MAX(a.orderdate) AS LatestOrderDate ,
       MAX(b.orderstatus) AS LatestOrderStatus ,
       MAX(b.orderreviewstatus) AS LatestOrderViewStatus
FROM #first a INNER JOIN #second b ON a.userid = b.userid
GROUP BY a.userid, a.orderdesc


why i am getting The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

There are two possibilities here:

Your table create script includes an attempt to send an email
Your DBA has placed a DDL trigger on the database to send an email notification when objects are created.

In the first instance, confirm that there are no attempted email sends from your script.

For the second the DBA might not have set the permissions correctly on the trigger itself, and so it is attempting to send an email using your credentials.
Work with the DBA to either grant you permission on msdb..sp_send_dbmail so that emails will flow, or to fix up the trigger.


Viewing Type Of Lock Being Used T-SQL - Update Lock Query Issue

I you try to find out which lock is currently been taking. Just take a short look in the tran_locks. There you find all current locks.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID() and request_session_id = [YOURSESSION]

Hope this helps you.

Another way could be the usage of WITH(HOLDLOCK) on your statement and append this code to the end of your transaction.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID() and request_session_id = @@SPID


Binlog - 'Race-like' Condition on Replication

Replication is always serialized to prevent the race-conditions you are speaking about.  See this stackoverflow post for my explanation of InnoDB locking.  Some important notes:

RBR with row-based replication and read-committed isolation level sets fewer locks than InnoDB would have previously.
We should be moving to a world with RBR anyway, because this will open more doors for parallel replication threads (by working out dependencies between transactions).

To answer your last question, I use RBR in production, and I haven't seen any major performance hit.  We do generate a lot more binary log data however, since the events logged contain whole rows.


How to set IP segment and connection string with IPv6?

Answer 1:

Like the documentation says:

  An IPv6 address range might look like ::1/128 for a single host (in this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96 for a small network. 0.0.0.0/0 represents all IPv4 addresses, and ::0/0 represents all IPv6 addresses. To specify a single host, use a mask length of 32 for IPv4 or 128 for IPv6. In a network address, do not omit trailing zeroes.

Answer 2:

Like the documentation says:

  host

  The host name of the server. Defaults to localhost. To specify an IPv6 address your must enclose the host parameter with square brackets, for example:

jdbc:postgresql://[::1]:5740/accounting


If you want the second table to contain the same data as the first table, why not simply wipe the table out and replace it?

BEGIN TRANSACTION;

  DROP TABLE TransactionEntry;

  SELECT * INTO dbo.TransactionEntry FROM dbo.TransactionEntryN
    -- other WHERE clauses?
  ;

COMMIT TRANSACTION;
-- create indexes / permissions etc.


You are correct in your description but the conclusion is not correct.  It may still be faster to use the index anyway even if it has to go off to the table for the rest of it.  The data pages contain many fewer rows than the index so there's lots of extra I/O when scanning.  Generally the index will tend to give you benefits if it selects few rows even if it's not a great fit.


There is great reason why you should never call stored procedures from within triggers.
Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.
Don't forget that Triggers require overhead. In fact, According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:

It is important to remember that, by necessity, triggers add overhead
to the DML statement to which they apply. the actual amount of overhead
will depend upon the nature of the trigger, but --- as all MySQL
triggers execute FOR EACH ROW --- the overhead can rapidly accumulate
for statements that process large numbers of rows. You should
therefore avoid placing any expensive SQL statements or procedural
code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The concluding point from that section states the following:

The lesson here is this: since the trigger code will execute once
for every row affected by a DML statement, the trigger can easily
become the most significant factor in DML performance. Code inside the
trigger body needs to be as lightweight as possible and -- in
particular -- any SQL statements in the trigger should be supported by
indexes whenever possible.

I explained other nasty aspects of Triggers in an earlier post.
SUMMARY
I would strongly recommend not calling any stored procedures from a Trigger, even if MySQL allows it. You should wlays check out the current restrictions for MySQL 5.5.


There are various ways to set the search_path in PostgreSQL.

To diagnose, log into the same database as the same superuser and check:

SHOW search_path;

If it contains dbms_sql, then check your postgresql.conf, databse and role settings to find where it came from.

Use any editor or for instance grep on in a Linux shell to audit postgresql.conf:

grep 'search_path' /your/path/to/postgresql.conf

To check on database and role settings, use the default command line client psql or a graphical GUI like pgAdmin.

In pgAdmin you just select the object in the object browser to see all settings in the SQL pane.

In psql, use

\drds superuser

to see settings for this role. And

\drds '' db_name

to see settings for the database. Or just

\drds

to see all settings for all roles and databases.


How to select a column to compare two values in it with AND

If I understand right you need select records that equals two values. You can look this query like exsample:

SELECT firm_property.firm_id, firm.title
FROM  firm_property INNER JOIN
   firm ON firm_property.firm_id = firm.id
WHERE  (firm_property.property_id IN ('PKR3344', 'WWF1155'))
GROUP BY firm_property.firm_id, firm.title
HAVING  (COUNT(*) = 2)

If you write your tables and treir data we can make the better answer query.


MySQL: Is there a way to update a field so that all foreign key related fields are updated as well?

It is not possible to update the multiple tables interlinked with foreign keys. The constraints in MySQL only updates the related column.

Hope this helps!


Performance settings for MyISAM tables - keep everything in memory

MySQL only caches the indexes in MySQL's memory, not the data.  The data is only cached by the underlying operating system's filesystem caches.  The most important value to tune in my.cnf is key_buffer_size which will allocate memory for MyISAM to cache indexes.

However, unless you have some specific reason for using MyISAM tables you should  convert those tables to InnoDB.  In this day and age, there are less than a handful of reasons to use use MyISAM and MyISAM is not good for concurrency, data integrity, or crash recovery.

If you switch to InnoDB, the most important setting is innodb_buffer_pool_size which is the internal memory cache that will cache data pages & indexes.  Other things to consider are the innodb_log_file_size and make sure to set innodb_file_per_table=1.  With 4GB of data+indexes, setting  innodb_buffer_pool_size=4G is reasonable.  If you are running in a highly concurrent environment, you could experiment with values for innodb_thread_concurrency.  My recommendation is to start with innodb_thread_concurrency=0, but you might find it necessary to explicitly set it to be somewhere between 4-8 depending on your workload and version of MySQL.


Is sql_variant compatible with First Normal Form (1NF)?

1NF rule says that we should not keep data of different types in one single column.

There's no general agreement about exactly what first normal form (1NF) means in detail. Personally, I prefer Chris Date's current interpretation, which simply states that all relation variables (relvars) are in 1NF by definition.

  Does it mean that sql_variant is not compatible with first normal form hence should not be used?

This is a bit imprecise since normal forms are properties of the logical model, whereas sql_variant is a physical (SQL Server-specific) type. We would need to define a mapping from relational model type (or domain) to physical implementation for this to truly make sense.

Nevertheless, I will say that sql_variant is not inherently an indication that the associated model violates 1NF - so long as the model defines the range of allowable attribute values to match those storable in sql_variant (with or without suitable constraints). The fact that sql_variant can store values of several different underlying types is neither here nor there. We might as well argue that float types violate 1NF since they can contain integers as well as reals etc.

In other words, a physical table using sql_variant can be a valid representation of a relation variable (within the general limitations of SQL anyway). It simply depends on what the underlying logical model says about the range of values allowed in the attribute.

Consider also that whatever we might physically store in a sql_variant might also be stored in a string representation, or as a binary value. String and binary values do not necessarily violate 1NF, so why should sql_variant? Similar arguments can be made about xml or json (and array types for databases that support such things).

All of the above is orthogonal to the question of whether using sql_variant is a feature of a "good" design/implementation or not.

Further reading:

Facts and Fallacies about First Normal Form by Anith Sen
First Normal Form in Theory and Practice by Fabian Pascal
The Third Manifesto - C J Date and Hugh Darwen
First Normal Form: Definitive Definition (Q & A)

Footnote

In relational maths, some domains may have relations as elements.  If such a domain were used as the basis for an attribute in another relation, the result would not be in what Codd originally called normal form. Removing such relations from consideration without reducing the expressive power of the model was the whole point.  Relvars eliminate this kind of nesting by definition, as stated.  Beginners often get around the restriction in physical design by storing CSV strings in columns. There are consequences of such designs with regard to keyed access to data.


Help with good RDBMS transactional schema design that mimicks a sports league

I imagine that most sports leagues don't change their hierarchy of conference, division, etc... very often so it's probably safe to have each level in its own table.

If your goal is a system that can handle different leagues that have different hierarchy depths, then the recursive table approach is probably better.

I don't know how easy it would be to do this with an ORM but I have a general mistrust of ORMs when it comes to anything other than simple CRUD operations... To prevent a team from existing at multiple tiers of the hierarchy, I'm not sure why your proposed Team.LeagueGroupId wouldn't work. You know what level the LeagueGroup referred to by LeagueGroupID is so you know that the team exists at the level of that LeagueGroup.


What is an accepted method of keeping old data in MySQL?

You could duplicate the table structure entirely, but under a different name, ie. questions_archive. Then move all of the now-irrelevant data into that table and only refer to it in your query via UNION with the original table if it is truly necessary. To maintain referential integrity you'll likely want to create a similar archive table for answers.

You should also be able to specify this table's data be stored on a higher-latency, higher-capacity volume [ie: spindle drive vs SSD, NFS share vs local, etc] if you are looking for further optimization for your non-archived data.

edit: Alternatively, partitioning.


How to use COALESCE with DISTINCT with multiple rows and without preceding comma

Not sure what code you're using now or what version of SQL Server you're using, but FOR XML PATH goes pretty far back. Here's how I would do it:

DECLARE @t TABLE(area int, city char(1), state char(1));

INSERT @t(area,city,state) VALUES
(1,'a','a'),(2,'a','a'),(3,'b','a'),(4,'b','a'),
(1,'a','b'),(2,'a','b'),(3,'c','b'),(4,'c','b');

;WITH x AS
(
  SELECT
    area = STUFF((SELECT ',' + CONVERT(varchar(11),area)
      FROM @t WHERE state = t.state GROUP BY area
      FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),1,1,''),
    city = STUFF((SELECT ',' + city
      FROM @t WHERE state = t.state GROUP BY city
      FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),1,1,''),
    state
  FROM @t AS t
)
SELECT area,city,state
  FROM x
  GROUP BY area,city,state
  ORDER BY area,city,state;

There is certainly a way to do this with STRING_AGG() in more modern versions (2016 SP1+), but it won't be much less messy.

;WITH x AS
(
  SELECT city,state FROM @t GROUP BY state,city
),
y AS
(
  SELECT x.state, city = STRING_AGG(x.city,',')
  FROM x GROUP BY state
)
SELECT area = STRING_AGG(t.area, ','), y.city, y.state
  FROM y INNER JOIN @t AS t ON t.state = y.state
  GROUP BY y.city, y.state
  ORDER BY area, y.city, y.state;


Find rows without matching second field for given constants in first field

I believe that you want the following (see fiddle here):
Create and populate table - using SQL Server 2012 as this will most closely emulate Access. I've used my own SQL style largely modelled on this.
CREATE TABLE source_table
(
  first_name VARCHAR (5) NOT NULL,
  last_name  VARCHAR (7) NOT NULL,
  CONSTRAINT f_l_uq UNIQUE (first_name, last_name)
);

and
INSERT INTO source_table
  (first_name, last_name)
VALUES
  ('Jane', 'Smith'),
  ('John', 'Smith'),
  ('Blake', 'Smith'),
  ('Jane', 'Johnson'),
  ('Blake', 'Johnson');

I've put a UNIQUE constraint on the combination of (first_name, last_name) because it makes sense - if this really is your entire table, you could make it the PRIMARY KEY - if there is/are (a) PK field(s) as part of a table with more fields, then just add this constraint.
If you do have duplicate first and last names combined (dupes), then you can just add a DISTINCT clause to your SELECT and this will give you unique values for your desired result(s) - included in the fiddle, not shown here.
then run the following SQL:
SELECT -- DISTINCT if there are dupes!
  s1.first_name,
  s1.last_name
FROM source_table s1
WHERE s1.last_name = 'Smith'
AND s1.first_name NOT IN
(
  SELECT first_name FROM source_table s2
  WHERE last_name != 'Smith'
);

Result:
first_name  last_name
      John      Smith

Et voilà - the desired result!
p.s. db<>fiddle is a better tool that SQLFiddle - more servers (see dropdown) and more functionality (see the compare link) for example!
p.p.s. welcome to the forum!


Is there any way to find the number of pages read by each operator in query plan?

Io statistics per table was added to actual execution plans starting in SQL Server 2016. It was ported to SQL Server 2014 SP2 as well.
I executed a simple query and uploaded the query plan here. Comparing the results of SET STATISTICS IO ON:

Table 'BIG_TABLE'. Scan count 1, logical reads 921659, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SMALL_TABLE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To what's in the XML for the actual plan:
<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="6451600" ActualRowsRead="6451600" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="972" ActualCPUms="972" ActualScans="1" ActualLogicalReads="921659" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  <DefinedValues />
  <Object Database="[TEST]" Schema="[dbo]" Table="[BIG_TABLE]" IndexKind="Heap" Storage="RowStore" />
</TableScan>

<RunTimeInformation>
  <RunTimeCountersPerThread Thread="0" ActualRows="10" ActualRowsRead="10" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  <DefinedValues />
  <Object Database="[TEST]" Schema="[dbo]" Table="[SMALL_TABLE]" IndexKind="Heap" Storage="RowStore" />
</TableScan>

Reveals a perfect match in this case.