Why are virtual log files not always allocated in order?

Paul Randal details a scenario where log VLFs may be allocated out of the expected sequence in this blog post.

Essentially, log file growth may result in unexpected allocation order, even in simple mode, when the existing VLFs cannot be re-used.

Why are you wondering about the order-of-allocation?  VLFs are used in FSeqNo order, both when writing new log records, and when performing rollback and rollforward recovery operations.

Having a database in Full Recovery model does not prevent out-of-order VLF allocation order.  I've created a minimally complete verifiable example:

SET NOCOUNT ON;
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'TestVLFSeq')
BEGIN
    ALTER DATABASE TestVLFSeq SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TestVLFSeq;
END
GO
CREATE DATABASE TestVLFSeq
ON (NAME = 'system', FILENAME = 'C:\temp\TestVLFSeq.mdf', SIZE = 10MB, FILEGROWTH = 10MB, MAXSIZE = 100MB)
LOG ON (NAME = 'log', FILENAME = 'C:\temp\TestVLFSeq.ldf', SIZE = 1MB, FILEGROWTH = 1MB, MAXSIZE = 10MB);
GO

ALTER DATABASE TestVLFSeq SET RECOVERY FULL;
BACKUP DATABASE TestVLFSeq TO DISK = 'NUL:';
GO

SELECT d.name
    , d.recovery_model_desc
FROM sys.databases d
WHERE d.name = N'TestVLFSeq';
GO

output:

╔════════════╦═════════════════════╗
║    name    ║ recovery_model_desc ║
╠════════════╬═════════════════════╣
║ TestVLFSeq ║ FULL                ║
╚════════════╩═════════════════════╝

USE TestVLFSeq;
GO

CREATE TABLE dbo.TestData
(
    someVal varchar(8000) NOT NULL
        CONSTRAINT DF_TestData
        DEFAULT ((CRYPT_GEN_RANDOM(8000)))
);
GO

INSERT INTO dbo.TestData DEFAULT VALUES
 /* insert 64 rows - ~512 KB of log usage */
GO 64

DBCC LOGINFO
GO

output:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║              0 ║      2 ║   253952 ║        8192 ║     34 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   278528 ║      770048 ║      0 ║      0 ║      0 ║         0 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

As you can see in the above output, we have 3 VLFs allocated.

Now, in a separate query window, run this to create log records that cannot be cleared, even by a log backup:

USE TestVLFSeq;
BEGIN TRANSACTION
INSERT INTO dbo.TestData DEFAULT VALUES
GO

Now, switch back to the first query window, and backup the database and the log to clear any VLFs that cleared:

BACKUP DATABASE TestVLFSeq TO DISK = 'NUL:' WITH INIT;
BACKUP LOG TestVLFSeq TO DISK = 'NUL:' WITH INIT;
GO

(The BACKUP DATABASE is not strictly required, but what the heck, we're backing up to "ether" anyway).

Now, when we check DBCC LOGINFO, we see:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║              0 ║      2 ║   253952 ║        8192 ║     34 ║      0 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      0 ║     64 ║         0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║         0 ║
║              0 ║      2 ║   278528 ║      770048 ║      0 ║      0 ║      0 ║         0 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

The first and second VLF have been cleared.  If we now insert another 64 rows into the table, and look at DBCC LOGINFO:

INSERT INTO dbo.TestData DEFAULT VALUES
 /* insert 64 rows - ~512 KB of log usage */
GO 64

DBCC LOGINFO
GO

We see:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║     CreateLSN     ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════════════╣
║              0 ║      2 ║   253952 ║        8192 ║     38 ║      2 ║    128 ║                 0 ║
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      0 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   278528 ║      770048 ║     37 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║     1048576 ║     39 ║      2 ║     64 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1302528 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1556480 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   286720 ║     1810432 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════════════╝

Pretty clearly, we have VLFs that are "out of sequence".  However, since SQL Server uses the FSeqNo to perform recovery-related operations, it doesn't matter that the VLFs are not physically "in order".

If, for some reason, you programmatically expect the VLFs to be in FSeqNo order, you could query DBCC LOGINFO; like this:

DECLARE @cmd nvarchar(max);
IF OBJECT_ID(N'tempdb..#loginfo', N'U') IS NOT NULL
DROP TABLE #loginfo;
CREATE TABLE #loginfo
(
    RecoveryUnitID int
    , FileID int
    , FileSize int
    , StartOffset bigint
    , FSeqNo int
    , [Status] tinyint
    , Parity tinyint
    , CreateLSN bigint
);
SET @cmd = 'DBCC LOGINFO;';
INSERT INTO #loginfo
EXEC sys.sp_executesql @cmd;
SELECT *
FROM #loginfo li
ORDER BY /* put "0" FSeqNo rows at the end */
    CASE
        WHEN li.FSeqNo = 0 THEN CONVERT(int, POWER(CONVERT(bigint,2),31) - 1)
        ELSE li.FSeqNo
    END;

Which has the following output for the "strange" VLF arrangement:

╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦═══════════════════╗
║ RecoveryUnitID ║ FileID ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║     CreateLSN     ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬═══════════════════╣
║              0 ║      2 ║   253952 ║      262144 ║     35 ║      0 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║      516096 ║     36 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   278528 ║      770048 ║     37 ║      2 ║     64 ║                 0 ║
║              0 ║      2 ║   253952 ║        8192 ║     38 ║      2 ║    128 ║                 0 ║
║              0 ║      2 ║   253952 ║     1048576 ║     39 ║      2 ║     64 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1302528 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   253952 ║     1556480 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
║              0 ║      2 ║   286720 ║     1810432 ║      0 ║      0 ║      0 ║ 38000000039500007 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩═══════════════════╝

VLFs marked with a Status of "2" indicates the VLF cannot be re-used.  You can determine what is preventing log re-use using the following query:

SELECT d.name
    , d.recovery_model_desc
    , d.log_reuse_wait_desc
FROM sys.databases d
WHERE d.name = N'TestVLFSeq';

If you run this code while there is an open transaction in the database, as in the example above, you'll see the output looks like:

╔════════════╦═════════════════════╦═════════════════════╗
║    name    ║ recovery_model_desc ║ log_reuse_wait_desc ║
╠════════════╬═════════════════════╬═════════════════════╣
║ TestVLFSeq ║ FULL                ║ ACTIVE_TRANSACTION  ║
╚════════════╩═════════════════════╩═════════════════════╝

The log_reuse_wait_desc column can have the following possible values, taken from the Microsoft Transaction Log Documentation:

CHECKPOINT - No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (VLF).  Applies to all recovery models.  Applies to SQL Server 2008 through SQL Server 2017.

This is a routine reason for delaying log truncation.

When a database has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.

You can force a checkpoint using the CHECKPOINT T-SQL command within the context of the database in question.
LOG_BACKUP - A log backup is required before the transaction log can be truncated.  Applies to full or bulk-logged recovery models only.  Applies to SQL Server 2008 through SQL Server 2017

Run BACKUP LOG <database> TO ... to create a backup of the log.
ACTIVE_BACKUP_OR_RESTORE - Applies to SQL Server 2008 through SQL Server 2017

Use the following query to see what backups are currently running:

SELECT d.name
    , der.command
    , der.start_time
    , der.percent_complete
FROM sys.dm_exec_requests der
    INNER JOIN sys.databases d ON der.database_id = d.database_id
WHERE der.command = N'BACKUP DATABASE'
    OR der.command = N'BACKUP LOG';

ACTIVE_TRANSACTION - Applies to SQL Server 2008 through SQL Server 2017

There is an open transaction that has not been committed or rolled-back.  Note this open transaction may not be actively running - it may be poorly written code that opens a transaction then performs some long-running action at the client, finally committing the transaction much later.

Consider the following:

A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. Note that long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint.
A transaction is deferred. A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.
Long-running transactions might also fill up tempdb's transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.

DATABASE_MIRRORING - Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database.  Applies to full recovery model only.  Applies to SQL Server 2008 through SQL Server 2017

If you see a large number of VLFs with Status = 2, and database mirroring is enabled, ensure database transactions are being applied to the mirror in a timely fashion.
REPLICATION - During transactional replication, publication-relevant-transactions have not been delivered to the distribution database.  Applies to full recovery model only.  Applies to SQL Server 2008 through SQL Server 2017.
DATABASE_SNAPSHOT_CREATION - Applies to SQL Server 2008 through SQL Server 2017

A database snapshot is being created.  Check the following for details on snapshots that exist on your SQL Server:

SELECT SnapshotDBName = d.name
    , SnapshotCreateDate = d.create_date
    , SourceDBName = d_source.name
    , SourceCreateDate = d_source.create_date
FROM sys.databases d
    INNER JOIN sys.databases d_source ON d.source_database_id = d_source.database_id
WHERE d.source_database_id IS NOT NULL
ORDER BY d.create_date;

LOG_SCAN - This is a routine, and typically brief, cause of delayed log truncation.
AVAILABILITY_REPLICA - An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. Applies to SQL Server 2012 through SQL Server 2017.
OLDEST_PAGE - Applies to SQL Server 2012 through SQL Server 2017

If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation. (All recovery models)

For information about indirect checkpoints, see Database Checkpoints (SQL Server).
OTHER_TRANSIENT - This value is currently not used.  Applies to SQL Server 2012 through SQL Server 2017
XTP_CHECKPOINT - When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint. - Applies to SQL Server 2014 through SQL Server 2017


SQL Server 2005 Restore from Full and Differential Backup

Hopefully, my previous research can be helpful for you. Here is a step-by-step guide I created when I had to move several very large databases to a data center. It uses log shipping.

Regards,
John


Bandwidth comparison between log shipping and transactional replication

It depends on your workload.  As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour.  Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour.  Then setup Transactional Replication and use Peformance Monitor to log the network counters for 1 hour.  The network bandwidth that Log Shipping and Transactional Replication consumes is the difference between the collections and the baseline.  The same can be done for memory and I/O.


Optimizing the repeated use of the same if condition inside a VIEW?

Test

CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
    table0.fieldBlah "FieldBlah",
    --more fields
    IF(@tmp := (table1.foo or table2.foo IN ('State1', 'State2')), 0, table3.uniqueField) "uniqueFieldName",
    IF(@tmp, 0, table3.uniqueField2) "uniqueFieldName2",
    --more of these if statements with the same condition ,but different field
FROM table1
--various joins with other tables
);


Alternative to xp_cmdshell for emailing a report as a CSV file

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:

  Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

Because of this, and assuming that you won't have this process running more than once at any particular moment, you should dump the desired rows from the Table Variable into a Global Temporary Table (i.e. starting with ## instead of #).

I believe sp_send_dbmail uses Service Broker and is asynchronous. This would seem to allow for the possibility of the global temporary table not existing when the query in @query finally gets executed, if the session that executed sp_send_dbmail ends before the query in @query starts. In this case, you can do either:

add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

SET NOCOUNT ON;

DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                  + CONVERT(NVARCHAR(36), NEWID()) + N']';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
EXEC (@SQL);

SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
EXEC (@SQL);

DECLARE @SomeTableVar TABLE ([Col1b] INT);
INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);

INSERT INTO dbo.TempDump ([Col1a])
  SELECT Col1b
  FROM   @SomeTableVar;

SELECT * FROM dbo.TempDump; -- only needed for debug

SET @SQL = N'DROP SYNONYM dbo.TempDump;';
EXEC (@SQL);

-- create the report query, ending with the DROP statement
DECLARE @ReportSQL NVARCHAR(MAX) = N'';
SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
print @ReportSQL;

-- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
--                      @query_result_separator = N',', @query_no_truncate = 1, ...;

If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).


Fixing MyISAM database without losing data

First of all, if your data is worth anything, don't store it in MyISAM.

I don't think you can reconstruct a table from corrupted MYD file. Records in MyISAM have different format that depends on values stored in each record. There are about 20 block types! To make it more fun the records in MyISAM can be fragmented. So, if you absolutely need the data from MyISAM table the only way I'm aware of is a C program with wildcard masks.

You can partially recover the table if you select from it ranges of the primary key. Corrupt PK ranges will be lost, but at least if the range is good you can get it back.

Here's a script that I wrote a while ago to read as many records is possible from a corrupted table. Try it out, I hope it'll help:

#!/bin/bash

# Adjust your values here
host=localhost
user=root
db=test
table=test
PK=id
newtable=newtest2
# end of user defined values

read -p "Password: " -s password
echo ""
if [ "xxx$password" = "xxx" ]
then
        pass_cmd=""
else
        pass_cmd="-p$password"
fi
mysql -h $host -u $user $pass_cmd -e "show databases" $db
if [ $? -eq 0 ]
then
        echo "Credentials are ok!"
else
        echo "Can't run query SHOW DATABASES"
        echo "Check what's wrong"
        exit
fi
minPK=`mysql -sN -h $host -u $user $pass_cmd -e "SELECT MIN($PK) FROM $table" $db`
maxPK=`mysql -sN -h $host -u $user $pass_cmd -e "SELECT MAX($PK) FROM $table" $db`
a=$minPK
b=$maxPK
let chunk=$b-$a

function insert_range {
q="INSERT IGNORE INTO \`$newtable\` SELECT * FROM \`$table\` WHERE \`$PK\` >= $1 and \`$PK\` <= $2"
mysql -h $host -u $user $pass_cmd -e "$q" $db 2>/dev/null
}
successful_tries=0
echo "Primary key ($PK) range: $minPK .. $maxPK"
while true
do
        insert_range $a $b
        if [ $? -eq 0 ]
        then
                echo "Good, primary key range: $a .. $b"
                let a=$b+1
                let b=$b+$chunk
                let successful_tries=$successful_tries+1
        else
                echo "Bad luck, primary key range: $a .. $b"
                let chunk=$chunk/2
                let b=$a+$chunk
                mysqladmin -h $host -u $user $pass_cmd ping > /dev/null 2>&1
                while [ $? -ne 0 ]
                do
                        mysqladmin -h $host -u $user $pass_cmd ping > /dev/null 2>&1
                done
                successful_tries=0
        fi
        if [ $a -gt $maxPK ]
        then
                exit
        fi
        if [ $chunk -eq 0 ]
        then
                let a=$a+1
                let b=$a+1
                let chunk=1
        fi
        # If things are going well, increase pace
        if [ $successful_tries -gt 5 ]
        then
                let chunk=$chunk*2
        fi
done


Copy contents of network drive to local folder

*.* will only return the files directly under K:\ (You probably have a folder with the database/instance name here). Use Copy-Item K:\ -Recurse M:\VoxtronRestore instead.


Find Employees With Multiple Managers Listed

Here is one approach

WITH DuplicateManagers
AS (
    SELECT empid
        ,count(*) AS rcount
    FROM #DuplicationIdentifier
    GROUP BY empid
    HAVING count(*) > 1
    )
SELECT a.empid
    ,a.managerid
FROM #DuplicationIdentifier a
JOIN DuplicateManagers b ON b.empid = a.empID
ORDER BY a.empid


I would not use any of those options: I would properly split out each part of the log entry, putting each in its own column of appropriate maximum length. This is more work of course as you need to break the data down for insert if you are getting it as a single string (it'll also consume a bit more disk space, probably) but it will greatly increase the analysis you can do on the logs later.

Also define useful indexes. At very least something on that ordering column and/or if there is a date+time in those logs there should be an index covering that.

If you are just storing logs as blocks of text per line then there is very little point pushing them into the database: just keep them in their original flat files where you can use grep and other such tools. If you want to take advantage of the database's features at all, create a table optimised for making best use of the DB instead of optimising solely for easiest insert convenience.

You could also break down the consistent parts of query strings, form parts, and cookies (I'm assuming these are HTTP(S) logs) into their own sub-tables, but unlike separating basic log variables that is probably going to be considerable overkill.


(Disclaimer for peeps flying in from Google: I do not recommend using the method asked about in this question to reclaim log file space! Use DBCC SHRINKFILE instead.)

  I am presuming that all the data is already in the .mdf file?

Yes, detaching initiates a CHECKPOINT in the database, so all dirty data pages make it to disk.

A database cannot be detached if the transaction log is required by another process (mirroring, replication, etc.) or if exclusive access cannot be obtained (all open transactions in the database need to commit or roll back). Therefore, the log will never contain in-flight transactions like you might see if the power suddenly turned off (i.e., a crash).

  The .ldf file is essentially empty? If the .ldf file is not empty what does it contain?

SIMPLE recovery: the log file will contain the log records describing the CHECKPOINT that occurred before the database was shut down. (A CHECKPOINT also clears the log in this mode.)
BULK_LOGGED and FULL recovery: the log file will contain all the log records that haven't yet been cleared out. (The only process that clears the log in this mode is a log backup.)

Tip: log records in the log file can be inspected using the undocumented TVF sys.fn_dblog.

  If I delete the .ldf file and manually create a new 0 bytes long .ldf file with same name as before, I would be able to attach the database again with no data loss?

Doing this will destroy the file header information, thus corrupting the log file.

To get the database to attach, you would have to remove/rename the log file, and attach the database using ATTACH_REBUILD_LOG to have SQL Server create a new log file from scratch in the default location.

Assuming you did that,

In SIMPLE recovery, no user data would be lost as all that disappeared were relatively unimportant CHECKPOINT log records.
In the other recovery models, it would break the log backup chain, and obviously any deleted log records could no longer be backed up (and probably weren't already backed up). This has obvious implications should the database need to be restored.

So... technically, no, there wouldn't be any immediate data loss; however, this method is certainly not a good practice, particularly in a recovery model other than SIMPLE.


Help in designing mysql table for large project

If there is a possibility of an employee to work in many companies such as Advisor or Instructor, the above designs will fail.
There are possibilitites where one employee can work in more than one company.

So the perfect design would be the below

Company_Id   Company_Name
   1          Aaaa
   2          Bbbb
   3          cccc

Employee_Id   Employee_Name
   11          A
   22          B
   33          c

Company_Id   Employee_Id
   1          11
   2          22
   3          33
   3          22

This works in both the ways. One employee can work in many companies and many companies can have same employee. Many to Many relationship .


What is the proper way to store a full table of data outside of a database? It needs to be able to be restored as well

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises.

If you want the data to be restored later, then best is to

Script out schema of the tables that you wish to drop and save it as a sql script.
BCP out the data (without using -n switch as -n is for native format which is not HUMAN readable) and store it in a safe place. If you want to save some space, you can also ZIP it.

  how do you properly store a table of data outside of a database?

If you want to really store the data outside of database, then option 1 and 2 are the ones that I will go for.

Now, what I will still do is -

Make a copy of the current database (using backup / restore method).
Restore it with a database_Archive name
Remove the tables that you don't need to archive i.e. Keep only tables that you will be eventually dropping from the main database (as per your Boss's requirement).
Then do a one time shrink (since you will be getting rid of lot of data)
Backup the final archive database.

The benefit of this is that

whenever you need to archive the data, it will be easy.
You dont have to manage individual files.
the database will be backed up, just in case you need it for a later time.
You can query the archive database anytime you need.

Check out the hyperlinks as it has method, scripts and best practice to help your out.


java.sql.SQLRecoverableException: No more data to read from socket

For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply.

You have two ways to address this:

upgrade to 11.2
set oracle parameter _optim_peek_user_binds = false

Of course underscore parameters should only be set if advised by oracle support


MariaDB event to automatically move data older than a specific time to another table without temporary table?

You say:

but we have the issue that, if in theory, the insert query takes up to
5 minutes, there will be a difference in the result between the INSERT
INTO and the DELETE statement, leading to 5 minutes of lost data.

No, you don't have to risk losing data! That's what TRANSACTIONs are for!
InnoDB uses (Multi-Version Concurrency Control - MVCC). You should read up on this important topic in order to better exploit the capabilities of your server.
Something like this will work:
SET TRANSACTION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;

  INSERT INTO api_log_archive
    SELECT *
    FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT 5000, it's not necessary

  DELETE FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0; -- removed LIMIT

  COMMIT;

SET TRANSACTION LEVEL READ COMMITTED;  -- or whatever your default is

Put this into an EVENT and schedule it to run every day at (say) 03:30 every night.
Once you are happy that your event is working regularly at the time you require, then all you'll have to do is make monitoring it part of your daily monitoring routine.
Isolation levels
A brief explanation of the difference between the READ COMMITTED and the SERIALIZABLE transaction isolation levels follows:
The default TRANSACTION ISOLATION LEVEL on MariaDB is READ COMMITTED.
So, in terminal one, I do the following
Terminal_1_> SHOW TRANSACTION ISOLATION LEVEL;

Result:
 transaction_isolation
-----------------------
 read committed
(1 row)

That's what we want - so now we issue this command:
Terminal_1_> SELECT * FROM t;

Result:
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

That's as we want it - so, now we delve into transactions as follows:
Terminal_1_> BEGIN TRANSACTION;
BEGIN
Terminal_1_> SELECT * FROM t;
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

Here, we "suspend" the transaction by switching to another terminal 2...
We run this query:
SELECT
       pid,
       age(clock_timestamp(), query_start),
       usename, query
FROM pg_stat_activity
WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Result (active queries which are not the one above):
 pid  |       age       | usename  |      query
------+-----------------+----------+------------------
 3500 | 00:05:46.113281 | postgres | SELECT * FROM t;
(1 row)

So, we can see that my SELECT * FROM t; query is still active and is over 5 minutes old.
So, now, again in terminal 2, we insert a row into the table:
INSERT INTO t VALUES (3, 'Three');

And (still terminal 2), I check:
Terminal_2_> SELECT * FROM t;
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

And I go back to terminal 1 and issue a SELECT * FROM t; and the result is:
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

Now, to see the behaviour with TRANSACTION ISOLATION LEVEL set to SERIALIZABLE.
We issue the command:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE

I'm using a local PostgreSQL instance as that's the only thing I have running and running these sorts of commands isn't possible using a fiddle - the equivalents for MariaDB are here - I leave it up to you to construct the required tests with MariaDB.
So, from termina1, we check our settings:
SELECT name, setting, short_desc FROM pg_settings WHERE name ILIKE ('default%transaction%o%');

Result:
             name              |   setting    |                          short_desc
-------------------------------+--------------+---------------------------------------------------------------
 default_transaction_isolation | serializable | Sets the transaction isolation level of each new transaction.
 default_transaction_read_only | off          | Sets the default read-only status of new transactions.
(2 rows)

So, we're good to go. From terminal_1_> (SERIALIZABLE) we run;
BEGIN TRANSACTION;
SELECT * FROM t;

Result:
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

Now, from terminal_2_> we run our check on active queries again:
SELECT
       pid,
       age(clock_timestamp(), query_start),
       usename, query
FROM pg_stat_activity
WHERE datname = 'test' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Result:
  pid  |       age       | usename  |      query
-------+-----------------+----------+------------------
 11064 | 00:03:37.694149 | postgres | SELECT * FROM t;
(1 row)

The query in terminal_1_> is sitting there and is now > 3mins old...
From terminal_2_> again, we update the table:
INSERT INTO t VALUES (3, 'Three');

and:
Terminal_2_> SELECT * FROM t;
 x |   y
---+-------
 1 | One
 2 | Two
 3 | Three
(3 rows)

Now we go back to terminal_1_> and run SELECT * FROM t; a second time within the same serializable transaction.
and we get:
Terminal_1_> SELECT * FROM t;
 x |  y
---+-----
 1 | One
 2 | Two
(2 rows)

The SERIALIZABLE transaction has the same view of table t that it had at the beginning of transaction, despite terminal_2_> having INSERTed a new record...
I would urge you to experiment with differing setups to get to know your server's capabilities and how you can best exploit them...
In terms of your problem, what this means is that:
Scenario 1 (you keep the defaults on your server - i.e. REPEATABLE READ):
At time t1, you run this:
CREATE TEMPORARY TABLE data_to_move
SELECT *
FROM api_log
WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0 LIMIT 5000;

Say, for example, you SELECT 10 records. It's a log table, so it's constantly being updated...
At time t2, you INSERT into your archive table.
INSERT INTO api_log_archive
SELECT * FROM data_to_move;

At time t3, you DELETE from your api_log
DELETE
FROM api_log
WHERE id in (SELECT id FROM data_to_move);

Now, your process will miss any records that have aged to over 6 months between t1 and t3 - so, fine, you say, I'll pick them up the next time...
Scenario 2 (your run your archiving process with the TRANSACTION ISOLATION LEVEL set to SERIALIZABLE).
For starters, you can cut out the CREATE TEMPORARY TABLE phase as follows:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE        -- check out MariaDB syntax for this

BEGIN TRANSACTION;

INSERT INTO api_log_archive
  SELECT id
  FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0;

DELETE FROM api_log
WHERE id IN
  SELECT id
  FROM api_log
    WHERE (datum - UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -6 MONTH))) < 0;

COMMIT;

-- clear up - set your transaction defaults back to your preferred default...

The advantage of this is you don't have the overhead of creating a potentially large TEMPORARY TABLE, you reduce the number of steps from 3 to 2 and your system will be more robust.

There is another possibility - you can lock your tables for the duration of your update - however, this means that no writes can occur to the api_log table during the time of your transaction - it's generally considered not to be good practice if it can be avoided. If you wish to explore this possibility, then you might like to ask another question...

This fiddle should help clarify things - PostgreSQL has the useful (and intelligently explicit) functions TRANSACTION_TIMESTAMP and CLOCK_TIMESTAMP - with READ COMMITTED any query sees the data as of CLOCK_TIMESTAMP and a SERIALIZABLE transaction sees the data as of TRANSACTION TIMESTAMP!


That are orphaned temporary files, when mysql crashes during an alter table

Orphan temporary table names begin with an #sql- prefix (e.g., #sql-540_3). The accompanying .frm file has the same base name as the orphan temporary table.

see manual
There you will find also how to deal with them
in mysql 8 they don't appear any more


It is a bit kludgey, but here's an idea sketch... instead of merging the cells, could you fake it by placing an A in the left cell aligned to the right and a D in the right cell aligned to the left?  You could then use expressions to control value, alignment, color, and borders of the two separate cells.

Let me know if you need more details.  I think this would give you the visual you want.  Good luck!


Unable to update table after creating an After update trigger

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` BEFORE UPDATE ON `pageprivilege`
    FOR EACH ROW
    BEGIN
     if (new.ad = 0 AND new.ed =0 AND new.dl =0 AND new.rd = 0) then
       set new.st=0 ;
     end if;
END;
$$

DELIMITER ;


How can I create a copy of postgis db from one windows machine to another

As @a_horse_with_no_name wrote pg_dump and pg_restore cope with PostGIS things without problems. At least since PostGIS 2.0, previously (before PostGIS became an extension) you might had to change paths of the c-library used for geometry-functions.

In PostgreSQL since 9.x ist is also fairly easy:

backup at the original databse with pg_dump
at the new system:

create a new database
login to that new database and run CREATE EXTENSION postgis;
restore the backuped file with pg_restore to the new database

That works for me as well on windows as on linux, you could even use PgAdmin for it.

Note: the version of pg_dump of the original (backed up) system may not be newer than of the new system - see https://stackoverflow.com/q/12836312/8957103


Putting foreign keys of several tables into the same column

-- Asset AST of asset type ATYP exists.
--
asset {AST, ATYP}
   PK {AST}
   SK {AST, ATYP}

CHECK ( ATYP in ('A', 'B') )

-- Asset AST, of asset type "A" exists.
--
asset_a {AST, ATYP}
     PK {AST}

FK {AST, ATYP} REFERENCES asset {AST, ATYP}

CHECK (ATYP = 'A')

-- Asset AST, of asset type "B" exists.
--
asset_b {AST, ATYP}
     PK {AST}

FK {AST, ATYP} REFERENCES asset {AST, ATYP}

CHECK (ATYP = 'B')

-- Portfolio PTF exists.
--
portfolio {PTF}
       PK {PTF}

-- Portfolio PTF contains asset AST.
--
portfolio_asset {PTF, AST}
             PK {PTF, AST}

FK1 {PTF} REFERENCES portfolio {PTF}
FK2 {AST} REFERENCES asset     {AST}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key


Migrate SELECT query from oracle to postgresql

This:

AND   EXTRACT(month from age(NOW(),td_inf.date_chgt_etat)) > 2

is something different then the original Oracle condition.

The extract functions does not get the equivalent of the interval in months, but simply the "month" field. So if the interval is "1 year  0 months 2 days 16 hours", extract(month from ...) will return 0.

What you want is:

and age(now(), td_inf.date_chgt_etat) > interval '2 months'

or if you want to make that use an index on the column:

and td_inf.date_chgt_etat < now() - interval '2 months'

Your query is also missing the

AND   ch.cod_ent in (...)

You have translated that to a JOIN which does something different. Whether or not that changes the result depends on how the two tables com_ent_ft and com_habilitation are related.


Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL

You can create a function and use it as the default for the column:

create function f() returns text language sql as $$
  SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
  FROM generate_series(1, 20)
$$;

create table foo(id serial primary key, url_prefix text not null default f());

insert into foo default values;

select * from foo;

id | url_prefix
-: | :-------------------
 1 | iByDaJ2h4JHi3dI3uBoy

db<>fiddle here


If you are doing SQL, then you probably would need to do a TRUNCATE/DELETE on the table and then INSERT into.

If you are using LOAD and/or IMPORT, they both have options for clearing the table before getting data into the table.


Why won't pg_basebackup output go to log?

You specified #!/bin/sh in your script's shabang line. This gives you a POSIX shell. Under POSIX style shells you can only specify numbers for redirection, as noted in the documentation under section 2.7 Redirection.

&> is a "bashism", shown here in the bash documentation under io-redirection.

This can cause undefined behavior under POSIX compliant shells acting as /bin/sh, and by running shellcheck -s sh on the contents of the script in the above question, as stated under the warning for SC2039, which warns that &> is non-standard, and might fail under different contexts.

You might have better luck, if you specify #!/usr/bin/env bash, or if you change the way that you're doing the redirection, if you want to keep using the #!/bin/sh shell.

As for the $BACKUP_PATH, you might want to double-quote it to prevent any sort of weirdnesses with globbing or word splitting that can happen, which could also possibly explain the zero size tar file.

Hope that helps. =)


Tools or built-in functionality to alert upon table data changes

If you need the data changes themselves then you will probably need to use Triggers. You could convert all values to string, build an HTML table of those changes, and then send that via sp_send_dbmail. Please note that while sp_send_dbmail does accept a query to include the results of in the email (either in the body or as an attached file), it runs in a separate session so it won't have access to the INSERTED or DELETED pseudo-tables, nor even local temporary tables (and global temporary tables either might not exist when the query is run or it would at least interfere with multiple DML operations happening at the same time, even if those are unlikely). You could also include the login information from SESSION_USER and ORIGINAL_LOGIN().

Regarding performance and Triggers, there doesn't seem to be much reason for concern:

These are lookup tables and there aren't that many changes coming through.
The sp_send_dbmail command runs asynchronously via Service Broker.

Another option is to track the data automatically in your version control system (e.g. TFS). This does not mean that the changes are introduced automatically into release scripts, but the changes are tracked so that you can investigate what changed and then figure out how to best get it scripted into your release process. I wrote an article on SQL Server Central several years ago about doing just this: Exporting and Versioning Lookup Data: A Real-World Use of the CLR (free registration is required to read content on that site). That article discusses using a SQLCLR Stored Procedure to do a daily/nightly export of the lookup tables into delimited files. Please note that the SQLCLR Stored Procedure, DB_BulkExport, is found in the SQL# library (that I created), and was available in the Free version when the article was written, but is now only in the Full version and no longer available for free. However, there is always the option of writing ones own SQLCLR Stored Procedure to do the same thing.

The system that I developed and discussed in that article is based upon running via a SQL Server Agent job once per night. But we had hundreds of lookup tables. The effects of running nightly are that you miss incremental changes made during the day, and you aren't able to capture who made those changes. If you need each individual change and who did it, then you can rethink that approach to get kicked off from a Trigger that exports the data and calls whatever external program handles the commit into the versioning system. A SQLCLR Trigger might be able to accomplish the export and calling the "commit" program all at the same time since it does have access to the INSERTED and DELETED tables.


As always, it depends on a lot of factors, but I'll answer like this:

If the SSIS package takes only the deltas from the last run and performs well, I'd keep using the SSIS, since its something that is already working and I assume you are familiar with.

Log shipping and replication will work, but they have drawbacks. For instance, with log shipping you will have to disconnect all of your users when restoring the log backups on the target server.


How to copy a database using scripts only, no bak, no mdf, no ldf

Disable all your foreign keys before you start the transfer and then reenable them afterwards.

You might also want to think about your indexes, it's usually much quicker to disable the indexes before copying data and then rebuild them afterwards.

https://sqlundercover.com/2017/09/25/copying-data-from-one-table-to-another-to-disable-indexes-or-not-to-disable-indexes-thats-the-question/

Have a look at the below scriopt this is part of a much larger script so you might want to check that it does what you want but it should be ok.  Just assign the relevant database names to the @SourceDatabase and @DestinationDatabase variables.

It'll deal with your indexes and constraints, disabling them before and reenabling them after the transfer.  As I said, I've pulled this out of the much bigger process and tweaked it slightly so make sure you test it out.

SET NOCOUNT ON

DECLARE @SourceDatabase SYSNAME
DECLARE @DestinationDatabase SYSNAME

SET @SourceDatabase = 'SourceDB'
SET @DestinationDatabase = 'DestinationDB'

IF OBJECT_ID('tempdb..#DisabledIndexes') IS NOT NULL
    DROP TABLE #DisabledIndexes

IF OBJECT_ID('tempdb..#DisabledFKs') IS NOT NULL
    DROP TABLE #DisabledFKs

IF OBJECT_ID('tempdb..#DisabledTriggers') IS NOT NULL
    DROP TABLE #DisabledTriggers

--Check for any objects that are currently disabled
RAISERROR ('Check For Currently Disabled Indexes', 0, 1) WITH NOWAIT

SELECT SCHEMA_NAME(tables.schema_id) + '.' + OBJECT_NAME(indexes.object_id) AS TableName, indexes.name AS IndexName
INTO #DisabledIndexes
FROM sys.indexes
JOIN sys.tables ON indexes.object_id = tables.object_id
WHERE is_disabled = 1

RAISERROR ('Check For Currently Disabled FKs', 0, 1) WITH NOWAIT
SELECT SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) AS TableName, name AS FKName
INTO #DisabledFKs
FROM sys.foreign_keys
WHERE is_disabled = 1

RAISERROR ('Check For Currently Disabled Triggers', 0, 1) WITH NOWAIT
SELECT SCHEMA_NAME(tables.schema_id) + '.' + OBJECT_NAME(tables.object_id) AS TableName, triggers.name AS TriggerName
INTO #DisabledTriggers
FROM sys.triggers
JOIN sys.tables ON triggers.parent_id = tables.object_id
WHERE is_disabled = 1

--Switch Database To BulkLogged
RAISERROR ('Switch Database To BulkLogged', 0, 1) WITH NOWAIT

EXEC ('ALTER DATABASE [' + @DestinationDatabase + '] SET RECOVERY BULK_LOGGED')

--Disable all nonclustered indexes
RAISERROR ('Disable all nonclustered indexes', 0, 1) WITH NOWAIT

DECLARE DisableIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + tables.name + ' DISABLE' AS DisableIndexes
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    JOIN sys.indexes ON indexes.object_id = tables.object_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'
    AND indexes.type_desc = 'NONCLUSTERED'

DECLARE @DisabledIndexCmd VARCHAR(MAX)

OPEN DisableIndexes

FETCH NEXT FROM DisableIndexes
INTO @DisabledIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisabledIndexCmd)

    FETCH NEXT FROM DisableIndexes
    INTO @DisabledIndexCmd
END

CLOSE DisableIndexes
DEALLOCATE DisableIndexes

--Disable Constraints and Triggers
RAISERROR ('Disable Constraints and Triggers', 0, 1) WITH NOWAIT

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + schemas.name + '.' + tables.name + ' DISABLE TRIGGER all' AS DisableTriggers,
    'ALTER TABLE ' + schemas.name + '.' + tables.name + ' NOCHECK CONSTRAINT all' AS DisableConstraints
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'

DECLARE @DisableConstraintsCmd VARCHAR(MAX)
DECLARE @DisableTriggersCmd VARCHAR(MAX)

OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints
INTO @DisableTriggersCmd, @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableTriggersCmd)
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints
    INTO @DisableTriggersCmd, @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--begin data transfer
RAISERROR ('begin data transfer', 0, 1) WITH NOWAIT

DECLARE InsertCmds CURSOR STATIC FORWARD_ONLY
FOR
    WITH ColumnList (TableID, SchemaName, ColumnList)
    AS
    (SELECT tables.object_id, SCHEMA_NAME(tables.schema_id),
                        STUFF((SELECT ',' + QUOTENAME(all_columns.name)
                        FROM sys.all_columns
                        WHERE tables.object_id = all_columns.object_id
                        AND system_type_ID NOT IN (34,35)
                        FOR XML PATH('')),1,1,'') AS txt
    FROM sys.tables)

    SELECT CASE WHEN MAX(CAST(is_identity AS INT)) = 1
        THEN 'SET IDENTITY_INSERT ['+ @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] ON; INSERT INTO [' + @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '](' + ColumnList + ') SELECT ' + ColumnList + ' FROM [' + @SourceDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + ']; SET IDENTITY_INSERT ['+ @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] OFF;'
        ELSE 'INSERT INTO [' + @DestinationDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + '] WITH (TABLOCK) (' + ColumnList + ') SELECT ' + ColumnList + ' FROM [' + @SourceDatabase + '].[' + SchemaName + '].[' + OBJECT_NAME(TableID) + ']'
        END
    FROM ColumnList
    JOIN sys.all_columns ON ColumnList.TableID = all_columns.object_id
    GROUP BY SchemaName,TableID,ColumnList

DECLARE @InsertCmd VARCHAR(MAX)

OPEN InsertCmds

FETCH NEXT FROM InsertCmds
INTO @InsertCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@InsertCmd)

    FETCH NEXT FROM InsertCmds
    INTO @InsertCmd
END

CLOSE InsertCmds
DEALLOCATE InsertCmds

--Rebuild all nonclustered indexes
RAISERROR ('Rebuild all nonclustered indexes', 0, 1) WITH NOWAIT

DECLARE RebuildIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + tables.name + ' REBUILD' AS DisableIndexes
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    JOIN sys.indexes ON indexes.object_id = tables.object_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'
    AND indexes.type_desc = 'NONCLUSTERED'

DECLARE @RebuildIndexCmd VARCHAR(MAX)

OPEN RebuildIndexes

FETCH NEXT FROM RebuildIndexes
INTO @RebuildIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@RebuildIndexCmd)

    FETCH NEXT FROM RebuildIndexes
    INTO @RebuildIndexCmd
END

CLOSE RebuildIndexes
DEALLOCATE RebuildIndexes

--Enable Constraints and Triggers
RAISERROR ('Enable Constraints and Triggers', 0, 1) WITH NOWAIT

DECLARE EnableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + schemas.name + '.' + tables.name + ' ENABLE TRIGGER all' AS DisableTriggers,
    'ALTER TABLE ' + schemas.name + '.' + tables.name + ' CHECK CONSTRAINT all' AS DisableConstraints
    FROM sys.tables
    JOIN sys.schemas ON tables.schema_id = schemas.schema_id
    WHERE tables.type = 'U'
    AND tables.name NOT LIKE 'sys%'
    AND tables.name NOT LIKE 'mspub%'
    AND tables.name NOT LIKE 'mspeer%'

DECLARE @EnableConstraintsCmd VARCHAR(MAX)
DECLARE @EnableTriggersCmd VARCHAR(MAX)

OPEN EnableConstraints

FETCH NEXT FROM EnableConstraints
INTO @EnableTriggersCmd, @EnableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@EnableTriggersCmd)
    EXEC(@EnableConstraintsCmd)

    FETCH NEXT FROM EnableConstraints
    INTO @EnableTriggersCmd, @EnableConstraintsCmd
END

CLOSE EnableConstraints
DEALLOCATE EnableConstraints

--Switch Database To Full recovery model
RAISERROR ('Switch Database To Full recovery model', 0, 1) WITH NOWAIT

EXEC ('ALTER DATABASE [' + @DestinationDatabase + '] SET RECOVERY FULL')

--Disable any objects that were previously disabled
RAISERROR ('Disable any objects that were previously disabled', 0, 1) WITH NOWAIT

DECLARE DisableIndexes CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER INDEX ' + IndexName + ' ON ' + TableName + ' DISABLE' AS DisableIndexes
    FROM #DisabledIndexes

OPEN DisableIndexes

FETCH NEXT FROM DisableIndexes
INTO @DisabledIndexCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisabledIndexCmd)

    FETCH NEXT FROM DisableIndexes
    INTO @DisabledIndexCmd
END

CLOSE DisableIndexes
DEALLOCATE DisableIndexes

--Disable Constraints and Triggers

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + TableName + ' NOCHECK CONSTRAINT ' + FKName AS DisableIndexes
    FROM #DisabledFKs
OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints
INTO @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints
    INTO @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--Disable Triggers

DECLARE DisableConstraints CURSOR STATIC FORWARD_ONLY
FOR
    SELECT 'ALTER TABLE ' + TableName + ' DISABLE TRIGGER ' + TriggerName AS DisableIndexes
    FROM #DisabledTriggers
OPEN DisableConstraints

FETCH NEXT FROM DisableConstraints
INTO @DisableConstraintsCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@DisableConstraintsCmd)

    FETCH NEXT FROM DisableConstraints
    INTO @DisableConstraintsCmd
END

CLOSE DisableConstraints
DEALLOCATE DisableConstraints

--Reseed Identity Columns
RAISERROR ('Reseeding Identity Columns', 0, 1) WITH NOWAIT

--drop table #ReseedCommands

DECLARE @Reseed VARCHAR(MAX) =
'SELECT ''DBCC CHECKIDENT ('''''' + schemas.name + ''.'' +  tables.name + '''''', RESEED, '' + CAST(IDENT_CURRENT(schemas.name + ''.'' + tables.name) AS VARCHAR) + '')''
FROM ' + @SourceDatabase + '.sys.all_columns
JOIN ' + @SourceDatabase + '.sys.tables ON all_columns.object_id = tables.object_id
JOIN ' + @SourceDatabase + '.sys.schemas ON tables.schema_id = schemas.schema_id
WHERE is_identity = 1'

DECLARE @ReseedCmd VARCHAR(8000)

CREATE TABLE #ReseedCommands
(command VARCHAR(8000))

SELECT @Reseed

INSERT INTO #ReseedCommands
EXEC (@Reseed)

DECLARE ReseedCursor CURSOR STATIC FORWARD_ONLY
FOR SELECT command FROM #ReseedCommands

OPEN ReseedCursor

FETCH NEXT FROM ReseedCursor
INTO @ReseedCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@ReseedCmd)

    FETCH NEXT FROM ReseedCursor
    INTO @ReseedCmd
END

CLOSE ReseedCursor
DEALLOCATE ReseedCursor


How to interpret the information spit out by innochecksum?

You should see what is inside ibdata1 from an artist's conception (brought to you by Percona)

Before you decide to shrink ibdata1, you need to know the following:

From the size of the ibdata1 file (1.7TB), I can safely assume you are using innodb_file_per_table=0. Thus, data and index pages for all InnoDB tables sits inside ibdata1. You need to full summary of what is inside ibdata1:

SET @pw = 3;
SET @bu = power(1024,@pw);
SELECT
    FORMAT(SUM(DL/@bu),2) DataStorage,
    FORMAT(SUM(DL),2) DataStorageBytes,
    FORMAT(SUM(IL/@by),2) IndexStorage,
    FORMAT(SUM(IL),2) IndexStorageBytes,
    FORMAT(SUM(DL+IL),2), InnoDBStorage
FROM
(
    SELECT data_length DL,index_length IL
    FROM information_schema.tables WHERE ENGINE='InnoDB'
    AND information
) A;

Take the InnoDBStorage and subtract it from the filesize of ibdata1

The difference, which I will call Wiggle Room, is left for all other logical structures inside ibdata1:

Data Dictionary
Rollback Segments
Undo Logs
Double Write buffer
Insert Buffer

It is possible to get table full errors if the Wiggle Room (difference of ibdata filesize - InnoDBStorage) cannot accommodate the two busiest logical structures that can change size quickly: 1) Rollback Segments, 2) Undo Logs. I have seen this happen as recently as 6 months ago (I wrote about this before : How to solve "The table ... is full" with "innodb_file_per_table"?)

Here are you options

OPTION #1

If you decide to shrink ibdata1, make sure you have enough diskspace for doing a mysqldump and gzip of all the data. If none of the InnoDB table have foreign keys constraints, you could convert all InnoDB tables to MyISAM, shutdown mysql, delete ibdata1, delete ib_logfile0, delete ib_logfile1, set innodb_data_file_path=ibdata1:10M:autoextend in /etc/my.cnf, start mysql, and convert all those tables back to InnoDB. I just recently learned that technique for Shlomi Noach.

OPTION #2

If you decide to enable innodb_file_per_table, ibdata1 should still incredibly small for life. However, accessing metadata about InnoDB tables will be a lot slower.

OPTION #3

If you want to leave things the way they are, please add ibdata2 within /etc/my.cnf:

STEP01 : service mysql stop

STEP02 : Get filesize of ibdata1

cd /var/lib/mysql
IBDATA1_FILESIZE=`ls -l ibdata1 | awk '{print $5}'`
echo ${IBDATA1_FILESIZE}

Let's suppose IBDATA1_FILESIZE is 2000000000000.

STEP03 : Add ibdata2 to InnoDB Data File Path in /etc/my.cnf

innodb_data_file_path=ibdata1:2000000000000;ibdata2:10M:autoextend

STEP04 : service mysql start

Give it a Try !!!

CAVEAT Here are some nice links for InnoDB Internals

http://dev.mysql.com/doc/internals/en/innodb.html
http://blog.jcole.us/innodb/
http://www.percona.com/files/percona-live/justin-innodb-internals.pdf

Google searches can give you much more InnoDB Internals Documentation


Postgres : Using WITH .... AS insert into to return non-inserted column

One possible solution is adding userid to the staging table you already have:

CREATE TABLE temp_import (
    row_id int,
    name varchar(32),
    email varchar(255),
    userid int not null default nextval('tmp_uid_seq'::regclass)
);

Use the same column definition above as you do in users - this way you use the same sequence, and even the nextval() will be correct after the import.

When doing the COPY, the userid will be filled.  This way you have the mapping and the staging all in the same place.  Now you have to copy the userid into the final table, too:

INSERT INTO users (userid, name)
SELECT userid, name
  FROM temp_import;

INSERT INTO mapping (userid, row_id)
SELECT userid, row_id
  FROM temp_import;

Notes:

in PostgreSQL, unlimited text and varchar have some performance advantage over the limited types.  Use the latter if you really have a limit.  With varchar(255) the case is usually 'We don't know how long it can be, but definitely not that long'.  Email addresses are usually considered as limited to 254 characters, not 255: https://www.rfc-editor.org/errata_search.php?eid=1690  Also, are you sure that nobody can have a name longer than 32 characters, including spaces, punctuation and so on?
it usually makes sense to use some consistent naming scheme in any software system, including databases.  I'm already confused a bit about the different styles used in userid and row_id.
if the import will happen repeatedly or in parallel, just use the same sequence all the time.  Sequences are transaction-safe and won't reuse any values until they wrap over (which can be mostly prevented by using bigint/bigserial).


mongod runs with command but not with config file

That error message "not master and slaveOk=false" is what you get when you try to query on a secondary server. If you're getting that message, that implies that:

The server has successfully started
The server has successfully joined the replica set
A different server is currently Primary

You should be able to check that by running rs.status() in the shell (assuming your user account has replicaset administration privileges).

If you want to run a query on this server to confirm that it is working (even though it is currently a secondary), you can use the rs.slaveOk call.


I assume you're looking for all foreign keys that are based on a given table (and it's primary key). If you were on SQL Server 2008+ you could use sp_fkeys.

EXEC sp_fkeys 'TableName'

As you're on 2005 you'll have to put together this data manually. Check this previous answer by Gishu, it should give you the script you're after.

https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server


You can use this query:

SELECT dataEntryIP FROM app_log
GROUP BY dataEntryIP
HAVING COUNT(*) > 1

Be careful above query doesn't use any index.

If your table will be grow, index the dataEntryIP column

ALTER TABLE `app_log`
ADD INDEX `dataEntryIP` (`dataEntryIP`);

Update 1

Oh sorry I did not understand your question.

I suggest before you insert the data into app_log remove www. prefix to unify domains (www.domain1.com is equal to domain1.com).

But for now I think it can help:

SELECT dataEntryIP from (
SELECT replace(domain, 'www.', '') as domain_without_www ,dataEntryIP from
app_log
GROUP BY domain_without_www,dataEntryIP ) t1
GROUP BY dataEntryIP
HAVING COUNT(*) > 1

But It is very slow, if your table is big.

if you can remove www. before insert to table, your query will be easier and faster.


Is there a way to optimize the process of uninstalling and installing SSMS?

All of the relatively recent versions of SSMS install side-by-side with each other, so I wouldn't bother removing them honestly. They'll upgrade the current version if it is on the same major release.
The newer versions are being released faster and sometimes can be a bit buggy, so having an older version to fall back on can also be helpful if you encounter a bug.
As far as making it faster, you could try using a package manager like winget (in preview, but by Microsoft) or chocolatey or any other generic automation software (Ansible, etc.)
There is also the command line option of SSMS itself, as Kin mentioned. And I will also echo the warning to not be doing this regularly (or at all) on the actual SQL Server machine.


Different results for logically same, but textually different queries

Do you have null values? If yes, the second form will be evaluated as null.

For example:

NULL || 'needle' = NULL

This way the second form can have less values than first one.

Try this:

coalesce(textfield1 ,'') || coalesce(textfield2,'')


Violation of PRIMARY KEY constraint in SSMS import/export wizard

Start by narrowing it down. Does it happen with only this one table? What if you play around with the scripting options (still only doing this one table); uncheck "everything". Etc. Until you have something working and then add bits and pieces back on until you find which one breaks it.
In the end, this is either a problem in the wizard (since duplicates cannot occur in the source data when you have a unique index), or we don't have the full story (hence my recommendation to narrow it down).
If it is a problem in the wizard, then consider reporting it to MS. They will likely want a repro for your problem.


To check if database or listener is running on Linux, you could check if Oracle processes are running, for example ps aux | grep pmon(or grep lsnr for listener).
Pmon is process monitor, process responsible for "housekeeping", like freeing buffer cache or some resources that are not in use anymore. It is always running if the instance is up ( but this process does not guarantee that you can connect, because the database could be started in mount mode, anyway it is a start of a troubleshooting ).

It seems that you didn't set ORACLE_SID, ORACLE_BASE, ORACLE_HOME and PATH variables. On link that you provided, environment variables are set at the runtime. You could permanently set them in your .bashrc or bash_profile file.

Add these lines to your .bashrc file(it is in your users home directory, for example /home/oracle/.bashrc).

ORACLE_SID=name_of_your_database

ORACLE_BASE=/oracle/base/

ORACLE_HOME=/oracle/base/dbhome_1

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH

Reload new bash profile with command - source .bashrc and try again and read Oracle installation documentation for more detalis.

However, since Ubuntu is not supported OS for Oracle, there is no way to know for sure what the problem really is or was. If installation is for learning purpose, easier for you will be to install some supported OS in a VirtualBox for example, or to use Oracle prebuild VMs.


Arbitrary queries on n:m relationship, including "all" and "any"

Is this query any efficient?

No, it is extremely inefficient for multiple reasons.

0. Test schema

Basing my answer on this simplified schema:

CREATE TABLE tag (
  tag_id serial PRIMARY KEY
, tag    text NOT NULL
);

CREATE TABLE task (
  task_id  serial PRIMARY KEY
, task     text NOT NULL
);

CREATE TABLE task_tag (
  task_id    int
, tag_id     int
, PRIMARY KEY (tag_id, task_id)  -- columns in this order
, FOREIGN KEY (task_id) REFERENCES task
, FOREIGN KEY (tag_id)  REFERENCES tag;
);

1. The query is very inefficient

Your original query, adapted to test schema:

SELECT DISTINCT ON (task.task_id) task.*  -- DISTINCT is dead freight
FROM   task, LATERAL (
   SELECT array_agg(tag.tag) AS tags  -- array_agg more expensive than array constructor
   FROM   task_tag
   JOIN   tag ON task_tag.tag_id = tag.tag_id
   WHERE  task_tag.task_id = task.task_id
   GROUP  BY task_tag.task_id  -- redundant noise
   ) tt
WHERE  tt.tags @> array['tag1'::text, 'tag3'::text];  -- or array literal.

What's wrong? For starters:

No reason to add DISTINCT ON. The lateral join only joins to a single row after the aggregation.
An array constructor is cheaper for this simple array aggregation.

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

No need to add GROUP BY, after the WHERE clause already filters one task_id.
Depending on how you call the query, it may be more convenient to pass a single array literal instead of an array constructor.

Equivalent query 1:

SELECT ts.*
FROM   task ts
JOIN   LATERAL (
   SELECT ARRAY (
      SELECT tg.tag
      FROM   task_tag tt
      JOIN   tag      tg USING (tag_id)
      WHERE  tt.task_id = ts.task_id
      ) AS tags
   ) tt ON tt.tags @> '{tag1, tag3}'::text[];

But that's still a waste. The LATERAL does more harm than good while all rows have to be aggregated anyway.

Equivalent query 2:

SELECT ts.*
FROM   task ts
JOIN  (
   SELECT task_id
   FROM   task_tag
   GROUP  BY 1
   HAVING array_agg(tag_id) @> '{1, 3}'::int[];
   ) AS tags USING (task_id);

As you can see, I am not building array of tags, just tag_id. Much more efficient. Resolve your input tags to IDs before you pass them to the query.

Much faster than before but still extremely inefficient for tables of non-trivial size.

2. The whole approach is very inefficient

The above approach cannot use an index. The dynamically generated array does work with indexes. The predicate comes after the aggregation.

2.1 MATERIALIZED VIEW for read-only (or mostly) tables

To make it work with an index you would have to add a MATERIALIZED VIEW with readily aggregated tag arrays (or IDs, preferably) per task_id. And a GIN index on the array column. Only good for read-only (or mostly) tables, since the MV is just a snapshot, quickly outdated with writes to the underlying tables.

Related:

Can PostgreSQL index array columns?

The MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW task_tags_mv AS
SELECT task_id, array_agg(tag_id) AS tag_ids
FROM   task_tag
GROUP  BY 1;

Of course, we work with tag_id again, not raw tags. And for integer arrays there are much faster, specialized operator classes and indexes available from the additional module intarray. Related:

GIN index on smallint[] column not used or error “operator is not unique”

So we use this specialized index:

CREATE INDEX task_tags_mv_arr_idx ON task_tags_mv USING GIN(tag_ids gin__int_ops);

And this query:

SELECT ts.*
FROM   task ts
JOIN   task_tags_mv mv USING (task_id)
WHERE  mv.tag_ids @> '{1, 3}'::int[];  -- uses intarray operator

This is fast, now. And the same setup works for matching ANY tag, using the overlap operator && instead of the contains operator:

...
WHERE  mv.tag_ids && '{1, 3}'::int[];  -- uses intarray operator

2.2 Generic queries

Else you need completely different query techniques that can work with indexes. Yper* pointed to a related question on SO in his comment:

How to filter SQL results in a has-many-through relation

You specific requirement is to make is simple to provide a list of tags to a generic query.

A recursive CTE is one way without dynamic SQL. (There are many others.) Nested in an SQL function with VARIADIC parameter for convenience:

CREATE OR REPLACE FUNCTION f_tasks_with_tags(VARIADIC _tags text[])
  RETURNS SETOF task AS
$func$
   WITH RECURSIVE cte AS (
      SELECT task_id, 1 AS idx
      FROM   task_tag
      WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[1])

      UNION
      SELECT task_id, c.idx + 1
      FROM   cte c
      JOIN   task_tag tt USING (task_id)
      WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[c.idx + 1])
      )
   SELECT t.*
   FROM   cte  c
   JOIN   task t USING (task_id)
   WHERE  c.idx = array_length(_tags, 1)
$func$  LANGUAGE sql STABLE;

Call:

SELECT * FROM f_tasks_with_tags('tag1', 'tag3'); -- add any number of tags

About VARIADIC (follow links for more):

Passing multiple values in single parameter

Depending on value frequencies, it helps performance (a lot) to pass sparse tags first. It is cheaper to eliminate non-qualifying tasks early.

Another potentially even faster approach: dynamic SQL. Build and execute a query like:

SELECT t.*
FROM  (
   SELECT task_id
   FROM   task_tag t1
   JOIN   task_tag t2 USING (task_id)
-- JOIN   task_tag t3 USING (task_id)
-- more ...
   WHERE  t1.tag_id  = 1
   AND    t2.tag_id  = 3
-- AND    t3.tag_id  = 789
-- more ...
   ) tt
JOIN  task t USING (task_id);

In this query, Postgres can optimize the sequence of joins automatically to evaluate sparse tags first - up to a maximum of join_collapse_limit tables. See:

Postgres JOIN conditions vs WHERE conditions

There are many code examples for building queries dynamically in a plpgsql function on this site. Try a search.

SQL Fiddle (also has intarray module).

dbfiddle here


Retrieve Indices of Associative Array as Collection

Is there some operator or function that can give me the indices of the array

No, you have to loop through the associative array:

declare
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       type keys_t is table of PLS_INTEGER;
       a_arr a_arr_t;
       keys_ keys_t := keys_t();
       l_index integer;
begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;

      l_index := a_arr.first;
      while (l_index is not null)
      loop
          keys_.extend();
          keys_(keys_.count):=l_index;
          dbms_output.put_line(keys_(keys_.count));
          l_index := a_arr.next(l_index);
      end loop;
end;
/

/*
1
10
100
*/


I can show you an example for SQL Server, but it should you help for your RDBMS aswell.

I hope I understood the question. Suppose you want to get TOP users based on the number of same skills as the current user.

Some sample data:

CREATE TABLE skills (
  id INT NOT NULL,
  skill INT NOT NULL);

INSERT INTO skills (id,skill)
VALUES
  (1,1),(1,2),(1,3),
  (2,1),(2,6),
  (3,1),(3,3),(3,4),(3,5),
  (4,1),(4,5),
  (5,1),(5,2),(5,3),(5,5),
  (7,2),(7,4),(7,5),(7,6),
  (8,8);

The query:

SELECT
  cus.id AS current_user_id
  ,ous.id AS other_user_id
  ,COUNT(*) AS same_skill_count
FROM
  skills AS ous -- other users' skills
  JOIN skills AS cus -- current user's skills
    ON cus.skill = ous.skill -- we are looking for users that have some of the current user's skills
      AND cus.id <> ous.id -- exclude the current user from the list
GROUP BY
  cus.id
  ,ous.id
ORDER BY
  cus.id
  ,same_skill_count DESC

SQLFiddle demo

Hope that helps.


If you use a normal (non-persisted) computed column - then yes, you might save a bit on disk space - BUT always constantly re-computing those values every time you access the row will likely cost you more in terms of processing time than you save by loading less data.

There's really no easy way to predict this - how much performance will you save by loading less data vs. how much performance is needed for constantly re-computing those column values. You'll need to run some performance tests to find out.

If you use persisted computed columns, then those values are stored just the same as with a normal column - so you won't have the negative performance impact from constantly re-computing the values, but you also won't be saving anything on disk space.

PS: I'm not entirely sure that SQL Server 2000 even had the PERSISTED keyword for computed columns (that SQL Server version is just too old for me to really remember...). I believe however that PERSISTED was a new feature in SQL Server 2005, so it wouldn't apply to you.


Should snapshot agent continue to run in transactional replication?

I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?) Should this be doing a refresh this often if no new articles are being added or changed?

NO. Snapshot should not be run frequently, unless you are adding/dropping articles.

Also, Transactional replication uses Concurrent Snapshot processing; meaning it does not hold share locks in place during the entire snapshot generation, thereby allowing users to continue working uninterrupted while replication creates initial snapshot files.

The sync_method parameter governs which mode is used - native, character, concurrent or concurrent_c.


MongoDB: Aggregation workaround by helper collection

Once you go beyond the limitations you mention (until $out is supported at least), then your best option is Map Reduce.  If you are going to run multiple Map Reduce jobs, I would recommend version 2.4 at least (uses V8 engine, muti-threaded).  For an example of how to do a unique count with Map Reduce, take a look here:

http://cookbook.mongodb.org/patterns/unique_items_map_reduce/


Get data from both tables what join should I use

Simply, you can use a CTE or SubQuery then ORDER BY [T1.T2] DESC as

WITH CTE AS
(
  SELECT Col1, Col2, 'T1' [T1.T2]
  FROM T1
  UNION
  SELECT Col1, Col2, 'T2'
  FROM T2
)
SELECT *
FROM CTE
ORDER BY [T1.T2] DESC;

Live Demo


Multiple database directories with postgresql

I got this working without specifying database directory, but you do need to specify the configuration file, so that the pg_ctl binary will know which data directory to use. So for example:

pg_ctl -D /etc/postgresql/9.4.2/main start


MySQL: Show full processlist has empty Host name. Why?

The system user is the user defined to execute MySQL Replication.
There are two DB Connections dedicated to performing MySQL Replication

IO Thread : This thread is responsible for downloading the latest Binary Log Entries from the Master and Storing those Entries in the Slave's Relay Logs.
SQL Thread : This thread is responsible for processing the relay logs like a FIFO queue. It basically reads the next available Relay Log Entry and processes the SQL of that Entry.

The system user is referred to as a non-client thread in the MySQL Documentation about SHOW PROCESSLIST;
As far seeing localhost:#####, that number after the colon is a really a port number within mysqld assigned to localhost.
UPDATE 2012-04-27 18:00 EDT
Questions from your comment

Can I rename the system user? Or any other properties of the system user? Also, is the system user is dedicated only for replication? Or any other MySQL processes are spawned by System User? I understand that system user cannot be accessed by a client, its an internal process spawned by MySQL.

Answers to Questions from your comment
No, you cannot rename the system user. It is dedicated to handle MySQL Replication only. The only way to manipulate properties of of the system user would be throught the GRANT command issued to create the replication user.
For example, when you setup a replication user, you issue a command like this:
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to 'repl'@'%';

When START SLAVE is issued on a Slave, the Master authenticates the DB Thread coming from the Slave and assign one thread on the Master. The thread on the Master will ship binary log entries to the I/O Thread on the Slave. The I/O Thread on the Slave is assigned to system user for handling communcation between Master and Slave. The SQL Thread on the Slave is also assigned to system user for handling intracommunication of local relay log entries to be processed FIFO (Frist In, First Out) by mysqld running on the Slave. No direct access is permitted via the MySQL Client on the Slave except for

STOP SLAVE; (Kills both I/O Thread and SQL Thread)
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
START SLAVE; (Creates both I/O Thread and SQL Thread)
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;

Of course, you could issue KILL ####; where #### is the process ID of either the I/O Thread or SQL Thread. You would be totally respsonsible for reestablishing replication at the risk of losing the correct log file and position if the KILL command misses any communication because of an unnatural stoppage of a replicaton thread.


You can add a WHERE clause to the UPDATE part of an INSERT ... ON CONFLICT

insert into the_table (sid, somecolumn)
values (...)
on conflict (sid) do update
  set somecolumn = excluded.somecolumn,
      changed = true
where the_table.somecolumn <> excluded.somecolumn;

If you need to deal with NULL values in somecolumn use

where the_table.somecolumn is distinct from excluded.somecolumn


Can you see other users' session variables in MySQL?

As of MySQL 5.7 the performance_schema can provide this information.

  Is there a way to see the SESSION variables of other active, connected users, from a superuser connection ?

You can get these from performance_schema.variables_by_thread.

You will need to know the Thread ID. The ID from SHOW PROCESSLIST is Process ID. Knowing the Process ID you can then look up the Thread ID from performance_schema.threads.

  is there a query or view that would show me which active sessions have variables set differently from the global variables?

You sure can.

SELECT VARIABLE_NAME, gv.VARIABLE_VALUE, tv.VARIABLE_VALUE
FROM performance_schema.global_variables gv
  JOIN performance_schema.variables_by_thread tv USING (VARIABLE_NAME)
WHERE gv.VARIABLE_VALUE <> tv.VARIABLE_VALUE
  AND THREAD_ID = ?;


QuasarDB cluster stuck in IOWAIT on AWS EC2 instances

(Disclaimer: I work at QuasarDB)
Because you are using the newer gp3 EBS volume types, and you are reporting a system-wide instability of the system (rather than just the QuasarDB process), I believe this is related to the EBS volume types, rather than something related to QuasarDB.
We have observed stability issues with gp3 volumes under high pressure ourselves, with a system getting stuck. To the best of our understanding, this is related to an EBS host-level driver. GP3 instability have been report by others as well.
What I would recommend is to use the older GP2 EBS volume types, and see if that solves the issue. If you would still like to achieve high-throughput, you can combine multiple GP2 EBS volumes using mdraid as recommended by AWS themselves.


SQL Server Configuration Manager 2012 - unable to set IP All = No

You cannot set IPAll to "no" on a failover cluster instance (as mentioned before), see:

How to configure SQL server to listen on different ports on different IP addresses?

More than likely, though, you are wondering how to secure your failover cluster instance of SQL Server to a certain IP address, or to verify that it isn't indeed listening on all IP addresses registered.

The failover cluster instance will be listening on the IP address(es) registered for the virtual network name in the cluster resource group with the Windows failover cluster.

But you want to verify which IP addresses SQL Server is listening on?

select
    ip_address,
    port,
    is_ipv4,
    type_desc,
    state_desc
from sys.dm_tcp_listener_states;

That will give you the real-time snapshot of what IP addresses SQL Server is listening on.  That'll be your verification.


How can I improve a slow comparison query that have over partition and group by

Since [ExternalTable] is linked to a view on another database server, it probably makes sense to "materialize" the rows in [ExternalTable] into a local temp table, then run the query against that.
So, something like this:
CREATE TABLE #t_combined
(
    SourceTable char(1) NOT NULL
    , foo int NOT NULL
    , bar int NOT NULL
    , [data] int NOT NULL
    , INDEX ix CLUSTERED (foo, bar, [data], SourceTable)
);

INSERT INTO #t_combined WITH (TABLOCKX) (SourceTable, foo, bar, [data])
SELECT '2', foo, bar, [data]
FROM [ExternalTable] et;

INSERT INTO #t_combined WITH (TABLOCKX) (SourceTable, foo, bar, [data])
SELECT '1', foo, bar, [data]
FROM [Table] et;

;WITH CombinedTables AS
(
    SELECT SourceTable
        , foo
        , bar
        , data
        , ROW_NUMBER() OVER (PARTITION BY foo, bar, data ORDER BY foo, bar, data) Row_Nbr
    FROM #t_combined
    GROUP BY foo
        , bar
        , data
        , SourceTable
)
SELECT DISTINCT *
FROM CombinedTables
WHERE CombinedTables.Row_Nbr < 2
    AND CombinedTables.SourceTable = '2';


Always being logged in as SA and not Windows account

DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed role. If DOMAIN\Glen is in the sysadmin role, the default schema is always dbo and you have to explicitly use the schema name.


Your index is probably not used because it does not match the most selective conditions. If too many rows are returned from the index scan, a sequential scan is more efficient.
Try this index:
CREATE INDEX ON account_transactions (transaction_type, subsidiary_type, status, transacted_at);

If the condition of any of the leading three columns is not selective, omitting the column from the index is better. The important part is that transacted_at is last, because it is not used in a condition with = and is used in ORDER BY.
You can add WHERE conditions to the index like you did in your question.


pgpool2 setup on master-slave replication using postgreql 9.1

Of PgBouncer and PgPool-II, only PgPool-II supports load balancing.

PgPool-II's load balancing supports only read-only queries that meet certain requirements; see the pgpool docs. Additionally, if you start load-balancing queries you are no longer guaranteed to get a consistent view of the database because the replica might be lagging behind the master.

Personally, I'd look into adding a caching layer (memcached / Redis) before doing fiddly things like read-query load balancing.


Can SSMS Object Explorer Details configurations be exported?

I don't think so. These settings are stored along with a bunch of other UI configuration in a binary file in your user profile directory.


No, not for any significant and practical amount of code anyway.  You can try to hew to standards (e.g., use COALESCE rather than ISNULL) but there are too many differences, big and small.  Off the top of my head:

SQL Server supports double quotes and square brackets for identifiers; MySQL uses backticks.
SQL Server supports TOP, most other databases use LIMIT.
PostgreSQL only relatively recently implemented stored procedures, though in practice you could use functions instead.
MySQL is case-sensitive for table names (but not field names) when running on UNIX, but not when running on Windows.  SQL Server is case-insensitive either way (except when it is).
CTEs and windowed functions are not available on all systems, and are not always implemented the same.
SQL Server does not require a command delimiter (except when it does), but MySQL and Oracle do.  MySQL also requires using an alternate delimiters when defining stored procedures; MS SQL does not support any such thing.
Security is almost always different for different vendors.
Error handling is always different.
All of the above can change, and may have since I last worked with those systems.

Many people have written software with the goal of letting one write RDBMS-agnostic queries.  Most of these experiments failed, and a few broke out of the lab, lurching across the landscape spreading destruction in their wake.  But even the best won't have the performance of code written with the target system in mind.


Script to backup TDE certificate and Key throws error Cannot write into file

When SQL Server saves certs to disk, it alters the ACLs on the exported files - you're probably running into a permissions issue where the SQL Server Agent account cannot overwrite the file once it gets generated the first time.
See my blog post at sqlserverscience.com for details about file system security.
The key part from that post is:

After those commands have been used to export each key or certificate, SQL Server modifies the Access Control List (ACL) on each file1. This ensure no-one other than the following Windows principals have access to the file:

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

You also need to ensure the SQL Server service accounts have share-level permissions that enable writes.


How to design a database that tracks sports player's positions

Sounds like your design isn't quite right. You probably need it like this (I imagine this is for football?):

Team Table - TeamID, TeamName...

Player Table - TeamID, PlayerName, PlayerPreferredPosition...

Position Table - PositionID, PositionName, PositionType (backs/mid/forward/Goalkeep etc)

Game Table - GameID, GameLocation, HomeTeam (FK Team), AwayTeam (FK Team), MatchStartTime...

GamePlayPosition - GameID (FK Game), PlayerID (FK Player), PositionID (FK Position), TimeStart, TimeEnd

That way you can figure out what players are playing at what position in what match at any time.

EDIT: If you want to use a grid and indicate exactly the time that player is spending there, I would suggest the following change, but what is above is still correct.

To the GamePlayPosition table add a surrogate key - GamePlayPositionID, use as your primary key. Then add another table:

ExactPlayPosition - GamePlayPositionID (FK), XPosition (small int), YPosition (small int), GameTime (datetime? Possibly an integer representing the seconds elapsed in the game time, easier to compare relative position game to game without doing datetime calculations if done with gametime).

The reason you have another table like this is because it will have a HELL of a lot of data, you want it as small as possible in terms of row width. You will be adding probably 3600 (# seconds in match) multiplied by 10 (# players in match) = 36k records per match, minimum if you don't include breaks. If tracking both teams of players, say 75k records per match, say over 50 matches... you get the idea.


If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague).

I typically advocate for separate databases, though, for a variety of reasons:

You seem to be separating on things that are functionally different - there is no real advantage to containing data for logically separate apps in the same database, whether you use schema separation or not - unless they truly do share the same copy of the data.
Easier to split out a database onto a separate server if it scales faster or outgrows the server it's on. Even with separate schemas it will be tedious to move just one app's data out of that database; if they start in their own database, it's as simple as backup, restore on the other server, and change the connection string for that app. If you have one database and you outgrow the server, you have to move the whole thing. And then again next time.
Easier to manage recovery separately - you may want use full recovery and frequent log backups for your source control, but don't need such rigor for the database that holds employees' favorite Power Ranger characters. You can also avoid having to restore all applications to the same point in time if you have to revert on e of the databases for some reason.
It sounds like more work to manage this, but really, you can make it so that managing 2 databases is not really any different from managing 2,000 databases.

In a previous life I managed a system with many databases; they were one per client with (largely) identical tables/views/procedures. I've answered multiple questions here based on my experience and knowledge through that architecture (including some drawbacks to be aware of in the first answer):

Is that a good idea to use one database for 50.000+ shops?
Handling growing number of Tenants in Multi-tenant Database Architecture
Automation of backups of Large Number of Database
At what point does one database per client become unfeasible?

I have yet to hear anyone argue successfully for storing different data (different apps, different customers, etc.) in the same database. I think anyone who chooses to do this is either confident that their data will never outgrow that server, or is focusing on the near term goal of simplicity & minimal effort (which can be quite short-sighted, and bite you in the long run).


How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

You can use AWS Database Migration Services (DMS) to replicate full and/or change data to another system (such as S3 or a PostgreSQL database running on-premises or on EC2)

make sure use DMS v3.3 if you use PG 10+
create a new pg parameter group. set:
rds.logical_replication=1
save. modify the RDS instance to use that parameter group, apply changes
reboot the RDS instance

You'll need a VPN connection if your PG instance is on-prem (not on AWS network)

DMS supports DDL changes too

You can use AWS Schema Conversion Tool to migrate your schema from source to target


ok, so called a buddy.  He mentioned another post that mentioned to grant the appropriate AD user(s) read and execute privilidges on the Reporting Services directory.

i did that and it worked.  Hopefully this helps someone else as well.


B-tree node split strategy in SQL Server for monotonically increasing value

If it is adding a row at the end of the index it will just allocate a new page for the row rather than split the current end page. Experimental evidence for this is below (uses the %%physloc%% function which requires SQL Server 2008). See also the discussion here.

CREATE TABLE T
(
id int identity(1,1) PRIMARY KEY,
filler char(1000)
)
GO

INSERT INTO T
DEFAULT VALUES
GO 7

GO
SELECT sys.fn_PhysLocFormatter(%%physloc%%)
FROM T

GO

INSERT INTO T
DEFAULT VALUES

GO

SELECT sys.fn_PhysLocFormatter(%%physloc%%)
FROM T
GO

DROP TABLE T

Returns (Your results will vary)

(1:173:0) /*File:Page:Slot*/
(1:173:1)
(1:173:2)
(1:173:3)
(1:173:4)
(1:173:5)
(1:173:6)
(1:110:0) /*Final insert is on a new page*/

This does only appear to apply to leaf nodes though. This can be seen by running the below and adjusting the TOP value. For me 622/623 was the cut off point between requiring one and two first level pages (might vary if you have snapshot isolation enabled?). It does split the page in a balanced manner leading to wasted space at this level.

USE tempdb;

CREATE TABLE T2
(
id int identity(1,1) PRIMARY KEY CLUSTERED,
filler char(8000)
)

INSERT INTO T2(filler)
SELECT TOP 622 'A'
FROM master..spt_values v1,  master..spt_values v2

DECLARE @index_info  TABLE
(PageFID  VARCHAR(10),
  PagePID VARCHAR(10),
  IAMFID   tinyint,
  IAMPID  int,
  ObjectID  int,
  IndexID  tinyint,
  PartitionNumber tinyint,
  PartitionID bigint,
  iam_chain_type  varchar(30),
  PageType  tinyint,
  IndexLevel  tinyint,
  NextPageFID  tinyint,
  NextPagePID  int,
  PrevPageFID  tinyint,
  PrevPagePID int,
  Primary Key (PageFID, PagePID));

INSERT INTO @index_info
    EXEC ('DBCC IND ( tempdb, T2, -1)'  );

DECLARE @DynSQL nvarchar(max) = 'DBCC TRACEON (3604);'
SELECT @DynSQL = @DynSQL + '
DBCC PAGE(tempdb, ' + PageFID + ', ' + PagePID + ', 3); '
FROM @index_info
WHERE IndexLevel = 1

SET @DynSQL = @DynSQL + '
DBCC TRACEOFF(3604); '

EXEC(@DynSQL)

DROP TABLE T2


Determining which database instance makes biggest IO

I would suggest you to take a look at atop. When you run it interactively you can select 'i' then press 1 , after that you can select 'd'.

Atop will colour the resources thrashed and 'd' will show only processes hitting disks.


Trying to understand MySQL Replication with SSL

Create the server and client cert/key files on the master server and then copy only the client cert/key files to each slave server and configure SSL replication. So basically you have the same client cert and key on all slave servers. If a slave gets compromised change the replication user's credentials.


SQL Server 2012 import/export wizard not showing at all

The import/export wizard is not showing because it is not available for Azure SQL. Even if you downloaded the latest version of SSMS, you shouldn't see it.

You are in a different environment that how on-premise SQL Server databases work. In that Azure SQL does not support using cross database queries. So doing an insert into or select insert are not going to work.

Depending on how big the table is, your easiest thing to do is using the script object wizard and just include the data. Then just run that under the destination database.

If you need to do this on a routine basis then look at the data sync services Azure offers.


This would be more efficient:
With jsonb and jsonb_array_elements_text() in pg 9.4+
EXPLAIN
SELECT p.id AS p_id, p.data
     , c.id AS c_id, c.data
FROM   test p
LEFT   JOIN LATERAL jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE
LEFT   JOIN test c ON c.id = pc.child::int;
db<>fiddle here
About jsonb_array_elements_text():

How to turn JSON array into Postgres array?

Use the -> operator instead of ->> in the reference to children. The way you have it, you'd first cast json / jsonb  to text and then back to json.
The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children.
To exclude those, change to a [INNER] JOIN LATERAL or CROSS JOIN - or the shorthand syntax with a comma:
, json_array_elements(p.data->'children') pc(child)

Avoiding duplicate column names in result.
With json and json_array_elements() in pg 9.3
SELECT p.id AS p_id, p.data AS p_data
     , c.id AS c_id, c.data AS c_data
FROM   test p
LEFT   JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE
LEFT   JOIN test c ON c.id = pc.child::text::int;

Old sqlfiddle
Aside: A normalized DB design with basic data types would be way more efficient for this.


Three ways. Either IGNORE duplicate errors
(but don't use that, see note in the end):
INSERT IGNORE
  ... ;                   -- without ON DUPLICATE KEY

or try to do a redundant update when there is a duplicate:
INSERT
  ...
ON DUPLICATE KEY UPDATE
  id = id ;

or check for duplicates before inserting:
INSERT INTO requests
  (id, ctg, msg, nick, filled, dated, filldate)
SELECT
  NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW()
FROM
  dual
WHERE NOT EXISTS
      ( SELECT *  FROM requests  WHERE (nick, msg) = ('Hermione', 'Help!') )
  AND NOT EXISTS
      ( SELECT *  FROM requests  WHERE (ctg, msg) = ('urgent', 'Help!') ) ;

A difference between the 3rd way and the first two is that when there are duplicates, the id will not be incremented. With INSERT IGNORE and INSERT ... ON DUPLICATE KEY, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id.
I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.
*Note: INSERT IGNORE will ignore all insert related errors, even not null constraint violations, so it is best to avoid it.


Where can I find information on shredding xml with .nodes?

Those methods are part of the xml data type in Sql Server, starting from 2005, since the xml data type was implemented for Sql Server 2005.

The local-name method is part of XQuery implementation of Sql Server. Its far from being a complete implementation of the XQuery specification, but it can be helpful.


How can I speed up an sql query that round trips between two tables

It's more or less like this...

SELECT m.*
     , COUNT(p.sid) frequency
  FROM music m
  JOIN plays p
    ON p.sid = m.sid
 WHERE p.time > NOW() - INTERVAL 3 DAY
   AND p.sid != ''
   AND m.perms != 'c'
 GROUP
    BY m.sid
 ORDER
    BY frequency DESC
 LIMIT 50


Mariadb processes stuck after aborted dump restore. What to do?

you can kill the mysqld.exe process

taskkill /im mysqld.exe /f

from the elevated command prompt. Then start service again.
But before you do this, it would be nice of you to take a process dump (with procdump for example, e.g procdump -mp mysqld.exe) , and file a bug in mariadb bug tracker https://jira.mariadb.org , attaching the dump.


SSRS --> .xlsx file --> SQL ETL -- "External table not in expected format" error, but only when files have not been opened

Here are the possible reasons and solutions I can think of:

The Excel file instance may be getting opened in protected mode. Opening, saving, and closing them is solving this issue temporarily. To fix it permanently, you need to add the Excel file location as a trusted one in the Trust Center.

To do this, you need to go to excel>>file>>options>>Trust center>>trust center settings>>Trusted locations. and add the location of your Excel file there.
Another thing that you can try is changing the file block settings in the same options window for Excel 2007, and later file types according to your needs.
The last reason that I can think of is Excel is unable to identify the type of value being inserted in the columns. This problem is resolved by entering some default sample values in the first row for each column. This workaround helps Excel in deciding what kind of value is getting entered and most of the time fixes the format issue.

For example:

column1_has_to_get_integer_value then insert any integer value in first row.
column2_has_to_get_string_value then insert any string value in first row.


In phpMyAdmin, how can I execute mysqldump query?

The command you posted does not execute from within PHPMYADMIN

That is supposed to be executed in the OS Shell

There are many web pages that explain running a backup of MySQL with PHP

For example, if you go to How to backup your Mysql database with phpMyAdmin, you will learn how to do the backup starting from the Export tab as well as doing the mysqldump from the OS.