inserting a special character into a table

It is possible to add that character as long as either:

the destination column is NCHAR / NVARCHAR / XML (NTEXT also works, but has been deprecated since the release of SQL Server 2005 so don't use it),
OR

the destination column is CHAR / VARCHAR and the Collation of the column uses a Code Page that supports the character(s) in question.

So, make sure to do the following:

Prefix the string with N (not required if the current Database's default Collation uses a Code Page that supports the character, but helps in that it removes the Database's default Collation from being a factor that could cause this issue -- reason being that if the current DB's default Collation uses a Code Page that doesn't support the character, then it will be converted to either a "best fit" match, or ? if no best fit match can be found, while it is being held in a variable or string literal).
Along those same lines, if this is coming into a stored procedure or parameterized query batch via a parameter and the parameter is of type CHAR or VARCHAR, then the current Database's default Collation does not use a Code Page that supports the character, so switch to using NVARCHAR / NCHAR as the parameter datatype.
If the destination column is VARCHAR / CHAR, then be sure that the Collation of the column uses a Code Page that supports that character.
If the destination column is VARCHAR / CHAR and you cannot find a Collation that uses a Code Page that supports the character(s) in question, then you need to convert the column to NVARCHAR / NCHAR so that it can store any character.   ( This is what worked for the O.P. )

For more info on working with Collations, please visit: Collations.info


Using expressions in the SELECT list to thin down a CASE statement

T-SQL doesn't support Booleans like that. But you could use CROSS APPLY with a subquery that uses CASE to produce a bunch of 1s and 0s, and then combine them in the SELECT clause using the bitwise operators & and |.


How do I make an index for an OR query in MongoDB?

You're hitting SERVER-13732.

For the query planner to properly consider indexes and apply the appropriate index bounces, the query will need to be reformatted to the following structure:

{
       "$or" : [
            {
                    "association.ownerId" : ObjectId("55e5c40fe4b03984a0528f89"),
                    "visitorId" : 384975548,
                    "created" : {
                           "$gte" : ISODate("2016-09-18T22:00:00Z"),
                           "$lte" : ISODate("2016-09-19T22:00:00Z")
                    }
            },
            {
                    "association.organisationId" : ObjectId("55e6d92ee4b010a9ebff510d"),
                    "visitorId" : 384975548,
                    "created" : {
                           "$gte" : ISODate("2016-09-18T22:00:00Z"),
                           "$lte" : ISODate("2016-09-19T22:00:00Z")
                    }
            }
    ]
}

It should then be able to use the combination of { "association.ownerId" : 1, "visitorId" : 1, "created" : 1 } and { "association.organisationId" : 1, "visitorId" : 1, "created" : 1 }. That being said, you should try testing the re-structured query pattern first using .explain().


WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

The correct syntax for this would be:

SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR
FROM MyTable
WHERE MyID = 2074163 AND (
    DATEDIFF(M, TheDate, TheOtherDate) >= 0 AND
    DATEDIFF(M, TheDate, TheOtherDate) <= 4
)


Is it possible to perform skip N rows and fetch M rows without full scan

The standard approach to get rows N through M is to do something like

SELECT *
  FROM (SELECT a.*, rownum rnum
          FROM (SELECT emp_id, last_name
                  FROM employees
                 WHERE positionID in (1,3)
                 ORDER BY <<something>>) a
         WHERE rownum <= 60) b
 WHERE rnum > 50

Note that you need to include the order by in the query in order for it to make sense.  Oracle may use a table scan (depending on whether or not positionID and/or the column(s) in the order by are indexed) but there will be a stopkey limiter so Oracle knows it can stop scanning the table (or the index) once 60 rows have been read.

In 12.1 and later, you can simplify this a bit

SELECT emp_id, last_nae
  FROM employees
 WHERE positionID in (1,3)
 ORDER BY val
OFFSET 50 ROWS
 FETCH NEXT 10 ROWS ONLY

More examples of using the new row limit clauses in 12.1


Statement based updates to a server using row based logging

The logs will be applied in the format which they are consumed, so on Server B statement based execution is applied.

Whether or not Server B even produces binary logs depends on whether or not binary logging is enabled on Server B. https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_bin
It is not required if the server is only to be a slave.

Even then, Server B will only produce binary logs related to the updates it receives from Server A if log_slave_updates is set
https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_log-slave-updates

The resulting log format from the slave updates depends only on what binlog_format is set as on the slave https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_binlog-format

Keep in mind that Server A(statement format) to Server B(row/mixed format)  will work fine, but Server A(row/mixed format) to Server B (statement format) will fail. See this question for further explanation
https://dba.stackexchange.com/a/159905/129765

To answer your question tersely, Server B executes the statement based SQL and then translates it to row based SQL which it outputs to the binary logs.


Use TSQL, SSMS, or Powershell, per:  Remove an availability group listener (SQL Server)


Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

This thread is discussing one example of deadlocks.  There are dozens, maybe hundreds, of ways to get a deadlock.

Sure, sorting the ids might prevent the IN(100,200) case, but what about all the rest?  Sure, go ahead and sort the ids.  And be sure to have suitable indexes.  And so on.  These might at least diminish the frequency of deadlocks.

Another example of a deadlock cause:  Lack of an index that leads to a table scan instead of an index lookup.  I think this can lead to locking all rows in the table, thereby both slowing things down and by having more locks, thereby begging for a deadlock.

I'll tell you what I have said many a time:  Deadlocks happen; live with it.  Be ready to restart the transaction from the BEGIN.

If you ever get a second deadlock, you should probably abort the program and spend some quality time figuring out how to speed up the entire transaction.  That is the best way to minimize deadlocks.

(OK, I'll admit that I don't know the definitive answer about IN().  Long ago (ver 4.1?), I thought I had proof that sorting was beneficial.  Later I heard that MySQL sorted the IN list, thereby killing my theory.  Or is all this hearsay evidence?)


What does "Rows Removed by Index Recheck" without "lossy" mean?

Some index strategies don't definitely conclude that a tuple meets the criteria.  They can definitely and rapidly eliminate most tuples which provably can't meet the criteria (which is where the performance gain comes from) but some of the ones that pass might be false positives, so need to be rechecked.

I guess you are using gin_trgm_ops, which is an example of such an indexing strategy.


MS Access Vs MySQL

MS Access works only on Windows whereas MySQL works on almost all platforms.
MS Access does not support Partitioning feature whereas MySQL has composite, Range partitioning support.
MySQL is opensource so no cost involved.
Performance in MySQL is way better than MS Access
MS Access database is more suitable for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Microsoft Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server.
Security - MS Access is limited to security in terms of user-name/password on the database. Where as MySQL is much more secured and you can configured it with SSL support.
MySQL is the most famous or rather more loved amongst the professional folks because of it's multiple choice in custom storage engines. You can have custom storage engines developed in MySQL based on your requirements or choose amongst the inbuilt ones
Actually there is no comparison between MySQL as a real commercial heavy duty database, and MS Access which is typically a local use, giveaway in Microsoft Office. It is no good for remote access, its' connection system is not solid. It is generally useless for multiple user purposes.And you are far better using a web based interface for mysql, VB is also limited in functions and power. Consider using one of the free distributions of the Apache Web server which carries php as the scripting language, and the mysql database pre-configured.

Go ahead with MySQL and experience it.


Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

There are many reasons why you may not have missing index requests!

We'll look at a few of the reasons in more detail, and also talk about some of the general limitations of the feature.

General Limitations

First, from: Limitations of the Missing Indexes Feature:

  It does not specify an order for columns to be used in an index.

As noted in this Q&A: How does SQL Server determine key column order in missing index requests?, the order of columns in the index definition is dictated by Equality vs Inequality predicate, and then column ordinal position in the table.

There are no guesses at selectivity, and there may be a better order available. It's your job to figure that out.

Special Indexes

Missing index requests also don't cover 'special' indexes, like:

Clustered
Filtered
Partitioned
Compressed
XML-ed
Spatial-ed
Columnstore-d
Indexed View-ed

What columns are considered?

Missing Index key columns are generated from columns used to filter results, like those in:

JOINs
WHERE clause

Missing Index Included columns are generated from columns required by the query, like those in:

SELECT
GROUP BY
ORDER BY

Even though quite often, columns you're ordering by or grouping by can be beneficial as key columns. This goes back to one of the Limitations:

  It is not intended to fine tune an indexing configuration.

For example, this query will not register a missing index request, even though adding an index on LastAccessDate would prevent the need to Sort (and spill to disk).

SELECT TOP (1000) u.DisplayName
FROM dbo.Users AS u
ORDER BY u.LastAccessDate DESC;

Nor does this grouping query on Location.

SELECT TOP (20000) u.Location
FROM dbo.Users AS u
GROUP BY u.Location

That doesn't sound very helpful!

Well, yeah, but it's better than nothing. Think of missing index requests like a crying baby. You know there's a problem, but it's up to you as an adult to figure out what that problem is.

You still haven't told me why I don't have them, though...

Relax, bucko. We're getting there.

Trace Flags

If you enable TF 2330, missing index requests won't be logged. To find out if you have this enabled, run this:

DBCC TRACESTATUS;

Index Rebuilds

Rebuilding indexes will clear missing index requests. So before you go Hi-Ho-Silver-Away rebuilding every index the second an iota of fragmentation sneaks in, think about the information you're clearing out every time you do that.

You may also want to think about Why Defragmenting Your Indexes Isn’t Helping, anyway. Unless you're using Columnstore.

Adding, Removing, or Disabling Indexes

Adding, removing, or disabling an index will clear all of the missing index requests for that table.  If you're working through several index changes on the same table, make sure you script them all out before making any.

Trivial Plans

If a plan is simple enough, and the index access choice is obvious enough, and the cost is low enough, you'll get a trivial plan.

This effectively means there were no cost based decisions for the optimizer to make.

Via Paul White:

  The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization.

When a plan is trivial, additional optimization phases are not explored, and missing indexes are not requested.

See the difference between these queries and their plans:

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2;

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation = 2
AND 1 = (SELECT 1);

The first plan is trivial, and no request is shown. There may be cases where bugs prevent missing indexes from appearing in query plans; they are usually more reliably logged in the missing index DMVs, though.

SARGability

Predicates where the optimizer wouldn't be able to use an index efficiently even with an index may prevent them from being logged.

Things that are generally not SARGable are:

Columns wrapped in functions
Column + SomeValue = SomePredicate
Column + AnotherColumn = SomePredicate
Column = @Variable OR @Variable IS NULL

Examples:

SELECT *
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 1000) > 1000;

SELECT *
FROM dbo.Users AS u
WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000

SELECT *
FROM dbo.Users AS u
WHERE u.UpVotes + u.DownVotes > 10000000

DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren'
SELECT *
FROM dbo.Users AS u
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo
      OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;

None of these queries will register missing index requests. For more information on these, check out the following links:

Optional Parameters and Missing Index Requests
SARGable WHERE clause for two date columns
What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?

You Already Have An Okay Index

Take this index:

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

It looks okay for this query:

SELECT p.OwnerUserId, p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20070101'
AND   p.CreationDate < '20181231'
AND   p.Score >= 25000
AND 1 = (SELECT 1)
ORDER BY p.Score DESC;

The plan is a simple Seek...

But because the leading key column is for the less-selective predicate, we end up doing more work than we should:

  Table 'Posts'. Scan count 13, logical reads 136890

If we change the index key column order, we do a lot less work:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

And significantly fewer reads:

  Table 'Posts'. Scan count 1, logical reads 5

SQL Server Is Creating Indexes For you

In certain cases, SQL Server will choose to create an index on the fly via an index spool. When an index spool is present, a missing index request won't be. Surely adding the index yourself could be a good idea, but don't count on SQL Server helping you figure that out.


oid as a type is a 32-bit unsigned integer that stands for object id, and is used by the system as an all-purpose surrogate key to refer to different objects and sometimes rows in pg_catalog. Being the type for keys to large objects  is just one of its many uses. Other "objects" like tables, sequences, types and more are refered to by their oid.

  But I am confused about pg_largeobject table structure, why its using
  both data types for same data?
  Is postgres internally storing oids as bytea, and just returning oids
  for reference? If its working in this manner, why can't use just
  numeric type for reference instead of oid type.

It stores the contents of large objects as small bytea pages (~2000 bytes)in the pg_largeobject table, with loid as the ID or the large object, and (loid,pageno) as the unique key for that table.
The generator of new values for loid can be compared to a integer sequence, except that after wrapping around at 2^32, it can avoid conflicts with existing values. As users, we don't have to care about this, it's managed by the internals.

  If not, what is the use of oid here and is any data sitting behind the
  oid somewhere in the database, and what is the bytea column data
  about.?

The oid is used as a handle and the bytea column holds the actual binary data.


Disabling Checking of Schema On Function/Stored Procedure Creation

You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See Erland's ideas for SET STRICT_CHECKS ON; to get some ideas of the places this works and the places it breaks:

http://www.sommarskog.se/strict_checks.html

(And how he'd like the polar opposite of what you're after - you want to allow anything to compile regardless of existence, and he wants every single column or table to be checked.)

There is no setting like SET DEFERRED_NAME_RESOLUTION OFF; though it has been asked for:

http://connect.microsoft.com/sql/127152

And there is no setting like IGNORE ALL_RESOLUTION;.

You could get around this in a few ways, including:

(a) use dynamic SQL in the affected stored procedure(s).

(b) build a stub for CREATE PROCEDURE with nothing in it, then run the rest of your script, then run an ALTER PROCEDURE which has the real body (in essence, deploy the procedure in two phases).

(c) make your deployment tool smarter about the order of operations. If table changes require the presence of a function, script those changes last. Schema comparison tools like RedGate's SQL Compare are pretty good about generating scripts for you in the proper dependency order. You don't mention what tool you're using, but if it's not doing this...

(d) Martin Smith has an interesting workaround here, but I haven't played with it.


Is there a way to use PIVOT syntax to accomplish the following?

--Unpivot the table.

SELECT Location, Account,Dept, Year, Month, Amount
FROM
   (SELECT Location, Account,Dept, Year, Month, Amount, January ... Dec
   FROM Table_A) p
UNPIVOT
   (Amount FOR Month IN
      (January, Feb ... Dec)
)AS Table_B;
GO

reference: link


Errcode: 13 is a file permission issue.

If your datadir is /var/lib/mysql, do the following:

cd /var/lib/
ls -l

Check the file permissions as make sure /var/lib/mysql is owned by mysql:mysql.

If it is not, then do this:

chown -R mysql:mysql /var/lib/mysql

If you ran sudo, you are probably root. Make sure root can write into /var/lib/mysql

Give it a Try !!!


SELECT *
FROM `sdriving_turno`
WHERE hora_inicio < NOW() AND hora_fin < '17:00:00'

you could also do something like

SELECT *
FROM `sdriving_turno`
WHERE hora_fin BETWEEN NOW() AND '18:00:00'


How to handle a results in a field where they could be NVARCHAR, FLOAT, or INT

Assuming your question is asking how you might define a table similar to your 'alternative', here is 'an' answer.

While I cannot tell you 'the recommended approach', I can say we have a table that is very similar to your situation.  We can store different kinds of data depending on other criteria.  Our table definition is similar to what your 'alternative' describes.

In our table, we have a column called DATA_TYPE CHAR(1) which identifies the type of data being stored.  I didn't design the table (as I would have opted for more than 1 character to identify the data type), but valid values are

C (Character)
D (Date)
I (Integer)
P (Decimal) - (it comes from a mainframe data type called packed-decimal)

(In your situation, you might also define F-Float, B-Bit, etc.)

We then have separate columns to hold the different data types

CREATE TABLE [SCFBDM].[CDB_AUDIT_LOG](
    [ENTITY_TYPE] [char](6) NOT NULL,
    [ENTITY_ID] [char](2) NOT NULL,
    [ENTITY_KEY] [datetime2](6) NOT NULL,
    [POLICY_NUMBER] [int] NOT NULL,
    [DEC_AMEND] [decimal](7, 3) NOT NULL,
    [ITEM] [datetime2](6) NOT NULL,
    [ENDORSEMENT] [char](6) NOT NULL,
    [ENDORSEMENT_ITEM] [datetime2](6) NOT NULL,
    [TABLE_OWNER] [char](8) NOT NULL,
    [TABLE_NAME] [char](18) NOT NULL,
    [ENTITY_OR_COLUMN] [char](1) NOT NULL,
    [ENTITY_COLUMN_NAME] [char](18) NOT NULL,
    [ROW_TYPE] [char](1) NOT NULL,
    [ACTION] [char](1) NOT NULL,
    [OVERALL_EFFECT] [char](1) NOT NULL,
    [ACCOUNT_NUMBER] [datetime2](6) NOT NULL,
    [DATA_TYPE] [char](1) NOT NULL,
    [DATA_CHARACTER] [char](35) NULL,
    [DATA_DATE] [date] NULL,
    [DATA_DECIMAL] [decimal](11, 2) NULL,
    [DATA_INTEGER] [int] NULL,
    [USER_ID] [char](8) NULL
)


Note the db.collection("players").find(query).toArray() returns an array of documents - these are available as result array. Your code console.log(result.balance) returned undefined - as you are looking for a property called balance in the array. You could do that on an object (or document) which has a property with that name - not on the array.
Retrieve an individual property from an array of objects - do the following to print each document's balance property value:
result.forEach(doc => console.log(doc.balance));

Here is your function's code in a slightly different way:
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';

MongoClient.connect(url, { useUnifiedTopology: true }, function(err, client) {
    if (err) throw err;
    console.log("Connected successfully to server");
    const db = client.db("DiscordDB");
    const id = "some value"; // input value

    findBalance(db, id, function(result) {
        console.log(JSON.stringify(result));
        result.forEach(doc => console.log(doc.balance));
        console.log("Closing connection.");
        client.close();
    });
});

const findBalance = function(db, id, callback) {
    const query = { uuid: id };
    db.collection("players")
        .find(query)
        .toArray(function(err, result) {
            if (err) throw err;
            console.log("Query ran okay");
        callback(result);
    });
};


Can I set different database directories in MongoDB?

All databases for a MongoDB server are created within the configured storage.dbPath directory.

You can't directly specify directory paths for new databases via an end user command, however you can enable the storage.directoryPerDB option in your mongod config to use a separate subdirectory for each database. The subdirectory name used is determined by the storage engine, but the default storage engines (WiredTiger in MongoDB 3.2+, MMAPv1 in earlier versions) currently base this on the database name. Since database names use UTF8, any special characters that might be problematic will be encoded when mapped to a subdirectory name.

With a directoryPerDB  configuration you can also mount or symlink subdirectories to different volumes in your filesystem (assuming the dbPath isn't in use when you change existing mount points). This can be useful if you want to separate I/O for different databases. Note: if you end up managing databases on different volumes this may also affect your backup strategy. For example, filesystem-level backups require a consistent backup of the full dbPath — you cannot independently backup database subdirectories in a MongoDB deployment.

An important caveat noted in the documentation:

  To change the directoryPerDB option for existing deployments, you must restart  mongod instances with the new directoryPerDB value and a new data directory (dbPath), and then repopulate the data.

The directoryPerDB config option can vary per member in a replica set, which can be useful for migration between different dbPath layouts without downtime.


Does schema normalization enhances query efficiency

It depends on the situation.

If normalization leads you to split your relations into multiple tables, and if you build those tables with primary keys and foreign keys which are defined to the database so that they are properly indexed, then in that sense query performance is enhanced.

Similarly, if your table is not in 1NF you might have to do expensive table scans and text parsing to find examples of a particular data item in a multivalued column.  In 3NF this column could be indexed making the query more efficient.

It's worth noting that normalizing your schema may also make your queries simpler and easier to maintain for the same reasons and in the same situations.

On the other hand, when data has become static (e.g. historical data) it is often denormalized to make retrieval faster.  This frequently takes the form of precalculating values, especially aggregate values.  This is the approach of data warehousing.  It is a good practice, as long as the risk of data insert/update/delete anomalies is very, very low or easy to manage.


Quiet installation of MS SQL 2012 RC0 from command line using config file

Have you specified the rest of the required command line parameters for a quiet install for instance IACCEPTSQLSERVERLICENSETERMS?

This help page covers the required parameters for a quiet command line install.


If you want to ensure you minimize the amount of time you are without HA during your migration, here's a trick I recently used:

Set up the AG with a "dummy" database so that your AG is ready in advance.
Log ship the database(s) to both your primary & secondary AG replicas--at this point, the log shipped database is not part of the AG, you're just doing the log shipping in parallel to all nodes.
When you're ready to cut-over:

Take your final tail log backup on the old server.
Restore the tail log to both AG replicas, and on the primary node, restore WITH RECOVERY.
Take a log backup from the primary node, and restore it to secondary WITH NORECOVERY. (At this point, your secondary node is pre-seeded and ready to be added to the AG)
Add the DB to the AG, without having to do backup/restore.


The grouping may be the same, but you have an asterisk (shortcut for select all field, but the sequence is unkown what prevents grouping and aggregation) at the end of your selected fields that causes the error.

If you want to get the sum of Transaction_Amount per ID you just group by ID not by Transaction_Amount

SELECT
    Sum([Account Transactions].Transaction_Amount) As SumAmount
  , [Account Transactions].ID
FROM
    [Account Transactions]
GROUP BY
    [Account Transactions].ID


Under Option 1, you are guaranteed a single row (if you remember to insert the one row firsthand).

If you want to introduce a new setting, you would have to do ALTER TABLE to add the needed column.
If you wanted to remove an old setting,  you would have to do ALTER TABLE to remove the unneeded column.
You would have to assume to the presence of the column in the code. Otherwise, you may have to query information_schema.columns to make sure the column representing the setting needed exists in the table or not. Querying the table information_schema.columns is extremely bad in the presence of dozens (or even hundreds) of InnoDB tables in a heavy write environment. That could potentially lock information_schema.columns intermittently until it is free to read.
You should make sure the table count is always one(1).

Under Option 2, things are a little different

If you want to introduce a new setting, you would have to do an INSERT to add the needed setting as a row.
If you wanted to remove an old setting, you would have to do a DELETE to remove the needed setting as a row.
You have to query the table by variable name to see if the option is present.
No virtual limit to adding to or deleting a setting from a table

What do these Options have in common ?

If you leave old settings alone, this can allow for old versions of app code to use the old settings, but that just introduces clutter into the table.
Storage Engine should be InnoDB to allow multiple people to read from it.

Personally, I would choose Option 2 because creating new settings, optionally removing old settings, and checking for existing settings would all occur at one table. The method of retrieving settings would all be the same. Under Option 1, you would have to query information_schema.columns plus make certain assumptions about the settings table. The sequence of events for retrieving settings may change from code version to code version.


Since you're on SSD you're better of disabling readahead. Seek time is not an issue for flash based drives. That's for the spinning era. It's more important to align the filesystem properly.

Postgresql is using 8k pages so best performance generally comes with matching blocksize.

With XFS use nobarrier and make the filesystem with proper agcount and logging. (https://serverfault.com/questions/222305/trying-to-determine-the-correct-number-of-xfs-allocation-groups-for-postgresql-s).

You can also benefit from having compressed file system like ZFS especially if your data cannot fit in cache and has to be read from disk. Usually I get 2-4x for databases which results in 2-4 times less blocks read from disk but that's very use case dependant. If you use ZFS use recordsize=8k, compression=lz4, primarycache=all and check if you need to set ashift for the particular disk (if it is using 512 byte or 4k blocks by running cat /sys/block/[device]/queue/physical_block_size). http://zfsonlinux.org/faq.html#PerformanceConsideration


Question concerning minimal/cannonical cover

The algorithm to compute the minimal cover of a set of dependencies requires that, when there are multiple attributes on the left part, we should eliminate the extraneous ones, that is the attributes such that the remaining ones still determine the right part.

In other words, if you have a dependency like BCDE → A, you should in turn try to eliminate each attribute on the left part, and see if A is contained in the closure of the remaining attributes.

Let’s try the algorithm in your case:

1. Try to eliminate B, and see if A ∈ CDE⁺; CDE⁺ = ABCDE, so B can be eliminated
2. Try to eliminate C from CDE, and see if A ∈ DE⁺; DE⁺ = ABCDE, so C can be eliminated
3. Try to eliminate D from DE, and see if A ∈ E⁺; E⁺ = BCE, so D cannot be eliminated
4. Try to eliminate E from DE, and see if A ∈ D⁺; D⁺ = ABCDE, so E can be eliminated

So, the dependency BCDE → A can be substituted by D → A. Applying this algorithm to all the dependencies (remember to check at the end if there are superfluous dependencies), produces the following canonical cover:

D → A
D → E
E → B
E → C


SSIS transfer SQL server objects task -> Foreign key constraint error

AS sugested by Billinkc we tried to first copy the table and then copy the relations. This worked as hooped.


How to model the relationships (a) between properties and owners and (b) between properties and tenants?

Business rules

So, (1) after we engaged in some deliberations via comments and chat, and (2) once you discussed the possibilities with your customer and colleagues, it has been defined that, in fact:

A Property has zero-one-or-many Owners, each of which must be a User
A User is the Owner of zero-one-or-many Properties
A Property has zero-one-or-many Tenants, each of which must be a User
A User is the Tenant of zero-one-or-many Properties
A Property is uniquely identified by the combination of its Street and its Number
A User is uniquely identified by its EmailAddress

Therefore —in your business context— among other aspects, there are two distinct conceptual-level associations (or relationships) of cardinality many-to-many (M:N) between the entity types Property and User.

Illustrative IDEF1X diagram

Consequently, I have created an IDEF1X1 diagram that consolidates the business rules formulated above, which is shown in Figure 1:

As demonstrated, every entity type —be it associative or independent— is portrayed by means of its individual box.

I depicted (a) ownership and (b) tenancy as two different associative entity types since, although very similar, they represent the prototypes of two distinct things of interest in you business domain, so it is very convenient to manage them separately because, e.g.:

Apart from

solely retaining the fact that a given user is the tenant or owner of a property by way of true or false attributes —named, say, is_owner and is_tenant—,

you might as well be interested in

keeping the exact point in time when all the ownership and tenancy instances where registered in the system, thus establishing a time-related attribute —entitled, say, created_datetime— for said entity types would be very advantageous.

In the future, one of these entity types may entail keeping track of certain attributes that do not apply to the other, hence administering them discretely yields benefits from the beginning, making up a more extendable and versatile conceptual schema.

It is worth to mention that, in the diagram, the user_id attribute migrates from (i) the user entity type to (ii) ownership and (ii) tenancy as owner_id and tenant_id respectively —both quite descriptive role names that you provided in the question—.

Expository logical SQL-DDL design

Then, based on the IDEF1X diagram detailed above, I wrote the expository DDL layout that is declared as follows:

-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE user_profile (
    user_id          INT      NOT NULL,
    first_name       CHAR(30) NOT NULL,
    last_name        CHAR(30) NOT NULL,
    gender_code      CHAR(3)  NOT NULL,
    birth_date       DATE     NOT NULL,
    email_address    CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT user_profile_PK  PRIMARY KEY (user_id),
    CONSTRAINT user_profile_AK1 UNIQUE ( -- Multi-column ALTERNATE KEY.
        first_name,
        last_name,
        gender_code,
        birth_date
    ),
    CONSTRAINT user_profile_AK2 UNIQUE (email_address) -- Single-column ALTERNATE KEY.
);

CREATE TABLE property (
    property_id      INT      NOT NULL,
    street           CHAR(30) NOT NULL,
    number           CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT property_PK PRIMARY KEY (property_id),
    CONSTRAINT property_AK UNIQUE      (street, number)
);

CREATE TABLE ownership (
    property_id      INT      NOT NULL,
    owner_id         INT      NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT property_PK              PRIMARY KEY (property_id, owner_id),
    CONSTRAINT ownership_to_property_FK FOREIGN KEY (property_id)
        REFERENCES property (property_id),
    CONSTRAINT ownership_to_owner_FK    FOREIGN KEY (owner_id)
        REFERENCES user_profile (user_id)
);

CREATE TABLE tenancy (
    property_id      INT      NOT NULL,
    tenant_id        INT      NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT tenancy_PK             PRIMARY KEY (property_id, tenant_id),
    CONSTRAINT tenancy_to_property_FK FOREIGN KEY (property_id)
        REFERENCES property (property_id),
    CONSTRAINT tenancy_to_tenant_FK   FOREIGN KEY (tenant_id)
        REFERENCES user_profile (user_id)
);

Hence, in such logical-level design:

each base table represents an individual entity type (which prevents ambiguities introduced by denoting distinct meanings and intentions by virtue of the same base table);
every column stands for a specific attribute of the respective entity type;
a particular data type is fixed for each column in order to ensure that all the values it contains belong to a single and well defined set, be it INT, DATETIME, CHAR, etc.; and
multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined in the conceptual schema.

All of this in agreement with the stipulations by Dr. Edgar Frank Codd in his relational model (as far as the capabilities of the Structured Query Language permit at the time of writing).

Of course, with this logical layout you can still, e.g., derive in BOOLEAN form whether a given User is the Owner of a particular Property or not, with the aid of a SELECT operation that produces a scalar value that evaluates to either 'True' or 'False', which can be accessed from one or more (present or future) application programs sharing this database.

Covering the initial business rules with the same logical-level arrangement

These aspects are now mostly anecdotical, but some of the initial business rules were:

A Property has zero-or-one Owner, which must be a User
A User is the Owner of zero-one-or-more Properties
A Property has zero-or-one Tenant, which must be a User
A User is the Tenant of zero-one-or-more Properties

And they can be coped with using the same logical design previously discussed declaring the property_id column as an ALTERNATE KEY in both the ownership and the tenancy tables, via two more constraints, i.e.:

ALTER TABLE ownership
    ADD CONSTRAINT UNIQUE ownership_AK (property_id);

ALTER TABLE tenancy
    ADD CONSTRAINT UNIQUE tenancy_AK (property_id);

This shows other facet (that can be of help for a future visitor) of the versatility offered by the arrangement proposed in this answer.

Endnote

1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


does using optimizer_mode=first_rows_n for application that using Oracle database is a best practice?

If you're not ready to make this a system-wide setting, you could try it in an isolated fashion by using a query hint:

SELECT /*+ FIRST_ROWS(10) */
       first_column,
       second_column
  FROM my_table_or_view

This is less risky, since you don't need to consider the effects on the entire system.


How to append zeros to substring Datetime values in sql table?

Do not store dates and times as strings like nvarchar while the database has native datatypes like datetime. These are not saved as strings but in an internal binary format designed for storing this type of information.

Such datatype saves the exact information instead of some of its various representations, allowing you to get it in different formats, making the conversion needed in this question unnecessary. More than that, it allows doing calculations and comparisons using database functions.


Fabio has given a great answer.

However for Redshift, it is worth adding that how your data is physically laid out has a dramatic impact on EXPLAIN plan cost.

Create some dummy data:
(inspired by https://stackoverflow.com/questions/38667215/redshift-how-can-i-generate-a-series-of-numbers-without-creating-a-table-called)

drop table if exists #my_table;
create table #my_table as
select
    (row_number() over (order by 1)) - 1 as user_id
    ,(current_date - user_id::int)::timestamp created_at
from
    stl_load_errors
limit 24

Reproduce similar EXPLAIN plans to the question:

explain
SELECT user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM #my_table) x
WHERE rownum = 1

EXPLAIN:
XN Subquery Scan x  (cost=1000000000000.79..1000000000001.39 rows=1 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=1000000000000.79..1000000000001.09 rows=24 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Sort  (cost=1000000000000.79..1000000000000.85 rows=24 width=16)
              Sort Key: user_id, created_at
              ->  XN Network  (cost=0.00..0.24 rows=24 width=16)
                    Distribute
                    ->  XN Seq Scan on "#my_table"  (cost=0.00..0.24 rows=24 width=16)

Next:

explain
SELECT ac1.user_id, ac1.created_at FROM #my_table ac1
JOIN
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM #my_table
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE

EXPLAIN:
XN Hash Join DS_DIST_INNER  (cost=0.72..822858.77 rows=13 width=16)
  Inner Dist Key: ac1.user_id
  Hash Cond: (("outer".maxdate = "inner".created_at) AND ("outer".user_id = "inner".user_id))
  ->  XN Subquery Scan ac2  (cost=0.36..0.66 rows=24 width=16)
        ->  XN HashAggregate  (cost=0.36..0.42 rows=24 width=16)
              ->  XN Seq Scan on "#my_table"  (cost=0.00..0.24 rows=24 width=16)
  ->  XN Hash  (cost=0.24..0.24 rows=24 width=16)
        ->  XN Seq Scan on "#my_table" ac1  (cost=0.00..0.24 rows=24 width=16)

This is indeed much faster but data is distributed between nodes (DB_DIST_INNER).

Now try:

drop table #my_table_dist;
create table #my_table_dist
distkey(user_id) sortkey(user_id,created_at) as
select
    (row_number() over (order by 1)) - 1 as user_id
    ,(current_date - user_id::int)::timestamp created_at
from
    stl_load_errors
limit 24

Now run EXPLAIN:

explain
SELECT user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM #my_table_dist) x
WHERE rownum = 1

EXPLAIN:
XN Subquery Scan x  (cost=0.00..0.78 rows=1 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=0.00..0.48 rows=24 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Seq Scan on "#my_table_dist"  (cost=0.00..0.24 rows=24 width=16)
 The data is already sorted and distributed so Redshift simply reads off the answer.

Whilst:

explain
SELECT ac1.user_id, ac1.created_at FROM #my_table ac1
JOIN
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM #my_table_dist
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE

EXPLAIN:
XN Hash Join DS_DIST_INNER  (cost=0.36..822858.77 rows=13 width=16)
  Inner Dist Key: ac1.user_id
  Hash Cond: (("outer".maxdate = "inner".created_at) AND ("outer".user_id = "inner".user_id))
  ->  XN Subquery Scan ac2  (cost=0.00..0.66 rows=24 width=16)
        ->  XN GroupAggregate  (cost=0.00..0.42 rows=24 width=16)
              ->  XN Seq Scan on "#my_table_dist"  (cost=0.00..0.24 rows=24 width=16)
  ->  XN Hash  (cost=0.24..0.24 rows=24 width=16)
        ->  XN Seq Scan on "#my_table" ac1  (cost=0.00..0.24 rows=24 width=16)
Note no difference in cost due to distribution of data between nodes (DB_DIST_INNER).


I would suggest not storing images in the database.
Don't store unsalted password in the database. Hint: use pgcrypto
In PostgreSQL, don't use varchar where text would otherwise work, because it's implemented the same way except it doesn't have the check.

Don't use the JSON datatype unless you have good reason. Use jsonb, which you can operate on and index.


If your primary concern is with the graph of user connections, then use a graph database. There are many out there. Some are graph-processing engines are built on top of existing NoSQL or relational products.

These products are specifically optimized for the friend-of-friend type of queries typical for social networks. The schema definition and queries are typically much simpler than the same rules implemented on a relational engine.


SQL Server Developer Edition 2012 or 2014 - Download

Developer Edition is free

As of March 31, 2016 Microsoft SQL Server Developer Edition is now free for SQL Server 2014 and up for Visual Studio Dev essential members (which is free as well).

For all editions, see How to buy SQL Server.

If you want to explore SQL Server, you can download SQL Server Evaluation Edition for 180 days, and then purchase it.

Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation


Oracle Stored Procedures / schema maintenace but still keep service online, how?

Why does the service need to be taken down when you fix a bug?

You can deploy a new PL/SQL package without shutting anything down.  If you are changing the interface between packages or to the middle tier this may be complicated by needing to keep a backwards compatible version of some procedures or functions around.

If someone is running the existing PL/SQL package then your install of a new package will block until the user exits as it needs to obtain a lock at the package level.


What are some important MySQL status variables to keep an eye on?

Here's a link to a bunch of links on "tuning best practices."

The short form is: It depends. What are you tuning for? Tuning read performance is different than write performance and everything changes when you move to a cluster. There is no one set of "magic variables" that can be quietly optimized for every situation.

From these, you can look at monitoring variables, as the variables that need to be tuned are strongly related to the variables that require monitoring.


How to check if sysadmin logins have a master database as a default database in T-SQL?

You can get a list of such logins and then run alter login commands in a loop:

DECLARE @Name SYSNAME
DECLARE @Command NVARCHAR(2000)

DECLARE c CURSOR READ_ONLY
FOR
SELECT p.name
FROM sys.server_principals p
    JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    AND p.name NOT LIKE '##%'
    AND p.default_database_name not in ('master')
    AND s.sysadmin = 1

OPEN c
FETCH NEXT FROM c INTO @Name

WHILE @@FETCH_STATUS = 0 BEGIN

    SET @Command = 'alter login ' + QUOTENAME(@Name) +' with default_database = [master]'
    --print @Command
    EXEC(@Command)
    FETCH NEXT FROM c INTO @Name

END

CLOSE c
DEALLOCATE c


How to reference a table from another schema inside a package?

From section "6 Coding PL/SQL Subprograms and Packages" in the "Oracle Database Advanced Application Developer's Guide, 11g Release 2 (11.2), E41502-05"

  To create without errors (to compile the subprogram or package successfully) requires these additional privileges:
  - The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
  - The owner cannot obtain required privileges through roles.

You must have granted select permission on this table to the schema that owns the package.
It is important that this permissions are granted directly to the package owner and not
indirect using a role. Using a role is sufficient to select the table directly by Toad
but it is not sufficient if you want to select the table in a package.


Get all rows where specific column value occurs more than once (filter out single occurences)

The first approach is to create an index by sessionId. But its datatype is MEDIUMTEXT...
So the approach is: normalize your data, move sessionId values into separate table and refer to it by foreign key. The reference column will be compact (4 or 8 bytes) and indexed, so its usage in the query as GROUP BY expression will improve.
The columns clientID and stage seems to be candidates to such normalization too.


SQL Server 2014 Restore DB only has "Verify Backup Media" option

After lots of searching it seems abit hard to figure out why restoring database only has the "Verify Backup Media" option in my SSMS.
I tried "Restore Files and Filegroups..." but hit another error "The backup set holds a backup of a database other than the existing", until I came to this post, writing the script instead to restore the ".bak" file works for me.


Innobackupex: How to extract individual files from xbstream compressed backups

XtraBackup logs the most recent LSN not only in xtrabackup_info which you'll have to extract, but to STDERR, too:

150731 16:33:28  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150731 16:33:28  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1948062848'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1948062848)

The LSN would be 1948062848. Find string xtrabackup: The latest check point (for incremental): in the output. See http://bazaar.launchpad.net/~twindb-dev/twindb-agent/trunk/view/head:/twindb.py#L1888 for example:

def grep_lsn(output):
    """
    Finds LSN in XtraBackup output
    :param output: string with Xtrabackup output
    :return: LSN
    """
    lsn = None
    for line in output.split("\n"):
        if line.startswith("xtrabackup: The latest check point (for incremental):"):
            lsn = line.split("'")[1]
    return lsn

Then, pass 1948062848 with --incremental-lsn= option when you take an incremental copy. Example: http://bazaar.launchpad.net/~twindb-dev/twindb-agent/trunk/view/head:/twindb.py#L1429

if backup_type == 'incremental':
    last_lsn = job["params"]["lsn"]
    xtrabackup_cmd.append("--incremental")
    xtrabackup_cmd.append(".")
    xtrabackup_cmd.append("--incremental-lsn=%s" % last_lsn)


As far as I understand from the comments, you wanted a correlated subquery, to find the "parent" ticket and from that the realted asset (and its name):

HD_TICKET.TITLE = (SELECT NAME
                   FROM ORG1.ASSET
                   WHERE ORG1.ASSET.ID = (SELECT parent.ASSET_ID
                                          FROM HD_TICKET AS parent
                                          WHERE parent.ID = HD_TICKET.PARENT_ID))

Now, you'd still get a similar error with the above code change, for the same reasons. MySQL does not allow the table that is updated to appear in a subquery in the WHERE clause.

Solution is to move the second reference to the table to the UPDATE clause and trasnform the subqueries into outer joins:

  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID

Since we did that, it feels right to convert the existing join to T5 to the same JOIN syntax as well:

update HD_TICKET as T
  join HD_STATUS as T5
    on T.HD_QUEUE_ID = T5.HD_QUEUE_ID
  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID
set
  T.HD_STATUS_ID = T5.ID,
  T.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
  T.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
  T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
  T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
  T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT),
  T.TITLE = A.NAME
where T5.NAME = 'New'
  and T.ID in (<TICKET_IDS>) ;


Properly escape characters in a string for JSON

There is a system function for this purpose called STRING_ESCAPE that will escape the characters as needed for a given string type.

In your example:

declare @data nvarchar(max) = N'"TEST"';
declare @jsonFragment nvarchar(max);
declare @id int = 999;

set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + STRING_ESCAPE(@data, 'json') + '"';

select @jsonFragment;

Returns:

,"999":"\"TEST\""

It works with all elements that need to be escaped, such as slashes in a pathname, for example:

SELECT STRING_ESCAPE(N'C:\PathName\Temp\', 'json')

Returns

C:\\PathName\\Temp\\


How to force a sysadmin to store data in schema other than dbo?

The only way for a member of the sysadmin fixed server role to use a non-dbo schema is to explicitly qualify their object names with the schema name:

CREATE TABLE MySchema.TableName (...

instead of:

CREATE TABLE TableName (...

There is no way around this. The documentation for ALTER USER (in the "Remarks" section) even has a note for:

  🛈 Important

  The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

This is most likely due to the fact that Logins (instance-level) that are members of the sysadmin fixed server role become the dbo User (database-level) in all databases. To verify this, log in as any non-sa Login that is a sysadmin and execute the following:

SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User];


Execution of sequential delete and uncommitted read

As the SELECT is uncommitted, does it start executing before the DELETE is committed?

No, T-SQL statements always execute sequentially in SQL Server. The point is the SELECT may read uncommitted changes made by other concurrently-executing transactions.

Would this mean that removing NOLOCK should stop the error?

Yes, but only because error 601 is only possible when the transaction isolation level is READ UNCOMMITTED. Moving to a different isolation level prevents that particular error from occurring.
Additional information
Error 601 can occur for a variety of reasons, but all share a common theme: the SQL Server engine was following some pointer chain or other, when it encountered a situation where a structure it was expecting to be present had been moved or deleted by another concurrently-executing process.
The number of cases where error 601 can occur has been progressively reduced over SQL Server releases, with SQL Server 2012 the least likely to return this error, though it is still possible.
My own view is that all error 601 incidents are bugs - for a suitable value of 'bug'. The behaviour is certainly undesirable, and goes well beyond the SQL standard's description of phenomena that may be encountered under the READ UNCOMMITTED isolation level.
While it is certainly true that the SQL standard does not define the detailed behaviours of the different isolation levels very well, what it does say leads to the common belief that the only consequence of READ UNCOMMITTED is that a transaction may see data that has been changed by another transaction before that other transaction commits. As a result of that, using READ UNCOMMITTED isolation level is often justified on the basis that almost all transactions commit very quickly (and very few roll back) so reading 'dirty' data is just a timing difference.
Unfortunately, the implementation of READ UNCOMMITTED in SQL Server goes much further than simple dirty reads. A READ UNCOMMITTED transaction in SQL Server can return duplicated data, a partial read of a large data type, skip data records entirely, or simply fail with an 601 error.
It is possible to experience some of these behaviours under READ COMMITTED and even REPEATABLE READ. This leads some to conclude that the only acceptable isolation levels are those that provide at least statement-level consistency, namely row-versioning READ COMMITTED, SNAPSHOT isolation, or SERIALIZABLE.
Of these, row-versioning READ COMMITTED is usually the easiest to transition to. See this Books Online Topic and its subtree for details of row-versioning isolation levels.


MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT

Assemble desired JSON using string functions and then cast it into JSON.

Example data

create table item (itemName varchar(200), itemProperties json);

insert into item values
('sword', '{"damage": 20, "durability": 300}'),
('magical sword', '{"damage": 30, "magical damage": {"fire": 5},
                    "durability": 400}'),
('dummy', '{}'),
('spellbook', '{"spell": "lightning bolt", "charge": 10}');

Query to merge all itemProperties together

select cast(
  concat('{',  -- wrap everything in root object '{ ... }'
    group_concat(
      -- strip parenthesis from individual item representation
      -- '{"foo": 1}' -> '"foo": 1'
      substring(itemProperties, 2, length(itemProperties) - 2)),
  '}')
as json) allProperties
from item
-- skip empty JSON values to avoid getting extra comma during
-- group_concat
where itemProperties != JSON_OBJECT();

Resulting JSON

{
  "spell": "lightning bolt",
  "charge": 10,
  "damage": 20,
  "durability": 300,
  "magical damage": {
    "fire": 5
  }
}

Several caveats:

Behaviour of this snippet is different from JSON_MERGE(), for example:

When two or more properties have the same name their values are
overwritten instead of being merged into array of values
It can't merge objects with arrays

Solution as presented only works with objects as a top level entity
and not with arrays. It can be modified to work with arrays.
If relies on string representation of JSON objects beginning and
ending with curly brackets {}. This might change in future versions
of the server.


window is a reserved word. You can create a column with that name if you enclose it in double quotes:

CREATE TABLE ATTR
(
  "window" character varying(64) NOT NULL
);

More details about identifiers, quoted identifiers and keywords can be found in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

But I highly recommend you find a different name that does not need to be quoted. Using quoted identifiers is more trouble in the long run than they are worth it.


MySQL exception after upgrading from Java 6 to Java 7

This is a bug in recent versions of the JDK - it fails in jdk1.7.0_25 but I'm using jdk1.7.0_21 and it works fine. Watch this issue for more information


How to add userAdmin user in Mongodb Atlas

You cannot grant this role. The only way to create new MongoDB users in Atlas is either via the UI or the API. See Add MongoDB Users for details and note that this page states:

  Atlas rolls back any user modifications not made through the UI or API. You must use the Atlas UI or API to add, modify, or delete MongoDB users on Atlas clusters.

See Create a Database User for details on how to create MongoDB users via the API.


Check if table exists on a linked server

You can query the Information_Schema views on the linked server:

if exists(
        select  *
        from    [Linked_Server_Name].[Database_Name].INFORMATION_SCHEMA.TABLES
        where   table_name = 'Table_Name'
                and table_schema = 'Table_Schema'
)
print 'Table Found'


Adding data in a bridge table automatically

When contemplating the problem that your are describing, I thought that a VIEW and a TRIGGER won't give you the solution you are after.  (My current view is: a VIEW should be used for viewing or for creating "virtual tables", and TRIGGERs are best avoided ... but that's just me.)  Instead, I have written some example code for a PROCEDURE that could do the job.  I have changed the table names slightly (I have used the prefix "se" for "stackexchange"), but have kept the column names and the constraints:

-- DDL code
CREATE TABLE SECustomer (
  cust_id    number primary key,
  cust_name  VARCHAR2(100),
  cust_phone VARCHAR2(20)
);

CREATE TABLE SEAddress (
  address_id NUMBER PRIMARY KEY,
  address    VARCHAR2(500),
  area       VARCHAR2(100)
);

CREATE TABLE SECustAdd (
  cust_id    NUMBER REFERENCES SECustomer ( cust_id ),
  Address_id NUMBER REFERENCES SEAddress ( address_id ),
  PRIMARY KEY ( cust_id, address_id )
);

For the procedure's logic, I have more or less coded what you have specified in your comment (27 Apr 2017, 1221 hours):

  "The first thing added is the phone no. of the customer. Then we check
  if the phone no. already exists in the customer table we use the id
  corresponding to that phone no. else we create a new id and add
  customer details and address details."

I have sprinkled some comments into the code, so that it is easier to see what's going on.  Also, there are quite a few dbms_output.put_line() statements.  These should be removed if you really use the procedure in a "serious" setting.

create or replace procedure addnewclient (
  phone VARCHAR2
, customername VARCHAR2
, anaddress VARCHAR2
)
as
  phonecount number := 0 ;
  newcustid number := 0 ;    -- only needed if the customerid is NOT generated
  maxcustid number := 0 ;    -- latest/highest customer_id using a particular phone number
  newaddressid number := 0 ; -- only if addressid is NOT generated
  existingaddr number := 0 ;
  errorcode number := 0 ;
  errormsg varchar2(128) := '' ;
begin
  -- does the phone number exist?
  select count(cust_phone) into phonecount from secustomer
  where phone = cust_phone ;

  -- if ids are NOT "generated always as identity"
  select max(cust_id) + 1 into newcustid from secustomer ;

  if phonecount > 0 then        -- phone number exists, use an existing address
     dbms_output.put_line('phone number ' || phone || ' EXISTS in customer table') ;
     -- Use the newest customerid (in customer table)
     select max(cust_id) into maxcustid from secustomer where cust_phone = phone ;
     insert into secustomer (cust_id, cust_phone) values (newcustid, phone);
     dbms_output.put_line('  INSERT (Customer) -> new customer id, existing phone number ') ;

     -- Find the address id for the newest customerid (in intersection)
     select address_id into existingaddr from secustadd where cust_id = maxcustid ;
     insert into secustadd values (newcustid, existingaddr) ;
     dbms_output.put_line('  INSERT (CustAddr) -> new customer id, existing address id') ;

  elsif phonecount = 0 then     -- we need to record a new address
     dbms_output.put_line('phone number ' || phone || ' does NOT exist in customer table') ;
     -- Generate a new id (only needed if customer_id is NOT generated!)
     select max(address_id) + 1 into newaddressid from seaddress ;

     insert into secustomer (cust_id, cust_phone) values (newcustid, phone) ;
     dbms_output.put_line('  INSERT (Customer) -> new customer id, new phone number ') ;
     insert into seaddress (address_id, address) values (newaddressid, anaddress) ;
     dbms_output.put_line('  INSERT (Address) -> new address id, a new address ') ;
     insert into secustadd values (newcustid, newaddressid) ;
     dbms_output.put_line('  INSERT (CustAddr) -> new customer id, new address id ') ;

  end if;
exception
  when others then
    errorcode := SQLCODE;
    errormsg := SUBSTR(SQLERRM, 1, 128);
    dbms_output.put_line('ERROR: ' || errorcode || '  ' || errormsg ) ;
end;

I have assumed that you are NOT using auto-ids (...generated always as identity).  For testing, I suggest that you INSERT just one line of test data into each table - for starters -, and then call the procedure from an anonymous block (several times).

-- insert some test data
begin
  insert into secustomer (cust_id, cust_phone)  values (1, '123456789') ;
  insert into seaddress (address_id) values (50) ;
  insert into secustadd values (1, 50) ;
end;

-- testing:

begin
  -- phone number exists
  addnewclient('123456789', 'first_new', 'newaddress1') ;
end;

begin
  -- phone number does not exist
  addnewclient('333444555', 'second_new', 'newaddress2') ;
end;

I'm sure this will need some more tweaks (exception handling etc), but it should get you started.
Best of luck!


Backup SQL Server database on UNC path using SA

You can map a local drive letter to your UNC folder using xp_cmdshell like:
exec xp_cmdshell 'net use X: \MyServer\MySharedFolder\'

Then you can issue your backup statement to use the X: drive as if it was local.

You may have to run sp_configure to enable xp_cmdshell.


Can I disable execution plan caching for debugging purposes?

According to the docs there are three ways to recompile a stored procedure and/or the queries inside of a stored procedure.

WITH RECOMPILE

This is probably the most direct way to force a recompile, if you are willing to edit the definition of the stored procedure then you can automatically force a recompile every time. There are some significant disadvantages to watch out for though.

CREATE PROCEDURE dbo.FooForProfit @Blood INT, @Sweat BIGINT, @Tears SMALLINT
  WITH RECOMPILE
AS
  -- Do that foo-do that you do so well (for profit)!

Alternatively you can just add the hint after the execute thusly:

EXECUTE dbo.FooForFun @Joy = 42 WITH RECOMPILE;

The problems:

This will hurt performance, and most likely give you tainted performance values.
Adding WITH RECOMPILE after the EXECUTE statement might not be possible depending on how you're executing the stored procedures.
The compiled plan will not be cached, nor will the performance information be maintained1.

  The compiled plan for the stored procedure is not cached. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats.

This will disable the Parameter Embedding Optimization1.

  Parameter Embedding Optimization takes this process a step further: query parameters are replaced with literal constant values during query parsing. The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.

I feel it is important to note that some very smart and experienced people like Aaron Bertrand make it a point not to go down this route. I initially thought this would be your best bet I'm now convinced that this probably isn't the ideal solution.

SP_RECOMPILE

If you can't or don't want to modify the stored procedure and your data access mechanism doesn't let you append the query hint you can manually force a recompile of the stored procedure using another provided stored procedure. One nice up side to this route is you can force a recompile at will so you can easily compare executions with or without the compilation penalty.

EXEC sp_recompile N'dbo.FooForFame';

The problems:

This is going to add a separate step to your process to reset your test bed.
You could probably add this to a trigger linked to executing your stored procedure. <-- Don't do that because you don't want to encourage the maniacs or create more.

RECOMPILE Query Hint

Like it says in the docs, this will give you more fine grained control over what gets recompiled if a stored procedure has multiple queries.

  RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. RECOMPILE is also useful when you create plan guides.

It will also let you recompile statements that aren't within a stored procedure. That is useful for obvious reasons. Here is a sample:

SELECT
  Peace
FROM dbo.TheWorld
OPTION (RECOMPILE);

The problems:

This will be slightly more work if you really do have multiple statements in a stored procedure.


SSIS Job "Argument "ORARA********.xxxxxxx.COM" for option "connection" is not valid. The command line parameters are invalid. The step failed. " error

I fixed this issue by changing the connection name(without space, dot,comma) of both source and destination in Package itself and I tried to run the job manually by clicking Start Job at Step and it worked fine :)


That's not bytes, it's display width.  It's used as a hint for the maximum expected typical character width of values for the column, to applications that care to actually use the metadata -- which many don't.
As explained in the documentation:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The default value for an unsigned tinyint is (3) because that's the size of "255" which the widest number (in displayed character positions) that can be stored in an an unsigned tinyint.
The display width also does not change the storage requirements for the data types; so, for example, an INT(1) still takes up 4 bytes of storage.


Your code seems working well. In software development, "hard code" or "hard coding" can be interpreted as "embedding your external data like SQL statement directly to your source code".

Reference:

Hard coding

Soft coding


Should I use MyISAM over InnoDB to save disk space?

You're worried about 3MB? er- don't.
Even at 10x that, roughly your expected increase, it would be 30M.
You aren't going to get saving of 30M of storage to counter any reliability failure.
Use InnoDB.


You could try using
select * from table(xmlsequence( myfunc() )) . In this case you get xml in columns.

Or try this example to extract fields from your refcursor

select extractvalue(column_value,'/ROW/FIRST_NAME') first_name
     , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(f()));

FIRST_NAME | LAST_NAME
:--------- | :--------
Donald     | Duck
Mickey     | Mouse

dbfiddle here

PS. Note about xml overhead.


Help on deciding how to pick Correct Index

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access.

The commenters are correct - the query as written will always produce a scan. The outer query will always produce a scan since the query has neither a join nor a sargable condition in the WHERE clause to limit it. The inner query is likely using a scan because of the number of times it is executed and the probability of returning more than 30% of the rows over the course of the query. (30% is roughly the threshold for choosing a scan over a seek.) A join may produce a better plan, but it really depends on the distribution of values in the temporary table.

I can think of a couple of things you might try:

If you're running this query repeatedly to test it, make sure you've added OPTION(RECOMPILE) to the end while you're testing. This will force it to re-evaluate the query instead of using a cached plan. If you don't, the optimizer may not see that an option other than a scan is available.
Try using a CROSS APPLY instead of a join. You can use a subquery as the target of the APPLY; I've used that technique before with stubborn queries with good results. Your query would then resemble SELECT DISTINCT D.TypeID FROM dbo.Sizes S CROSS APPLY (SELECT TOP(1) 1 FROM #PermissionsTable PT WHERE PT.ProductID = S.ProductID AND PT.CountryID = S.CountryID) P. Keep in mind that the APPLY will still be run as many times as you have rows in Sizes, so even though you're using a seek, it might still produce a poor plan.

If neither of those has an effect, you may be able to improve performance by changing the way you think about the query. How many distinct ProductID/CountryID combinations are there in #PermissionsTable? Instead of searching on all 400,000 rows, can you select only those distinct combinations into another temp table, then join that to Sizes? Can you select only the distinct combinations of ProductID/CountryID/TypeID from Sizes? Can you reverse the query so that Sizes is in the inner query? The goal of all of these would be to reduce the number of rows that the query must retrieve.


Cannot INSERT into database on a shared server

You have granted usage to user@% but all other things to usr@%.


1 million strings and exact match search

PostgreSQL is perfectly fine for this.
You got a couple of options to make this work.

First of all:
PostgreSQL has a special index type for that called GIN (http://www.cybertec.at/gin-just-an-index-type/). It is perfect for full-text-search in general.

The cool thing is: In the latest version of PostgreSQL there is support for a thing called jsonb. You can put your string into a JSON document and search on EACH field in the JSON in a nice way using GIN and a couple of cool operators (see http://www.postgresql.org/docs/9.4/static/datatype-json.html).
JSONB is really really fast, very powerful and it definitely kills MongoDB on the performance side.
In addition to that a couple million of rows are no real big deal for PostgreSQL anyway.
There is one more cool thing: PostgreSQL can do fuzzy search on strings (through Nearest-Neighbour search).


Some additional points that I have noticed is that in case you are using the backup compression feature, this feature together with TDE does not go that well. We have noticed a very minimal compression rate, almost negligible. Therefore consider this point of backup compression if you are using one.
I am sure you would be aware, but just to add, TDE is available for Enterprise edition, so therefore consider this as well during setting up SQL server for TDE.
TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.
Make sure the encryption keys are stored safely in a secure location that can be accessed in the event of a restore scenario. Familiarize yourself with restoring a database that has been encrypted to a new server. (originally a comment by Jonathan Fite).


Strictly speaking, _id fields are as unique as you make them.  As you have discovered, the default values for _id are ObjectIDs but you can populate the field with any data you wish.

Therefore, for example, you could use a UUID as I have done in this sharding example and then the chances of a collision across collections would essentially be whatever the collision chance is of your generation method and content. A crude version of this _id override can also be seen in this gist (something I used for testing GUIDs some time ago)

In terms of ObjectIDs themselves, the topic has been covered pretty well already (here and here for example) and can be inferred from the documentation you linked.  Basically, there is a chance of collision, particularly for ObjectIDs generated by the same host within the same second at a high volume but for most use cases that is not a concern.

Finally, with respect to any database level guarantee of uniqueness across collections, there essentially isn't one.  Uniqueness is enforced by the index, and there is no option for an index to span multiple collections, hence uniqueness is only guaranteed within a given collection.


A change in the 5.6.20 release notes:

Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.
As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in "Row size too large" errors.

(emphasis mine)
The default for innodb_log_file_size is 50331648, which means the largest BLOB/TEXT value you can create, regardless of data type, is close to 5033164, and you discovered the precise value is 5033932. I suppose internally the calculation involves some fudge factor.
So you need to increase innodb_log_file_size if you want to store larger BLOB/TEXT data. Fortunately, changing the log file size is a lot easier in 5.6 than in earlier versions of InnoDB. Just add a line in your my.cnf with the new value, and restart mysqld.


SQL Server Concurrent Updlock Serializable vs Try Catch

Are both ways full proof, and will stop duplicate inserts?

Method 2 is not safe under concurrency as written. There is no guarantee that the row that caused the insert to fail will continue to exist when the select in the catch clause runs.

In addition, the catch clause could execute for errors other than a duplicate key violation, because the code does not check the error number.

You should also be aware of the potential for a doomed transaction.

Aaron Bertrand wrote about the overhead of try/catch. The overhead is usually higher than checking first.

  What is best coding practice wise, and performs better?

Method 1 is a common pattern, but needs a transaction to be safe. Performance depends on local factors, so you should conduct your own testing. As a side note, you can avoid one query by using the output clause instead:

DECLARE
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

BEGIN TRANSACTION;

    IF NOT EXISTS
    (
        SELECT *
        FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
        WHERE Name = @Name
    )
    BEGIN
        INSERT dbo.Customer(CustomerId, [Name])
        OUTPUT @CustomerId AS CustomerId
        VALUES (@CustomerId, @Name);
    END;
    ELSE
    BEGIN
        SELECT CustomerId FROM dbo.Customer WHERE [Name] = @Name;
    END;

COMMIT TRANSACTION;

As an alternative, you may want to compare the performance of a safe merge solution:

DECLARE
    @CustomerId uniqueidentifier = {guid '16D39773-9CC2-4CCF-A6A8-ACF1465030CC'},
    @Name varchar(255) = 'name';

MERGE dbo.Customer WITH (SERIALIZABLE) AS C
USING (VALUES(@CustomerId, @Name)) AS I (CustomerId, [Name])
    ON I.Name = C.Name
WHEN NOT MATCHED
    THEN INSERT (CustomerId, [Name])
    VALUES (I.CustomerId, I.[Name])
WHEN MATCHED THEN UPDATE
    SET @CustomerId = C.CustomerId,
        @Name = C.[Name]
OUTPUT @CustomerId AS CustomerId;


Mysql 5.1.X How to upload data from dump file without the partitioning commands?

If your target database is an older MySQL version such as MySQL 5.0, then partitioning commands will already be ignored since they are wrapped in comments like this:

/*!50500 PARTITION BY RANGE  COLUMNS(column)
(PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB,
 PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB) */;

If you are loading into MySQL 5.1 or higher, then one trick that came to my mind is to change the version number in the comment section. You do not have to manually edit the dump file, you can process the file through sed. For example:

sed 's\!50500 PARTITION\!60000 PARTITION\g' dumpfile.sql | mysql


PostgreSQL how to return data from anonymous code block?

With Postgres you could do that in a single data modifying CTE without the need for an IF statement:

WITH data AS (
    SELECT          tbl.id, tbl."someLongURI"
    FROM            mytable tbl
    WHERE           tbl."userId" = 123;
), changed AS (
    UPDATE          mytable tbl
    SET             tbl.status = 'IN_WORK',
                    tbl."userId" = 123
    WHERE           tbl."someLongURI" IN (
        SELECT      "someLongURI"
        FROM        tbl.mytable
        WHERE       tbl.status = 'UNUSED'
            AND     pg_try_advisory_xact_lock(id)
        ORDER BY    id ASC
        LIMIT       1
        FOR UPDATE
    )
    AND NOT EXISTS (SELECT * FROM data)
    RETURNING   tbl.id, tbl."someLongURI"
)
SELECT *
FROM changed
UNION ALL
SELECT *
FROM data
WHERE NOT EXISTS (SELECT * FROM changed);


Relational Design - One table, two foreign keys or Two tables, one foreign key each

Yes, there is an option D that would involve a supertype table for the subtype tables Cases and LocationInventories. You can search for the "supertype/subtype" pattern and "shared primary key" for additional info.

Option D is moved below and I'll add the other options for comparison:

Option A

Cases
    CaseID   PK
    ... more columns

LocationInventories
    LocationInventoryID   PK
    ... more columns

InventoryNeeds
    ?        PK
    CaseID   FK -> Cases (CaseID)
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity
        CK: CHECK ( CaseId IS NULL AND LocationInventoryID IS NOT NULL
                 OR CaseId IS NOT NULL AND LocationInventoryID IS NULL )

Option B

Cases
    CaseID   PK
    ... more columns

LocationInventories
    LocationInventoryID   PK
    ... more columns

InventoryNeedsCases
    ?        PK
    CaseID   FK -> Cases (CaseID)
    NeededQuantity

InventoryNeedsLocations
    ?        PK
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity

Option C

Without foreign keys, you lose referential integrity. I see no reason to follow this option.

Cases
    CaseID   PK
    ... more columns

LocationInventories
    LocationInventoryID   PK
    ... more columns

InventoryNeeds
    ?        PK
    NeedType CK: CHECK ( NeedType IN ('C', 'L') )
    NeedID   FK
    NeededQuantity

Option D

With the addition of this one table, the constraints are quite simplified. The only thing that gets complicated is the insertion (and deletion) of rows to the two tables (Cases and LocationInventories) that will involve an additional INSERT into (or DELETE from) the Bases Needs table.

Needs
    NeedID   PK

Cases
    CaseID   PK  FK -> Needs (NeedID)
    ... more columns

LocationInventories
    LocationInventoryID   PK  FK -> Needs (NeedID)
    ... more columns

InventoryNeeds
    ?        PK
    NeedID   FK -> Needs (NeedID)
    NeededQuantity

Option E

This is a modified D option that combines the NeedType of C option, without losing any referential integrity. It also avoids the (accidental or not) pitfall of option D, that allows a Need to be inserted in both Case and LocationInventory.

Needs
    NeedID   PK  UQ1
    NeedType     UQ1  CK: CHECK ( NeedType IN ('C', 'L') )

Cases
    CaseID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType PK  FK1
    ... more columns
        CK: CHECK ( NeedType = 'C' )

LocationInventories
    LocationInventoryID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType              PK  FK1
    ... more columns
        CK: CHECK ( NeedType = 'L' )

InventoryNeeds
    ?        PK
    NeedID   FK1 -> Needs (NeedID, NeedType)
    NeedType FK1
    NeededQuantity


Lost entire MySQL Server. Unsure if "master-slave" or "master-master" - How to tell?

For M-M, both need to be masters and both need to be slaves.  Those need

log_slave_updates
log_bin

These should show something, even if not actively replicating at the moment:

SHOW SLAVE STATUS;
SHOW MASTER STATUS;

If you can't run things on the broken machine, then I suggest you do

Clone the good machine's disk to the bad
Change my.cnf to have a different server_id
Turn on the two flags above
CHANGE MASTER ... on both -- to establish being a Slave, and provide the pwd for the replication 'user'.

Now you will have Master-Master.  (There is not a lot of differences between M-S and M-M, so you may as well go for M-M.)


Avoiding code duplication for counting and selecting the same resultset

I was checking ways to make this work and it appears that an inline table valued function is a decent option (even though I'd prefer something that didn't add extra stuff to the database).

CREATE FUNCTION [dbo].[myFunction]
(
    @filter1 int,
    @filter2 int
)
RETURNS TABLE
AS
RETURN
(
    SELECT (alias1.field1 as Key, alias2.field2, alias3.field3)
    FROM <Complex joins + where @filter1/@filter2>
)

By extracting the main complexity into the function, I can now reuse it for both queries easily enough:

CREATE PROCEDURE [dbo].[myProcedure]
    @includeCount bit,
    --... where parameters
    @skip int,
    @take int,
    @totalCount int OUTPUT
AS
BEGIN
    IF (@includeCount = 1)
        SELECT @totalCount = COUNT(*) FROM [dbo].[myFunction](where parameters)

    SELECT *
    FROM [dbo].[myFunction](where parameters)
    ORDER BY [Key]
    OFFSET @skip ROWS
    FETCH NEXT @take ROWS ONLY
END
GO

There is still some form of duplication due to the parameter passing, but I guess that's unavoidable as there is no such thing as a closure in SQL.

The ORDER BY needing to be outside the function is also not very intuitive, but SQL doesn't allow me to specify it by itself in the procedure without carrying over the paging statements.

This works for me so I'm marking my own answer as the answer for now. Obviously open to better suggestions if there are any.


As per my original question, my solution was as follows:

My resolution

I deleted the mongod.lock file in each member directory, boot up the members, and a connection was established and a vote took place.

Thank you to Dmytro Shevchenko for assisting with clarification via his link, explaining that deleting the mongod.lock files are OK.


Connecting to MySQL 5.1 using IBM Data Studio

Here is a similar processes to connect to a local derby database

Confirm that your Debrby server is running

I used the startNetworkServer.bat file on Windows 7 to start derby

Open IBM Data Studio
Navigate to Windows>Prefernces>DataManagement>Connectivity>Driver Definitions

Edit the Specific Driver you will use to connect

In my case that was Derby10.2-DerbyClient JBDC Driver Default

On the Jar List tab you will see the default driver files

In my case I updated to c:\db-derby\lib\derbyclient.jar  since my derby is installed in c:\db-derby

I had to save this new location and restart IBM Sata Studio

When I selected this connection parameter in the JDBC driver pulldown I was able to define a successful connection

Hope this helps


You need to ask the database from another angle. Please run this:

SHOW CREATE DATABASE mydb;

This will tell you what it sees. If you want to change the Character set and.or collation, do this:

ALTER DATABASE mydb ... ;

In mysql, ALTER DATABASE can change character set and/or collation. I have mentioned this in my past posts in the DBA StackExchange :

Dec 04, 2012 : Why default character_set_server is latin1?
Sep 18, 2012 : change encoding of database from latin1_swedish_ci to utf8
Sep 26, 2011 : How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?
Sep 19, 2011 : How to convert mysql database charset to utf-8


PostgreSQL CREATE TYPE using multiple relations?

The old version with support is 9.4 today and i can achieve your case, and then in the recent version too.

for example:

create table a ( i int, j int);
create table b ( k text, l text);
CREATE TYPE compfoo AS (f1 a, f2 b);
create table c (m compfoo );
insert into c values ( row(row(1,2),row('hello','world'))   );
select * from c;

result:
("(1,2)","(hello,world)")

you can check this test in
https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=4499c5d52e876979d81fb3f2537c2439

check your version


What's the equivalent of --safe-updates of mysql in MS SQL Server?

There may or may not be such a thing in MS SQL, but why would you want this?

There is already a way to limit what UPDATE affects (the WHERE clause).
There is already a mechanism to safeguard your data when making changes (Transactions).

Combining the two, we get the general-case solution: "Do your updates in a transaction, with appropriate WHERE clauses to limit what they touch, and make sure the results look right before you commit":

> BEGIN;
> UPDATE mytable SET foostring='NewValue' WHERE id > 16 AND id < 32;
15 Rows Updated

> SELECT * FROM mytable;

  [Omitted -- Make sure it looks right]

> COMMIT;  --- Or ROLLBACK if the SELECT doesn't look right


Moving Tables Indexes to Separate file / file-groups, What Happens?

Your example should indeed work but it's not the recommended approach. By doing a drop/create on the CL followed by a drop/create in the NC, you're rebuilding the NC indexes 3 times.

Drop CL = table converts from cluster to heap, which requires NC rebuild
Create CL = table rebuilds from heap to cluster, which again rebuilds NC
Drop/Create NC = rebuilds them again

Despite your approach not being the fastest way to do this, it should work. Can't explain what's going wrong but can suggest a different method. Can you try the following:

Drop NC indexes
Run the following to drop the PK/CL and rebuild on the new FG

ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2]
GO

CREATE UNIQUE CLUSTERED INDEX CLIX_matter_sysid ON [lntmuser].[matter] (sysid ASC)
-- Add appropriate fill factor option etc
ON [Alternate]
GO

ALTER TABLE [lntmuser].[matter] ADD CONSTRAINT PK_Matter PRIMARY KEY (sysid)
GO
Create the NC indexes

If that works, we can go back to working out why your first script failed.

Edit: Just in case... you mentioned checking reads for the new filegroup. What are you using to check this? Are you looking at sys.dm_io_virtual_file_stats and seeing no change as the tables are fully cached in the buffer pool?


SELECT permission on view querying table from another database

Sounds to me like you have a case of ownership chaining. The link should provide you with the details on how to make sure the chain stays intact.


how to open a corrupted progress v9.1e database

Assuming your tag is right and this is a Progress, not a Postgres (as in the text), database you can try forcing your way in. However, backup beforehand! A forced database will be marked as forced and might never work again.

That aside, force might be a lifeline and the way to enter the database to dump it's content.

Here's a longer article about the force flag (-F).


MySQL Simple pivot table with MAX(), GROUP BY and only_full_group_by

If you want ties, i.e. when an employee has two jobs on the same day, that happen to be the latest one display both, you can join a derived table with query doing the aggregation.

SELECT ej1.*
       FROM employee_job ej1
            INNER JOIN (SELECT ej2.employee_id,
                               max(ej2.created_at) created_at
                               FROM employee_job ej2
                               GROUP BY ej2.employee_id) ej2
                       ON ej2.employee_id = ej1.employee_id
                          AND ej2.created_at = ej1.created_at;

If you want to eliminate ties and say you want the one with the largest ID in such a case, add another level.

SELECT ej1.*
       FROM employee_job ej1
            INNER JOIN (SELECT max(ej2.id) id
                               FROM employee_job ej2
                                    INNER JOIN (SELECT ej3.employee_id,
                                                       max(ej3.created_at) created_at
                                                       FROM employee_job ej3
                                                       GROUP BY ej3.employee_id) ej3
                                               ON ej3.employee_id = ej2.employee_id
                                                  AND ej3.created_at = ej2.created_at
                               GROUP BY ej2.employee_id,
                                        ej2.created_at) ej2
                       ON ej2.id = ej1.id;


Poor performance after changing SQL Server 2008 R2 database

So the major differences I see on first glance is that the table scan on ARMiscIncome feeds a merge join in the slow version, which requires a sort:

But in the fast version it feeds a hash join (so no sort is required):

This sort is the highest overall cost in the slow plan, so I might suggest either finagling a hash join there (this could be simply due to different row counts in the two systems, even if statistics are out of date), or adding an index, such as this one which satisfies the output:

CREATE INDEX SortHelper ON dbo.ARMiscIncome(GuestID)
  INCLUDE (Amount, ChargeCode, DateEntered, Description, TranDate);

(Or changing the clustered index on that table.)

Usual caveats apply, e.g. adding/changing an index may help this specific query, but may not help others, and/or may cause undue stress on DML operations. But certainly worth thoroughly testing IMHO.

As an aside, I was able to find the most expensive operator by comparing the plans in SentryOne Plan Explorer. This is a free tool that makes exactly this type of investigation a lot more painless than it is in Management Studio.


Partition Switch - What is the benefit if staging has to be same as destination table

Speed is the name of the game here. You can load large amounts of data into a staging table then switch it in, this reduces the amount of contention for locks/resource on the primary table (though note when you do switch back in, though very fast it does require a lock on the main table). It allows you to manipulate and sanitize your data whilst in staging so you're not hitting the main table unnecessarily post import. The initial load from source can be quicker too, so if that is a bottle neck for you, it could be a benefit.

Just as a rough example of time I used to work with a large database where flat files were loaded into a DB daily, about a million + rows in a load. Inserting directly into the table in batches took around 4 hours. With a table partitioning strategy loading to stating and switching in took 1 hr 30 mins. And only < 1 min of that was the switch that hit the primary table.

Every situation is different, experiment and play around, see if its worth it, even if its not its a good bit of experience.

  Or is it that loading data into a huge partitioned clustered indexed table will take more time versus loading into exactly same empty un-partitioned clustered indexed table?

Yes and no, its to do with the indexes and the amount of data already in the table if your indexes are not partition aligned (partitioned indexes) they should be to get full benefit of partitions anyway, without them inserts would take a massive hit, also data being accessed from the primary table, if the queries do not eliminate partitions that's going to cause issue while inserting too. another benefit of using staging is if the load fails you just have to drop the staging table, not clean up the primary.


Subquery for column names of tablefunc crosstab queries

No, you can't make your resultset's schema dependent on the values inside the table. But you can use dynamic sql to generate a query.

SELECT FORMAT($$

  SELECT * FROM crosstab
  (
    'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
    'SELECT DISTINCT month FROM mytable ORDER BY 1'
  )
  AS
  (
    employee_name text,
    %s
  )
$$,
  'something here...'
);

Now you just want to fill out that 'something here..'

SELECT FORMAT(
    $$
        SELECT * FROM crosstab
        (
            'SELECT employee_name , month , allocation
             FROM mytable
            ORDER BY 1, 2',
            'SELECT DISTINCT month FROM mytable ORDER BY 1'
        )
        AS
        (
            employee_name text,
            %s
        )
    $$,
    string_agg(
        FORMAT('%I %s', month, 'double precision'), ', '
        ORDER BY month
    )
)
FROM (
    SELECT DISTINCT month
    FROM mytable
)
    AS t;

Then run the query that produces, and you get..

 employee_name | 2017-11-30 | 2017-12-31
---------------+------------+------------
 A             |        0.5 |
 B             |        0.8 |        0.5
(2 rows)


Index for finding combinations of attributes in JSON array

Why?
The expressed requirement that artist and title must match in the same element of the JSON array is not reflected in your query, which finds all rows where at least one element matches the artist and another (possibly a different one) matches the title.
The example data for your first case was inconclusive, since the query cannot fail this way for a single array element. Your second example demonstrates the case well, though.
Solution in Postgres 9.3
There are various ways to fix this. One way would be to translate each json array element to an SQL array of composite type consisting of artist and title and match the whole type as one.
Another way would be to keep indexes like you have now (or even a single composite spanning both expressions: (json2arr(data, 'artist'), json2arr(data, 'title')). Your current query identifies possible matches. Unnest the json array data for all identified candidates and check whether both artist and title match on the same element. May or may not be efficient enough.
Better use jsonb in n Postgres 9.4
I am not going into detail for json / pg 9.3, because the release of jsonb in Postgres 9.4 with advanced indexing capabilities mostly obsoleted the problem. This can be implemented much simpler and more efficient with a native GIN index on a jsonb column. There are various options. To optimize for the presented case:
CREATE TABLE tracks (id serial, data jsonb);
INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

Index:
CREATE INDEX tracks_data_gin_idx ON tracks USING gin (data jsonb_path_ops);

Query:
SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

Sequence of attributes in the JSON value and insignificant whitespace don't matter for jsonb. I added detailed information for jsonb to the referenced answer on SO:

Index for finding an element in a JSON array