How does a users DEFAULT_SCHEMA get set to something other than "dbo"

One way this happens is by having a Login that has the CONTROL SERVER server-level permission (hence does not need an actual USER in order to access a database) but not in the sysadmin fixed server role (as then there is an implied default schema of dbo) create an object without explicitly specifying the schema to put it in.

According to the MSDN page for CREATE SCHEMA:

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

  A login has CONTROL SERVER privileges.
  A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

  When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

Hence, I am speaking of the first case noted above, and @Max's answer is about that second case.

The following is an example for the first case (please note that the following example uses tempdb since every instance has that, but I did test on a non-tempdb database to be sure). To see an example of the second case, please see @Max's answer (there is no need to duplicate that here).

USE [master];

CREATE LOGIN [DefaultSchemaTest] WITH PASSWORD = 'DefaultSchemaTest';
GRANT CONTROL SERVER TO [DefaultSchemaTest];
GO

EXECUTE AS LOGIN = 'DefaultSchemaTest';

USE [tempdb];
SELECT * FROM sys.database_principals WHERE [type] <> 'R';
SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

CREATE TABLE MySchemaTest (Col1 INT);

SELECT * FROM sys.database_principals WHERE [type] <> 'R';
SELECT * FROM sys.schemas WHERE [name] NOT LIKE N'db[_]%';

USE [master];
REVERT;


If you aren't sure that all of your dates in the second table end are on the first day, you can be sure the comparison on year/month with EXTRACT( YEAR_MONTH FROM datecol) like this,

SELECT
  t2.Day,
  t1.Month,
  t1.Value1,
  t2.Value2
FROM Table2 t2
LEFT JOIN Table1 t1
  ON EXTRACT(YEAR_MONTH FROM t2.Day) = EXTRACT(YEAR_MONTH FROM t1.Month)

It is likely faster regardless, unless you have an index on t1.month


How to debug trivial MariaDB query with simple execution plan which often runs really slowly?

Only answering the question "how to debug it further".

(Sorry about this one, but it must always be said) Make sure you're looking at the right record in the slow log. The part before a query relates to the query, the part after it does not:

# Time: 161128 19:20:53
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 25  Schema: test  QC_hit: No
# Query_time: 1.925981  Lock_time: 0.924844  Rows_sent: 0  Rows_examined: 0
SET timestamp=1480353653;
select UNIX_TIMESTAMP(my_timestamp_field) AS my_name FROM my_table WHERE my_column=9;
# Time: 161128 19:21:11
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 25  Schema: test  QC_hit: No
# Query_time: 10.000428  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
SET timestamp=1480353671;

The first 6 lines in the block above are the relevant part. Even if the query actually takes 1 second and not 10, it is worth investigating, but it would be a quite different kind of mystery.

Double-check the records in the slow log, specifically Rows_sent and Rows_examined. Since my_column is not unique, it's possible that with some parameter values, or with some unfortunate statistics, the query scans many rows, not just one as in your query plan example. This should rule out a bad execution plan.

# Time: 161128 19:42:19
# User@Host: elenst[elenst] @ localhost [127.0.0.1]
# Thread_id: 26  Schema: test  QC_hit: No
# Query_time: 1.622502  Lock_time: 0.000373  Rows_sent: 32768  Rows_examined: 32768
SET timestamp=1480354939;
select UNIX_TIMESTAMP(my_timestamp_field) AS my_name FROM my_table WHERE my_column=3;

Additional check in regard to a bad plan. Try to run a similar query, but not using index, either with a non-indexed column in WHERE, or with IGNORE INDEX hint. The goal is to get full scan and to see how long it takes. With 45K records, even full scan shouldn't take 10+ seconds; but if it does, a wrong plan is still on the table. If it's not anywhere near 10+ seconds, then more likely something else is blocking the query.
Start a monitor which runs SHOW PROCESSLIST frequently, e.g. every 5 seconds, so that you certainly catch a 10-second query. Better if the monitor runs in a persistent connection, but if it's too difficult, just run the MySQL client with -e 'SHOW PROCESSLIST' in a loop. Don't forget to show timestamps, too. When you catch the query, the State field will show in which stage the query spends the time, and possibly the process list will also give a hint with which other queries your might be conflicting.
If you have permissions, add SHOW ENGINE INNODB STATUS\G to the monitor. It might show some details if the query is spending time inside InnoDB.
Add SHOW GLOBAL STATUS LIKE 'Open%' to the monitor, maybe less frequently. It should show whether the tables get kicked out of the table cache too often, it can cause delays in a busy environment.


How to select multiple values into an array and loop through? (postgres 9.3)

A much better way, yet: just update. No loop needed.

UPDATE loop_test
SET    id_copy = id
     , other_id_copy = other_id;
WHERE  id IS NOT NULL;

The WHERE condition is only useful if id can be null and you want a perfect equivalent of what you had.

Loop

If you are just exploring loops - you can assign multiple variables:

CREATE OR REPLACE FUNCTION better_loop_test()
  RETURNS void AS
$func$
DECLARE
   _id int;
   _other_id int;
BEGIN
   -- example makes no sense, just a loop demo
   FOR _id, _other_id IN
      SELECT id, other_id FROM loop_test
   LOOP
      UPDATE loop_test
      SET    id_copy = _id
           , other_id_copy = _other_id
      WHERE id = _id;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

While you just need the two columns of known type, that may be a bit cheaper than fetching whole (possibly big) rows.


Check that /var/log/mongodb/mongod.log, there is error message what tells what went wrong.

BTW, please don't insert screen captures. Just copy&paste lines and tag text as "code sample"


How to alter/migrate the schema of a table with tons of records

What I do in this case is:

create a shadow table with the new schema
move over all the data
xlock the original table
apply the changes since moving the data
rename the original table to bak
rename the "new" table to the correct name

This makes your downtime minimal at the cost of disk space.


Is there a way to have mysql automatically create partitions as needed?

Why do you want to use partitions?  "Large" is not a reason; I have seen billion-row tables that work just fine without partitioning.  PARTITIONing are unlikely to provide any benefit.

If you will be deleting "old" data based on the YEAR(date), that is a good use case for partitioning.

More discussion shows how to DROP the oldest partition, etc.


Count the nodes in a binary tree structure

This is most easily implemented in SQL Server using a Recursive Common Table Expression.

Table definition

DECLARE @binaryUser AS TABLE
(
    id          integer NOT NULL,
    joiningDate date NOT NULL,
    placement   char(1) NOT NULL,
    pId         integer NOT NULL,
    cId         integer NOT NULL,
    referBy     integer NOT NULL
);

Data

INSERT @binaryUser
    (id, joiningDate, placement, pid, cid, referBy)
VALUES
    (4, '20150202', 'L', 4, 5, 4),
    (6, '20150202', 'R', 5, 8, 4),
    (8, '20150202', 'R', 4, 11, 4),
    (9, '20150202', 'L', 5, 10, 4),
    (25, '20151212', 'L', 8, 9, 4),
    (31, '20150821', 'R', 8, 12, 4),
    (33, '20150821', 'R', 12, 13, 4),
    (36, '20150821', 'R', 9, 14, 4),
    (37, '20150821', 'M', 9, 15, 4),
    (38, '20150821', 'L', 10, 16, 4),
    (39, '20150821', 'M', 4, 17, 4);

Solution

This is presented as a script, but it is trivial to convert it to a stored procedure. The basic idea is to traverse the tree recursively, then count the rows found.

DECLARE @pId integer = 4;

-- Recursive CTE
WITH R AS
(
    -- Anchor
    SELECT
        BU.joiningDate,
        BU.cId,
        BU.placement
    FROM @binaryUser AS BU
    WHERE
        BU.pId = @pId
        AND BU.placement IN ('L', 'R')

    UNION ALL

    -- Recursive part
    SELECT
        BU.joiningDate,
        BU.cId,
        R.placement
    FROM R
    JOIN @binaryUser AS BU
        ON BU.pId = R.cId
    WHERE
        BU.placement IN ('L', 'R')
)
-- Final groups of nodes found
SELECT
    R.placement,
    R.joiningDate,
    Total = COUNT_BIG(*)
FROM R
GROUP BY
    R.placement,
    R.joiningDate
OPTION (MAXRECURSION 0);

SEDE Demo

Output:

╔═══════════╦═════════════╦═══════╗
║ placement ║ joiningDate ║ Total ║
╠═══════════╬═════════════╬═══════╣
║ L         ║ 2015-02-02  ║     3 ║
║ R         ║ 2015-02-02  ║     1 ║
║ L         ║ 2015-08-21  ║     4 ║
║ L         ║ 2015-12-12  ║     1 ║
╚═══════════╩═════════════╩═══════╝


Is it better to add an index and search via the index or add a unique constraint and let an insert fail?

Use a unique constraint (unique index), this is exactly what they're for.

The difference will be in your code. If you use the unique constraint method, you need to catch the error that the dbms will throw on duplicate entries, but if you check for the value explicitly first, you won't need to catch an error.

However, if you don't use the unique constraint method, you're not really restricting the duplication of the data at the database level, allowing the data to be corrupted easily by another application or even a maintenance script being run by a careless developer or dba.


Performance: Select count(*) where... VS select column_name where

It really depends on a database you use while executing the query and gathered statistics.

Oracle DB.  If stats have already been gathered , the first query would return the amount of rows in 0.00001 sec. If have not been, then it will be faster in five more times than while executing the second query as it is retrieving large amount of rows instead of just one.
For MS SQL will be almost the same thing.
For MySQL or MariaDB it depends on the engine. But I assume these databases always try caching the value of amount of rows as the optimizer will do queries faster.

In addition, columns in WHERE clause and in COUNT() do matter as well. They could be indexes or not and etc. There are lot of criteria considering perfomance between the queries but in the most cases the first one is recommended for using to get amount of rows. I could tell you more precisely if I had certain example.


Moving Oracle / Oracle datafiles to different file system on Linux

The simplest way, in 12c, you can move datafiles online, e.g:

alter database move datafile '/current_fs/system01.dbf' to '/another_fs/system01.dbf';


I would say that you are pretty close to be fair - and I think (though it is a personal opinion) that 3NF is more important at an educational level that it is when it comes to the real world. I say this because it is not always logical or practical to obtain perfect 3NF, this could be due to an applications design or the performance of the queries etc etc...

That being said, the best thing I could give you to remember 3NF is a quote a friend once told me (who read it in a book to be fair!) and that is this: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. - I think the person who said it originally was Bill Kent.

So on that basis, there are some minor improvements you could make; let's take your city table for example. You have an ID, Title and Post Code; well if you think about it, there are many Post Codes per City, and your design would mean that for each new Post Code you would need to add the ID and Title again - thus breaking the requirements for 3NF. For this you could have an additional table called PostCodes which could contain an ID, Name and City columns, you could then reference your city this way - which is better. The same goes for your Client Info table which has an address field - but it's possible that an address can be shared among many people, as can phone numbers, email addresses etc etc you get the picture!

The best way to remember it is this - if there is a possibility that you have to duplicate information just to satisfy your design - then you either have a special requirement (which is possible - like I said "real world") or your design is wrong - you need to judge it on a case by case basis.

I hope that this helps you.


This query will generate the proper restore chain for all databases hosted on the instance.  This should provide you with the proper statements and in their proper order.  Just copy the statements out of the RestoreStatement field and paste them into a new query window:

WITH BackupHist
AS
(
       SELECT
                s.server_name
              , d.name AS database_name
              , m.physical_device_name
              , CASE m.device_type
                     WHEN 2 THEN 'Disk'
                     WHEN 102 THEN 'Backup Device (Disk)'
                     WHEN 5 THEN 'Tape'
                     WHEN 105 THEN 'Backup Device (Tape)'
                     WHEN 7 THEN 'Virtual Device'
                END AS device_type
              , CAST (s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
              , CAST (s.compressed_backup_size / 1048576.0 AS FLOAT) AS compressed_backup_size_mb
              , s.backup_start_date
              , s.first_lsn
              , s.backup_finish_date
              , s.database_backup_lsn
              , CASE s.[type]
                     WHEN 'D' THEN 'Database (Full)'
                     WHEN 'I' THEN 'Database (Differential)'
                     WHEN 'L' THEN 'Transaction Log'
                     WHEN 'F' THEN 'File or Filegroup (Full)'
                     WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                     WHEN 'P' THEN 'Partial (Full)'
                     WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
              , s.recovery_model
              , ROW_NUMBER () OVER (PARTITION BY s.database_name, s.database_backup_lsn ORDER BY s.backup_start_date) AS Row
       FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m
              ON s.media_set_id = m.media_set_id
              RIGHT OUTER JOIN sys.databases d
              ON s.database_name = d.name
              AND s.recovery_model = d.recovery_model_desc
       COLLATE SQL_Latin1_General_CP1_CI_AS
), BackupHistFullIterations AS
(
    SELECT database_name
         , backup_finish_date
         , first_lsn
         , ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM BackupHist
    WHERE backup_type = 'Database (Full)'
)
SELECT    bh.server_name
        , bh.database_name
        , bh.backup_finish_date
        , bh.backup_type
        , CASE backup_type WHEN 'Database (Full)' THEN 'RESTORE DATABASE [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
            WHEN 'Transaction Log' THEN 'RESTORE LOG [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
            ELSE ''
          END AS RestoreStatement
FROM BackupHist bh INNER JOIN
        (
            SELECT *
            FROM BackupHistFullIterations
            WHERE BackupIteration = 1 -- Show the X most recent iteration(s)
        ) bhfi
     ON bh.database_name = bhfi.database_name
    AND (bh.database_backup_lsn >= bhfi.first_lsn
         OR bh.first_lsn = bhfi.first_lsn)
    AND (bh.backup_finish_date >= bhfi.backup_finish_date)  -- used in case db was rebuilt/lsn reset
ORDER BY 1, 2, 3

Some general disclaimers, this script does not include any MOVE clauses or other customizations (such as restoring from a striped backup set).  This won't be an issue for the TLog restores, but it may require that you adjust the Full (and potentially Differential) backup restore statement(s) to fit your needs.  I use this often when setting up a new server as an AG replica as the file paths between servers should match up to make life easier.


New transactions during a Sybase "dump database" command

The dump process happens in 3 phases:

All data and log pages are dumped.
Data pages modified during phase 1 by non-logged operations are dumped.
Log pages are dumped again.

Everything happening until end of phase 2 are contained in the dump. But the changes performed during phase 3 will be missing. Of course phase 3 is pretty fast, so the chances are that you'll not be missing anything. You can check the state of your transaction log after the dump. If anthing is in there, then you know these transactions are not in the dump.

But of course transactions happening afterwards will not be in the dump either so I guess it's not much of an issue.

If your concern is that you dump multiple databases and the dumps might be out of sync, you should consider using quiesce database.


SQL SERVER: How to get the stored procedure text EXCLUDING comments?

Below is a PowerShell example that uses the Microsoft.SqlServer.TransactSql.ScriptDom to parse procs and identify those with BEGIN TRAN statements. This version will download the assembly from NuGet if Microsoft.SqlServer.TransactSql.ScriptDom.dll doesn't already exist in the specified location. You could use a package manager instead for that task.

param (
    # Location of Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly.
    $scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll",
    # Url of Microsoft.SqlServer.DacFx.x64 in official Microsoft NuGet repository.
    $scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/150.4200.1"
)

$procQuery = @"
SELECT
      QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(p.name) AS ProcedureName
    , m.definition
FROM sys.procedures AS p
JOIN sys.sql_modules AS m ON m.object_id = p.object_id;
"@

# Add Microsoft.SqlServer.TransactSql.ScriptDom assembly, downloading if needed
Function Add-ScriptDomAssemblyType ($scriptDomAssemblyPath, $scriptDomNuGetUrl) {

    # if T-SQL script DOM assembly does not exist, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
    if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
        if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
            throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
        }
        $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
        if ($response.StatusCode -ne 200) {
            throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
        }
        $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
        [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
        $response.BaseResponse.Dispose()
        $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
        Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
        $tempZipFilePath | Remove-Item
        Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
        $tempUnzipFolderPath | Remove-Item -Recurse
    }

    # load assembly into app domain so it can be used in this script
    Add-Type -Path $scriptDomAssemblyPath
}

# parse CREATE PROCEDURE script, returning true if it contains a BEGIN TRAN T-SQL statement
Function Test-HasBeginTrasaction ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a BeginTransactionStatement is found
        if ($statement.GetType().Name -eq "BeginTransactionStatement") {
            return $true
        }
    }

    # return false if no BeginTransactionStatement is found
    return $false

}

############
### main ###
############

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]

        if( $hasBeginTransaction ) {
            Write-Host "Has BEGIN TRANSACTION: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}

This can be easily extended to also check for SET XACT_ABORT ON in order to include only those procs that need attention, assuming SET XACT_ABORT ON is properly placed in the proc code. Below is an additional function and body code to do this.

# parse CREATE PROCEDURE script, returning true if it contains a SET XACT_ABORT ON
Function Test-HasSetXactAbortOn ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a SET XACT_ABORT ON is found
        if (($statement.GetType().Name -eq "PredicateSetStatement") -and
            ($statement.Options -band [Microsoft.SqlServer.TransactSql.ScriptDom.SetOptions]::XactAbort) -and
            $statement.IsOn ) {
            return $true
        }
    }

    # return false if no SET XACT_ABORT ON is found
    return $false

}

############
### main ###
############

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]
        $hasSetXactAbortOn = Test-HasSetXactAbortOn -procText $reader["definition"]

        if( $hasBeginTransaction -and !$hasSetXactAbortOn ) {
            Write-Host "Has BEGIN TRANSACTION without SET_XACT_ABORT ON: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}


How to create column group in WiredTiger engine in mongodb 2.8?

Although WiredTiger itself does support a column stores, this is not supported with MongoDB.  WiredTiger as implemented in MongoDB only supports LSM trees and the default row-store.


When was second log file created for database?

This will query the default trace for any changes to the database, which will include the addition of files (but it will also include other things that you can't differentiate, like changing a database's compatibility level). It will also only include data that is currently in the default trace files (they age away; see this answer for details, but basically, how far back the history goes depends largely on how many trace events are captured), and requires that the default trace has not been disabled.

With all those caveats out of the way, this may at least narrow down the set of logins that have made changes to your database recently, and then you can ask them directly (assuming you can map logins to humans):

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.trace WHERE is_default = 1;

SELECT LoginName, NTUserName, HostName, StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 164                 -- Object:Altered
  AND DatabaseName = N'my_database_name' -- ***CHANGE THIS***
  AND ObjectID IS NULL                   -- not an object
  AND ObjectType = 16964                 -- database
  AND EventSubClass = 1                  -- only need one of the row pair
  ORDER BY StartTime DESC;


How to perform strictly SQL query that uses multiple values and searches all fields

Well I looked into the Full Text Search but found it a bit cumbersome to perform it on joined tables, additionally, it's not that efficient so I just cleaned up the function I have

public function findByFilterQuery($request)
{
    $dbFields = ['first_name', 'last_name', 'email_address', 'tt_group.name', 'tt_position.name', 'tt_division.name'];
    $params = isset($request['search']) ? explode(' ', trim($request['search'])) : [];
    $users = User::ArrayBuilder()->with('group')->with('position')->with('division');
    foreach ($params as $param) {
        foreach ($dbFields as $field) {
            $users->orWhere($field, '%'.$param.'%', 'like');
        }
    }
    $results = [];
    $request['page'] = isset($reqest['page']) ? $this->data['page'] : 1;
    $users = $users->paginate($request['page']);
    if (sizeof($params) && $users) {
        foreach ($users as $user) {
            $binaryArray = array_fill(0, sizeof($params), 0);
            foreach ($params as $key=>$param) {
                foreach ($dbFields as $dbColumn) {
                    $val = $user;
                    foreach (explode('.', $dbColumn) as $subColumn) {
                        $val = $val[$subColumn];
                    }
                    $val = strtolower($val);
                    $param = strtolower($param);
                    if (strpos($val, $param) !== false) {
                        $binaryArray[$key] = 1;
                    }
                }
            }
            if (!in_array(0, $binaryArray)) {
                $results[] = $user;
            }
        }
    }
    return sizeof($results) ? $results : $users;
}


Call dynamically a function using plpgsql

Don't ever call it like that. Because it returns a single value (RETURNS text) Call it like this instead, it's cleaner.

SELECT id, get_dcteam(id)
FROM monitoring_table
WHERE id = 30;

Now if you want it for more id's remove the WHERE Clause.

SELECT id, get_dcteam(id)
FROM monitoring_table;

Also you should probably rewrite that to use regular SQL, as it may be inlined.

CREATE OR REPLACE FUNCTION get_dcteam(id int)
RETURNS text
AS $$
    SELECT monitoring_table.dc_team
    FROM monitoring_table
    WHERE monitoring_table.id = get_dcteam.id;
$$ LANGUAGE sql
IMMUTABLE;

But one step further, I'd just delete the function entirely. It's basic SQL and it's just going to slow you down. Look at what you're doing: get_dcteam is querying monitoring_table, and you're calling it on a query that is already visiting that table! This will be much faster and clearer:

SELECT id, dc_team
FROM monitoring_table
WHERE id = 30;

Having previously answered this you said

  thanks for your answer. This call will give me several rows. What I Want is use this function in a where clause of an other one, but without passing the id in parameters. – zakaria mouqcit 3 hours ago

I'm not even sure what that means. Perhaps you could explain.


Where's my text highlighting in SSMS 2014?

SQL Server Management Studio (SSMS) 2016 has this feature.  There is no real reason to user the older versions now as it's supposed to be backwards compatible, although there may be a bug or two (see below).

Just press Ctrl+ F to see your keywords highlighted like this:

Maybe a few bugs for SSMS 2016 on connect ...


Stored Procedure to Rename Table and Column names

This solution uses the sys.sp_rename() database engine stored procedure.

  Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

But read carefully the warning message:

  Caution: Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to
  rename stored procedures, triggers, user-defined functions, or views;
  instead, drop the object and re-create it with the new name.

You can get table and column names by querying the INFORMATION_SCHEMA system information schema views (but see The case against INFORMATION_SCHEMA views by Aaron Bertrand).

--= returns tables
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME LIKE '%account%'
AND    TABLE_TYPE = 'BASE TABLE'
AND    TABLE_CATALOG = 'rextester'

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | TABLE_TYPE |
|---------------|--------------|--------------------|------------|
| rextester     | dbo          | DimAccountTime     | BASE TABLE |
| rextester     | dbo          | FactAccount        | BASE TABLE |
| rextester     | dbo          | FactAccountBalance | BASE TABLE |

--= returns columns
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  COLUMN_NAME LIKE '%account%'
AND    TABLE_CATALOG = 'rextester'

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | COLUMN_NAME  |
|---------------|--------------|--------------------|--------------|
| rextester     | dbo          | FactAccount        | account_id   |
| rextester     | dbo          | FactAccount        | account_item |
| rextester     | dbo          | FactAccountBalance | account_id   |
| rextester     | dbo          | DimAccountTime     | account_id   |
| rextester     | dbo          | DimAccountTime     | account_time |

I have set up the next schema on Rextester:

CREATE TABLE FactAccount
(
     account_id int primary key,
     account_item int
);

CREATE TABLE FactAccountBalance
(
    account_id int references FactAccount(account_id),
    balance decimal
);

CREATE TABLE DimAccountTime
(
    account_id int,
    account_time datetime
);

Rename columns

  Syntax: sp_rename 'schema.table.column_name', 'new_column_name', 'COLUMN'

--= cursor to rename columns
-------------------------------------------------------------
CREATE PROCEDURE dbo.renameColumns(@Catalog sysname, @OldName sysname, @NewName sysname)
AS
BEGIN

    DECLARE @schema nvarchar(128);
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE @cmd nvarchar(max);

    DECLARE curRes CURSOR FAST_FORWARD FOR
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  COLUMN_NAME LIKE '%' + @OldName + '%'
    AND    TABLE_CATALOG = @Catalog

    OPEN curRes;
    FETCH NEXT FROM curRes INTO @schema, @table, @column;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @cmd =   'sp_rename ''' + @table + '.' + @column + ''', '''
                 + replace(@column, 'Account', 'Items') + ''', ''COLUMN''';

        EXEC(@cmd);

        IF @@ERROR <> 0
        BEGIN
            CLOSE curRes;
            DEALLOCATE curRes;
            RAISERROR('Error renaming columns',16,1);
            RETURN -1;
        END

        FETCH NEXT FROM curRes INTO @schema, @table, @column;
    END

    CLOSE curRes;
    DEALLOCATE curRes;
    RETURN 0;
END
GO
--=-------------------------------------------------------------

exec renameColumns 'rextester', 'account', 'items';
GO

Rextester here

Rename tables

  Syntax: sp_rename 'schema.table_name', 'schema.new_table_name'

--= cursor to rename table's name
-------------------------------------------------------------
CREATE PROCEDURE dbo.renameTables(@Catalog sysname, @OldName sysname, @NewName sysname)
AS
BEGIN

    DECLARE @schema nvarchar(128);
    DECLARE @table sysname;
    DECLARE @cmd nvarchar(max);

    DECLARE curRes CURSOR FAST_FORWARD FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM   INFORMATION_SCHEMA.TABLES
    WHERE  TABLE_CATALOG = @Catalog
    AND    TABLE_NAME LIKE '%` + @OldName + `%'
    AND    TABLE_TYPE = 'BASE TABLE';

    OPEN curRes;
    FETCH NEXT FROM curRes INTO @schema, @table;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @cmd =   'sp_rename ''' + @schema + '.' + @table + ''', '''
                     + @schema + '.' + replace(@table, @OldName, @NewName) + '''';

        --EXEC(@cmd);
        select @cmd;
        IF @@ERROR <> 0
        BEGIN
            CLOSE curRes;
            DEALLOCATE curRes;
            RAISERROR('Error renaming tables',16,1);
            RETURN -1;
        END

        FETCH NEXT FROM curRes INTO @schema, @table;
    END

    CLOSE curRes;
    DEALLOCATE curRes;
    RETURN 0;
END
GO
--=-------------------------------------------------------------

exec renameTables 'rextester', 'account', 'items';
GO

Rextester here

Now, if you query INFORMATION_SCHEMA again, replacing account with items, this is the result:

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | TABLE_TYPE |
|---------------|--------------|----------------------|------------|
| rextester     | dbo          | dbo.DimItemsTime     | BASE TABLE |
| rextester     | dbo          | dbo.FactItems        | BASE TABLE |
| rextester     | dbo          | dbo.FactItemsBalance | BASE TABLE |

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | COLUMN_NAME |
|---------------|--------------|----------------------|-------------|
| rextester     | dbo          | dbo.FactItems        | Items_id    |
| rextester     | dbo          | dbo.FactItems        | Items_item  |
| rextester     | dbo          | dbo.FactItemsBalance | Items_id    |
| rextester     | dbo          | dbo.DimItemsTime     | Items_id    |
| rextester     | dbo          | dbo.DimItemsTime     | Items_time  |


What do these parameters mean at the beginning of my SQL Server query plan?

Yes, you are likely seeing implicit conversions due to the guesses SQL Server has to make. It is creating a plan that will work not just for the parameter value you're passing now, but other potential values, too. With strings it (or perhaps JDBC? not sure) chooses an arbitrary default of 4000 and Unicode. I believe there are ways in JDBC to not send Unicode (Unicode vs. non-Unicode implicit conversions are among the most tedious), but you're still going to deal with the length issue (which may or may not be an issue in your specific case; again, not sure).

To avoid the implicit conversions and guesses, and ultimately the scans and high CPU conditions, you need to make SQL Server understand the data type that is being used in the parameter through explicit / strong typing.

Here are my suggestions, in order of preference:

Switch to stored procedures.
Switch to stored procedures.
Declare your variables explicitly, e.g.:

DECLARE @p1 VARCHAR(36) = ?; UPDATE ... WHERE col = @p1;

Keep using CAST/CONVERT.


Deploying to an existing database from VS 2010 on TFS

For SSDT, when you choose Publish to generate a script the advanced options include "Always re-create database". Untick that and you're good to go.

Same option applies if you do a local debug build.

Similar options buried in the old VS2010 Database Project options somewhere.

To be honest, from the description of what you're doing SSDT and/or VS2010 database projects are overkill. Scribble a script in SSMS and be done with it.


mysql - can a replica have a different primary key than the source?

Depends on the Storage Engine
InnoDB
The auto_increment column will behave but the Replica will become very bloated. Why ???
For any given non-unique index in an InnoDB table, each entry in s secondary index will have a copy of the PRIMARY KEY as part of the entry.
If your InnoDB table has PRIMARY KEY (id) (which is 8 bytes), then each secondary index entry will have the id attached.
Now if you change the PRIMARY KEY to (username,id), then each secondary index entry will have the (username,id) attached. That can be up to 28 (8 + 20) bytes to each entry.
This will make INSERTs slower on the Replica.
This also allow duplicates of username.
You are better off creating a UNIQUE KEY on username.
MyISAM
For those still using MyISAM, PLEASE DO NOT ATTEMPT THIS UNDER ANY CIRCUMSTANCES !!! Why ???
Any MyISAM table that has a compound key that involves an auto_increment column with increment based on the presences of the other columns
EXAMPLE from the MySQL Documentation
CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

which prints out
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Do you see how id increments on a common value when grp already exists  in the table ?
The Source and the Replica will be so horribly out-of-sync. There will be no way to know which row in the Source belongs to a row in the Replica, and vice versa.
This makes changing the PRIMARY KEY of a MyISAM table totally dangerous for a Replica.
I have discussed this subject over 6 years ago

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?

UPDATE 2019-04-20 08:52 EDT
Of course, it is possible but BEWARE !!! : My post was just about the dangers of doing so. In the event of problems, MyISAM would be an absolute nightmare to unravel. InnoDB would be somewhat OK but you are messing with the PRIMARY KEY.
Back on Aug 14, 2012, I have recommended changing the storage engine and adding an index on a replica (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?). I still recommend do something like this for older versions of MySQL (When FULLTEXT indexes for InnoDB did not exist yet). The PRIMARY KEY can be a different story.
I can easily see the EXPLAIN plan of a SELECT being radically different on a replica from the EXPLAIN plan of its source given a completely different order of PRIMARY KEY columns.
You could never use a tool like pt-table-checksum and pt-table-sync to reconcile differences should the table become out of sync or replication breaking for unrelated reasons. Why ??? Both tools rely on the PRIMARY KEY columns. Even with plain MySQL, just running CHECKSUM TABLE table_name; would always be different between Source and Replica.
Your hoping for better performance runs the risk of MySQL interpreting the table as not being the same (because it simply won't be the same).
If you looking for the username to be unique, FORGET IT !!! Having (username,id) as a PRIMARY KEY allows for it. If you already have a UNIQUE KEY on username on the Source, then the Replica already has the key it needs.
SUGGESTION
Make sure the Source has a UNIQUE KEY on the username.
Don't do anything to the PRIMARY KEY on the Replica. If you want to the Source to have it, then do it on the Source so it transfers to the Replica.
EPILOGUE
Giving you a simple it's possible or it's not possible answer would be absolutely unfair to you given the bullet-riddled problems it would produce down the road for anyone who tries.
I would never recommend changing the PRIMARY KEY of a Replica regardless of storage engine or DBMS. If you are still willing to accept the responsibility of changing the PRIMARY KEY on a Replica after reading this, Godspeed, Spiderman !!!


Modelling users and groups - but with multiple types of groups

Create a Company table. Create/Verify you have a Group table and a GroupCompany table. Those will need populated on addition of a company to the system. You may want to create a template of groups common for a new company. Users that are added to the system are added to a group. Each group is tied to a company by the forementioned GroupCompany table. You can add a GroupType field to categorize company groups across the whole system.

Adding a company to your system helps you scale it across many organizations. It should be standard on most systems you architect. When the requirement comes in that we have a new client but they want all their data reported separately, you are covered.

Good Luck


What is the impact on a running database when OS date is changed and set to past

That's an extremeley broad question.  If your DB's tables use date in the primary keys you could run into duplicate problems.  If you query by date range you may have duplicates or holes in your data.  You may even have difficulty saying which row was written before another, depending on your design.  Reproducing any previous state may become impossible e.g. "What was the customer's balance on the 1st January?"  and so forth.

Really, you need to understand your application and DB design deeply, and test, test, test.


Instead of using double-quotes, try using single-quotes:

DELETE
FROM LOGIN
WHERE login = '336959686@login.com';

If the delete statement produces no error but doesn't appear to delete the row affected, that means one of the following:

The criteria (the WHERE clause) is not matching the desired row.
You have a transaction that is rolling back the delete, or has not yet committed the delete.  Check the transaction chaining mode using SELECT @@tranchained;  Perhaps try this:

SELECT *
FROM LOGIN
WHERE login = '336959686@login.com';
BEGIN TRANSACTION
DELETE
FROM LOGIN
WHERE login = '336959686@login.com';
COMMIT TRANSACTION
SELECT *
FROM LOGIN
WHERE login = '336959686@login.com';

Undetected database corruption - this is extremely rare.  Have you performed DBCC CHECKDB lately?  Make certain you have a reliable backup. Test the backup by restoring it onto another instance of Sybase ASE.  If you have done that, running DBCC CHECKDB should be performed. It may heavily impact I/O, so I would run it during non-peak hours.


Extremely slow query on indexed column in Postgres

There is a subtle problem hidden in your ORDER BY clause:
ORDER BY updated_at DESC

Would sort NULL values first. I assume you do not want that. Your column updated_at can be NULL (lacks a NOT NULL constraint). The missing constraint should probably be added. Your query should be fixed either way:
SELECT *
FROM   orders
WHERE  shop_id = 3828
ORDER  BY updated_at DESC NULLS LAST
LIMIT  1;
The multicolumn index @Ste Bov mentioned should be adapted accordingly:
CREATE INDEX orders_shop_id_updated_at_idx ON orders (shop_id, updated_at DESC NULLS LAST);
Then you get a basic Index Scan instead of the (almost as fast) Index Scan Backward, and you won't get an additional index condition: Index Cond: (updated_at IS NOT NULL).
Related:

Unused index in range of dates query
Optimizing queries on a range of timestamps (two columns)

Asides
You can save a bit of wasted disk space by optimizing the sequence of columns for your big table (which makes everything a bit faster):
id                 | integer                     | not null default nextval( ...
shop_id            | integer                     |
sent               | boolean                     | default false
name               | varchar(255)                |
total_price        | double precision            |
recovered_at       | timestamp without time zone |
created_at         | timestamp without time zone |
updated_at         | timestamp without time zone |

See:

Configuring PostgreSQL for read performance

Add NOT NULL constraints to all columns that cannot be NULL.
Consider text or varchar instead of varchar(255), timestamptz instead of timestamp and integer for the price (as Cents) or numeric (for fractional numbers) which is an arbitrary precision type and stores your values exactly as given. Never use a lossy floating point type for a "price" or anything to do with money.


They are identical.

The bullet point you mention states:

  The user name and host name parts, if quoted, must be quoted
  separately. That is, write 'me'@'localhost', not 'me@localhost'; the
  latter is interpreted as 'me@localhost'@'%'.

And that is what you did, you quoted them separately. In other words, all these are equivalent:

drop user    user_01@host01;

drop user   user_01 @ host01 ;

drop user  'user_01'@'host01';

drop user 'user_01' @ 'host01' ;

And if you wrote:

drop user 'user_01@host01';

... it would be interpreted as:

drop user 'user_01@host01' @ '%';


SQLNET.ALLOWED_LOGON_VERSION=12 and JDBC thin client

I just tested it too, and found that I can connect with 11.2.0.3/11.2.0.4 JDBC thin clients without error, but when using older thin clients like 10.2.0.4, then I get the expected ORA-28040: No matching authentication protocol.

In my opinion, the most logical explanation is that Oracle updated this document many times since 2011 and the particular line you quoted is in fact incorrect today.  It might have been correct pre-11.0.2.3.

Oracle doesn't maintain a change log (such as you would find on FOSS projects, a Wikipedia article, or even the StackExchange questions). However, you can get lucky sometimes if the page was crawled by the Internet Archive Wayback Machine. Turns out this Oracle doc was, so we're in luck, and they have snapshots of it dating back to 2011, preceding the CPUOct2012 by a year.
https://web.archive.org/web/20110715000000*/http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm

Curiously, if you look at the Dec 2011 version of the doc above, the paragraph you cited actually said (back then):

  ... following implications of setting the value to 11 [not 12] ...

It would seem to me that someone at Oracle updated the number from 11 to 12 without researching the implications.

I vote for answer #1, the manual is wrong.

ADDENDUM: The above criticism aside, you probably want to either submit a "Reader Comment" on the bottom of the Oracle doc, or submit an SR, or both.  If you have an Oracle customer relationship, you're probably paying a lot of money, so might as well make use of it and file the SR. Either way Oracle responds your a$$ is covered. i would very much like to see Oracle's response to this question.


I really can't get rid of these conditional statements in mysqldump?

It's really important to keep the conditional-execution comments. But if you absolutely know that the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this:

sed -r  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g

It will convert lines such as

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

to

SET SQL_MODE=@OLD_SQL_MODE ;

Because this line must run on any MySQL >= 4.1.1

Note that this will not remove multi-line conditional-execution comments, such as when dumping a trigger.

Since it's impossible to predict the future, it's better to store the dump with the comments on, and only remove them when you want to visualize it.

mysqldump ... > dump.sql
cat dump.sql | sed -E  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g > dump.no-comments.sql


Quickly restore test/QA SQL Server database

I believe you could use database snapshots. Though snapshot can not replace full database backup, since it contains only amended pages from the moment the snapshot was created.

Essentially, you restore the production database onto the development server, take a snapshot, perform whatever test work you need to.  When you need to return to the original state of the database, revert the snapshot.

Here is MSDN articles that describes process of recovery to the state before a snapshot was taken.

http://msdn.microsoft.com/en-us/library/ms189281.aspx


MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa?

There are two methods to mass convert all your MyISAM data into InnoDB

CONVERSION METHOD #1 : In-Place Conversion

You can run ALTER TABLE tblname ENGINE=InnoDB; against all MyISAM tables

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
SQL="${SQL} SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -AN-e"${SQL}" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
less /tmp/Mass_Convert_MyISAM_To_InnoDB.sql

If you are satisfied with the script's contents, then run it

mysql ${MYSQL_CONN} < /tmp/Mass_Convert_MyISAM_To_InnoDB.sql

I wrote a similar script 3 years ago : When to switch from MyISAM to InnoDB?

CONVERSION METHOD #2 : Dump and Reload

Verace's answer is short and sweet.

Here is the way to do it

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Gather all Databases except MySQL-centric Databases
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name"
SQL=${SQL} NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Dump the Schema Only, changing the Storage Engine on the Fly
#
DUMP_OPTS="--single-transaction --routines --triggers -d --add-drop-database -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > Schema.sql
#
# Dump the Data Only
#
DUMP_OPTS="-t -B ${DBLIST}"
mysqldump ${MYSQL_CONN} ${DUMP_OPTS} > Data.sql

Then, you run the two scripts

mysql ${MYSQL_CONN} < Schema.sql
mysql ${MYSQL_CONN} < Data.sql

I wrote a similar answer last year : How to export mysql databases with different ENGINE?

NOTE : If you want to convert just one database, here is the in-place method

DB_TO_COVERT=mydata
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
SQL="${SQL} SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema = '${DB_TO_COVERT}'"
mysql ${MYSQL_CONN} -AN-e"${SQL}" > /tmp/Mass_Convert_MyISAM_To_InnoDB.sql
mysql ${MYSQL_CONN} < /tmp/Mass_Convert_MyISAM_To_InnoDB.sql

GIVE IT A TRY !!!


sql server partitioned view execution plan questions

The DATEPART() function still has to be performed on every row before any filtering can be done/calculated.

Your view would be much more efficient if you changed it like this:

CREATE VIEW PartitionedTb_Sales
AS
SELECT *, 2010 AS SalesYear FROM tb_sales2010
UNION ALL
SELECT *, 2011 AS SalesYear FROM tb_sales2011
UNION ALL
SELECT *, 2012 AS SalesYear FROM tb_sales2012

Then, instead of using DATEPART() to calculate the year for each row, it will already be defined.

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE SalesYear = 2011


Why can I execute this query but not see its execution plan?

I do have privileges on the table accessed by SQL as the query runs
  fine.

And that is a false assumption. Just because you can query a view, it doesn't necessarily mean you have privileges on the underlying tables. Anyone can query the view ALL_TABLES, but a regular user doesn't have the privileges to query the underlying tables such as OBJ$ or TAB$.

This is how it works, and it is really easy to reproduce. Create two users, one with a table and view, then grant SELECT on that view to another user:

SQL> grant create session, create table, create view to u1 identified by u1;

Grant succeeded.

SQL> grant create session to u2 identified by u2;

Grant succeeded.

SQL> create table u1.t1(id number);

Table created.

SQL> create view u1.v1 as select * from u1.t1;

View created.

SQL> grant select on u1.v1 to u2;

Grant succeeded.

Now connect as u2, and try to select from the view:

SQL> connect u2/u2
Connected.

SQL> select * from u1.v1;

no rows selected

No problem. Now try to explain:

SQL> explain plan for select * from u1.v1;
explain plan for select * from u1.v1
                                  *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view


Import from incremental backups to a new host in Oracle 11g

yes, oracle can do that provided there is a full backup available to start the restore with. When that is restored you can recover the incremental backups against the restored database. When the incrementals were done online (mostly the case, you also need the archived log files)


Can I force MSDTC to control only within a single database?

Microsoft Distributed Transaction Coordinator is a service that runs on the machine that cannot be configured per database.  The service is either installed, or it isn't.

Since a distributed transaction must use a linked server, you could theoretically use permissions on any linked servers you have configured to allow only your process access.  You could use a DDL Trigger to prevent creation of any further linked servers (or at least to audit their creation).

Ad-Hoc Distributed Queries can be turned off at the server level using EXEC sp_configure 'Ad Hoc Distributed Queries' - this setting may help prevent the majority of distibuted query attempts, especially if the users don't have the ability to create stored procs.


Ola scripts = log shipping and transaction logs

I believe your answer lies in another answer here on StackExchange.  Transaction log shipping together with backup job = conflict?.

The log shipping process does its work by taking log backups. There
cannot be another job that does the same, it will break the log backup
chain. See Using Log Shipping as Part of a Recovery Plan. Your
recovery strategy should clearly document the location of the log
backups as taken by the log shipping job and detail step-by-step
instructions how to recover the database using these log backups. You
should also test this strategy to validate it.
If you create a maintenance plan (or Ola backup) for a database that is configured as
a log shipping database, do not have the maintenance plan create
transaction log backups. If a maintenance plan and log shipping both
attempt to create transaction log backups of the same database, log
shipping will not function correctly. You can do full and differential
database backups through your maintenance plan (or Ola backup) without any conflict
with log shipping.

According to Ola's Frequently Asked Questions
Does the SQL Server Maintenance Solution support log shipping?

The SQL Server Maintenance Solution supports log shipping. Full and
differential backup, integrity check, and index and statistics
maintenance are performed on the primary database. Transaction log
backups are not performed on databases that are involved in log
shipping.

So, if you implement Log Shipping, just keep in mind that THAT process will be taking your transaction log backups and NOT Ola's process.


pg_dump - how to split it into directories and files?

The directory format will write one file per table, so that sounds like a good candidate. But if you are interested in a text diff, it is of course useless.
The only thing I can think of is to dump each table, view and function separately using the -t option. You might still be disappointed if you want a diff of the table contents, because rows in a PostgreSQL table keep changing position.
Best practice is not to meddle with the database and then to reverse engineer a diff, but to use a schema versioning tool like Liquibase and write SQL diff files yourself.


Learning to draw entity-relationship diagram for a scenario involving Offices, Employees and Projects

Good. The essentials are there. A first rough list of entities is to take the nouns of the story - office, employee, project, clients, assignment, skill. Each table has a unique, sequential, meaningless key. Columns are given descriptive names. Here are the suggestions by entity.

office> The manager is an employee and should have a separate foreign key relationship with manager_employee_id. There is a many-to-many with employee. Avoid M-to-M relationships. In this case, an office has many employees. But an employee has one home office. I suggest naming fields uniquely with a common suffix describing the domain, office_address, instead of address.

employee> The job_id should be a foreign key to job instead of title. The office_id should be a foreign key to office instead of location. The column names should be hire_date instead of the start_date to avoid confusion with the project start_date. Use employee_address instead of address based on the domain suffix rule.

project> The project manager is a separate foreign key to employee pm_employee_id. What is the business difference between estimated_cost and quoted_cost? Make sure there is a foreign key field to match every relationship line. Client_id should be the foreign key. Office_id should be a foreign key.

assignment> Good, this is an intersecting table between project and employee. I suggest renaming the date to assigned_date to keep the suffix consistent. Notice that the hours_worked are in this table with the assigned_date. The assigned_date happens once. But the hours worked happen daily.

daily_assignment> Maybe there should be a separate child table called daily_assignment with its own primary key, a foreign key to assignment, and the worked_date and hours_worked as fields. This tracks each item of data uniquely at its own level.

client>
I suggest renaming the address to client_address.

There are ways to make it more complicated. But I think this model works well. It should give you flexibility to make business changes, yet still be simple enough to code.


Stored Procedure with HINT "WITH RECOMPILE" loses performance, solved with DBCC FLUSHPROCINDB

The hint WITH RECOMPLILE is telling your database to rebuild the execution plan every time. This rebuild requires some overhead, and can explain why it takes an extended period of time in some circumstances. Determining exactly why and when the recompile takes longer will require more information from the system (ambient load, how much change there is in the tables being analyzed, table contention, etc).

A more detailed explanation can be found here.
https://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why


How do you set the MySQL password on Mac OS X?

Are you getting an access denied error or something else?  mysqladmin should be printing a second line with more details, too. Have you looked over the installation notes for 5.5.25 (the current GA) at http://dev.mysql.com/doc/refman/5.5/en/osx-installation-notes.html ?

Sometimes the client don't look for the mysql socket file in the same place as it was written, or something deletes it if it's written in /tmp or another similar location.  If so then connecting over TCP might work (add -h 127.1 to the command line options).

If you need to reset the password, you can stop mysql and start it (temporarily) with an option to just allow any access without authentication.  I find the easiest way to get the current options it's running with is ps axwwwwwww | grep mysql.


Constraint to prevent overlapping Time periods

With the 24 hour restriction you can change your function to the following:

CREATE FUNCTION [dbo].[fnIsItemGroupAvailabilityValid]
(
    @availabilityStart time(0),
    @availabilityEnd time(0)
)
RETURNS int
AS
BEGIN
    DECLARE @OverLappingRows int

    IF(@availabilityEnd > @availabilityStart)
    BEGIN
        SELECT @OverLappingRows = count(*)
        FROM [dbo].[ItemGroupAvailability] iga
        WHERE @availabilityStart < iga.AvailabilityEnd
              AND @availabilityEnd > iga.AvailabilityStart
    END
    ELSE
        SET @OverLappingRows = 1

    RETURN @OverLappingRows
END

This will insure that the times are sequential, if not it returns an @OverLappingRows = 1 as a sentinel value to tell you something went wrong. You can set it to something else like -1 if you want to specifically catch that failure case.

I removed the equals from the comparisons to drop the inclusion aspect when comparing, it should now allow time spans to butt up against each other but not overlap. Dropped the OR as it's redundant now that we're verifying the the two time stamps are sequential before running the query.

Finally, all day should be 0:00 to 23:59, not 0:00 to 0:00 otherwise you need to do a bunch of convoluted logic.


How can I use all the selected values of a column, one by one, in another query?

Let's start from the inner query and work our way out. You want to return rows from Table3 for which a column matches (using the LIKE operator) any column from Table1, right? I would use the EXISTS operator for that. We can reword your problem as "return all rows from table3 for which there exists at least one matching row from Table1". Here's one implementation:

SELECT key_in_table
FROM Table3
WHERE EXISTS
(
    SELECT 1
    FROM Table1
    WHERE UPPER(Table3.some_column) LIKE '%' || Table1.column_name || '%'
);

You can run that query separately so you can be satisfied that it returns the results that you're after. The final query is as simple as integrating that subquery into what you already had:

SELECT dif_col_1, dif_col_2
FROM Table2
WHERE key_in_table =
(
    SELECT key_in_table
    FROM Table3
    WHERE EXISTS
    (
        SELECT 1
        FROM Table1
        WHERE UPPER(Table3.some_column) LIKE '%' || Table1.column_name || '%'
    )
);


What happens when sga and pga reach limits?

SGA: nothing serious, it is a fix sized memory area, once it gets full, the database reuses memory that is rarely accessed using LRU (least recently used) algorithm

Memory Architecture

PGA: Once PGA_AGGREGATE_LIMIT is reached, the database aborts calls of sessions having the most untunable (like PL/SQL collections) PGA memory, and those sessions receive ORA-04036 error. If that does not help and PGA usage is still above the limit, the database may even kill those sessions:

PGA_AGGREGATE_LIMIT

  Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

  Parallel queries will be treated as a unit. First, the sessions that
  are using the most untunable memory will have their calls aborted.
  Then, if the total PGA memory usage is still over the limit, the
  sessions that are using the most untunable memory will be terminated.

  SYS processes and background processes other than job queue processes
  will not be subjected to any of the actions described in this section.
  Instead, if they are using the most untunable memory, they will
  periodically write a brief summary of their PGA usage to a trace file.

(19327352832 + 12884901888)/1024/1024/1024 = 30

You have 32 GB memory in your server, you have allocated 30 GB memory to the database, and there is nothing else running on it, as you said. With proper configuration and sizing, you should not see swapping under normal circumstances:

hugepages for decreasing page tables
filesystemio_options=setall for direct I/O and avoiding filesystem caching
vm.swappiness set to 10 or even lower
sizing: reasonable amount of database sessions/processes

This does not mean the system will never swap. For example:

SYS sessions and background processes are not limited by PGA_AGGREGATE_LIMIT. You can still have rogue procesesses consuming PGA memory above the limit causing swapping.
If you use and unreasonably sized connection pool, like 100s or even 1000s of database processes, those processes have some private memory that is not part of PGA, so they add up on top of the SGA + PGA limit. This is especially significant on AIX, fortunately not so much on Linux.


Is this an appropriate forum for help with beginners dba questions?

From your tag I assume that you are into mySQL. Do you like video tutorials?

There are plenty of very good ones on YouTube:

Playlist with simple mySQL tutorials:

https://www.youtube.com/watch?v=g883j_5JsUc&list=PLvVojG9m055L_khQ4BhO5yYbrcEB23jOQ

Another one:

https://www.youtube.com/watch?v=mdGKgasqKCs&list=PLyTQS5V4iATQTdd8DqI5-NQ3HYUOMYtZE

My reputation doesn't allow me to post more links, but as soon as it goes up I'll add many more.


Postgres Compare with Date in Where Clause

Postgres will do the string to date conversion just once, because to_date is a STABLE function and the argument is a constant.

But if you’re worried about this, use the date literal syntax: date '2020-10-01'.

See https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT


Host not Allowed Connectivity to MySQL Server

Use a SQL commands like this:

mysql -u user -p -e 'Your SQL Query Here' database-name

to figure out what users are accessing your db.

In the results, did you only see localhost and 127.0.0.1 for the host? This would mean that only these two hosts are allowed for access. you can try:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'myIPaddress'

and don't forget to follow that with

FLUSH PRIVILEGES


Can I use a Clob as a table key (from Java)?

The simple version would be to generate a hash from the CLOB and use this as a key. This key will fit into the allowed key width for your engine (900 SQL Server, 767 InnoDB, 1000 MyISAM etc).

The hash can be generated by the engine as a computed column, trigger, or by some ETL process, or by the application

There is a faint chance of collision (birthday problem) but this would only matter for many records and poor choice of hashing algorithm


On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated?

Only PRIMARY KEY and UNIQUE constraints have an optional ON filegroup clause. That's because these constraints always create an automatic index to support their uniqueness. The filegroup clause decides where this index will live, so in your case it should always be 'app_index'.

You cannot specify a filegroup for other constraints such as FOREIGN KEY, CHECK or DEFAULT.

The metadata about all constraints (their name, table, columns etc.) is kept in system tables and thus is always in the PRIMARY filegroup (even if you change the default filegroup).

BTW: That's why you always have to restore at least the PRIMARY filegroup of a database. Without it, there is no metadata about the database content available to SQL Server. And this is why I recommend to use the PRIMARY filegroup only for metadata - at least in enterprise scenarios. Have at least one other filegroup and set that as default. This way PRIMARY remains the minimal possible size and runs the least risk of corruption.


This seems to me to be a pretty vanilla recursion query so long as you unwrap the JSON dynamically.

If HandyD will excuse some slight plagiarism...

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

;WITH hier ([Level], Parent, Person, staff) AS (
    SELECT
        1 AS [Level],
        convert(nvarchar(255),'') AS Parent,
        l1.person,
        l1.staff
    FROM OPENJSON(@Employees) WITH (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) l1
UNION ALL
    select
        [Level]+1,
        convert(nvarchar(255),h.Person) as Parent,
        oj.person,
        oj.staff
    from hier h
    outer apply openjson(h.staff) with (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) oj
    where [Level] < 100 -- or relevant maxrecursion level
        and oj.Person is not null
)
SELECT
    [Level],
    Parent,
    Person
FROM hier
order by [Level];


MAXDOP or 1 only? Or sql can use a value between 1 and configured maxdop

When you set MAXDOP = 8 this is not saying that the Degree of Parallelism will be 8 cores for your process.  It means that no more than 8 cores will be used for your process.

It could use only 1 core up to 8 cores with that setting.  This all depends on many other usage factors such as when several long processes are running on your server.  The SQL Server tries to manage the processes so that every running process gets some CPU time allocated to it very regularly.

https://www.brentozar.com/archive/2014/11/many-cpus-parallel-query-using-sql-server/

Brent's post also verifies that Paul White has audited Kendra Little's post as correct. (Just in case you want more verification.)

Again from Paul White from a discussion at http://www.sqlservercentral.com:

  The instance-wide MAXDOP setting sets the maximum possible degree of
  parallelism. A setting of zero (the default) allows SQL Server to use
  as many threads as there are processor cores that it is configured to
  use.

  The cost threshold for parallelism is only used by the optimizer.
  If the cost threshold is set to 5, the query must have an estimated cost of
  at least 5 before the optimizer will look for a parallel plan
  alternative.

In a case like that a MAXDOP 8 setting for the query may not exceed the cost threshold and thus will not create a parallel plan.

If you have a process where you specifically need to manage resources, more than 1 but less than the MAXDOP setting, you could add the OPTION (MAXDOP 4) (as an example) or some other setting that would fit the process you are running.

Therefore, you have some choices and are not stuck with just: 1 core, 8 cores, or "all" processors.


How can I create and initialize a schema for a embedded MySQL DB?

Here are the functions from the C/C++ API

mysql_library_init() : Should be called before any other MySQL function is called, preferably early in the main() function.
mysql_library_end() : Should be called before your program exits.
mysql_thread_init() : Should be called in each thread you create that accesses MySQL.
mysql_thread_end() : Should be called before calling pthread_exit()

Personally, I have searched high and low for the Perl equivalent of these C/C++ API calls that would open an embedded MySQL Server. Apparently, none exists for the Perl API.

Here is an excerpt from dev.mysql.com that verifies this:

  The embedded MySQL server library
  makes it possible to run a
  full-featured MySQL server inside a
  client application. The main benefits
  are increased speed and more simple
  management for embedded applications.

  The embedded server library is based
  on the client/server version of MySQL,
  which is written in C/C++.
  Consequently, the embedded server also
  is written in C/C++. There is no
  embedded server available in other
  languages.

You may have to manually trigger the start of mysql yourself with something like this:

@args = {"/sbin/service","mysql","status"};
$rc = 0xFFFF & system(@args);
if ( $rc != 0 ) {
    @args = {"/sbin/service","mysql","start"};
    system(@args) == 0 or die "mysql start failed : $?";
}


Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

You are right. XDB$RESOURCE is not in DBA_TABLES. It is in DBA_OBJECT_TABLES, because it is an object table.

Object tables are listed in {DBA,ALL,USER}_OBJECT_TABLES. All regular (non-object) tables are in {DBA,ALL,USER}_TABLES.


Restaurant reservations - Tables combinations

It's a pretty interesting question and in general a very tricky one. For your question, there are some limitations that make it a lot easier. I'll assume here that a table can be combined with another table if there id:s are adjacent.
create table tables
( tabid int not null primary key
, min_capacity int default 2 not null
, max_capacity int default 4 not null );

insert into tables (tabid) values (1),(2),(3);

If such an assumption can't be made, there need to be additional information that describes which table combinations that are valid:
create table related_tables
( tabid int not null
, reltabid int not null
, primary key(tabid, reltabid)
-- anti-reflexive
, check (tabid <> reltabid));

-- symmetric
create view symmetric_related_tables as
select tabid, reltabid from related_tables
union all
select reltabid, tabid from related_tables;

I'll ignore the latter and also further assume that a maximum of 3 tables can be combined:
select x.tabid as tabcombo
    , sum(min_capacity), sum(max_capacity)
from tables x
group by x.tabid
union all
select 10*x.tabid+y.tabid
     , sum(x.min_capacity+y.min_capacity), sum(x.max_capacity+y.max_capacity)
from tables x
join tables y
   on y.tabid = x.tabid+1
group by x.tabid, y.tabid
union all
select 100*x.tabid+10*y.tabid+z.tabid, sum(x.min_capacity+y.min_capacity+z.min_capacity)
     , sum(x.max_capacity+y.max_capacity)
from tables x
join tables y
   on y.tabid = x.tabid+1
join tables z
   on z.tabid = y.tabid+1
   and y.tabid = x.tabid+1
group by x.tabid, y.tabid, z.tabid;

For the first leg of the union, the aggregation is not necessary, but I'll
leave it for generality.
For the sample data above this results in:
tabcombo    sum(min_capacity)   sum(max_capacity)
1   2   4
2   2   4
3   2   4
12  4   8
23  4   8
123 6   8

Some vendors support GROUP BY CUBE, which could have shortened the query, but AFAIK, MySQL does not.
The next question is how long a reservation lasts? If not a fixed amount of time, say 4 hours, we will need a length or a stop time. I'll assume:
create table reservations
( reservation_id int not null primary key
, start_time datetime not null
, end_time datetime not null );

I'm in a bit of a hurry so this will only be a sketch, but for a specific time, the available tables with :S seats at the time :T can be determined as:
select x.tabid
    , sum(min_capacity), sum(max_capacity)
from tables x
where not exists (
    select 1
    from reservations r
    join reservation_tables rt
        on ...
    where rt.tabid = x.tabid
      and #T between r.start_time and r.end_time
)
group by x.tabid
having :S between sum(min_capacity) and sum(max_capacity)
union all
...

for the remaining legs of the union, you will have to check that none of the involved tables are occupied.


Who has SHOWPLAN permissions on a database

You asked "in a database", so setting aside sysadmins and people who have been granted permission at the server level, you can see people who were granted or denied that permission at the database level by running this query in the database you're curious about:

SELECT p.name, perm.*
  FROM sys.database_permissions perm
  INNER JOIN sys.database_principals p ON perm.grantee_principal_id = p.principal_id
  WHERE perm.permission_name = 'SHOWPLAN';

Note that I'm not filtering for state_desc (grant or deny) there because if you're interested in who has access, you're probably also interested in who's been denied access.


Occasional end user complaints about "slow" isn't an indication of a SQL Server performance issue.

However, enabling this will improve caching of data but adds some small overhead of managing PAE. AWE requires PAE too.


Downgrading Enterprise to Standard Edition

I do not believe there is a "downgrade path" that's officially supported for what you want to do. Nothing that is full proof or baked in, in other words. I would take full backups of all databases, then uninstall and reinstall (also reinstall required SPs). Microsoft's supported upgrade paths: https://msdn.microsoft.com/en-us/library/ms143393.aspx


MySQL query optimization with aggregate ORDER BY

Did you tried in the following way:

SELECT * FROM (
SELECT i_id, n_id, SUM(m1) AS sum1, SUM(m2) sum2
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
LIMIT 10 OFFSET 0)x
ORDER BY sum1 + sum2 DESC

I'm in SQL Server and there it's helping me.


Capturing Column Names in Dynamic SQL Statement

There's no TSQL api to examine the resultset shape of a procedure that returns multiple different resultset shapes.  For normal procs you can use sp_describe_first_result_set.

If you can afford to actually run the procedure and examine the results, the least-janky solution is to use SQL CLR.  In CLR you get a SqlDataReader that you can use to examine the resultset shape.


Is Oracle 11g database supported in windows 10?

Windows 10 is not certified officially.

Home edition Windows versions are not supported at all. That information you can find in the installation guide:

Windows x64 Software Requirements


Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table.

create table tmpIds(id int not null)
insert into tmpIds (59156)
insert into tmpIds (57045)
etc...

SELECT COUNT(*)
FROM "VERSION"
INNER JOIN tmpIds ON
"VERSION"."VERSION_ID" = tmpIds.id

drop table tmpIds


Error: There is a column named in table "*SELECT*", but it cannot be referenced from this part of the query

PostgreSQL doesn't see the column by the alias you've given it. That's not how it's parsed. It sees the z by the name you've tried to insert it as a on a psuedo-table EXCLUDED. You want to do this instead,

INSERT INTO foo
  SELECT 5 AS z
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.a;

For more information see the docs on INSERT,

  The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.


How to handle variations of the same data point?

I may just be repeating @jeffmc's answer, but I would add an author_names table that has a one-to-many relationship with authors, and then make your junction table (books_authors) a junction of books and author_names. (For clarity you may want to rename it books_author_names.) This way you are able to group authors by all their various identities/pseudonyms while being to tell exactly which moniker was used in the publication of a specific book.

I think this is a more precise way of doing it than using a time-based structure, as an author may use multiple identities simultaneously (e.g., Stephen King published both as Richard Bachman and Stephen King for several years).


New Group1:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   '1'                   AS Group
FROM Template t
LEFT JOIN Group1 g ON t.Name=G.Name

New Group2:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   '2'                   AS Group
FROM Template t
LEFT JOIN Group2 g ON t.Name=G.Name

The ISNULL is comparable to the COALESCE function, but simpler to type, without breaking fingers

  It needs to be a single result set since the numbers of groups is not limited to two

If your groups are in table Group, a single query would be:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   g.Group               AS Group
FROM Template t
LEFT JOIN Group g ON t.Name=G.Name


High availability SharePoint farm with restricted conditions

Note: This part of the answer corrects the misconception about Log Shipping not being compatible with Failover Clustering.

As @sp_BlitzErik implies in the comments, Log Shipping is very much compatible with Failover Clustering.  I've seen others fall prey to this implied lack of interoperability as it seems the MSDN article you link to is a direct reference of this MS Blog post from 2013.  It seems even @Shanky commented about this discrepancy directly on that article as well.

Long story short, that article is incomplete.  It should also reference Failover Clustering under Log Shipping along with a handful of other DR technologies.

Final note on using log shipping with Failover Clusters; the main caveat is to ensure your TLogs are being backed up to a clustered share that can failover along with the instance and other clustered services.

Answer: Now to address the rest of the answer.

You've got a few options.  Really what you're able to do is often limited by your budget (e.g. licensing, hardware, etc.), so any option proposed will need to fall within your budgetary constraints.  That being said, here some options I can think of, but by no means do I consider this a complete list of your available options:

Log Shipping: For the sake of completeness, you CAN configure log shipping on a failover cluster.  As I say above, be sure to configure the backup location as a shared folder that fails over along with the other clustered resources (e.g. SQL Server Service, SQL Agent, etc.).
Multi-Subnet Failover Cluster: This is adding onto what it sounds like you already have, but you can include a different site into your failover cluster.  Proper care will need to be taken so that automatic failover to your DR site is only done when necessary.  Additional constraints are replicating the data to the DR site.  Configuration of this approach is not simple and will depend a great deal on your Windows Version(s), SQL Server version, budget, etc.
SAN-to-SAN Replication: This is generally a hardware based approach and will depend upon your SAN vendor.  Most Enterprise-level SAN solutions support SAN-to-SAN replication and in conjunction with virtualization/etc. you can run across some elegant and expedient DR solutions.

Disaster Recovery is a complex topic and providing anything resembling a complete answer can't be provided on any forum answer.  If you're new to this or feel overwhelmed, I heavily suggest reaching out to MS, a MS Partner, a Consulting Firm/Consultant, etc. and ask for help.


Why does data get corrupted in "large" text files - Reasons to use database

I can only speak to MS SQL Server not MySQL I'm afraid.  SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here.  With a large text file any time you make a chance you re-write the entire file.  With a SQL Server when you make changes you only write down the PAGES that have changed.  This means that instead of writing megabytes an gigabytes or even terabytes you are writing down information in 8K chunks.  Less writing means less chance of causing corruption.

So what happens once you have some corruption?  Well in a text file you won't realize that the corruption exists until you happen to be looking at the corrupted portion of the file or the file will no longer open.  This means that over time more and more corruption will build up over time.  In SQL Server each page has a CHECKSUM built in.  This CHECKSUM is checked under several conditions.  Every time that page is written, when backups are taken with the WITH CHECKSUM option and when DBCC CHECKDB is run.  As a side note DBCC CHECKDB checks for a number of different possibilities of corruption not just the CHECKSUM.  Because of all this if you (or your DBA) is careful they will find the corruption more quickly.

Which leads us to repairing the corruption.  In a text file if your network admin is taking proper backups then you can hopefully restore your file back to before the corruption.  If you caught the corruption early enough.  If you didn't there may not be a backup old enough.  Also your only option is to restore the entire file.  This means any changes made to your file since the corruption occurred will be lost.  Databases provide a number of restoration methods.  If you are taking regular backups you can of course just restore the database.  This isn't much different than your text file.  However at least in SQL Server there are also options to restore parts of a database even down to the PAGE level.  And in fact with certain types of high availability there is even the option of automatically fixing a page as soon as corruption occurs.

Here is some additional reading for you.

SQL Skills postings on page checksums.

Understanding Pages and Extents

DBCC CHECKDB and database integrity

Sorry this is MS SQL Server and not MySQL but I imagine some of the principals are the same or at least very similar.


Chunk error when upgraded to mongodb 2.6 while uploading doc

Did you follow the guidelines?

  To upgrade an existing MongoDB deployment to 2.6, you must be running
  2.4. If you’re running a version of MongoDB before 2.4, you must upgrade to 2.4 before upgrading to 2.6.

I have upgraded from 2.4 to 2.6 with no issues on a sharded cluster.


Statistics and Execution plan in SQL Server

Statistics are being updated when the following number of rows have been affected in the said table : 20% * (table row count) + 500, unless the table contains less than 6 rows (in this case it is updated every time 6 rows have been affected), or less than 500 (same rule applies).

Now for the plan you displayed, it looks very weird. I can't repro it either.
Can you tell us how yo updated the statistics please ?

@++ ;)


If you can afford RAID, then go for it.. RAID or no RAID depends.. if your hardware provides redundancy then go with ASM and mirror it as you wish.
Tom Kyte has brilliant insight towards this..try visiting his site as well.


Do any document databases (noSQL) allow you to do cross database joins?

You can do it postgres

Update as of 9.3

You can now use the new postgres_fdw (foreign data wrapper) to connect to tables in any Postgres database - local or remote.

also you can use dblink functionality but it isn't part of the default PostgreSQL install, but you can add it in.-  dblink.


Store Decimal values without Trailing Zeros

The storage of Data shouldn't concern  you, as long you have the wanted precision
and representation of the data you can do on your application.
If you want a select that will remove the trailing Zeros
you can use
SELECT convert(DOUBLE PRECISION, 20.10)


How to handle -904 Invalid identifier error in oracle store procedure

The way you wrote your procedure, ORA-00904 is a compile time error, you can not catch that with a runtime exception handler.

Simplest workaround is to use dynamic SQL. Instead of:

 insert into test_proc_tbl(un_id) /* this column doesn't exist */
        select 'asd' from dual;

Use this:

execute immediate ' insert into test_proc_tbl(un_id) select ''asd'' from dual';

This will compile regardless of the existence of un_id column, and you can catch the error. However, you will lose dependecy tracking between the table and your procedure.


Decided to do away with GlobalParameter and use a special PackageID (0) for global parameters. Simplifies matters a great deal in both the model and the code.


Is a year-to-date dimension the same as a time dimension?

Year to date sales is a different measure.  Depending on your platform (OLAP systems, for example) it may be purely calculated on the fly.  If you have an appropriate unit of analysis to do so, store it, otherwise calculate it on the fly.


Restore subscriber from subscriber's backup

All you have mentioned should be fine. Just remember to script out the  replication before doing this as a backup.

Script out the replication.
Take the database backup for Publisher.
Backup one of the subscriber (right after no.1).
Copy subscribers backup (which is much lighter) to the future subscriber and restore
Configure the new subscription accordingly.


Fixed by connecting the database via 127.0.0.1 rather than localhost, thanks all!


Easy way to have non-tech users modify database?

If you run SQL Server Enterprise edition it comes bundled with Master Data Services which sounds like it would do what you want. It was originally intended to merge multiple sets of data and come out with a golden master but if you think about it that process can be applied to almost any data work.

It's non-cloud so safe for internal use. It has a web application used to define and edit the schema and relations, and also for users to view/enter/edit existing data (they can also do the latter through an Excel add-on which is pretty cool).

Using the same web app you can also set rudimentary business rules about data checking, set up data export options (views), and there are other options for initially loading data using T-SQL.

Data changes have lineage information and can be held back until approved by someone authorized. So there's a little workflow component possible, though I haven't seen that in use much.

For schema changes, it has some versioning/lineage capability, but I'm not 100% sure if you can see who made the change and/or if you can separate doing the change to the model from authorising a deployment of it. It has granular security, and it has a Metadata model to retain this information, but Microsoft have been on/off about whether they're deprecating that future or not.


Select rows with the highest value for one ID, after grouping by two IDs

Simply adding DISTINCT ON (PersonID) will do the job in PostgreSQL:
SELECT DISTINCT ON (PersonID)
       AgentID, PersonID, Count(*) AS BustCount
FROM   InvolvedIn i
WHERE  AgentID IS NOT NULL
AND    isCulprit
GROUP  BY AgentID, PersonID
HAVING COUNT(*) > 1
ORDER  BY PersonID, BustCount DESC, AgentID;

I added AgentID as (optional) third ORDER BY expression and tiebreaker to get a deterministic result in case of multiple agents with the same amount of busts. Adapt to your requirements.
Detailed explanation:

Select first row in each GROUP BY group?

We can do all of it in a single SELECT without subquery, because DISTINCT ON is applied after aggregation. About the sequence of events in an SELECT query:

Best way to get result count before LIMIT was applied

Oh, and WHERE isCulprit = TRUE is just a more noisy way of saying WHERE isCulprit.


There are other ways to do it, eg STUFF or just some simple integer maths, but these do make certain assumptions about the string, eg

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourString CHAR(13) )

INSERT INTO @t VALUES
    ( 1010000123456 ),
    ( 1010001234567 ),
    ( 1010012345678 ),
    ( 1010123456789 ),
    ( 1011234567890 )

SELECT
    CAST( STUFF( yourString, 1, 3, 0 )  AS INT ) [stuff],   -- 2 functions
    CAST( yourString  AS BIGINT ) - 1010000000000 do_math,  -- 1 function
    SUBSTRING(yourString, 3+PATINDEX('%[^0]%', SUBSTRING(yourString, 4, LEN(yourString))), LEN(yourString)) AS ypercube     -- 5 functions
FROM @t


Cannot set character_set_database and character_set_server to utf8mb4

OK, I found what caused the problem. If mysqld is installed as a service,  it sets the path to the executable to:

C:\xampp\mysql\bin\mysqld.exe --defaults-file=c:\xampp\mysql\bin\my.ini mysql

(if XAMPP is installed in C:\xampp, of course)

When mysqld is started with the --defaults-file argument, it reads the specified option file only. And as this path doesn't show up in mysql --help's list of options files, I had no idea of its existence.


You should look at the execution plan for the exact order. As the plan depends on heuristics based on indexes, statistics and the like, you can't tell simply from the query.

So hit Ctrl-L in SSMS and look at the execution plan. Each operator calls the one on its right, requesting rows as needed.

The physical order in the execution plan may be quite different from the logical order expressed in the query. Itzik Ben-Gan has a good flowchart.


Partition SQL Server Table by Year, 35 days rolling period

SQL Server does not currently have a way to declaratively specify a sliding partition window. It is necessary to execute T-SQL to remove the old boundary and create a new one. A T-SQL script (perhaps encapsulated in a stored procedure) can be scheduled daily for this purpose.

It is generally best to split and merge only empty partitions but this is not possible in your case because of the historical yearly partitions in addition to the 35-day window partition in the same table. One method to accomplish this with relative efficiency is to employ an identical partition scheme, function, and staging table. This allows you to operate on the 35-day window partition without excessive data movement and logging that would be required with SPLIT and MERGE of non-empty partitions.

The script below slides the daily window and moves data outside the active window using DELETE...OUTPUT (100K daily rows). The heavy lifting for the data within the active window (about 3.5M rows) is done with CREATE INDEX...WITH(DROP_EXISTING=ON). I would expect this to run well within your 3 hour maintenance window, probably seconds or minutes at worst.

This assumes your table and indexes are aligned. Below is example DDL.

CREATE PARTITION FUNCTION RangePartFunction (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101');
CREATE PARTITION FUNCTION RangePartFunction_Staging (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101');
CREATE PARTITION SCHEME RangePartScheme
    AS PARTITION RangePartFunction TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);
CREATE PARTITION SCHEME RangePartScheme_Staging
    AS PARTITION RangePartFunction_Staging TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);

--partition for last 35 days
DECLARE @Start35DayWindowDate datetime2 = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
ALTER PARTITION SCHEME RangePartScheme
    NEXT USED Part_35Day;
ALTER PARTITION SCHEME RangePartScheme_Staging
    NEXT USED Part_35Day;
ALTER PARTITION FUNCTION RangePartFunction()
    SPLIT RANGE(@Date);
ALTER PARTITION FUNCTION RangePartFunction_Staging()
    SPLIT RANGE(@Date);
GO
--example tables
CREATE TABLE dbo.Table1(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000)
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1(Col2, Col1)  ON RangePartScheme(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1(Col2) ON RangePartScheme(Col1);
CREATE TABLE dbo.Table1_Staging(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000)
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1)  ON RangePartScheme_Staging(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) ON RangePartScheme_Staging(Col1);
GO

Daily partition maintenance script (see inline comments):

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;

    DECLARE @Start35DayWindowDate datetime2  = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
    DECLARE @LastBoundaryDate datetime2 = (
        SELECT MAX(CAST(value AS datetime2))
            FROM  sys.partition_functions AS pf
            JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
            WHERE
                pf.name =   N'RangePartFunction'
        );

    IF @Start35DayWindowDate > @LastBoundaryDate
    BEGIN

        --switch last partition into staging table
        ALTER TABLE dbo.Table1
            SWITCH PARTITION $Partition.RangePartFunction(@LastBoundaryDate)
            TO dbo.Table1_Staging PARTITION $Partition.RangePartFunction_Staging(@LastBoundaryDate);
        --merge original function (with empty partition)
        ALTER PARTITION FUNCTION RangePartFunction()
            MERGE RANGE(@LastBoundaryDate);

        --create new yearly partition when the 35-day window passes the first of the year
        --NOTE: current year filegroup must be created and this script changed with the new filegroup name before February 6th of each year
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction()
                SPLIT RANGE(@LastBoundaryDate);
        END;

        --split original function for new 35-day window boundary
        ALTER PARTITION SCHEME RangePartScheme
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction()
            SPLIT RANGE(@Start35DayWindowDate);

        --rebuild staging table using original scheme
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);

        --switch partition with new window from staging table back into original table
        ALTER TABLE dbo.Table1_Staging
            SWITCH PARTITION $Partition.RangePartFunction(@Start35DayWindowDate)
            TO dbo.Table1 PARTITION $Partition.RangePartFunction(@Start35DayWindowDate);

        --move remaining rows into original table historical yearly file group (single day older than 35 days)
        DELETE dbo.Table1_Staging
            OUTPUT deleted.* INTO dbo.Table1;

        --apply same changes to staging function, scheme, and table to prepare for next day
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            MERGE RANGE(@LastBoundaryDate);
        ALTER PARTITION SCHEME RangePartScheme_Staging
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            SPLIT RANGE(@Start35DayWindowDate);
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme_Staging
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction_Staging()
                SPLIT RANGE(@LastBoundaryDate);
        END;
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
    END;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO