Glen Berry has some example code on his blog here - http://sqlserverperformance.wordpress.com/2012/03/27/sql-server-database-mirroring-tips-and-tricks-part-3/ - which aims to fix this problem. It works by monitoring the tagged databases, and if it notices one of them has failed across to the mirror it will fail the others across. I had a play with it after a similar question on SE a couple of weeks ago and it does the job, though whether it's as fast/reliable as clustering is something you'll have to evaluate for yourself.

Grouping mirrored databases into a 'group' which fails over together is a feature of Availability Groups in SQL Server 2012.


How to insert into junction table using triggers

You have to think in terms of the design.

After triggers on the Song table cannot help populate the SongArtist table since Song and Artist have no immediate relationship.

Although you should not need this, you could have before triggers on SongArtist to check for validity of SongID and ArtistID.

DELIMITER $$
CREATE TRIGGER before_songartist_insert
BEFORE INSERT ON SongArtist
FOR EACH ROW
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
CREATE TRIGGER before_songartist_insert
BEFORE UPDATE ON SongArtist
FOR EACH ROW
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
DELIMITER ;

In reality, the ON DELETE CASCADE and ON UPDATE CASCADE clauses should make using triggers unnecessary. You should insert rows into the Song and Artist as a single transaction. Then, using songIDs and artistIDs from the completed transaction, you can insert them into SongArtist and let any defined CONSTRAINT check the validity.


Add an additional listener to Oracle Database Appliance

According to Oracle Support, there should not be any special considerations on Oracle Database Appliance, and thus we can use steps common to Oracle 11gR2 RAC on Linux.

Typically this involves

Adding the additional interfaces to /etc/hosts
Adding a network resource and virtual ip resources to CRS with srvctl
Creating a new listener with netca
Adding the new listeners to the LISTENER_NETWORKS initialization parameter

If you want the new listener to live on the default port of 1521, we need to create it on a different port initially, and then manually remove the listener with srvctl and finally re-add the listener on the correct port with srvctl using the -s option. Note that due to a bug in 11.2.0.1 this only works properly on 11.2.0.2 and higher.

Note that the network and vip resources must be added as root, while the rest is done as the grid infrastructure owner. On oda this is "grid".

I wrote at blog post with a more detailed walk-through of how I went about this at http://www.dolicapax.org/?p=116


SSIS packages fail with Pending Execution Status

If the packages are running as part of a SQLAgent job, check the job for what user it's running as.  Check those permissions, and that could be your problem.  I just had to repair a situation like that.  Permissions got yanked from the wrong users, and it broke everything...


Azure Data warehouse - User defined function issues

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):

  function_body
      Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.

Could you double check that function is created in DW?


Does pg_restore continue after internet connection got lost

pg_restore keeps executing SQL statements on the server. If the connection to the server fails, the restore was not successful.
What is strange here is that there should not be any long idle times in the connection, with the possible exception of creating indexes and refreshing materialized views.
At any rate, you should see that the network gets configured properly. You cannot really work with an unreliable network like that that drops your connection as soon as you turn your back.


SSAS Calculated Field - rolling sum, for a year of days

So the calculated member I came up with is:

sum(
    {ParallelPeriod([Calendar].[Fiscal Calendar].[Date],365,[Calendar].[Fiscal Calendar].CurrentMember):[Calendar].[Fiscal Calendar].CurrentMember},
    [Measures].[Occurrences Received]
)

simple enough, I guess. It could have been the ParallelPeriod for the Year level and 1 though I guess as this is a Fiscal Calendar 365 members at the day level needs to be used.


As at MongoDB 3.2, there is no option for the Windows MSI to only install the mongodump.exe and mongorestore.exe command line tools so a default install is the most straightforward option.

  Are there mongodump and mongorestore standalone tools that one could use for this or is it best to install the entire MongoDB package on our build server (which feels like an overkill)?

If you install from a standard (non-Administrator) command prompt, the installer will not be able to create a Windows Service definition so the only overhead will be some extra binary files. In this case you can also use the MSI installer to uninstall or upgrade to newer versions of MongoDB. You will also have additional command line tools available if you want to monitor a deployment (mongostat.exe, mongotop.exe) while you are backup/restoring, and will have mongoimport.exe and mongoexport.exe if you want to import/export JSON or delimited text.

If you want a more minimal install only including specific binaries, you can copy the binaries and the two OpenSSL DLLs which can be found in the bin directory of a full install of MongoDB: ssleay32.dll and libeay32.dll. The DLLs will need to either be in your path or the same directory as the MongoDB .exe files you are running.


Speeding up very slow UPDATE query (Postgresql 9.6)

You should create a B-tree index:

CREATE INDEX ON links_publicreply (direct_reply_id);

Note: your index links_publicreply_answer_to_id is useless and should be dropped.


mysql store all the dates in the same style. Internally this is three bytes that are expanded to the YYYY-MM-DD. You can convert this format to the any desired form by DATE_FORMAT() function like that:

SELECT DATE(now()) AS sysformat
     , DATE_FORMAT( NOW(), '%W %M %D %Y, %j\'th day of the year') AS myformat


PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824

I found the answer with regards to how and why this is happening.
PG_Monz is using the following query to track temp bytes:

UserParameter=psql.db_temp_bytes[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select temp_bytes from pg_stat_database where datname = '$5'"

However, this select statement does not show the current temp bytes, instead it shows an increment of all temp bytes ever created since the last stats reset. IE: It's not showing a delta over a time period which would be the accurate number.

Looking at this query and then checking the folder, I see it's empty and therefore my server actually has no temp bytes.

select current_setting('data_directory') || '/base/pgsql_tmp'

Source: https://www.marshut.net/wnsmt/postgresql-9-2-4-temp-files-never-released.html

The same goes for a DB being too large:

Zabbix threshhold is incorrect for the way PostgreSQL reports size of databases:
See info about select pg_database_size(dbname)


Large accounting table, best way to partitioning by two dates

Since stop_datetime will not be known at insert time the partition must be done by start_datetime.

I think partitioning by day (date_format(start_datetime, '%Y-%m-%d')) would be adequate since 1 billion rows divided by (3 years * 365 days) ≃ 900,000 rows per partition

Updating indexes is expensive and it happens at inserts, updates and deletes. Keep the indexes to a minimum. If the main queries will be those selects then I would keep the primary key like the following and trash all the other keys:

PRIMARY KEY (`user`,`start_datetime`,`stop_datetime`,`mac`,`ip`)


Why does SQL Server fail to create a procedure referencing a linked server?

Some objects are allowed to be created to facilitate Deferred Name Resolution. The assumption is that if you create a procedure that references dbo.MissingProcedure, you will create dbo.MissingProcedure some time between the moment you create the procedure, and the first time you execute it. Another situation that works is a table that doesn't exist yet. I can say the following, even if dbo.table_name doesn't exist:

CREATE PROCEDURE dbo.procedure_name
AS
BEGIN
  SET NOCOUNT ON;
  SELECT column_name FROM dbo.table_name;
END

Again, because SQL Server is giving me the benefit of the doubt in this case. But it doesn't always. If dbo.table_name exists, but does not (yet) contain a column named column_name, I'll get an error:

  Msg 207, Level 16, Procedure procedure_nameInvalid column name 'column_name'.

SQL Server validates the column names if the object exists, just like it validates the remote server objects in your case - which it can't do, because the login doesn't (yet) have permission.

Now, why SQL Server is implemented such that it lets some things slide and not others, you'll have to ask the dev team. Erland Sommarskog has some great ideas on giving us the option to make the behavior more consistent; you should read Ideas for SET STRICT_CHECKS ON and vote for - and comment on - his Connect item.


Transaction with multiple SQLCMD calls in a batch file

Because each sqlcmd execution is a separate session, it means you can't have transactions spanning those executions.  You're going to want to combine your scripts into a single script for you to do what you want.

If you can properly order your scripts, the following approach in Powershell will work for you:

$scripts = Get-ChildItem "C:\temp\*.sql" |Sort-Object
$fullbatch = @()
$fullbatch += "BEGIN TRANSACTION;"

foreach($script in $scripts){
    $fullbatch += Get-Content $script
}

$fullbatch += "COMMIT TRANSACTION;"

sqlcmd -S localhost -d test -Q "$fullbatch"

Essentially, I read in each script (assuming they are ordered correctly) into a single batch variable, append a BEGIN TRANSACTION and END TRANSACTION to the batch, then execute that back as a query within my sqlcmd execution.


try this

SELECT [Subject] , [Type], MarksObtd
  FROM [Marks_Table]
  INNER JOIN [Subjects_Table]
  ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode
  INNER JOIN [Types_Table]
  ON [Marks_Table].TypeID = [Types_Table].TypeID

As james added comment, I think it is a simple join and you need to change [TypeID] in select clause to [Type]


Restoring a mysql database in windows 10

Mysqldump is single threaded so you’re bound by the speed that 1 core can run at to play all your SQL commands in serialised fashion. There are some config variables that can be changed to reduce the amount of disk flushes needed and permit larger memory buffers for speed.

Innodb (if that’s what engine you use) variables (plus others)

innodb_buffer_pool_size
Innodb_log_file_size
innodb_flush_log_at_transaction_commit
Sync_binlog

Should get your research and tuning off to a start to make things faster.


Mysterious procedure calls without parameters - but no exceptions generated

SET PARSEONLY ON will do this.  FMTONLY will generate an error.

EG:

USE [AdventureWorks]
GO

set parseonly on
go
exec [uspGetBillOfMaterials]
go
set parseonly off


When updated records get written to the real physical pages in mdf file?

We can think of a data store as being a list of all the events that have ever happened - insert row 1; insert row 2; update row 2; delete row 1. To find a current value we simply replay all events over each other and where we end up is the answer. In current parlance this is known as event sourcing.
There are some problems with this, however. Performance is the obvious one as every time a value is required the whole log must be reprocessed.
Since history does not change once recorded an obvious optimization would be to take a snapshot at a point in time and summarize all events up to that point. Now queries can be answered from this summarization plus the much smaller set of events that have happened since the snapshot was calculated. We could even persist these summaries to avoid having to read the whole event stream on start-up.
Further, every event as it arrives can be written into this summary so no query need ever read the event stream. This, effectively, is how a database server works.
On a controlled shut-down these summaries can be persisted cleanly, ready to be read on start-up, and all values are consistent. But what of uncontrolled shutdowns, such as system crashes or loss of power? How can we handle transactions that were not yet commited when the outage occured? Some hardware is too small to hold all summaries in memory so some parts must be shuffled on and off disk. How to keep all parts consistent irrespective of their current location and transactional status?
There are several solutions to these problems. One is the Aries protocol which SQL Server follows. This separates the events from the summaries. The events go to a log file (ldf in SQL Server) and the summaries (i.e. tables) into a data file (mdf). Log records are written synchronously as SQL statements are processed. Tables are written occassionally and asynchronously as the system decides it needs to. Persisting tables to disk is called checkpointing. There is no direct relation between when a SQL statement is processed or a transaction committed and when a checkpoint is processed. Checkpoints are written by a background system task. Often we decide checkpoint frequency to meet the system's recovery time objective (RTO).
Importantly a table can be checkpointed even if it contains values written by a transaction which has not committed. Aries stores enough in the log to correctly identify and recover from this situation.
Under Aries the logical and physical states of the data are separate. Locking a row is a logical operation; the page holding that row may or may not be in memory. Committing a transaction is a logical operation; the pages touched by that transaction need not be persisted immediately, or may have been persisted already.
When data is changed the old and new values plus some metadata are written to the log file and flushed to disk. This write is synchronous. Once this is acknowledged there is a permanent record of the change which can be examined after a crash and used to bring the system back to a consistent state. Only after the log is acknowledged will the change be applied to the data page. This is why it is known as write-ahead logging.
The start and end of each transaction is also recorded in the log. Transactions can start because the client explicitly requested one (BEGIN TRANSACTION). If no explicit transaction exists the system will start its own to ensure the atomicity (the "A" in ACID) of each SQL statement. When a transaction commits only the log record recording this fact is guaranteed to be persisted. The data page holding the newly written value may either be in memory only or may have been checkpointed. It doesn't matter to Aries which is the case.
On recovery the system can read the log, find the last checkpoint, remove changes made by un-committed transactions and apply changes made by committed transactions.
For your specific example of updating a customer's postcode let's suppose there are two matching rows. The log file will end up looking a little like this (in my made-up pseudo-code).
lsn:12345; begin transaction 1;
lsn:24680; transaction 1; object: table1; row:654: column: postcode; old: '3000'; new: '9000'
lsn:35791; transaction 1; object: table1; row:357: column: postcode; old: '3002'; new: '9000'
lsn:45678; commit transaction 1;

Of course if you run a hex editor over an ldf it will look nothing like this, but it illustrates the point. "LSN" is the Log Sequence Number and is used by Aries to relate data pages to log events. Other users' log records will be interleaved with yours. I've indicated this by leaving large gaps between LSNs.
Table checkpoint log records can appear before, between or after what I've shown, or not at all. It doesn't matter to the protocol.
Aries protocol is simple in concept but has to accommodate many corner cases which makes it complex to implement. There are many on-line resources available explaining it. I like this one (YouTube) by a professor at Carnegie Mellon University and this write-up.
Recently SQL Server has introduced a new protocol called Accelerated Database Recovery. It can reduce recovery times from minutes to milliseconds.
Kudos to @DanGuzman for his work on this.


Postgresql role_table_grants grantee differences between users

One possible cause is that UserA belongs to "admin_group" and that UserB does not belong to "admin_group".
Please post output of following statements run by a superuser from psql CLI:

\du userA
\du userB
select * from information_schema.role_table_grants where grantee='user_group';
select * from information_schema.role_table_grants where grantee='admin_group';


mysqldump doesn't restore database. why not?

You have to use the mysql client to reload

mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql

Another way to reload would be

mysql -u root -p -Ddatabasename

then from the MySQL prompt, do this

mysql> source /home/databasename_bkup.sql

If you would like the mysqldump to drop and recreate the database for you, create the dump like this:

mysqldump -u root -p --add-drop-database -B databasename > /home/databasename_bkup.sql

Then, running the script

mysql -u root -p < /home/databasename_bkup.sql

does the DROP DATABASE command for you.


Migrating reports from SSRS 2005 to SSRS 2014 and SSAS 2005 cube to SSAS 2014?

There is no definitive answer to your question, since it will totally depend on the complexity of SSIS packages, SSRS reports and SSAS Cubes.

Doing in-place upgrade is always risky, since if something goes wrong, it would be difficult to rollback. A side-by-side migration is always a good and reliable method, since if something goes wrong, you already have a system up and running that you can point to.

Always, test, test and test your migration and have a fully tested rollback plan. Use upgrade advisor (this touches DB Engine, SSIS, SSRS and SSAS) and fully address all the issues raised by it.

I have written a thorough answer for database migration and post restore steps
For SSRS migration, I have written it here.
Devin Knight talks about Upgrading Packages to SSIS 2012 and msdn has a whitepaper on 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012
For SSAS, this method should work in your case as well.


Date range query for past 24 hour in Mongo shell

Found answer here:
https://stackoverflow.com/questions/1296358/subtract-days-from-a-date-in-javascript

db.system.profile.find({
  "timestamp" : {
    $lt: new Date(),
    $gte: new Date(new Date().setDate(new Date().getDate()-1))
  }
})


What does this error mean when doing a pg_restore on a Postgres 9.6.2 database?

The database the dump file was taken from has a certain foreign key constraint defined.  But your target database does not.  Probably because someone went and added it to the dumped database since the last dump.  Since you specified --clean, it is trying to drop all the objects that exist in the dump file from your target database before recreating them.  It is failing, because it doesn't exist in your target database to be dropped.

You can add --if-exists to the restore command.  Or you can remove --exit-on-error from the command, and just ignore the error message you get.


No it does not create a read or write lock on films table as this creates ACCESS SHARE LOCK;

to test this  open two sessions

in session one  run this command

  begin;
  create table films2 as
     table films;

then in the second session run this command

Select * from films;
Update films set something = 0

If you need to lock films table you have to issue a Lock like so

begin ;
  Lock table films in exclusive mode;
  create table films2 as
     table films;
commit;

this will block the table from updates but not reads...

NOTE  Access Share Locks block any transaction that try to acquire an ACCESS EXCLUSIVE locks such as DROP Films etc...


Replace " " character derived column SSIS

Looks like the quote (") character is considered a part of the input string.

There is two options:
1. correct you flat file source to define quotation characters for this field, so it will remove " values from the beginning and end of the string.
2. Use another replace to get rid of quotes.REPLACE(REPLACE(SSN,"\"",""),"-","")


This query will give you a listing of all the linked servers and their logins:

-- linked servers and their logins
select s.name, p.principal_id, l.remote_name
from sys.servers s
    join sys.linked_logins l
        on s.server_id = l.server_id
    left join sys.server_principals p
        on l.local_principal_id = p.principal_id
where s.is_linked = 1
go

Add more columns if you need more information. Here are the base DMVs:

-- list the linked servers
select *
from sys.servers
where is_linked = 1
go

-- linked server logins
select *
from sys.linked_logins
go

-- local logins
select *
from sys.server_principals
go


At least version 5.6.14 of the client has the --sigint-ignore option to totally ignore the SIGINT handler. It appears to have been added on Oct 7th, 2004 -- so it's been around a while at least. I just tested it, and it does keep CTRL-C from cancelling the MySQL client. However, it would be nice if CTRL-C would also cancel the current command line buffer like it does on the BASH shell -- as the PostgreSQL CLI does. I've forked MySQL in order to see how hard it would be to implement some such thing. I'll post a Homebrew formula with a tarball link when/if it's ready.

UPDATE 1
Ever have one of those days? I created the promised patch, and then I decided to create a video to demonstrate it. It worked! However, I couldn't disable it! Well, it turns out that MySQL actually baked in the functionality to the core client last year! Yep, the A to this Q is to download at least MySQL 5.7.3-794-g901d27f's client. It has the functionality we all desired.

EXAMPLE
From the command line just add the --sigint-ignore flag:

mysql --host=127.0.0.1 --port=8900 --user=root -p --sigint-ignore DBNAME

Or, do like I did and create a MySQL config file for your user account at $HOME/.my.cnf (%USERPROFILE%/.my.cnf on Windows) with the following content:

[mysql]
sigint-ignore=TRUE

This will ensure that all mysql executions ignore the annoying Ctrl-C and behave like a good SQL client.


There is no CREATE TRIGGER IF NOT EXISTS

There is DROP TRIGGER IF EXISTS

You must execute the trigger's creation as follows:

USE xxx_admin
DELIMITER $$
DROP TRIGGER IF EXISTS test_AFTER_UPDATE $$
CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE`
AFTER UPDATE ON `test` FOR EACH ROW
BEGIN
    INSERT INTO auditTest
    select *, now() from test where id = NEW.id;
END $$
DELIMITER ;

GIVE IT A TRY !!!

As for you error, using IF like that is not valid from the CLI.


DELETE TECHNIQUE #1
If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.
Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey
Here is how to perform the massive DELETE without using DELETE
CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.
DELETE TECHNIQUE #2
If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:
DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Feb 15, 2012 : How to implement soft deletes?
Mar 05, 2012 : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
Nov 07, 2012 : Can I do anything about a very long running KILL command?
Aug 07, 2013 : How to delete duplicate records

Give it a Try !!!


Mixing sharding and Replica in MongoDB cluster

In MongoDB 3.6 and newer, shards must be deployed as a replica set. Replication provides data redundancy and high availability for each shard; sharding enables a collection to be distributed across multiple shards.

For more information see Sharded Cluster Components and the Deploy
a Sharded Cluster tutorial in the MongoDB documentation.


The two options would be:

Model them in the same table with a property to distinguish their type (such fields are often called a discriminator) and all the properties of both types.
Model them with a third table holding their common properties (and usually a discriminator also) and one child table per type. This pattern is referred to as table inheritance.

The latter option would be preferable, but I'm not sure how one would implement it in Access (I've not used that in a decade and a half).


You are right: process 2 begins with update on Table2, but encounters deadlock with update on Table1 because the row is being updated by process 1 (and not yet committed).

Your assumption that the database "knows" it will encounter a deadlock in process 2 and will deny the transaction is wrong. The database is unaware of future statements in a transaction, therefore it can not avoid a deadlock.


Here is one of the options:

Connect to the CDB and query cdb_data_files and cdb_pdbs data dictionary views

select file_name
  from cdb_data_files cdf
       join cdb_pdbs p
          on (cdf.con_id = p.con_id)
where p.pdb_name = '<container_name>'

Note: cdb_pdbs data dictionary view doesn't show root container - only pdbs associated with the current cdb. If there is a need to see all pdbs including cdb query v$containersview.


How to use SQL*plus from standard user in Windows Vista to run sql query to Oracle10g on my server?

I didn't find how to run sqlplus from standard user but JJ. answer in this question help me to find alternative way.

I just use PsExec to run sqlplus in my server.


Trigram index on all values of a JSONB column

You can create a function which returns the data as a string:

CREATE OR REPLACE FUNCTION public.jsonb_val_to_string(jsonb)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare x text;
BEGIN
  select string_agg(value,' ') into x from jsonb_each_text($1);
  return x;
END
$function$

And then create the index on that:

create index on jason using gin (jsonb_val_to_string(x) gin_trgm_ops);

And then query also using the function:

explain select * from jason where jsonb_val_to_string(x) ilike '%foo%';

Of course you might want to change the delimiter in string_agg to something you know will never be included in the ilike pattern.


I'm not very familiar with LDAP (beware of understatement!), but I'm pretty sure there is no way to do this automatically on the PostgreSQL side.  If you create your LDAP users in a GUI then probably you want to set up a cron script to export the users to the database (I don't know if you can define trigger-like behaviour there, I mean something which would fire a user-creation DB script once a user is created for LDAP).  If you do this from a script then it is quite easy to solve the problem, as you already mentioned.


MongoDB doesn't do master-master replication; you can only have a single primary:

Replication in MongoDB
A replica set is a group of mongod instances that maintain the same data set. A replica set contains several data bearing nodes and optionally one arbiter node. Of the data bearing nodes, one and only one member is deemed the primary node, while the other nodes are deemed secondary nodes.


SQL Server SSL connection - What am I missing?

Note that when you force encryption on the server side, the connection will be encrypted by using ssl. You can use any certficate for this. Your connection will be encrypted, However the client will not check if the identity of the SQL Server is in fact valid. Without this check you could be open for a man in the middle attack.

If you choose force encryption on the client side, a full validity check will be performed. That way not only is the connection encrypted, also the client knows that the server it is connection to, is in fact the server mentioned in the certificate because it can trust that the certificate has not been tampered with.

For this to work, the certificate used on the SQL Server must be issued by a certificate authority that the client trust. This can be any well known certificate authority or for example when you have a windows domain and a CA in that domain, you can have that CA issue one. You then have to import the root certificate of that CA into all clients and that will work too.

to check if your connection is encrypted use the following:

SELECT session_id,encrypt_option
FROM sys.dm_exec_connections


MySQL - SELECT from multiple tables by unique attribute, return one row with all values

You do not need UNION in this case, simple LEFT JOIN

SELECT
   a.id,
   b.street,
   c.postcode
FROM t1 a
LEFT JOIN t2 b ON a.id=b.id
LEFT JOIN t3 c ON a.id=c.id

should work


Workarounds for setting default schema for domain group?

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.


Copying a table while maintaining consistency in Oracle

If you keep a separate table to maintain whether your procedure is running or not, then you only need to keep a lock as long as it takes you to update the table to ensure that another session is not doing the same.  Here is some code to setup the table and then use it to run your procedure.

--Setup
CREATE TABLE ProcedureLock AS (SELECT logon_time FROM v$session WHERE rownum<=1);
UPDATE ProcedureLock SET Logon_Time = NULL;
COMMIT;

--Use
DECLARE
   vLogon_Time ProcedureLock.Logon_Time%Type;
   eInUse      Exception;

   PRAGMA EXCEPTION_INIT (eInUse, -54);
BEGIN
   Begin
       SELECT Logon_Time INTO vLogon_Time FROM ProcedureLock FOR UPDATE NOWAIT;
   Exception
      When eInUse Then
         DBMS_Output.Put_Line('The procedure is running.');
         Return;
   End;

   UPDATE ProcedureLock SET Logon_Time =
    (SELECT min(logon_time) FROM gv$session WHERE SID = SYS_CONTEXT('USERENV','SID'))
   WHERE Logon_Time IS NULL;

   If (SQL%ROWCOUNT <> 1) Then
      DBMS_Output.Put_Line('The procedure is running or failed to complete.');
      Return;
   End If;

   COMMIT;

   DBMS_Output.Put_Line('The procedure is NOT running.');
   DBMS_Output.Put_Line('Run Procedure here.');

   UPDATE ProcedureLock SET Logon_Time = NULL;
   COMMIT;
END;
/

First we lock the new ProcedureLock table and then update it.  If either of these fail we know the procedure is running and therefore we should not run it again.


How does one investigate the performance of a BULK INSERT statement?

As far as I can tell you can optimize a bulk insert in a very similar way that you'd optimize a regular insert. Typically, a query plan for a simple insert isn't very informative so don't worry about not having the plan. I'll go over a few ways of optimizing an insert but most of them probably don't apply for the insert you specified in the question. However, they could be helpful if in the future you need to load larger amounts of data.
1. Insert data in clustering key order
SQL Server will often sort data before inserting it into a table with a clustered index. For some tables and applications you can improve performance by sorting the data in the flat file and letting SQL Server know that the data is sorted through the ORDER argument of BULK INSERT:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
Specifies how the data in the data file is sorted. Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any.

Since you are using an IDENTITY column as the clustered key you don't need to worry about this.
2. Use TABLOCK if possible
If you are guaranteed to have only one session inserting data into your table you can specify the TABLOCK argument for BULK INSERT. This can reduce lock contention and can lead to minimal logging in some scenarios. However, you are inserting into a table with a clustered index that already contains data so you will not get minimal logging without trace flag 610 which is mentioned later in this answer.
If TABLOCK is not possible, because you can't change the code, not all hope is lost.  Consider using sp_table_option:
EXEC [sys].[sp_tableoption]
    @TableNamePattern = N'dbo.BulkLoadTable' ,
    @OptionName = 'table lock on bulk load' ,
    @OptionValue = 'ON'

Another option is to enable trace flag 715.
3. Use an appropriate batch size
Sometimes you will be able to tune inserts by changing the batch size.

ROWS_PER_BATCH = rows_per_batch
Indicates the approximate number of rows of data in the data file.
By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows. For information about performance considerations, see "Remarks," later in this topic.

Here is the quote from later in the article:

If the number of pages to be flushed in a single batch exceeds an internal threshold, a full scan of the buffer pool might occur to identify which pages to flush when the batch commits. This full scan can hurt bulk-import performance. A likely case of exceeding the internal threshold occurs when a large buffer pool is combined with a slow I/O subsystem. To avoid buffer overflows on large machines, either do not use the TABLOCK hint (which will remove the bulk optimizations) or use a smaller batch size (which preserves the bulk optimizations).
Because computers vary, we recommend that you test various batch sizes with your data load to find out what works best for you.

Personally I would just insert all 695 rows in a single batch. Tuning the batch size can make a big difference when inserting lots of data though.
4. Make sure that you need the IDENTITY column
I don't know anything about your data model or requirements, but don't fall into the trap of adding an IDENTITY column to every table. Aaron Bertrand has an article about this called Bad habits to kick : putting an IDENTITY column on every table. To be clear, I'm not saying that you should remove the IDENTITY column from this table. However, if you determine that the IDENTITY column is not necessary and remove it that could improve insert performance.
5. Disable indexes or constraints
If you're loading a large amount of data into a table compared to what you already have then it may be faster to disable indexes or constraints before the load and to enable them after the load. For large amounts of data it is usually more inefficient for SQL Server to build an index all at once instead of as data is loaded into the table. It looks like you inserted 695 rows into a table with 11500 rows, so I would not recommend this technique.
6. Consider TF 610
Trace Flag 610 allows minimal logging in some additional scenarios. For your table with an IDENTITY clustered key, you would get minimal logging for any new data pages as long as your recovery model is simple or bulk-logged. I believe this feature is not on by default because it may degrade performance on some systems. You would need to test carefully before enabling this trace flag. The recommended Microsoft reference still appears to be The Data Loading Performance Guide

I/O Impact of Minimal Logging Under Trace Flag 610
When you commit a bulk load transaction that was minimally logged, all of the loaded pages must be flushed to disk before the commit completes. Any flushed pages not caught by an earlier checkpoint operation can create a great deal of random I/O. Contrast this with a fully logged operation, which creates sequential I/O on the log writes instead and does not require loaded pages to be flushed to disk at commit time.
If your load scenario is small insert operations on btrees that do not cross checkpoint boundaries, and you have a slow I/O system, using minimal logging can actually slow down insert speeds.

As far as I can tell this doesn't have anything to do with trace flag 610, but rather with minimal logging itself. I believe the earlier quote about ROWS_PER_BATCH tuning was getting at this same concept.
In conclusion, there probably isn't much that you can do to tune your BULK INSERT. I wouldn't be concerned about the read count that you observed with your insert. SQL Server will report reads any time you insert data. Consider the following very simple INSERT:
DROP TABLE IF EXISTS X_TABLE;

CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);

SET STATISTICS IO, TIME ON;

INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows

Output from SET STATISTICS IO, TIME ON:

Table 'X_TABLE'. Scan count 0, logical reads 11428

I have 11428 reported reads but that is not actionable information. Sometimes the number of reported reads can be reduced by minimal logging, but of course the difference cannot be directly translated into a performance gain.


Index Maintenance for DW- DB ( ETL process )

If you are worried about index fragmentation (which really only matters if you are doing range scans off disk) but you are loading your data periodically during an ETL process I'm assuming you have your indexes enabled (and updated) during the ETL process.

When you load your data your indexes have to be updated all the time while inserting data but end up being fragmented which is why you want to rebuild them again.

Maintaining the index when loading data only to rebuild them later is useless work for SQL Server.

One approach I would suggest considering is the following

Disable all your non-clustered indexes on a table before you load data into it
Load your data in the order of the clustered key if you have one (most datawarehouses use a surrogate key anyway)
Enable your indexes again after you loaded your data. That way the index is only built once after the data load and will be contiguous.

The code to disable and enable/rebuild all indexes for a table would be the following

ALTER INDEX ALL ON dbo.[Table Name] DISABLE

-- perform data load

ALTER INDEX ALL ON dbo.[Table Name] REBUILD


Why don't you just use an actual SEQUENCE which was introduced to SQL Server with 2012?

The following approach overloads the BIGINT value where the leading 10 digits represent the server IP address after being converted to a BIGINT (via the method outlined in this blog post by SQLDenis) and the right-most 10 digits represent the CustomerID local to the instance.  A BIGINT can have up to a maximum of 19 digits, but because the leftmost digit will never be higher than 4 to start the sequence this allows for far more customer numbers than the max value of an INT allows for (the comments in the code explain it in a little more detail).

The only requirement for this approach is that this solution only be deployed to, at most, one instance per server (because IP won't be unique across instances hosted on the same server).  As stated above, this approach still allows up to the INT maximum of 2,147,483,647 customers per instance in your solution.  Because the CID column in the tables is defined as a INT in your question, I'm assuming this is acceptable.  It's still about as fast of an approach as you'll find anywhere else.

-- This sequence will have a dynamic starting number **PER SERVER** based on IP
-- Max value for BigINT is 9,223,372,036,854,775,807
-- Max value for INT is                2,147,483,647
-- Max IP (255.255.255.255) to BIGINT: 4,294,967,295
-- IP to padded BIGINT:    4,294,967,295
--                       *             1,000,000,000
--                       ---------------------------
-- Max Starting Value =    4,294,967,295,000,000,000

-- Variables to capture server id (IP) and generate sequence DDL statement
DECLARE @serverid BIGINT, @sequenceDSQL VARCHAR(4000)

-- Convert IP to BIGINT
SELECT TOP(1) @serverid =
        (CONVERT(bigint, PARSENAME(local_net_address,1)) +
         CONVERT(bigint, PARSENAME(local_net_address,2)) * 256 +
         CONVERT(bigint, PARSENAME(local_net_address,3)) * 65536 +
         CONVERT(bigint, PARSENAME(local_net_address,4)) * 16777216)
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;

-- Shift Server IP value to left of the BIGINT value
SET @serverid = @serverid * 1000000000

-- Generate Dynamic SQL DDL for CREATE SEQUENCE Statement
SET @sequenceDSQL = '
CREATE SEQUENCE dbo.NextCustomerNumber
AS BIGINT
    START WITH ' + CAST(@serverid AS VARCHAR(19)) + '
    INCREMENT BY 1
    NO MAXVALUE
    NO CACHE -- Or switch to CACHE if you''re seeing performance issues on inserts'

-- Create the Sequence
EXEC(@sequenceDSQL)

-- Create the Table
create table dbo.Customers
(
    CID int identity not null primary key,
    CustomerName varchar(50),
    CustomerNumber bigint DEFAULT (NEXT VALUE FOR dbo.NextCustomerNumber)
)
GO

If you want to also factor in the port number for the @serverid value, you're going to reach a limit of what a BIGINT can hold, so I would suggest coming up with some other unique identifier for the server other than IP/port.  However, my code should show a general approach that you can adjust as needed to solve your problem.


Prevent MySql from mangling queries by casting string to int

Gotta love MySql ;)

I searched Stack Overflow for a MySql equivalent to some isnumeric function, and found this.

This rather convoluted WHERE clause would return the expected 0 rows:

where Id = case when concat('','3R100C'*1) = '3R100C' then '3R100C' else null end

The idea is to only match the parameter against Id when it is a valid numeric value. Hope it helps!

I'd create an actual isinteger function to abstract away this sheer madness:

CREATE FUNCTION `isinteger`(v varchar(255)) RETURNS bit(1)
BEGIN

    RETURN CASE WHEN CONCAT('', v * 1) = v THEN 1 ELSE 0 END;

END

That said I don't think it's right to use an auto-incremented Id field as a search-fallback column - this ID is your table's primary key, its meaning belongs strictly on the database side, no program should ever care about it, even less a program's user.


How can I import multiple .xlsx and .xls files into multiple tables in Access?

I haven't done MSSQL in a while but if I remember you can do a ssis package.  I think its part of sql management studio. Where you can read in files and do inserts.


How to install downgrade of SQL Standard 2016 to Standard 2014

If you want a Sql Server 2014 instance, you'll need to install that specific version. Installing Sql Server 2016 will create a Sql Server 2016 instance, but you can control certain database behaviors to be compatible with the specified version of SQL Server by ALTERING the COMPATIBILITY_LEVEL.


Can AWS Aurora High Availability allow different Schema on Replicas?

AWS Aurora's replication is more akin to Always On Availability Group. The primary pushes storage changes to other replicas. You don't get to make schema changes on the subscriber.

More details are available in several re:Invent deep dive sessions.


Data Warehouse measures without a corresponding dimension

The standard approach for handling missing dimensional data would be to enter a null value for the relevant data in the fact table.

If this approach is problematic for some reason (table constraints, business rules) , adding a dimensional value to represent 'null' would be acceptable, if less technically correct. If you choose to implement this solution, keep in mind that you will need to consider that null <> null for incremental processing.

Another possible solution would be to encourage the business (modify the application, change the data feed, consult with the outside data vendor etc.) to define a more robust salesCategory dimension- that is, define more cases for the dimension so that the number of missing values is reduced or eliminated and less holes in the data are present.


You don't need a subquery at all. But you have to decide what ordering you want.

After the group by there is no tldid values any more. And every group will be made by collapsing many rows (with different tldid values) into one row. Which of them should be used for the ordering? Is it the maximum, the mimimum or some other?

You can write for example:

select locid, tlid, locdescription
from
  trip_log left join trip_log_det on tlid = tldtripid
  left join locations on tldlocation = locid
where tlid = 50
group by locid, tlid, locdescription
order by min(tldid) ;

In Postgres, you could also use the non-standard DISTINCT ON syntax to achieve the same result. This is more flexible, as you can have the tldid and other (non-grouped) columns in the select list as well:

select distinct on (locid, tlid, locdescription)
    locid, tlid, locdescription
from
  trip_log left join trip_log_det on tlid = tldtripid
  left join locations on tldlocation = locid
where tlid = 50
order by locid, tlid, locdescription,
         tldid asc ;


Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

The InnoDB plugin came into existence in MySQL 5.1 since version 5.1.38.

Many new features in the plugin allow you to

make InnoDB access more CPUs

innodb_read_io_threads
innodb_write_io_threads

scale up InnoDB to match the IOPs of your disk (innodb_io_capacity)

You are better off upgrading to MySQL 5.6 which has surpassed MySQL 5.1/5.5 because in  5.6

you can dump the buffer pool to disk on shutdown and reload it on startup
you can segment the buffer pool into buffer pool instances to help prevent swapping if the buffer pool is more than 50% of installed RAM

I would suggest you upgrade to MySQL 5.6 instead of dealing with enabling the old 5.1 plugin


Oracle Warning in Alert Log Regarding PGA_AGGREGATE_LIMIT

According to our database admins, this is a known ORACLE error since version 12. We have been advised to set pga_aggregate_limit to 0 in order to work around this problem. I haven't been able to test this thoroughly, but we haven't seen errors since changing this setting (using alter system, we haven't set the parameter in our database's [s]pfile).


DB Master Key Encrypted By *Correct* Service Master Key?

As per books online

  "The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key."

Here is the link http://technet.microsoft.com/en-us/library/ms174382.aspx


How do you create a new database in SQL Developer?

The term database has a different meaning in Oracle. The definition of database by Oracle:

  A set of files, located on disk, that store data. Because a database
  instance and a database are so closely connected, the term Oracle
  database is often used to refer to both instance and database.

Oracle Database Architecture

If you wanted to seperate data per application, you could do that by using seperate tablespaces and schemas.

Creating such a database is a complex process, and it is done with DBCA or SQL*Plus on the database server, not with a remote connection:

Creating and Configuring an Oracle Database

In 12c, the multitenant architechture was introduced, that is similar to the concept in SQL Server.

Overview of Managing a Multitenant Environment

If you have a container database, there you can easily create new pluggable databases from SQL Developer, when using a DBA connection.

Managing Pluggable Databases (PDBs) using Oracle SQL Developer


How to update a field in sharded environment in mongodb using C# driver

All targeted updates (multi: false so targeting a single document) must contain enough information to target a single document in order to be routed correctly (i.e. the shard key, or the _id of the document being targeted).

To understand why, consider what would happen with your attempted update. You have said (by stating multi: false that you only want one document to be updated.

First, because you have not specified the _id value of the document (unique value), if the query is broadcast to all shards, there could be multiple resulting updates (each shard will only update one document but if you have 3 shards, 3 docs could be updated total). Hence that is rejected.

The other option is to route the update operation only to a single shard.  But to do so, you must specify the JobId (your shard key) as part of the criteria for your update.  Then the operation would not need to be broadcast to all shards, it could be routed to a single shard and your original criteria (only update one such document) could be satisfied.

If your intent is to update more than one document, then simply change multi to true.  If not, then you need to be more specific about what single document you want to update.


What is the cost of adding a foreign key in mysql?

In MySQL, a foreign key requires an index. If an index already exists, the foreign key will use that index (even using the prefix of an existing multi-column index). If no index exists, defining the foreign key will build the index.

So the size increase and time to create a foreign key is about the same as to create an index on the same column(s).

The performance of a SELECT is not impacted significantly by the presence of a foreign key. Only a slight additional work for the optimizer, to consider the new indexes.

The performance of updating is more, because for each foreign key, an INSERT/UPDATE/DELETE has to check to see if the constraint is satisfied. That means a primary key lookup to the referenced tables. This impact is measurable, and it is greater if the referenced tables are not in the buffer pool.

Another impact is the locking. If I update a row in a child row that has a foreign key, InnoDB places a shared-lock on the referenced rows in the parent tables. That means no one can update those parent rows until I commit. If you have lots of threads updating child rows, then the parent rows may be locked most of the time, and this can make it hard to do concurrent work in the parent tables. Not so much a performance problem, but a concurrency problem.

As with all "how does that perform" questions, the answer really depends on your workload. If you don't have concurrent updates, for example, that issue may not effect you for all practical purposes. Testing it yourself with load testing is the only way to be sure. It's not something anyone on StackOverflow can answer precisely for you.


Find and print the restaurant name and city for all restaurants serving Salad

SELECT Restaurant.restaurantName,
       Restaurant.city
FROM   Restaurant,
       MenuItem,
       Dish
WHERE  Dish.dishName = 'Salad'
       AND Dish.dishNo = MenuItem.dishNo
       AND MenuItem.restaurantNo = Restaurant.restaurantNo
ORDER  BY Restaurant.city,
          Restaurant.restaurantName


MySQL-load data infile- invalid data validation

You can validate and have check the values before inserting. Check the example below:

load data infile ‘path/to/file.csv’ into table TABLENAME fields terminated by ‘,’ (col1,col2,@TRUEFALSECOLVAR) set TRUEFALSECOL=case @TRUEFALSECOLVAR when ‘true’ then 0 when ‘false’ then ‘1’ end;

More  load data infile usecases.


Percona Cluster Failed with Haproxy : Connection Limit Exceeded

OBSERVATION #1
You have max_connect_errors to 1000000 (One Million). If you ever reach that many consecutive connection failures, you would simply run FLUSH HOSTS; to clear up blocked connections. Given the high value of that setting, you will never need to run FLUSH HOSTS;
OBSERVATION #2
I am glad you mentioned your TIME_WAITs. I wrote this post in ServerFault back on Feb 01, 2012 : MySQL lowering wait_timeout value to lower number of open connections. This is a bit of a hack but it is a necessary evil in this instance.
In short, just run this on every PXC node
SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse

This will  make TIME_WAITs timeout in 1 second. You have to run these lines every time a node reboots.

I got this idea from this post: How to forcibly close a socket in TIME_WAIT?
The accepted answer has a pictorial representation of when a TIME_WAIT comes into existence.
The answer with the idea that I liked is the one I am now suggesting.


The system cannot find the file specified

This ended up being a permission issue to the path where sqlcmd resides.


SQL-Server performance metrics: What exactly is Lock Waits/Sec (or: Why is it that unrealistically high?)

The name "Lock Waits/Sec" is terribly misleading.
When you read this counter from sys.dm_os_performance_counters, it is a cumulative value that is reset when the instance is restarted.  Note that this is true of many (or maybe all?) of the "per second" counters here.
If you view it in Perfmon.exe, though, that application does the work of taking diffs and showing a "per second" view of the value.
In your case, 2 million lock waits over the entire uptime of the server might be pretty innocuous.  Of course, a lot of that probably depends on how long things waited on the locks, so this metric won't help you much in isolation.
To your broader issue, if you want to get a sense of what's going on with this server, running sp_BlitzFirst is a good place to start.  It will take a 5 second sample of a number of performance-related metrics (including perfmon counters), and give you a summary of the good, the bad, and the ugly.


Apart from the fact that where not in () is almost always very slow, distinct will also be slow.

Try this, and let me know if it is better:

select distinct(col1)
from table1
    left join table2 on table1.col1 = table2.col1
        and table2.col2 not like '%XYZ%'
where table1.col2 > 'some -time'
  and table2.col1 is null;

Note: You have to bear in mind that there could be a magnitude of other issues affecting the performance, such as hardware, indexing, other users etc.; the above query represents some improvement, but is not guaranteed to run blindingly quick.


How to correct SQL Server Import and Export Wizard error that says my data conversion length must be between 0 and 4000?

I've finally obtained an easy enough workaround. What I did was reopen my *.idy.xlsx Excel spreadsheet where my source data resided and re-saved it as an Excel 97-2003 Workbook.  These get a filename extension of *.xls. I went back to the SQL Server Import and Export Wizard and repeated the import steps, this time selecting the newly saved *.xls file. Ladies and gentlemen, it worked. A pair of trivial warnings appeared but the data came through flawlessly for me!


Changing numeric type slow down query big time for postgres

I would like to point out that the join condition in the EXPLAIN was still comparing ::numeric with ::integer as can be seen in the EXPLAIN PLAN you provided:

...
Hash Cond: (((lead.id)::numeric = lead_address.lead_id)
...
 Hash Cond: ((address.id)::numeric = lead_address.address_id)
...

So even though you seemed to have dropped, converted and re-created the relevant information, your query was still creating an execution plan based on old information.

Cleaning up the database with VACUUM ANALYZE ... rectified your issues.


The problem can not be solved with the relational model, but, as @jean says in the comments, we can calculate a hash of the objects that make up the set of objects and form a field with restriction of uniqueness.


Mysql Right join with mutiple condtion not working

table2.fname cannot be both 'city' and 'name' in the same row.

To get both of this EAV model we use two joins on the table2 with a different alias.

Its unclear why you want a RIGHT JOIN. Normal [inner] JOIN is fine.

I've moved the selection to a WHERE criteria just for ease of readability. Because its inner it could be put in the ON criteria.

While you can group by table1.orderid when you do this you cannot SELECT table1.* because by grouping their is only one row per orderid and therefore multiple orders matching the same critera cannot return on the same line (without an aggregate function like GROUP_CONCAT).

select table1.*
from table1
inner JOIN table2 t2name ON
        t2name.table1_order_id = table1.orderid
inner JOIN table2 t2city ON
        t2city.table1_order_id = table1.orderid

where
    t2name.fname = 'name'
    AND t2name.field_value LIKE '%karthi%'
    AND t2city.fname = 'city'
    AND t2city.field_value LIKE '%salem%'

or

select table1.orderid
from table1
inner JOIN table2 t2name ON
        t2name.table1_order_id = table1.orderid
inner JOIN table2 t2city ON
        t2city.table1_order_id = table1.orderid
where
    t2name.fname = 'name'
    AND t2name.field_value LIKE '%karthi%'
    AND t2city.fname = 'city'
    AND t2city.field_value LIKE '%salem%'
group by table1.orderid


Because this version I am using is Oracle 12c, so user SEC_MGR is replaced by SYSTEM user.

Try to login with user SYSTEM/manager to access.


Transaction Log Maintanance While Using AlwaysOn Availability Group

You can backup the transaction log from either of the replicas.  Doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable (provided no other stoppers are in place like active transactions, etc.).

To do a test, in a non-production environment, setup an availability group just as you have it in your production system (asynchronous commit to the secondary replica).

In my test environment I have a test database, TestBackupDatabase, and I bloated it with logged transactions through a dummy table:

use TestBackupDatabase;
go

create table dbo.TestTable
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTable
default values;
go 1000

Now when I do a transaction log backup on my primary, using DBCC SQLPERF(LOGSPACE) I see on both transaction logs (primary and secondary) that space used has dropped due to log truncation.  Bloating the transaction log back up with the same test on the primary:

insert into dbo.TestTable
default values;
go 1000

I now do a transaction log backup on the secondary async replica.  Running DBCC SQLPERF(LOGSPACE) again on each replica I see the same behavior:  transaction log reuse.

BOL Reference: Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)


Why would I use the MySQL "system" command?

There is actually a far more useful application for the SYSTEM command than simply executing ls to see the current directory contents:

As mentioned in the docs (and elaborated on by a comment), this is way to make backups:

echo "FLUSH TABLES WITH READ LOCK; SYSTEM snapshot.sh; UNLOCK TABLES;" | mysql

snapshot.sh contains code to make an atomic snapshot of the mysql tables, using LVM, ZFS or btrfs capabilites.
You can't split this up into multiple commands from the shell script, because as soon as the mysql session is closed, the table lock is released.


How to create a relationship between one table and multiple other entities

I faced this same problem 4 or so years ago here https://stackoverflow.com/questions/3636061/database-design-similar-contact-information-for-multiple-entities.

In the end I went with my method one which did involve many more joins etc during the life of the project but I always liked the fact that contact information only had to be stored in one place, once in the database.

Another way to look at this is to maybe see if you can reduce the number of other tables. Client_rep, other_party, third_party all seem like types of users. have you considered a super type - sub type relationship. For example dbo.user is the SuperType with client_rep, other_party and third_party related as the subtype. You would then only need two associative tables dbo.phoneUser and dbo.emailuser between dbo.phone, dbo.email and dbo.user


Utilizing a Right and Left Outer Joins in same SELECT

In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.

Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.

You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.


MySQL: How do I limit or prevent caching for specific tables?

InnoDB and MyISAM already use different buffer/cache structures, so if you use different engines for the two tables it is very easy to configure different cache sizes.

For InnoDB you set up innodb_buffer_pool_size which caches data and indexes.
For MyISAM you configure key_buffer_size for buffering indexes. MySQL leaves MyISAM data caching to the OS to manage. You could also create custom key buffers for each MyISAM table and decide how big the cache is.


I would try to answer the question as whole. Yes I believe book has some discrepancy as far as my knowledge goes.

One thing for sure is that the backup explanation pic mentioned in the book has some typos. It forgot to use word commit and instead continued with word starts. Like Xact A Starts is a transaction that started much before backup and somewhere at last it committed and still the pic shows Xact S starts it should be Xact A commits similarly for transaction B. This is what you mentioned in point 3

Now let me give you very clean definition of what is MIN LSN here. The MIN LSN would be MINIMUM of (Oldest active transaction which is when transaction C starts , LSN when backup started and checkpoint was fired, in this case red line showing checkpoint). This is always how minimum LSN in case of backup would be calculated. Please note Transaction A started before backup but committed before backup ends so it would not be oldest active transaction and also as per backup operation the pages were read so this would not be the oldest active transaction.

Now let us go to definitions

Backup Start LSN: This is basically "Checkpoint LSN"( As mentioned by Paul Randal in Understanding SQL Server Backups) this is recorded when backup starts and checkpoint is fired.

Backup End LSN: This is basically reading the transaction log again and recording 2 things

The LSN of oldest active transaction.
The LSN when backup operation started, For this I am not sure whether this happens or not IMHO this should be same as Checkpoint LSN or Backup start LSN but may be SQL Server is doing this 2nd time to confirm. I believe it does only what I have mentioned in point 1.

Minimum LSN: The would be minimum of ( LSN of oldest active transaction, Checkpoint/backup start LSN ). The definition in the BOOK is little bit confusing.


postgres: could not receive data from WAL stream: ERROR: requested WAL segment has already been removed

We had a similar situation and ended up initializing from the current primary. There is a nasty bug here. So, to play safe my advice is to re instantiate the slave. (until the bug fix is in place)
If you have a nice reproducible case, it would help to document it so it can be studied and with a little luck fixed in the code.
Reporting bugs: https://www.postgresql.org/docs/current/bug-reporting.html
PostgreSQL 12.3, 11.8, 10.13, 9.6.18, and 9.5.22 Released!
This bug was fixed in 9.5.22 and 9.6.18 and 10.13 and 11.8 and 12.3:
Avoid premature recycling of WAL segments during crash recovery (Jehan-Guillaume de Rorthais)
WAL segments that become ready to be archived during crash recovery were potentially recycled without being archived.


Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

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


MySQL row does not persist, but primary key with auto-increment is incremented

The disappearing rows are a common behavior of mysqlcheck (or myisam check), because it tries to repair the corrupted blocks. Other engines (eg. Oracle) have more advanced repairing algorithms, that can actually preserve your data.

Corruption usually appears when there's a problem with the disk (space, partition headers etc), so start by checking them. Then check the integrity of your data files, and make sure they can expand correctly.

PS: When runing mysqlcheck, you're using the --auto-repair argument?


Benefit to keeping a record in the database, rather than deleting it, for performance issues?

In PostgreSQL, an UPDATE is implemented as marking the current row version as dead, followed by inserting a new version of the row with the new values.
The entire row is recreated no matter how many columns are updated.
Additionally, if it leads to an index change, a new row version is also created in each concerned index.
On the whole, it's pretty heavy (compared to overwriting bytes in a file).

By contrast, DELETE is implemented as marking the row version as dead, both in data and indexes. Since dead in this context means no longer visible to this transaction and the future transactions (unless it's rolled back), this is a bit similar to your hidden flag, except it's more efficient than an UPDATE since there's no new version of the row.

At some point, VACUUM will kick in, presumaby by way of autovacuum, and
mark the dead row as reusable space. Quoting the documentation:

  In PostgreSQL, an UPDATE or DELETE of a row does not immediately
  remove the old version of the row. This approach is necessary to gain
  the benefits of multiversion concurrency control (MVCC, see Chapter
  13): the row version must not be deleted while it is still potentially
  visible to other transactions. But eventually, an outdated or deleted
  row version is no longer of interest to any transaction. The space it
  occupies must then be reclaimed for reuse by new rows, to avoid
  unbounded growth of disk space requirements. This is done by running
  VACUUM.

The bloat caused by high-frequency or high-volume writes, both UPDATEs and DELETEs, is one of the things that a postgres DBA should indeed keep an eye on, but there's no apparent reason DELETE would cause more bloat. In fact, based on the implementation choices, it's probably the contrary.

In any case, periodic REINDEX are worthwhile. See Routine reindexing.


Indexing or changing one-to-many relationship

The relationship seems fine; the problem seems to be in the presentation. If you want this query to work faster, just return the join to the client, and perform the looping / concatenating on the client.

This sounds like a tongue-in-cheek answer, but I'm 100% serious. Why does SQL Server have to do the work of transposing your rows to a concatenated column? Your client language is certainly much better equipped to do that (loop through the joined results, ordered by ObjectID, and start a new concat whenever a new ObjectID is encountered). It may be less efficient at the very high end (say, thousands of license types per ObjectID) but I suspect you're not at an edge case.


SQL Server: Get location of transaction log from MDF file

This is stored in a couple or locations in the MDF.

the base table sysprufiles - I've checked a few databases installed locally and found it on page 155 and page 153 of the ones I've checked. I imagine this will always be fairly early in the file but it doesn't appear to have a fixed location.
sysfiles1 - which is reliably on page 32 for every database I have checked so far.

The initial version of this answer used a hex editor to read it from the MDF.

Dan Guzman points out there is an undocumented DBCC command that is  massively simpler.

DBCC CHECKPRIMARYFILE ( N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf',1);

Returns a result like

+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+
| fileid | groupid | size  | maxsize | growth | status  | perf |          name           |                                           filename                                           |
+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+
|      1 |       1 | 26240 |      -1 |   2048 |       2 |    0 | AdventureWorks2012_Data | C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf |
|      2 |       0 |   128 |      -1 |     10 | 1048642 |    0 | AdventureWorks2012_Log  | C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf  |
+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+

I was curious as to how that approach worked so looked at the activity in process monitor.

This ends up reading the file header (page 0), database boot page (page 9) and page 32 of the file.

I didn't see page 32 mentioned on either of the preceding pages so I think that page is hardcoded to contain the information rather than being looked up from them.


How to refer to individual columns of a record returned by a function?

The problem, I guess, hits everyone starting with PostgreSQL.  Fortunately, the solution is rather easy: simply call

SELECT * FROM circular_ref();

instead of

SELECT circular_ref();

This way it won't be a single record you get but a proper row.  This way, you can even do

SELECT id, path FROM circular_ref();

to get the columns you want.


Consider using Ola Hallengren's maintenance scripts. His scripts are community agreed upon gold standard for this type of work. His scripts will break out each server and instance into separate folders for you automatically.

Going to one location is fine. Some companies use a Data Domain which a SAN built for backups. You essentially write to a central network share. Writing to a single location for backups is not uncommon.

Now....some things to consider when doing this

network bandwidth to the device you are writing too
network bandwidth from the device you are backing up
compute power of the device you are writing to

The idea with these items is to think about not overwhelming your backup location. You also do not want to kick off too many backups on one server and use all of the network bandwidth on your server. This would cause timeouts and other client side issues. Maybe break up the workloads by environment. Non-prod starts at 5pm (the end of the developer day), while production starts at 8pm (the end of the business day). This way your backups are not all firing off at all once. You may need to adjust further based on what results you receive.

Test, test and test more with a small subset of servers. Then you can adjust to scale out for the larger subset of servers and databases.


Table redirect / filter / trigger on select

You could do something like this:

create or replace package body pkg1
is
...
begin
  dbms_application_info.set_module('PKG1');
end;
/

create or replace package body pkg2
is
...
begin
  dbms_application_info.set_module('PKG2');
end;
/

You can then access the module information using SYS_CONTEXT():

create or replace view v1
as
select
  case
    when sys_context('USERENV','MODULE') = 'PKG1' then to_char(grade_pct)
    when sys_context('USERENV','MODULE') = 'PKG2' then calc_grade_letter(grade_pct)
    else ''
  end
...
from
  yourtable

Instead of the function calculating the letter from percentage you could use a view to select from, of course. Be aware that the performance of queries against that view will likely suffer because of all the PL/SQL calls.


What you're running into is probably a misunderstanding with how DATEADD works.

If you run these queries, you'll see what I mean:

DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.

If you need to flatten a datetime to the beginning of the day, you need to do something like this.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);

You can check out my answer here for more information:

How does date math work in SQL Server?


Identify changes across records for the field ID

SELECT id
FROM table
GROUP BY id
HAVING COUNT(DISTINCT gender) != 1
    OR COUNT(DISTINCT FirstName) != 1


Help to decrese query time in huge table of 100M+ rows on Ms-SQL Server

To make this work well you need to create a separate query for each sort order, and have indexes that support the OFFSET .. FETCH without needing to sort the whole table first.  For the query part you can use IF statements with static SQL, or dynamic SQL.  Here's how to do this with some filtered indexes and dynamic SQL:

--drop table if exists users
go
create table users(id int primary key, name nvarchar(200), address nvarchar(200), city nvarchar(200), deleted_at datetime2 null)
go
create index ix_users_city on users(city) where deleted_at is null
create index ix_users_name on users(name) where deleted_at is null
create index ix_users_address on users(address) where deleted_at is null
go

CREATE OR ALTER PROCEDURE BaseUserIndex
    @Offset int,
    @Limit int,
    @SortCol int,
    @SortDir nvarchar(10),
    @Search nvarchar(255) = NULL
AS BEGIN
/*
  exec BaseUserIndex 0,100,2,'asc'
*/
    SET NOCOUNT ON;

    declare @sql nvarchar(max) = concat(N'
    SELECT id,
    name,
    address,
    city
    FROM users
    WHERE deleted_at IS NULL
    ORDER BY ',
        case
        when (@SortCol = 0 and @SortDir='asc')  then N'name asc'
        when (@SortCol = 0 and @SortDir='desc') then N'name desc'
        when (@SortCol = 1 and @SortDir='asc')  then N'address asc'
        when (@SortCol = 1 and @SortDir='desc') then N'address desc'
        when (@SortCol = 2 and @SortDir='asc')  then N'city asc'
        when (@SortCol = 2 and @SortDir='desc') then N'city desc'
        end
        ,N'
    OFFSET ', @Offset, N' ROWS
    FETCH NEXT ', @Limit, N' ROWS ONLY'
    )

    --print @sql
    exec ( @sql )

END


Reportviwer matrix show all months/weeks of year

At a high level:

Create a temporary table (a subquery or CTE could be used as well, it's up to you) with the week numbers you want to show, and their corresponding month numbers.

Then modify your query as follows:

SELECT b.ID_Equip_PIMP]
      ,b.[Contrato_Nr]
      ,b.[NomeCliente]
      ,b.[NomeInstituicao]
      ,b.[NomeServico]
      ,b.[NomeMarca]
      ,b.[NomeModelo]
      ,b.[VALOR_CEL]
      ,b.[Data_Plano]
      ,b.[Data_Execucao]
      ,b.[NomeRotina]
      ,b.[TipoRotina]
      ,b.[NomeCategoria]
      ,CASE a.[Mes]
            WHEN 1 THEN 'JANEIRO'
            WHEN 2 THEN 'FEVEREIRO'
            WHEN 3 THEN 'MARÇO'
            WHEN 4 THEN 'ABRIL'
            WHEN 5 THEN 'MAIO'
            WHEN 6 THEN 'JUNHO'
            WHEN 7 THEN 'JULHO'
            WHEN 8 THEN 'AGOSTO'
            WHEN 9 THEN 'SETEMBRO'
            WHEN 10 THEN 'OUTUBRO'
            WHEN 11 THEN 'NOVEMBRO'
            WHEN 12 THEN 'DEZEMBRO'
       END AS [Mes_Nome]
      ,b.[ID_Equipamento]
      ,b.[Nome]
      ,b.[Marca]
      ,b.[Modelo]
      ,b.[Categoria]
      ,b.[Num_Serie]
      ,b.[Num_Inventario]
      ,b.[Num_Equipamento]
      ,b.[Dia]
      ,a.[Semana]
      ,a.[Mes]
      ,b.[Trimestre]
      ,b.[Semestre]
      ,b.[Ano]
  FROM #temp_table a
         LEFT OUTER JOIN (
                          /*  Place entire current query here, except ORDER BY  */
                         ) b ON (a.Mes = b.Mes AND a.Semana = b.Semana)
 ORDER BY /* place current query's ORDER BY here */
;

The temp table will ensure that you have at least one row for every week; however, if there's no actual data for that week, all the related values except the week and month will be NULL.

You may have to make adjustments on the reporting side, depending on how that handles NULL values.


Optimistic concurrency check failure when updating linked server table with timestamp column

Do the update remotely:

exec RemoteServer.MyDB.dbo.sp_executesql N'UPDATE Customer
    set    FirstName = @FirstName,
       LastName = @LastName
     where  AccountNumber = @AccountNumber',
     N'@FirstName varchar(...), @LastName varchar(...), @AccountNumber <type>',
     @FirstName, @LastName, @AccountNumber;

Must enable RPC and RPC Out on the linked server.


From the EXPLAIN output, it looks like first table userdata_langue would benefit from an index that contains both id_langue and id_inscri. If there is already an index on that combination, try forcing it.

The second table userdata_sexe could be partitioned on sexe (I assume there are only two options here?). This would speed up the scan required to fetch all those.

I am not sure if mySQL would benefit from moving the order of the UNION DISTINCT components around. It might be worth trying to move component 3 and 4 up to the top of the query.

Finally, consider if you can afford to increase the sort buffer size in InnoDb (see: http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/) to make sure you can hold everything in memory while mySQL calculates the UNION DISTINCT.


Secondary mysql instance runs but mysqld_multi reports it is "not running"

Check creation mysql instance dir, confirm mysql rights and install instance in dir
mkdir -p /u01/data/mysql/mysql1 && chown mysql:mysql /u01/data/mysql /u01/data/mysql/mysql1
mysql_install_db --user=mysql --datadir=/u01/data/mysql/mysqld1


uninstalling SSRS and SSIS from clustered SQL Server 2008 R2 SP1

Neither of these components are cluster aware and they will be separate from the database install, so you would just run the SQL 2008 R2 Add/Remove programs from the control panel of each cluster node and select uninstall for both of these components.  A reboot may be required to take effect.


Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"

From what I’ve found, there is a special character in the label on the instance that doesn't work and throwing the error.
Instance causing issues
SQL> SELECT ASMNAME_KFDSK, FAILNAME_KFDSK, LABEL_KFDSK FROM x$kfdsk;

ASMNAME_KFDSK                  FAILNAME_KFDSK                 LABEL_KFDSK
------------------------------ ------------------------------ -------------------------------
DG01_DATA_0000                 DG01_DATA_0000                 ▒▒▒▒▒u▒▒▒▒▒
DG01_DATA_0001                 DG01_DATA_0001

Instance working fine (From the +ASM instance)
SQL> SELECT ASMNAME_KFDSK, FAILNAME_KFDSK, LABEL_KFDSK FROM x$kfdsk;

ASMNAME_KFDSK                  FAILNAME_KFDSK                 LABEL_KFDSK
------------------------------ ------------------------------ -------------------------------
DG01_DATA_0000                 DG01_DATA_0000

ASM Provisioning String Has Junk Characters "kfdhdb.driver.provstr:ORCLDISKÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ " ( Doc ID 2088023.1 )


Change

    call.duration,
-- agentactivity.State
FROM shorewarecdrarchive.connect

to

call.duration --removing the comma
-- agentactivity.State
FROM shorewarecdrarchive.connect

or

call.duration,
agentactivity.State --uncommenting this line
FROM shorewarecdrarchive.connect

The problem is the trailing comma at the end of call.duration


Query to List Encryption Certificate for Databases

You can join on the certificate thumbprint:

use master;
go

select
    database_name = d.name,
    dek.encryptor_type,
    cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

My sample output:

database_name           encryptor_type    cert_name
=============           ==============    =========
tempdb                  ASYMMETRIC KEY    NULL
AdventureWorks2012TDE   CERTIFICATE       TdeCert


To resolve this, I did the following:

Sample data table.

CREATE TABLE city_state
(
  location VARCHAR(100),
  city VARCHAR(100)
);

Sample data.

INSERT INTO city_state VALUES ('Minneapolis - Minnesota', 'Minneapolis'); -- note, space between hyphen and Minnesota
INSERT INTO city_state VALUES ('Orange County - CA', 'Orange County');
INSERT INTO city_state VALUES ('Denver - Colorado', 'Denver');
INSERT INTO city_state VALUES ('Dowingtown-Pennsylvania', 'Downingtown');  -- note, no space between "Downington" and "Pennsylvania" - also,
INSERT INTO city_state VALUES ('Manhattan-New York', 'Manhattan');
INSERT INTO city_state VALUES ('Ventura-California', 'Ventura');
INSERT INTO city_state VALUES ('Winston-Salem-North Carolina', 'Winston-Salem');  -- note, compound place name, i.e. Winston-Salem

For the Winston-Salem example, check here!

As pointed out by @Dudu Markovitz, you have to select from the last hyphen.

SELECT SUBSTRING_INDEX( location, '-', -1) FROM city_state;

But, there are also fields with spaces between the hyphen and those without, so a more complete answer would be:

SELECT TRIM(SUBSTRING_INDEX( location, '-', -1)) FROM city_state;

Minnesota
CA
Colorado
Pennsylvania
New York
California
North Carolina

Note, no spaces preceding (or following) the location name.


Query for existence of objects across two multi-to-one relationships

You can add this restriction with either a NOT EXISTS subquery or with LEFT JOIN / IS NULL:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'foo' AND value = 'bar'
) as SQ1 on SQ1.cfg = MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'fuz' AND value = 'baz'
) as SQ2 on SQ2.cfg = MyObject.cfg
WHERE MyObject.value = 42
  AND NOT EXISTS
      ( SELECT 1
        FROM ConfigKV
        WHERE ConfigKV.cfg = MyObject.cfg
          AND NOT ( key = 'foo' AND value = 'bar'
                 OR key = 'fuz' AND value = 'baz'
                  )
      ) ;


Community wiki answer:

MERGE has some issues you should be aware of, see Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.

More specifically to your present situation, MERGE provides no additional protection against concurrency issues beyond an automatic containing transaction. You would need to add a SERIALIZABLE hint (or its synonym, HOLDLOCK) to the MERGE statement as well, see “UPSERT” Race Condition With MERGE by Dan Guzman. Note that an UPDLOCK hint is not necessary with MERGE.

For a more general overview of the issues and options, see the following by Michael J. Swart:

SQL Server UPSERT Patterns and Antipatterns
Mythbusting: Concurrent Update/Insert Solutions

A popular and robust solution is to add a WITH (UPDLOCK, SERIALIZABLE) hint to table access in the NOT EXISTS check, with a containing transaction around the whole UPSERT operation.

Using your example:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orders () VALUES ()
ELSE
    UPDATE Orders
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

Or equally:

BEGIN TRANSACTION;

INSERT INTO Orders ()
SELECT ...
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
);

IF @@ROWCOUNT = 0
BEGIN
    UPDATE Orders
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

The order of the INSERT and UPDATE operations can be switched so that whichever you expect to succeed most often is tried first. The hints must always be applied to the first operation.

For example, if updates are expected to be most numerous:

BEGIN TRANSACTION;

UPDATE Orders WITH (UPDLOCK, SERIALIZABLE)
SET ...
WHERE OrderLineID=@OrderLineID
AND LastModified<@CreatedTime;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Orders ()
    SELECT ...
END;

COMMIT TRANSACTION;

The uniqueness constraint mentioned in the question is a good idea for consistency in general, but not strictly needed for the methods noted here. If you were to use Michael Swart's Just Do It pattern, a uniqueness guarantee would be required for correct operation.

As a reminder, if you prefer MERGE for whatever reason, you must use a SERIALIZABLE hint against the target table.

While it is true in general that locking hints should be avoided as far as possible, this is one example of a valid use case.


MariaDB I/O capacity with innodb_io_capacity parameter

It looks to me that you should increase that parameter - what I would advise is that you set up database benchmarking  and vary the parameter in steps of 1000 until you find a sweet spot. There are any number of MySQL benchmarks - sysbench for example. You could try mysqlslap - it's "quick and dirty" but should be good for a first approximation - 1, 2, 3, 4, 5 and check out the documentation. I've been messing with this recently so I have the links to hand.


Assuming you have taken log backups since the log file grew, you should be able to shrink the file without changing recovery models.

But you absolutely, 100% have to understand that shrinking the log file is an absolutely useless activity if the log file is going to grow again. Let it stay as big as it's going to get during normal operations, because shrinking it to free space temporarily doesn't gain you anything. What will you use that space for today when you know that tomorrow the log file will grow again? This is like watering the lawn right before a thunderstorm. Perhaps you are not backing up the log often enough? Doing it more often

Please read this in full before proceeding. And I really mean in full. Like, the whole thing.

Now, if you have read that entire page, and are absolutely, 100% sure that you need to shrink the log file, and that it is a worthwhile activity because you know the log file won't grow again, then:

USE your_database;
GO
DBCC SHRINKFILE(your_database_log, 100); -- hypothetical, target size is in MB
GO

If you don't know that the log file won't grow again, put the mouse down and leave the log file alone.


SQL80001 is a SQL Server error message. SQL Server doesn't support the old Oracle (+) syntax for outer joins.

Re-write your SQL using LEFT/RIGHT OUTER JOIN .... ON .....


Selecting multiple rows by given amount of volume

You would need to use a reduction method to slowly calculate the parts that make up the target volume. For instance 1.75, you would as you say be 1.00, then 0.5 then 0.25. You can use a loop to calculate the remainder using modulus, then use that remainder to find the next largest volume less than or equal to your value.

This is how I would do it (there might be better ways but this works)

CREATE TABLE VolumePrice (
    id int not null,
    volume decimal(9,4) not null,
    price decimal(9,2) not null
);

insert VolumePrice (id, volume, price)
values (1, 1.0000, 14250.00),
       (2,0.2500,14251.10),
       (3,0.2500,14250.00),
       (4,0.0050,14200.00),
       (5,0.5000,14255.01);

-- create a temp table to hold the output
create table output (
    id int auto_increment not null primary key,
    remainder decimal(9,2),
    volume decimal(9,2)
);

DELIMITER //

create procedure do_insert()
begin
set @v = 1.75;
set @remainder = @v;

while @remainder > 0
do
    insert output(remainder, volume)
    select @remainder % max(volume)
         , max(volume) as volume
      from volumeprice
     where volume <= @remainder;

  set @remainder = (select remainder
                      from output
                  order by id desc
                     limit 1);
end while;
end;
//

DELIMITER ;

CALL do_insert();

select v.*
  from output as o
  join (select volume, max(price) as max_price
          from volumeprice
      group by volume) as vp on vp.volume = o.volume
  -- I only link back to the volumeprice table here to obtain the id,
  -- if you do't need the id you can remove this part and just select vp.* instead...
  join volumeprice as v
    on v.volume = vp.volume
   and v.price = vp.max_price;

And here is a SQL Fiddle that seems to do the trick.


Using 100's of schemas in a database SQL2008R2?

One problem you'll have is with SSMS. I have a client with a lot of objects in a single database, and if we expand the Tables node in Object Explorer, SSMS dies.

As well as this, I suspect your code might need to be changed. Either you need to rely on the default schema for every connection, or else you need to change every query. Hopefully you've been using two-part naming so far, so every dbo.reference will need to change. Far from ideal.

You still have a lot of flexibility over filegroups, which is good, and having a single database might even help avoid disk head movement on the transaction log.

I doubt performance will be affected much either way. But the code changes required could be a huge frustration. And if you need to rely on the default schema, there are extra locks (LCK_M_X) that need to be taken out to work out which objects you're referring to.

I'd be really hesitant to redesign that much, but it really depends on your priorities.


Justify NOT using (nolock) hint in every query

You pick your battles and battles like this can't be easily won. We have a system where every DML is hinted with the ROWLOCK hint (irrespective of modifying one row or several thousand rows). I showed several examples why it really hurts performance but as the system is already working, there is resistance to change. Note that I convinced them enough NOT to use this going forward though.
NOLOCK has it place but I can recommend some good references showcasing the troubles of using it:

Microsoft SQL Server Development Customer Advisory Team blog – Previously committed rows might be missed if NOLOCK hint is used
Itzik Ben Gan at SQL Magazine – Clustered Index Scans Part III
Itkiz Ben Gan on sqlpass.org – Beware the NOLOCK hint


You need to use a LEFT OUTER JOIN to achieve this:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE ta
( id INT NOT NULL,
  name VARCHAR(20) NOT NULL
) ;

INSERT INTO ta
  (id, name)
VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e'),
  (6, 'f') ;

CREATE TABLE TB
    (`id` int, `p_id` int, `name` varchar(20))
;

INSERT INTO TB
    (`id`, `p_id`, `name`)
VALUES
    (1, 1, 'a'),
    (2, 1, 'b'),
    (3, 1, 'c'),
    (4, 2, 'a'),
    (5, 2, 'b'),
    (6, 2, 'd')
;

GO

CREATE PROCEDURE test
(my_p_id INT)
BEGIN
  SELECT TA.id, TA.name,
         (IF(TB.name IS NOT NULL, 1, 0)) AS `match`
    FROM TA
    LEFT JOIN TB
      ON TA.name = TB.name
     AND TB.p_id = my_p_id;

END;

Query 1:

CALL test(1);

Results:

| ID | NAME | MATCH |
|----|------|-------|
|  1 |    a |     1 |
|  2 |    b |     1 |
|  3 |    c |     1 |
|  4 |    d |     0 |
|  5 |    e |     0 |
|  6 |    f |     0 |

Query 2:

CALL test(2);

Results:

| ID | NAME | MATCH |
|----|------|-------|
|  1 |    a |     1 |
|  2 |    b |     1 |
|  3 |    c |     0 |
|  4 |    d |     1 |
|  5 |    e |     0 |
|  6 |    f |     0 |

The query of interest is wraped in the procedure test. That makes working with SQLFiddle a little easier. But you certainly can use the query outside of a procedure. However, pay attention to the fact that the comparison to the parameter value is part of the LEFT JOIN evaluation.

For more informations on JOINs check out my JOIN series here: http://sqlity.net/en/1146/a-join-a-day-introduction/

That series is written about SQL Server. But most concepts and most of the syntax apply to MySQL as well.


Mysql 5.5.45 crashing after refreshing web page rapidly(Too Many Connection warning)

If that snapshot really says you are doing it, do not mix mysql_* interface calls with mysqli_* calls.  That could cause mysterious problems.

Do not use mysql_* at all.

You could try raising max_connections to 400.  But that is just a bandaid.


You use built-in table-valued functions (TVFs) as described in Books Online : Using Change Data (MSDN).

First you need to find the LSNs you want to use to filter the ranges you want to query. These LSNs will represent points in the log which you can correlate to datetime values. You can do this using sys.fn_cdc_map_time_to_lsn. Let's say you want to report on all the change data captured today:

DECLARE
  @s DATE = SYSDATETIME(),                  -- start today at midnight
  @e DATE = DATEADD(DAY, 1, SYSDATETIME()), -- end tomorrow at midnight
  @sl BINARY(10),                           -- start LSN
  @el BINARY(10);                           -- end LSN

SELECT
  @sl = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @s),
  @el = sys.fn_cdc_map_time_to_lsn('largest less than',              @e);

SELECT @sl, @el; -- start LSN and end LSN within today

Then you can use those LSN values in the cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes functions, depending on what you are after (let's say, for a table called dbo.floob):

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_floob(@sl, @el, 'all');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_floob(@sl, @el, 'all');


Is Oracle 18c XE good enough to start learning to be a DBA?

The "First Commandment" of any DBA is: Thou Shall Not Lose Data!

To accomplish this, you'll want to teardown, build, and restore/recover the database multiple times.  To that end, I'd recommend you use some sort of virtualization (eg Oracle VirtualBox).

You might be able to find both Oracle EE and MySQL pre-made virtual guests for VirtualBox.

I believe RMAN on XE is limited.  So, for backup/recovery practicing, I'd use EE.

Beyond that, any edition (of any RDBMS) would do.


Is this a named instance? If so, you need to ensure that the SQL Server Browser service is started. If it is not, you will not be able to connect.

Also, can you ping this server remotely? How about telnet to port 1433 (default port for the default instance)?

Check your SQL Server error log to see if there is any unusual activity and errors.

That's a start to troubleshooting this problem.


Optimze performance for LIMIT / OFFEST on collected result of multiple queries

There is a general technique to achieve this, with UNION ALL and LIMIT:

SELECT 1 ...
UNION ALL
SELECT 2 ...
UNION ALL
SELECT 3 ...
LIMIT N;

Postgres evaluates nested SELECTs in order and stops as soon as enough rows have been returned. The rest is never executed.

This optimization does not happen with an outer ORDER BY, which forces Postgres to collect all candidate rows and sort before applying the LIMIT. Nor does it work for UNION (instead of UNION ALL) which also considers all rows before removing duplicates and, finally, the LIMIT.

You need parentheses around each nested SELECT that has ORDER BY or LIMIT in addition to the outer LIMIT.

Related:

How to force order of WHERE clauses in PostgreSQL?
Optimize a query on two big tables
How to give precedence to rows from one table over matching rows in another?
Constraint exclusion to fetch latest N rows


I would like to share how I have fixed this issue.

I just created a fresh mysql dir with mysql_install_db and then copied all other database directory into it and it worked.


Table design for linking pictures to a place

I don't see there is much more difference in the design and given the choice I would choose to have a single table to store these picture.

Single table removes the need to have JOIN in sql query and will improve the performance.

In a single table structure you can create Indexes depending on your sql query used to read data that will improve the performance too.


Here is a start, if I can guess that by row you actually mean column:

SELECT
  [table]  = QUOTENAME(OBJECT_SCHEMA_NAME([t.object_id])) +'.'+ QUOTENAME(t.name),
  [column] = c.name
FROM sys.tables AS t
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
ORDER BY t.name, c.column_id;

This doesn't provide exactly the output you want, but surely your presentation tier can - while it is looping through all of the rows - figure out to only list a table when it comes across a new one.


How to benchmark disk performance from within SQL Server?

To get a general sense of I/O speed on the disks, you can perform any of these disk-intensive maintenance tasks.  Note that these won't really help if you're trying to test for a specific workload, but if you're purely interested in disk I/O driven by T-SQL commands only, this is a good "easy" solution.

Create or Rebuild Indexes

This will give you both reads and writes, as tables will be scanned, and then written to new indexes in the specified index order.  This is probably most helpful if you can do it on tables with lots of rows, or big data types.

DBCC CHECKDB

Running DBCC CHECKDB on a database will generate a large amount of reads, as it's checking for corruption throughout all of the indexes and tables in the database.  Since you're on 2008 R2, which doesn't have parallel CHECKDB, the utility of this option might be limited.

Database backups

If you weren't on RDS (which restricts backups to be performed to S3 through a wrapper stored procedure), running a native SQL Server backup would be a good option to push read and write I/O.

You may still be able to test read I/O by backing up to the NUL device.  See BACKUP (Transact-SQL), and especially:

  The NUL device can be used to test the performance of backups, but should not be used in production environments.

The general approach with any of these solutions would be to get a before and after from sys.dm_io_virtual_file_stats.  There is an example of how to do that in the Paul Randal blog post you linked to in the question.