How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

Foreign key constraints are currently implemented with special internal triggers. All of them are run FOR EACH ROW.

Note that these are implementation details that can change, so don't rely on it. But the basics have not changed over the last couple of major versions, so major changes are unlikely.

I ran a quick test with a simple FK constraint from tbl to tbltype. A simple FK is implemented with four simple internal triggers FOR EACH ROW in my test on pg 9.4.
Here is a quick rundown on how to investigate:

SELECT oid  -- 74791
FROM   pg_constraint
WHERE  conrelid = 'tbl'::regclass
AND    contype = 'f';

SELECT objid, classid::regclass  -- 74792,74793,74794,74795 / 'pg_trigger'
FROM   pg_depend
WHERE  refobjid = 74791
AND   deptype = 'i'

SELECT tgrelid::regclass, tgname, tgfoid, tgtype FROM pg_trigger
WHERE  oid IN (74792,74793,74794,74795) ORDER BY tgfoid;

'tbl'    ;'RI_ConstraintTrigger_c_74794';1644;5
'tbl'    ;'RI_ConstraintTrigger_c_74795';1645;17
'tbltype';'RI_ConstraintTrigger_a_74792';1654;9
'tbltype';'RI_ConstraintTrigger_a_74793';1655;17

SELECT oid, proname FROM pg_proc
WHERE oid IN (1654,1655,1644,1645);

1644;'RI_FKey_check_ins'
1645;'RI_FKey_check_upd'
1654;'RI_FKey_noaction_del'
1655;'RI_FKey_noaction_upd'

Two internal "noaction" triggers on tbltype.
Two internal "check" triggers on tbl.
All of them are run FOR EACH ROW, as indicated by odd numbers in tgtype.

The 2 bytes of the Postgres tgtype  smallint represent an int16 in C source code where the least significant bit encodes TRIGGER_TYPE_ROW. Detailed explanation here:

Meanings of bits in trigger type field (tgtype) of Postgres pg_trigger

You can easily test this with a pair of identical triggers where you only change FOR ROW / STATEMENT ...


How do i know a table size and row counts for each month, for the past six months?

Unless you are tracking tables, table size can not be known. To get current size:

sp_spaceused 'TableName'

To get row count:

Select count(*) from 'TableName'

To get row count with a timestamp field:

    Select
          DATEPART(YEAR, 'timestamp_field') AS 'Year',
          DATEPART(MONTH, 'timestamp_field') AS 'Month',
          DATEPART(DAY, 'timestamp_field') AS 'Day',
          COUNT(*) AS Check

    from
         'TableName'
    group by
         DATEPART(DAY, 'timestamp_field'),
          DATEPART(MONTH, 'timestamp_field'),
          DATEPART(YEAR, 'timestamp_field')
ORDER BY  'Year',
          'Month',
          'Day'

Database growth:

SELECT
[database_name] AS DB,
DATEPART(day,[backup_start_date]) AS Day,
DATEPART(month,[backup_start_date]) AS Month,
DATEPART(year,[backup_start_date]) AS Year,
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(m,[backup_start_date]),DATEPART(d,[backup_start_date]),DATEPART(y,[backup_start_date]),backup_start_date;

for database size details. Check this link
Create table size tracking report


"ERROR: column rel.relhasoids does not exist" with PostgreSQL 12 and PgAdmin 4.3

The column relhasoids has been dropped from the system catalog pg_class in PostgreSQL v12, because the long deprecated WITH OIDS clause has been removed from CREATE TABLE.
Obviously you are using an old version of pgAdmin 4 that has not got the message yet.
Upgrade to a newer version of pgAdmin, and the error should vanish.


Always Encrypted Certificate clarification

I just want to know what is the difference between creating a certificate using SSMS and creating a self-signed certificate using PowerShell.

You have much more control over the options used to create the self-signed certificate through Powershell. There is still the minimum options the certificate needs, but one example you've already mentioned is the valid date.

Other than that, the way it is done doesn't really matter. It's the output (certificate) that's needed.

  What is a self-signed certificate means in the context of data encryption?

In this case, not a whole lot. The SSMS cert is still a self signed cert. Self signing just means you're the only authority and no one else can claim you are who and or what you say you are which in this case, doesn't matter. The private and private keys will still be used to encrypt and decrypt regardless where the certificate was made, it just needs to be a valid cert.

You may or may not be bound by using an PKI ( Public Key Infrastructure) you have at your company or using HSM (Hardware Security Module) to store the keys versus just generating a local certificate on your machine for use in other places.


How to set local_listener to null upon creating database

I tested specifying -initparams local_listener='' but the local_listener has been set anyways by specifying the empty string.

dbca -silent \
 -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbName testdb \
 -sid testdb \
 -createAsContainerDatabase false \
 -SysPassword oracle \
 -SystemPassword oracle\
 -emConfiguration NONE \
 -datafileDestination /oradata \
 -storageType FS \
 -characterSet AL32UTF8 \
 -memoryPercentage 10 \
 -initparams local_listener=''

You might want to create a custom template referencing a .sql script with the command alter session set local_listener.

I'd only make that effort if in need to use it multiple times, though. If it is a one time thing, maybe you're better off setting local_listener manually.


How to Properly shutdown and disassemble SQL Database Replication

To properly clean up replication, follow below steps :

Open SSMS --> Local Publications --> Expand Publication -->

Use sp_removedbreplication to remove replication bit set on database.

Another Method is script our the DROP or disable the components by right click on publication and then run that script using SSMS.


Should a filtered index theoretically perform faster in this example?

It would be at least a little faster, and also use less disk space, depending on how many rows there are with UniverseName = 'Unknown'.

The original index would allow the query to seek to the correct movie title starting character, and apply a residual predicate to each row to make sure it's not from an 'Unknown' universe.

The filtered index would still seek to the movie title starting character.  But it wouldn't have to apply the residual predicate (it already knows none of those rows will be present).  And it also might have to read less data pages, if there are a lot of rows with UniverseName = 'Unknown'.

Also, you should probably include UniverseName in the index definition, for the reasons outlined here: Should the filtering column(s) always be in the keys / includes?

Sidebar: the variable with string concatenation will likely result in a dynamic seek plan, unless you include a RECOMPILE hint on the query.  This may or may not matter for your use case, but is something to be aware of.


Nested queries or temp (#) tables in SQL Server

The SQLCat team ideas and articles are sometimes pitched at edge cases that most folk will never see given their load or database size.

Saying that, the ideas they give there are common techniques to improve performance (eg change OR to UNION)

Temp tables have some issues around stored procedure compilation etc, but don't confuse these with table variables (the SQLCat articles mentions this). Also, temp tables should be local not global to separate processes don't affect each other

Personally, I use temp tables quite often to break queries down: but not all the time. If I can do it in one SQL statement that runs well enough (for it's frequency of use) then I'll use that.

In your case, I'd identify a few problem queries and see if using temp tables suits these better. If you can't see any problem queries then do nothing.


Memory distribution across numa nodes within Sql server

For a system with 1TB of RAM, and over 600 GB dedicated for SQL Server, page-life-expectancy of 300 to 600 seconds seems very low.  That indicates, on average, every page in the buffer pool is being replaced every 5 to 10 minutes.  That means the server is experiencing significant I/O just to service queries - as in 100GB per minute (or more) of data being transferred from disk into memory, all the time.  I'd recommend looking at getting PLE higher, by:

Look for queries that perform an excessive amount of I/O.  Check if those queries might be optimized to avoid excessive reads by either adding appropriate indexes or breaking large queries up into smaller chunks with intermediate #temp tables.
Add physical memory.  On this note, why is max server memory set to 656GB when the machine has 1TB of memory? You're leaving 250GB on the table, that SQL Server would probably dearly love to use.  Make sure this server is dedicated to only run SQL Server, and up the max_server_memory to at least 900GB, perhaps more.  I'd set min server memory to something like 768GB to ensure Windows can "claw-back" some memory from SQL Server if it really needs to.

In extreme cases, you may want to configure thread affinity per the choices listed in this MSDN blog article.  Trace Flag 2479 can be used to enable "Connection Bound" thread placement, which place threads on one node.  SQL Server treats threads as if the connection node is the only node on the system, forcing threads for any given query to be processed by the NUMA node where the client connection started.  Per that page, parallelism is allowed as long as free threads on the node are >= (degree-of-parallelism * branches-in-query).


CREATE CERTIFICATE, see the first example Creating a self-signed certificate:

use <mydb>;
go

CREATE CERTIFICATE Shipping04
   ENCRYPTION BY PASSWORD = 'mysecretpassword'
   WITH SUBJECT = 'Used to signed procedure usp_foo',
   EXPIRY_DATE = '20181231';
GO

When you sign with it you need to specify again the password:

ADD SIGNATURE TO [usp_foo]
  BY CERTIFICATE <certname> WITH PASSWORD 'mysecretpassword';

See Signing an activated procedure for an example.


Queueing MySQL record inserts to avoid over-subscription of a related resource ... table locking?

SELECT FOR UPDATE can't lock rows that don't exist, therefore if it is used on table seats, it cannot prevent the insertion of new rows. So if you use that, it has to be done on table lifeboats.
Since customers will be in a hurry to find a lifeboat with available seats, it would be better to let go of the slow count() and add a column occupied_seats in table lifeboats.
This should be updated via a before insert or delete trigger on seats, that does an update on the corresponding row from lifeboat. This update will automatically lock the row, which handles your concurrency issues.
This trigger should raise an error (use SIGNAL sql keyword) if the insert would exceed available seats.
This can be done in a semi lock-free manner by doing
UPDATE lifeboats SET occupied_seats = occupied_seats+1
WHERE id=... AND occupied_seats < available_seats;

then testing if that did update one row. That will save one SELECT FOR UPDATE. If it did not update the row, the lifeboat is full, throw an error. If it did, this also locks the row, so COMMIT quickly.


SQL Server - Log Shipping Into an AlwaysOn Availability Group

The database on the end of log shipping has to be in NORECOVERY to accept the log backups being shipped over. A database involved in AlwaysOn Availability Group has to be removed from the group in order to be restored or set to any restoring state. So setting up log shipping to that database would require it to no longer being replicated.

I would go back to the stockholders and determine their full needs for HA as it seems they left stuff out of their initial request.


Getting the information that exists in "Job properties -> Notifications" page?

Because of where the information is stored in msdb, part of the information is obtained when the Job Properties window is initially opened, then more information is queried when you actually click on the Notifications page. SSMS uses an INSERT/EXEC on the sp_help_ stored procedures to obtain the operator information, which is pretty unwieldy... so unfortunately, using Profiler in this case to trap the queries isn't particularly helpful.

Here's a completed query that returns all the information from the page in one shot:

USE [msdb];
GO

WITH jobStates AS
(
    SELECT 0 AS Level,  'Disabled' AS Description UNION ALL
    SELECT 1,           'On Success' UNION ALL
    SELECT 2,           'On Failure' UNION ALL
    SELECT 3,           'On Completion'
)
SELECT
    j.job_id,
    j.name,
    es.Description AS EmailOnJobState, e.name AS EmailOperatorName, e.email_address AS EmailOperatorEmailAddress,
    ps.Description AS PageOnJobState, p.name AS PageOperatorName, p.pager_address AS PageOperatorPagerAddress,
    nss.Description AS NetSendOnJobState, ns.name AS NetSendOperatorName, ns.netsend_address AS NetSendOperatorNetSendAddress,
    els.Description AS EventLogOnJobState,
    ds.Description AS DeleteJobOnJobState
    FROM [dbo].[sysjobs] j
    INNER JOIN jobStates es ON es.Level = j.notify_level_email
    INNER JOIN jobStates ps ON ps.Level = j.notify_level_page
    INNER JOIN jobStates nss ON nss.Level = j.notify_level_netsend
    INNER JOIN jobStates els ON els.Level = j.notify_level_eventlog
    INNER JOIN jobStates ds ON ds.Level = j.delete_level
    LEFT OUTER JOIN [dbo].[sysoperators] e ON (j.notify_level_email > 0) AND (e.id = j.notify_email_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] p ON (j.notify_level_page > 0) AND (p.id = j.notify_page_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] ns ON (j.notify_level_netsend > 0) AND (ns.id = j.notify_netsend_operator_id);


64 bit Postgres 9.1 Limitions on Windows 2008 R2?

No clue.
What kind of scale (up or out)? From what little that I've found on the web, it appears that Pg on Linux can handle more connections than on Windows.
Not if you are running anti-virus software on the Windows box ;^). There is an old (8.2 vintage) pgsql-performance thread here regarding performance differences. The take away seems to be "it depends" (based on usage) and that you need to do benchmarking to really know how much performance difference there will be. There is also some discussion regarding performance differences on serverfault.


Those queries won't do the same thing.

Update Table set REC_ID = isnull(REC_ID,'')

This one will update each record and if REC_ID is NULL it will set it to ''.

Update Table set REC_ID = '' where REC_ID is NULL

This one will only update records containing a null value in REC_ID, and set it to ''.
While both of them will end up giving the same result, the second one will be executed on less records (except if every REC_ID is NULL), it should be faster.


Uploading a csv and losing a connection results in lost disk space and table bloat

The tuples were created and they left dead because the transaction wasn't committed. Simply run

VACUUM and the dead tuples will be purged. Or run
VACUUM FULL and the table will be rewritten (removing the dead tuples). If you have more dead tuples then live ones this may be faster, but it'll require a lock. It's usually massively slower but it sounds like you may be an exception.


Count distinct values of a column and insert them to another table

Try using

SUM(Case WHEN problem Like '%err1%' THEN 1 ELSE 0 END) //for problem count

'%err1%' is a sample error containing letter to rectify which error is this.


How many log files does a database usually have?

Having an additional log file doesn't actually hurt performance in and of itself.  That doesn't however mean that you won't have performance issues when you have multiple files.  If one of your files is on slower storage for example you could end up with intermittent performance issues which can be difficult to troubleshoot and cost you quite a bit of time.  Also frequently when people use a second log file they end up with a larger log than they actually need.  As pointed out by Paul Randal in the link in the comments this can cause you significant issues during recovery.

The important thing to remember is that the system writes to one specific point in the log, and writes sequentially.  So if you have multiple log files you are only writing to one file at a time.  This means that having more than one log file is generally pretty useless.

The only reason I've found that you actually want to have a second log file is to temporarily extend your log onto a second drive.  You've run out of space for your existing log file and don't have time to add space to that drive.  You then create a second log file on a different drive with more space.  This should be a temporary situation however.  You'll want to expand your existing space (or create a new one) and move your log back into a single file.

I wrote about it in more detail here if you are interested.


Pagination performance, with subquery, inner join and where

I don't think you'll be able to get good performance while using OFFSET.  The database must search through 1,000,025 rows of output from the inner query; even if you have a good clustered index on TaskResults the system doesn't know for certain that it can skip ahead to date X.

But you do!  Assuming this is for some kind of GUI, make a note of the earliest StatusDate from the previous query, then use it to fitler next page:

SELECT
    tr.Id, StatusDate
FROM
    (
    SELECT tr.Id, tr.StatusDate
    FROM mon.ArchivedTaskResults_201504 as tr WITH (NOLOCK)
        INNER JOIN mon.ViewDevicesWithGroups dev WITH (NOLOCK) ON tr.DeviceId = dev.Id
    WHERE tr.ClientId = 4 AND dev.Deleted = 0
        AND
            (
            -- Retrieve only records from before the previous page
            tr.StatusDate < @PrevStatusDate
            OR (tr.StatusDate = @PrevStatusDate AND tr.Id < @PrevID)
            )
    ) AS tr
ORDER BY StatusDate, Id DESC
FETCH NEXT 25 ROWS ONLY

So if page #123 ends with 2015/05/01, record #234, you want to consider all records that are from 2015/04/30 or earlier, or which are also from 2015/05/01 but are for records #1 .. #233.

This should work well with your more complex UNION query, but "real" partitioning would probably be easier than this roll-yer-own partitioning..

If StatusDate is unique, or it's acceptable to occasionally show the same record on two adjacent pages, you can drop the @PrevID and ORDER BY Id bits.  If Id is always-increasing, you can filter off of it and skip StatusDate.

Keep in mind that retrieving pages like this can easily skip a record or include the same record twice if records are being adding, removed, or reordered in the underlying data.  But that's another topic.


Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

Information turned into a Community Wiki answer from this question on Stack Overflow: What is the data type of a computed column?

It's determined by the value with the highest datatype precedence.

You can use sql_variant_property to determine what datatype a literal expression is as per my answer here. e.g. 2147483648 is interpreted as numeric(10,0) rather than bigint.

In addition, if you want to force a specific data type instead of relying on data type precedence, you can use CONVERT or CAST in the computation to force it (assuming all potential outcomes are compatible with the type you choose). This can be very useful in cases where, by default, you end up with a wider data type than you intended; the most common use case I've seen is when you end up with an INT instead of, say, a BIT:

Active1 AS CASE WHEN something THEN 1 ELSE 0 END,
Active2 AS CONVERT(BIT, CASE WHEN something THEN 1 ELSE 0 END)

In this case Active1 is an INT (4 bytes) while Active2 is a BIT (1 byte - or less, potentially, if it is adjacent to other BIT columns).


Mysql - How number of columns effect the database performance

If you have 42 columns to store and you are selecting only 4 columns, then yes the other columns can affect the query since it increases the amount of disk I/O the server uses to select the wider table.

If you primarily need the 4 columns, doing a vertical partition would give a smaller I/O overhead and could result in better performance for that query.

Likewise, a covering index for the 4 columns could also be processed with less I/O than the wider 42 column select.

Will it be faster?  It should be, but it all depends on what else is involved in the process but not explicitly mentioned in your question.


SQL Server 2014 All Administrator Accounts Disabled

You need to start SQL Server in single-user mode.

The documentation Connect to SQL Server When System Administrators Are Locked Out says:

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

See the page linked above for further considerations and step-by-step instructions.


access denied when mysql to localhost using public ip

If you want to login to MySQL on the local machine using the IP address do this

mysql -u cgao -h xx.xxx.xxx.xxx -P3306 --protocol=tcp -p

This force a TCP/IP connection locally


Is it possible to set a timeout for optimization in PostgreSQL?

No, and no, though it does have some heuristics (like join_collapse_limit) to limit how long it tries for, and it also has things like GEQO to switch to different optimisation strategies for big joins under some circumstances.

There's no optimisation timeout as such.

(It'd help if you'd explain the kinds of queries you're running and why they're taking so long to plan.)


You have chain replication as

Master--> SLAVE A--> SLAVE B (Slave A is Slave of Master and Slave B is slave of Slave A)

As you told On Slave Server(A) binary log is enabled.

  You additionally need to enable log_slave_updates because a slave
  does not log to its own binary log any updates that are received from
  a master server. This option tells the slave to log the updates
  performed by its SQL thread to its own binary log.

So you need to enable log_slave_updates on Slave A.

Please have a look at Improving Replication Performance.


How to UPDATE MAX and MIN of a GROUP BY?

SELECT * FROM test WHERE id IN
(SELECT MIN(id) FROM test GROUP BY category HAVING col='x'
UNION SELECT MAX(id) FROM test GROUP BY category HAVING col='x')

Fiddle


How can I extract a list of dependent tables from an execution plan?

If this was a one-off operation, I would be inclined to use SQL Sentry Plan Explorer (it's free!), rather than faff around writing a SQL Script.

Open the Execution Plan in Plan Explorer, navigate to the Query Columns pane and you can see a list of the objects that are used in the query.


Microsoft Access is giving me network interrupted errors

Let's have a look at the individual components.
Microsoft Access
According to Microsoft the Office program Access does have some limitations in regards to using it on network drives:

There are several ways that you can share an Access database depending on your needs and resource availability.

Reference Ways to share an Access desktop database (MS Office Support)
...but specifically:

Share data by using network folders
This is the simplest option and has the least requirements, but also provides the least functionality. In this method, the database file is stored on a shared network drive, and all users share the database file simultaneously. Some limitations include reliability and availability if there are multiple simultaneous users changing data since all database objects are shared. This technique can also reduce performance as all the database objects are sent across the network.

(Emphasis mine)
Reference: Share data by using network folders (MS Office Support)
Bentley AutoPLANT Modeler SS8 Refresh
The support notes for your versions are listed as:

This build supports

Windows 7, Windows 8.1 and Windows 10 x64
Office 2010, 2013 and 365/2013 and 365/2016 (both 32bit and 64bit)
SQL Server 2008 R2 / 2012 SP2 / 2014 (Enterprise / Standard / Express Edition)
ORACLE 11g Release 2 (v11.2.0.3) / 12c Release 1 (v12.1.0.2)
OpenPlant Isometrics Manager (OPIM) V8i (SELECTseries 6) Refresh
AutoPLANT i-model Composer V8i (SELECTseries 8) Refresh is required
The Run-time version of Microsoft Access is not supported
The Professional version of Office (including Microsoft Access) is required:

Enable use of Bentley Project Administrator.
For the generation of reports via Data Manager, Vision and Document Manager etc.
For use of Advanced Selection dialog in AutoPLANT Modeler and AutoPLANT Isometrics.

Reference: AutoPLANT Modeler SS8 Refresh - Installation Sequence (Bentley Communities)
Combining The Two Bits of Information

If you are using Access for the intended purpose, then I would recommend opening up a support ticket with Bentley to determine what the issue might be.

If you are using Access for the central database (which should be running on Microsoft SQL Server or Oracle 9i RDBMS), then I would recommend to re-install your current environment.

If Access remains the culprit, then you might have to check your Company's Microsoft Domain Policies. You might have a rule that is automatically disconnecting your network drives after a certain period of inactivity.

If your network drives are indeed failing, then you might want to contact the infrastructure guys to have a look a the SAN disks (where your network drives ar situated) and/or the network department to determine if your network is dropping connections:

after a period of time
due to network connectivity issues

If you have a support contract with Microsoft then open up an incident with Microsoft.


A fast refresh would copy incremental changes over the network but requires that a materialized view log be created on the master site on the source table.  That adds some overhead to the inserts happening on the master table but would generally make the refresh more efficient.

A complete refresh would copy every row over the network every time the materialized view is refreshed.  That is likely to be less efficient from a refresh perspective but there will be no overhead to inserts on the source table and the master site does not need to create a materialized view log.

Oracle provides a host of data replication technologies-- materialized views are the oldest and probably the least efficient but are relatively trivial to set up.  Streams is a newer technology that has much lower overhead but is quite a bit more complex to set up.  Golden Gate is the preferred replication technology today but that has extra licensing costs.


When you run SSRS (or any other application for that matter) on the same server as the database engine, you need to be mindful that overall server resources are shared, especially memory and processor.  Make sure the server is sized to accommodate both, and consider configuring SQL Server 'max server memory' so that the memory used by SSRS, the OS, and SQL Server doesn't exceed physical memory.

Running SSRS (and/or SSAS) on the same box may be advantageous from a licensing cost perspective since you will need additional SQL Server licenses to run on a separate physical server.  However, it will only detract from the resources available to the database engine.


I'm using GET_LOCK and RELEASE_LOCK in this situation.

  GET_LOCK() can be used to implement application locks or to simulate
  record locks. Names are locked on a server-wide basis. If a name has
  been locked within one session, GET_LOCK() blocks any request by
  another session for a lock with the same name. This enables clients
  that agree on a given lock name to use the name to perform cooperative
  advisory locking. But be aware that it also enables a client that is
  not among the set of cooperating clients to lock a name, either
  inadvertently or deliberately, and thus prevent any of the cooperating
  clients from locking that name. One way to reduce the likelihood of
  this is to use lock names that are database-specific or
  application-specific. For example, use lock names of the form
  db_name.str or app_name.str.

  If multiple clients are waiting for a lock, the order in which they
  will acquire it is undefined. Applications should not assume that
  clients will acquire the lock in the same order that they issued the
  lock requests.

Before running a bunch of statements, run:

SELECT GET_LOCK('my unique lock name', 10);

If it returns 1 (success) go on with your bunch of statements and in the end

SELECT RELEASE_LOCK('my unique lock name');

Add some handling of the timeout case, such as retry few times.

It doesn't matter whether your statements work with one table or many tables, whether they are MyIsam, or not. These application-specific locks are server-wide.


Bizarre density results in sampled statistics

I've seen this same density problem on some of the nonclustered indexes on the largest databases that I have access to. First I'll start with a few observations that I've made about histograms and density calculations:

SQL Server is able to use the primary key on the table to infer something about the density of both columns. This means that the density that includes the PK columns will usually be very accurate.
The density calculation for the first column in the statistics is consistent with the histogram. If the histogram does not model the data well then the density may be off.
To create the histogram the StatMan function makes inferences about the data that is missing. The behavior can change depending on the data type of the column.

For one way to look at the problem, suppose that you sample 100 rows from a 10000 row table and you get 100 distinct values. One guess at what the rest of the data in the table is that there are 10000 unique values. Another guess is that there are 100 distinct values but each of the values are repeated 100 times. The second guess may seem unreasonable to you, which I will agree with. However, how do you balance the two approaches when the sampled data comes back unevenly distributed? There is some set of algorithms developed for this by Microsoft contained in the StatMan function. The algorithms may not work for all data disruptions and all sample levels.

Let's go through a relatively simple example. I'm going to use VARCHAR columns like in your table to see some of the same behavior. However, I'll just add one skewed value to the table. I'm testing against SQL Server 2016 SP1. Start with 100k rows with 100k unique values for the FK column:

DROP TABLE IF EXISTS X_STATS_SMALL;

CREATE TABLE X_STATS_SMALL (
ID VARCHAR(10) NOT NULL,
FK VARCHAR(10) NOT NULL,
PADDING VARCHAR(900) NOT NULL,
PRIMARY KEY (ID)
);
-- insert 100k rows
INSERT INTO X_STATS_SMALL WITH (TABLOCK)
SELECT N, N, REPLICATE('Z', 900)
FROM dbo.GetNums(100000);

CREATE INDEX IX_X_STATS_SMALL ON X_STATS_SMALL (FK);

-- get sampled stats
UPDATE STATISTICS X_STATS_SMALL IX_X_STATS_SMALL;

Here are some samples from the statistics:

╔═════════════╦════════════════╦═════════╗
║ All density ║ Average Length ║ Columns ║
╠═════════════╬════════════════╬═════════╣
║ 1.00001E-05 ║ 4.888205       ║ FK      ║
║ 1.00001E-05 ║ 9.77641        ║ FK, ID  ║
╚═════════════╩════════════════╩═════════╝

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║ 1005         ║ 0          ║ 1       ║ 0                   ║ 1              ║
║ 10648        ║ 665.0898   ║ 1       ║ 664                 ║ 1.002173       ║
║ 10968        ║ 431.6008   ║ 1       ║ 432                 ║ 1              ║
║ 11182        ║ 290.0924   ║ 1       ║ 290                 ║ 1              ║
║ 1207         ║ 445.7517   ║ 1       ║ 446                 ║ 1              ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 99989        ║ 318.3941   ║ 1       ║ 318                 ║ 1              ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

For evenly distributed data with one unique value per row we get an accurate density, even with a VARCHAR histogram column and a sample size of 14294 rows.

Now let's add a skewed value and update statistics again:

-- add 70k rows with a FK value of '35000'
INSERT INTO X_STATS_SMALL WITH (TABLOCK)
SELECT N + 100000 , '35000',  REPLICATE('Z', 900)
FROM dbo.GetNums(70000);

UPDATE STATISTICS X_STATS_SMALL IX_X_STATS_SMALL;

With a sample size of 17010 rows the density of the first column is smaller than it should be:

╔══════════════╦════════════════╦═════════╗
║ All density  ║ Average Length ║ Columns ║
╠══════════════╬════════════════╬═════════╣
║ 6.811061E-05 ║ 4.935802       ║ FK      ║
║ 5.882353E-06 ║ 10.28007       ║ FK, ID  ║
╚══════════════╩════════════════╩═════════╝

╔══════════════╦════════════╦══════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS  ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬══════════╬═════════════════════╬════════════════╣
║ 10039        ║ 0          ║ 1        ║ 0                   ║ 1              ║
║ 10978        ║ 956.9945   ║ 1        ║ 138                 ║ 6.954391       ║
║ 11472        ║ 621.0283   ║ 1        ║ 89                  ║ 6.941863       ║
║ 1179         ║ 315.6046   ║ 1        ║ 46                  ║ 6.907561       ║
║ 11909        ║ 91.62713   ║ 1        ║ 14                  ║ 6.74198        ║
║ ...          ║ ...        ║ ...      ║ ...                 ║ ...            ║
║ 35000        ║ 376.6893   ║ 69195.05 ║ 54                  ║ 6.918834       ║
║ ...          ║ ...        ║ ...      ║ ...                 ║ ...            ║
║ 99966        ║ 325.7854   ║ 1        ║ 47                  ║ 6.909731       ║
╚══════════════╩════════════╩══════════╩═════════════════════╩════════════════╝

It's surprising that the AVG_RANGE_ROWS is pretty uniform for all steps at around 6.9, even for buckets of keys for which the sample couldn't have found duplicate values. I don't know why this is. The most likely explanation is that the algorithm used to guess at the missing pages does not do well with this data distribution and sample size.

As stated earlier, it is possible to calculate the density for the FK column using the histogram. The sum of the DISTINCT_RANGE_ROWS values for all steps is 14497. There are 179 histogram steps so the density should be about 1 / (179 + 14497) = 0.00006813845 which is pretty close to the reported value.

Testing with a larger table can show how the problem can get worse as the table gets bigger. This time we'll start with 1 M rows:

DROP TABLE IF EXISTS X_STATS_LARGE;

CREATE TABLE X_STATS_LARGE (
ID VARCHAR(10) NOT NULL,
FK VARCHAR(10) NOT NULL,
PADDING VARCHAR(900) NOT NULL,
PRIMARY KEY (ID));

INSERT INTO X_STATS_LARGE WITH (TABLOCK)
SELECT N, N, REPLICATE('Z', 900)
FROM dbo.Getnums(1000000);

CREATE INDEX IX_X_STATS_LARGE ON X_STATS_LARGE (FK);

-- get sampled stats
UPDATE STATISTICS X_STATS_LARGE IX_X_STATS_LARGE;

The statistics object isn't interesting yet. Density for FK is 1.025289E-06 which is close to exact (1.0E-06).

Now let's add a skewed value and update statistics again:

INSERT INTO X_STATS_LARGE WITH (TABLOCK)
SELECT N + 1000000 , '350000',  REPLICATE('Z', 900)
FROM dbo.Getnums(700000);

UPDATE STATISTICS X_STATS_LARGE IX_X_STATS_LARGE;

With a sample size of 45627 rows the density of the first column is worse than before:

╔══════════════╦════════════════╦═════════╗
║ All density  ║ Average Length ║ Columns ║
╠══════════════╬════════════════╬═════════╣
║ 2.60051E-05  ║ 5.93563        ║ FK      ║
║ 5.932542E-07 ║ 12.28485       ║ FK, ID  ║
╚══════════════╩════════════════╩═════════╝

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║ 100023       ║ 0          ║ 1       ║ 0                   ║ 1              ║
║ 107142       ║ 8008.354   ║ 1       ║ 306                 ║ 26.17787       ║
║ 110529       ║ 4361.357   ║ 1       ║ 168                 ║ 26.02392       ║
║ 114558       ║ 3722.193   ║ 1       ║ 143                 ║ 26.01217       ║
║ 116696       ║ 2556.658   ║ 1       ║ 98                  ║ 25.97568       ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 350000       ║ 5000.522   ║ 700435  ║ 192                 ║ 26.03268       ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 999956       ║ 2406.266   ║ 1       ║ 93                  ║ 25.96841       ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

AVG_RANGE_ROWS is up to 26. Interestingly, if I change the sample size to 170100 rows (10X the other table) then the average value for AVG_RANGE_ROWS is again right around 6.9. As your table gets bigger SQL Server will pick a smaller sample size which means that it needs to make guesses about a larger percentage of pages in the table. This can exaggerate statistics problems for certain kinds of data skew.

In conclusion, it's important to remember that SQL Server does not calculate the density like this:

SELECT COUNT(DISTINCT FK) * 1700000. / COUNT(*) -- 1071198.9 distinct values for one run
FROM X_STATS_LARGE TABLESAMPLE (45627 ROWS);

Which for some data distributions will be very accurate. Instead it uses undocumented algorithms. In your question you said that your data wasn't skewed, but the INSTANCEELEMENTID value with the highest number of associated IDs has 12 and the most common number is 1. For the purposes of the algorithms used by Statman that could be skewed.

At that point there's nothing that you can do about it except for gathering stats with a higher sample rate. One common strategy is to gather statistics with FULLSCAN and NORECOMPUTE. You can refresh the stats with a job on whatever interval makes sense for your rate of data change. In my experience a FULLSCAN update isn't as bad as most people think, especially against an index. SQL Server can just scan the entire index instead of the entire table (as it would do for a rowstore table against an unindexed column). Additionally, in SQL Serer 2014 only FULLSCAN stats updates are done in parallel, so a FULLSCAN update can finish faster than some sampled updates.


There is no easy way to do this.  Triggers don't work, as if you select from a table no trigger is fired.  The best way that I've found to do this is to have the developers track what they use.  When something is going to be dropped check with all the dev teams, and after everyone signs off, rename the object.  Then is nothing breaks for a month or to, the object can be safely dropped.


mysqlslap number in sql statement to change with iteration

If you are worried about the performance effect of caching the result in the query cache, you can disable caching in your query by using SQL_NO_CACHE:

SELECT SQL_NO_CACHE age FROM member WHERE id = [some number]


How to SELECT a column in a row and return another row if it doesn't exists?

Well this is incredibly inefficient and I fail to see the point, but...

IF EXISTS (SELECT 1 FROM ... WHERE ... <1st clause>)
BEGIN
  SELECT ... FROM ... WHERE <1st clause>;
END
ELSE
BEGIN
  SELECT ... FROM ... WHERE <2nd clause>;
END

Or if the types of col1 and col3 are compatible then...

SELECT ... FROM ... WHERE <1st clause>
UNION ALL
SELECT ... FROM ... WHERE <2nd clause>
  AND NOT EXISTS (SELECT ... FROM ... WHERE <1st clause>);

I don't know of any other way for SQL Server to determine there will be no rows from a query without actually running the query.


Execution Plan Warnings cardinality xml data type

Add TYPE to return XML from the inner query rather than a string

DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL, TYPE
)


Mysql Pivot rows into dynamic columns with unknown number of columns

Procedure:

DELIMITER @@;

DROP PROCEDURE IF EXISTS pivot@@;
CREATE PROCEDURE pivot ( IN schema_name VARCHAR(64) /* database name */
                       , IN table_name VARCHAR(64)  /* table name */
                       , IN id_name VARCHAR(64)     /* row values field name */
                       , IN key_name VARCHAR(64)    /* col values field name,
                                                           must be char or varchar type
                                                           and <= 64 chars long */
                       , IN value_name VARCHAR(64)  /* val values field name */
                       )
pivot:BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @error := 1;
    SET @error := 0;
    SELECT character_maximum_length
        INTO @maxlen
        FROM information_schema.columns
        WHERE table_schema = schema_name
          AND table_name = table_name
          AND column_name = key_name
          AND data_type IN ('char', 'varchar');
    IF @error OR !@maxlen OR @maxlen IS NULL THEN
        SELECT '@error OR @maxlen=0 OR @maxlen IS NULL', @error, @maxlen;
        LEAVE pivot;
    END IF;
    DROP TEMPORARY TABLE IF EXISTS temp_pivot;
    SET @sql := CONCAT('CREATE TEMPORARY TABLE temp_pivot (key_name VARCHAR(',
                       @maxlen,
                       ')) ENGINE=Memory SELECT DISTINCT `',
                       key_name,
                       '` key_name FROM `',
                       schema_name,
                       '`.`',
                       table_name,
                       '`;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    SELECT GROUP_CONCAT(CONCAT( ', MAX(CASE `',
                                key_name,
                                '` WHEN ''',
                                temp_pivot.key_name,
                                ''' THEN `',
                                value_name,
                                '` END) `',
                                temp_pivot.key_name,
                                '`') SEPARATOR '')
        INTO @sql
        FROM temp_pivot;
    DROP TEMPORARY TABLE temp_pivot;
    SET @sql := CONCAT('SELECT `',
                       id_name,
                       '`',
                       @sql,
                       ' FROM `',
                       schema_name,
                       '`.`',
                       table_name,
                       '` GROUP BY `',
                       id_name,
                       '`;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    SET @error := NULL;
    SET @maxlen := NULL;
    SET @sql := NULL;

END pivot@@;

DELIMITER ;

Usage example:

/* USE test; */
DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab(id INT, `key` VARCHAR(16), val INT);
INSERT INTO testtab (id, `key`, val)
VALUES (1,'key1',11),
       (1,'key2',12),
       (1,'key3',13),
       (2,'key1',21),
       (2,'key2',22),
       (2,'key4',24),
       (3,'key1',31),
       (3,'key2',32),
       (3,'key3',33),
       (3,'key4',34);
SELECT * FROM testtab;

CALL pivot('test', 'testtab', 'id', 'key', 'val');

DROP PROCEDURE pivot;
DROP TABLE testtab;


If it's saying the file is not empty, it's not empty. You need to figure out what's there and either rebuild it on another filegroup manually, or drop it. EMPTYFILE only works on the last file if there are no objects at all, and of course this is not possible on the PRIMARY filegroup.

My first guess is there's a table, index, or partition scheme still associated with this filegroup:

DECLARE @fgid int;

SELECT @fgid = data_space_id
  FROM sys.filegroups
  WHERE name = N'smoochies';

SELECT [object] = o.name, [index] = i.name
  FROM sys.objects AS o
  INNER JOIN sys.indexes AS i
  ON o.[object_id] = i.[object_id]
  WHERE i.data_space_id = @fgid
    OR i.data_space_id IN
    (
      SELECT partition_scheme_id
        FROM sys.destination_data_spaces AS ds
        WHERE data_space_id = @fgid
    );

SELECT ps.name
  FROM sys.destination_data_spaces AS ds
  INNER JOIN sys.partition_schemes AS ps
  ON ds.partition_scheme_id = ps.data_space_id
  WHERE ds.data_space_id = @fgid
  GROUP BY ps.name;

When you find the thing (it could be an index on an empty table, an empty heap, or just a partition scheme still referencing the filegroup you want to remove), you'll have to drop / rebuild / shift objects to a new partition scheme. If the partition currently referenced is the "permanent" partition (first in RANGE RIGHT or last in RANGE LEFT), see this answer.


What's the difference between group roles and login roles?

All these questions are basically answered in the documentation.  To be specific:

  A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group", or both depending on how it is used.

That implies that internally there is no difference between these, except the LOGIN option.  If you specify LOGIN (or use CREATE USER):

CREATE ROLE dezso WITH LOGIN PASSWORD 'bla';

dezso will become a login role, otherwise not.  You can specify passwords for non-login roles, too - if you later decide to make it a login role, use

CREATE ROLE non_login_role PASSWORD 'bla';

--later
ALTER ROLE non_login_role WITH LOGIN;

You are right when saying 'it's only possible to authenticate with login role'.  Note, however, that a password is not always necessary - if you use anything else than the password or md5 authentication methods.  On the other hand this means that a role without the LOGIN option set cannot log in in any way.

Finally, you may set rules in pg_hba.conf for non-login roles, but as it's used only for authentication, those roles will have no effect (unless you enable login later, as above).


There are 2 High Availability types:

Advanced HA on Enterprise edition
Standard or Basic HA on Standard edition (or BI edition)

Capabilities, prerequisites and restrictions

You can look for SQL Server 2014 Capabilities at the top of page 5: SQL Server 2014 Licensing Guide.

If you look at the 2nd row from the bottom of the Compare edition table (SQL Server 2016) on SQL Server Editions page, only the Enterprise edition includes this:

  Advanced high availability (AlwaysOn, multiple, active secondaries; multi-site, geo-clustering)

If you look at Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) you will find a similar restriction among others:

  Each server instance must be running the Enterprise Edition of SQL Server 2016.

Since you are consolidating several instances and servers, you should also look at other restrictions such as collation, etc.

SQL Server Standard

Now using Standard edition with only a Basic High Availability group is posible with some of the limitations found on Overview of AlwaysOn Basic Availability Groups (SQL Server):

Limit of two replicas (primary and secondary).
No read access on secondary replica.
No backups on secondary replica.
Support for one availability database.

Some of these restrictions are ~similar~ to the deprecated mirroring functionnalities.

Consolidation with Standard licence

Because you are consolidating servers and instances, you obviously have several databases.

You could create X Availability Groups for your X databases with a single database in each AG. However you will have to create them using the WITH BASIC option. You will then fall under the other restrictions of Basic AG and you won't be able to use more than 2 replicas, hence no instance 3.

Therefore a Standard licence does not seem to be an option in your case.

HA aside, if Instance 1 is using a single Enterprise option, then an Enterprise licence is needed on all 3 instances.

You should review SQL Server 2014 Licensing Guide and SQL Server 2014
Licensing Datasheet and then check and discuss it with you software vendor.

Please note that since SQL Server 2016 is not yet available, some of the links and information are based on SQL Server 2014 licencing model.

Instance 1 Licence:

This is your main instance with several databases. It requieres an Enterprise licence.

Instance 2 Licence:

If this instance is really passive, then it may already be covered by the licence on instance 1 as a free passive server:

  Beginning with SQL Server 2014, each active server licensed with SA coverage allows the installation of a single passive server used for fail-over support.
   The passive secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive. If it is serving data, such as reports to clients running active SQL Server workloads,
  or performing any “work” such as additional backups from secondary servers, then it must be licensed for SQL Server.

If this instance is used and accessed (reporting, backups, etc.) or is not covered then it also need it own Enterprise licence.

Instance 3 Licence:

This instance is used (reporting) and host several databases. It needs its own Enterprise licence.


The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes.

  NOTE: The sysjobschedules table refreshes every 20 minutes, which may
  affect the values returned by the sp_help_jobschedule stored
  procedure.

Which is why your next_run_date & next_run_time columns could show out of date data.

For a much longer answer & deep dive. Check out this answer by Aaron Bertrand

He talks about using dbo.sysjobactivity.

You could use the next_scheduled_run_date, which is a datetime column from this dmv.

Example query:

SELECT sj.Name,
next_scheduled_run_date
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

With the max(session) used because the agent starts a new session each time it restarts.

This results in

Name                            next_scheduled_run_date
LSAlert_My_machine              2019-10-20 11:34:00.000
LSRestore_My_machine_Test42     2019-10-20 11:45:00.000
LSCopy_My_machine_Test42        2019-10-20 11:45:00.000
syspolicy_purge_history         2019-10-21 02:00:00.000

SSMS

When tracing what SSMS is doing to get the job info when calling job activity monitor. the last_run_date & last_run_time are taken from the xp_sqlagent_enum_jobs procedure, in a cursor wise manner.

You could get the job info & place a cursor over it by using this procedure.

An example of getting the info for one job:

DECLARE @job_id uniqueidentifier
DECLARE @job_owner varchar(255)
SELECT @job_owner = SUSER_SNAME()

SELECT @job_id=job_id FROM msdb..sysjobs
WHERE name = 'LSAlert_My_machine'
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id;

2nd until 5th columns returned by the procedure:

Last Run Date   Last Run Time   Next Run Date   Next Run Time
20191020        114400          20191020        114600

If you wish to run the procedure for all jobs:

DECLARE @job_id uniqueidentifier, @job_name varchar(255)
DECLARE @job_owner varchar(255)
SELECT @job_owner = SUSER_SNAME()
DECLARE @xp_results TABLE(Job_name varchar(255),Job_Id varbinary(16),last_run_date varchar(10),last_run_time varchar(10),next_run_date varchar(10), next_run_time varchar(10), next_run_schedule_id int, requested_to_run bit,request_source int, request_source_id int, running bit, current_step int, current_retry_attempt int, [State] int)

DECLARE C CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [name],job_id FROM msdb..sysjobs
OPEN C
FETCH NEXT FROM C INTO @job_name,@job_id
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @xp_results(Job_Id,last_run_date ,last_run_time ,next_run_date , next_run_time , next_run_schedule_id , requested_to_run ,request_source , request_source_id , running , current_step , current_retry_attempt , [State] )
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id;

UPDATE @xp_results
SET Job_name = @job_name
WHERE Job_Id = @job_id;

FETCH NEXT FROM C INTO @job_name,@job_id
END
CLOSE C
DEALLOCATE C
SELECT Job_name,last_run_date,last_run_time,next_run_date,next_run_time
FROM @xp_results;

Example output

Job_name                        last_run_date   last_run_time   next_run_date   next_run_time
LSAlert_My_machine              20191020        115800          20191020        120000
LSCopy_My_machine_Test42        20191020        114500          20191020        120000
LSRestore_My_machine_Test42     20191020        114500          20191020        120000


Failed to start MySQL Community Server. (start-limit-hit)

I got after running
sudo mysqld --initialize

  [ERROR] --initialize specified but the data directory has files in it. Aborting.

for me there was a single file in /var/lib/mysql/ that you could just remove

then I got further, but another error stopped me about apparmor where I followed this, which got me further still but am still stuck:

>

  Resolution
  Add permissions for the objects reported in the lines that start with 'name=' in the output to the file /etc/apparmor.d/usr.sbin.mysqld.

  In this example, it is required to add r permissions to /proc/*/status and /sys/devices/system/node/. The paths may be different depending on the error messages.

  Connect the server via SSH.
  Open the file /etc/apparmor.d/usr.sbin.mysqld in a text editor (for example, vi editor) and add the lines below at the end of the /usr/sbin/mysqld section at the end of the file:

/usr/sbin/mysqld {
...
/proc/*/status r,
/sys/devices/system/node/ r,
/sys/devices/system/node/node*/meminfo r,
/sys/devices/system/node/*/* r,
/sys/devices/system/node/* r,
...
}

  Reload AppArmor configuration for MySQL service:

  apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

  Start MySQL server:

  service mysql start

  Note: In case MySQL fails to start again, check the output of journalctl -xe for new error messages.


Compare each day of year with DATEFIRST = 1 and DATEFIRST = 7

I started with an attempt at fixing Max Vernon's answer so that it would work for any year, but ended up with two different approaches.

Approach 1. Being clever

In this method I am calculating the Sunday-based week number directly from the offset from the year's first Sunday-based week's Sunday.

A year's first Sunday-based week's Sunday can be obtained like this:

SET DATEFIRST 1;
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @FirstDayOfYear) % 7), @FirstDayOfYear);

You are taking the week day of January 1 as a number of days and subtracting it from January 1, unless that date falls on Sunday, in which case you are subtracting nothing (that is what the … % 7 is for). For Monday-based week numbers, the result of the above formula will always be a Sunday, and in this case it will be the Sunday of the first Sunday-based week of the year.

Before going on to calculating Sunday-based week numbers, here is another formula for turning any date into January 1 of the same year:

SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, @Date), @Date);

Basically, take the date and subtract its DAYOFYEAR value and add one day.

Now here is a query that is using Max Vernon's set of CTEs, the above two formulae and another one to get the actual week number:

SET DATEFIRST 1;
WITH Nums AS (
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Num)
)
, Dates AS
(
    SELECT Date = DATEADD(DAY
                , ROW_NUMBER() OVER (ORDER BY n1.Num, n2.Num, n2.Num)
                , N'2016-12-31T00:00:00')
    FROM Nums n1 --10 rows
        CROSS JOIN Nums n2 --100 rows
        CROSS JOIN Nums n3 --1000 rows, add more cross joins for more rows
)
SELECT [Date] = FORMAT(d.Date, 'dddd MMM dd, yyyy')
    , WeekOfYearStartingMonday = DATEPART(WEEK, d.Date)
    , WeekOfYearStartingSunday = DATEDIFF(DAY, y.Sunday1, d.Date) / 7 + 1
FROM Dates AS d
CROSS APPLY (
    SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, d.Date), d.Date)
) AS x (January1)
CROSS APPLY (
    SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, x.January1) % 7), x.January1)
) AS y (Sunday1)
ORDER BY d.Date;

Approach 2. Using system loophole

This method is completely different in that it relies on a built-in peculiarity of the DATEDIFF function.

As you probably know, setting DATEFIRST affects the results of such functions as DATEPART(WEEK) and DATEPART(WEEKDAY). Unlike them, DATEDIFF(WEEK) does not respect the DATEFIRST setting. It always calculates the difference as if DATEFIRST were set to 7, that is always treating weeks as Sunday-based.

Knowing that, you can calculate the difference in weeks between January 1 and any date of the same year, add 1 and use that as the Sunday-based week number:

SET DATEFIRST 1;
WITH Nums AS (
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Num)
)
, Dates AS
(
    SELECT Date = DATEADD(DAY
                , ROW_NUMBER() OVER (ORDER BY n1.Num, n2.Num, n2.Num)
                , N'2016-12-31T00:00:00')
    FROM Nums n1 --10 rows
        CROSS JOIN Nums n2 --100 rows
        CROSS JOIN Nums n3 --1000 rows, add more cross joins for more rows
)
SELECT [Date] = FORMAT(d.Date, 'dddd MMM dd, yyyy')
    , WeekOfYearStartingMonday = DATEPART(WEEK, d.Date)
    , WeekOfYearStartingSunday = DATEDIFF(WEEK, x.January1, d.Date) + 1
FROM Dates AS d
CROSS APPLY (
    SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, d.Date), d.Date)
) AS x (January1)
ORDER BY d.Date;


Galera cluster into Google cloud platform

but I have difficulties to see the added
  value compared to Cloud SQL instances with replication and backup. I
  am not very familiar in scaling heavy load systems.

Galera-based clusters like Percona XtraDB Cluster support true active/active multi-master, so failover is seamless because you can actually be writing on any node at any time (tho it's recommended to write only on a single node to avoid performance issues due to lock-conflicts that can happen due to the optimistic locking  strategy used by Galera).

This type of cluster can also do distributed fully-synchronous reads so you can scale heavy read loads without problems;  One shining example is Magento, which is read-heavy and has many reads that are critical -think money- and traditionally needed to be done on master to guarantee consistency, and here they can be distributed.

Keep in mind that as with any distributed CP system, Galera-cluster will add latency to the writes as it has to verify the write is valid on the other nodes, and hence it's not designed to scale writes;  That said:  it can usually withstand same write load than a regular master, as long as you keep transactions small (in terms of amounts of rows affected) and short (in regards to time spent by active transaction holding locks).

Also make sure to read list of limitations here: https://www.percona.com/doc/percona-xtradb-cluster/LATEST/limitation.html

  What strategy should I adopt in order to migrate this architecture to
  GCP ?

To decrease downtime you can migrate by setting up the GCP cluster as an asynchronous slave of the current cluster (yes, Galera cluster nodes can act as a master and/or slave in traditional, binlog based, asynchronous replication).  To prime the slave cluster you can use XtraBackup, which can take fully lock-less backups from current cluster (make sure to use latest 2.3 XtraBackup) which you can then restore on one of the nodes and allow the other two nodes to perform Snapshot State Transfer.  Then simply designate one of the nodes to become the slave, and start replication using binlog coordinates from xtrabackup_binlog_info.

Overview of steps would be:

Enable log-bin, log-slave-updates, server_id on one of the nodes from the current cluster
Take a backup of that node using xtrabackup (make sure to desync the node before: https://www.percona.com/blog/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/).
Bring up one of the nodes in GCP cluster and restore the backup into it, and bootstrap the cluster with this node. Most likely use cloud storage to feed the backup to the cluster.
Bring up the other two nodes in GCP, one at a time and allow them to perform SST
Setup one of the GCP cluster nodes as async slave to the current cluster (this node also requires log-bin, log-slave-updates, server-id); this node will feed the other nodes with updates
Failover to new cluster once the slave node is caught up

Hope that helps! :)

Full disclosure: am a member of Percona Support team.


You are missing a webserver_id column in the TEST table.

CREATE TABLE test(id integer primary key AUTOINCREMENT
                , page varchar(10)
                , dp_record_id integer
                , webserver_id integer
                , foreign key (webserver_id) REFERENCES target_dp(id));

Works just fine.


SQL Server 2008: how do I hide data from a DB Maintainance User?

Sysadmins can see all the data in the instance and there is no way to restrict the permissions of members of the sysadmin server role.

The only possible way to hide data from sysadmins is by encrypting it, using a key that is not available on the server. This is exactly what the Always Encrypted feature in SQL Server 2016 does.

In SQL Server 2008 your options are very limited: you can use a custom encryption layer, but you would have to code it yourself. Also, unlike what happens in Always Encrypted, there would be nothing to enforce that the columns you want to encrypt actually contain only encrypted data (the application has to take care of that).

That said, this is not only a tech issue: the company that does maintenance for you is a trusted partner, so you can talk to them straight and ask not to look at the data. You can also set up auditing to catch unauthorized reads/writes. However, it's a grey area: if they're troubleshooting a performance issue, for instance, they will probably run some queries and see some data.

Bottom line is: if you don't trust a third party, don't let them manage your instances.


How can I find out the last time a MySQL table was analyzed?

There is no immediate control data to tell you that, but there are a few mechanisms you can setup.
MECHANISM #1
If you have binary logging enabled, simply do a grep -i "analyze table" against all the binary logs using the output from mysqlbinlog.
MECHANISM #2
If you have the general log enabled, simply do a grep -i "analyze table" against the general log file and locate the timestamp just about the command.
MECHANISM #3
You should schedule a cronjob that runs ANALYZE TABLE against all tables that have high-write, high-update, high-delete volume. That way, there is no guess work.
MECHANISM #4
Try setting innodb_stats_on_metadata to have a measure of predictability as to when an InnoDB table needs ANALYZE TABLE. (See my Mar 26, 2012 post  :
When are InnoDB table index statistics updated? )
CAVEAT
In the past, I have often stated that running ANALYZE TABLE table against InnoDB is useless.

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Aug 04, 2011 : Optimizing InnoDB default settings
Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down

Hopefully, MECHANISM #4 is probably what you need.


I'd recommend reading Erland Sommarskog's "Dynamic Search Conditions" post here

In your case, here's a fiddle with the pattern:
SQL Fiddle

For posterity, here's the code:

SELECT *
FROM records r
WHERE
EXISTS (
  SELECT 1
  FROM access_rights a
  WHERE r.[function] = a.[function]
  and a.[user] = 'user'
  AND (A.[object] = r.[object] or a.[object] = ''))

Also, please stop using reserved words as column names.


Creating Database Mail with T-SQL returns "principal name is not valid"

Is the name you're using a user in the msdb database?

  [ @principal_name = ] 'principal_name'
  The name of the database user or role in the msdb database for the association. principal_name is sysname, with a default of NULL. Either principal_id or principal_name must be specified. A principal_name of 'public' makes this profile a public profile, granting access to all principals in the database.


Apache Ignite as linked Server in SQLServer?

Maybe you don't have tables for your caches. By default, Apache Ignite will not create tables in its caches - i.e. they are not mapped to SQL but work in key-value mode.

There are basically three options to make tables in Ignite - CREATE TABLE, Query Entities, and Indexed Types + annotations.

You can validate it by connecting to cluster with built-in sqlline utility and then issuing !tables command.


A synonym is an alternative name for an object.

From Database SQL Language Reference

  Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.

So the synonym is an additional name that you can use to access the object. But you must have still appropriate grants on the base object.

If SYNAAA is a public synonym for the table A.TABAAA and you get an error
when you execute

select * from SYNAAA

then you will get the same error if you execute

select * from A.TABAAA

the error

ORA-00942 table or view does not exist

means either the table A.TABAAA  exists but you have no privileges on it or the table does not exist at all. The view all_tables doe not show you all tables of the database but only the tables that you can access. Generally  you cannot check if a table does not exist at all nor can you grant yourself privileges on a table of another schema. A DBA can  query the dba_tables view to check if the table exists.

select owner, table_name
from dba_tables
where owner='A'
and table_name='TABAAA'

If Oracle raises an error message and you post this here then you hould always post the complete error message, which is

ORA-00942 table or view does not exist

An explanation of the error message can be found in the documentation of your database version, in the documentation of the current version. If there is no explanation you should try the documentation of Oracle 9i.
For this error message  the 9i manual says

  (...)
  Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.


How password can be prompted in percona xtrabackup

I donot think there is --ask-pass in xtrabackup! You might want to write a wrapper shell script which will

echo enter mysql password
read -s pw

and then pass it as --password=$pw.


Possibility to implement time to execute / TempDB usage restrictions on some role groups in SQL Server?

You cannot limit some users for use the certain amount of tempdb only, instead, you can put the limit to it's max size.

Anyway, putting tempdb at the same disk with your system files is a bad idea.

You can monitor who is consuming your tempdb making smth like this:

    create view [dbo].[vw_tempdb_usage]
    as
    select session_id,
           cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
           cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
           cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
           cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
           cast(sum(internal_objects_alloc_page_count -
                    internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
           cast(sum(user_objects_alloc_page_count -
                    user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
    from sys.dm_db_task_space_usage
    group by session_id
    having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count +
          user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0;

go

    CREATE TABLE [dbo].[tempdb_details](
        [dt] [datetime] NOT NULL,
        [spid] [smallint] NOT NULL,
        [db] [nvarchar](128) NULL,
        [loginame] [nchar](128) NOT NULL,
        [hostname] [nchar](128) NOT NULL,
        [program_name] [nchar](128) NOT NULL,
        [login_time] [datetime] NOT NULL,
        [internal_objects_diff_Gb] [decimal](10, 2) NULL,
        [user_objects_diff_Gb] [decimal](10, 2) NULL,
        [cmd] [nchar](16) NOT NULL,
        [txt] [nvarchar](max) NULL,
        [Statement] [nvarchar](max) NULL,
     CONSTRAINT [PK_tempdb_details_dt_spid] PRIMARY KEY CLUSTERED
    (
        [dt] ASC,
        [spid] ASC
    ));

go

    insert into dbo.tempdb_details
               (dt, spid, db, loginame, hostname, program_name,
                login_time, internal_objects_diff_Gb,
                user_objects_diff_Gb, cmd, txt, Statement)
    select GETDATE() as dt,
           p.spid,
           db_name(p.dbid) as db,
           p.loginame,
           p.hostname,
           p.program_name,
           p.login_time,
           v.internal_objects_diff_Gb,
           v.user_objects_diff_Gb,
           p.cmd,
           txt.text as txt,
           CASE WHEN encrypted = 1 THEN '-- ENCRYPTED'
                   WHEN p.stmt_start >= 0
                   THEN substring(txt.text, stmt_start/2 + 1,
                                  CASE stmt_end
                                       WHEN -1 THEN datalength(txt.text)
                                       ELSE stmt_end/2 - stmt_start/2 + 1
                                  END)
              END AS Statement
    from dbo.vw_tempdb_usage v join sys.dm_exec_sessions s
            on v.session_id = s.session_id
         join sys.sysprocesses p
            on v.session_id = p.spid
         cross apply sys.dm_exec_sql_text(p.sql_handle)  txt
    where v.internal_objects_diff_Gb > 1 or v.user_objects_diff_Gb > 1;

Here I created a view using sys.dm_db_task_space_usage that shows me in real time who is using more than 1Gb of tempdb now.
Then I put the query that reads from this view in a job running every 5 minutes,
so it captures every query along with it's owner if it consumes more than 1Gb. Then it's up to you how you want to react to it. My actions are simple: tempdb has a limited size (100Gb) so it's impossible to overflow it. Every violator is just rolling back receiving smth like this:

  Could not allocate space for object 'dbo.Large Object Storage System
  object:  422539438653440' in database 'tempdb' because the 'PRIMARY'
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup.  error.

At the same time every attempt to overflow it is captured by my job. So I can easily see who it was and what he was doing, and take actions.


Run PS-Script after job is done on 3 instances - SQL Server

You have a few options on how you could handle this...

Create a Scheduled Task on that server that contains a PowerShell script. The first part of that script contains a workflow that would iterate over each instance and call the backup job on each instance. Use a foreach -parallel would allow all the jobs to start at one time. You would then need to script out a while loop to wait and see that all jobs have completed. Then the end of that PowerShell script just call the code you need to run.
Pick an instance and use it as a "central" location to actually run the backups against each instance. You could utilize linked servers to remotely call Ola' scripts. This would offer a bit more control and ease of management because you would know once the command finished the backup for that instance are complete. Your last step would just be your PowerShell script.
Leave each backup job on the given instance. Add a scheduled task that calls your PowerShell script, but at the start of that script check to make sure a recent backup has been done. If not, just stop the script. Schedule that task to run between an the average end time of the backup jobs until a given a few hours later. This would ensure at some point it will find that the backups on all instances are current, then execute your code.


I am wondering what the best course of action is and if there is
  anything else i need to do to prevent this issue in the future once
  resolved.

As Kin Shah mentioned,

  To get you out, change the log size to unlimited, run a checkpoint and
  take a log backup (probably few). Why cap the tlog size ?

Setting the log size to unlimited and not capping it at 2GB whilst taking regular log backups should fix your problem.

In your case, that was not possible yet.

  I get the same error when trying to change the size to unlimited.

The error

  The transaction log for database 'SolarWindsOrion' is full due to
  'XTP_CHECKPOINT'"

A temporary solution could be adding another log file, taking a log backup & changing the size of the original log file.

An example of creating the log file, taking a log backup, changing the size to unlimited and removing the log file.

USE [master]
GO
ALTER DATABASE [DB_NAME] ADD LOG FILE ( NAME = N'DB_log2', FILENAME = N'F:\Log1\DB_log02.ldf' , SIZE = 65536KB , FILEGROWTH = 65536KB );
GO
ALTER DATABASE [DB_NAME] MODIFY FILE ( NAME = N'DB_log', MAXSIZE = UNLIMITED);
GO
BACKUP LOG [DB_NAME] TO DISK = '\\LogBackupLocation\DB_LogIssue.trn';
USE [DB_NAME]
GO
ALTER DATABASE [DB_NAME]  REMOVE FILE [DB_log2]
GO

--The file 'DB_log2' has been removed.

  That seems to have done the trick. As far as best approach moving
  forward. Now that i have the transnational backups happening every
  30secs (as originally planned) should that prevent this issue from
  happening in the future?

Depends on the Always On Availability Group settings and the size of some transactions if you keep the max size at 2GB.

Setting the max size of your log file to unlimited and on a separate log drive where it could grow should be best.
As far as scheduling log backups go, 30 seconds should be pretty good.


MySQL - Unable to login with password, but without password its working

network mask - is part of user name in MySQL

create user 'bhuvi'@'%' identified by 'bhuvi'; - You are create user with password
grant all privileges on . to 'bhuvi'@'localhost'; - You create 2nd user without password

1st have password, but not privileges

2nd have all privileges, but not password


What is the best way to create Primary Key on a large table in Postgresql 9.5?

You can create a unique index with the option concurrently which will allow read and write access to the table while the index is created. However, building an index concurrently will take much longer then adding the index without that option.

create unique index concurrently unique_id on the_big_table (id);

Once the index is created, you can use that as a primary key:

alter table the_big_table
   add primary key using index unique_id;

That will only lock the the table for a very short time.


Can every SPARQL query be expressed as a GraphQL query?

We are currently working on a GraphQL library to query a SPARQL Endpoint according to an OWL schema. It will be the main API access point for the DATAtourisme, french government project to handle and share touristic open data.

Contact me to tell me more about your project, i will tell you if the library can fit your needs.


Full Back up over to Network failed due to unknown network error 59

If you are having network reliability issues:

speak to your network/SAN team (and/or use a different file share that doesn't exhibit the problem)
Back up the file, temporarily, to the local SQL Server system, assuming there is adequate space. Then copy the backup to the network share, typically using a tool that can resume interrupted transfers, and delete the original when the copy has been confirmed (which usually includes validating that the backup can be restored). If a copy fails, no big deal; you just start the copy again, and you haven't lost anything.

And I agree with Max, this doesn't seem to be something that altering a session timeout will fix; that's kind of like changing a tire when your headlight is out. You might be better prepared for the next pothole, but you're still only driving with one light.


View SQL Server Agent jobs -> MSDB roles

SQL Server Agent Jobs can be targeted at non-local servers, via the "Target Server" property.

In order to make the "Target multiple servers" option enable, you need to setup a Master Server which enables automated administration across an enterprise.

SSMS is running the msdb.dbo.sp_help_targetserver stored procedure to obtain details about the servers that may potentially be listed in the "Targets" property window.

In order to run the sp_help_targetserver stored procedure, the user must be a member of the sysadmin role.  If the user is not a member of sysadmin, they can simply ignore the error message.  They should, however, be aware that in a Master Server environment the properties window may not show accurate details.

Running msdb.dbo.sp_help_job will, by default, run sp_help_jobserver.  However, if you run EXEC dbo.sp_help_job @job_name = 'some_job', @job_aspect = 'JOB';, the target servers are not checked, and not displayed.  This may provide a good way for non-sysadmin users to look at job properties, although not via the GUI.

FYI, the Microsoft Docs for SQL Server Agent Roles says this about SQLAgentReaderRole:

  SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

The above seems at odds with the Docs for sp_help_targetserver, which clearly states the user must be a member of the sysadmin role.


SSIS Promote Package - Move Some Stored Procedures

Have you looked at a third party tool such as SQL Compare from Redgate? This is designed to compare schemas and ease the process of deploying changes.
I don't think SSIS is the most appropriate tool for this task.


can't connect to remote postgresql database

You have to configure the following two files

pg_hba.conf

host all all 0.0.0.0/0 md5

postgresql.conf

listen_addresses='*'

You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/

If it's not then add a rule to your iptables:

iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT

0/0: If you want anybody to access it.
You can change it to a specific IP address or range of IP addresses.


Parameter for Sybase Sql Anywhere 12 Serverport

The proper syntax is:

dbsrv12.exe -z -x tcpip(ServerPort=54382)

No space between tcpip and the (. You can use "Port=" instead of "ServerPort=" if you want.


Query keeps on giving me 'unknown column' issue

The problem is that your select in the from part:

SELECT DISTINCT date(l.created_at),
(SELECT MIN(created_at) FROM logs WHERE updated_at BETWEEN l.created_at AND l.updated_at) AS TIME_ENTER,
(SELECT MAX(updated_at) FROM logs WHERE created_at BETWEEN l.created_at AND l.updated_at) AS TIME_EXIT
FROM logs l

does not return the field updated_at.


Restoring the superuser account on the postgres server

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install.

You  need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser.

There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit single user mode, and start the server back up.

The exact details are a little distro and version dependent, but something like:

service postgresql stop
sudo -u postgres postgres --single /var/lib/pgsql/9.1/main/data

ALTER USER postgres SUPERUSER;

(control-D to exit)

service postgresql start

On some systems you might need to use an explicit path to the postgres executable, like /usr/pgsql-9.1/bin/postgres. Again, paths may vary based on version and distro.


How to reset MariaDB into a "fresh install" state?

MariaDB can be reverted to the fresh state by removing its data files.

Say if you run MariaDB on a Debian you can do the next:

systemctl stop mysql
rm -rf /var/lib/mysql/*
systemctl start mysql

At the start if no datafiles exists MariaDB will recreate the internal scheme mysql.* with all default values. All leftovers like config and log files you have to clean up by hands.


sql script to export table's column data to another table's column

An update with a simple join will work.

CREATE TABLE Table_A
    (ID varchar(3) not null, Number int not null, Column3 varchar(5), Column4 varchar(5))
;

INSERT INTO Table_A
    (ID, Number, Column3, Column4)
VALUES
    ('ID1', 1, 'AAA', 'BBB'),
    ('ID1', 2, 'CCC', 'DDD'),
    ('ID2', 1, 'EEE', 'FFF'),
    ('ID2', 2, 'GGG', 'HHH')
;

CREATE TABLE Table_B
    (ID varchar(3) not null, Number int not null, Column3 varchar(5), Column4 varchar(5))
;

INSERT INTO Table_B
    (ID, Number, Column3, Column4)
VALUES
    ('ID1', 1, 'AAA_1', 'BBB_1'),
    ('ID1', 2, 'CCC_1', 'DDD_1'),
    ('ID2', 1, 'EEE_1', 'FFF_1'),
    ('ID2', 2, 'GGG_1', 'HHH_1')
;

alter table Table_A
add constraint pk_ID_NUM_A PRIMARY KEY (ID, Number)
go
alter table Table_B
add constraint pk_ID_NUM_B PRIMARY KEY (ID, Number)

select * from Table_A
select * from Table_B

Now run the update :

update Table_A
set Table_A.Column3 = Table_B.Column3
from Table_A inner join Table_B
on Table_A.ID = Table_B.ID
and Table_A.Number=Table_B.Number

Below is the expected result that you want :

select * from Table_A
select * from Table_B


is it reasonable to use NoSQL for my project

Given that you say you believe this data model screams "relational" the thought that comes to mind is, "I have a nail and I would really like to use a crescent wrench.... Can you?  Yes.  Are you aware of the tradeoffs?  Doubtful."

The basic point about NoSQL is that it makes sense when your data use cases are fairly simple and you have encapsulated that behind a full-featured API.  The example I give for an area where NoSQL would generally be very good would be the backplane of an LDAP directory forest.

For the application you mention your big issue is likely to be flexibility in reporting.  You would have to purpose build your reports in advance and that's the major cost.  For an application like this, that is a very big cost.

One other point I would make is that you may find that there are opportunities to use NoSQL along side the RDBMS.  For example you could implement a caching system for the web app using it.


I will try to :

Create empty table with indexes
DISABLE KEYS
Load Data
myisampack table
myisamchk -rq

Hopefully myisamchk will build the indexes without ENABLE KEYS


is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

Thanks to a How to make foreign key constraints trusted on StackOverflow, I figured out that the constraint was untrusted because it was marked NOT FOR REPLICATION. After re-creating the constraint without NOT FOR REPLICATION, all were trusted!

This behavior makes sense. If the constraint isn't replicated, other replication participants won't be enforcing it. When changes made by those other participants make their way back to the database I'm working with, there's no guarantee that the foreign key constraint's condition is satisfied—so NOT FOR REPLICATION forces the constraint to be marked is_not_trusted.


query to check all SCD tables that have data for specific date

You should be able to create a stored procedure that executes your query to get the table names. Then you loop over the results and execute your effective date query for each table individually.

You might even be able to do this without wrapping it into an stored procedure.


I can't restore my database on SQL Server 2012. Exceeds size

Express Edition limits the size of the database to 10 GB.

The backup is small because it only contains pages that are used -- regardless of whether or not the backup is compressed -- but the original file sizes will be restored to the size they were when they were backed up. The file sizes are what count against the limit.

Assuming this is the problem, the only solutions are to shrink the files before taking the backup, or restore the backup on a different edition (probably Developer edition).


Filtering records not present with specific conditions in another table

One option would look something like this:

SELECT a.* FROM account a
  LEFT JOIN extra_info ei ON ei.account_id = a.id AND ei.data_key = 'test'
 WHERE ei.account_id IS NULL;

This might seem counter-intuitive, since "obviously" er.account_id is never actually NULL sitting at rest in the table, so I'll explain:

The LEFT JOIN of course means all rows from account and only the matching rows from extra_data... and by "matching rows" we mean those having an identical account_id and a value of 'test' in data_key.

Every row in a result set has, at some point during execution, either a value (or a NULL) for every column from all tables, even if those columns aren't listed in the SELECT list... so, in a LEFT JOIN where the table on the right has no corresponding rows, the columns in the result set that "came from" the right-hand table are NULL... so, WHERE ei.account_id IS NULL filters out the rows with a value for ei.account_id (the ones with matching account_id and 'test' in data_key) leaving you with a result set containing the rows that you want.

If it's not apparent now, it makes more sense when you remember that the WHERE clause is not telling the server which rows to find in this case... it's specifying which rows not to eliminate.

Now, if you need other data from extra_info in your report, you will need to [left] join that same table again in your query with different aliases in addition to ei in order to pluck out the other values.

@ypercube's advice on indexes still stands... in the case of the query in the question, an index on (data_key,account_id) would probably be more useful, since the subquery isn't correlated to anything in the outer query, while in the LEFT JOIN query, this index would be what you'd want:

[UNIQUE] KEY(account_id,data_key)


Why is it faster to do four simple selects instead of one select with left joins?

Your execution plan for the "individual queries" shows that pre-calculating the StartID, Category etc allows an index to be used efficiently on A, "seeking" straight to the record(s) you want (you have a Non-Clustered Index Seek in your query plan), having identified the given Category etc to search for.

The "single-query" with JOINs, on the other hand, needs to carry out the join between table A-B-C "on the fly" before returning the "top 1" result matching your criteria. This join will involve all the records of those tables where they match to the other related table (full clustered index scan).

If you are frequently querying by Day and Title like this, it looks like an index is missing (or statistics out of date) that allow table B and C to be searched by those criteria.

By the way, Top 1 will only give a deterministic result if you "order by" some criteria (or if your search statement is unique) - otherwise you will get the 1st row the query comes to, which may or may not be consistent between occasions when you run it.


Query hint? for deduplicated CTE in Merge

If you have a clustered (or covering) index starting with (Key1, Key2, Key3, ...) on the table that the CTE is querying, this should be a well-performing query.

Can you add a "Key4", preferably an identity column, in the source table to your non-unique clustered index, so you perhaps can make the index unique? That way, you could also set "Key4" as your ORDER BY in the ROW_NUMBER(). This might improve performance, but it's just a guess.

The fact that the Clustered Index Merge operator is over 90% of your plan doesn't by itself indicate a performance problem, it just says that there's not that much else to do in the query plan apart from the join. In fact, it may indicate that you have a well-optimized plan - when you have the proper indexes, a ROW_NUMBER() can be very efficient.


MYSQL - Problem with foreign keys on same table

You have identified the cause of the problem. A possible workaround is to create another table for the linked list:

CREATE TABLE calendars
  ( calendar_id INT PRIMARY KEY
  -- other columns
  ) ;

CREATE TABLE calendar_list
  ( calendar_id INT NOT NULL
  , calendar_next INT NOT NULL
  , PRIMARY KEY (calendar_id)
  , UNIQUE (calendar_next)
  , CONSTRAINT calendar_list_1
      FOREIGN KEY (calendar_id)
      REFERENCES calendars (calendar_id)
      ON DELETE CASCADE ON UPDATE CASCADE
  , CONSTRAINT calendar_list_2
      FOREIGN KEY (calendar_next)
      REFERENCES calendars (calendar_id)
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ;

The following query can then be used to show what you have now:

SELECT c.calendar_id,
       prev.calendar_id AS calendar_prev,
       next.calendar_next
FROM calendars AS c
  LEFT JOIN calendar_list AS prev
    ON c.calendar_id = prev.calendar_next
  LEFT JOIN calendar_list AS next
    ON c.calendar_id = next.calendar_id ;

The UPDATE will work fine with this setup. Test at SQL-Fiddle. (Deletes would be a different issue but that wasn't addressed with the previous design either. Setting to null would break the list if the delete was anywhere but the first or last point.)


Best practices on common person fields (Name, email, address, gender etc...)

I would tend to be very suspicious of any set of universal best practices because, for most of these fields, the devil is in the details.  Just because the information is relatively common doesn't mean that your application uses the data in exactly the same way that other applications use it.  That means your data model may need to be slightly different.

First & last name: Why are you capturing the name?  If you have a requirement to capture a person's full legal name (i.e. you are preparing legal documents or birth certificates), you probably want to allow more space for people to type than you would if you're just asking for a person's name so you have something to call them in your new web app.
Address: What are you going to do with the address?  What sort of addresses are you storing?  If you are storing the address of a property in the United States that you're creating a mortgage on, you likely care very much about getting a fully standardized address in which case the data model will probably want to hew very closely to whatever your address standardization tool returns.  If you just want people to be able to type in an address to deliver a product, a couple lines for freeform text is probably sufficient.  The length of the lines there may depend on the requirements of the downstream processes that do things like print address labels.
State: Assuming you can identify the valid state values, it probably makes sense to create a STATE table and create a foreign key relationship between the STATE and ADDRESS tables.  But the ability to identify the valid values implies that you're limiting the set of valid addresses at least to a particular set of countries.  That's fine for many sites but then you've got to do a bit of work to support a new country.
City: If you are dealing with data where there are potentially city-level regulations in place (i.e. where there are different sorts of tax rates that are applied based on the city), you may want to treat it much like the state and have a CITY table with the valid cities and a foreign key relationship between the CITY and ADDRESS tables.  On the other hand, if you're just trying to get a product delivered and you don't much care if you have various versions of the same city in your table, letting the user free-form enter text is sufficient.  Of course, if you are storing foreign keys, you'll have a fair amount of work to make sure that you have all the valid values.  But there are products where the whole point is that the company has already done that work (i.e. sales tax databases).
Phone: What are you doing with phone numbers and why?  Some applications will want to take in phone numbers in whatever format the user decides to enter them and preserve that formatting for all subsequent queries.  This would be common if you are designing a personal address book where users have their own preferences for how phone numbers are stored and displayed.  Other applications would want to ignore the formatting that is entered, extract only the numeric characters, and then format the data on retrieval so that all phone numbers have similar formatting.  If you're catering to businesses, you may want a separate field for users to enter an extension.  If you are trying to support an outbound calling process, you may want to store the area code and country code in separate columns because you'll want to make sure that you have time zone specific windows for calling people in different area codes (making a call to someone in the Eastern time zone at 10am is going to go over much better than making that same call to someone in the Pacific time zone where it is 7am).
Gender: For a great many applications, it's perfectly reasonable to store a gender code ('M' or 'F') in a table.  On the other hand, there are cases when you may want additional options (Other, Intersex, Transgendered) or where you need to store something like the gender at birth and the current gender.


Why all users excep sys and system can connect to oracle db after database deletion on multi database environment?

I finally found a solution. I changed sys password using orapwd. There is just one issue. As documentation says, the password for users which do not have sysdba or sysoper privileges will be expired after some time. So, to avoid this login as sysdba and grant sysdba and sysoper to the sys.


Copy PostgreSQL database from a remote server

If the DB server takes database connections from your host (usually on port 5432), then you don't need to use SSH.  In this case, you need to do the following:

pg_dump -h your_db_host -U db_user -C db_name | psql -h localhost -d local_db_name -U local_db_user

If you can only connect to your database from the host itself, you have to add the SSH logic from your MySQL example.

With -C, you will get a CREATE DATABASE command in the beginning of the dump so you don't have to create it yourself.  The above command will ask for a password twice unless you store these in a .pgpass file (assuming you are on linux/Unix).  Also, the local connection may be different if your DB doesn't listen on TCP (in this case just omit the -h localhost part).

For further details and options, see pg_dump documentation.


If you don't want to write your own parser, the quick and dirty solution would be to replace < and > with some other punctuation, so that the existing parsers don't decide to discard them as html tags.

SELECT *
FROM xmldocument
WHERE to_tsvector(regexp_replace(content::text,'[<>]',' ','g')) @@ 'boo2';

  As to why I don't just use ILIKE % etc. the reason is that I need to optimize by using a GIN index and I don't think it is possible or meaningful to build an index on simple VARCHAR values.

If you use the pg_trgm extension, you can build a gin index on VARCHAR values which will optimize ILIKE queries.  How effective it is depends on the size of your indexed documents, and the size of your query.  I'd recommend trying it and seeing how it does for you.

Version 1.2 of pg_trgm (to be included in PostgreSQL 9.6, but it is fairly easy to back-port into 9.4 and 9.5 if you are willing to compile some code) will be much more effective with large queries.


MySQL InnoDB over a Raw Device in an Active/Passive topology

It's worth noting that InnoDB's write ahead log (WAL), the ib_logfile*, is not the only thing that will need a file system. You have:

System tables in the mysql schema that likely use the MyISAM storage engine (.frm, .MYD, .MYI per table) (most are now using InnoDB in 5.7)
.frm files for each InnoDB table, even when using the shared system tablespace (table metadata that's required)
MySQL log files (error log, general log, binary log)
SSL artifacts
auto.cnf (where the MySQL instance UUID is generated and automatically stored)
db.opt file for each schema (in /<datadir>/<schema>/)
.par file if you create a partitioned table (gone in 5.7)
.trn and .trg files if you create triggers
InnoDB tmp tablespace (5.6+)
Persisted buffer pool page map (ib_buffer_pool, 5.6+)

All of the above are typically within the data directory, so as long as you have datadir=/some/valid/fs/path -- that's also replicated (e.g. DRBD) or shared (e.g. NFS, GFS, OCFS) between the two nodes -- then you'll be fine.

It's worth noting that the .frm, .par, .trn, .trg, and .opt files will go away with the new Data Dictionary.

Stay tuned for some big announcements there in the coming months! :)

It's not clear to me why you're using the RAW device? I'm sure you have your reasons though. :)

Good luck!


Auto update stats turned off for a user database on SQL Server

You have two questions:

1. Do you have to restart after setting auto update stats on? No.

2. Should you consider dropping the user created stats? If your statistics updates jobs (typically done with a maintenance plan or with Ola Hallengren's maintenance scripts) are taking longer than your maintenance window allows, then yes. SQL Server has to read the entire index (or table) to update each statistic, so you can end up with really long stats update jobs. If your maintenance window isn't a problem for you, then you can leave 'em as is.