How can I troubleshoot issues with dynamic registration?

Listener registration in the database can be traced with:

alter system set events='immediate trace name listener_registration level 3';

Disable tracing when it is not needed anymore:

alter system set events='immediate trace name listener_registration level 0';

PMON registers to addresses defined in local_listener and remote_listener parameters. If you have an invalid address defined there, then it will not be able to register the instance in the listener.

If you have nothing defined there, then it tries to register on the local machine using the default 1521 port.

You could also try to set your local_listener as:

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.13.134)(PORT=1521))';
alter system register;


How to create read-only user for schemas which can be created in the future?

You can modify template1 to grant read only permissions to all future schemas.

/connect template1
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;

Now all future CREATE DATABASE statements will also have the read only permissions applied to them.

CREATE DATABASE dbname; // is the same as CREATE DATABASE dbname TEMPLATE template1;


Get Updated_At for any change in row on Sybase?

From your question I assume that you cannot create the updated_at column in that customer table by yourself (maybe because that table "belongs" to the software vendor).

Logging the creation or update timestamp in your additional table customers_getting_updated is simple. You need two triggers on the customer table, one for (after!) the creation and one for (after!) the update.

The first trigger fires after a customer record has been inserted. It simply inserts a record into the customer_getting_updated table with the (new) customer_id from the customer table and sets the current timestamp.

CREATE TRIGGER customer_inserted AFTER INSERT
ORDER 1 ON customer
REFERENCING NEW AS new_cust
FOR EACH ROW
BEGIN
  INSERT INTO customer_getting_updated ( customer_id, updated_at )
  VALUES (new_cust.customer_id, CURRENT TIMESTAMP);
END;

The second trigger fires after an existing customer has been updated. The trigger checks whether or not there is already a "timestamp logging entry" for that customer id. If existing it updates the timestamp, if missing it creates a new logging record (identical to the after insert trigger).

CREATE TRIGGER customer_updated_at AFTER UPDATE
ORDER 1 ON customer
REFERENCING NEW AS new_cust
FOR EACH ROW
BEGIN
  IF EXISTS(SELECT 1 FROM customer_getting_updated WHERE customer_id = new_cust.customer_id) THEN
    UPDATE customer_getting_updated SET updated_at = CURRENT TIMESTAMP
    WHERE customer_id = new_cust.customer_id;
  ELSE
    INSERT INTO customer_getting_updated ( customer_id, updated_at )
    VALUES (new_cust.customer_id, CURRENT TIMESTAMP);
  END IF;
END;

A short note on before or after triggers:

If you want to manipulate the new (or updated) record itself you typically use a before trigger and change values in that record before they are written to the table.
If you want to do something else - often with values from the new or updated record - you use an after trigger.


How restore a specific database from backup using mongorestore command

To restore a single database you need to provide the path to the dump directory as part of the mongorestore command line.

For example:

# Backup the training database
mongodump --db training

# Restore the training database to a new database called training2
mongorestore --db training2 dump/training

The --db option for mongodump specifies the source database to dump.

The --db option for mongorestore specifies the target database to restore into.


Have never encountered anything remotely like this and suspect something has been lost in translation. I will of course apply the usual caveat for licensing questions, "don't take anything you read here as gospel, check it with your licensing partner, and Microsoft, and call both back to speak to a different individual. There's a good chance you'll get a different answer the second time around".

You get a key, you install the software. Any CPU limitation is a product of the physical environment or version of SQL Server. For standard edition that is:

  Limited to lesser of 4 Sockets or 16 cores [source]

A volume license agreement is essentially a discount pricing arrangement, it does not involve a different installation, key distribution policy or CPU utilisation limitation:

  If you plan to buy five or more software licenses, Microsoft offers
  price advantages for volume purchasing. Discover how Microsoft Volume
  Licensing can help your organisation realise its goals and find out
  what customers and analysts have to say about it.
  [source]

Licensing is always complex. Microsoft licensing is at least as complex as other vendors. Unfortunately, my experience has been that they have not yet mastered the art of training their own, or their resellers employees about license models.

There is much fun to be had by locking a Microsoft licensing specialist in a room with a reseller specialist and watching them argue about the finer points of SQL Server or MSDN license arrangements. If you think SQL Server is a minefield, try MSDN.

NB: The "helpful" diagram at the top of the article explaining edition compute capacity limits neatly demonstrates why folk lose their way.


Help - SQL query // Extracting from XML // getting error if no data found

Have you tried checking if column5 is NULL in the SELECT list instead of in the WHERE clause? I presume that feeding NULL to xmltype() is the source of your errors.

Select Column1,Column2,Column3,Column4,
       CASE WHEN column5 IS NOT NULL
         THEN extractvalue(xmltype(column5), '/PymtInit:notifyOutwardCrdtTrnsfrStatusReq/')
         ELSE 'No Value Yet'
       END as "Response",
       Extractvalue(Xmltype(Responsemsg))
From Table.Name
where column5 is not null;

I've also added a closing parenthesis to the sixth column in the SELECT list; you may want to give that column an alias as well.

Note: not a frequent Oracle user, and have no experience with their XML tools; just applying standard SQL. Also, of course, untested.


SQL Query: Selecting a person who is a member of all existing groups

There are several ways to get to this answer.  I'll tackle it in two parts.  The first is to find out how many groups there are.  This is a simple summary query:

select count(distinct GroupID)
from MEMBER

The second is how many groups one person is in.  This is similar:

select
  PID,
  COUNT(*)
from MEMBER
group by PID

Next you want to tie these together

select
  PID,
  COUNT(*)
from MEMBER
group by PID
having COUNT(*) = (select count(distinct GroupID)
                   from MEMBER)

Depending on which RDBMS you're using (Oracle, DB2, MySQL) there may be other syntaxes or layouts which make this faster, prettier or otherwise more desirable.


Insert where not already available for FK?

You can just insert into table where fk not in the criteria you specified. This all can be a single insert statement. No need to do RBAR.

It sounds like you're looking to insert where you have fk records that do not have a corresponding "log" record, then insert.

insert into table
select distinct name, fk
from table t
where fk not in (
  select distinct fk
  from table t1
  where name like 'log%'
    and t1.fk is not null)

This is really crude but... just want to make sure I understood you correctly.

Also, in the future, if you do need to do a RBAR operation...

while ((select count(1) from @nolog_ids) > 0)
begin
    declare @currentid int
    set @currentid = (select top 1 ids from @nolog_ids)

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid
end

I prefer to use this instead... more readable and potentially faster.

declare @currentid int

while exists (select * from @nolog_ids)
begin

    select top 1 @currentid = ids from @nolog_ids

    insert into table (name, fk)
    select 1, 'log', @currentid

    delete from @nolog_ids where ids = @currentid

end


How to size SQL Server VM so that it is comparable to bare metal primary SQL server?

This is the online Microsoft guide about it:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices
interesting to point out:

If you are creating a new SQL Server on Azure VM and are not migrating
a current source system, create your new SQL Server VM based on your
vendor requirements. The vendor requirements for a SQL Server VM are
the same as what you would deploy on-premises.

Not to mouch after all. Said that I believe you have to do some test, but how?
You have to capture the on premise workload and reply it to an the azure vm and check the results.
A goo tool for doing it is this:
https://github.com/spaghettidba/WorkloadTools/wiki


Since you're in full recovery and just need to keep the "support" DB up-to-date from production as-needed or once-a-week, then is log shipping not an acceptable option? You could have it restore once-a-day during off hours if it being in standby for the triage of issues would work. The transaction logs could copy over every hour, or whatever you feel is appropriate.

Otherwise, you may be able to setup a custom SQL Agent job (with TSQL scripting or maybe xp_cmdshell) to copy over the transaction logs from prod but have a disabled SQL Agent job (on support instance) to kick off ad hoc to as-needed to apply the copied over transaction logs to the support DB. This would allow you to have ad hoc log shipping essentially without standby assuming you setup the restores correctly to not be in standby.

The once-a-week backup file from prod could be copied over to the "support" server and overwrite or delete the existing file depending on naming conventions. You could then schedule that with SQL Agent job to do the once-a-week full backup and still let the transaction logs copy over without restore until you need to kick it off ad hoc as-needed.

You would setup a job to purge TRN files that are older than a certain time frame once the full backups complete so the old ones are deleted from the "support" server. This may be step 2 of your "support" full backup file restore and just plugin the age of files to purge as-needed. See below example and change arguments and variables as-needed per your requirements and test of course.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1


Tempdb log growth 3x as slow on new server, SP does not perform

While determining why the growth is taking longer on the new server is a task that can depend a lot o your environment, as noted by David Spillett's comment, it's likely that you might have sized the tempdb in a way that is not optimal for your server, causing auto growth to be triggered frequently.
As Brent Ozar says on his Cheat Sheet: How to Configure TempDB for Microsoft SQL Server:

configure one volume/drive for TempDB. Divide the total space by 9,
and that’s your size number. Create 8 equally sized data files and one
log file, each that size. Presto, the drive is full and your TempDB is
configured for easy performance.

Sizing tempdb properly is also listed on the Optimizing tempdb performance in SQL Server doc as a good practice:

Preallocate space for all tempdb files by setting the file size to a
value large enough to accommodate the typical workload in the
environment. Preallocation prevents tempdb from expanding too often,
which affects performance. The tempdb database should be set to
autogrow to increase disk space for unplanned exceptions.

Finally, if your new server has more than 64 CPUs, the doc on Best Practices for running SQL Server on computers that have more than 64 CPUs says:

Do not rely on autogrow to increase the size of the transaction log
file. Increasing the transaction log must be a serial process.
Extending the log can prevent transaction write operations from
proceeding until the log extension is finished. Instead, preallocate
space for the log files by setting the file size to a value large
enough to support the typical workload in the environment.

Considering mainly the point about preventing transaction write operations, if the SP that is not performing does any write operation, it would be affected by the autogrow.
So, even though this advice does not answer your question about the reason the growth is taking longer to be performed, I thought it was worth mentioning it.


How to update a UID throughout the database?

Our database makes use of uniqueidentifier and we have the need to update that value everywhere it's found in our system.

Question: Why?
The whole point on using an arbitrary, sequential identifier is that they should never change them over the entire lifetime of a record, from the point they're created until they are finally deleted.  If you do find you need to change them, then something is badly wrong with the way you're using them.
Also, don't query the "largest" value to find out the id value that MySQL generated - it may not work correctly in a multi-user environment.  Use the provided LAST_INSERT_ID() function instead:
INSERT INTO Users( name )
VALUES( 'Jim Bob' );

INSERT INTO Tasks( name, userId )
VALUES( 'Some task', LAST_INSERT_ID() );   <-- gets the last inserted User


You don't actually have to specify the ODBC driver as long as the driver is in the library path.

[MyIQboxOfDoom]
CommLinks=tcpip(ip=myiqboxofdoom;port=5000;DOBROADCAST=NONE;VERIFY=NO)
AutoStop=no

and connect using

dbisql -c "dsn=MyIQboxOfDoom;uid=myuser;pwd=mypass" -nogui


Adding a primary key to an existing table in mysql is taking time

Yes, creating a PK on a big table takes time.

When you don't have PK (more precisely a clustered index - the two is the same in MySQL using InnoDB) the table is called heap and the DBMS adds a 'row identifier' to each row to track them. This ID is used to identify records in indexes and other places.

When you have a clustered index (PK in MySQL), the PK key will be the row's identifier and all indexes are using the clustered key to identify records.

When you modify the type of the table (heap to clustered), all indexes and the DBMS should rebuild the table and reorganize the data stored in it.

Best practices:

Drop ALL nonclustered indexes before you change (or drop/create) the clustered key
Stop all processes which are using that table to prevent deadlocks and long wainting (do it in your maintenance window)
If you want to change (or drop/create) the clustered key on a huge table, create a new table with the required primary ky (clustered index), then copy all data from the original table, then replace the new table with the old by renaming. When this done, you can add additional indexes to the table.

  how long it will take to complete?

It depends on the server and the processes runing next to the alter on the server. In short, we simply can not say a number how long it will take.


How to put this particular constraint on this link table in MySQL?

The solution is to add the group category column in the StoreGroups table and then add a unique constraint on (storeId, groupCategory). The table definitions would be like this:

CREATE TABLE Groups
  ( groupID INT NOT NULL PRIMARY KEY
  , name VARCHAR(255) NOT NULL
  , groupCategory NOT NULL VARCHAR(30)
  , exprireDate DATE NULL
  , UNIQUE (groupCategory, groupID)       -- this is needed for the FK below
  ) ;

CREATE TABLE StoreGroups
  ( storeID VARCHAR(16) NOT NULL
  , groupID INT NOT NULL PRIMARY KEY
  , groupCategory NOT NULL VARCHAR(30)
  , PRIMARY KEY (storeId, groupID)
  , FOREIGN KEY (groupCategory, groupID)
      REFERENCES Groups (groupCategory, groupID)
  , UNIQUE (storeID, groupCategory)              -- your rule enforced
  ) ;

The UNIQUE constraint enforces that there cannot be 2 StoreGroups with the same storeID and the same groupCategory.


What is the best way to create a table with 800 columns in a single script?

The best way to run a very long script is not to load it into the editor, any editor.

Just reference it via @ and run it

So @my_script.sql and execute that.


Which Database to choose: MySQL or Oracle

Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are.  I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully.

In your specific case, however, there may be reasons to look specifically at PostgreSQL. The major issue is programmability and advanced indexing but there are other reasons too (basically in InnoDB your table is contained in the pkey index, while in PostgreSQL you can do a physical-order scan of the table (and this has advantages when pulling a lot of rows).  Major disadvantages might include not liking the binary type interfaces (and often saving/retrieving the values can use more memory than I would like).  As for filesystem I know a lot of people who are very happy with XFS and also with ZFS.

Finally one specific tradeoff here is the Pg 8k data page size.  It sounds like your time series would be larger than this, and so would be TOASTed (moved to extended storage).  This speeds up many queries, but it also slows down retrieving the specific time series slightly because extra physical files must be scanned.

As for programmability, basically you could write functions that can look inside a time series for specific useful information and then you can index the output of this function.  Consequently this can be used to speed up searches because the function doesn't have to be run.


I'm trying to restore single database from mysqldump all-databases with different name

After much Googling and messing about :-) I succeeded in getting it done ultimately due to this post here. It's quite simple and logical really - I just went down a few blind allies. Check out the answer by user1201232 and not the accepted answer - with a slight modification from me (experimenting!).

You get your dump of your entire server as you have done.

./bin/mysqldump -S ./mysql.sock -u root -pdba --all-databases > serverdump.sql

Take a backup of this original dump (see why later).

Then, you perform a sed on the file. I have a schema called gnarly which I renamed to xyzg in the dump file. This is a good reason to take a backup, otherwise, if your schema name is some common word, it might mess up other restores that you may wish to perform.

sed -i 's/gnarly/xyzg/g' serverdump.sql

Then create a schema called xyzg on the server

./bin/mysqladmin -S ./mysql.sock -u root -pdba create xyzg

Then restore the backup "fooling" mysql into thinking it's restoring an already made one.

./bin/mysql -S ./mysql.sock -u root -pdba xyzg < serverdump.sql

Connect and check your tables.

+--------------------+
| Tables_in_xyzg     |
+--------------------+
| customer_addresses |
| customers          |
| order_items        |
| order_payments     |
| order_status       |
| orders             |
| recurring_orders   |
| status_types       |
+--------------------+

A couple of selects showed that my data was indeed present. Et voilà!

Forgot to mention that MySQL Workbench has a way of doing this - go through the Data Import/Restore and there's a "New" button you can use to select a different schema name. My setup is custom and I wasn't able to get this to work, but if you have a default setup, it should work (I believe).

Of course, there's always the good old manual editing of the dump file. I did some further reading and found this is not as difficult as it might appear.

Using the example above, changing schema gnarly to xyzg.

Get your dump (serverdump.sql) of all schemas as per above.

From the shell prompt

$> grep "USE \`gnarly\`"
USE `gnarly`;
USE `gnarly`;
$>

only occurs twice. Use vi (or the editor of your choice) to change gnarly to the string of your choice, i.e. xyzg.

[EDIT]

Better way again - no need to edit anything.

Just do

sed -i 's/USE \`gnarly\`/USE \`xyzg\`/g' serverdump.sql

No need to use vi - or worry about changing anything else in the file.

Create a schema xyzg as per above and follow the other steps.

Might be useful if sed is overkill. From here.


Below steps will help you :

Create full backup with compression
Ship the compressed backup to the new data center
Configure database mirroring in async mode
When you are ready for failover, change the mirroring mode to sync mode.
Initiate failover to secondary (new data center)
  alter database db_name set partner failover
change the web.configs for your application to the new server.
Follow the steps from my answer - it covers migrating your logins/users/jobs/ssis packages and other post restore steps.

  If database mirroring is on can we start to read/write to the target of the mirror in preparation for turning the target into the primary database.

When you initiate a failover, this is where your downtime is - your application end users will be getting an error and they will be able to reconnect to the new data center database server (provided you have changed the conf setting).

I am assuming that you are using enterprise addition since standard edition of sql server has limitations on database mirroring.

Also, database mirroring is deprecated and you should look into moving to AlwaysON (depending on edition and version of sql server you are using).


Why is cost threshold for parallelism ignored?

Anyone ever see Cost Threshold for Parallelism being ignored?

It is not being ignored. During the compilation process, the optimizer first considers a serial plan. If the estimated cost of that plan exceeds the Threshold, the optimizer goes on to look for a parallel plan. If the resulting parallel plan is costed below the best serial one, it will be chosen.

So, the parallel plan will have a lower cost that the serial one (which you cannot see). It is perfectly possible for the final parallel plan to have an estimated cost below the Threshold - the point is the best serial plan candidate exceeded the Threshold.

An example can be seen in my blog post on parallel plan bitmaps.


How to reduce sysaux tablespace size with Audit Trail Unified enabled

SQL> select owner||'."'||table_name||'"' t from dba_tables where table_name like 'CLI_SWP$%';

T
-----------------------------
AUDSYS."CLI_SWP$18c7c2a9$1$1"

SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";
truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1"
*
FEHLER in Zeile 1:
ORA-55941: DML- und DDL-Vorgänge sind auf Tabelle
"AUDSYS"."CLI_SWP$18c7c2a9$1$1" nicht zulässig

SQL> shutdown immediate;
…
ORACLE-Instanz heruntergefahren.
SQL> startup upgrade;
…
Datenbank geöffnet.
SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";

Tabelle mit TRUNCATE geleert.

SQL> shutdown immediate;
SQL> startup;


You can assign a table alias when you join tables, just like you do with the column names. Not only does it save you a lot of typing, but it makes the code a little more readable, and it solves the problem of joining the same table twice.

SELECT p1.name AS "primary",
       relationships.related_person_id AS relatedto,
       p2.name AS related_name
FROM people AS p1
INNER JOIN relationships AS r
    ON p1.name_id=r.primary_person_id
INNER JOIN people AS p2
    ON p2.name_id=r.related_person_id
ORDER BY p1.name_id;

In the example above, I've assigned p1 and p2 to the people table and r to the relationships table.

Note: some database platforms don't like the "AS" keyword. You can skip "AS" alltogether if you want, like so:

...
FROM people p1
INNER JOIN relationships r
...


Postgres 9.6 export the database structure as pdf

You need a Entity Relation Diagram (ERD) generator. The most popular and advanced one that I know of that is FOSS is SchemaCrawler.

It does everything you want anyway. Though the diagrams are generated with Graphviz (so don't expect Visio quality).

You may want to go scavenger hunting through different graphviz viewers. This one using d3 seems to be pretty decent.


Improving performance for sum and group by with large datasets

The big issue is "inflation-deflation" when you do JOIN + GROUP BY.

Think of what JOIN does -- it generates an intermediate table with teams * types/team * users/type/team * results/...  That is, that table is much bigger than any of the original tables.

Then it does the GROUP BY do deflate that back to one row per team.

Usually the combination gives COUNTs and SUMs that are bigger than expected.  (Did you check?)  And it takes a long time.

So, the trick is to use subqueries to avoid any single query with both JOIN and GROUP BY in it.  (The combo is ok if the JOIN is known to be 1:1, which seems to be the case for teams and types, but not the other JOINs.)

There are two ways to do such subqueries:

SELECT
        ( SELECT SUM() FROM ... WHERE ... ) -- correlated subquery; single result only
    FROM ...

The subquery is effectively a GROUP BY without saying it, but it boils down to a single value, thereby avoiding the "inflation".

and

SELECT ...
    FROM ( SELECT COUNT(), SUM(...) FROM ... GROUP BY ...
         ) AS x  -- tmp table for some of the data; no JOIN
    JOIN ...

The subquery "deflates" some of the data before getting to the JOIN.

Caution:  Using multiple subqueries of the second kind is slow because of lack of indexes:

SELECT ...
    FROM ( SELECT ... ) a
    JOIN ( SELECT ... ) b ON ...

5.6 helps somewhat because it discovers and adds an index.


SQL Server Availability Group LeaseTimeout and slow IO

how can slow IO-requests cause a lease-timeout? Can to many pending IO-requests cause a lease-timeout?

No, slow I/O requests can't cause a lease timeout directly.
However, if the server is completely overloaded (CPU at 100%), than can cause pending I/O requests and lease timeouts.  The default lease timeout is 20 seconds, and your pending I/O is 26 seconds.  High CPU or some other server / OS level problem is more likely the issue here.
Another cause is that SQL Server encountered a serious error, and is generating dump files (which causes the process to pause, potentially long enough for WSFC to think the lease timed out).
See the documentation for a couple more possibilities:

OS not responding, low virtual memory, working set paging, generating dump, pegged CPU, WSFC down (loss of quorum)

You should review the SQL Server error log to see if there are dumps being created.  If you have monitoring from the time of these incidents, you could also check for maxed out CPU.


Assuming that it works like PostgreSQL in this case:

This is a serialization error that you get because you are using the SERIALIZABLE transaction isolation level.

That is nothing to worry about, just repeat the transaction.


How can I know the progress when importing data into MySQL using LOAD DATA INFILE?

Sort the incoming file by the PRIMARY KEY; this will speed up the LOAD.  Don't have any secondary keys during the load.
There is no way (that I know of) to watch the progress in 8.0.
The .ibd file size is not a reliable indication -- you can't easily predict how big it will eventually be.  Datatypes, data values, PK, other indexes, etc, impact the size.
Also, that file will grow in bursts.
Having just two BIGINTs (8 bytes each), the table might be about 40-60 bytes per row.  The smaller range might be about right if the data is in PK order; the larger if not.
I hope the query does not timeout.
The process is probably I/O-bound, indicating that nothing you could do would speed it up.


Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

If you have a unique key that is not null, it gets promoted internally to be the clustered key (or if you have more than one that matches this criteria - the first one does.)

If you don't have a unique key that is not null, a hidden key is created that is 6 bytes in length.

There is no way to specify the key to cluster around, and there is no way to not have a clustered index.


Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?

Please pay attention. The answer from Julien is correct. BUT if you attach the database to an SQL Server 2008 instance it will be upgraded! If the application can't handle SQL Server 2008 and needs SQL Server 2005 you're in trouble. Take an extra step and make a backup.

Furthermore you have to create the needed logins on the new server. You might want to attach the old master under a new name for looking up the details.


Can upgrade from MariaDB 5.5 to MariaDB 10.3 directly?

The whole jump it is possible (depends on your data structure) but is not recommended and based on official documentation is not supported.

The best option is to perform and step-by-step IN-PLACE upgrade so you will only do a mysqldump before start to backup everything then just install every version and run mysql_upgrade then continue with next version and so on.


Encrypt application name in c# and decrypt in SQL Server

I don't believe this is possible natively in SQL Server since MD5 is a one-way (apparently loss-full) hash. Here's a good StackOverflow answer that goes over MD5 in general.
SQL Server has a built in function to generate hashes called HASHBYTES() which maybe you could leverage (and one of the other hashing algorithms that are loss-less) to potentially generate a hash string that is reversible. But I have feeling even that might not be possible to do.


How can I backup/dump MYSQL database with foreign key constraints?

When using MyISAM as the engine of your table, mysql will "ignore" the foreign key constraint and let you go on, this is for compatibility reasons throughout all mysql engines.
The recommended engine to use foreign keys is InnoDB, and honestly, for 99% of cases InnoDB is better.
You can easily change all your database to use InnoDB instead of MyISAM:

add default_storage_engine = InnoDB to /etc/mysql/my.cnf (or equivalent)

modify existing tables with alter table my_table engine=innodb

to modify all tables you can try this more complex statement:
SELECT concat_ws(' ', 'alter table', t.table_name, 'engine=innodb')
FROM information_schema.tables t
WHERE t.table_type = 'base table'
  AND t.table_schema = 'my_db'
  AND t.engine != 'innodb'

Now all your foreign keys should be respected and when you use mysqldump you will se them.


How to structure database in order to lookup and store post ID's in one PostgreSQL command?

you can use join table for example

select p.id, p.userid, p.content
from post p
join userinfo ui on p.userid=ui.id
order by created_date desc
limit 5

or you can use CTE feature (imagine two table as one table)

Reference
https://www.postgresql.org/docs/11/tutorial-join.html
https://www.postgresql.org/docs/11/queries-with.html


GRANT SELECT ON SEQUENCE succeeds, but to no effect?

The manual:

  The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role.

Nothing additional will show up there after GRANT SELECT .... Try instead:

GRANT USAGE ON SEQUENCE public.some_id_sequence TO new_role;

.. if that's, indeed, what you want. The SELECT privilege has limited use for a SEQUENCE, it allows currval() and lastval().
Typically, you want the USAGE privilege which also allows the crucial nextval() in addition to the above. To work with a serial column, for instance. See:

Explicitly granting permissions to update the sequence for a serial column necessary?

So why do you see this confusing line in your test after granting SELECT?

live@live ~ => select * from information_schema.role_usage_grants limit 1;
┌──────────┬──────────┬────────────────┬───────────────┬──────────────┬─────────────┬────────────────┬──────────────┐
│ grantor  │ grantee  │ object_catalog │ object_schema │  object_name │ object_type │ privilege_type │ is_grantable │
├──────────┼──────────┼────────────────┼───────────────┼──────────────┼─────────────┼────────────────┼──────────────┤
│ old_role │ old_role │ old_role       │ public        │ some_id_seq  │ SEQUENCE    │ USAGE          │ YES          │
└──────────┴──────────┴────────────────┴───────────────┴──────────────┴─────────────┴────────────────┴──────────────┘

The owner of an object has all privileges on it automatically. Hence pg_class.relacl starts out as NULL - signifying default privileges. Once that owner grants privileges to another role, his own privilege is entered explicitly in addition - thus popping up in the information schema views as well. Or, quoting the source:

  If the “Access privileges” column is empty for a given object, it
  means the object has default privileges (that is, its privileges
  column is null). Default privileges always include all privileges for
  the owner, and can include some privileges for PUBLIC depending on the
  object type, as explained above. The first GRANT or REVOKE on an
  object will instantiate the default privileges (producing, for
  example, {miriam=arwdDxt/miriam}) and then modify them per the
  specified request.

Generally, to debug non-trivial stuff, I'd rather look at pg_catalog tables, which are the primary source of truth. pg_class.relacl in this particular case:

SELECT relnamespace::regnamespace, relname, relacl
FROM   pg_class
WHERE  relname = 'some_id_sequence';

Or \z (short for \dp) in psql:

\z some_id_sequence

Assuming old_role is the owner, you should see:

{old_role=rwU/old_role,new_role=r/old_role}  -- after GRANT SELECT...
{old_role=rwU/old_role,new_role=U/old_role}  -- after GRANT USAGE ...
{old_role=rwU/old_role,new_role=rU/old_role}  -- after granting both

And NULL (empty in psql) before granting anything.

Your added output shows that new_role actually has the SELECT privilege, you have just been looking up the wrong view. Seems like new_role is lacking privileges on the public schema - which are normally given to PUBLIC by default.

Aside:  IDENTITY columns in Postgres 11 or later avoid the fuss. Those use sequences just as well, internally, but implicitly owned by the IDENTITY column and with the appropriate privileges automatically. Then you need no separate grants for sequences. See:

Auto increment table column


The example below uses a CTE to find the gaps in your TimeScheduleId sequence.  The field  HasPreviousItem identifies if a row is the first in a sequence and RowNum is used to number all of the records.
To find the start dates/times select for records where  HasPreviousItem = 0.

The Duration assumes that each record is a 15 minute block of time.  The cross apply subquery finds all records for the same ApptCode in the same block.  That is records in sequence not interuppted by a HasPreviousItem value change.
If the "duration" of a record is not always 15 minutes an alternate method to find the duration of an appointment would be needed.

CREATE TABLE ApptTemp (
     ProviderId INT NOT NULL DEFAULT 196
    , OfficeId INT NOT NULL DEFAULT 7
    , ApptCode CHAR(1) NOT NULL
    , Description VARCHAR(20) NOT NULL
    , ScheduleTimeId INT NOT NULL
    , ScheduleTime DATETIME NOT NULL
    , ApptDate DATETIME NOT NULL
);

GO

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES ( 'L', 'Lunch', 49, '1900-01-01 12:00', '2014-03-20 12:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 50, '1900-01-01 12:15', '2014-03-20 12:15'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 51, '1900-01-01 12:30', '2014-03-20 12:30'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 52, '1900-01-01 12:45', '2014-03-20 12:45'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 33, '1900-01-01 08:00', '2014-03-20 08:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 34, '1900-01-01 08:15', '2014-03-20 08:15'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 41, '1900-01-01 10:00', '2014-03-20 10:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 42, '1900-01-01 10:15', '2014-03-20 15:00'  );

WITH ApptCte
AS (
SELECT ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate, ProviderId, OfficeId

        , CASE
            WHEN  EXISTS
                (
                SELECT  *
                FROM     ApptTemp AS A2
                WHERE  A2.ScheduleTimeId = A1.ScheduleTimeId - 1
                    AND A2.ApptCode = A1.ApptCode
                )
                    THEN 1
            ELSE 0
        END AS HasPreviousItem

        , RowNum = Row_Number() OVER(ORDER BY ScheduleTimeId)

FROM ApptTemp AS A1

)

SELECT
     ApptDate
    , Description AS ApptDetail
    , OfficeId
    , ProviderId
    , Duration.Total AS Duration

FROM ApptCte AS MainCte
    CROSS APPLY
        (
            SELECT (COUNT(*) + 1) * 15 AS Total
            FROM ApptCte AS CountCte
            WHERE CountCte.ApptCode = MainCte.ApptCode
                AND CountCte.HasPreviousItem = 1
                AND CountCte.RowNum > MainCte.RowNum
                AND NOT EXISTS ( SELECT *
                                    FROM  ApptCte AS InnerCte
                                    WHERE InnerCte.ApptCode = CountCte.ApptCode
                                        AND InnerCte.HasPreviousItem = 0
                                        AND InnerCte.RowNum != MainCte.RowNum )
        ) AS Duration

WHERE HasPreviousItem = 0

ORDER BY ApptDate;


Postgresql query is very slow for my big table

Counting rows is slow, because all rows of the table have to be visited.
Counting id is even slower, because PostgreSQL first has to check if id is NULL or not (NULL values are not counted).

There are a few options to speed things up:

Use a more recent version of PostgreSQL.

Then you can get parallel query, which will make the execution even more expensive, but faster.
Use the index on id and keep the table well vacuumed.

Then you can get an index only scan.
Use an extra table with a counter that gets updated on every data modifying statement on the large table using a trigger.

Have a look at my blog post for an in-depth discussion.


MySQL Error wth Relay logs - Could not open relay logs file for logging

Raised the issue to Oracle Support.

The issue is a bug where Oracle development team is still working on it.

Issue: MySQL Enterprise Backup tries to backup all the binlogs and during that time, internal mechanism cant take backup of binlogs and crashes the DB. Oracle development is still working on the issue.

Workaround:
Avoid taking backup of binlogs and relaylogs in both incremental and full backup.


MySQL Union not showing 2nd query result set

Since UNION lines up the columns, not the column names, you cannot directly go from what you have.  However, here is one technique:

Instead of

SELECT * FROM (
    SELECT x, ss     FROM...
    UNION
    SELECT x, httpul FROM...
)

do

SELECT  x,
        MAX(ss) AS ss,  -- MAX() will ignore the NULL from the 'other' table
        MAX(httpul) AS httpul
FROM (
    SELECT x,         ss, NULL AS httpul  FROM ...
    UNION
    SELECT x, NULL AS ss,         httpul  FROM ...
)


What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy?

This is actually an NP-complete problem

You won't find an optimal way to pack your bins, but you can find a good way

Here's Joe Celko's take on it:

https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/


How can I stop SQL from validating an OPENROWSET query when its not going to be executed?

There are a couple of options available.  One of them is to use dynamic SQL for your insert calls.  That way, validation doesn't actually occur until the execution occurs.  For your example, it could look like:

declare @sql nvarchar(max);

IF (@flagA = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT A, B, C
    FROM (
        SELECT *
        FROM OPENROWSET(
            ''Microsoft.ACE.OLEDB.12.0'',
            ''Text; HDR=Yes; Database=\\server\tmpFiles'',
            ''SELECT * FROM FileA.txt'')
    ) as T
';
END

IF (@flagB = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT D, E, F
    FROM (
        SELECT *
        FROM OPENROWSET(''Microsoft.Ace.OLEDB.12.0'',
        ''Excel 8.0;Database=\\server\tmpFiles\FileB.xls'',
        ''SELECT * FROM [Sheet$]'')
    ) as T
';
END

IF (@flagC = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT G, H, I
    FROM (
        SELECT *
        FROM OPENROWSET(''Microsoft.Ace.OLEDB.12.0'',
        ''Excel 8.0;Database=\\server\tmpFiles\FileC.xls'',
        ''SELECT * FROM [Sheet1$]'')
    ) as T
';
END

execute sp_executesql @sql;

In this case, validation wouldn't occur until runtime.  The downside is that troubleshooting gets harder because you're now trying to debug dynamic SQL.

You might also be able to merge the several separate statements into one.

A second alternative is to split the call out into separate procedures.  I would put this at a distant second-place, mostly because it's easy to abuse this and start pretending that T-SQL stored procedures are equivalent to C-style methods, forgetting that there can be significant performance differences.  Anyhow, move the INSERT statements into three separate procedures and pass in any necessary parameters, like so:

IF (@flagA = 1)
BEGIN
    exec InsertFlagA @parms1;
END

IF (@flagB = 1)
BEGIN
    exec InsertFlagB @parms2;
END

IF (@flagC = 1)
BEGIN
    exec InsertFlagB @parms3;
END

You couldn't do this with a temp table, so that might throw this option out unless you had a permanent "temporary" table available (or used a global temp table).

If you did want to use a temp table, you could change the exec statement to insert into #tmpData exec InsertFlagA @parms1.


AWS Aurora running out of freeable memory and crashing

Rate Per Second=RPS - Suggestions to consider for your AWS Aurora Parameters Group

log_output=TABLE,FILE  # from TABLE so you can review your Error Log FILE after crash
log_warnings=2  # from 1 to include aborted_connects and other info in your Error Log
query_cache_min_res_unit=512  # from 4096 to conserve QC RAM used
query_cache_size=64M  # from ~ 443M to reduce CPU cycles used for QC management
innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
thread_cache_size=24  # from 11 to reduce threads_created count
read_rnd_buffer_size=256K  # from 512K to reduce Handler_read_rnd_next RPS of 2,558
read_buffer_size=512K  # from 256K to reduce Handler_read_next RPS of 29,608
tmp_table_size=64M  # from 16M to expand capacity
max_heap_table_size=64M  # from 16M to reduce created_tmp_disk_tables RPHr of 1,134

if ALLOWED,

innodb_io_capacity=1900  # from 200 to enable higher IOPS to SSD devices

OBSERVATIONS,
innodb_buffer_pool_size adjustment to be considered when innodb_buffer_pool_reads > 50 RPS (you are less than 1 RPS today)
innodb_change_buffering=none    to be reconsidered, review refman details

These Suggestions may help stabilize your instance.

Disclaimer: I am the content author of website mentioned in my profile, Network profile where we offer FREE downloadable Utility Scripts to assist with improving performance, more suggestions, contact info.


How to convert numeric values to text in sql for select query

Once you have removed the [ and ] you can use string_to_array() to convert the list to an array of integers. This can be used directly in the where clause:

select *
from mytable
where numeric_column = ANY(string_to_array(trim('[123, 124]', '[]'),',')::numeric[])


Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause.

Your second query can be rewritten without derived tables by simply moving that condition to the on clause:

select x.*, y.*
from factI as x
left join factI as y on  x.tickedId = y.tickedId
                     and y.TransType = 'used'
where x.TransType = 'sold' ;


How can I test a constant boolean expression?

You can use

SELECT (4 + 5 = 9) AS expression_result ;

It will either give you 1 (TRUE), 0 (FALSE) or NULL (UNKNOWN).

If you prefer string output instead of numbers:

SELECT CASE (4 + 5 = 9)
         WHEN TRUE  THEN 'TRUE'
         WHEN FALSE THEN 'FALSE'
         ELSE            'UNKNOWN'
       END AS expression_result ;

Alternatively:

SELECT 1 FROM dual WHERE (4 + 5 = 9) ;

It will give you either 1 (TRUE) or no rows in the results (FALSE or UNKNOWN).

I'd prefer to use the first method as it distinguishes between all three possible results of a boolean expression in SQL (TRUE / FALSE / UNKNOWN).


MySQL with Glusterfs Storage vs MySQL replication with hostPath storage

The minimum for an HA setup is 3 nodes, geographically distributed.

If you put all the nodes in a single data center, you are vulnerable to a datacenter crash.  (They do happen.)
If you use Master + Slave(s), failover is not well automated.  See MHA as a possible alternative.
With Galera cluster, you can write to all nodes, thereby helping minimize downtime.  (I don't know about Glusterfs.)


Looking for a simpler alternative to a recursive query

This was hard! I don't know if this is simpler, but at least it doesn't use window function nor produce rows that require being filtered out.

with recursive r(k, n) as (
    with t(k) as (values (1),(2),(3),(4),(5))   -- the data we want to filter
    -- with t(k) as (values (1),(5),(7),(10),(11),(12),(13))
    -- with t(k) as (values (6),(8),(11),(16),(20),(23))
    -- with t(k) as (values (6),(8),(12),(16),(20),(23))
         ,t2(k,n) AS (select k, (select min(k) from t tt where k >= t.k+5) from t) -- precalculate what's next
    select * from (select * from t2 limit 1) x   -- limit 1 directly fails in a union!
    UNION ALL
    select t2.* from r, t2 where t2.k = r.n      -- on each iteration, keep only the value that matches the previous precalculated next one
)
select k from r

Testing

This alternative seems to be less efficient for very small sets, but more or less linear in performance, whilst the original seems to be exponentially more sluggish.

drop table if exists t;
create temp table t(k) AS
with recursive r(n) as (
  select floor(random()*10)::int + 1
  UNION ALL
  select n + floor(random()*10)::int + 1
  from r
  where n < 100000)        -- change to increase or reduce set
select * from r;           -- surprisingly fast! Go PG!
create index on t(k);

with recursive r(n, pri) as (
    select min(k), 1::bigint from t
    UNION
    select k, (rank() over(order by k)) rr
    from r, t
    where t.k >= r.n+5 and r.pri = 1
)
select count(*) from r where pri = 1; -- I aborted it after waiting for a minute

with recursive r(k, n) as (
    with t2(k,n) AS (select k, (select min(k) from t tt where k >= t.k+5) from t)
    select * from (select * from t2 limit 1) x
    UNION ALL
    select t2.* from r, t2 where t2.k = r.n
)
select count(*) from r -- 26" in my server


Copy a SQL Server database from one server to another on demand

I have two servers, Prod and Test, both running SQL Server 2008 RTM. Is it possible to have a PowerShell- or VBScript that could copy a database from one server to another?

You have couple of options :

Use Ola's solution to do backup on PROD server and then schedule the script in SQL Agent job to a. do backup, b. copy the backupfile to test server and c. restore the backup on the test server.
use PowerShell to do the job.

Use PowerShell To Copy A Database To Another Instance
backup and restore your databases using SQL Server PowerShell and SMO.
Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets

  The copy should of course include all contents of the database, with the exception of privileges so I don't lock myself out from editing the destination.

The database backup includes all the data in the database when the FULL backup was taken including the database users. When you restore the database on Test server, make sure to SYNC Orphan users.

  The destination database should be cleared/reset or completely overwritten, so both source and destination are identical.

When you restore the database, you have to use REPLACE option to overwrite the files with the new ones.

  I am slightly familiar with PowerShell, so if this only means connecting and starting a task it should be doable. Or do I have to look for advanced solutions?

You can schedule the script to run at specific time or just run it on demand. Refer to the links that I have suggested above.


SSIS - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

It turns out, I had done something really stupid.

My SSIS package, as it turns out was perfect, I had named the transactions and set "RetainSameConnection" to true. Begin/commit/rollback commands were all specified correctly.

My problem was that one of the stored procedures called as part of the SSIS package had a unnamed transaction in it, causing SQL Server to commit everything once the commit in that stored procedure was called.

Resolution was to modify the stored procedure to name the transaction, so that SQL Server treats that transaction as a nested transaction (In theory, I would have thought the second "begin transaction" statement would do this, but for some reason this was not happening).


Can I find out which database is queried on the most?

You can use sys.dm_exec_sessions. This will only give you the count based on the current activity. You would need to store this historically if you wanted to trend it. Keep in mind that session ids of 50 or less are system sessions.

--By Database
select
    db_name(database_id) DatabaseName
    ,count(session_id) as Session_count
from sys.dm_exec_sessions
where
    session_id > 50
group by
    database_id

--By Login
select
    login_name
    ,count(session_id) as Session_count
from sys.dm_exec_sessions
where
    session_id > 50
group by
    login_name


I tested this example using SQL Server 2000 (MS2000) as the publisher and SQL Server 2008 (MS2008 )as the subscriber.  On the publisher, one database called arp has several tables, but only the table users is included as an article in the publication. This example removes the subscription from the both servers and then puts it back again.

On the Subscriber MS2008

Removed the existing subscription from MS2008

USE arp
GO

EXEC sp_droppullsubscription @publisher='MS2000', @publisher_db='arp', @publication='arp_pub'

On the publisher MS2000

Dropped the Subscription

-- dropped subscription
exec sp_dropsubscription @publication = N'Arp_pub', @subscriber = N'MS2008', @destination_db = N'arp', @article = N'all'

Dropped the Subscriber

-- On MS2000, dropped subscriber from MS2008 after removing it from MS2008
exec sp_dropsubscriber @subscriber = N'MS2008'

Later on, I recreate the subscription.

On the Publisher, MS2000

Add back in the subscriber

use [master]
exec sp_addsubscriber @subscriber = N'MS2008', @type = 0, @description = null, @security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 99991231

exec sp_changesubscriber_schedule @agent_type = 1, @subscriber = N'MS2008', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 99991231
GO

Add back in the subscription

use [Arp]
exec sp_addsubscription @publication = N'Arp_pub', @subscriber = N'MS2008', @destination_db = N'arp', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO

On the Subscriber MS2008

Create the database

CREATE DATABASE arp;
GO

Add the pull subscription

use [arp]
exec sp_addpullsubscription @publisher = N'MS2000', @publication = N'Arp_pub', @publisher_db = N'Arp', @independent_agent = N'False', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0

Add the agent

The @job_login is a ntlm user that has rights to the default snapshot folder.  That user also has access to the publication on the publisher

The snapshot folder is a shared folder accessible by both Publisher and Subscriber.

exec sp_addpullsubscription_agent @publisher = N'MS2000', @publisher_db = N'Arp', @publication = N'Arp_pub', @distributor = N'MS2000', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20120214, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'MS2008\replication_user', @job_password = 'test123', @publication_type = 0
GO

On the publisher MS2000, I ran the snapshot job

The replicated table was once again visible on MS2008.


Can changing database owner affect permissions or role memberships?

If the user is the owner of the database, that user implicitly gets db_owner permission, without having to be explicitly assigned that role.

The owner of the database (a single user) is a different assignment than membership in the db_owner role, but have the same effect. I like to remember it as the owner of the database is an INSTANCE LEVEL setting (ie, it's in sys.databases, but db_owner role membership is a DATABASE LEVEL permission.

A user can be ONLY the (server level) database owner, and not have any permissions within that database. When that happens, changing the database owner to sa would effectively lock that user out of the database they previously owned. This would not revoke permissions from the user, but it would change the effective permissions.

To avoid this problem, you would need to add the user to the db_owner role for that database first, then change the owner.

In the context of your question, if the service account was the prior database owner, then this would explain a change in effective permissions, even though there were no changes to the explicit database-level permission/role assignments.


Restore plan cache and buffer pool after database restore?

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use?

No.  The caches will have to warm up based on use.  You can force plans using the Query Store for particular queries if you want, as the Query Store is part of the database, and will be present after Restore.


Assuming the test numbers suggest the order in which the tests are taken and you want athletes having a Negative test coming after the last Positive test of theirs, you can group your table by athleteId and then:

get the last test number where the outcome is 'Negative':

MAX(CASE outcome WHEN 'Negative' THEN test END)

get the last test number where the outcome is 'Positive':

MAX(CASE outcome WHEN 'Positive' THEN test END)

compare the two results to see if the former is greater than the latter:

HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) >
    MAX(CASE outcome WHEN 'Positive' THEN test END)

Full query:

SELECT
  athleteId
FROM
  aTable
GROUP BY
  athleteId
HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) >
    MAX(CASE outcome WHEN 'Positive' THEN test END)
;

If you want athletes who have a Negative test coming after at least one Positive test (not necessarily the last one), you could try a different approach:

get a Negative test;
see if there exists an earlier Positive test for the same athlete.

Or like this in SQL:

SELECT DISTINCT
  athleteId
FROM
  aTable AS neg
WHERE
  outcome = 'Negative'
  AND EXISTS
  (
    SELECT
      *
    FROM
      aTable AS pos
    WHERE
      pos.athleteId = neg.athleteId
      AND pos.outcome = 'Positive'
      AND pos.test < neg.test
  )
;


How to convert timezone in MySQL using the timezone name?

To answer the question:

  Is there any way to convert timezone using timezone name not from timezone offset

The function CONVERT_TZ has this example and warning:

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
    -> '2004-01-01 13:00:00'

  Note
  To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

And the section on Time Zone support has a 'Populating the Time Zone Tables' section which describes the process of setting up the mysql timezone tables.


Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

For what is worth, I only use the 64K allocation unit size when in production or UAT environment and if I have a cluster with a SAN in which I have a LUN for the Data files, another LUN for the LOG files and another LUN for the TEMPDB files, at least.

Other consideration is that if your environment only have one disk and this is the OS disk, leave the allocation unit size as is. If you are trying to setup SQL Server 2014 on a different disk from your OS disk, perhaps in that disk you will get better performance with the 64K allocation unit size, but do consider that a best practice, both for performance and maintenance, states that you separate your data files, log files and TEMPDB files, at best, in different disks. This from a suggestion in this blog post from the SQL Server product group: http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/19/disk-and-file-layout-for-sql-server.aspx


Same Parameter in MySQL stored procedure

First of all, I don't think it's a good idea to use dynamic sql in the case when you have nothing really 'dynamic'.

If you still want to do it, it seems that you are missing an important point about prepared statements. You are preparing, executing, and then deallocating prepared statement in one procedure; thus, next time you call your procedure, you are repeating all the steps again. Take a look on https://stackoverflow.com/questions/11605783/mysql-permanently-prepared-statements , there is an example on how to get benefits from prepared statements inside stored procedure (briefly, you do not deallocate statement at the end, instead of this, you check whether a variable associated with the statement is not null and call prepare only once. That expands lifetime of the prepared statement to lifetime of session effectively caching execution plan).


How to disable ldf file or limit its size

You cannot disable/remove the LDF file. It has to exist, as it stores database transactions.
What you can do is:

Set the database recovery model to Simple

This should keep your LDF file from growing, and is set via the Options page of the Database Properties dialog.

For additional reducing, which I don't think is necessary, you can:

2 Set Auto Shrink to True

This is also done from the Options page of the Database Properties dialog:

3 Turn auto-grow off

This is done from the Files page of the Database Properties dialog:

Be aware that when a database is in the Simple recovery model, you will not be able to recover to a point in time

Or

Set the database recovery model to Full (I guess it's already set this way)
Create a full database backup
Start creating transaction log backups regularly. It's best to schedule a SQL Server job to do this

The latter scenario provides better disaster recovery as it enables recovery to a point in time
It's up to  you to decide how much data you can afford to lose


How to generate PostgreSQL COPY bin files?

Your best bet really is to create CSV files and then import them with COPY.  If you want to, you can then wrap them simply in a COPY ... WITH CSV FROM STDIN statement.

The existing docs on the COPY command should be sufficient.  See http://www.postgresql.org/docs/9.1/static/sql-copy.html


Run the following command to enable the user to write to /run/media/thomson/data2/mongo

sudo chown -R $USER:$USER /run/media/thomson/data2/mongo


SQL Server SSL Encryption - SelfSign Cert working - why?

Traffic will be encrypted regardless of the client encryption specification when you turn on the Force Encryption option on the server side (SSCM-->SQL Server Network Configuration-->Protocols for MSSQLSERVER-->Properties-->Flags-->Force Encryption). If you don't install a certificate from a trusted CA, SQL Server will use a self-signed certificate.

Clients cannot validate self-signed certificates because no trusted third party exists. Consequently, a client that requires encryption (Encrypt=true) must also explicitly specify that the certificate is to be trusted for the for the connection to succeed with a self-signed cert. TrustServerCertificate=true is  basically saying "yes, I understand the risks but connect anyway" and the certificate chain isn't validated.

A CA-issued certificate adds a trusted third party into the mix so the cert chain can be validated by the client with TrustServerCertificate=false. Although you could connect with TrustServerCertificate=true and a CA-issued cert (which I think is your second question), this is less secure because a self-signed cert could be used unwittingly by the client.

  Why doesn't the client (Mgmt Studio) attempt to verify/authenticate
  the Cert (be it self-signed/CA signed) when only ServerSide encryption
  is turn on ?

  Why does the client only authenticate/verify the Cert given by the Server when > client-side encryption ( "encrypt connection" ) is turn on ?

The server can force encrypted traffic for all clients but the onus is always on the client to verify the certificate. Consider the default connection setting is Encrypt=false in SSMS and other clients. This is saying "I don't need encrypted traffic so connect regardless of server settings or certificate." The ForceEncryption=true server setting strengthens this default by encrypting traffic regardless of the client's requested encryption level. This behavior aligns with this excerpt from the connection string syntax documentation for ADO.NET providers:

  Application settings never reduce the level of security set by SQL
  Server Client Configuration Manager, but may strengthen it.

Since the client didn't request encryption in the first place, certificate verification isn't performed but security is still strengthened by encrypting traffic with whatever certificate is provisioned on the SQL Server. The client can specify Encrypt=true and TrustServerCertificate=false to strengthen security further. This not only verifies a trusted certificate exists but also encrypts traffic even if ForceEncryption=false on the server.

Clients have to opt-in to certificate verification.  This is done to enable you to force encryption from the server without getting a trusted cert, installing a cert on the client or changing the client connection strings. See Using Encryption Without Validation in the product documentation.


How to prevent transaction log getting full during index reorganize?

Best practice is to REORGANIZE below around 30% fragmentation and REBUILD above this. Simply, REBUILD makes a clean copy, REORGANIZE does it in-situ.

Check what you're actually doing: you don't have a maintenance plan doing both do you?

On larger tables (50GB table is getting there) I've seen REORGANIZE consume all transaction log space if you follow this rule. Not often: only one system with a certain load pattern. The REORGANIZE just ran until the log expanded and consumed all disk space.

We switched to REBUILD instead with no more problems, but ignored fragmentation below 25%. This worked better for us: you'll have to see if it works for you.

REBUILD may affect the performance more than REORGANIZE in production, but this can sometimes be mitigated with the ONLINE option (Enterprise Edition required).


SQLite error: foreign key mismatch when I try to make an INSERT command

The error message "foreign key mismatch" does not indicate a constraint violation, but that your database schema is wrong.

Assuming that the vaccine table and the vaccine_id column actually exist, the most likely reason is that the required index is missing, i.e., that vaccine_id is not the primary key (or at least unique).


How to use COLUMN and TABLE options in a RAISE statement

It depends on the client-side verbosity. When using psql, issuing

\set VERBOSITY verbose

before calling the statement would caused the error message to include:

TABLE NAME:  accounts
COLUMN NAME:  username

In recent versions of psql, it's also possible to issue \errverbose after the fact to get the last error message reformated in verbose form.


Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

This seems to be an on-going issue
Views are messy to handle with Dynamic SQL
Earliest Bug was Cannot create VIEWs in prepared statements from 11 years ago. There was a patch put in to address it.
Another bug report, Prepared-Statement fails when MySQL-Server under load, states that error 1615 is not a bug when the underlying tables are busy. (Really ?)
While there is some merit to increasing the table cache size (See MySql error when working with a mysql view), it does not always work (See General error: 1615 Prepared statement needs to be re-prepared (selecting mysql view))
ALTERNATIVES
Over a year ago, someone mentioned this in the MySQL Forum (MySql “view”, “prepared statement” and “Prepared statement needs to be re-prepared”).
Someone came up with the simple idea of not using the view in the prepared statement but using the SQL of view in a subquery instead. Another idea would be to create the SQL used by the view and execute it in your client code.
These would seems to be better workarounds that just bumping up the table cache size.


If I correct understand Your question this should work. I have provided a link to SQL FIDDLE:

declare @max_date datetime
declare @min_date datetime

select @min_date = '2015-10-01 07:00:00'
select @max_date = '2015-10-31 05:00:00'

SELECT EmployeeId
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) ) AS  MinDateTime
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) DESC ) AS MaxDateTime
FROM table_name AS t1
WHERE CheckTime BETWEEN @min_date and @max_date GROUP BY EmployeeId;

SQL Fiddle - http://sqlfiddle.com/#!6/fd199/2
DATA:

1   2015-10-14 18:06:22
1   2015-10-28 23:06:40
2   2015-10-07 18:13:58
2   2015-10-14 11:14:15
1   2015-10-14 19:20:11
2   2015-10-08 10:20:34
3   2015-10-01 20:25:28
3   2015-10-30 10:26:04

RESULT:

1   2015-10-14 18:06:22.000 2015-10-28 23:06:40.000
2   2015-10-08 10:20:34.000 2015-10-07 18:13:58.000
3   2015-10-30 10:26:04.000 2015-10-01 20:25:28.000


Consolidating a row of data, based on previous rows

Sample data with the Date column typed as date:

CREATE TABLE dbo.Staff
(
  [ID] integer NOT NULL,
  [Surname] varchar(5) NULL,
  [FirstName] varchar(4) NULL,
  [Office] varchar(9) NULL,
  [Date] date NOT NULL,

  PRIMARY KEY (ID, [Date])
);

INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date])
VALUES
  (001, 'Smith', 'Bill', 'Melbourne', '2015-01-01'),
  (001, NULL, NULL, 'Sydney', '2015-03-01'),
  (002, 'Brown', 'Mary', 'Melbourne', '2014-04-01'),
  (002, 'Jones', NULL, 'Adelaide', '2014-05-01'),
  (002, NULL, NULL, 'Sydney', '2015-01-01'),
  (002, NULL, NULL, 'Perth', '2015-03-01');

The idea of the following solution is to lag back as many rows from the current row as there are preceding nulls:

SELECT
    G.ID,
    Surname = LAG(G.Surname, G.SurnameLag) OVER (
        PARTITION BY G.ID
        ORDER BY G.[Date]),
    FirstName = LAG(G.FirstName, G.FirstNameLag) OVER (
        PARTITION BY G.ID
        ORDER BY G.[Date]),
    Office = LAG(G.Office, G.OfficeLag) OVER (
        PARTITION BY G.ID
        ORDER BY G.[Date]),
    G.[Date]
FROM
(
    -- Find the LAG offset per column
    SELECT
        S.ID,
        S.Surname,
        SurnameLag = SUM(IIF(S.Surname IS NULL, 1, 0)) OVER (
            PARTITION BY S.ID
            ORDER BY S.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        S.FirstName,
        FirstNameLag = SUM(IIF(S.FirstName IS NULL, 1, 0)) OVER (
            PARTITION BY S.ID
            ORDER BY S.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        S.Office,
        OfficeLag = SUM(IIF(S.Office IS NULL, 1, 0)) OVER (
            PARTITION BY S.ID
            ORDER BY S.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        S.[Date]
    FROM dbo.Staff AS S
) AS G
ORDER BY
    G.ID, G.[Date];

For versions of SQL Server prior to 2012, the IIF expressions can be written as CASE WHEN <column> IS NULL THEN 1 ELSE 0 END.

Output:

This will all become much easier when Microsoft implements LAG with the IGNORE NULLS option.

For more options, see The Last non NULL Puzzle by Itzik Ben-Gan.


Auto increment primary key inside a number range

This example should get you what you need, or at least it worked in my testing.

Setup

CREATE TABLE dbo.TestInsert
    (
    TestID INT NOT NULL
    , TestSubID INT NOT NULL
    , TestValue VARCHAR(100) NOT NULL
    );

GO

CREATE TRIGGER dbo.trgTestInsert ON dbo.TestInsert INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.TestInsert
(TestID, TestSubID, TestValue)
SELECT I.TestID
    , NewSubID = ISNULL(T1.MaxID, 0) + ROW_NUMBER() OVER (PARTITION BY I.TestID ORDER BY I.TestID)
    , I.TestValue
FROM INSERTED AS I
    LEFT OUTER JOIN (
                    SELECT T.TestID
                        , MAX(T.TestSubID) AS MaxID
                    FROM TestInsert AS T
                    GROUP BY T.TestID
                    ) AS T1 ON T1.TestID = I.TestID;

END

GO

Testing

INSERT INTO dbo.TestInsert
(TestID, TestSubID, TestValue)
VALUES (10, NULL, 'test 1')
    ,(10, NULL, 'Test 2')
    ,(11, NULL, 'Test 3')

SELECT TestID, TestSubID, TestValue FROM dbo.TestInsert;


Is it possible to migrate Sharepoint 2016 and all it's databases from SQL SERVER2012 to AZURE?

Is it possible? Yes.

Should you? Documentation says...

  Important edit: this is currently not officially supported for
  SharePoint Server, even for Azure-hosted farms. This is likely due to
  the 1-millisecond SharePoint -> SQL supportability requirement which
  Azure SQL Database doesn't & won't guarantee. At the time of writing
  there's no plans to do so either, so please note that despite the fact
  this configuration by all accounts does work, this article is purely
  academic.

No.


How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else

As documented in the manual a trigger in Postgres consists of a trigger function and a trigger definition.

String constants are written in single quotes in SQL, "LOAD" refers to a column or table name, 'LOAD' is a string (character) constant.

If you want to change something using a trigger, you should use a BEFORE trigger and simply assign the new value to the record. Running a separate DML statement for that is the wrong approach in Postgres.

You didn't really give us much to go with, but I assume you are looking for something like this:

Define the trigger function:

create function check_load()
  returns trigger
as
$$
begin
  new.sname := 'LOAD_GENERAL';
  return new;
end;
$$
language plpgsql;

Then create the trigger:

CREATE TRIGGER trg_check_Load
  BEFORE UPDATE ON podium_core
  FOR EACH ROW
  WHEN sname = 'LOAD' OR internal 'LOAD'
  EXECUTE PROCEDURE check_load();

It's more efficient to only fire the trigger when the specified values are provided, rather than firing the trigger always and check for the value inside the trigger.

But if you want to do that check inside the trigger function it would be something like this:

create function check_load()
  returns trigger
as
$$
begin
  if (new.sname = 'LOAD' or new.internal = 'LOAD') then
    new.sname := 'LOAD_GENERAL';
  end if;
  return new;
end;
$$
language plpgsql;

CREATE TRIGGER trg_check_Load
  BEFORE UPDATE ON podium_core
  FOR EACH ROW
  EXECUTE PROCEDURE check_load();


SQL Server 2016 SP1 Replication: creation of publication stops with nvarchar(max) not supported with memory optimized tables

Fixed it. The @schema_option shouldn't be  0x40000000000 but 0x80000000000. This means the replication converts clustered index to nonclustered index for memory-optimized articles. It's nowhere close to error message.

So, if you need to create a replication from a disk table to a memory optimized table, do not let the wizard do the work but create the script en alter the @schema_option.
At least this lets you create the publication and the subscriber can connect to the publication.

Bugger, not fixed. Yes, the creation of the entire replication procedure works great. But then, the new memory optimized tables do not get created. The only thing this stupid feature does is drop the table (whether they exist or not). And then starts complaining the target table does not exist.

Now trying to hack the @schema_option to combine the 0x40000000000 and 0x80000000000. Somehow these values cross a threshold that makes them readable to SQL Server. One or the other works (and results in failures or weird behavior) together nothing...

Still not working in version 17.1 of Management Studio.

-- edit november 2017

The newer versions of Management Studio seem to support the in-memory to in-memory replication. And the error handling has improved.
So question marked as solved. If anyone still encounters issues, please add to this question :)


Moved 'tempdb', now file 'tempdev2' does not exist

You are not providing a unique physical filename for each file.  You should alter tempdb like this:
use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\tempdev.mdf')
go
alter database tempdb
modify file (name = templog, filename = 'S:\templog.ldf')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\tempdev2.mdf')
go

Microsoft Docs for ALTER DATABASE file and filegroup options state the following:

To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:
MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )


Collect performance information on high CPU condition in SQL Server

I think we might need to dip into sys.dm_os_ring_buffers and sys.dm_os_sys_info to get the CPU utilization data.

Please find below the script of Glenn Berry

-- Recent CPU Utilization History (SQL 2008/2008 R2 Only)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(144) SQLProcessUtilization AS [SQLServerProcessCPUUtilization],
               SystemIdle AS [SystemIdleProcess],
               100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

Source : Glenn Berry's Script


How does the operator "&" work in sql server?

A single ampersand is the Bitwise AND operator. The numbers on either side represent the value of one or more bit positions:

Actual binary (not BINARY datatype), evaluated from right to left -> 00100010

BIT    Position    Value (Position ^ 2)         "Selected"
0       0                            1                 0
1       1                            2                 2
0       2                            4                 0
0       3                            8                 0
0       4                           16                 0
1       5                           32                32
0       6                           64                 0
0       7                          128                 0
...   ...                          ...               ...
0      62          4611686018427387904                 0

Hence, 34 is made up of bitwise values of 2 and 32.

A very simple example showing that the & operator will return whatever value(s) is/are present on both sides is:

SELECT 1 & 1 -- 1
SELECT 1 & 2 -- 0 (because 2 is made up of only 2)
SELECT 1 & 3 -- 1 (because 3 is made up of 1 and 2)

SELECT 2 & 2 -- 2
SELECT 2 & 3 -- 2 (because 3 is made up of 1 and 2)
SELECT 2 & 4 -- 0 (because 4 is made up of only 4)

SELECT 3 &  4 -- 0 (because 3 is made up of 1 and 2, while 4 is made up of only 4)
SELECT 3 &  5 -- 1 (because 3 is made up of 1 and 2, while 5 is made up of 1 and 4)
SELECT 3 & 11 -- 3 (because 3 is made up of 1 and 2, while 11 is made up of 1, 2, and 8)

Looking back at the code you posted in the question, you should be able to see how it can be used to determine if one or more bits / flags are "set" within a given value. For example, let's say you want to require that AnsiPadding (value = 16), AnsiNulls (value = 32) and QuotedIdentifier (value = 256) are set to ON. You would add up those individual values -- 16 + 32 + 256 -- and then compare the total -- 304 -- with some "current" value, let's say 7000 (values 8 + 16 + 64 + 256 + 512 + 2048 + 4096):

SELECT 304 & 7000; -- 272

The result, 272, is the combination of 16 and 256. So if you want to require those 3 options (additional options will not adversely affect the result), you would use:

IF (@options & 304 = 304)
BEGIN
  ...
END;

As you can see, bitwise operations allow you to extract one or more "options" from one or more "options". But, in the code that you posted, they are just looking for individual options since using & with an individual bitwise value will return 0 if not present or that particular bitwise value if present. And the CAST to the BIT datatype will reduce all of those variations to either 0 (if the & yielded a 0) or 1 (if the & yielded anything else).

Please note that for the bitwise operators, either side (i.e. expression) can be:

  any of the data types of the integer data type category, or the bit, or the binary or varbinary data types.

However, counter-intuitively:

  In a bitwise operation, only one expression can be of either binary or varbinary data type.

That's right: "binary" operations do not work on purely "binary" data ;-). Attempting to do what seems entirely reasonable:

SELECT 0x03 & 0x0B;

will result in the following error:

  Msg 402, Level 16, State 1, Line 28
  The data types varbinary and varbinary are incompatible in the '&' operator.

In practical terms, this means that bitwise operations are constrained by the upper-limit of the BIGINT datatype (which is 8 bytes / 64 bits). Hence:

Total bits (i.e. flags / options): 63 (the 64th bit is used for negatives as BIGINT is signed)
Largest individual bit value: 4611686018427387904  (i.e. 2 ^ 62 ; 62 being the 63rd bit position))
Largest usable value: 9223372036854775807 (i.e. (2 ^ 63) - 1 ; all 63 bits present / set to "on")

Meaning: if you need to handle more than 63 flags / options, then you won't be using the & operator ;-).


AlwaysOn AG between 2 sql server failover clusters

If you are using SQL Server 2016, you can use Distributed Availability Groups, which is what you are describing. See more here.

If you are using SQL Server 2014 or SQL Server 2012, the answer is no. You'd have to put all 4 nodes into the same cluster and then setup an AG with 4 replicas. If two of the nodes are in a different data center, you'd want those two replicas to be asynchronous for performance reasons.


Finding the end of a Relationship chain optimally

I don't have your dataset, so can't test if this is better, but it feels better. After building the chains, we reverse them to find the 'childest' item in each chain, then join back to the original chain.

-- The same as your first CTE
;WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID
)

-- This CTE is new and reverses the sense of the chains
, BackChain AS (
SELECT ID, RootID, ROW_NUMBER() OVER (PARTITION BY RootID ORDER BY Seq DESC) BackSeq
FROM RelationshipChain
)

-- Now we join each chain to the childest item in the reverse chain
SELECT
    rc.ID
    , rc.ParentID
    , rc.ChildID
    , rc.Seq
    , rc.RootID
    , bc.ID ChainEndID
FROM RelationshipChain rc INNER JOIN BackChain bc ON RC.RootID = bc.RootID AND bc.BackSeq = 1
ORDER BY RootID, Seq

Results with your sample data:

ID   ParentID ChildID Seq         RootID ChainEndID
---- -------- ------- ----------- ------ ----------
0001          0003    0           0001   0007
0003 0001     0005    1           0001   0007
0005 0003     0006    2           0001   0007
0006 0005     0007    3           0001   0007
0007 0006             4           0001   0007
0002          0004    0           0002   0004
0004 0002             1           0002   0004

Indexes will help performance.


BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1

Change the command
From
, FIELDTERMINATOR = '';''

to
, FIELDTERMINATOR = '',''

This should do the trick
BULK INSERT [test_db].[dbo].[test_insert]
FROM 'C:\temp\test_table.csv'
WITH
(
    FORMAT = 'CSV',
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

As the ROWTERMINATOR  goes <ou should try it or switch to \r\n


SQL Server Msg 2812, level 16 state 62 line 30

It would need to be EXEC(@cmd) not EXEC @cmd.

Without the parentheses it looks to find a stored procedure with the same name as the string inside @cmd.

Hence the error you are seeing of

  Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK =
  'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.


Slow query with where and index on some fields

Rewrite the condition on reserved_until to
WHERE COALESCE(reserved_until, TIMESTAMP '-infinity') <= '2021-05-14 14:23:16'

and create an index for it:
CREATE INDEX ON table_name (COALESCE(reserved_until, TIMESTAMP '-infinity'))
   WHERE rejected_at IS NULL AND deleted_at IS NULL;

Then you should gather statistics:
ANALYZE table_name;


distinct values from multiple fields in array

Nevermind, just found the answer, just need to add 2 unwind stages

[{
    $unwind: {
        path: '$exchange.export'
    }
}, {
    $unwind: {
        path: '$exchange.import'
    }
}, {
    $group: {
        _id: null,
        countries: {$addToSet: '$country'},
        export: {$addToSet: '$exchange.export.resource'},
        import: {$addToSet: '$exchange.import.resource'}
    }
}]


Find accounts with passwords that are not complex

Maybe that will help
PWDCOMPARE (https://docs.microsoft.com/en-us/sql/t-sql/functions/pwdcompare-transact-sql?view=sql-server-2017)?

Some example:
https://dzone.com/articles/use-pwdcompare-to-find-sql-logins-with-weak-passwo

Credits @SQLRockstar link

First, let's build a list of weak passwords and save it to file:

$pwdList = Get-Content .\password_list.txt

Build a query and execute against SQL Server:

$SQLText = "SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('$password', password_hash) = 1;"
Invoke-Sqlcmd -Query $SQLText -ServerInstance $SQLServer
}


~2 Billion row fact table in star schema on Oracle. Advisable?

Row count isn't a great indicator of database size. I would not be worried about Oracle scaling to 2 billion rows. Relational databases can practically scale up into the many terabyte range. The ability to scale comes down to data model, hardware, and developer skill, user requirements, and budget. It's feasible to scale an Oracle (or SQL Server) data warehouse up into the 10s to 100s of terabytes.

Using a star schema is the right starting point for a relational data warehouse. There are a number of well thought out techniques for data warehousing outlined in The Data Warehouse Tookit. This will help you design a data warehouse that can scale.

At some point, you'll want to look into features that your vendor offers like partitioning, materialized views, and dimensional hierarchies. You should be able to put that off in your first attempt, though.


Granting a SQL Server database only user execute on xp_instance_regread fails

So if anyone is curious the fast answer was to go into the UI and add the foo user to master thru the user mapping tab.

Wonder why?


MySQL Replication With single Master DB to multiple Slave DBs

Make sure each of your replicas has a different value of server_id. If all have same server_id, then the master may only send an event (such as the create table event) to one of the replicas.

Regardless, and since you are new to MySQL, I'd like to discourage you from using replicate-do* and replicate-ignore*, at least until you're really sure of what you're doing. Replication filters have many surprising effects. Normally, you don't use any filters in your production setup.


Is it a good practice to isolate login information (username, password) in a separate table?

I would recommend you implement security by encrypting the data you want to keep secure.  Separating the username and password to a different table would not significantly improve their protection.


User and login mapping after an SQL Server restore

It doesn't matter when the logins are created.  As long as the SIDs match, the database user will be associated with the login.

Every time a user attempts to connect to a database, SQL Server is going to check for a mapped login (unless it's a contained database).  It does this by checking to see if there is a login with a SID that matches the user's SID.  Accordingly, it doesn't matter when the login was created because it will be matched at the the time the user attempts to connect to the database.


Depends on db flavor, most provide command line tools to load sql files. Looks like you may be using ms sql, reference here http://technet.microsoft.com/en-us/library/ms165702(v=sql.105).aspx, more specific http://technet.microsoft.com/en-us/library/ms170572.aspx

sqlcmd -S myServer\instanceName -i C:\myScript.sql


Does Sql Server 2008 support Database Sharding

No, SQL Server doesn't support sharding. SQL Server scales up not out.

This applies to RDBMS in general:

CAP theorem says you can have at most 2 of C, A and P.
RDBMS are ACID compliant which is the point of using them

The "C" in both acronyms is "Consistency" and sharding sacrifices "Consistency"
So generally sharding and consistency are mutually exclusive

There are exceptions (more or less) of course. Some examples:

Oracle RAC which is expensive and tricky
MySQL replication which is not Consistent
MySQL cluster sacrifices Availability in case of node failure, and can be set to prioritize either consistency or availability


What type of database can I use to store mutual scores between every combination of users?

Because I could look up either user, I have to store the pairings "both ways"

Well, that's not true. You could normalize the data because the relations aren't directed anyway.

CREATE TABLE scores (
  user_id   int,
  friend_id int,
  score     int,
  CHECK ( user_id < friend_id ),
  PRIMARY KEY (user_id, friend_id)
);

INSERT INTO scores (user_id, friend_id,score)
VALUES
  ( 1, 2, 15.2 ),
  ( 1, 3, 19.7 ),
  ( 2, 3, 21.0 );

Now you can find the relation easy.

SELECT *
FROM scores
WHERE user_id = 1
  AND friend_id = 2;

If you don't want to put the rule in the app, just rewrite the query into.

SELECT *
FROM scores
WHERE (user_id, friend_id) =
  ROW( least(1,2), greatest(1,2) );


extension "pg_buffercache" already exists and relation "pg_buffercache" does not exist

The extension was created in a schema which is not in your search_path.  So you can't create another copy of it, but also can't find the view.  You can use \dx to find what schema it was installed to.


What will be inside the last differential backup file?

I'd definitely recommend that you read A SQL Server DBA myth a day: (30/30) backup myths from SQL Server expert Paul Randal for some really good information about SQL Server backups.

To answer your immediate question, the lastest differential backup (diff_backup_2018_05_09_008000.bak) in your question would contain all changed pages since the last full (non COPY_ONLY) backup (full_backup_2018_05_09_000000.bak) **PLUS ** enough of the transaction log to be able to recover the database to a transactionally-consistent view of the database at the time the data-reading portion of the backup finished.  Transaction log backups would not affect what pages are included in full or differential backups


DATEADD and DATEPART with milliseconds yields strange results

This is due to a strange limitation1 in the accuracy of the datetime data type, as documented:

Accuracy | Rounded to increments of .000, .003, or .007 seconds

The solution would be to use datetime2, which provides better accuracy, as you can see in this dbfiddle demo:

The return type of the DATEADD is dynamic based on what you send into it.  So the important thing is to make sure you are passing datetime2 to the step where you add the milliseconds on.

1 Randolph West has a great blog series on how SQL Server data types are stored, including one on dates and times (How SQL Server stores data types: dates and times).  That post has a useful comment from Data Platform MVP Jeff Moden:

The time portion of DATETIME is actually an integer count of the number of 1/300’s of a second that have passed since midnight. That translates to a 3.3 millisecond resolution but that level of resolution isn’t possible for the way the data is stored so 0.0 and 3.3 ms are rounded down to 0ms and 3ms and 6.6 milliseconds is rounded up to 7 milliseconds.


Creating a query to pull very specific information using four tables

Using the hint Rick James provided, I came up with the following.  I'm not sure about efficiency, but it gives me the results I was looking for.

SELECT
    testData.questionUUID,
    COUNT(testData.answerUUID) AS wrongAnswerCount
FROM testData
    LEFT JOIN answerData ON answerData.uuid=testData.answerUUID
    LEFT JOIN testHistory ON testHistory.uuid=testData.testUUID
WHERE testHistory.userUUID = ?
    AND answerData.answerCorrect=0
GROUP BY testData.questionUUID
HAVING COUNT(testData.answerUUID) > 5
ORDER BY wrongAnswerCount DESC


Query to determine a count by rolling interval

Try this:

establish the base date per customer. Something like SELECT MIN(dtshipdate) ... GROUP BY intCustomerId You can do this as a CTE or put the data in some table.
JOIN order to the above and calculate the number of intervals passed since first order
get the ROW_NUMBER partition by the result of the calculation in step 2, order by the date.
any row with ROW_NUMBER() = 1 needs to send a message.

This should work if the date of the first order sets the intervals. If you need to establish a new interval base date with each new order after a break longer than the interval, leave a comment below.


The following are natively available in SQL Server:

SUMIFS

-- SUM of a column or expression
SELECT SUM(IIF(so.[type] = 'S', so.[schema_id], 0)) AS [SumIFs]  -- 293
FROM   [master].[sys].[objects] so;

COUNTIFS

-- SUM as a counter, COUNT counting everything, and COUNT counting non-NULLs
SELECT SUM(IIF(so.[type] = 'S', 1, 0)) AS [SumIFs],  -- 74
       COUNT(IIF(so.[type] = 'S', 5, 0)) AS [CountIFsGotcha], -- 103
       COUNT(IIF(so.[type] = 'S', 1, NULL)) AS [CountIFsCorrect] -- 74
FROM   [master].[sys].[objects] so;

SUMPRODUCT

SELECT so.[type], SUM(CONVERT(BIGINT, so.[object_id]) * so.[schema_id])
FROM   [master].[sys].[objects] so
GROUP BY so.[type]
ORDER BY so.[type];

STDEV

SELECT STDEV(so.[schema_id]) AS [StandardDeviation]
FROM   [master].[sys].[objects] so;

Please note that the IIF statement -- introduced in SQL Server 2012 -- is just shorthand for: CASE WHEN expression THEN true_result ELSE not_true_result END. Example:

SELECT IIF('a' = NULL, 1, 2); -- 2

The following are not available natively in SQL Server, but can most likely be created as SQLCLR User-Defined Aggregates (UDAs).

RSQ
LOGEST
LOGNORM.INV


How to join global temp tables in SQL Server?

SELECT *
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key collate database_default
LEFT JOIN ##C as c ON a.key = c.key collate database_default
LEFT JOIN ##D as d on a.key = d.key collate database_default

Should eliminate the problem

But the underlying problem is that your (source) tables have different collations. This should be avoided.

This is a good source for some more information:
Questions about sql server collations


Does the length of a table name affect performance?

Table names have to be stored just like any other data (in the system catalog pg_class to be precise), and there are multiple objects that are typically named matching the table name (constraints, indexes, sequences, ...) so there is a marginal cost for longer strings. But you will not be able to notice any difference in performance. Internal references are implemented with oid numbers (object identifier types), so independent of the string used as name.

The more important consideration is that your code becomes longer and harder to read and type. Typos more likely. Aliases are nice, but you still have to spell out the name in many places. So the rule of thumb should be:

As short as possible.
As long as necessary to be clear.


XML queries with case insensitive tag- which is the best query

You can easily test the performance yourself.

Create a regular table that you can test your queries on.

create table myTable ( yourXML XML )

Add a couple of rows that will give you a match.

INSERT INTO myTable SELECT '<z>1<c>2</c></z>'
INSERT INTO myTable SELECT '<Z>1<c>2</c></Z>'
INSERT INTO myTable SELECT '<Z>1<c>2</c></Z>'

Add a whole bunch of rows that will not match in different parts of the XML.

insert into myTable
select top(10000) '<X>1<c>2</c></X>'
from sys.all_objects as o1, sys.all_objects as o2

insert into myTable
select top(10000) '<Z><X>1<c>2</c></X></Z>'
from sys.all_objects as o1, sys.all_objects as o2

insert into myTable
select top(10000) '<Z><X>1</X><c>2</c></Z>'
from sys.all_objects as o1, sys.all_objects as o2

Use SET STATISTICS IO (Transact-SQL) and SET STATISTICS TIME (Transact-SQL) and execute your queries in SQL Server Management Studio.

set statistics time on
set statistics io on

-----Solution 1----------

SELECT * FROM myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = "z"]/*[lower-case(local-name(.)) = "a"] where  ( ($x/*[lower-case(local-name(.)) = "b"][1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 2----------

SELECT * FROM myTable
WHERE
(CONVERT(XML,LOWER(CONVERT(VARCHAR(MAX),[yourXML]))).exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 3----------

SELECT * FROM myTable WHERE
([yourXML].exist('for $x in (/Z/A,/z/a) where  ( ($x/b[1],$x/B[1]) = 1 )  return $x') > 0 )

-------------------------
-----Solution 4----------

select *
from myTable
where yourXML.exist('(Z/A,z/a)[(b,B)=1]') = 1

Switch to the message tab and evaluate execution time and required reads.

Presumably you have better data in your database to test on. The performance characteristic will change depending on the actual structure of the XML you have to deal with in the real world.

A side note is that your queries are not equivalent.
The first is easily adapted to longer element names.
The second query changes the content of the elements, not just the element names.
The third query does not really deal with case insensitive element names, it just enumerate all possible element names that in this case happens to be different in cases only. If you wanted to deal with lets say a 3 letter element name with your third solution you would have 8 (I think) different permutations to deal with.

I added a fourth solution,  mostly because it is short and pretty, that has the same limitations as your third solution. It is in my measurements slightly faster than using FLWOR.


How to store an array in a table column?

You can create separate tables, like user table and contact table:

user table

uID
---
2

contact table

Contact | uID
1221    | 2
1223    | 2


MAXDOP=0 enables SQL to use all available CPU's. - This is true upto 64 logical processors . If you have more like 128 MAXDOp=0 will still only use 64 logical processors.

For cores < =8 you will still need to check on hyper threading enabled or not as that is also a factor that counts here.

Also for cores less than or equal to 8 on single numa , ideally is asked to keep as # of processors which is where confusion arises. Ideally in today's world its very rare to find such servers because if you are having issues on less than 8 core and looking to set MAxDOP you have a bigger problem.

But yes for less than 8 more or less will be same. But point to note is query process may or may not use all cores even when set to that MAXDOP. Also this setting should be checked in parallel with Cost threshold of parallelism as well