I guess these {...} get replaced somewhere in the code with something that makes sense.

This is indeed an old script because compute statistics is not quite state of the art anymore.

Go ahead and create the index with the sql you just posted:

create index x on y(gid) tablespace IDX_TS;

And then analyze the table with dbms_stats:

begin
  DBMS_STATS.GATHER_TABLE_STATS(
       ownname          => '<owner of the table>'
      ,tabname          => 'Y'
      ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
      ,method_opt       => 'FOR ALL COLUMN SIZE AUTO'
      ,granularity      => 'AUTO'
      ,cascade          => TRUE
    );
end;
/

EDIT: "cascade => TRUE" will analyze indexes of the table as well.


String equality test fails for base64 encoded sha512 digest

With 64 bytes, the sha512 digest is large enough that encoding it in base64 generates a newline character (LF) in the result (at about 76 characters from the start).

Probably what goes wrong is that newline character not being correctly reinjected in the SELECT query, where you truncated the literal with an ellipsis (so we can't actually see it):

SELECT * FROM tabel WHERE code='K5HUaRwxsXzkAl3NaWHbBkvnYEK/...';

To inject a newline, you may use the E'...' syntax and have \n to denote that character.

SELECT * FROM tabel WHERE code= E'K5HUaR.....\n...rest of string';


Cannot get my slave - replication server to start

Steps to set up the hot standby

First, determine the mount points of the Postgresql data directories and the WAL (Write Ahead Log) directories.
For performance, the $PGDATA and pg_xlog directories should be on separate volumes.

In the examples below, they are defined as follows:

$PGDATA is the "master" cluster, located at /pgdata/9.3/data, at (for example) 192.168.10.0
The "slave" (aka "hot standby") will be (for example) located at /pgdata/9.3/data, at (for example) 192.168.10.1
The WAL staging area on the slave will be /pgdata/WAL_Archive. This is where the WAL segments are sent to from the archive_command on the master. Ideally on a separate volume from $PGDATA.

Assumptions:

The master cluster is up and running normally.
There is no slave cluster yet.
You are doing all operations as the "postgres" OS user (not root!)
ssh between both servers is working
You are using at least PostgreSQL 9.2
pg_hba.conf and postgresql.conf are located at $PGDATA/. If not, change the instructions below to match your location.
You are setting up the slave on a separate server.
If the servers are not on the same network (eg. different colocations), use the "-z" flag in the "archive_command" rsync on the master to compress before transferring.
These commands are for Linux, but with some substitution of commands should work on Windows too.

On the slave, create the staging directory to hold the master's log files

mkdir -p /pgdata/WAL_Archive
chown postgres:postgres /pgdata/WAL_Archive

On the master, edit the $PGDATA/postgresql.conf

wal_level = hot_standby
archive_mode = on
                    ## /pgdata/WAL_Archive is a staging directory on the slave
archive_command = 'rsync -W -az %p postgres@$SLAVE_IP_HERE:/pgdata/WAL_Archive/'
max_wal_senders = 5
wal_keep_segments = 5000   # If you have the room, to help the pg_basebackup
                           # not fail due to WAL segments being removed from master.
                           # For clusters will very little traffic, 100 is probably fine

On the master, create the replication role, which will be copied to the slave via pg_basebackup. Set a password for the "replication" role

psql -U postgres -d postgres -c "CREATE USER replication WITH replication ENCRYPTED PASSWORD 'changeme' LOGIN"

Modify the master $PGDATA/pg_hba.conf and enable the replication user for the IP of the slave

# TYPE DATABASE USER ADDRESS METHOD
#hostssl    replication     replication     $SLAVE_IP_HERE/32       md5
host    replication     replication     $SLAVE_IP_HERE/32       md5

Restart the master cluster to pick up the changes to the postgresql.conf. This is done as the cluster superuser.

For example:

pg_ctl -D $PGDATA restart -m fast
## The master cluster MUST be restarted before the pg_basebackup command is executed.

On the slave, from $HOME, issue the pg_basebackup command to start setting up the hot standby from the master.

## --host=IP_OF_MASTER  -> The master's IP
## --pgdata=$PGDATA     -> The slave's $PGDATA directory
## --xlog-method=stream -> Opens a second connection to the master to stream the WAL segments rather than pulling them all at the end
## --password will prompt for the replication role's password
## Without compression, "stream" gets the changes via the same method as Streaming Replication

time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432 --username=replication --password --xlog-method=stream --format=plain --progress --verbose

## Alternate version with compression, note "--xlog --gzip --format=tar"
#time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432 --username=replication --password --xlog --gzip --format=tar --progress --verbose

On the slave, after pg_basebackup has successfully completed, edit $PGDATA/postgresql.conf

hot_standby = on #off               # "on" allows queries during recovery
max_standby_archive_delay = 15min   # max delay before canceling queries,
                                    # set to hours if backups will be taken from here
max_standby_streaming_delay = 15min # max delay before canceling queries
hot_standby_feedback = on #off

On the slave, create $PGDATA/recovery.conf:

standby_mode = on

## To promote the slave to a live database, issue "touch /tmp/promote_db"
## Warning: If multiple slaves share the same /tmp/ directory,
##          then the trigger file must be named uniquely, else multiple slaves
##          could attempt to be promoted in the presence of the trigger file.
trigger_file = '/tmp/promote_db_slave'

## Host can be the master's IP or hostname
primary_conninfo = 'host=IP_OF_MASTER port=5432 user=replication password=CHANGEME'

## Log the standby WAL segments applied to a standby.log file
## TODO: Add the standby.log to a log rotator
## The paths must be explicitly defined, including the path to pg_archivecleanup
restore_command = 'cp /pgdata/WAL_Archive/%f "%p" 2>>/pgdata/9.3/data/pg_log/standby.log'

## XXX: If multiple slaves share the staging WAL directory,
##      do not use pg_archivecleanup as WAL segments could be removed
##      before being applied to other slaves.
archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /pgdata/WAL_Archive %r'

## On hot standby clusters, set to 'latest' to switch to the newest timeline in the archive
recovery_target_timeline = 'latest'

The slave should be ready to be started now. Start the cluster on the slave with whatever method works best.

pg_ctl -D $PGDATA start

The following are the commands I run when recreating a standby over and over for test. I add them to a script called /root/recreate_standby.sh. Runs as root, but that is not necessary - if you run as postgres, removed the "sudo su - postgres -c" command.

#!/bin/bash

## This script runs on the standby.
## Executed as root, else remove the "sudo - postgres -c" commands.
## Assumes you have a valid recovery.conf saved at
## $PGDATA/../recovery.conf.bkp

export PGDATA=/path/to/data/dir     ## Must be set correctly
export PGPORT=5432
export MASTER=192.168.x.x            ## IP or host entry for the master Postgresql server
export PGBIN=/usr/pgsql-9.3/bin

service postgresql-9.3 stop -m immediate

if [ $? != 0 ]; then
    service postgresql-9.3 start
    echo "Could not shut down PostgreSQL. Aborting."
    exit 1
fi

rm -rf $PGDATA

if [ $? != 0 ]; then
    echo "Could not remove the PostgreSQL $PGDATA dir. Aborting."
    exit 1
fi

## If the replication role is not set to "trust" in the master's
## pg_hba.conf file, the password will need to be passed into the command below,
## and "--no-password" will need to be removed or revised to be "--password"
su - postgres -c "$PGBIN/pg_basebackup --pgdata=$PGDATA --host=$MASTER --port=$PGPORT --username=replication --no-password --xlog-method=stream --format=plain --progress --verbose"

su - postgres -c "cp -p $PGDATA/../recovery.conf.bkp $PGDATA/recovery.conf"

service postgresql-9.3 start

su - postgres -c "$PGBIN/pg_isready -U postgres -p $PGPORT -t2"

while [ $? != 0 ]; do
  echo "Sleep 1 second, check if slave is up yet. If not, sleep again."
  sleep 1;
  su - postgres -c "$PGBIN/pg_isready -U postgres -p $PGPORT -t2"
done

su - postgres -c "$PGBIN/psql -d postgres -U postgres -qXc 'select pg_is_in_recovery() as is_pg_in_recovery'"

exit 0

For more in-depth details, current Postgresql docs:

High Availability, Load Balancing, and Replication
Recovery Configuration
Continuous Archiving and Point-in-Time-Recovery


In 2016 SP2 / 2017, you can generate an actual plan, and it will tell you what statistics were used. In older versions, you could do the same, but use trace flags as Paul White describes here. But I believe you are right, updating the statistics triggers a new plan, but updating them is not what leads to better plans, its simply the new plan that's generated the first time after the stats update. you should figure out why the plan is going wrong in the first place.

Since you mention that it is a search procedure, I'm going to guess this is a parameter sensitivity issue (seeing the procedure definition and some typical calls will help confirm). There are two thoughts here:

If the problem is due to parameter A sometimes being supplied and sometimes not, consider dynamic SQL. You build the query only with the WHERE clauses that involve supplied parameters. This lets SQL Server optimize differently for each unique combination of parameters (highly recommended to combine with the instance-level optimize for ad hoc workloads setting).
If the problem is due to parameter A sometimes leading to a small number of rows and sometimes leading to a large number of rows, consider OPTION (RECOMPILE). This forces SQL Server to re-examine the parameters every time and optimize based on how many rows this set of parameters is likely to yield. There is some overhead to recompiling but it is often worth it for the stability it brings.

If both 1 and 2 are sometimes true, consider a combination - build the statement dynamically and add OPTION (RECOMPILE) to the end.

I talk about this in a lot more depth here:

#BackToBasics : An Updated "Kitchen Sink" Example

Sure, up to date stats help, but if you're simply re-using the first plan that gets compiled after stats are updated, they're worthless. In the case where the first time you search for last name like 'S%' and the second time you search for salary < 70000, the second will use the wrong index. As for filtered indexes, yes, you're definitely going to want to keep those updated manually, since they're not likely to ever fall into the 20% threshold (or the new one in place that mimics TF2371). But at least if you use dynamic SQL you will have a chance to use the filtered index plan when (and only when) it's appropriate.

Some other reading:

Why is my query suddenly slower than it was yesterday?


Ola Hallengren IntegrityCheck Excludes TempDB

Answer based on a comment left by Randi Vertongen:

Ola mentioned a long time ago (+5 years) on SQL Server Central that this is by default:

  This is by design. DatabaseIntegrityCheck is not doing checkdb on tempdb.

and a way around it by adding it explicitly.

  In the latest version you can do like this:

EXECUTE dbo.DatabaseIntegrityCheck
      @Databases = 'SYSTEM_DATABASES,tempdb',
      @CheckCommands = 'CHECKDB'

He goes on to say:

  When I developed the scripts I was looking at how Microsoft was doing in the Maintenance Plans. They are not doing checkdb for tempdb, so I did also not do that. Some time ago I added support for checking of tempdb, but not as the default. You need to specify tempdb explicitly.

Not entirely sure if this is still the case.


TSQL - How to strip out debit card numbers from a string of text?


Move extended property comments to create table comment

There is no need to change from extended props to plain comments. Since I figured out that the descriptions are displayed in the visual designer part of the table in the SSDT project's tables, this is not really required to change.

This article by Jamie Thomson explains the details and that the descriptions of the columns are getting visible there as soon the column "Description" in the designer is enabled. Also, they are automatically created into the create script if entered in the designer what is pretty cool.


this is a simple case of a conditional aggregate:

select product_name,
       sum(case when year = 2014 then sales_dollars end) as previous_year_sales,
       sum(case when year = 2015 then sales_dollars end) as this_year_sales
from the_table
group by product_name;


Azure SQL cross-database queries returns too many rows with joins

Recreating the issue

Test data below

SELECT o.OrderId, c.CustomerID, c.CustomerName
FROM OrderInformation o
JOIN CustomerInformation c ON o.CustomerID = c.CustomerID
WHERE o.OrderId = 155;

All records are returned by the remote query operator, to filter down to one when the inner join is applied.

Some more background on pushing down the predicate to a remote database:

  Previously, elastic database query could not push parameterized
  operations to remote databases. As a result, sometimes large row sets
  had to be unnecessarily brought local to evaluate these operations.
  With the recent improvements, parameterized operations can now be
  pushed to remote databases and be evaluated remotely. For a query over
  an external table and a local table like the following, this can now
  avoid transferring millions of rows by evaluating the selective filter
  in the WHERE clause on the remote database:

DECLARE @low int
DECLARE @high int
SET @low = 100
SET @high = 200

SELECT c.CustomerId, c.Name, count(OrderId)
FROM remote_customers c
JOIN local_orders o
ON c.CustomerId = o.CustomerId
WHERE c.CustomerId > @low and c.CustomerId < @high
GROUP BY c.CustomerId, c.Name;

Source

Above query shows an example of using parameters to define a range from where to read the data. The difference with your problem is that the filtering is happening on the local table.

If we know that this predicate can be pushed down to the remote table

SELECT o.OrderId,c.CustomerID, c.CustomerName
FROM CustomerInformation c
JOIN OrderInformation o on o.CustomerId = c.CustomerID
WHERE o.customerID > 1 and o.Customerid < 10;

You could look for a range of customerid's and filter on these

MAX() and MIN() functions on o.Customerid are  used to get the upper and lower boundaries for WHERE o.OrderId = 155

DECLARE @MAX INT, @MIN INT;

SELECT @MAX= max(o.Customerid) , @MIN = MIN(o.Customerid)
FROM OrderInformation o
WHERE o.OrderId = 155;

SELECT o.OrderId,c.CustomerID, c.CustomerName
FROM CustomerInformation c
WHERE c.customerID >= @MIN and c.Customerid <= @MAX;

The complete result

 DECLARE @MAX INT, @MIN INT;

 SELECT @MAX= max(o.Customerid) , @MIN = MIN(o.Customerid)
 FROM
 OrderInformation o
 WHERE o.OrderId = 155;

 ;WITH CTE as(
 SELECT c.CustomerID, c.CustomerName
 FROM CustomerInformation c
 WHERE c.customerID >= @MIN
 AND c.Customerid <= @MAX
 )

 SELECT o.OrderId, c.CustomerID, c.CustomerName
 FROM CTE c
 INNER JOIN  OrderInformation o
 on o.customerID = c.CustomerID
 where o.Orderid = 155;

Pushing down the predicates:

Execution plan of this query

RemoteQuery="SELECT [c].[CustomerID] [Col1005],[c].[CustomerName] [Col1007] FROM [CustomerInformation] [c] WHERE [c].[CustomerID]&gt;=? AND [c].[CustomerID]&lt;=?"

It depends on your data & parameters passed how much this could filter out on the remote table. YMMV

Test data

Customerinformation (remote db)

CREATE TABLE dbo.CustomerInformation (CustomerID int identity(1,1) , CustomerName nvarchar(255));

DECLARE @i int = 1;
WHILE @i < 20000
BEGIN
INSERT INTO CustomerInformation(CustomerName)
VALUES ('Frodo');

SET @i += 1;
END

On the database that will use the external table

CREATE TABLE OrderInformation(OrderId Int identity(1,1), CustomerId int,Ordervalue int);

SET NOCOUNT ON;
DECLARE @i int = 1;
WHILE @i < 200
BEGIN
INSERT INTO OrderInformation(CustomerId,Ordervalue)
Values (@I,111);

set @i += 1;
END

CREATE EXTERNAL TABLE  [dbo].CustomerInformation(
    CustomerID [int],
CustomerName nvarchar(255))
    WITH
(
    DATA_SOURCE = Stackoverflow
);


How do I Continue After a Failure in SSIS

To answer the question as I asked it, it is possible to continue after failures by making sure that MaximumErrorCount is zero for the task and its parent containers... In this case that means the "Test Connection" task, the "foreach" container and the package itself.

I ended up doing something different though. And Jamie Thomson's Verify A Connection Before Using It was helpful here. Here's what I did:

I replaced the Test Connection task with a script task. The script task was almost exactly like Jamie's script except that I

only used one connection manager, not all of them,
the task result was always success
and I set a new variable "User::ConnectionSucceeded" to the result of the connection test.

I also changed the arrows coming out of the test connection task to evaluate expressions, they evaluate:

@[User::ConnectionSucceed] == True
@[User::ConnectionSucceed] == False

respectively.

I also had to remember to edit the multiple constraints going into the "Update LastMonitored" task. I changed them to use a logical "OR".

Here's what my SSIS package looks like now:


Interview Question:- I have one table with following data in sql server

You need a rolling string aggregate function. STRING_AGG doesn't allow OVER() clause, so a solution is to use a correlated subquery with FOR XML to generate the list.

SELECT
    T.COL1,
    RollingAggregate = STUFF (
        (
            SELECT
                ',' + CONVERT(VARCHAR(10), N.COL2)
            FROM
                [dbo].[T_COL] AS N
            WHERE
                N.COL1 = T.COL1 AND     -- Same letter
                N.COL2 <= T.COL2        -- Equal or lower number
            ORDER BY
                N.COL2
            FOR XML
                PATH('')
        ),
        1, 1, '') -- Remove leading comma
FROM
    [dbo].[T_COL] AS T
ORDER BY
    T.Col1,
    T.Col2

Result:

COL1    RollingAggregate
A       1
A       1,2
A       1,2,3
B       1
B       1,2
B       1,2,3


How to run mysqldump from windows command prompt

it permissions conflict when try create file -

  c:>mydatabase.sql

create new folder c:\backup, change string to

C:\>mysql\bin\mysqldump.exe -u root -p mydatabase > c:\backup\mydatabase.sql

I think it just mistake when type there -

c:\>mydatabase.sql

it is also not correct, correct:

c:\mydatabase.sql

correct, but still will return error, right way - create folder


Is there some plugin that would reconnect query tabs to original DB's in SSMS?

You can set the default database for each login. If you subsequently drop this database you can run into chicken-and-egg problems trying to correct it, so be careful.


Your first example is considerably better than your second.  It is a standard rule to not use repeating groups of fields and to break fields down to their smallest meaningful value.  BTC_Price and ETH_Price are not broken down to their smallest meaningful value; price and crypto.  In the first example you separated the crypto from price.  This is the correct thing to do.  Joe is also correct in that you should have another table to hold the different kinds of cryptocurrencies with a auto incrementing primary key.  This is often referred to as a lookup table.

Have a look at database normalization and learn about what are called "normal forms".  Your first table conforms with what is called "3rd normal form" and your second table conforms with I believe "2nd normal form".  Typically the higher the normal form the better but 3rd normal form is considered the standard that you should attempt to achieve at minimum.


how can database snapshot to be used to recover data?

Yes, you have to use restore database ... FROM DATABASE_SNAPSHOT. It is called reverting the database to a database snapshot. You would use it to reverse a serious error e.g. drop table, deleting data, etc.
This is important :

all changes made after the snapshot was created are lost.

.

can we take a backup of database snapshot and restore it in a new server?

No, you cannot. This is covered in Database Snapshot limitations

You cannot back up or restore database snapshots.

You cannot attach or detach database snapshots.

Very important :

Using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.


How to include concatenated values in a dynamic pivot column value?

You can use REPLACE on the date column in the @PivotColumnHeaders string to build the concatenated column.

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
WHERE (@SelectedSystemIDs IS NULL OR  System.ID IN(select  * from dbo.SplitInts_RBAR_1(@SelectedSystemIDs, ',')))
AND ((@PlatformID =0) OR  (System.PlatformID = @PlatformID) OR (@PlatformID = 12 AND System.PlatformID <= 2))

REPLACE(@PivotColumnHeaders, *datecolumn*, *datecolumn* + '' '' + username + '' '' + CONVERT(nvarchar(10), recorded))


Automatically create linked row in a different table

Use triggers, see here

Create an insert trigger on the user table. In your trigger, create a record on the preferences table, get its id and store it in prefId on user.


Use filters instead of queries in MS Access?

No it isn't wrong, as both are the same, only the display is different, it simply doesn't matter.
Use the view you can best solve your problems.
Forms are only a gui, for your data. So that user can access , edit or delete data, without knowledge of sql or filters.
AS today a html form and web browser are much more common  and many different rdms server are available, the usage of access is reduced and that much new functionality in the newer Versions is almost not there.


Mariadb master master replication supported by HAPROXY for automatic failover

With your current configuration, if Server A goes down, HAProxy will switch to Server B. But as soon as Server A is back up and running on port 3306, HAProxy will switch back to Server A, which is not what you want.

The best way to handle this use case is to use the stick-table parameter.

With a stick table based on the destination IP address, HAProxy will redirect connections to server B even when server A is operational again.

It will only switch back to server A when you restart the HAProxy service or if server B fails or if you disable it manually.

Here is an example of a configuration:

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance source
    stick-table type ip size 1 nopurge
    stick on dst
    server MASTER_A X.X.X.X:3306 check
    server MASTER_B X.X.X.X:3306 check backup

You will find more information about this on this HAProxy blog post and in HAProxy documentation (see stick-table, stick on and peers options)


What's my option? Is there any way to sort the string like a number?

Yes, you have an option to sort string like a number, but in all your queries you need to convert your string field to an integer field just for sorting and it is not recommended.

 db.collection.aggregate(
[
    {
        '$addFields': {
            '_id': {
                '$toInt': '$_id'
            }
        }
    }, {
        '$sort': {
            '_id': 1
        }
    }
]
)

  Or I have to change my _id type?

Yes, you need to change the type of _id field from string to integer as a permanent solution. Since _id filed is an immutable field, you cannot directly convert the data of _id field from string to integer. you need to remove all your documents and insert with the corrected data type.


What happens to an insert when a mysql(innoDB) table is being altered(locked)?

What version of MySQL are you using?

Have you considered ALGORITHM=inplace and LOCK=none in your ALTER TABLE statement?

See also Using the LOCK clause to control concurrency


Best way to document database mappings for migrations

The most common way to do this Excel, but it has it's tradeoffs:

Very difficult to update the spreadsheet if either side of the migration changes (due to missing something, something changing, etc.)
Excel is harder to use by the people implement the rules documented in it.
Change tracking is nearly impossible if you have multiple people collaborating on the document.

I prefer to use data lineage features of data modelling tools such as ER/Studio, ERwin or PowerDesigner to track the transformations required for migration from one data store to another.  I get all the benefits of RE and compares to the source and target, plus none of the tradeoffs mentioned above.  It really makes a difference.


Begin Tran
    Create Table #TEMP(id INT,name NVARCHAR(50))

        INSERT INTO #TEMP
        SELECT 1,'cake' UNION ALL
        SELECT 2,'car' UNION ALL
        SELECT 3,'mountain'

        UPDATE #TEMP
        SET    NAME = +'*'+ NAME
        WHERE  id = '1' OR id = '2'

        Select * from #TEMP

ROLLBACK TRAN


Get slave status using sql query inside if-else condition

Why not simply do STOP SLAVE?  If not "slave_running", it will have no effect.


The same Procs takes 2 to 5 times more to run sometimes (it seems to vary).

You should recompile the stored proc using sp_recompile 'procedureName'. This should be one of the post migration step.

Another thing to be aware is if you are going to use more max memory (since you have more RAM on new server), you should look into testing and enabling TF2335

Also check the power option on the server. It should be set to high performance.

I agree with @Phil, check with your VM admin to see if the host machine is over committed in-terms of resources or not. Depending on if you are using VMWare  there are things like balloon driver (pdf whitepaper). For Hyper-V, read Running SQL Server with Hyper-V Dynamic Memory whitepaper.


How to find the MySQL query that got rolled back

Look in the SHOW ENGINE INNODB STATUS output for

LATEST DETECTED DEADLOCK

and between this line and the

WE ROLL BACK TRANSACTION

you will find QUERY content that was involved for each contributor to the deadlock (there may be thousands of lines of detailed info).


Pass parameters to a procedure with SQLcmd

There is no special way to pass SQLCMD variables to a Stored Procedure. Stored Procedures are encapsulated code and cannot be passed SQLCMD variables as you are attempting to do in the code posted in the Question.

Stored Procedures take input parameters to pass in values. And those input parameters should be of the correct datatype for the data being passed in; you do not need to accept VARCHAR for date values simply because SQLCMD variables do not specify their type.

CREATE PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
(
  @FirstDate DATETIME,
  @LastDate DATETIME
)
AS
SET NOCOUNT ON;

SELECT DATEDIFF(DAY, @FirstDate, @LastDate) AS [NumberOfDays];

Then you call it in one of the following ways:

EXEC [dbo].[_testProcedure] first_date, last_date;
EXEC [dbo].[_testProcedure] @FirstDate = first_date, @LastDate = last_date;

Now, IF you are executing the above T-SQL from SQLCMD, then you can pass in SQLCMD variable into that T-SQL, which is the current query:

-Q "EXEC [dbo].[_testProcedure] $(first_date), $(last_date);"

or:

-Q "EXEC [dbo].[_testProcedure] @FirstDate = $(first_date), @LastDate = $(last_date);"

Also, you need to pass in SQLCMD variable values using single-quotes, not double quotes. Try the following to see why:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate='12/05/2006'

Returns:

12/05/2006

But:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate="12/05/2006"

Returns:

0


What are the minimum requirements for running a dtsx package on a PC?

You have delivered a solution that uses SSIS. SSIS requires an installation of SQL Server Standard, BI, or Enterprise Edition (depending on components used).

At this point, your client will need to spend ~ 8k per core (SE) to 23k per core (EE) for a license of SQL Server for their desktop. That's probably not what they're expecting.

What can you do?

Assuming they have SQL Server installed in their enterprise, it should be a matter of deploying to somewhere that instance can access and educating the client on where they need to copy the source spreadsheet to and where they can pick up the output. I'm fond of my ETL file processing pattern but go with whatever works best for you and your client.

That addresses getting the package so it can run with the right input. Now you need to figure out how the client can call it. I would create a SQL Agent job with no predefined schedule and then the world's tiniest application that simply calls EXECUTE msdb.dbo.sp_start_job 'ExcelProcessor' That'll be an asynchronous process so you'd then want to do something like poll the output folder until a file appears or monitor the job table to see whether the job completes successfully before signalling the user that processing is complete.


How to set a auto-increment value in SQLAlchemy?

From the SQLAlchemy 1.2 Documentation:

  Postgresql 10 has a new IDENTITY feature that supersedes the use of SERIAL. Built-in support for rendering of IDENTITY is not available yet, however the following compilation hook may be used to replace occurrences of SERIAL with IDENTITY:

from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles

@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
    return text


Need help with LIKE operator and square brackets

There is no need to escape the closing bracket. If there is no opening bracket SQL Server will not consider the closing bracket as part of a placeholder.

So you should be fine escaping only the opening bracket, either using brackets or by defining an escape character (which I personally find more readable in this case).

CREATE TABLE statements (statement nvarchar(max));

INSERT INTO statements (statement)
VALUES (N'SELECT TOP (1)  whatever FROM [dbo].[customer] AS [Extent1] WHERE whatever')
,(N'SELECT TOP (2)  whatever FROM [dbo].[customer] AS [Extent1] WHERE whatever')
,(N'SELECT TOP (2)  whatever FROM [dbo].[product] AS [Extent1] WHERE whatever')

-- won't return anything
SELECT *
  FROM statements
  WHERE STATEMENT like '%FROM [[]dbo[]].[[]customer[]]%';

-- should return 2 records
SELECT *
  FROM statements
  WHERE STATEMENT like '%FROM [[]dbo].[[]customer]%';

-- should return 2 records
SELECT *
  FROM statements
  WHERE STATEMENT like '%FROM \[dbo].\[customer]%' ESCAPE '\';

DBFiddle here

The documentation specifies both methods of escaping and specifically states (under the heading Using Wildcard Characters As Literals):

Symbol          Meaning
LIKE '[[]'      [
LIKE ']'        ]


sqlcmd to run large SQL file causes high CPU usage

If the sql script is 156 MB I suppose that you're doing data load using a single statement for each row.

Is that everything in one big batch (all statements one after the other without GO)? If yes, then SQL Server will try to parse that file all at once... on a file that big you're killing the CPU.

Suggestions:

can you split that file in more than one batch? so the parsing will be done batch after batch..
can you load the data from bcp files instead of sql file?


How to create an INSERT/UPDATE/DELETE trigger

Here is a snippet of how you would construct the INSERT trigger. UPDATE would be very similar and DELETE would depend on how you want to handle it. We'll need a create table statement to help you in depth with all triggers.

Triggers are just T-SQL with a little logic tied in more or less. So how would you take data from A to B and rename it? Below is an example:

CREATE TABLE TableA (A1 INT
                   , A2 INT
                   , A3 INT
                   , A4 INT
                   , A5 INT
                   , A6 INT);

CREATE TABLE TableB (B1 INT
                   , B2 INT
                   , A3 INT
                   , A4 INT
                   , A5 INT
                   , A6 INT);
GO

CREATE TRIGGER TriggerTest
ON TableA
AFTER INSERT
AS
BEGIN
    INSERT INTO TableB (B1
                      , B2
                      , A3
                      , A4
                      , A5
                      , A6)
    SELECT i.A1
         , i.A2
         , i.A3
         , i.A4
         , i.A5
         , i.A6
    FROM   inserted i;
END;
GO

I'm matching up the column I want to INSERT with the SELECT clause, SQL knows A1 is going into B1 because of the ordering and declaration of the INSERT and SELECT. You can also use aliasing later if you need, example below:

SELECT 1 AS 'FakeNumber';

SELECT 1 AS 'NewColumnName';

Also, triggers can be both helpful and extremely dangerous. Use with caution. Put your development through the ringer with QA and testing, propagate up to production with caution. And as always, evaluate that you are using the best tool for the job, not all problems are nails and not all solutions are hammers.


You can use NVL to return a default string when a null value is encountered:

SELECT NVL(fieldname,'Show this text instead of null')
FROM myTable;

In your case, I would try something like this:

SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",
   INITCAP(NVL(RE.LASTNAME,'No Referral')) AS "REFERRED LAST NAME",
   INITCAP(NVL(RE.FIRSTNAME,'No Referral')) AS "REFERRED FIRST NAME"
FROM CUSTOMERS CU
LEFT OUTER JOIN CUSTOMERS RE ON CU.REFERRED = RE.CUSTOMER#;


How can I get all data from a given table since my last run?

I think you can base your query on the ORA_ROWSCN pseudocolumn.

If two transactions T1 and T2 modified the same row R, one after
another, and committed, a query on the ORA_ROWSCN of row R after the
commit of T1 will return a value lower than the value returned after
the commit of T2

For this use case, the table must be created with ROWDEPENDENCIES (cannot be changed after table creation)

With this feature, each row in the table has a
system change number (SCN) that represents a time greater than or
equal to the commit time of the last transaction that modified the
row.

If you can recreate the table with ROWDEPENDENCIES, then you would swap your latest_changed_date_from_last_run for latest_ora_rowscn_from_last_run and that should do the job.


Incremetal backup using mysqldump in MySQL

As pointed out in my previous answer, mysqldump does not provide incremental backup/dump capabilities:

  The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Reference: 4.5.4 mysqldump — A Database Backup Program (MySQL Documentation)

mysqldump doesn't contain any option(s) that would allow you to perform an incremental dump.

Reference: Option Syntax - Alphabetical Summary (MySQL Documentation)

Solution

To have an incremental backup without being able to use the mysqlbackup program you would have to perform the following task.

Physical (Raw) Backup

You can perform a backup of the MySQL \data directory to have a current backup of the databases in your MySQL instance. It is recommended to close/shutdown the instance to have a consistent state:

  Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup.

(emphasis mine)
Reference:  7.1 Backup and Recovery Types (MySQL Documentation)

Incremental Binary Log Backup

  MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see Section 5.4.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS or mysqldump --flush-logs. See Section 4.5.4, “mysqldump — A Database Backup Program”.

Reference: 7.2 Database Backup Methods (MySQL Documentation)

FLUSH LOGS

  Closes and reopens any log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. If relay logging is enabled, the sequence number of the relay log file is incremented by one relative to the previous file.

Reference: FLUSH LOGS

An Example:

Once a week at 8pm:
 a. Create a copy of the MySQL /data directory while the database is down.
 b. After "FULL" Backup: Restart the database.
Every other day at 8pm:
 a. execute a FLUSH LOGS command
 b. create a backup copy of the binary logs directory

You can further optimise the incremental backup by storing the name of the last n-1 binary log file as an additional flag-file and starting off from there. That depends on your requirements.

  DISCLAIMER
  Please test your backup and restore procedure before you implement this in a productive environment. No guarantee given that this procedure will work for you.


Copying the value of ROWVERSION to another column in same row on insert

I do not think that there is a clean solution to add the rowversion value in the same insert statement. Here are some reasons as to why I would think that

You can create a Binary(8) column and store the MIN_ACTIVE_ROWVERSION() in there, but among other issues, multiple inserts will fail.

CREATE TABLE dbo.ID_Test_rowver
(
Rowver rowversion NOT NULL,
EmpName nvarchar(50),
Rowver2 binary(8) PRIMARY KEY NOT NULL
);

INSERT INTO ID_Test_rowver(EmpName,Rowver2)
VALUES('Emp3',CONVERT(BIGINT,MIN_ACTIVE_ROWVERSION()));

Validating the values

Rowver              EmpName Rowver2
0x00000000000007E3  Emp3    0x00000000000007E3

Run an update

UPDATE dbo.ID_Test_rowver
SET EmpName = 'Emp4'

Validating

Rowver              EmpName Rowver2
0x00000000000007E4  Emp4    0x00000000000007E3

Inserting multiple records is going to fail

INSERT INTO dbo.ID_Test_rowver(EmpName,Rowver2)
SELECT EmpName, MIN_ACTIVE_ROWVERSION()
FROM  dbo.ID_Test_rowver;

  Violation of PRIMARY KEY constraint 'PK__ID_Test___51A71CFE5E24148F'.
  Cannot insert duplicate key in object 'dbo.ID_Test_rowver'. The
  duplicate key value is (0x00000000000007e9).

Because it is calculated once per set, not per row.

An after insert trigger to update the column

CREATE TRIGGER RowverTrigger
ON dbo.ID_Test_rowver
AFTER INSERT
AS
UPDATE RT
SET RT.Rowver2 = I.RowVer
FROM dbo.ID_Test_rowver RT
INNER JOIN INSERTED I
ON I.RowVer = RT.Rowver;

But you would not be able to do that with a primary key constraint unless you have a unique placeholder value.

CREATE TABLE dbo.ID_Test_rowver
(
Rowver rowversion NOT NULL,
EmpName nvarchar(50),
Rowver2 binary(8)
);

And, as a side effect of this update, the rowversion column is updated again.

Rowver              EmpName Rowver2
0x00000000000007EF  Emp3    0x00000000000007ED

Changing the query to do an insert + Update in would get the same end result as the trigger.

BEGIN TRANSACTION
INSERT INTO dbo.ID_Test_rowver(EmpName)
VALUES('Emp5'),('Emp6')

UPDATE RT
SET RT.Rowver2 = RT.RowVer
FROM dbo.ID_Test_rowver RT
Where Rowver2 IS NULL
COMMIT TRANSACTION

Another option would be Row by row inserts with an instead of trigger.

CREATE TRIGGER RowverTrigger
ON dbo.ID_Test_rowver
INSTEAD OF INSERT
AS
BEGIN
        DECLARE @count INT ;
        DECLARE @counter INT ;

        SELECT  @count = COUNT(*) ,
                @counter = 0
        FROM    inserted ;

        SELECT  * ,

                ROW_NUMBER() OVER ( ORDER BY ( SELECT   1
                                             ) ) AS TriggerRowNumber
        INTO    #inserted
        FROM    inserted ;
        WHILE @counter < @count
            BEGIN
                INSERT  INTO dbo.ID_Test_rowver
                        ( EmpName ,
                          Rowver2
                        )
                        SELECT  EmpName ,
                                @@DBTS + 1
                        FROM    #inserted
                        WHERE   TriggerRowNumber = @counter + 1 ;
                SET @counter = @counter + 1 ;
            END
 END

Based on Listing 9 from this source

Note that I had to use @@DBTS + 1 here, because MIN_ACTIVE_ROWVERSION() was not deemed active inside the loop.

Among other considerations, performance of this will be very slow depending on the amount of inserted records.

When considering these examples, you would have to choose between

Not being able to do multi value inserts.
Row by row inserts.
Not having a primary key.

Those choises are really not the choices I would like to make.

Not to say that somebody more knowledgable than me might give you a better solution


I use this solution but it is not an elegant solution. It converts all accented french characters to english equivalents and strips out odd characters.

order by translate(LibelleCourant,'ÉéÀàÇçÈèÙù''/*;"<>() ','EeAaCcEeUu')

Edit:  if you want to keep the special characters to sort by then change the translate function.
Example: you want to keep the < and > symbols, just add them to the end string
order by translate(LibelleCourant,'ÉéÀàÇçÈèÙù<>''/*;"() ','EeAaCcEeUu<>')

What is happening is that any single character which is present in the first string and has a corresponding value in the second string will be replaced.  Extra characters will be removed.  These characters will be replaced ÉéÀàÇçÈèÙù<>
and these characters will be removed:          ''/*;"()

See the Oracle docs for more info.


How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?

Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.
And:
The undo log is usually part of the physical system tablespace, but from MariaDB 10.0, the innodb_undo_directory and innodb_undo_tablespaces system variables can be used to split into different tablespaces and store in a different location (perhaps on a different storage device).
https://mariadb.com/kb/en/innodb-undo-log/
For MEMORY: What about VARCHAR(65532)?


Database in SIMPLE recovery model full using SSIS bulk insert task

I can watch the database size grow from 2GB to 12GB (max space
  available) where it runs out of space and returns the following
  message:

The issue is either you're simply running out of disk space and the physical file cannot grow to complete the transaction, or you have a limit on the SQL file growth settings and the physical file cannot grow more to complete the transaction (whatever max space available means extactly e.g. per settings or disk capacity).

  I thought because I was in SIMPLE recovery model, bulk loading isn't
  logged.

Per the below site and quoted text it does state  minimal logging is more effecient than full logging, but it does NOT state that NO logging occurs at all in SIMPLE recovery mode.

  Prerequisites for Minimal Logging in Bulk
  Import

  under the simple recovery model or bulk-logged recovery model, minimal
  logging of bulk-import operations reduces the possibility that a
  bulk-import operation will fill the log space. Minimal logging is also
  more efficient than full logging.

So this means there IS still SQL transaction logging that occurs when the database is in SIMPLE recovery mode but it's minimal logging when compared to FULL recovery mode.

  Why is it growing so large?

It would seem the explanation would be that your your committed transaction sizes with the BULK INSERT operations are HUGE. Simply make the commit\batch size parameters of your logic in SSIS or your execute TSQL in the package of a smaller size. Try testing with 100, 1000, 10000, 100000, and so on to see what gives you the best result and prevents the issue from occurring.

If the transactions are smaller, once the committed transactions are committed in SIMPLE recovery mode, then the log space of the committed transactions can be reused by subsequent BULK INSERT (or other) transactions.

Additional Resources

(These articles seem to have some references to potential parameters in SSIS package areas to set for smaller transactions, etc. The titles or the steps may be for a different ultimate goal, but there is potentially applicable content in these for what you may need to change in your SSIS logic to rectify your issue so these may be worth a simple read.)

Top 10 SQL Server Integration Services Best Practices
Have your SSIS Fast Load (Bulk Load) and Row by Row Error Messages too


Yes, your answers are mostly right, except a few mistakes:

1. R UNION S

max : n+m ( union we add all the tuples from both relations)
Correct, when R and S have no common tuple.
min: 0 ( taking m=n=null )
Wrong, the minimum is n (the greatest of the two sizes, m and n). When all the tuples of R also exist in S.
And m and n cannot be null, they are the sizes of the relations, they are numbers (integers).

2. R INTERSECTION S

max : m ( m<n both relation contains same keys then we may get maximum m keys )
Correct but the reasoning is wrong. You compare tuples of the two relations, not keys.
min: 0 ( taking m=n=null if no common keys in both relations)
Correct but the reasoning is wrong. The result can be 0 because the two relations may have no common tuples).

3. R - S

max : m ( if they are disjoint then in R-S we will get all tuples of R )
Correct
min: 0 ( if all tuples in R is also present in S)
Correct

4. S - R

max : n ( as explained above )
Correct
min: 0 ( as explained above )
Wrong, the minimum is n - m.

5. R natural join S

max : n*m ( if no matching key constraints natural join will produce Cartesian product )
Correct
min: m ( m < n  when key constraints are taken into consideration )
Wrong, the minimum is 0. You can easily find an example, identical with case 2 (INTERSECTION).

6. R LEFT OUTER JOIN S

max : m ( everything from left table will be output even if no match)
Wrong, the maximum is m * n, the same as for natural join. Or just take ON TRUE.
min: 0 ( when m=0 )
Wrong, the minimum is m. Example can be the same as for NATURAL join above (or just take ON FALSE) but it cannot give as a result lees than the number of tuples in R (the left relation in the join).

7. R / S

max : m ( when n=0 )
Correct but it doesn't have to be n=0 or m=0. You can find another example.
min: I'm not able to make a conclusion.
Minimum is 0 Consider that relational division is similar to integer division. 3 / 7 gives 0 in integer division for example. Try to convert this into relational division.


creating an index for varchar column greater than 900 bytes

If you need an index so seeks on the first n characters work well (where n <= 900 bytes), you can create a computed column of LEFT(column, n) and index that. If you want an index for exact matching, sorry, there isn't a way that I know of to do that unless you use a checksum or similar. Can you explain the actual business problem? What strings > 900 bytes do you feel would benefit from such an index, and why?


Is there a way to extract a report of or script all of the primary keys in a database?

SELECT *
FROM
 (SELECT obj.name + '.' +col.name as colName, idx.name, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint
  FROM
    sys.indexes idx INNER JOIN
    sys.objects obj ON idx.object_id = obj.object_id INNER JOIN
    sys.index_columns idxcol ON idxcol.index_id = idx.index_id AND idxcol.object_id = idx.object_id INNER JOIN
    sys.columns col ON col.column_id=idxcol.column_id AND col.object_id = idxcol.object_id
  WHERE obj.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')) a
WHERE a.is_primary_key = 1


Minimal Permissions to Create an Application Database and Database Owner

That can be achieved by creating a user based on a certificate and Signing a stored procedure by using a certificate:
USE master;
GO

CREATE CERTIFICATE MyInpersonationCert
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Certificate to do a test',
   EXPIRY_DATE = '20261031';
GO

CREATE LOGIN login_from_cert FROM CERTIFICATE MyInpersonationCert;
GO

--It might be a little bit too much permission, but I'm tired and it's late at night.
--You'll figure out the proper privileges for this login ;)
ALTER SERVER ROLE sysadmin ADD MEMBER login_from_cert;
GO

With the login from the certificate having the privileges to create and manipulate the objects you need you can create the procedure.
CREATE PROCEDURE impersonate_procedure
    @database_name nvarchar(128),
    @login_name nvarchar(128),
    @login_password nvarchar(128)
AS
BEGIN

    --add some verification on the received parameters to avoid SQL Injection (still tired)

    DECLARE @sql nvarchar(max);

    SET @sql = '
        CREATE LOGIN ' + @login_name + ' WITH PASSWORD = ''' + @login_password + ''';
        ALTER LOGIN ' + @login_name + ' DISABLE;
        CREATE DATABASE ' + @database_name + ';
        ALTER AUTHORIZATION ON database::' + @database_name + ' TO ' + @login_name + ';';

    EXECUTE sp_executesql @sql;

END;
GO

ADD SIGNATURE TO dbo.impersonate_procedure
    BY CERTIFICATE MyInpersonationCert
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

Now you can grant execute to the login used for the CI scripts on dbo.impersonate_procedure. Your login will be able to execute the procedure, but not to explicitly issue a CREATE DATABASE or ALTER AUTHORIZATION.


PostgreSQL JSON query array against multiple values

There is a way: combine the containment operator @> with the ANY construct:
SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:
...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.
You can use an index for this:

Index for finding an element in a JSON array

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?


SQL Server 2016 - Catastrophic linked server error

If you get a stack dump, it's time to contact the author of the linked server driver and start a support call. That's not something you can fix via query changes or configuration.


Clustered Index - Trade-off Queries VS Inserts?

30k to 40k rows of Insert/update/Delete is not considered a big problem.

CI should be the most searchable column of each table.

Yes CI do have impact on DML due to index fragmentation.As you are saying there can 30k insert in same showid.You can minimise this be tuning correct Fill Factor.

Choosing correct fill factor will avoid frequent page split.

Correct application design (Not look and feel),DB design and optimize query is very important.

As per you explanation one user insert 30k rows and next minute it will analyse those 30K rows.That means Insert/Select both are equally important in your application and Select have more weight-age than insert.

So CI will be there in each table.only concern is correct choice of KEY and fill factor.

  I mean, it would be slower if it was with different ShowId? My
  understanding is, since this is CI, then each insert would have to
  physically shift positions to insert. Is that true? If so, why having
  the same ShowId makes any difference?

Sorry If i am wrong.See if there are 30k insert,and showid is CI and also there are different Showid then insert will be relatively slower.Why ?
Because CI are ordered and optimizer will first search for  for correct sequence of each page then insert showid one by one.Example Showid 2 will be inserted between 1 & 3 and showid 34 will be inserted between 33 & 35 and so on.So optimizer task will increase.

In case of similar showid,optimizer have to search just once.

I think this link will clear your doubts,

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

Of course first read about fill factor also.

And Rebuild Index every 1-2 days.


can we transfer sql database backup from one server to another server directly?

Your options are pretty limited.

You can create a shell database and then use something like SQL Compare from RedGate to compare the schema and migrate the schema.  Then possibly do a Data Compare to script out the data and migrate it.

Alternatively, you could use the export option in SQL Server to export a bunch of tables which will recreate the tables on the destination.  The downside here is that FKs and other constraints can get muddled.

Another option is to use replication.  Unfortunately, that means you must backup the database and restore it to the destination server.

And then there is the copy database wizard as mentioned by shanky.

My preference is to use backup/restore.


Mysql - Each mysql database with its own user?

I would opt for different users accounts for each database.

If you implement the logon module seperately from the rest of the application, you have something that looks more like a portal to individual provisioned environments. That leaves you with more options in the future.

The only drawback I can think of is that this could lead to many database connections, not sure when that is a performance killer.

Having one connection to the database (and thus one session), means you have to switch between databases while handling requests from different companies. You should investigate how this affects performance as well.


SQL Server varbinary column modifying inserted data

The data you pasted is exactly 65533 hex characters.  Including the "0x" prefix, this is 65535 which is exactly 2^16 - 1.  This strongly hints that the code that generates the hexadecimal string representation of the binary data is limited to 65535 characters.  Very likely, it is using a buffer of that length.

If you are using SQL Server Management Studio to retrieve a varbinary value to use in your update query, please note that the value will be truncated according to this SSMS setting:

  Tools / Options / Query Results / Results to Grid / Max Characters
  Retrieved / Non XML Data

This setting happens to have a maximum value of 65535.  If you set this max value to, say, 31, you would get the same odd-numbered string from your example:

select * from photo -- 0xFFD8FFE000104A46494600010101 -- 29-digit hex string

This StackOverflow answer explains that the when there is an odd number of hex characters, SQL server will assume there is a leading 0.  So if you write this:

update Photo set photo_data = 0xF -- a 1-digit hex string

It is functionally equivalent to writing this:

update Photo set photo_data = 0x0F

And in fact selecting it out of the table will show you it is 0x0F.


how to search this field in Postgres for an array of characters

select *
from the_table
where 'skiing' = any (tags);

Online example: http://rextester.com/BDF4617


Indexing Strategy for the query performance

For a general-purpose or OLTP design, the initial index design should be more conservative:

Clustered index on Primary Key.
Unique non-clustered index any other unique keys.
Index supporting each Foreign Key (where not already covered above).

Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.

Then additional indexes as needed for optimization of the actual workload.  These can be indexes with included columns, filtered indexes, non-clustered columnstore indexes, or indexed views on a case-by-case basis.


CREATE DATABASE permission denied in database 'master'

You need to execute this T-SQL command using an account that does have administrative rights, such as sa:

USE master;
EXECUTE sp_addsrvrolemember @loginame = N'YourAppUserLogin', @rolename = N'dbcreator';

Replace YourAppUserLogin with the login the application user uses to connect to the database.


Because you wrote "each one of the..." it sounds like you are updating individual rows. Rather than doing a row at a time, have you explored writing the SQL so you are letting the DB update many rows in one SQL statement? That will likely get you much better performance than trying to multi-thread manually.


Issue with variable length string between compatibility level 80 and 100

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?

No, not that I am aware of. This is an issue of datatype precedence with regards to the CASE expression. The CASE expression needs to return a consistent datatype no matter which branch is selected. Since VARCHAR has a higher precedence than CHAR, the only choice here is to return VARCHAR. It looks like you will need to update the code to possibly convert the result of the CASE expression to CHAR(30). For example:

SELECT
    CONVERT(CHAR(30), CASE
                        WHEN condicion= '1' THEN texto1
                        ELSE 'TFI'
                      END)
FROM  #PRUEBA

Or, I suppose you can simply convert the string literal to match the datatype of the column:

SELECT
    CASE
      WHEN condicion= '1' THEN texto1
      ELSE CONVERT(CHAR(30), 'TFI')
    END
FROM  #PRUEBA

Also, why are you using compatibility level 80? That is for SQL Server 2000. And you are about to move up to SQL Server 2016? By default that would be compatibility level 130. And lucky for you, level 100 is available in SQL Server 2016, though it is the lowest level available. Still, if you are currently relying upon SQL Server 2000 behavior, you will likely have a lot more to change than just this if moving to SQL Server 2016.


Change the Row Order to alphabetical then numeral

I created a table called "billy".

mysql> SELECT * FROM billy ORDER BY f1;
+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho1234 | test4 |
| soho222  | test3 |
+----------+-------+
5 rows in set (0.00 sec)

which doesn't give the correct sort order - 1234 is bigger than 222!

Then, from here, I got the SUBSTRING() function and formulated
the following SQL:

mysql> SELECT CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED)
AS fred, f2 FROM billy ORDER BY fred ASC;

which gives you the integers you require to sort on ("fred" is an alias for the new integer column):

+------+-------+
| fred | f2    |
+------+-------+
|   10 | test5 |
|   12 | test1 |
|  123 | test2 |
|  222 | test3 |
| 1234 | test4 |
+------+-------+
5 rows in set (0.00 sec)

mysql>

This also helped for CASTing. And thanks to @oNare for his question in comments below which led me to change this post from using the MID() function to the more standard SUBSTRING().

And the answer you require is given by

SELECT * FROM billy ORDER BY CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED) ASC;

+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho222  | test3 |
| soho1234 | test4 |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


DBCC CHECKDB Data Purity errors but no rows found

I suspect that =96.00 is not the actual value in the row. If you look at the memory dump section above, the memory dump stops at the previous column.

Can you post the output from the following (replacing Database Name with the name of the database)?

DBCC Traceon(3604);

DBCC Page ('Database Name', 1, 807256, 3);


Why wouldn't statistics update if all rows in a table were replaced?

Unfortunately after searching SolarWinds' KB for about 10 minutes I cannot find what "Ineffective Statistics" measures. That information would be helpful. However, based on your question I'll infer that it means that the Statistics objects may be out of date or have taken quite a few modifications and Database Performance Analyzer has not detected an update of some sort. Without more information from the product vendor, it's hard to tell.

Having said that, there are a few things that you should know.

First, you can verify what's going on with your server right now (e.g. when you get the alert) and compare it against your monitoring tool. Here's a script that I use:

use [mydatabasename];
go
select
    [schema_name] = sh.name,
    [table_name] = t.name,
    [stat_name] = s.name,
    [column_names] = stuff((
                              select ','+index_col(t.name,sc.stats_id,sc.stats_column_id)
                              from sys.stats_columns sc
                              where sc.object_id = s.object_id
                                and sc.stats_id = s.stats_id
                               order by sc.stats_column_id
                              for xml path('')), 1, 1, ''
                            ) ,
    s.stats_id,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    [sample_rate] = (100.*sp.rows_sampled)/sp.unfiltered_rows,
    modification_counter,
    [default_mod_threshold] = case when sp.unfiltered_rows < 500
                                                   then 500
                                                   else ( .20
                                                          * sp.unfiltered_rows
                                                          + 500 )
                                              end ,
    [2371_mod_threshold] = sqrt(1000 * sp.unfiltered_rows),
    [default_mod_hit] = case when sp.modification_counter > case
                                                              when sp.unfiltered_rows < 500
                                                              then 500
                                                              else ( .20
                                                              * sp.unfiltered_rows
                                                              + 500 )
                                                              end then 1
                                           else 0
                                      end ,
    [2371_mod_hit] = case when sp.modification_counter > sqrt(1000
                                                              * sp.unfiltered_rows)
                                          then 1
                    end

from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
order by t.name, s.name;

Most of it is self-explanatory. However, I added the modification threshold for a statistics object at default and with TF 2371 included. The default threshold is an interesting calculation, while TF 2371 is the square root of 1000 * Table Cardinality. I included a calculation to include whether or not each of these thresholds had been hit yet. So run that inside the alerted database and see if what SolarWinds is seeing comports with what is occurring on the server right now. If it isn't then there's probably a timing issue in there somewhere such that the alert was generated before a stats update but after a massive amount of data change.

Having said that, it's important to know how AUTO_UPDATE of statistics actually works. There isn't a clock-like sweep that goes through and constantly updates statistics. Instead, the statistics are updated when the statistic object is loaded to compile a query plan. If the statistic is rarely/never used then it won't be updated unless you manually update it or if it happens to get caught up in an index rebuild as a dependent object for the index. However, if you have an index that's never getting used then that's a different situation altogether.

So in summary:

Verify what the alert is telling you
Know the factors that lead to a stats update


1) If you don't count your own time as a resource, then you should always be able to hand-craft a vacuum schedule which uses fewer total resources than autovacuum does.  If you do count your own time, this is almost surely not worthwhile.

2) Other than manually or algorithmically turning it on or off, no.  Nor would it make sense to do that.  A database that sees only a very steady predictable load with no unusual spikes still needs vacuuming.  However, if you do spend a lot of time coming up with a highly optimized hand-crafted vacuum schedule, then autovacuum will never have any vacuuming to do except under unusual activity not anticipated in your custom schedule; so in this sense what you want is automatically the case.

3) It isn't clear whether you mean when an autovacuum worker will start up in a given database,  or when any given table will start to be vacuumed.  The first is determined by autovacuum_naptime, unless autovacuum_max_workers becomes the limiting factor.  The second is determined by when the table crosses the threshold, with a granularity of autovacuum_naptime, except when autovacuum_max_workers becomes the limiting factor.  How accurately you can predict this depends on how steady the activity on that table is, and whether the vacuum tasks are generally overloaded or are mostly idle.


Only one NULL for one part of primary key

If I understand your question properly, SQLServer's partial index can do the job :

-- only 1 FinishDate nullable
CREATE UNIQUE INDEX IDXU_TAB1_1NULL ON TableName(FinishDate) WHERE FinishDate IS NULL;
-- or only 1 FinishDate is nullable for the same PersonId
CREATE UNIQUE INDEX IDXU_TAB2_1NULL ON TableName(PersonId, FinishDate)
WHERE FinishDate IS NULL;

Just to repeat what's already said :
  1. any column which is part of PK cannot be nullable
  2. as  Chris Travers mentioned in his comment to another answer, SQLServer treats NULLs in a different ways than other RMDS when it comes to unique constraints


Sql Server 2008 R2 Memory Caching , why doesn't it release the memory?

For any RDBMS, the bottleneck for performance is (usually) disk performance.  Compared to all other parts of the computer, disk I/O is the most expensive in terms of time required.  To this end, RDBMS engines focus on operating on as much data as possible in memory, as RAM is much faster to read and write to than physical disk.  It also provides a safety mechanism for ACID compliance, so that only work that is completed successfully will be persisted to disk.  If anything is rolled back, either due to failure or manual intervention, it can easily be discarded from memory without affecting the physical storage.

This means that relational engines will try and keep as much data in memory for as long as possible.  To continuously push and pull objects from disk to memory is expensive and will hurt application performance.  There are times items need to be cycled out of memory, but this will happen based on a LRU algorithm that is designed to keep active data in memory.

It is possible to view information about what is in your cache.  Note that the memory pool for SQL Server will contain different objects.  Primarily it will be the buffer pool, which stores data objects, and the plan cache, which are the query execution plans.  You can utilize specific dynamic management views for this observation, which are used in Glenn Berry's DMV script.  Look at queries 39 and 55.

If you wish to restrict the amount of memory SQL Server can use, the only option you really have is to reduce your Max Memory configuration for your instance.  Be careful with adjusting this, though, as a reduced memory pool can hamper your performance based on the factors described above.


Error ERROR 1153 (08S01) during mysqldump restoration on Amazon RDS

In order to change a dynamic option in Amazon RDS, you will have to modify the DB Parameter associated with the DB Instance.

Edit the option max_allowed_packet value, setting it to 1073741824.

Once you save it, it should be applied immediately since it is a dynamic option.

Afterwards, you can verify the change by logging into the RDS instance and running

SELECT @@global.max_allowed_packet;

Once verified, you may proceed to load your mysqldump.


Replicating only changes made by a specific database user

I agree with Phil+1 that this is not readily possible.  Furthermore, it is very unclear how this could even work.  Imagine the following simple table is being replicated.

 C1   C2
  1    'Apple'
  2    'Orange'
  3    'Pear'

Scenario 1

 batchjobaccount : INSERT INTO T1 VALUES (4,'Peach');
 appserveraccount: UPDATE T1 SET C2='Grape' WHERE C1=4;

What should happen in the replication?  Should the record be created so that the appserveraccount action can take place or should the update be ignored?

Scenario 2

 batchjobaccount : UPDATE T1 SET C1=5 WHERE C1=2;
 appserveraccount: DELETE FROM T1 WHERE C1=5;

What should happen in the replication?  Should the record be deleted or not?

Scenario 3

 batchjobaccount : DELETE FROM T1 WHERE C1=3;
 appserveraccount: UPDATE T1 SET C2='Strawberry' WHERE C1=3;

What should happen?  Should the update fail on the local database but succeed on the replicated database or should it not run on the remote database because it failed on the local database?

If you don't want changes by batchjobaccount to replicate, then why give them permission to make the changes on the local database?  If they need a local playground, then can you just make a local copy of the tables and only give batchjobaccount access to those?


What is the best way to represent the relationship between Team and Player in an ERD?

If you want a history of the all the players that have passed in that team the approach: Team 1:N Contracts N:1 Players is fine.
Otherwise if you just want to know the players that are currently in the team and contract is not a needed information for you, the approach: Team 1:N Player will also work


Oracle 10g dump file into Oracle 11g database

That error is most likely due to incorrect environment settings indeed, probably your PATH is mixed up too.

In a command prompt, assuming that your Oracle 11g home is indeed as you posted above, run:

set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_1
set ORACLE_SID=ORCL11R1
set PATH=%ORACLE_HOME%\bin;%PATH%

Make sure you can connect with sqlplus, then start imp.

(Do consider using the expdp/impdp Data pump tools instead though. imp/exp are essentially deprecated in 11g.)


The join part uses OR that's why running long I'm trying to make it faster

You may want to try a union of:

 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[consid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000

and

 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[concid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000

i.e.

 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[consid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000
 UNION
 SELECT sc.clientid
        ,sc.dictionid
        ,sc.firstname
        ,sc.lastname
        ,sc.DB
        ,sc.DOLS
        ,sc.dlnum
        ,Cons.Description AS ConS
        ,Conc.Description AS ConC
        ,'False' as Assigned
    FROM    [monitor].[uxref] xref
    JOIN [Monitor].[dbo].[seacc] sc
        ON xref.regid = sc.[concid]
    LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
    LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
    WHERE xref.UserId =1000


It sounds like the column is a composite of two or more items.  Creating an index on two or more columns can increase the chance that a value in the key is different from any other value in the index key.  This can have its benefits.  But as you add more columns than will be used in the where clause, the performance of the index degrades because it becomes excessively complex to evaluate.

The varchar data in the index is ultimately stored in a binary format.  Therefore the order of the parts of the varchar strings (variable then a constant contrasted with constant then a variable) in the column has no affect on performance.  Operations invoking an index use a binary tree for filtering and matching.

One way to optimize the query would be to add where clauses to the query.  But sometimes there is no way to do this for business/functional reasons.

Another way would be to find a key (e.g., a composite key) with combinations of values that will likely be different from other combinations of values on a row-by-row basis.  The next step would be to create an index based on this composite key.  But sometimes there is no way to do this for business/functional reasons.


SQL Server history of growth and shrink events

Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ?

There is easy way to get information about Data/log file autogrowth and Auto shrink events from database reports. The report fetches data from default trace. I am not sure how recent would be your report that depends on when was default trace rolled over. The default size is 20 MB after which it will roll over and also note that after SQL Server restart the trace will be rolled over

Connect to SQL Server instance and then right click on database and then select reports and then standard reports and then click disk usage.

In the report which comes look for Data/log file autogrowt auto shrink events and expand the + sign this is just below the circular usage report. The report would look like below.

In my case it only reported autogrowth event as I don't have auto shrink or any shrink activity running, which I guess you know is utterly bad.


Few things to keep in mind first, you're not using features from 5.6, that are not available in 5.1 like  utf8mb4 encoding (needed for emoji characters)

Take dump of all databases using mysqldump except mysql, is safer to recreate mysql.user table.

Try restoring on staging server first, to see if restored db works the way as you expect.


Make DB tables customizable from an Admin

You don't want to be in the business of constantly dynamically creating a separate table for each store's admin to store their own data in. Instead, use a single table and designate a column to differentiate the admin's customization, organized by store.

You haven't given enough details to get into the minutiae of the implementation, but this approach can facilitate having users only see/edit data from their store with a view, stored procedures, etc. once you associate each employee and inventory item to a specific store.

  Inventory
  ---------
  Image
  Description
  Price
  Store

  Employee
  ---------
  Name
  Store
  Title
  StartDate


Here is a concise way using IFNULL() function

SELECT *
FROM TABLE
ORDER BY IFNULL(order_by,'')='';

or adding the IF() function

SELECT *
FROM TABLE
ORDER BY IF(IFNULL(order_by,'')='',1,0);

UPDATE 2017-03-22 14:43 EDT

ypercubeᵀᴹ has one more suggestion:

SELECT *
FROM TABLE
ORDER BY (order_by > '') DESC;


What does wait_resource LOG_MANAGER mean in SQL Server 2008?

Whenever the log fills up it has to grow. See How to shrink SQL Server log for an explanation how it grows. Any transaction could be the one that triggers growth, simply because any transaction could be the one that happened to take the very last unused byte in the existing file. When growth occur all transaction block. And if you have default 10% auto-growth in place and your database log already grew out- of check then the log growth can be very slow as instant initialization is not possible on log files.

What matters is that you have a log growing, so you must take immediate measures to understand why is growing and to mitigate accordingly. Go read Factors That Can Delay Log Truncation right now. The fact that you caught B in the fact of being blocked by A could be just random. B could had just as easily trigger the growth, it would had taken exactly the same time to grow it, and you would have catch A being blocked by B. The fact that A is causing a long running transaction may be one of the factors that prevent truncation, but that has to be confirmed.


QUOTED_IDENTIFIER is a connect-specific setting.  You can see what sessions have it set through the sys.dm_exec_sessions DMV.

But if you are looking to set the default of the instance, you can configure this through sp_configure:

exec sys.sp_configure 'user options', 256
go
reconfigure with override
go

See this BOL reference on user options config.

To set this on the database level, you would do:

alter database YourDB
set quoted_identifier on
go

BOL reference

Within Visual Studio 2010, the option can be found under Properties -> Database.sqlsettings.  Below is a screenshot showing the option:


When you start a backup or stop a backup you'll get a WAL location, if you want to know it's physical location on disk use the following function: select pg_xlogfile_name('7/2DBCED18');


Regardless of any other improvements you make, be sure to test the impact on spatial execution times of enabling trace flags 6532, 6533, and 6534 (start-up only). These turn on native code spatial implementations. SQL Server 2012 Service Pack 3 or SQL Server 2014 Service Pack 2 required (Microsoft Support article). Native compilation is on by default from SQL Server 2016.

For STDistance the important trace flag is 6533. In a simple test, this improved execution time from 2100ms to 150ms without using a spatial index on my laptop's SQL Server 2012 instance.

Example adapted from SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s) by Bob Ward):

Test Data

CREATE TABLE dbo.SpatialTest
(
    ID      integer NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Points  geography NOT NULL
);
GO
-- Insert random sample points
SET NOCOUNT ON;
GO
DECLARE @Point float = 1.1;

INSERT dbo.SpatialTest (Points)
VALUES ('POINT(' + CAST(@Point AS varchar(20)) + ' ' + CAST(@Point AS varchar(20)) + ')' );

WHILE(SCOPE_IDENTITY() < 100000)
BEGIN
    SET @Point = @Point + RAND(SCOPE_IDENTITY());
    IF (@Point > 90.0)
    BEGIN
        SET @Point = -89.0 + RAND(SCOPE_IDENTITY());
    END;

    INSERT dbo.SpatialTest (Points)
    VALUES ( 'POINT(' + CAST(@Point AS varchar(20)) + ' ' + CAST(@Point AS varchar(20)) + ')' );
END;

Test Query

DBCC TRACEON (6533);
DBCC TRACESTATUS;
GO
DECLARE
    @s datetime2 = SYSUTCDATETIME(),
    @g geography = 'POINT(1.0 80.5)';

SELECT [Matches] = COUNT_BIG(*)
FROM dbo.SpatialTest AS ST
WHERE ST.Points.STDistance(@g) > 10000000.5
OPTION (MAXDOP 1, RECOMPILE);

SELECT [Elapsed STDistance Query (ms)] = DATEDIFF(MILLISECOND, @s, SYSUTCDATETIME());

Average execution times: 2100ms (trace flag off); 150ms (trace flag on).


Using a SELECT command to retrieve data on MySQL

The SELECT starts off by saying "give (select) me these columns" from this table that connects or joins to these other two tables in this way and where this rule is true.

So you figure out what columns names you need for the results.

Then you figure out how to connect all of the pieces together (foreign key here connects to some primary key there).

Then you add a where clause to filter out unwanted data.

Select p.first_name, p.middle_name, p.last_name,
  c.subject_name, c.level_of_entry
From classes c
Inner Join entries e on e.class_id = c.class_id
Inner Join pupils p on p.puil_id = e.pupil_id
Where c.exam_board = 'WJEC'


how wsrep_local_index is defined or could be changed?

The wsrep_local_index does not change due to desync mode. You most likely restart the node, this process does change the local index.

Here is the log of the process to show that it does not change:

MariaDB [(none)]> show global status like 'wsrep_local_index';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| wsrep_local_index | 0     |
+-------------------+-------+
1 row in set (0.002 sec)

MariaDB [(none)]> set global wsrep_desync=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show global status like 'wsrep_local_index';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| wsrep_local_index | 0     |
+-------------------+-------+
1 row in set (0.002 sec)

MariaDB [(none)]> set global wsrep_desync=0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show global status like 'wsrep_local_index';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| wsrep_local_index | 0     |
+-------------------+-------+
1 row in set (0.002 sec)


Postgres repmgr - downstream nodes not attached

Some needed wal files to replicate wasn't found on primary.
Then I reinstated the standby cloning it again.

Commands submitted on standby server:

   pg_ctl stop
   repmgr -f /etc/repmgr/10/repmgr.conf --force --rsync-only  -h node1 -d repmgr -U repmgr --verbose standby clone
   pg_ctl start
   repmgr node status
   repmgr node check


Ms VS2017 with SSDT has the 'User with Password' template but thinks it's wrong

As Aaron mentioned in the comments, try enabling containment in the database settings:

That's after right-clicking the database project and choosing "properties" in Visual Studio.

If you are not using contained databases, use the "User" script template instead of the "User with Password" script template.


In order to understanding why error 3456 would be thrown, we need to take a little step back and understand how SQL Server handles this corner of recovery.

When SQL Server is redoing an operation, and that redo is a page modification, it makes a quick check.  In the page header there is ultimately going to be a PageLSN, which is an indication of the last LSN that has modified that page, recorded by the page.  Think about it like this, the page keeps track of the last LSN that has made modifications to it.  This is the PageLSN.

Every time there is a logged page modification operation, that log record includes a few LSNs.  Namely, the log record's LSN (think... Current LSN), and then it has what's called the Previous Page LSN (PrevPageLSN going forward).  So when we modify a page, one of the pieces of data that is put into the log record is what the page indicates as being the last LSN before you to have modified the page.

Think about it like this...  Your car needs to have work done on it.  Mechanic John works on your car, and in the engine bay it has a little tag and Mechanic John writes "John worked on this car last".  Then the next time you take your car in to another shop, Mechanic Mark looks in the engine bay and sees that Mechanic John worked on this car last.  On his data sheet he writes this information.  Same idea with SQL Server.

This can be somewhat confusing, so take a look at this image below on sequential page modifications, and how the PageLSN and PrevPageLSN relate:

Let's loop back around, as this all comes into play when you need to redo an operation on a page (restores, recovery, HA, etc.).  When SQL Server needs to redo a page operation, it makes a sanity check to see if the PageLSN on the page matches the PrevPageLSN that the log record includes.  If that is not equal, then you will see error 3456 get thrown.

Does PageLSN equal PrevPageLSN?  No???  Stop and raise error 3456...

Let's analyze your error message, which includes the how:

  Could not redo log record (210388:123648:232), for transaction ID (0:1016710921), on page (4:8088), database 'SomeDB' (database ID 6). Page: LSN = (0:0:1), type = 11. Log: OpCode = 4, context 11, PrevPageLSN: (210388:122007:1). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

I have bold'd the two pieces of data that have an inequality causing the error.  You can see that our PageLSN is 0:0:1 (this was found in the page's header), and our PrevPageLSN is 210388:122007:1 (this was found in the data on the log record that was attempting to be redone).  These are obviously not equal, hence err3456.

So in order to find out the why of this event, would be to find out why there is a disparity here.  We really need to trace the lifecycle of page 4:8088 and see where the disconnect is.  Unfortunately without further information, or hands-on troubleshooting there isn't much else I can do besides give you the background of this recovery operation and what causes the error.


Schema for 1  0..1(+), many related but only one primary

Try a filtered unique index, not a trigger.

CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID)
WHERE IsPrimary = 1;


In which form Microsoft SQL password should be given when creating a user?

Yes, it is there correctly. You have run CREATE LOGIN correctly.

The number of stars in the SSMS box is just a mask and is unrelated to your actual password. SSMS doesn't know or try to read your password from the server

Note that SQL Server doesn't store your password so it can't relate to the **** in SSMS anyway. SQL Server hashes it into the password_hash column of sys.server_principals


Any way to maintain a partial index on only the highest element in Postgres?

The best is to take an alternative approach and not to query the large table all the time, if that is the only purpose of the index.

Create a second table that only contains a single timestamp and define a trigger on sometable that updates that small table whenever a new row arrives or an old one is updated.

This approach will only work well if you never delete the row with the latest created_at or never update created_at to an earlier timestamp.


Combining Always Encrypted AND Column level encryption in SQL Server 2016

I have yet to come across a definitive "Best Practices" list regarding multiple encryption types for SQL Server, but in your situation I would probably recommend the following:

Encrypt necessary data using Always Encrypt

Its the new hotness. You're on 2016, enjoy it!
Better security - the DBA can't decrypt data since the key is stored outside the database.
More options for tuning individual queries and reducing the performance hit of the feature.
Easiest to implement on both the database and app side (unless TDE is an option) since you only have to configure driver parameters versus changing your queries as with Column Level Encryption.
The additional decryption load becomes the application's (specifically the driver's) problem (fine for me as a DBA).
This feature is likely to be improved and optimized for a while since it is a new (my opinion).

For columns you need to search on, create a hash value to do lookups on.

Maintains security of the data but is very efficient for lookups. It allows you to have the performance of a searching on a normal row without the overhead of decrypting every row or defaulting to a table scan. You can index it for further performance if desired.

Your app will take the user input and hash it, then use that hashed value to query the hashed version stored in the table. This lookup will be fast and will allow you to only decrypt the desired row(s) versus decrypting rows you don't actually want as part of your result set.

Design of this depends on how many columns you need to look up and if you're using multiple or individual ones in your WHERE clauses.
Extra space is worth it for performance as long as you can afford it.

Don't bother using Column Level Encryption

You'll have to decrypt the entire column to do searches, which will be less efficient than an un-encrypted hash column. This means the larger your table, the more inefficient this method becomes. 1 million rows means you have to spend the CPU to decrypt a million rows even if your filter is only returning 1 row. Just because you can perform a search doesn't mean it will be performant.
I can see long term maintenance of two encryption features within the same table being confusing and becoming at best tribal knowledge.

You can also implement #2 with Column Level Encryption as well, but without any constraints in place, I can't see why you'd choose CLE over Always Encrypted in general.

I joke about making encryption the application's problem, but I find that the database often gets saddled with a lot more business logic and wacky functionality than it should be in most cases. If there is an option to push some computation back to the application side, I've found that is generally the best option and helps prevent troubleshooting and general tuning from becoming a nightmare.

Of course you should fully test the alternatives. If you find CLE, or a combo, works best for your particular scenario, so be it.

Additional reading:

Always Encrypted Performance - Aaron Bertrand
Always Encrypted Performance - A follow Up - Aaron Bertrand
Column Level Encryption Overhead - Dallas Snyder
Always Encrypted (Database Engine) (official documentation landing page)


How to select rows with a particular rank per partition?

window functions have some limitation for example you can use them in having.

You can only use window functions in the SELECT list and ORDER BY clauses of a query.

Window functions are listed between the two keywords SELECT and FROM at the same place where usual functions and columns are listed. They contain the keyword OVER.
The definition :
-- Window functions appear between the key words SELECT and FROM
SELECT   ...,
         <window_function>,
         ...
FROM     <tablename>
...
;

And in mysql WINDOW functions restriction is written

Using such functions in a subquery of these statements (to select rows) is permitted.

So your approach is correct. You Have to put the SELECT in a subquery or as Akina said you can use cte , which is only another FORM
Your query has to be
select department, employee, salary
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank
      from employee e
      join department d on e.departmentid=d.id
      group by d.id ) temp
WHERE salary_rank=1;


Can we replicate or restrict specific databases in MongoDB

Replica sets in MongoDB are designed for data redundancy and failover, so all data-bearing members of a replica set currently contain the same databases & collections as at MongoDB 4.2. A member with a partial data set would not be able to fulfil either of these key design goals for a replica set deployment.

There is a relevant server issue you can watch & upvote: SERVER-1559: Ability for a replica set node to only have a subset of the databases or collections. This is currently on the development backlog but there is an alternative approach you can use in the interim.

If you want to support non-standard replication/sync scenarios, I would look into writing a small program to replicate desired changes using the MongoDB Change Streams API. Change stream events are based on the replication oplog and allow you to filter and process relevant events matching on criteria such as namespace, operation type, or field values. You can also modify change stream output using additional aggregation stages (for example, projecting a subset of fields or adding calculated fields). Most importantly, the Change Streams API gives you a supported upgrade path to future versions of the MongoDB server.

In MongoDB 3.6 you can only open change streams at a collection level. Since you are interested in changes at a database or deployment level, it would be worth upgrading to MongoDB 4.0 which includes more comprehensive change stream support.


Why do the outdated statistics degrade a query performance?

It seems that the facts you presented are not so precise, let me say:

1. The estimated execution plan is created by Query Optimizer which uses statistics to generate the query processor tree. (query processor tree = execution plan)

2. The actual execution plan is the estimated execution plan, annotated with the real cost. This can be used to validade how wrong is the plan.

If your query optimizer don't have good stats, it will generate bad plans. Your query will run with that bad plan and only at the end (after the execution), if you compare with the actual execution plan, it only proves that Query Optimizer estimated it all wrong. (i.e.: estimates 1 seek, executed 239827492 seeks)

What is called execution plan is the optimized version of the original parsed query tree. The process involved on the optimization is complex and it is a NP-complete problem. The changes made on the original tree are called optimization and they are done based on the statistics, as well as other variables such as number of CPUs available, etc. The plan does not changes during the execution, unless you consider Adaptive query processing, added on SQL Server 2017; but then this starts another topic

I would recommend that you check the following links understant better how it works:

SQL Server Statistics Basics - https://www.red-gate.com/simple-talk/sql/performance/sql-server-statistics-basics/

Execution Plan Basics - https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/


Setup master-slave replication between DB1 and DB2.
APP1 reads and writes to DB1.
APP2 reads from DB2.
APP2 writes (if it does) to  DB1.

Note that APP2 will read eventually consistent data and it will have to handle writes to DB1 itself. (I do not tend to like master-master replication setup and writes to both ends due to past experiences but it is also an option.)

You said no links but below this link includes steps for setting replication, you might want to refer.


It order to understand your problem and to simulate it I have generated one scenario. First of all I would like to tell how the Base64 works as general.
1) It takes the ASCII text.
2) Converts it to Hexadecimal values(Decimal to Hex conversion).
3) Represents the text in bits(groups as byte).
4) Since the Base64 takes three bytes of data. That is, it takes 24 bits of data and returns as 32 bit encoded characters. So bit should be represented as 6 bits string.
5) Then these 6 bits string will be converted to decimal number.
6) Then it selects the ascii characters(A-Z,a-z,0-1,+ and /) according to the decimal number calculated in step 4.

Now i want to show this practically and also want to shows how the Oracle's Base64 encoding returns RAW value.
Lets say we want to encode the text 'ABCD'.
1) Lets convert this to RAW data which is hexadecimal values.

SQL> SELECT UTL_RAW.CAST_TO_RAW('ABCD') FROM dual;
41424344

We have got the hex values of 'ABCD' that is '41424344'.
2) Lets encode this hex using Base64. In Oracle we have got UTL_ENCODE package to encode it, which takes RAW and returns encoded value as RAW.

SQL> SELECT UTL_ENCODE.BASE64_ENCODE('41424344') FROM dual;
51554A4452413D3D
Let me explain how we have got this values according the encoding steps that i have mentioned above.
1) Our text is 'ABCD'.
2) Hex representation is '41424344'.
3) Binary representation of text grouped by bytes.

41->01000001 42->01000010 43->01000011 44->01000100

4) Bit representation of text grouped by 6 bits.

010000 010100  001001  000011  010001  000000

(Adds '=' sign to output text as we added 0s to make it 6bits)
5) Decimal representation of 6 bits representation of text.

010000->16  010100->20  001001->9  000011->3  010001->17  000000->0

6) Now lets select from base64 characters(A-Z,a-z,0-9,+,/  altogether 64 characters:0-63) according to the decimal values.

16->Q, 20->U, 9->J,3->D,17->R,0->A

Output:QUJDRA==
But Oracle Base64 encoding returns this text as RAW(Hex Values). In order to get encoded string as text instead of RAW we can cast it to VARCHAR2.

SQL>SELECT UTL_RAW.CAST_TO_VARCHAR2('51554A4452413D3D') FROM dual;
QUJDRA==

If we convert the ASCII value of these text into hex we get the encoded string returned by Base64 encoding.

Character  ASCII  Hex
Q          81       51
U          85       55
J          74       4A
D          68       44
R          82       52
A          65       41
=          61       3D
=          61       3D

Now lets convert this text to hex.

SQL>SELECT UTL_RAW.CAST_TO_RAW('QUJDRA==') FROM dual;
51554A4452413D3D

We get the original encoded string

51554A4452413D3D

Now in order to get my text back to original text 'ABCD', lets decode it.

SQL>SELECT UTL_ENCODE.BASE64_DECODE('51554A4452413D3D') FROM dual;
41424344
SQL>SELECT UTL_RAW.CAST_TO_VARCHAR2('41424344') FROM dual;
ABCD

Conclusion: So if we want to get encoded value as text instead of Hex we can use UTL_RAW.CAST_TO_VARCHAR2 which simply converts Hex to varchar2.