Where is located sp_msreplcheck_publish stored procedure?

If is hidden from general view but, if you connect using a Dedicated Administrator Connection, you can see it in master via:

USE [master];
GO

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_msreplcheck_publish'));

Not sure what you need to see the definition for, though, or why knowing where it is stored will help you accomplish anything. It's really this simple, and you can't change it, regardless of where it is stored:

create procedure sys.sp_MSreplcheck_publish
as
begin
    --
    -- Do the sysadmin check first as it is 4 times more efficient
    -- than is_member call and reduces performance overhead in case
    -- the user has sysadmin privileges
    --
    if    is_srvrolemember('sysadmin') = 1 or
        is_member ('db_owner') = 1
    begin
        return (0)
    end
    else
    begin
        raiserror (21050, 14, -1)
        return (1)
    end
end


Sql Server 2008 : What is the cost of accessing an index?

That is because SQL Server reads entire pages instead of records and when a index is used SQL Server might need to follow the bookmark to fetch the fields that are not included in the index.

So if your query isn't selective enough, it might end up reading all the pages of the index, and following the bookmarks to read all the pages of the actual table too. This ends up being more expensive than just reading all of the pages of the table.

This effect can be somewhat mitigated using included columns in which some columns are included in the index, but aren't part of the index (i.e. they have no effect on the sorting of the index).

SQL Server estimates the selectivity of your query based on statistics.

This article explains a lot of it. And the stairways series really is a good starting point to understand how indexes work.


Table Partitioning in SP1 for SQL Server 2016

I can do one better than Standard Edition, here is proof that it is available even in Express Edition (click to enlarge):

As are all of the other features "they say" are enabled. Everything in this post is true, for example.


I am afraid you cannot drop the database.

The problem comes the table that is corrupt. It's not necessarily the .ibd file. It is, more than likely, the data dictionary entry within ibdata1 (See pictorial representation of ibdata1).

I wrote about this back on Mar 02, 2012 : InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

Although tedious, here is what you can do to save the other tables in that corrupt database bad_db:

STEP 01) USE bad_db

STEP 02) Run SELECT COUNT(1) FROM tablename; for every table in bad_db. Collect all table names that do not crash mysqld when you attempt to run the count.

STEP 03) Run mysqldump -u... -p... bad_db table1 table2 ... tableN > bad_db_goodtables.sql
for all the tables that did not crash mysqld.

If you are going to use a different database name, then reload bad_db_goodtables.sql into that new database.

If you need the database to be the same name, BAD NEWS !!! You will have overhaul ibdata1. There are more steps:

STEP 04) mysqldump all the other databases into separate dump files

STEP 05) Perform InnoDB Infrastructure Cleanup (See my Oct 29, 2010 StackOverflow Post)

STEP 06) Reload all the other mysqldumps good databases.

Give it a Try !!!


Alert and notification option in job properties

Magier -- The alerts are designed to allow you to capture and sent a notification for just about any condition that occurs on your SQL server. One common practice is to have the SQL agent looking for different fatal error severity levels that could be returned as a result of an error being raised from an executed command. Or for specific errors that are reported in the SQL logs that could indicate IO issues right up to database corruption.

Brent Ozar has a write up about some of the common alerts that he recommends as part of their sp_blitz procedure.

You could most certainly add many more if you don't have an additional monitoring service running to give you a heads up when something isn't within the range that you're expecting or warnings/errors are reported in the SQL logs.


MySQL InnoDB locking on combined UPDATE-JOIN statements

Without seeing your specific query and table structures, it's not possible to give a really precise explanation or to know whether MySQL is being unnecessarily cautious; however, the message you are seeing appears to be a consequence of using binlog_mode = STATEMENT which you should be able to safely change to binlog_mode = MIXED.

The problem is, if your update query isn't deterministic, and is replicated by executing the same query on the replica -- as opposed to simply sending rows changed by the query to the replica, as would likely be done in MIXED binlog format (and certainly in ROW format) -- then the execution on the replica might be optimized differently and result in your tables being different.

If that is the case -- or if MySQL thinks that is the case -- then it is correctly preventing you from running a query that might not replicate properly because of your chosen binlog_mode.

The innodb_lock_wait_timeout value is not related to deadlocks.  It is a timer for queries that block waiting for row locks, but which could eventually succeed because the lock requests aren't incompatible with any other locks.  This timer affects queries which fail this:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A deadlock, on the other hand, is a completely different thing.

Deadlocks have no timer, because by definition, a deadlock will hang infinitely.  So, as soon as they are detected, MySQL will kill as many queries as necessary to resolve the deadlock, typically killing the one(s) that did the least amount of work so far.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlocks occur when two (or more) threads try to do mutually-incompatible things that cannot ever possibly complete, because each of them has to wait for the other one to finish before it can proceed.

http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html


SQL Server service automatically stops after starting

I have gone through your Error log file. As per your Error log file your System data has corrupted. Mainly in your case 'Model' database. as per your log error shown like

  66.2015-10-14 09:31:04.68 spid12s      Error: 9003, Severity: 20, State: 1. at line number 66 in your error log.

As per Gail Shaw Blog :-
If a model file is corrupt, then we need to restore a backup of model. Let's see what Books Online has to say about restoring model:

  "Restoring model or msdb is the same as for performing a complete
  database restore of a user database."

For Ref : - https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

If a model file is missing, we need to find out what happened to it. Has a drive not come online (or come online after SQL started)? Has someone accidentally renamed or moved the file? If we can make the model files accessible to SQL Server, in a similar fashion as described previously for the missing or inaccessible master files, then this will solve the problem.

Let's try starting SQL Server from the command line, with traceflag 3608

Sqlservr.exe –T3608

This will prevent recovery of all databases except model. This will  'Clearing tempdb database' and 'Starting up database 'model'.

Since the problem seems to be the clearing of TempDB.
let's go back to the beginning and try to restart SQL Server, but this time with traceflag 3609.

SQLServr.exe –T3609

This time SQL Server won't even start. We specified via use of traceflag 3609 that SQL Server should, on startup, recover TempDB from the existing files but not attempt to clear it.

you can also ref   http://www.dbforums.com/showthread.php?1203681-Error-9003-Severity-20-State-1 and https://support.microsoft.com/en-us/kb/822852#/en-us/kb/822852 for clarification.

NB:- In case of production databse , Before doing any troubleshooting you must consult with system Administrator and DBA Team. Always keep all backup file in safe zone.


Meaning of "Dependency" in Relation to Elementary DB Normalization

In this context "depends on" means "determined by" or "dictated by". Knowing a person's ID we can look up their date of birth. Given a second ID we will, in general, get a different date of birth. (Of course they may incidentally be the same date but that is not dictated by the table definition, just an accident since there are only a finite number of dates to use.) So to know a person's date of birth we must know the ID number. We say date of birth depends on the ID.
To determine if a table is 2NF I would think "Given a row, if I changed part of the key only, would I still be certain what the non-key columns' values are?" For the proposed table Ward-Nurse (WName(fk), NurseID, NurseName, ..), if I changed WName but kept NurseID constant, would I still be certain what NurseName would be? The answer is yes, because NurseName depends on (is determined by / dictated by) NurseID only and is not in any way determined by WName. Similarly with TeamCode and TeamSkill.
The difference between 2NF and 3NF is 2NF talks about keys and 3NF talks about non-keys. Given a row from the 2NF table Nurse (NurseID, NurseName, TeamCode, TeamSkill), if I changed any non-key column will everything else still be necessarily the same? If I change the value of NurseName will TeamCode or TeamSkill remain constant? Yes, they will. If I change TeamCode will TeamSkill and NurseName remain as they were? No, TeamSkill must change; TeamSkill must change when TeamCode changes so its value depends on the value of TeamCode. Hence (TeamCode, TeamSkill) becomes its own table in 3NF.


Unable to backup SQL Server from a newly added clustered drive

When adding clustered disk to an instance of SQL Server, there are a few steps that need to be taken.

Present the disk to all possible owners of that disk (nodes)
Bring the disk online from the first node that will own it
Bring the disk into the cluster - it'll now reside in available storage
Move the disk into the resource group for SQL Server
Set the SQL Server resource to DEPEND on that new disk
Double check sys.dm_io_cluster_shared_drives to see that SQL "sees" the disk. If it doesn't, one of the steps above was not successfully completed.

In this case, step 5 was missed which is why it didn't show up in the DMV listed in step 6.


Extracting two particular values from a multi valued column

If you don't need the extra commas, then:

SELECT CONCAT(id, ',',
          GROUP_CONCAT(bars ORDER BY bars))
    FROM subscriber
    WHERE bars IN ('bar1', 'bar4')
    GROUP BY id;

If you do need the commas, then search for "pivoting".


SQL Server Management Studio 2014 12.0.5000.0 "Invalid Column Name" Error

If your database [FAMISPRO.WORLD] is a linked server then you'll always get red line. SQL Server doesn't hold meta data about linked servers so there is no way to verify if a column exists in a table or not and this results in a red line under it.

When you navigate to tables in linked server database, you won't see little + sign in front of it as you see in your normal database tables because management studio only allowed to access tables and views from linked server database.

However if you want to verify column exist or not use below query.

Select * from [FAMISPRO].[WORLD].INFORMATION_SCHEMA.COLUMNS
where table_name like 'FAMIS_EMP'

You can also use some sys tables too in linked server.


Can you create an unrecoverable SQL Agent back log?

If you're job is running at the time the next scheduled execution comes along it will be skipped

https://stackoverflow.com/questions/865042/will-a-sql-server-job-skip-a-scheduled-run-if-it-is-already-running


SSIS flat file manager - skips the last record

I had the same problem on Windows Server 2008 R2 x64.  A flat file source would not load the last record... So this answer helped me, and now it's working fine.

  Go to advanced column view, select all the columns and set the text qualification drop down to false for all the columns.


Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?

It depends on the transaction isolation level.

In read committed it will release locks.
In "Repeatable read" and "Serializable" it will hold locks.

You can find more info in the following article: SET TRANSACTION ISOLATION LEVEL


MongoDB | Problem inserting embedded documents into document

To solve this problem I changed the code to the following:

lt_mode_data = data['lifetime']['mode']
    mode_dict = {}
    game_mode_data = GameModeData()
    lifetime_mode = LifetimeMode()
    for index, (mode, mode_data) in enumerate(lt_mode_data.items()):
        mode_dict[mode] = mode_data['properties']
    lifetime_mode = lifetime_mode.from_json(game_mode_data.from_json(json.dumps(mode_dict)).to_json())


Hide column values for some rows in auto-updatable views while retaining the "write" capability for them in PostgreSQL

I went with something like this

create or replace view clients as
    select id, name, address
    from data.clients;
grant select (id, name) on clients to employee;

The only downside is that for employee role select * will fail, but for my use case, specifying the columns is ok


DBCC CHECKDB consistency-based I/O error

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup.
The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that doesn't solve it, you can move on to next steps according to the guy who wrote CHECKDB, Paul Randal. He has a ton of articles on CHECKDB, that cover just about every conceivable scenario, but which ones are relevant to you will depend on what happens next when you attempt your repair. One that may be useful as a start, so you know which table you'll need to fix:

Finding a table name from a page ID


PostgreSQL's PGP_SYM_ENCRYPT log security

You can change your configuration settings to make this less likely to occur (What setting was triggering the log message in the first place?)

But in general, if you send your password to the database, then the database has your password. There is simply no way around this, other than not sending your password to the database and dealing with the consequences of that.  If you do send your password to the database, then you must guard the database log files with the same vigilance as you guard the rest of the database.


Delete SQL Server logins on replicated server

I dont see a need for 1.

You can use below script to move logins from one server to another. It uses SQLCMD and xp_cmdshell.

You can also look for a PowerShell option if you dont want to use xp_cmdshell.

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins from one server to another
-- Version  :: 1.0 for dba.stackexchange.com
--  Date    :: 05/24/2013
-- Change your paths for the output from D:\logs\ to whatever suits your need

declare     @dbid tinyint,
            @SQLText varchar(8000),
            @destserver varchar(255),

set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)


How to select top 10 records from each category

Use row_number() in a derived table. Partition by CellID and use the order by as per your specification. In the main query you filter on rn to get the top 10 rows per category.

select T.CellID,
       T.PathID,
       T.Duration
from (
     select T.CellID,
            T.PathID,
            T.Duration,
            row_number() over(partition by T.CellID order by T.Duration desc) as rn
     from dbo.YourTable as T
     ) as T
where T.rn <= 10;


How do I merge three update query into one single update query

You could do it with a CASE statement like so:
UPDATE P
SET P.next_promotion =
     CASE
         WHEN P.grade_level IN (14,15,16,17,18) THEN P.last_promotion + 4
         WHEN P.grade_level IN (7,8,9,10,11,12,13) THEN P.last_promotion + 3
         WHEN P.grade_level IN (1,2,3,4,5,6) THEN P.last_promotion + 2
         ELSE P.next_promotion
     END
FROM Promos AS P
WHERE P.grade_level >= 1
AND P.grade_level <= 18 -- Ensures we only update the records we want to change and doesn't do any extra work updating

And this equivalent query minimizes the code (if you're counting characters :).
UPDATE Promos
SET next_promotion =
     CASE
         WHEN grade_level BETWEEN 14 AND 18 THEN last_promotion + 4
         WHEN grade_level BETWEEN 7 AND 13 THEN last_promotion + 3
         WHEN P.grade_level BETWEEN 1 AND 6 THEN last_promotion + 2
         ELSE next_promotion
     END
WHERE grade_level BETWEEN 1 AND 18 -- Ensures we only update the records we want to change and doesn't do any extra work updating


Maybe it is not possible to know that information?

Exactly. It is assumed to be non-critical. You may (should) be lucky with the sql server logs but then those rorate and it is quite likely to no there - anymore (unless it happened very recently).

Check the SQL Server logs , imho your best chance.


In SQL Server, in layman's terms, how do you lock resources well enough to perform an INSERT-IF-NOT-EXISTS transaction?

Option 1: take a lock out which locks at least the range in the index that the row would exist in.
SET XACT_ABORT ON;
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM [TheTable] WITH (UPDLOCK, HOLDLOCK) WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)
COMMIT

HOLDLOCK will give serializable semantics and lock the range between existing keys in the index where the value would fit (if there is a suitable index, otherwise it will lock the whole table). UPDLOCK reduces the probability of deadlocks in this pattern as two concurrent queries can't take out the same range lock in the reading phase.
Option 2: You can just add a unique constraint on ColumnX and try the insert anyway and catch the error raised from duplicate key violation.
Given that Option 1 needs an index with leading column ColumnX anyway to meet your preference of not "locking the whole table every time" you might as well add one and define it as unique. The index will speed up the existence check anyway. With that in place I would select between option 1 and 2 on the basis of how frequently I expect attempts to insert duplicates.


Update many rows in a table with a single statement?

First here is sample data

mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (
    ->     id int not null,
    ->     value VARCHAR(255),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable (id) values (1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | NULL  |
|  2 | NULL  |
|  3 | NULL  |
+----+-------+
3 rows in set (0.00 sec)

mysql>

Here is the new query

update mytable A inner join
(
    SELECT 1 id,'xyz' value UNION
    SELECT 2   ,'abc'       UNION
    SELECT 3   ,'def'

) B USING (id)
SET A.value = B.value;

Here is the new query executed

mysql> update mytable A inner join
    -> (
    ->     SELECT 1 id,'xyz' value UNION
    ->     SELECT 2   ,'abc'       UNION
    ->     SELECT 3   ,'def'
    -> ) B USING (id)
    -> SET A.value = B.value;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | xyz   |
|  2 | abc   |
|  3 | def   |
+----+-------+
3 rows in set (0.00 sec)

mysql>


Move postgresql database from one server to another

You can run a dump of the database:

pg_dump yourdatabase | gzip -9 > outfile.sql.gz

and then import back into PostgreSQL on your work computer. It will take quite some time and the resulting file, even if compressed, might be in the gigabyte range, but should be small enough to be contained on a USB key.

On your work computer, create the new database and load the dump:

zcat outfile.sql.gz | psql yournewdatabase

If you have a fast connection (or can take the time), you can even transfer the database through the Internet via an encrypted SSH connection, or tools such as rsync.


Need help to understand tuning up a slow SQL server query

You can improve things greatly without rewriting the query by just adding indexes, from what I can tell.
Here's the execution plan I get on SQL Server 2019 (note that I changed the date to '20090814' just so it would return some results):

Table 'Users'. Scan count 18, logical reads 15430

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 94 ms.

This scans the entire Users table once to get the users that meet the date criteria, and then several more times to get the rest of the users that have matching display names.
Not ideal.
The query only takes 94 ms on my machine, mostly because everything is in RAM and the query is running at degrees of parallelism (DOP) 8.
However, adding these two indexes helps the situation a lot:
CREATE NONCLUSTERED INDEX IX_LastAccessDate
ON dbo.Users (LastAccessDate)
INCLUDE (CreationDate, DisplayName);

CREATE NONCLUSTERED INDEX IX_DisplayName
ON dbo.Users (DisplayName);

Now I get this execution plan:

Table 'Users'. Scan count 15, logical reads 153

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server is able to produce a seek plan here by using the internal GetRangeThroughConvert function to determine the range of possible datetime values that are equivalent to CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE).  Essentially, it rewrites the query in the background to match the suggestion made by Mo64.
Note: you are probably better off rewriting to explicitly use that range query, rather than depending on the hidden implicit conversion
You can read more details about this sort of execution plan on Paul White's blog: Dynamic Seeks and Hidden Implicit Conversions
The situation could be improved even further if you eliminate the key lookups - either by selecting less columns, or including all the necessary columns in the first index (on LastAccessDate).

I realize this is kind of a toy example, but hopefully it illustrates the general solution that can be applied to your real situation:

Add an index that will allow SQL Server to jump to the right date in the subquery
potentially rewrite the CAST from datetime to date to be two inequality conditions.  In other words, replaced this:

WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)

With this:
WHERE LastAccessDate >= '20160814' AND LastAccessDate < '20160815'

Add an index that will allow SQL Server to jump to the matching varchar(50) column values


Start using your licenses in a smart way. Many customers buy EE licenses, only using SE features. Don't underestimate the power of SE. You can use SE with a max of 4 sockets and with modern CPU's that's a lot of power. SE1 has a max of 2 sockets. See Database Licensing - Oracle

Next to that, consider consolidating your smaller applications into 1 serious database, often leading to much better CPU and license utilization. For one customer I saved 800.000,-/year doing so.

The question about how to size for over provisioning is really a business question. How does the business get hurt when it can't process every request immediately? Some will lose a lot of money for lost sales, others can handle it.


I find UPDATE statements, with complex WHERE clauses, are easier to understand, and maintain, when written as a MERGE statement.

Examples

MERGE INTO TableA a1
USING (
 SELECT TableA_ID, 'Y' value1
 FROM TableA a
 JOIN TableB b ON a.TableB_ID=b.TableB_ID
  JOIN TableC c ON a.TableC_ID=TableC_ID
WHERE
    b.class in ( '1', '2' )
  AND c.name NOT LIKE 'MPG%'
) b1
ON (a1.TableA_ID=b1.TableA_ID)
WHEN MATCHED THEN UPDATE
SET a1.value=b1.value
;


Which MongoDB administration UIs offer a table view (OS X)?

MongoDB has a good list of administration tools and interfaces here.  Of these, I've played with a couple mostly on Windows, but some options for you that should work on OS X (because they are Java based) are:

Robomongo
Umongo

Of the two, I prefered Robomongo.  There is also an OS X specific tool called MongoHub that you may want to try out.


You cannot alter the LOGGED attribute of a LOB column. However, you can alter the table by adding a new LOGGED LOB column, update the new column from the existing column, then drop the original column. It may require less wasted disk space but is unlikely to be faster than using ALTOBJ.


As Max mentioned the alert probably fired just prior to the log growing/needing to grow. SCOM collects transaction log free space % although I am not sure at what threshold the alert will fire.

here is a quick example to show you what state tempdb is probably in when you get these alerts but no log file growth.

first create a database, set recovery to full, and back it up

    create database tlogspace
    on(name=tlogspace_dat,
        filename='c:\temp\tlogspace.mdf',
        size=4MB)
    log on (name=tlogspace_log,
        filename='c:\temp\tlogspace.ldf',
        size=1MB);
    go

    alter database tlogspace set recovery full;
    go
    backup database tlogspace to disk='nul';
    go

now switch to that database, create a table, and run DBCC sqlperf(logspace) to check the size oand free space in your log file.

use tlogspace
go

create table data(id int identity(1,1), col varchar(8000))
dbcc sqlperf(logspace)

On my system I have logfile size of 0.9921875 and log space used (%) of 48.4245. Now insert some data into the table and run DBCC sqlperf(logspace) again. On my system 45 rows inserted gave desired results(number of rows inserted may need to be adjusted).

insert into data(col)
select replicate('a',8000)
go 45 --may need to adjust number
dbcc sqlperf(logspace)

This time, the DBCC sqlperf output should show that log size is the same but log space used is just under 100%. In this case, SCOM would probably fire an alert that log space is low. There is no more activity going on to cause the log file to grow and (in this example) no tlog backup to free up used space. tempdb is in simple recovery, so your active transaction probably used up most of the available space and did not release it but there was not enough activity in tempdb to trigger log file growth thus causing the alert fire.

cleanup database when finished

use master
drop database tlogspace


ORACLE: performance of query running across two schemas

Briefly, such an approach appears to me as a valid first step in integration process.  As long as the second schema is used for querying only, all its objects can be moved to readonly tablespace thus minimizing impact on backup time.

You may or may not need to tune the instance, it all depends on how much load it adds to the database. For running reports, it should be safe enough to create resource profile (and use it) which limits CPU/Session time. Depends on your license, you can use Database Resource Manager which gives many more options to control resource consumption compared to profile.


Can't set root password after fresh installation of mariadb using ansible

Solution:
We need to add login_unix_socket: /var/run/mysqld/mysqld.sock to the task:
- name: update mysql root password for root account
  mysql_user:
    name: root
    login_unix_socket: /var/run/mysqld/mysqld.sock
    host: 'localhost'
    password: '{{ mysql_root_password }}'
    priv: "*.*:ALL,GRANT"
    check_implicit_admin: true

Explanation:
The passwordless login is not possible when connecting through the network (this is what ansible does), only when connecting using the unix socket (the mysql CLI does that when connecting to localhost). So we need to force ansible to connect to the socket.
I hope this post saves somebody the time figuring that out.


What is most efficient way to store one or two chars?

It doesn't matter much: text (= varchar) would be a little smaller, and an enum type would be a little faster (to compare).
Make the decision based on usability: if you might ever need to delete one of the values, don't use an enum. The best is probably to stick with what you have now.
As for indexing, that might just about make sense for 8 different values, but you would have to check if any of your queries might actually benefit. Without knowing the queries, it is impossible to say for sure.


Select value in Left Join ordered by Date

You're on the right track - except that you need a RIGHT JOIN and not a LEFT one - that's IF you want to include the person with the login_user_id of 5 in your results. If not, a LEFT JOIN will indeed work.

Firstly, the RIGHT JOIN. You're either looking for this (works on version 5.6 - see fiddle here):

SELECT
  tab.login_user_id AS id,
  COALESCE(cs.user_login, '---------') AS name,
  COALESCE(cs.user_id, 0) AS lid,
  tab.dtm AS tme
FROM
  cs_users cs
RIGHT JOIN
(
  SELECT login_user_id, MAX(login_dtm) AS dtm
  FROM cs_login_log
  -- WHERE clauses as applicable
  GROUP BY login_user_id
) tab
ON cs.user_id = tab.login_user_id
-- WHERE clauses as applicable
ORDER BY tab.login_user_id;

or something like this which works for version 8 up (fiddle).

SELECT
  cs.user_id AS id,
  COALESCE(cs.user_login, '--------') AS name,
  tab.ltm AS tme,
  tab.rn
FROM
  cs_users cs
RIGHT JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY csl.login_user_id
                     ORDER BY csl.login_dtm DESC) AS rn,
    csl.login_user_id AS id,
    csl.login_dtm AS ltm
  FROM cs_login_log csl
  -- WHERE clauses as applicable
) tab
ON cs.user_id = tab.id
WHERE tab.rn = 1
-- AND whatever other clauses are applicable
ORDER BY tab.id;

This second query uses the (not strictly necessary in this case) ROW_NUMBER() Analytic (aka Window) function. These functions are very powerful and well worth getting to know and I would urge you to explore this fiddle and at least "play with" an instance of version 8 on your own personal laptop if you don't have it in work.

Also the COALESCE function is not strictly necessary here - but it can be a great help for the likes of reporting, making them much more aesthetically pleasing.

Result:

id  name        lid tme
1   cornerstone 1   2019-09-02 15:49:20
2   claire      2   2019-09-15 19:18:07
3   ben         3   2019-09-02 16:12:14
5   ---------   0   2019-09-02 16:12:33

You can use EXPLAIN EXTENDED to see which one works better for you - I have no running MySQL instances, only fiddles, so I can't do that sort of performance analysis. From version 8.20 there will be serious enhancements to this part of MySQL's functionality in this area - well worth an upgrade.

On the other hand, if you're only looking for the most recent login times for the three users in the cs_users table, then all you have to do is simply turn the RIGHT JOIN into a LEFT one (see the fiddle here).

SELECT
  tab.login_user_id AS id,
  COALESCE(cs.user_login, '---------') AS name,
  COALESCE(cs.user_id, 0) AS lid,
  tab.dtm AS tme
FROM
  cs_users cs
LEFT JOIN
(
  SELECT login_user_id, MAX(login_dtm) AS dtm
  FROM cs_login_log
  WHERE login_user_type = '1' AND login_status = '1'
  GROUP BY login_user_id
) tab
ON cs.user_id = tab.login_user_id
WHERE cs.user_status = 1  -- no data provided!
ORDER BY tab.login_user_id;

Result:

id  name    lid tme
1   cornerstone 1   2019-09-02 15:49:20
2   claire  2   2019-09-15 19:18:07
3   ben 3   2019-09-02 16:12:14

As noted in the SQL, I have no data provided for the cs_users field user_status (my working assumption is that it's 1 for everybody). The key to your understanding this problem is to realise that there is an ENORMOUS bug in all versions of MySQL prior to (AFAIK) 5.7 (I'm sure that all versions of 8 don't suffer from it - at least by default). See the above fiddle also.

There is a (super-)variable called sql_mode which is (more or less) one massive hack! There are various bits and pieces you can add or subtract from this variable - it's basically a long line of different sub-variables. One of these is ONLY_FULL_GROUP_BY which isn't on by default in version 5.6 - the one that you're using. This means that queries like this:

SELECT
  login_user_id,
  login_dtm,
  MAX(login_dtm)
FROM cs_login_log
WHERE login_user_type = '1'
  AND login_status = '1'

give a result with your data.

login_user_id   login_dtm                      MAX(login_dtm)
            1   2019-09-02 15:23:38 2019-09-15 19:18:07

Try running that query on PostgreSQL (a decent server!) and you'll get this (fiddle):

ERROR:  column "cs_login_log.login_user_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:   login_user_id,

So, the reason that your (second) SQL didn't work as you'd hoped was because your internal query only returned one data point and therefore you could only join to that one record and you got NULL for the rest - to be expected. When you GROUP BY the login_user_id (as @Akina also suggested) you get a result for all the users, which is what you want.

My advice is to add ONLY_FULL_GROUP_BY to the sql_mode of your server and you won't be caught by this again! It bit me in the ass when I was starting out on my SQL journey also! :-)

What I did was created and populated the tables:

CREATE TABLE cs_users
(
  user_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_login VARCHAR (20) NOT NULL
);

and

CREATE TABLE cs_login_log
(
  login_id INTEGER NOT NULL,
  login_user_id INTEGER NOT NULL,
  login_user_type INTEGER NOT NULL,
  login_dtm DATETIME NOT NULL,
  login_status INTEGER NOT NULL
);

populate:

INSERT INTO cs_users (user_login)
VALUES
('cornerstone'), ('claire'), ('ben');

and

INSERT INTO cs_login_log
VALUES
(        2 ,             1 ,               1 , '2019-09-02 15:23:38' ,            1 ),
(        6 ,             1 ,               1 , '2019-09-02 15:49:20' ,            1 ),
(        8 ,             2 ,               1 , '2019-09-02 16:10:14' ,            1 ),
(        9 ,             3 ,               1 , '2019-09-02 16:12:14' ,            1 ),
(       10 ,             5 ,               1 , '2019-09-02 16:12:33' ,            1 ),
(       25 ,             2 ,               1 , '2019-09-15 19:18:07' ,            1 );

Run the query (either one):

SELECT
  tab.login_user_id AS id,
  COALESCE(cs.user_login, '---------') AS name,
  COALESCE(cs.user_id, 0) AS lid,
  tab.dtm AS tme
FROM
  cs_users cs
RIGHT JOIN
(
  SELECT login_user_id, MAX(login_dtm) AS dtm
  FROM cs_login_log
  GROUP BY login_user_id
  -- WHERE clauses as applicable
) tab
ON cs.user_id = tab.login_user_id
-- WHERE clauses as applicable
ORDER BY tab.login_user_id;

Result:

id  name        lid tme
1   cornerstone 1   2019-09-02 15:49:20
2   claire      2   2019-09-15 19:18:07
3   ben         3   2019-09-02 16:12:14
5   ---------   0   2019-09-02 16:12:33

Note the name of '--------' and lid of 0 for user with login_user_id of 5 in the cs_login_log table. Of course, these placeholders are there because there is nobody with a user_id of 5 in the cs_users table.

In the second fiddle, I've left in the rn (ROW_NUMBER()) field - yet again, not strictly necessary, but it helps to show my train of thought as I constructed the query - obviously, delete as applicable!

Next time you ask a question, you might get better responses if you include your tables as DDL (CREATE TABLE foo (...);) and your table data as DML (INSERT INTO foo VALUES (...);), or you could construct a fiddle (just make sure to put the fiddle stuff here also) - help us to help you! There are some articles on how to ask questions here on my profile, you might want to take a look? p.s. welcome to the forum!


The minimum permission to create/alter index can be easily found in corresponding BOL articles CREATE INDEX (Transact-SQL) and ALTER INDEX (Transact-SQL):

  Requires ALTER permission on the table or view.

This means you can grant alter table on every table of interest that already exists.

If you don't want to grant so granular permissions you can grant alter on schema(s) where your table(s) reside, this will assure that the user would be able to create/alter indexes on any future table/view.


Oracle shared memory exception ORA-04031

Oracle 10g or earlier? Probably you already restarted the database. Then you can check SQL Area:
    SELECT sql_text, parsing_schema_name, executions FROM v$sqlarea ORDER BY executions desc;
If there are tens of thousands SQLs with 1 execution - you are having shared pool fragmentation. To avoid future exceptions either rewrite SQLs (usually not using binding variables causes SQL area bloat) or upgrade to 11g. It better handles shared pool fragmentation.


How can I in Oracle discover the average number of rows affected per transaction?

Not exactly an answer to your question but amount of changes generated by the session can be found using such query.

select st.inst_id, trunc(st.value/(1024*1024)) as MB, ss.sid, ss.username,
  ss.osuser, ss.machine, ss.program, ss.logon_time
from gv$sesstat st, v$statname sn, gv$session ss
where st.statistic# = sn.STATISTIC# and sn.name = 'redo size'
  and st.inst_id = ss.inst_id and st.sid = ss.sid and ss.type != 'BACKGROUND'
order by 2 desc;

Number of rows changed correlates with amount of redo generated.


What are the disadvantages of using surrogate keys?

There are many disadvantages to using surrogate keys, the most important ones IMHO are:

Using natural keys significantly reduces the number of joins that your queries need to perform. With a surrogate key, you will always have to join back to the original table to get meaningful values.
Surrogate keys 'abstract' the actual values users are looking for from the optimizer which, significantly hinders the usefulness of statistics.
Natural keys enforce logical consistency and prevent duplicates. Surrogate keys are way too often abused as a magic solution, without regard to the candidate keys which in turn result in logical duplication of data.

While there are (rare) cases that justify the use of surrogate keys, the state-of-the-art is that 99.9% of the databases that i've seen just use this 'magic one-size-fits-all' solution, with detrimental impact on performance, modularity, and data consistency.

To learn some more about it, watch this session that I delivered on the subject at Silicon Valley Code Camp 2017.


What's an example of an exploitation of the security from enabling Ole Automation Procedures Server Configuration option?

I don't think I would look at it in terms of exploitation. It's very similar to (if not the same security issue as) xp_cmdshell. These are wide open in terms of what you can execute if they are enabled, and for processes that reach outside of SQL Server, they use the security context of the service account running SQL Server (although xp_cmdshell does at least have the option to set up a proxy account for non-sysadmin users).

But, you need to be in the sysadmin instance-level role in order to execute the sp_OA* procs (or be given explicit EXECUTE permission), so you can at least keep all uses of it within the confines of stored procedures that use Module Signing to get the elevated permission (yes, specifying WITH EXECUTE AS N'dbo' also works, if the Login associated with the dbo User is in the sysadmin role, but Module Signing is better).

Looking at the CIS quote in @kevinsky's answer (i.e. "These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server."), unless I am missing something, that seems to be an overblown, or possibly uninformed, reaction. It would certainly be a huge issue if all users had access to it if it were enabled. But really, it's only users that already have the ability to enable "OLE Automation procedures" that are allowed to use the sp_OA* stored procs. I'm not saying that it should be enabled if it isn't being used, but this doesn't seem to be an inherent security risk.

I think the security "issue" is that it allows for code to be created by developers that either does things that you might not approve of on the network or file system or at the OS level, or perhaps there are unintended consequences.

That being said, if you are asking with regards to legacy code that you need to support, then while not ideal, it will probably work. But, if this is in regards to new development then you shouldn't be using OLE Automation; you should instead be using SQLCLR. SQLCLR has many benefits over OLE Automation, including (but not limited to):

Better security: you can restrict code to being confined to SQL Server internally (SAFE) or able to access external resources but not alter the system or do asynchronous work, etc (EXTERNAL_ACCESS)
Ability to impersonate caller's Window's login when accessing external resources (not confined to service account)
Better datatype support: OLE Automation should be COM interface, and it doesn't support MAX types, XML, any of the more recent types.
Better memory management


SQL 2008 Merge Replication and table-level data compression

It will fail if you try to save replicate including compression, but if you set it to only "replicate the partition scheme but not compress the data on the Subscriber."

  The Distribution Agent does not check for down-level Subscribers when it applies scripts. If the replication of compression is selected, creating the table on down-level Subscribers will fail. In the case of a mixed topology, do not enable the replication of compression.

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

Following from that, what happens when you restore a backup from sql 2008 enterprise with some table compression enabled, onto a sql 2008 standard server instance.. I've not actually tried and it's an interesting concept.


Postgres inherit indexes for partitioned tables

As explained before, INHERITS does not copy index definitions.

Therefore, you need to state both inheritance and index copying.

    CREATE TABLE sub_tab (LIKE tab INCLUDING ALL) INHERITS (tab);

Thank you, @dezso, for your remark.


Function extract() from date returns double precision value

Because the return values can be large or a decimal number.
Double precision will accept a wide range of values

Looking at other numeric types, you only have decimal which will have overhead:  you don't know the return scale or precision needed beforehand so it would have to wide

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.12

See PG docs


MySQL: Point in Time query using InnoDB log sequence number?

it is no similar mechanism to retrieve record state in the past from inside MySQL using LSN.

based on business idea, You can save timestamped state of important data by trigger or procedure.


Rankings based on "versus" style matchups?

This will always have the problem where A beats B, B beats C and C beats A. I don't know of any deterministic ways to define who ranks top in this case. Rock, paper, scissors and similar games exist because of this.

You're likely to get some form of circular dependency like this for any real sport, particularly when looking at a small number of games. There may be some algorithm to resolve this, but I'm not aware of it.

Assuming there is some algorithm to do what you'd like, changing your data model slightly will make it easier to do:

RESULTS (team_a_id, team_b_id, team_a_wins, team_b_wins, ties);

This will reduce the number of records you have to inspect as there will only be one entry for each pair of teams, instead one for each game for each pair of teams.


Effective way to migrate from SQL Server to MySQL

If you're looking for an easy-to-use, open-sourced tool, checkout etlalchemy.

You can carry out your SQL Server to MySQL database migration with 4 lines of Python code:

To Install:

pip install etlalchemy

(On El Capitan you may have to run pip install --ignore-installed etlalchemy)

To Run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

mssql_db_source = ETLAlchemySource("mssql+pyodbc://username:password@DSN_NAME")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name",
              drop_database=True)

mysql_db_target.addSource(mssql_db_source)
mysql_db_target.migrate()

If you want to learn more about etlalchemy, check out this article.


More Databases Per Sql Server 2005 Instance

It's going to be a challenge to apply a uniform policy across such a varied estate. My first port of call would be to understand the biggest consumers of key resources (CPU, memory, IO) on each instance, with a view to isolating the problematic databases from the others. Glenn Berrys healthcheck DMVs would be a good starting point.

Who's using most of the memory (buffer pool) on each instance?

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

How long are pages staying in the buffer pool?

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

Who's generating most IO?

SELECT DB_NAME(DB_ID()) AS [Database Name],[file_id], num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms,
CAST(100. * io_stall_read_ms/(io_stall_read_ms + io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
CAST(100. * io_stall_write_ms/(io_stall_write_ms + io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
(num_of_reads + num_of_writes) AS [Writes + Reads], num_of_bytes_read, num_of_bytes_written,
CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],
CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) OPTION (RECOMPILE);

Which databases are consuming the most CPU and which particular procedures?

SELECT
    DB_NAME(qt.dbid) AS DatabaseName
  , SUM(qs.total_worker_time) AS [TotalWorkerTime]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    DB_NAME(qt.dbid) IS NOT NULL
GROUP BY
    qt.dbid
ORDER BY
    SUM(qs.total_worker_time) DESC
OPTION
    (RECOMPILE) ;

SELECT TOP (1000)
    DB_NAME(qt.dbid) AS DatabaseName
  , qt.[text] AS [SP Name]
  , qs.total_worker_time AS [TotalWorkerTime]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    DB_NAME(qt.dbid) IS NOT NULL
ORDER BY
    qs.total_worker_time DESC
OPTION
    (RECOMPILE) ;


Get the max value from a slice of a JSON Array?

You can unnest json array:

Postgres WITH ORDINALITY:

  When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

Have a look at this answer of Erwin Brandstetter:

PostgreSQL unnest() with element number

  SELECT
     r."ID",
     MAX(t.elem::int) MaxElem
  FROM
      raw r
  JOIN
      features f
      ON f."ID" = r."ID"
  JOIN LATERAL
      json_array_elements_text(r."Data")
      WITH ORDINALITY AS t(elem, n) ON TRUE
  WHERE
      n >= (f."Features"->>'Start')::int + 1
      AND
      n <= (f."Features"->>'End')::int + 1
  GROUP BY
      r."ID";

ID | maxelem
-: | ------:
 1 |       7

db<>fiddle here

Or if you prefer to use intarray module:

SELECT
    r."ID",
    (sort_desc(((ARRAY(SELECT json_array_elements_text(r."Data")))::int[])[(f."Features"->>'Start')::int + 1:(f."Features"->>'End')::int + 1]))[1]
FROM
    raw r
JOIN
    features f
    ON f."ID" = r."ID";

rextester here


Use of integer instead of interval (of one type)

Perfectly ok - especially since you can add and subtract integer to / from  date directly in Postgres.

However, int2 might not be better than a plain integer. True, integer occupies 4 bytes instead of 2, but many operations are optimized for integer. Among other things, integer is the default numeric type for numbers without decimal point. For int2 you often need to add an explicit cast to avoid errors.

Also, while we are talking about a single column, you will most probably gain nothing in regard to disk storage or RAM. Some closer understanding of storage mechanisms is needed here, in particular padding and data alignment.

If you have several int2 columns in a table  (or even "many" as you mention) and / or know what you are doing, the odds for int2 get better.

Details:
Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

If in doubt, run a test and measure:
Measure the size of a PostgreSQL table row


convert date to timestamp and store into table using trigger mysql

@@global.time_zone variable

To see what value they are set to

SELECT @@global.time_zone;

To set a value for it use either one:

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';

@@session.time_zone variable

SELECT @@session.time_zone;

To set it use either one:

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

both "@@global.time_zone variable" and "@@session.time_zone variable" might return "SYSTEM" which means that they use the timezone set in "my.cnf".

For timezone names to work (even for default-time-zone) you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Note: you can not do this as it will return NULL:

SELECT
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime`
FROM `table_name`


Checking if the query is made async or sync on SQL Server 2008 R2

As far as I can tell, SQL Server doesn't know a query is asynchronous. It's always synchronous to SQL Server. The connection string change appears to affect the client only.

sys.dm_exec_connections etc don't have anything to show whether a query is asynchronous or not


First, from looking at your existing indexes and comparing them to your example query, you are missing an index on just accountid. The way MySQL handles indexes is left-most, meaning you can have a composite index like this:

KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE,

and run a query that looks for contactid and the contactaccounts index would be a potential index*. However doing a query on accountid will not utilize the index, because accountid is not the left-most column.

If you never search for contactid without an accountid, I would create the index like this:

DROP INDEX `contactaccounts` ON `tbl_sessions_2012`;
CREATE INDEX `accountscontact` ON `tbl_sessions_2012` (`accountid`,`contactid`,`journalcode`);

Now, analyzing your other indexes, the first of each set is redundant using the left-most rule, and can be dropped in favor of the second:

KEY `contactaccounts` (`contactid`,`accountid`,`journalcode`) USING BTREE
KEY `contactaccountloginmonths` (`contactid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `contactaccountcollections` (`contactid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `contactaccountcollectionloginmonths` (`contactid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `organisationaccounts` (`organisationid`,`accountid`) USING BTREE
KEY `organisationaccountloginmonths` (`organisationid`,`accountid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

KEY `organisationaccountcollection` (`organisationid`,`accountcollectionid`,`journalcode`) USING BTREE
KEY `organisationaccountcollectionsloginmonths` (`organisationid`,`accountcollectionid`,`journalcode`,`logintypeid`,`actionTime`) USING BTREE

By having the duplicates you are using up a lot of space on indexes.

* I say potential because there is still the possibility that your index will not be used due to the amount of rows MySQL has to scan through. As your account_id IN () statement grows, MySQL will determine that it's faster just to do a full table scan regardless of the index.


How to use mysql to migrate database changes from local databases?

Database Migration tools

There are tools to help you manage the process of updating the structure of your database as your app evolves. ”Database migration" tools seems to be the emerging technical term for this, though imprecise/ambiguous.

Examples include Flyway (my favorite) and Liquibase. By the way, Flyway is Java-based but can be used by non-Java programmers via command-line utilities that wrap the Java classes.

The idea is that you must be disciplined in making no ad-hoc changes at all. Every change to the database structure must be written in a script to be formally managed by the migration tool.

You can run the tool wherever makes sense to you. If you have a single monolithic app, you could run the migration tool every time the app launches. Or you could call the tool from the command-line, or from a hook in your build tool or deployment tool.

The tool automatically detects which scripts have been applied already and which need to be applied. So you are accumulating scripts over time, never deleting them. Great for testing too, as you can recreate the database and take it to any point in the history of your project.

The scripts may be simply SQL code, as is the case with Flyway, plus optional Java classes you can write for complicated cases beyond the capability of SQL. Liquibase uses XML-based scripts.

These tools are best used from the start of a project but you can start with a 'baseline'. Personally, I suggest rebuilding your database from scratch using the tool to recreate the database entirely from your new organized scripts.

Consistent use of a database migration tool eliminates your questions. For example, ”What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors?“, your migration scripts would alter the table to drop or modify the column. You know the column exists because you know the migration tool ran earlier scripts that already added the column.


It's SQL Server that's consuming all of this memory.  You aren't seeing the memory it's consuming because SQL Server is probably using "locked pages" which are not part of the server's working set, which is often all you'll see in Task Manager.

There's a detailed description on why this is on one of the MSDN Blogs.


Create a user on database A with a procedure from database B (with EXECUTE AS)

Since LoginA is not member of sysadmin server role, the login must need IMPERSONATE permissions to use EXECUTE AS i.e.

USE dbB;
CREATE USER LoginA for login LoginA; -- This will create user under "public" database role
GRANT IMPERSONATE ON USER::LoginB to LoginA; -- This is required to use "EXECUTE AS"

Once LoginA got IMPERSONATE access, you can create procedure as follows in dbA where LoginA got full access (db_owner)

use dbA
go

CREATE PROCEDURE [UserAfterRestore]
    @TargetDBName varchar(128),
    @GranteeLogin varchar(128),
    @ExecuteAs varchar(128)
AS
BEGIN
    SET NOCOUNT ON;
    Declare @TSQL varchar(2000);

    SELECT @TSQL = 'USE [' + @DBName + '];
                    Execute as user = '''+ @ExecuteAs +''';
                    IF NOT EXISTS (SELECT name FROM SYS.database_principals WHERE name = '''+ @GranteeLogin +''')
                        BEGIN
                            CREATE USER [' + @GranteeLogin + '] FOR LOGIN ['+ @GranteeLogin + '];
                        END
                        ALTER ROLE [db_datareader] ADD MEMBER ['+ @GranteeLogin +'];
                        ALTER ROLE [db_datawriter] ADD MEMBER ['+ @GranteeLogin +'];'
    Print 'Executing: ' + @TSQL

    EXEC (@TSQL);
END
GO

Call procedure as follows:

exec dbA.dbo.[UserAfterRestore]
        @TargetDBName = dbB,
        @GranteeLogin = 'LoginA',
        @ExecuteAs = 'LoginB';

If the target is multiple databases, then LoginA must be either member of sysadmin server role or must be user of all databases (under public role) and GRANTED for IMPERSONATE on respective database's db_owner


Archiving data from logging db (high writes, no reads)

The problem is that these deletes are now taking over 10 hours to run.

if you are not on Enterprise edition - as Partitioning - switching in and out is only supported in Enterprise edition, I would suggest you to create and ordered view on the top of table that you are deleting data from. This will incur less I/O and generate minimal T-Log.

Founded the link written by SQL CAT team about  Fast ordered delete technique of using a view or a CTE.

e.g.

-- tested to delete 27 million and it took 24 mins to delete

CREATE VIEW vw_TABLE_TO_DELETE
as
select top (100000) * from TABLE_TO_DELETE
where CONDITION
ORDER BY LogTime

----  Caution : perform actual deletes now !!
--- start an Explicit transaction to avoid Log flushes.

BEGIN TRANSACTION

DELETE
FROM vw_TABLE_TO_DELETE

---- if FULL Recovery then do T-log backup
---- if SIMPLE Recovery then perform a CHECKPOINT

COMMIT TRANSACTION  --- in case if we want to cancel in between, the deletes are committed in the log already

  Should the logging db be in snapshot isolation mode?

You have to test it out depending on your workload. Read committed Snapshot isolation (RCSI) has a tempdb overhead as it has to do a lot of row-versoning. The Database engine will store a version of the previously committed image of the row in tempdb.

ON a highlevel - RCSI - selects do not lock data during a read operation and Read transactions do not block write transactions and vice versa.


In order for SQL Server services to start as part of a Windows Failover Clustering Service, quorum needs to be attained by at least one node in the cluster.  If a two-node cluster has one node down, that node cannot contribute to the quorum, so some other method is necessary for the node that is up to attain quorum.  Quorum is essentially a set of votes to see which node gets to run SQL Server.  Without quorum, no node can run the service, which is the symptoms you are seeing.
The MSDN page on Failover Clustering quorums states:

Why configure the quorum?
To increase the high availability of the cluster, and the roles that are hosted on that cluster, it is important to set the cluster quorum configuration appropriately.
The cluster quorum configuration has a direct effect on the high availability of the cluster, for the following reasons:

It helps ensure that the failover cluster can start properly or continue running when the active cluster membership changes. Membership changes can occur because of planned or unplanned node shutdown, or when there are disruptions in connectivity between the nodes or with cluster storage.

When a subset of nodes cannot communicate with another subset of nodes (a split cluster), the cluster quorum configuration helps ensure that only one of the subsets continues running as a cluster. The subsets that do not have enough quorum votes will stop running as a cluster. The subset that has the majority of quorum votes can continue to host clustered roles. This helps avoid partitioning the cluster, so that the same application is not hosted in more than one partition.

Configuring a witness vote helps the cluster sustain one extra node down in certain configurations. For more information about configuring a quorum witness, see Witness configuration later in this topic.

Be aware that the full function of a cluster depends on quorum in addition to the following factors:

Network connectivity between cluster nodes

The capacity of each node to host the clustered roles that get placed on that node

The priority settings that are configured for the clustered roles

For example, a cluster that has five nodes can have quorum after two nodes fail. However, each remaining node would serve clients only if it had enough capacity to support the clustered roles that failed over to it and if the role settings prioritized the most important workloads.

You need to configure the failover cluster with one of the following options:

Node majority (no witness) - Only nodes have votes. No quorum witness is configured. The cluster quorum is the majority of voting nodes in the active cluster membership.

Node majority with witness (disk or file share) - Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes in the active cluster membership plus a witness vote.  A quorum witness can be a designated disk witness or a designated file share witness.

No majority (disk witness only) - No nodes have votes. Only a disk witness has a vote. The cluster quorum is determined by the state of the disk witness.  The cluster has quorum if one node is available and communicating with a specific disk in the cluster storage. Generally, this mode is not recommended, and it should not be selected because it creates a single point of failure for the cluster.


How many users and objects can be in an Oracle Database?

All Oracle Documentation can be found on-line
https://docs.oracle.com/en/database/oracle/oracle-database/index.html
It is highly recommended that you read the "Concepts Guide".  If you understand just a tiny bit, it will make you a much better developer/DBA.
For your specific answer, you need to look in the "Database Reference" manual section "Database Logical Limits".

Users and Roles  |  maximum   |   2,147,483,638
Tables | maximum per database | unlimited

App Design Notes
Web Users are normally stored in a table.
In Oracle :  Users and Schemas are almost synonymous.  This makes the requirement "create an Oracle user for each and every Web User" a very expensive task.... And a dangerous one at that.  (The last thing you want is L33TH4CKR to have his/her own account on your database)


MongoDB secondary node does not become primary when other nodes are stopped

This is by design. If you want to run with only one node, restart that node without replica set switch.

  If a majority of the replica set is inaccessible or unavailable to the
  current primary, the primary will step down and become a secondary.
  The replica set cannot accept writes after this occurs, but remaining
  members can continue to serve read queries if such queries are
  configured to run on secondaries.

Read these resources for more details:

Replica Set Elections
mongo replica set to single server
Voting in MongoDB


I posted it in the MySQL bug database and got an answer:

Adding log-error = /var/log/mysql/mysqld.log in the [mysqld] section in the file my.cnf creates mysql.log in the directory I want to have.

However, I still don't understand why it remains empty when the above setting isn't mentioned.


Can an entity in an entity-relationship diagram have more than one relationship?

It means that time is an essential component of the relationship.  It's telling you that there's a functional dependency (employee, time) -> project.

In an entity-relationship diagram, I might model it like so:

I made it a weak entity dependent on Employee and with time as a weak key, but it's still a relationship between Employee and Project, hence a weak associative entity.


Change the join condition to:

ON tb_user_messages_recap.Auteur = tb_user.Pseudo OR
   tb_user_messages_recap.Intervenant= tb_user.Pseudo

But really why not:

SELECT NIC_Handle
FROM tb_user
WHERE Pseudo =$_SESSION_PSEUDO

I could be wrong. Question is a bit ambiguous.


How can I disable mysql_native_password and mysql_old_password on MariaDB / MySQL?

You are suppose to run this command from server command line not from MariaDB console.
mysql_plugin disabled_plugins DISABLE
And then login to MariaDB & check :
 SELECT * FROM mysql.plugin;


Query optimization ndbcluster + order by

Right, I've found the answer to my own question, and thought I'd post it here in case anyone else would ever have the same basic question:

Given that a cluster shard the data across numerous nodes, there is no real guarantee that an AUTO_INCREMENT primary key will reflect the order in which records were added. Especially when dealing with multiple master replication.
Details can be found here

In essence, the answer to my question boils down to this:
Each node is given a range of available increment-values for the next N inserts. However, you have no real say in which node will insert the next row, and thus you can't be absolutely sure that the next row to be inserted will receive the MAX(pk_id_val) + 1 as a PK value.
A simple diagram to clarify:

 +------------+      +------------+
 |   Node 1   | <==> |   Node 2   |
 +------------+      +------------+
 |   PK 1-3   |      |   PK 4-6   | (Range of available increment vals)
 +------------+      +------------+
 INSERT INTO cluster_tbl (field) VALUES ('test');
 INSERT INTO cluster_tbl (field) VALUES ('test');
 INSERT INTO cluster_tbl (field) VALUES ('test');

If all three insert queries are sent to Node 1, then all is fine and dandy, and the PK's will be 1, 2 and 3 respectively. However, if the first query is sent to node 1, the second to Node 2, and the third back to node 1 again, then the PK's will be 1, 4 and 2 respectively.
In which chase a SELECT * FROM cluster_tbl ORDER BY pk_field DESC LIMIT 0,1; query will serve up the second row that was inserted, and not the third.

A possible solution: create an index on an inserted field, defined as follows:

`insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

And order the query by descending timestamp values:

SELECT *
FROM cluster_tbl
ORDER BY insert DESC
LIMIT 0,1;

That, for me at least, did the trick


SQL Server: specifying a unique optional column

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Yes, the following works (only in SQL Server 2016 onwards):

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) NULL
        INDEX uq_employees_tfn
        UNIQUE (tfn)
        WHERE tfn IS NOT NULL
);

The inline syntax results in the same unique filtered index as for the separate CREATE UNIQUE INDEX. Subsequent DDL will need to manage it like an index rather than a constraint.

It is not brilliantly clear in the CREATE TABLE documentation, but is illustrated in Example S.


How do I query only for committed data while avoid a deadlock

Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event.

How To Track Down Deadlocks in SQL Server using Profiler

Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.


MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls

To prevent this insert from occurring, turn on strict mode.

mysql> set session sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO invite (`email`) VALUES ('e@mail.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> select * from invite;
Empty set (0.00 sec)

Note: Be sure to follow the description in the link I provided, NOT copy my command regarding setting sql_mode. Otherwise you may disable other settings.


What "tool" to use for monitoring instances and where to store this data?

sql sentry
Quest spotlight for sql server enterprise
Microsoft Operations Manager
Various RedGate tools
Your own scripts, data collectors and jobs
Windows Performance monitor


Efficient way to query set of rows from MySQL

First, try putting together the ids in a query (a tableless query)

SELECT 1 id UNION
SELECT 2    UNION
SELECT 3    UNION
SELECT 4    UNION
SELECT 5    UNION
SELECT 6    UNION
SELECT 7;

Watch it work...

mysql> SELECT 1 id UNION
    -> SELECT 2    UNION
    -> SELECT 3    UNION
    -> SELECT 4    UNION
    -> SELECT 5    UNION
    -> SELECT 6    UNION
    -> SELECT 7;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

mysql>

Now, JOIN that tableless query to the data table

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B ON A.id = B.id
;

or

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B USING (id)
;

Give it a Try !!!


Counting distinct entries in a column using relational algebra

Besides the more compact syntax (from @McNets' answer):

select   User,
         count(distinct Hotel_Reviewed) HotelsReviewed
from     InitialRelation
group by User;

we can also do a projection first to find distinct User, Hotel_Reviewed pairs and then aggregate:

select   User,
         count(Hotel_Reviewed) as Hotels_Reviewed
from
    ( select distinct
               User,
               Hotel_Reviewed
      from     InitialRelation
    ) as D
group by User ;

This leads us to the relational algebra notation:

R = Ɣ User, COUNT(Hotel_Reviewed) -> Hotels_Reviewed
        (π User, Hotel_Reviewed (InitialRelation)) -> D


The answer is simple and short. Yes, that's the right way to do it.


postgres automatic table list partitioning

I haven't heard of any plans to add such a feature.

You can easily create a BEFORE INSERT trigger that creates partitions on demand, but of course that trigger has to run for each row inserted, which means a certain performance impact.

Using a default partition like you suggest is a solution with better performance, but it requires later explicitly moving the data to a different partition, which can be painful.

If you expect new countries to pop up all the time, maybe the trigger is the way to go. If it happens once in a blue moon, I might prefer the second way.


"Diamond Shaped Relationship" What's the formal term?

You'd have to add the crows feet (or whatever notation you prefer) for us to properly understand the diagram. I choose to interpret it as directed top-to-bottom.

    top table
     |     |
     ^     ^
   Left   Right
      |    |
      ^    ^
      Bottom

Fig: Diamond pattern

I do not recall ever seeing this pattern explicitly named. "Diamond" seems as good as any.

As such I don't believe it is a circular reference. For that you'd have to be able to follow the "many" back to the origin:

  First ---< Second
    v           |
    +-- Third >-+

Fig: Circular reference

I question the premise that the diamond pattern is a bad design. For certain applications it may be entirely appropriate. Perhaps it's just not appropriate for yours.

As a toy example consider a small multi-tenant app to track hot-desking. We have entity types Company, Employee, Desk and DeskAllocation. A Company hires many Employees. The Company provisions many desks. Each employee can sit at a different desk each day. Naturally an employee may only sit at a desk provisioned by their employer.

We can identify the company by it's business registration number or a made-up number we supply. It doesn't matter. We'll call this CompanyId.

Companies create employee IDs for their hires. Since every company has an employee who wants to be hire #1 we have to have a composite key of (CompanyId, EmployeeId).

Each company has its own labeling scheme for desks but most of them use a simple numerical sequence. So the PK of Desk is the composite (CompanyId, DeskId).

So what's the PK of DeskAllocation? It will be the PK of Employee, that of Desk and a date column. Those first two are themselves composite, each containing CompanyId. That may seem redundant, or even an error, but is integral to the enforcement of the "sit at your employer's desk" rule. That can be implemented as a check constraint.

The pseudo code for DeskAllocation will be

table DeskAllocation
(
  DeskCompanyId,
  DeskId,
  EmployeeCompanyId,
  EmployeeId,
  AllocationDate

  primary key
  (
    DeskCompanyId,
    DeskId,
    EmployeeCompanyId,
    EmployeeId,
    AllocationDate
  )

  foreign key FK_Desk
  (DeskCompanyId, DeskId) references Desk(CompanyId, DeskId),

  foreign key FK_Employee
  (EmployeeCompanyId, EmployeeId) references Employee(CompanyId, EmployeeId)

  constraint CHECK DeskCompanyId = EmployeeCompanyId
)

This is may seem artificial but I have implemented exactly this pattern in an application for automotive pricing.


Some options are:

Create a backup and restore as a different name (if on same server):
https://www.nucleustechnologies.com/blog/restore-sql-database-with-a-different-name/

Script a database by using the Generate Scripts option using Types of data to script set to Schema and Data;

Use the Import and Export Data with the SQL Server Import and Export Wizard.


Recovering from: A transport-level error has occurred when sending the request to the server in SSMS

In 2005 at least, hit F5 to re-run your query. SSMS will determine it is no longer connected and then prompt you to reconnect. If I recall correctly, 2005 will put you back into the same database while 2008 puts you into your default catalog. A second execution of F5 will actually run the command. I've never had a semaphore timeout but it works for other disconnect scenarios.


For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense.

You cannot nest a data-modifying CTE like you tried (as you already found out the hard way) - and you don't need to. This query would achieve your objective:

WITH e AS (
   SELECT name, employee_id
   FROM   employee_details
   WHERE  name = 'jack bauer'
   )
 , i1 AS (
   INSERT INTO employee             -- no target columns!
   SELECT                           -- empty SELECT list!
   WHERE NOT EXISTS (SELECT FROM e)
   RETURNING id
   )
 , i2 AS (
   INSERT INTO employee_details (name, employee_id)
   SELECT 'jack bauer', id
   FROM   i1
   RETURNING name, employee_id
   )
SELECT employee_id, name FROM e
UNION ALL
SELECT employee_id, name FROM i2;

The core feature is the INSERT with no target columns and an empty SELECT. Postgres fills all columns not listed in the SELECT with default values. This way we can replace the unconditional VALUES (default) with a conditional INSERT. The CTE i1 only inserts a row if the given name was not found.

The manual:

  If no list of [target] column names is given at all, the default is all the
  columns of the table in their declared order; [...]

  Each column not present in the explicit or implicit column list will
  be filled with a default value, either its declared default value or
  null if there is none.

This is a Postgres specific extension of the standard:

  Also, the case in which a column name list is omitted, but not all the
  columns are filled from the VALUES clause or query, is disallowed by the standard.

The final CTE i2 only inserts a row if i1 returned a row. Voilá.

This is subject to race conditions under concurrent write load to the same tables. If you need to rule that out, you need to do more. Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?

Without the complications from the conditional INSERT in the 2nd table, this would boil down to a common case of SELECT or INSERT:

Is SELECT or INSERT in a function prone to race conditions?

Aside

"id" text DEFAULT gen_random_uuid()

I'd strongly advise to use the data type uuid to store UUIDs.

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
What is the optimal data type for an MD5 field?


Dynamic Data Masking Issue when Concatenating Fields

Documentation is shamefully silent in regard to what is the behavior when masked column is the part of an expression.
This is how masked column is represented in execution plan:
Scalar Operator(DataMask([LastName],0x05000000,(3),(2),'XXXX',(2)))

here (3) denotes masking function type and correspond to partial masking.
And this is how LastName + ', ' + FirstName expression is represented:
Scalar Operator(DataMask([LastName]+', '+[FirstName],0x05000000,(1),(0),(0),(0)))

As you may see, the behavior is "compute, then mask". Masking function type in this case is (1), which correspond to default masking. This is the result of masking function adjustment and intentional modification.
With the "compute, then mask" approach, this (mask adjustment) is the necessary measure apparently, because of otherwise simple expressions involving LEFT, RIGHT or SUBSTRING functions could unmask data easily. And determining whether expression is "safe" or not would be too complex probably.
I can only guess why approach is "compute, then mask" and not "mask, then compute", but I think that the latter, being implemented, would suffer from its own problems. Some, that I can think of, include possibility of arithmetic errors for numeric types (such as division by zero, for example, if something is divided to masked column), or logic skew possibility (if there is a conditional expression that depends on masked column).


Schema Migration for application upgrades

Edition-Based Redefinition is perfect for this. You define schema changes in a new edition, along with procedures that are used to migrate the data between the base and new edition/schema. Switching between the two is seamless and can be done with zero downtime.

There's a good demo on the Oracle Base blog.


Importing SQL Server database from a .sql file

Create an empty database or if you have one already target that one.

Open CMD with elevated privilege and run:

sqlcmd -S SERVERNAME -d MYDATABASE -U USERNAME -P PASSWORD -i C:\path\mysqlfile.sql -o C:\path\results.txt

basically:

-S: is your servername or localhost
-d: is the database you are targeting
-U: is the username
-P: is the password
-i: is the path to your .sql file
-o: is where the logs file will be saved so if you had problem during the importation you can debug them


How to merge overlapping time intervals if the interval not more than 4 minutes in PostgreSql

As I mentioned in my comment, your request is not well defined because it is unclear how the 4-minute wide time windows are supposed to be chosen.
In addition, the nature and importance of the id column is unclear.
Be that as it may, I came up with the following query. It uses window functions in order to define the group edges, and then filters out any row that is not an edge:
WITH t AS (
    SELECT id, action, date
         , min(date) OVER (PARTITION BY round(extract(epoch FROM date)/240)) min_date
         , max(date) OVER (PARTITION BY round(extract(epoch FROM date)/240)) max_date
    FROM tab
)
SELECT id, action, date
FROM t
WHERE date = min_date OR date = max_date;


Who is using my worker threads? SQL Server 2014 - HADR

Community wiki answer:

You have a high number of databases in availability groups, that's going to be where your threads are going. There is a lot involved in the compression, encryption, and transport cost. Try turning off compression, it will reduce your thread usage by a about a third (depending on the replica count).

The question is tagged SQL Server 2014, which will by default use compression. SQL Server 2016, by default, will not use compression for sync.

You may need to increase the worker threads on the instance, or better: balance out the most active ones and inactive ones on multiple servers. See the related Q & A AlwaysON Availability group query very slow.

You may also find it is an application that is unable to close requests properly. This can result in lot of sleeping sessions lying around (which consume workers).

The number of threads actually used depends on how active the databases are. You could have 1,000 databases and, if most are idle 95% of the time, you won't have any issues. It seems that your databases have become active more often and have eaten more of your threads. That's the long and short of it.


For those kind of tasks, you should use the tools you are most comfortable with.

A DB would of course run a scheduled database script or use SSIS. A C# dev creates a small C# tool and a sysadmin creates a PowerShell script.


Update trigger issue throwing errors on IF statements

This logic seems wrong to me, but I think you have it way more complicated than it needs to be:

ALTER TRIGGER [dbo].[UpdateTransport_TEST1]
   ON  [dbo].[MVF_Transport_Register]
   AFTER UPDATE
AS
BEGIN
  UPDATE c
    SET c.Record_Status = i.Record_status
    FROM dbo.MVF_Transport_Acc AS c
    INNER JOIN inserted AS i
    ON c.Order_No = i.Order_No
    AND c.Record_Status <> i.Record_Status
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price > 0;

  IF @@ROWCOUNT = 0
  BEGIN
    INSERT dbo.MVF_Transport_Acc
    ([Order_No], [Record_Status], [Notes], [Transport_Supplier],
     [Surcharge], [Vendor_No], [Pallets], [Amend_Pallets],[Price],
     [Input_Date], [Amend_Price], [Transport_Invoice_No],[Transport_Job_No],
     [Amend_Date], [Report_Price], [Return_Price], [Report_Date])
    SELECT i.[Order_No], i.[Record_Status], i.[Notes], i.[Transport_Supplier],
           i.[Surcharge], i.[Vendor_No], i.[Pallets], i.[Amend_Pallets],
           i.[Price], i.[Input_Date], i.[Amend_Price], i.[Transport_Invoice_No],
           i.[Transport_Job_No], i.[Amend_Date], i.[Report_Price],
           i.[Return_Price], i.[Report_Date]
    FROM inserted AS i
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price > 0;
  END
END
GO

I still feel like that logic is incomplete for some reason.


This solution could be of help. For scheduling, you'll have to do what is outlined here. You can connect to an Express instance using MySQL WorkBench as outlined here, but you can't schedule jobs.

Also, have you considered using ODBC to connect and send the data to another (i.e. MySQL) server. You can schedule many of these systems also!

Or try JDBC using SQuirreL SQL or DBeaver? Personally, I can recommend SQuirreL SQL! If you want to schedule a job using Java, try the solution here! Just be aware that your dumps may not be internally consistent unless you are careful about your transactions!


Reporting Services in SharePoint Integrated Mode with SSPI/Windows Authentication Architecture

Reporting Services in SharePoint Integrated Mode  can only be installed on a SharePoint Server. That is, you can only have the Reporting Services on the same server because Report Server database is no longer utilized when you install in SHP Integrated mode. Instead, SharePoint databases are used, there are a number of sharepoint databases used by SHP and one of them will host your meta data and report definitions. This makes centrally managing reports possible. So there is only one answer to your question, Server 2 or Sharepoint server.


The error you're seeing in the SQL Server Error Log is this one:

  Recovery of database 'CrashTestDummy' (9) is 0% complete (approximately 42 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required

More generically, it will say:

  Recovery of database '{Database Name}' ({Database ID}) is {N}% complete (approximately {N} seconds remain). Phase {N} of 3. This is an informational message only. No user action is required

Because your database was not shut down cleanly on restart, the database must go through "crash recovery". This is required to ensure the database remains consistent. When the database is not shut down cleanly, SQL Server must make sure that transactions written to the transaction log have been reconciled properly against the data files.

All transactions are guaranteed to be written to the transaction log. However, updating data is initially only done in memory. Updates to the physical data files are done asynchronously via a checkpoint. The asynchronous nature of the data file updates are why a crash or unclean shutdown requires extra work on startup.

As indicated by the error message, there are three phases to the recovery. Each of these is essentially a pass through the transaction log:

Analysis
Redo / Roll Forward
Undo / Rollback

Analysis

This phase is simply to review the transaction log & determine what needs to be done. It will identify when the most recent checkpoint was, and what transactions might need to be rolled forward or back to ensure consistency.

Redo / Roll Forward

Completed transactions from the transaction log need to be reviewed to ensure the data file has been completed updated. Without this, changes that were only in-memory might have been lost.

This phase will take those transactions that were committed after the most recent checkpoint and redo them, to ensure they are persisted to the data file.

If you are using SQL Server Enterprise edition, Fast Recovery will allow the database to come online & be available after this phase of recovery. If you are not using Enterprise Edition, the database will not be available until after the Undo phase has completed.

Undo / Rollback

Transactions from the transaction log that were rolled back, or were uncommitted at the time of "crash" must be rolled back. SQL Server must verify that if uncommitted changes were made to the data file, they are undone. Without this, a rolled back change could be partially committed, violating the ACID principles of the database.

This phase will perform the rollback of any transactions that were uncommitted at the time of crash, or were rolled back after the final checkpoint.

So what can you do about it?

While the database is in recovery, attempts to bring the database online via a RESTORE command like this will fail:

RESTORE DATABASE CrashTestDummy WITH RECOVERY;

SQL Server is already attempting to do this. The RESTORE...WITH RECOVERY; will simply put the database through the exact same steps in order to bring the database online in a consistent manner.

Be patient

The right thing to do is just be patient. This part of the message from the error log is the one that you should pay attention to:

  No user action is required

Note, too, that the time remaining is an estimate. In my experience, it is wildly inaccurate. Sometimes the time remaining will grow larger, rather than reducing. Sometimes it will report a very long completion time, and suddenly complete very fast. Its just an estimate.

Can you just "throw away" your transaction log and start fresh?

I advise against it. I would suggest you never, ever do this with a production database. There is a procedure to attach a database without a transaction log, and ask SQL Server to ATTACH_REBUILD_LOG. I won't detail all the steps, but the "punchline" for that procedure is to do this:

CREATE DATABASE CrashTestDummy
ON (FILENAME = 'C:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\CrashTestDummy.mdf')
FOR ATTACH_REBUILD_LOG;

Running this on a crashed database may result in this error:

  The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
  Msg 1813, Level 16, State 2, Line 5
  Could not open new database 'CrashTestDummy'. CREATE DATABASE is aborted.

In which case, you're stuck. You'll need to use the original transaction log & be patient. Just wait for it to recover.


How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

In PostgreSQL at least, multi-column indexes are usable for single-column searches on the left column, but not for single-column searches on the right column.

So: an index on (a,b,c) can be used for searches on a, a,b and a,b,c but not for searches on b,c or c.

If search patterns make individually usable indexes on all columns necessary and if the column values are small, people often create indexes a,b,c, b,c and c.

An alternative can be individual indexes on a, b, and c. PostgreSQL can use a bitmap index scan in many circumstances to combine the indexes, so a search with conditions on b and c will use both the indexes on b and c.

Rather than guess, the best thing to do is use EXPLAIN ANALYZE or the auto_explain module to examine the query plans and run-time stats from your queries, so you can see what's actually happening.


Invalid column name even after checking if it exists so it should skip the query part

This is a parse time error, not a runtime error. You can't use an IF check to determine if a column reference will be ok to use, because the column reference is validated first, long before the IF ever runs (this is why IF 1 = 0 also fails).

  Note: The script in the question will appear to work (e.g. it will print end) if you copy the script to your own system where these objects do not exist. Due to deferred name resolution, the parse check will succeed if any of the objects do not exist yet, but will fail if all objects do exist, but any column does not.

As proof, compare:

IF 1 = 0
BEGIN
  SELECT 1 FROM sys.columns AS c
    INNER JOIN sys.objects AS o
    ON c.blat = o.splunge;
END
GO

/*
  Msg 207, Level 16, State 1
  Invalid column name 'blat'.
  Msg 207, Level 16, State 1
  Invalid column name 'splunge'.
*/

IF 1 = 0
BEGIN
  SELECT 1 FROM dbo.blats AS b
    INNER JOIN dbo.splunges AS s
    ON b.blat = s.splunge;
END
GO

/*
  "success"
*/

Now create a table called dbo.blats but without a column named blat:

CREATE TABLE dbo.blats(id int);

The second script still succeeds, because there is still an object that doesn't yet exist, and SQL Server is giving you the benefit of the doubt - at least at parse time - that you'll create it before you execute the script. It will fail when you add the second table, dbo.splunges, but without the referenced column splunge:

CREATE TABLE dbo.splunges(id int);

To get around this, you can use this cumbersome workaround,  or just use dynamic SQL (and here I use sys.columns instead of INFORMATION_SCHEMA, well, because):

IF EXISTS
(
  SELECT 1
    FROM sys.columns AS c
    INNER JOIN sys.objects AS o
      ON o.[object_id] = c.[object_id]
    INNER JOIN sys.schemas AS s
      ON o.[schema_id] = s.[schema_id]
    WHERE s.name = N'schema_name'
      AND o.name = N'object_name'
      AND c.name = N'column_name'
)
BEGIN
  EXEC sys.sp_executesql N'UPDATE ... <query that references column_name> ...;';
END

Also here is why I avoid shorthand metadata helpers like OBJECT_NAME(), SCHEMA_NAME(), etc., and instead recommend creating your own helper views.


Something like:

ALTER TABLE customer
    ADD CONSTRAINT email_chk CHECK ( email like '%@%' );

should work. If email is mandatory you should make it NOT NULL as well. For a more thorough handling of the email-domain, check out the link:

What is the best way to store an email address in PostgreSQL?

that @MaxVernon posted in his comment.


Is it possible to take a database offline when doing a backup using an SQL job?

→ Is it possible to restore read-only DB in the new server?

Yes

An example:

CREATE DATABASE ReadOnlyDB;
GO

ALTER DATABASE ReadOnlyDB SET READ_ONLY;

BACKUP DATABASE ReadOnlyDB TO  disk = '\\share\readonly.Bak';

  destination server is already having a Read-Write(Online) database by
  the same name.

Not entirely sure what you mean by this, but you could restore the database with a different name if you would like to, remember to remove the read only property afterwards.

RESTORE DATABASE ReadOnlyDB2
FROM DISK = '\\share\readonly.Bak'
WITH MOVE 'ReadOnlyDB' to '\Datalocation\ReadOnlyDB2.mdf',
 MOVE 'ReadOnlyDB_log' to '\Loglocation\ReadOnlyDB_log2.ldf',
STATS = 5;
GO
ALTER DATABASE ReadOnlyDB2 SET READ_WRITE;

You could also replace the existing database with the REPLACE keyword.

  → Is it possible to restore offline DB in the new server?

No, this is not possible as sql server removes all handles from the database files and will not be able to access it while it is offline.

ALTER DATABASE ReadOnlyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
BACKUP DATABASE ReadOnlyDB TO  disk = 'C:\temp\readonlyOffline.Bak';

  Msg 942, Level 14, State 4, Line 10 Database 'ReadOnlyDB' cannot be
  opened because it is offline. Msg 3013, Level 16, State 1, Line 10
  BACKUP DATABASE is terminating abnormally.

You could look into detach / attach but backup restore is preferable.


How to change entire SQL server location from C:\ program file to another drive?

Trying to move a SQL Server installation in this manner is very dangerous, time consuming, and unsupported.  If someone did this to my server I would not sleep well.

A better approach would be to:

Take note of exactly what service pack and CU level is running, and any non-default configuration items in SQL Server Configuration Manager
Backup all system and user databases
If you need to move user database files, detach the user databases and move the files
Uninstall SQL Server
Install SQL Server with the new drive configuration
Install service packs and/or cumulative updates to get to the version noted in step 1
Restore the master and msdb databases
Apply non-default configurations in SQL Server Configuration Manager
If the user database files have been moved, attach them

Please note that these are general steps and don't account for things specific to your environment, such as shutting down application servers, etc.


Splitting a non-empty partition requires about 4x logging as normal DML.  Assuming the table and indexes are aligned, you can avoid excessive logging with large tables by creating a partitioned staging table with the original boundaries.  Then switch in the problem partition, split the empty partitions of the original function, repartition the staging table, and switch the remediated partitions back in.  Below is an example script.  You'll need to tweak this if you use a RANGE LEFT partition function and specify your actual filegroups.

--create staging partition function and scheme
--with same boundaries and filegroups as original
CREATE PARTITION FUNCTION PF_CustomerId_Staging (int)
    AS RANGE RIGHT FOR VALUES();
ALTER PARTITION FUNCTION PF_CustomerId_Staging()
    SPLIT RANGE(NULL);
DECLARE @CustomerId int = 0;
WHILE @CustomerId <= 60
BEGIN
    ALTER PARTITION FUNCTION PF_CustomerId_Staging()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
CREATE PARTITION SCHEME PS_CustomerId_Staging
    AS PARTITION PF_CustomerId_Staging ALL TO ([PRIMARY]);
GO

--create staging table with same schema
CREATE TABLE dbo.CustomerData_Staging(
      CustomerId int NOT NULL
    , CustomerDataId int IDENTITY NOT NULL
    , CustomerData int NOT NULL
    , CONSTRAINT PK_CustomerData_Staging PRIMARY KEY CLUSTERED (CustomerId, CustomerDataId)
        ON PS_CustomerId_Staging(CustomerId)
    );
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData) ON PS_CustomerId_Staging(CustomerId);
GO

--switch partition with multiple customers to staging table
ALTER TABLE dbo.CustomerData
SWITCH PARTITION $PARTITION.PF_CustomerId(60)
TO dbo.CustomerData_Staging PARTITION $PARTITION.PF_CustomerId_Staging(60);

--split original partition function (empty partitions)
DECLARE @CustomerId int = 61;
WHILE @CustomerId <= 68
BEGIN
    ALTER PARTITION SCHEME PS_CustomerId NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION PF_CustomerId()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--repartition staging table and indexes with same function/scheme
CREATE UNIQUE CLUSTERED INDEX PK_CustomerData_Staging ON dbo.CustomerData_Staging(CustomerId, CustomerDataId)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);

--switch remediated partitions back into main table
DECLARE @CustomerId int = 60;
WHILE @CustomerId <= 68
BEGIN
    ALTER TABLE dbo.CustomerData_Staging
        SWITCH PARTITION $PARTITION.PF_CustomerId(@CustomerId)
        TO dbo.CustomerData PARTITION $PARTITION.PF_CustomerId(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--drop staging objects
DROP TABLE dbo.CustomerData_Staging;
DROP PARTITION SCHEME PS_CustomerId_Staging;
DROP PARTITION FUNCTION PF_CustomerId_Staging;
GO


Mongo is NOT an RDBMS. Don't treat it like one and you'll avoid a lot of mistakes. This is why applications that require an RDBMS don't support Mongo.

MongoDB is well suited to applications where:

The working set of documents (and their indexes) can be kept in RAM.
Document sizes do not change much between creation and steady state.
The schema needs to be flexible.

It can definitely be used in large applications, with the right engineering. It depends a lot on how the application plans to use Mongo, though. With sharding to spread write loads you can get to some very high throughputs.


WITH
cte_hi AS ( SELECT DISTINCT name, hi_priority.col2, 1 priority
            FROM hi_priority
            ORDER BY col2, name LIMIT 7 ),
cte_lo AS ( SELECT DISTINCT lo_priority.name, lo_priority.col2, 2 priority
            FROM lo_priority
            LEFT JOIN cte_hi USING (name)
            WHERE cte_hi.name IS NULL
            ORDER BY col2, name LIMIT 7 ),
cte_total AS ( SELECT * FROM cte_hi
               UNION ALL
               SELECT * FROM cte_lo
               ORDER BY priority, col2 LIMIT 7 )
SELECT hi_priority.*
FROM cte_total
JOIN hi_priority USING (name)
WHERE priority = 1
UNION ALL
SELECT lo_priority.*
FROM cte_total
JOIN lo_priority USING (name)
WHERE priority = 2
ORDER BY col2, name, id

fiddle