The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

Your particular example uses and inner join. The results will always be the same whether you use ON or WHERE.

I think of the ON clause as applying the predicate to the row from the right-hand table before it is combined with the left-hand row. The WHERE applies after the combination is formed. This is just how I think inside my head. The query optimizer does not work this way. I'll use an outer join to demonstrate what I'm saying.

Table1       Table2
------       ------
A            A
B

Query 1:
select * from t1
left outer join t2
  on t2.col = t1.col
where t2.col is null;

Query 2:
select * from t1
left outer join t2
  on t2.col = t1.col
  and t2.col is null;

Let's step through Q1. First A is read from t1. Then the ON clause says "find matching rows from t2". This returns A from t2 and we have an output row (A, A). Then we read B from t1. Again the ON says "find matching rows from t2". This time there is no match. Since it's an outer join we return (B, NULL). Now we've read all the rows and we apply the WHERE clause. Only one output row has t2 as NULL so query 1 returns

(B, NULL)

Repeat with Q2. We read t1 and return A. We read t2 but now the ON says t2's value must by both A and also NULL. No row matches. It's an outer join so we return (A, NULL). We read the next row from t1 getting B. We look in t2 for "B and NULL" and do not get a match. We return (B, NULL). There is no where clause so we're done. Query 2 returns

(A, NULL)
(B, NULL)

Different results depending on whether the predicate is in the ON clause or the WHERE clause, but only with outer joins.


Turned out that the query I was using to calculate space combines indexes and data sizes into one. I used the sp_spaceused procedure as well as the below query to track down the the vast size discrepancy to two very large indexes on the current state table that I think were created by the tuning adviser. Those two indexes accounted for 60Mb!!, twice the size of the actual data, lol.

Without those huge indexes current state performs horribly while on the other hand future state doesn't require any additional indexes to perform as fast if not faster. Future state only added about 1.7Mb increase in storage needs which is far below the current states requirements.

query to find index sizes:

SELECT i.[name] AS IndexName
      ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
GROUP BY i.[name]
ORDER BY i.[name]
GO

result shows two huge indexes:

sp_spaceused 'Post_Old' result confirms that the those two indexes account for the discrepancy:


Order of source rows irrelevant, source is array column

Your updated requirements are much simpler for two reasons:

We don't need to establish any order among source rows.
And the array already establishes order among array elements.

Simple table:

CREATE TABLE vals (j int[]);

Simple query:

SELECT ARRAY(SELECT elem FROM vals, unnest(j) elem) AS arr;

Order of source rows relevant, source are integer columns

Not as simple. Based on this table:

CREATE TABLE vals (i int, j int);

There are various ways to preserve order. I think this should be among the fastest - with an ARRAY constructor:

SELECT ARRAY(
   SELECT t.elem
   FROM  (SELECT i, j FROM vals ORDER BY i, j) v
        , LATERAL (VALUES (v.i), (v.j)) t(elem)
   ) AS arr;

My first draft had ORDER BY i, j after the LATERAL join, which would not guarantee the order of columns. I pulled the ORDER BY into a subselect to produce the correct order now. This works, but it's not documented. To be absolutely sure, you have to add an explicit ORDER BY later:

SELECT ARRAY (
   SELECT elem
   FROM   vals v, LATERAL (VALUES (1, v.i), (2, v.j)) t(ordr, elem)
   ORDER  BY v.i, v.j, ordr
   ) AS arr;

About guaranteed order:

PostgreSQL unnest() with element number

About the VALUES expression in a LATERAL join:

SELECT DISTINCT on multiple columns

Another way with - this time with array_agg():

SELECT array_agg(elem) AS arr
FROM  (
   SELECT i, j, 1 AS ordr, i AS elem FROM vals
   UNION ALL
   SELECT i, j, 2 AS ordr, j AS elem FROM vals
   ORDER BY i, j, ordr
   ) sub;

The ARRAY constructor is faster, array_agg() is easier to integrate with multiple result columns.

SQL Fiddle.

The query in your test case would actually work in Postgres 9.5 (currently beta, to be released in the course of 2015). Quoting the release notes for pg 9.5:

  Allow array_agg() and ARRAY() to take arrays as inputs (Ali Akbar, Tom
  Lane)

However, you get a 2-dimenstional array instead, not your desired result.  You can easily implement the same (but slower) aggregate function in older versions:

Is there something like a zip() function in PostgreSQL that combines two arrays?


Why doesn't a delete increase unused space in a table?

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.


Issue with forming correct relationship between subtype and another table in ERD

I actually think your diagram is good for your rule. The only thing is that with the relationship line connecting "Cash Accountability" and "Cashier", I would remove the circle on the Cashier side, to indicate that a "Cash Accountability" has to have exactly one Cashier.

  So as it is now, the relationship states that a cashier cashes out many times in a day and I want to express that a cashier only cashes out once per day.

The relationship is simply saying that a cashier can cash out more than once (not necessarily more than once per day), which is technically correct.

You could add a unique constraint to the implementation of your data model between "Cash Accountability"'s EmpID and Date fields in order to enforce that a Cashier cannot cash out more than once a day. Here's how this would be done in MySQL: https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql

I'm not sure if there's a "correct" way to display this unique constraint in an ER Diagram, but I suppose you could just add a comment.


Communication between DB servers outside Active Directory

Because the machines are on different domains, you cannot use Windows/Integrated authentication. For mirroring, you can use certificates instead. The place to start is here:

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/setting-up-database-mirroring-sql-server.

In that article, there is a specific section that links to the details of setting up certificates for outbound and inbound traffic.

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/use-certificates-for-a-database-mirroring-endpoint-transact-sql.


Clustered index always better than Non-Clustered index?

I just want to put in a word of warning: please very carefully pick your clustered index!

It's the most replicated data structure in your SQL Server database (assuming it's SQL Server you're talking about). The clustering key will be part of each and every non-clustered index on your table, too - certainly in the leaf level, possibly also in the index navigation structure.

You should use extreme care when picking a clustering key - it should be:

narrow (4 bytes ideal)
unique (it's the "row pointer" after all - if you don't make it unique, SQL Server will - for you - in the background - costs your a couple of bytes for each entry - times the number of rows and the number of nonclustered indices you have - can be very costly!)
static (never change - if possible)
ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)
it should be non-nullable and ideally also fixed with - a varchar(250) makes a very poor clustering key

Anything else should really be second and third level of importance behind these points ....

See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!

GUIDs as PRIMARY KEYs and/or the clustering key
The Clustered Index Debate Continues...
Ever-increasing clustering key - the Clustered Index Debate..........again!


Compare words in a string without considering their positions?

I suggest a single SELECT in a plain SQL function:

CREATE OR REPLACE FUNCTION strings_equivalent(a text, b text)
  RETURNS bool AS
$func$
   SELECT a1 = b1
   FROM  (
      SELECT string_agg(w, ' ') AS a1
      FROM  (
         SELECT w
         FROM   unnest(string_to_array(a, ' ')) w
         ORDER  BY w
         ) a1
      ) a2
   , (
      SELECT string_agg(w, ' ') AS b1
      FROM  (
         SELECT w
         FROM   unnest(string_to_array(b, ' ')) w
         ORDER  BY w
         ) b1
      ) b2
   WHERE  length(a) = length(b)
   UNION ALL
   SELECT FALSE
   LIMIT 1;  -- for clarity, not needed
$func$ LANGUAGE sql IMMUTABLE;

Call:

SELECT strings_equivalent('this is a test number 01', 'number this is 01 a test');

This is assuming:

Duplicate words count like any other words
Separator is a single space
No leading or trailing spaces

The function returns NULL for any empty string or NULL input.

The UNION ALL construct is a shortcut to return FALSE immediately if the input strings don't have the same length and avoid more expensive processing. Related:

Return a value if no record is found
SQL order by query results in any arbitrary (but reproducible) manner

LIMIT 1 is not needed because the function only returns the first column of the first row anyway and ignores the rest if there are more rows.

IMMUTABLE (since the result never changes for the same input) helps performance with repeated evaluation and allows indexes on functional expressions.

You could use regexp_split_to_table(a, ' ') instead of unnest(string_to_array(a, ' ')), but regular expression functions are typically more expensive. (You can cover more sophisticated separator characters with the regex, though, like '\s+' for any white space). Related:

How to preserve the original order of elements in an unnested array?

BTW, your simple function sort_text() looks good. But use string_agg() instead of array_to_string(ARRAY(...)) in a simple SQL function. No variable assignment needed:

CREATE OR REPLACE FUNCTION sort_text(a text)
  RETURNS text AS
$$
   SELECT string_agg(w, ' ')
   FROM  (
      SELECT w
      FROM   unnest(string_to_array(a, ' ')) w
      ORDER  BY 1
      ) sub
$$ LANGUAGE sql IMMUTABLE;


So, let's break this down into steps.

First, you need the earliest and latest dateJoined from teamMembership for each team. this can be done with a slight variation on what you already have:

SELECT tt.teamId, tt.name, MIN(tmbr.dateJoined) as dateJoined
  FROM teachingTeam tt
         INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
 GROUP BY tt.name
UNION
SELECT tt.teamId, tt.name, MAX(tmbr.dateJoined) as dateJoined
  FROM teachingTeam tt
         INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
 GROUP BY tt.name
;

GROUP BY says to give us the MIN (or MAX) dateJoined for all the rows for each specific team.

UNION says to take the results of the two SELECT queries, and treat them as a single result set. It also gets rid of duplicates, so if (for instance) all the members of one team have the same dateJoined, we'll just list them once. If you really want to have the earliest and the latest dates when they are the same, use UNION ALL instead.

Now, you want to list the instructors who joined each team on the dates indicated. The simplest way to do that is to treat our first result set as if it were a table, and then to join that data to our teamMembership and instructor tables. This is why we included teachingTeam.teamId in our result set above; to facilitate that join.

SELECT djt.name, mbr.dateJoined, i.firstname, i.lastname
  FROM (SELECT tt.teamId, tt.name, MIN(tmbr.dateJoined) as dateJoined
          FROM teachingTeam tt
                 INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
         GROUP BY tt.name
        UNION
        SELECT tt.teamId, tt.name, MAX(tmbr.dateJoined) as dateJoined
          FROM teachingTeam tt
                 INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
         GROUP BY tt.name
       ) djt  -- for Date Joined Team
         INNER JOIN teamMembership mbr ON (    djt.teamId = mbr.team
                                           AND djt.dateJoined = mbr.dateJoined)
           INNER JOIN instructor i ON (mbr.member = i.id)
 ORDER BY djt.name, mbr.dateJoined
;

Note - all code is untested.

So, we use the teamId and dateJoined from our original query to find the team members we want, and then use member to connect to the instructor table to pick up their first and last names.


SQL Server Transaction Replication on different versions of Windows

You can use SQL Server replication on different Windows versions.  What is more important is the version of SQL Server.

https://blogs.msdn.microsoft.com/repltalk/2016/08/12/suppported-sql-server-versions-in-replication-topology/


Slow inserts in oracle 12c inmemory table

The DML in Inmemory involves new features like Journal & Inmemory Transaction Manager. When there is an insert operation on a table enabled for Inmemory, these new features get updated to bring the consistency in Transaction. Sometimes, there are overheads involved in keeping Inmemory Column Unit (IMCU) transactionally consistent.

Following are some of the factors which affect the performance:

The rate of change
The inmemory compression level chosen for a table
The location of the changed rows
The type of operations being performed

For example:  Tables with higher compression levels will incur more overhead than tables with lower compression levels.

Following are few other deciding factors which can be fine-tuned:

Inmemory Coordinator (IMCO) Process kicks-in every two minutes to
load the data into inmemory
The number of IMCUs repopulated via trickle repopulate in a given 2 minute window is limited by the new initialization parameter INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT
The overhead to update the indexes during DML
DISTRIBUTE or DUPLICATE clause used for the table while using in RAC environment

So, all these combined decide the runtime for an insert. Also, do look at the bundle path updates which are targeted for OLTP & DW performance improvements.

To improve performance in your case you can try changing the compression level used for the table to memcompress for dml:

alter table test_table inmemory priority critical memcompress for dml duplicate all

Regarding the methods you had tried:

Stop one instance on the RAC (2 node RAC)

Stopping one instance doesn't affect the performance as RAC nodes are mainly used for Inmemory Fault Tolerance through the DUPLICATE/DISTRIBUTE clause used for the inmeomory table. So, it won't help improve the insert performance.

Change the inmemory priority to "high" then "low".

Changing the priority clause is mainly used while populating/repopulating the table into inmemory. When there are many tables enabled for inmemory, the IMCO picks up the tables based on the priority setting (CRITICAL, HIGH, MEDIUM, LOW & NONE) and loads them into inmemory area. So, keeping the table priority to high or more has an impact when the expectation is to immediately see the updated version of table after an insert. For this, the IMCO duration needs to be fine-tuned.


How can I *actually* defragment my InnoDB tables?

You can either recreate your table or use "optimize table" command to rebuild your table.  Highly recommend making a backup just in case.

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-storage-layout.html
https://mariadb.com/kb/en/mariadb/optimize-table/


What is the purpose of session pool_mode in pgbouncer?

There would be 1 connection to the server for each connection to pgbouncer, but not necessarily a new connection. When a connection to pgbouncer is closed it doesn't close the connection to the server, just makes that connection available to the pool. Then when a new connection comes in, that pooled connection can be reused.

If the connections to pgbouncer are generally long-lived ones, this might not be of much benefit. But, if there are a lot of short connections, it could result in a major improvement.

There was an excellect blog post published recently about pgbouncer, and the different modes of operation.


How to find DELETE and UPDATE statements that have been executed on a database?

Run this in the context of the master database as you must be running it under the context of a user database set to SQL Server 2000 compatibility level.

The following should work fine

USE master;

SELECT deqs.last_execution_time AS [Time],
       dest.TEXT                AS [Query]
FROM   sys.dm_exec_query_stats AS deqs
       CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE  dest.text LIKE '%Delete%from%'
ORDER  BY deqs.last_execution_time DESC


Trigger: Only raise error if related record was NOT created between now and last Sunday

If I understood you correctly, then see if this helps:

select created_date from that table
check if it is lower than previous Sunday's 23:59

why? Because I presume that it can't be created in the future

How to find that moment in time? One option is to use NEXT_DAY function which returns the "next" Sunday, but - if you subtract 7 (days) from it, you ge previous Sunday which is what you want. Then add 23:59 to the truncated date value and that's it.
Words into code:
SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi, day') right_now,
  2    trunc(next_day(sysdate, 'sunday') - 7) + 23/24 + 59/(24*60) previous_sunday_2359
  3  from dual;

RIGHT_NOW                                              PREVIOUS_SUNDAY_235
------------------------------------------------------ -------------------
29.08.2020 22:39, saturday                             23.08.2020 23:59:00

SQL>

Put into the trigger:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE
    v_upd_row  NUMBER;
    v_cre_date date;
BEGIN
    SELECT COUNT(1) INTO v_upd_row
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
      AND sde_state_id = :new.deleted_at;

    SELECT MIN(a.created_date) INTO v_cre_date
    FROM a_test_base_table a
    WHERE a.objectid = :new.sde_deletes_row_id;

    IF v_upd_row = 0 AND
       v_cre_date < trunc(next_day(sysdate, 'sunday') - 7) + 23/24 + 59/(24*60)
    THEN
       raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END;


Retrieving auto-generated keys for an INSERT statement

There are several ways of doing this in Db2 for LUW.

Firstly, there is a function IDENTITY_VAL_LOCAL() that returns one most recently assigned identity value; it can be used when inserting single records:

insert into parent_tab (some_column) values ('foobar');
insert into child_tab (parent_id, some_other_col)
  values (identity_val_local(), 42);

assuming parent_tab in the example has an identity column id.

Another option is to use a data change table:

with parent_res (parent_id) as (
  select id from final table (
    insert into parent_tab (some_column) values ('foobar')
  )
)
insert into child_tab (parent_id, some_other_col)
select parent_id, 42 from parent_res;

The data change table will contain as many rows as are affected by the corresponding DML statement.

In Postgres a similar result can be achieved by using the RETURNING clause of the INSERT statement:

with parent_res (parent_id) as (
  insert into parent_tab (some_col) values ('foo') returning id
)
insert into child_tab (parent_id, some_other_col)
select parent_id, 42 from parent_res

Since the Postgres IDENTITY (formerly known as SERIAL) columns are backed by SQL sequences, you can also use the LASTVAL() function to retrieve the most recently assigned identity (sequence) value in the session.


Finding Deleted / Updated record statement from SQL Server transaction Log backup file

Method 1: If you want to find it from the transaction log backups you can use read the log viafn_dump_dblogto get the explicit information. On how to read and use this please refer Using fn_dblog, fn_dump_dblog

Note:

  functn uses this extensively that every time fn_dump_dblog is called,
  it creates a new hidden SQLOS scheduler and up to three threads, which
  will not go away (and will not be reused) until a server restart.

Method 2: Using the default trace which is enabled by default in SQL server and it can be used to find various such information on altered objects etc. as explained in this article The default trace in SQL Server - the power of performance and security auditing

Method 3: You can use third party tool like ApexSQL Log(You can give it a try with Trial version , else for full verison you can go for purchase) which is a SQL transaction log reader for forensic auditing and rollback of malicious or inadvertent changes


There is no difference between the two, you get a date field in both instances


When should I use a unique constraint instead of a unique index?

Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.

So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear - this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don't think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.

Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint, again to make the intended use more obvious when someone else looks at your table definition.

Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.


Join two tables using pivot combining two columns in one table

You can UNPIVOT and then PIVOT.

Half of it is below (untested):

WITH P1_Src As
(
SELECT ID_Skill, Val, Col
FROM Job_Skill
UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U
), P1 AS
(
SELECT Col, [1], [2], [7], [8]
FROM P1_Src
PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P
)
SELECT *
FROM P1

You would need to apply a case expression to get the desired Criteria string, then extend the example to do the same for the other table and join the two results on Criteria.

To make this dynamic, you would need to look at the places in the query that currently have hard-coded values, extract the distinct values from the table instead, and use these instead of the hardcoded values.

For an example of the basic technique, see Dynamic pivot: sum of sales per month or search for "dynamic pivot".


pgAdmin 4 v2.1 download - could not verify .sig file

works now after the maintainer sent me his public key, which I had to certify first, once that was all set up I was able to verify the .sig file.


Retrieve latest 3 values from a large table

Check:

SELECT meterid /* , IF(SUM(metervalue>0),1,0) flag */
FROM ( SELECT t.*,
              IF(@meterid=t.meterid,
                 @rownum:=@rownum+1,
                 @rownum:=1+least(0,@meterid:=t.meterid)) rown
       FROM meterreadings t,
            (SELECT @rownum:=1, @meterid:=0) vars
       /* WHERE t.date_time > @some_date_time */
       ORDER BY meterid, date_time DESC
     ) nums
WHERE rown < 4
GROUP BY meterid
HAVING SUM(metervalue>0) > 0

The resultset is a list of meterid's which have at least 1 positive value within last 3 values.

Commented WHERE allows to decrease the amount of records processed by setting some "start point".

If you need full list of meterid's with a 'flag' of positive value existence - remove HAVING condition and uncomment flag field in the output list.


MyISAM tables are not guaranteed to be consistent with disk and, effectively, Amazon recommends to manually do the equivalent to a FLUSH TABLES WITH READ LOCK (you cannot do that in RDS, so you have to lock and flush each MyISAM table individually). So you are right in the fact that backups may not be doing correctly on your setup.

Creating a backup from a slave is a very common way of doing backups, because in your case, you do not care about blocking or affecting the performance of the slaves. But you are not really solving the problem, because MyISAM is not supported for read replicas, as it basically uses the same snapshoting mechanism, potentially inconsistent with non transactional databases.

If you really need hot & consistent backups, you need InnoDB. Otherwise, lock, flush & snapshot.

If you want to guarantee the consistency of a backup, though, setting a replica and seeing that it works is one of the typical easy ways to partially do it. So creating a replica and leaving it on for a while without replication breaking may mean that at least it has all the records and tables you used in your latest transactions (with no guarantee overall).


Upgrade Oracle APEX 4.0.2 to 5.1.1 error

This is

Bug 25560625 : ORA-24344 ERRORS IN INSTALL.LOG THROUGH UPGRADE FROM APEX 4.0.2 TO 5.1

Still under investigation.

You could upgrade to 4.1 and then to 5.1 to avoid this bug, or you could also try 5.0.


I found the reason that did not work, and really was not password correct because I used the password to these characters who divided password to pieces, and of that reason but it was not correct. I used to password ",", "$" and other characters.


How to activate shard balancer in sharded MongoDB

Basically you have a few misunderstandings here, the first being that the balancer is a load balancer.  It is not - it simply looks to address imbalances in chunk counts on your shards.  That can have the side effect of balancing your traffic out as it moves chunks around, but strictly speaking it is not a load balancer.  It also does not run continuously, rather it runs when there is work to be done and imbalances to address, otherwise it is dormant.

To explain the output you are getting from the commands, let's take them one at a time.  First off, Let's look at what sh.getBalancerState() does (run any function without parentheses in the mongo shell and you get to see the code behind it):

mongos> sh.getBalancerState
function () {
    var x = db.getSisterDB( "config" ).settings.findOne({ _id: "balancer" } )
    if ( x == null )
        return true;
    return ! x.stopped;
}

So, what that command is doing is checking the settings collection in the config DB to determine if the balancer is enabled or not.  If we stop the balancer, we see the setting change:

mongos> sh.stopBalancer()
Waiting for active hosts...
Waiting for the balancer lock...
Waiting again for active hosts after balancer is off...
mongos> sh.getBalancerState()
false

If we flip it back to enabled, we see true returned once more:

mongos> sh.startBalancer()
mongos> sh.getBalancerState()
true

So, sh.getBalancerState() is basically for checking the setting and telling you whether the balancer is enabled or not.  What it does not speak to is whether the balancer is currently actively running (i.e. checking for imbalances, migrating to address any imbalances it finds).  That's where sh.isBalancerRunning() comes in.

However, if the balancer is not currently doing any work, it will not be "running" and so it will return false:

mongos> sh.isBalancerRunning()
false

Hence, let's give it some work to do.  I will re-use my example from this answer and create an imbalance while the balancer is off.  Here is sh.status() and the output of sh.getBalancerState() once I have completed the pre-split:

mongos> sh.getBalancerState()
false
mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 3,
    "minCompatibleVersion" : 3,
    "currentVersion" : 4,
    "clusterId" : ObjectId("53b5d3b5d95df3a66a597548")
}
  shards:
    {  "_id" : "shard0000",  "host" : "localhost:30000" }
    {  "_id" : "shard0001",  "host" : "localhost:30001" }
    {  "_id" : "shard0002",  "host" : "localhost:30002" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0001" }
    {  "_id" : "users",  "partitioned" : true,  "primary" : "shard0001" }
        users.userInfo
            shard key: { "_id" : 1 }
            chunks:
                shard0001   2049
            too many chunks to print, use verbose if you want to force print

Once I re-enable the balancer, it is going to have plenty of work to do to redistribute those 2049 (empty) chunks evenly across 3 shards, so I will have plenty of opportunity to run sh.isBalancerRunning() and get a positive.  Interestingly, it took me several tries to get this to return true (just showing two for brevity):

mongos> sh.isBalancerRunning()
false
mongos> sh.isBalancerRunning()
true

Why is that? Well, let's look at the function again:

mongos> sh.isBalancerRunning
function () {
    var x = db.getSisterDB("config").locks.findOne({ _id: "balancer" });
    if (x == null) {
        print("config.locks collection empty or missing. be sure you are connected to a mongos");
        return false;
    }
    return x.state > 0;
}

It is a query on the config database again, this time on the locks collection.  It looks for a lock belonging to the balancer and then returns true if the state is greater than 0.  Here are two examples of the document, one that returns false and one that returns true:

db.getSisterDB("config").locks.findOne({ _id: "balancer" });
{
    "_id" : "balancer",
    "process" : "adamc-mbp:30999:1404425140:16807",
    "state" : 2,
    "ts" : ObjectId("53b5d86fd95df3a66a5975ff"),
    "when" : ISODate("2014-07-03T22:25:51.574Z"),
    "who" : "adamc-mbp:30999:1404425140:16807:Balancer:1622650073",
    "why" : "doing balance round"
}
db.getSisterDB("config").locks.findOne({ _id: "balancer" });
{
    "_id" : "balancer",
    "process" : "adamc-mbp:30999:1404425140:16807",
    "state" : 0,
    "ts" : ObjectId("53b5d86ed95df3a66a5975fe"),
    "when" : ISODate("2014-07-03T22:25:50.528Z"),
    "who" : "adamc-mbp:30999:1404425140:16807:Balancer:1622650073",
    "why" : "doing balance round"
}

If you look closely, you will notice that the ts fields are essentially consecutive, and what you will see with empty chunks is that the non-zero states are very transient.  If I fill up the chunks with data it is far easier to generate a positive.

There you have it - a full explanation of the commands you were running and why you got the results you saw.  I suspect that the root of the question is actually related to a traffic imbalance, but it is not the balancer that generally causes that type of problem (as mentioned before, it is not a load balancer) - traffic imbalance is more likely cause by:

A poor shard key (perhaps very poor)
Balancing is not enabled for the database/collection (see previous pre-split answer for instructions there)
Something is preventing the balancer from working (config server down, balancer window, migration aborts)


You can use the builtin CONVERT function with SQL Server Management Studio.

select CONVERT(VARBINARY,'S-1-5-21-39949945-591092532-1226208319-1007') FROM TableName


Columnstore index and low selective columns

One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).

So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).


SQL Server - What is the difference between BULKADMIN and ADMINISTER BULK OPERATIONS

If we look at the SQL Server Permissions Poster (2017 edition):

It appears as though ADMINISTER BULK OPERATIONS covers bulkadmin in addition to OPENROWSET which allows access to external sources.


restoring Postgres database: pg_restore -vs- just using psql

If you created an SQL-format dump, all you can use is psql.

If you created a custom-format (pg_dump -Fc) or directory-format (pg_dump -Fd) dump, you can and must use pg_restore.

Custom and directory format dumps offer a lot of advantages over plain SQL script dumps, and I use them exclusively. You can selectively restore only some tables/schema, can choose whether to include only schema, only data, or both at restore time, etc. Lots of the options you have to specify at pg_dump time with SQL-format dumps can be chosen at restore-time if you use a custom-format dump and pg_restore.

If it weren't for backward compatibility I'm sure the default for pg_dump would be -Fc (custom) format.

You can't convert an SQL-format dump into custom-format or directory-format without restoring it to a PostgreSQL database and then dumping the restored DB.


How can a connection for one DB block a connection to another DB in SQL Server?

So there's a couple of ways that this can happen.

The queries in the blocking database reference objects within the blocked database.
The queries are waiting for tempdb to allocate pages and one query is blocking another query.
There is memory pressure and queries are waiting for memory to be allocated so they have to wait their turn in the memory allocator.

If you download and put sp_whoisactive on the server it'll give you a lot more information about why things are blocking if this happens again.

Given your note about the memory pressure you probably were suffering from either #2 or #3 on my list above.  Odds are it was memory not being able to be allocated fast enough, but it's possible that because you didn't have enough memory stuff was spilling to tempdb faster than tempdb to do "stuff".


Oracle: How to split records across days

There are several ways to do this.  One would be to use a pipelined function to return the additional row when the day spanning condition is detected.  Another way is to duplicate the rows and then eliminate the ones that don't need to be duplicated and adjust the times for those that do.  Here is an example:

SELECT
   Case When TwoRowsNeeded = 1 And RowNumber = 2 Then Trunc(EndDate)
        Else StartDate
   End StartDate,
   Case When TwoRowsNeeded = 1 AND RowNumber = 1 Then Trunc(EndDate)-1/24/60/60
        Else EndDate
   End EndDate
FROM
(
SELECT StartDate, EndDate
   , Row_Number() OVER (PARTITION BY StartDate, EndDate ORDER BY NULL) RowNumber
   , trunc(EndDate,'DD')-trunc(Startdate,'DD') TwoRowsNeeded
FROM t1
CROSS JOIN (select level from dual connect by level <=2)
)
WHERE RowNumber=1 Or TwoRowsNeeded=1;

Setup:

drop table t1;
create table t1 as (
   select trunc(sysdate) StartDate, trunc(sysdate)+2/24 EndDate from dual
   UNION ALL
   select trunc(sysdate)-1, trunc(sysdate)-1+2/24 from dual
   UNION ALL
   select trunc(sysdate)-2/24, trunc(sysdate)-3/24 from dual
   UNION ALL
   select trunc(sysdate)-2/24, trunc(sysdate)+1/24 from dual
   UNION ALL
   select trunc(sysdate)-1/24/60, trunc(sysdate)+1/24/60 from dual
   );

SELECT to_char(StartDate,'MM/DD/YYYY HH24:MI:SS') StartDate
   , to_char(EndDate,'MM/DD/YYYY HH24:MI:SS') EndDate
FROM t1;


This relates to rotating harddisks (not SSDs). One rotating harddisk is one spindle.

So if your server uses e.g. a RAID with 16 disks, then your effective_spindle_count is 16

It is essentially a measure of how many concurrent I/O request your server can manage. Rotating harddisks can (typically) only handle one I/O request at a time. If you have 16, your system can handle 16 I/O requests at the same time.

The mentioned rule of thumb is no longer valid for SSDs. As they can typically handle several parallel I/O requests at the same time. I don't know however how you could obtain that number.

Which is also mentioned in the Wiki page you linked to:

  There hasn't been any analysis so far regarding how well the formula works with SSDs.


Does a weak entity always have a weak relationship and total participation in ER diagram?

A weak entity type is one whose primary key contains a relationship to another entity. Total participation in a relationship means that every instance of an entity type must be paired across that relationship to the entity instance at the other end of the relationship. Add in the theoretical requirement that an entity type's primary key must not contain NULLs and you have the solution: a weak entity's key contains a reference to another entity and that reference cannot be NULL i.e. the weak entity has a total participation in that relationship.

In physical terms we can represent the weak entity type's implementation as a table like this:

create table weak_entity
(
  parent_id  not null,
  weak_id    not null,
  other_columns,

  primary key(parent_id, weak_id),

  foreign key(parent_id) references parent(parent_id)
);

Note that parent_id is NOT NULL so weak_entity has total participation in this relationship.

The converse need not be true - the "parent" entity need not have total participation across the same relationship.

In real life some DBMS do allow nullable columns in a key. Then the total participation requirement is relaxed. That's the messy reality of real life, distinct from nice relational theoretical considerations.

Your reference says a weak relationship

  exists when the PK of the related entity does not contain at least one of the PK attributes of the parent entity. For example, if the PK of a COURSE entity is CRS_CODE and the PK of the related CLASS entity is CLASS_CODE, the relationship between COURSE and CLASS is weak.

In other words, the foreign key column(s) in the child (that reference the parent's primary key) must not be part of the child's primary key. This contrasts with the definition of a weak entity (above) which says the parent's key must be part of the child's primary key. The implementation would look something like

create table strong_entity
(
  strong_id    not null,
  other_columns,
  parent_id  not null,

  primary key(strong_id),

  foreign key(parent_id) references parent(parent_id)
);

From this I would say a weak entity must have a strong relationship to that parent and a strong entity must have a weak relationship.

Note that a "child" table can have foreign keys referencing many other "parent" tables. Some of those relationships may be part of the child's primary key (and will be strong relationships) and some may not (and are therefore weak relationships).

As an example consider a Q&A site where a user can comment on many posts and a post can have comments from many users. The PK of entity Comment would likely contain the FKs referencing Post's PK and also the FK referencing User's PK. So Comment is a weak entity (its PK contains FKs) and those relationships are strong. Now imagine Comment also has a relationship to a table of IP addresses. That relationship is not part of the PK of Comment so it is a weak relationship.

Although PK columns should be mandatory there is no such theoretical restriction on FK columns. Therefore the columns implementing a weak relationship can be nullable and we cannot say, in general, whether there must be total participation over a weak relationship.


The permissions on "/etc/ssl/private" should be "drwx--x---" (aka "0710"), which should have been the permissions it came with.  I don't know it got changed.  You could restore it with:

sudo chmod 0710 /etc/ssl/private/

Although maybe there is also a way to do it with your package manager.

You don't need a server.key file in "/var/lib/postgresql/9.5/main", because postgresql is configured to look for it in "/etc/ssl/private/ssl-cert-snakeoil.key" instead.  You can make or get your own cert and put in "/var/lib/postgresql/9.5/main", but then you will have to change the configuration to look for it there.


As you are working with SQL Server 2014 you could cast/convert as a time (started in SQL 2008)

SELECT *,
cast(DATEADD(SECOND, ( DATEDIFF(SECOND, InTime, OutTime) ), 0) as time(0)) AS Total
FROM   TableXYZ


Stored Procedure stuck at waitfor delay '00:00:02'

This could be expected behaviour especially if you're deleting based on an index (aka where [primarykeyfield] < targetID)

since that delete will take in the ms of time you'll almost always catch the process in the wait time between the two batches

Say for example the delete takes 10ms (i imagine it will potentially take less than this but its just an example), the wait time is 2000ms

So for every time you run the usp_whoisactive command there is a 10/2010 chance of hitting that in the period of time the delete is running (less than 0.5%) and a 2000/2010 chance of catching it during the wait for delay.

If you want to observe it directly find the spid that its running over and run the profiler over it checking for the SP:StmtCompleted search and you'll see the delete occurring every 2s and going into the waitfor (note if you wan to see the start of the waitfor as well check the SP:StmtStarting but the completed one will give you the duration of how long it takes to run each part.


MSSQL Audits are growing out of control. 1gb every 4 minutes - any way to get size down?

Auditing should be enabled based on requirement from Compliance/IT security or Audit department of your organization. If you enable auditing as mentioned in this link, it would start auditing each and every thing. It will not only make audit files to grow rather, it would impact on performance of your instance badly.

Ideally if you follow C2 auditing, it captures basics of auditing and is accepted internationally.

  C2 Auditing and Common Criteria Compliance are two internationally
  accepted auditing standards.

  C2 audit mode is the auditing option that can be used to facilitate
  auditing standard. By enabling C2 auditing, it allows the
  administrator to enable a comprehensive type of auditing, logging.
  This is named as C2 audit mode because it is logging of the form
  specified by the U.S. Department of Defense regulations to meet the
  certification at the C2 level of trust. Those regulations were
  specified in a document called the Trusted Computer System Evaluation
  Criteria, often referred to as the “Orange Book” in the “Rainbow
  Series.” The Orange Book is now depreciated in favor of the Common
  Criteria. Nonetheless, C2 Auditing is still a commonly used term.

  C2 audit mode generally means assigning a unique generated audit ID to
  each group of related processes, starting at login. Thereafter,
  certain forms of system calls performed by every process are logged
  with the audit ID.

You might read more about C2 auditing at this link.

There is another method of auditing accepted called "Common Criteria Compliance" and details are as below:

  C2 auditing has been deprecated by the Common Criteria specification
  which was developed by the European Union. Common Criteria Compliance
  is internationally recognized set of guidelines for security for
  information technology products. This is applied to operating systems,
  Databases, Network Devices and Smart cards etc. Common criteria
  certified products have been rigorously evaluated by accredited third
  party security labs.

  This option is available same versions as C2 Audit but Common Criteria
  Compliance is available in Enterprise and Data Center editions of SQL
  Server 2008 R2 and after versions. However, with respect to the SQL
  Server, if you are complying with either C2 or common criteria, the
  audit result is similar. In the case of Common Criteria Compliance,
  audited data can be viewed from system views which is more secure than
  the C2 audit mode.

if you enable one of them, your problem of file size and performance should go.

In case, you are really interested to get to the root of your culprit in your current scenario then, you should query your audit log and group them by action and disable that event, same can be done using below query:

SELECT action_id, count(*)
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT)
group by action_id

Please remove SCHEMA_OBJECT_ACCESS_GROUP from auditing events and you should be fine for disabling select statement from audit. You can check more about this at link.

Hope this helps.


SQL Server query result transfer time over VPN (WAN)

In your VPN properties, click options, PPP Settings.
Do you have "enable software compression" ticked?
SQL data compresses quite nicely, and if the remote site is heavily used bandwidth wise, it may simply be struggling to send you the data.


If the login credentials are in the ODBC connection, which security connection setting to use?

I have a linked MySQL server set up through an ODBC connector, so these settings may help you get somewhere. I found the setting that worked 100% of the time for me was selecting:
Be made using the login's current security context
Then in the box above, click Add, then as your local login choose your default local SQL server user, or a specific SQL Server user, and un-check impersonate, then in the Remote User and Remote Password field enter your PostgreSQL credentials. I am not 100% sure of the actual logic behind this, but the way I see it is it's linking up your local SQL server user with your remote user.

If that doesn't work, then you could also try selecting Be made using this security context and entering your Remote password and Username there, but I found my previous suggestion to always yield the best results for me. Good luck!


Postgres : Truncate if exists in psql function with parameter

Use the variable FOUND:

create or replace function truncate_if_exists(tablename text)
returns void language plpgsql as $$
begin
    perform 1
    from information_schema.tables
    where table_name = tablename;
    if found then
        execute format('truncate %I', tablename);
    end if;
end $$;

Note that I've used PERFORM instead of SELECT as I don't need an output of the query. I want to know whether the query returns any row (FOUND = true) or not (FOUND = false).


Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

Although Normalization and partitioning both produce a rearrangement of the columns between tables they have very different purposes.

Normalization is first considered during logical datamodel design. It is a set of rules which ensure that each entity type has a well-defined primary key and each non-key attribute depends solely and fully upon that primary key.

Partitioning comes in during physical database design, when we start to map logical attributes to physical columns and determine the operational characteristics required from the system. Sometimes it is an optimisation added after testing under load because performance was found to be inadequate. It can also play a role in implementing a data retention policy.

In partitioning we recognise that a table is made from rows and columns. When we partition we separate some of those rows (or columns) from the others and hold them in a physically different location.

Horizontal partitioning is when some rows are stored in one table, and some in another. There could be many sub-tables. A typical example is when currently-active transactional data is separated from old "archive" data. This keeps "hot" data compact, with associated performance improvements. We many be able to make the archive tables read-only, compressed and on cheaper disk, too.

As the next step each partition may be moved onto separate hardware. This is commonly know as "sharding." Advantages include being able to use many cheaper boxes rather than one very large, very expensive server, and being able to position a user's data geographically close to her. The cost is increased application complexity. Some DBMS incorporate this ability natively.

Vertical partitioning is when some columns are moved to a different table or tables. Similar to horizontal partitioning the motivation is to keep the "hot" table small so access is faster. Say you run an e-marketing company. 99% for the time you need a person's name and email address and nothing else. These will go in one table and all the other stuff which is useful but seldom-used - birthday, golf handicap, PA's phone number etc. - go in a different table. It can also help when the partitions have different update regimes or are owned by different sections of the business. The two tables can have the same primary key column, and corresponding rows could have the same key value. While it is possible to have multiple vertical partitions for a table, and to shard vertically, I've never come across it.

Vertical and horizontal partitioning can be mixed. One may choose to keep all closed orders in a single table and open ones in a separate table i.e. two horizontal partitions. For the open orders, order data may be in one vertical partition and fulfilment data in a separate partition.

The techniques I've talked about are ways to change the design to improve performance. Scaling is when you change the hardware. One can scale up by buying a bigger box with more RAM, CPU or faster disk, or scale out by moving some of the work onto a different box. Scale up is sometimes called scaling vertically whereas scale out can be called horizontal scaling. While horizontal scaling and sharding have an obvious relationship they are not synonymous. It would be possible to use replication technologies to copy an entire database to another location for use by the users there, thus achieving scale-out, without having to partition any tables.


To first address your questions:

  The DB Admin team will remote desktop to that server and connect to
  SQL server using SSMS,not from their local SSMS. In that case do I
  have to have domain service accounts?

While I would not advise that a production database is administrated by running SSMS directly on the production server, these two items are completely unrelated.

  The application which DB support will have few hundred users
  connecting over network? In that case the default service account will
  be enough or do I have to have domain service accounts?

Again, these two items are completely unrelated.

You should check out Guidelines on choosing Service Accounts for SQL Server Services. The key parts of that, for you, are:

  During a new installation, SQL Server setup does not default SQL
  Server engine Service and SQL Server Agent service to any account. The
  account specification is required step for these services. For details
  on recommended secure accounts, refer to Books Online Topic Setting Up
  Windows Service Accounts.

and

  When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts.

So based on that, you will need to specific accounts for the engine and agent, and I would recommend having discrete logins for all services which adhere to the principle of least privilege as per the linked articles from the quotes.


Postgres declare table as a parameter for a function?

You cannot declare a table as function parameter per se, since there are no table variables in Postgres. But there are various ways to achieve what you might want to achieve - which isn't exactly clear yet.

You can "pass" a table as ...

table name (type text or name) - especially when tables may not actually exist (yet):

Define table and column names as arguments in a plpgsql function?

object identifier (type regclass):

PostgreSQL: Pass table as argument in function
Table name as a PostgreSQL function parameter

actual table type (type anyelement) - using the concept of polymorphism:

Refactor a PL/pgSQL function to return the output of various SELECT queries

In most cases you will end up using dynamic SQL inside the function. Be wary of SQL injections vectors. Related:

SQL injection in Postgres functions vs prepared queries
PL/pgSQL regclass quoting of table named like keyword

The return type depends on your input and what you want to achieve ...


Why shouldn't I increase SQL Server's timeout globally everywhere?

So we decided to increase database timeout for queries globally. But I
  have this feeling that this is wrong. Yet I'm not able to bring
  forward any reasons. Is it bad to increase timeout everywhere? Why
  shouldn't we do this?

The default client API query timeout of 30 seconds is appropriate for a typical OLTP workload, albeit arbitrary.  The value should be set high enough to allow queries on a reasonably healthy system to complete successfully yet low enough so that queries timeout when the rather than wait indefinitely when things aren't well, such as in a long-term blocking scenario or due to a bad execution plan.  There is no single timeout value that is appropriate for all queries unless the system is a 100% OLTP workload or 100% reporting.

It would be better to first address the root cause of longer-running queries before increasing the timeout value.  Perform query and index tuning, ensure hardware is adequately sized and configured properly for your workload, and make sure EF data access is optimal.  These practices will make the most efficient use of available resources, reduce compute costs, and provide the best end user experience.

If the amount of data required by queries doesn't increase with table size yet queries run longer as the number of rows in tables increase, this suggests scans. Query and tuning would be a good first step, assuming SQL Server memory is appropriate for the database size.


After comments on questions

You can query sys.indexes, looking at the allow_row_locks and allow_page_locks columns. Also, sys.tables has a column lock_escalation

I think that will allow you to find all non-default options about locks.


You should definitely two separate tables!

What if, in the future, documents get linked to something else?

As it is anyway with your documents_relationship table, you are most likely going to have fields that apply to clients and not assets and vice versa!

This will mean NULLs. Good frule of thumb IMHO, a field should never be NULL by design for its entire life. Note: this does not mean that NULLs should never be used!


If all you are getting is the header of the mysqldump, an error must be posted somewhere. In order to make the errors show up on demand, please run the dump like this:

mysqldump -u[username] -p[password] -h[hostname] zm3_quizzes 2>zm3_quizzes.err | gzip > [backup file path]

This should post the stderr of the mysqldump to zm3_quizzes.err.


Am I Setting Up My Database Correctly? Or Am I Thinking About Things The Wrong Way?

Views: Your approach is a very widely-accepted practice. The benefits are well-documented: https://www.c-sharpcorner.com/blogs/advantages-and-disadvantages-of-views-in-sql-server1

Stored Procedures: Also, as you described it, widely-practiced (the disadvantages are notable, too) http://sqltechtips.blogspot.com/2015/11/stored-procedure-benifits.html

In my experience it's common to see views and stored procedures used for the reasons stated, and they behave as advertised.

Whatever method you choose, I strongly advise consistency, as it can be frustrating to have to look in many places for business-rule implementation: code with embedded SQL, stored procedures, views, ORM...  try to apply some systematized implementation to limit the number of places you have to look for constraints, for example - it's possible to put them in any/all of the places I just listed.


How can I define the datatype of a view in MySQL?

You can hunt down the column definitions in the INFORMATION_SCHEMA. For example, for the table mydb.mytb and the column name needed is called mycol, do this:

SELECT column_type FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytb'
AND column_name='mycol';

For the query with formulas that you have in your question:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

Try creating your own empty temp table using your query with a FALSE-evaluated WHERE clause:

CREATE TABLE IF NOT EXISTS test.mycolumnmap
SELECT id, FLOOR(date_id / 100) month_id, SUM(value) value FROM some_table WHERE 1=2;

That table, though empty, has a definite table structure and some columns types defined. Now, just run the same query to get the column types from the temp table:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='mycolumnmap'
AND column_name IN ('id','month_id','value');

Another alternative is to just ask the INFORMATION_SCHEMA about the columns of the view directly:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='view_name';

Give it a Try !!!


What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

Everybody's excited about AlwaysOn and ColumnStore, but many of the benefits of SQL Server 2012 are not exclusive to high-end editions. I don't want to sound like a spokesperson, but I've given plenty of presentations on SQL Server 2012 and I think it has a lot to offer at whatever edition suits you.

Partially Contained Databases which allow you to move databases between servers or environments with a few less shackles (namely server-level logins and server collation dependencies - future versions will handle thornier items like linked servers and Agent jobs).
Management Studio is now a much better tool, aligned with Visual Studio. IntelliSense is better and a whole bunch of other features make editing easier. Now of course you can have 2008 R2 on your server and use the 2012 version of SSMS, but I'm not sure how that works licensing-wise, and some shops don't want mixed versions (I prefer having the most recent tools on my workstation even to manage downlevel servers). I blogged about the changes early on, when there were still bugs, so please ignore the negatives since most or all are fixed as of RTM. I shudder now when I have to use an earlier version of SSMS.
Metadata enhancements allow you to inspect resultsets of objects and ad hoc queries, and also allow you to better shape the output of queries.
Custom Server Roles allow you to define a much more granular set of permissions for users at a role level instead of granting/revoking one by one, or just giving in to complexity and giving them sysadmin.
FileTable lets you manage a folder like a table of documents, but still have external control over the contents (so imagine being able to do this with T-SQL, and imagine how hard it would be to do in cmd or PowerShell: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = 'Bob' AND Created < '20100101';)... think FileStream meets WinFS and gets some usability to boot.
T-SQL Enhancements allow you to do many things that were a pain in previous versions:

THROW (think of it as re-raise)
OFFSET / FETCH (simpler, ANSI-standard paging - also see this post)
SEQUENCE (centralized IDENTITY mechanism, like in Oracle)
Windowing/Framing enhancements (various things here, such as awesome running totals performance)
IIF() / CHOOSE() / CONCAT() / EOMONTH()
Date/Time Constructors (e.g. DATEFROMPARTS) similar to DateSerial in VB
PARSE() / FORMAT() - like their .NET counterparts (but see this post about the latter)
TRY_CONVERT() / TRY_PARSE() - return NULL if CONVERT / PARSE fail

Extended Events has an enhanced UI for configuration / viewing, and finally completely covers trace / audit functionality (including much better causality tracking).
Lots of new DMVs, system procedures and ShowPlan enhancements for diagnostics and performance troubleshooting. Also take a look at what CSS is calling "The Black Box Recorder."
Server Core allows you to run on a bare-minimum server without all the UI components (smaller surface area means it is more secure, and reduced maintenance since less parts of OS are subject to Windows Update).
Full-Text Search gets some important underlying performance enhancements, as well as semantic search (think keywords) and customizable proximity / NEAR.
AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB - so you will finally have motivation to get off your old 32-bit hardware.


Performance of an In-Memory Table is worse than a disk-based table

While this post won't be a complete answer due to lacking information, it should be able to point you in the proper direction or otherwise gain insight which you can later share with the community.

  Unfortunately, this definition results in a degradation of performance with respect to the previous situation with a disk-based table. The order of magnitude is more or less 10% higher (that in some cases reach 100%, so double time).

  Most of all, I was expecting to gain a super-advantage in high-concurrency scenarios, given the lock-free architecture advertised by Microsoft. Instead, the worst performances are exactly when there are several concurrent users running several queries on the table.

This is troubling as it should definitely not be the case. Certain workloads are not for in memory tables (SQL 2014) and some workloads lend themselves to it. In most situations there can be a minimal bump in performance just by migrating and choosing the proper indexes.

Originally I was thinking very narrow about your questions regarding this:

  Questions:

  what is the correct BUCKET_COUNT to set?
  what kind of index should I use?
  why the performance are worse than with the disk-based table?

Initially I believed there to be an issue with the actual in memory table and indexes not being optimal. While there are some issues with the memory optimized hash index definition I believe the real issue to be with the queries used.

-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
  SELECT @fixedValue, id2, col1, col2 FROM AnotherTable

This insert should be extremely fast if it were only involving the in memory table. It, however, also involves a disk based table and is subject to all of the locking and blocking associated with that. Thus, the real time waste here is on the disk based table.

When I did a quick test against 100,000 row insert from the disk based table after loading the data into memory - it was sub-second response times. However, most of your data is only kept for a very short amount of time, less than 20 seconds. This doesn't give it much time to really live in cache. Additionally I'm unsure how large AnotherTable really is and don't know if the values are being read off of disk or not. We have to rely on you for these answers.

With the Select query:

SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1

Again, we're at the mercy of the interop + disk based table performance. Additionally, sorts are not cheap on HASH indexes and a nonclustered index should be used. This is called out in the Index guide I linked in the comments.

To give some actual research based facts, I loaded the SearchItems in memory table with 10 million rows and AnotherTable with 100,000 as I did not know the actual size or statistics of it. I then used the select query above to execute. Additionally I created an extended events session on wait_completed and put it into a ring buffer. It was cleaned after each run. I also ran DBCC DROPCLEANBUFFERS to simulate an environment where all of the data may not be memory resident.

The results weren't anything spectacular when looking at them in a vacuum. Since the laptop I'm testing this on is using a higher grade SSD, I artificially turned the disk based performance down for the VM I am using.

The results came in with no wait info after 5 runs of the query on just the in-memory based table (removing the join and no sub-queries). This is pretty much as expected.

When using the original query, however, I did have waits. In this case it was PAGEIOLATCH_SH which makes sense as the data is being read off disk. Since I am the only user in this system and didn't spend time to create a massive test environment for inserts, updates, deletes against the joined table I didn't expect any locking or blocking to come into effect.

In this case, once again, the significant portion of time was spent on the disk based table.

Finally the delete query. Finding the rows based off just ID1 is not extremely efficient with a has index. While it is true that equality predicates are what hash indexes are proper for, the bucket that the data falls into is based off the entire hashed columns. Thus id1, id2 where id1 = 1, id2 = 2, and id1 = 1, id2 = 3 will hash into different buckets as the hash will be across (1,2) and (1,3). This won't be a simple B-Tree range scan as hash indexes are not structured the same way. I would then expect this to not be the ideal index for this operation, however I wouldn't expect it to take orders of magnitude longer as experienced. I would be interested in seeing the wait_info on this.

  Most of all, I was expecting to gain a super-advantage in high-concurrency scenarios, given the lock-free architecture advertised by Microsoft. Instead, the worst performances are exactly when there are several concurrent users running several queries on the table.

While it's true that locks are used for logical consistency, the operations must still be atomic. This is done through a special CPU based compare operator (which is why In-Memory only works with certain [albeit almost all cpus made in the last 4 years] processors). Thus we don't get everything for free, there will still be some time to complete these operations.

Another point to bring up is the fact that in almost all of the queries, the interface used is T-SQL (and not natively compiled SPROCs) which all touch at least one disk based table. This is why I believe, in the end, we aren't actually having any increased performance as we're still constrained to the performance of the disk based tables.

Follow-Up:

Create an extended event session for wait_completed and specify a SPID known to you. Run the query and give us the output or consume it internally.
Give us an update on the output from #1.
There is no magic number for determining bucket count for hash indexes. Basically as long as the buckets don't get completely full and the row chains stay below 3 or 4, the performance should stay acceptable. This is kind of like asking, "What should I set my log file to?" - it's going to depend per process, per database, per usage type.


Insert Array of JSON into Postgres Table

PART1 - INSERTING

You don't have to use unnest() but jsonb_array_elements() and add square brackets to the JSON data structure. I sugget you using a JSON validator website like JSONlint to test the correctness of your JSON data.

This codes inserts 3 new records in inputtable:

WITH json_array AS (
    SELECT 1 AS user_id,
           2 AS chart_id,
           jsonb_array_elements('
               [
                 {
                    "col1": "a",
                    "col2": 1,
                    "col3": 1,
                    "col4": "one"
                 }, {
                    "col1": "b",
                    "col2": 2,
                    "col3": 2,
                    "col4": "two"
                 }, {
                    "col1": "c",
                    "col2": 3,
                    "col3": 3,
                    "col4": "three"
                }
               ]'::jsonb) AS properties
)
INSERT INTO inputtable (user_id, chart_id, properties)
SELECT * FROM json_array

PART2 - UPDATING

In order to update you must specify data_point_id values, so you have to know them a priori.

This works perfectly, but probably there are other naive solutions:

WITH update_table AS(
   SELECT unnest(ARRAY[1, 2, 3]) AS data_point_id,
          jsonb_array_elements('
            [
              {
                "col1": "a",
                "col2": 6,
                "col3": 7,
                "col4": "eight"
              }, {
                "col1": "b",
                "col2": 10,
                "col3": 11,
                "col4": "twelve"
              }, {
                "col1": "c",
                "col2": 3,
                "col3": 3,
                "col4": "new"
              }
           ]'::jsonb) AS properties
    FROM inputtable
)
UPDATE inputtable
SET properties = update_table.properties
FROM update_table
WHERE inputtable.data_point_id = update_table.data_point_id


First verify if what you're seeing is the same as what SQL Server can see,  by running this:

USE tempdb;
EXEC sp_helpfile;

If all 3 of your files are here, you somehow created them there by accident.

TempDB is a 'special' system DB that gets re-generated every time you start the SQL Server service.

Setup TempDB the way you want it, restart the server so TempDB recreates itself and run the above query again, to see if the new settings stuck.

If you want to still add a file somewhere manually you can do this:

ALTER DATABASE tempdb ADD FILE (
    NAME = N'LogicalNameHere'
    ,FILENAME = N'D:\Whatever\tempDB2orWhatever.ndf'
    ,SIZE = xMB
    ,FILEGROWTH = xMB
    );

If you want to keep all of your TempDB files but just move them around, get the info from sp_helpdb and modify to include your paths/files/names as found here:

USE [master];
GO

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'E:\Sqldata\templog.ldf');

Note you should usually have multiple TempDB data files. If you don't, you are risking logical file contention, which is not pretty.

Check out Brent's blog for more info on SGAM and PFS contention.


MongoDB Migrating away from RocksDB to WiredTiger

If replication doesn't keep up with primary and it's not possible to extend the oplog, I'd try to shard the deployment and...

let smaller shards replicate faster

or
drain the original shard and then remove it from the deployment - this solves you storage engine problem, but leaves the replication as laggy as before


PostgreSQL requires "GROUP BY" when aliasing a table?

The problem is that the 2 tables are not identical.

The first has (id) as the PRIMARY KEY while the second has (id) a UNIQUE constraint (or index, doesn't matter). This might seem like a minor detail but it isn't.

Postgres has added in version 9.1, a feature were functionally dependent columns need not be mentioned in a GROUP BY clause (if the column they depend on is mentioned in the GROUP BY) and still used in the HAVING, SELECT and ORDER BY clauses. However the implementation is not 100% complete. It doesn't identify all possible functional dependencies but only those coming from PRIMARY KEY constraints. While a UNIQUE constraint with all columns NOT NULL does not differ in any meaningful way from a PRIMARY KEY constraint, the implementation does not consider UNIQUE constraints.

So, for example, with these two very similar tables (test in SQLfiddle):

CREATE TABLE products
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    PRIMARY KEY (id)
  ) ;

CREATE TABLE products_active
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    UNIQUE (id)
  ) ;

The query will succeed for the first table:

SELECT p.id, p.status, p.name
FROM products AS p
GROUP BY p.id ;

while it will fail for the second:

SELECT p.id, p.status, p.name
FROM products_active AS p
GROUP BY p.id ;

with:

  ERROR: column "p.status" must appear in the GROUP BY clause or be used in an aggregate function


View history of preferred replica in Availability Group - when

... the preferred replica in Availability Group changed?

Availability Groups don't have preferred replica. They have primary and secondary replicas, but not preferred. Windows Server Failover Clustering has preferred owners. Are you looking for who was the primary replica or who was a preferred owner?

If it's the WSFC preferred owner you are looking for, then you'll have to scrape the cluster log.

If it's the primary and secondary replicas, then nothing exists out of the box - you'll need to write your own. This data can be gathered from a few different sources:

AlwaysOn Health extended event session
SP_Server_Diagnostics output
Polling the DMVs and recording changes
Scraping the cluster log

You don't need all of those, it's a mix and match sort of thing. Basically you'll want to find out who was primary at the moment the logs started and then look for replica state changes. You'll need all of the logs from all of the replicas to get the most complete picture - but having a single log from a single replica can give you all the data needed as well, just takes a bit more data manipulation to get there.


Execution plan not understanding correctly

(To answer @Michael, then segue into the OP's question.)  ORDER BY fr_user DESC, created DESC can use an index backwards if all are DESC.  Or, as in this orginal case, it can do WHERE fr_user = constant ORDER BY created DESC when you have INDEX(fr_user, created).  It simply goes to the end of the fr_user=1 entries in the index and walks backward.

But your question is "why are all 170 rows examined".  Well, they aren't.  The "170" is bogus because EXPLAIN almost always ignores LIMIT.  This is a known failing of EXPLAIN.  The Optimizer, in some situations, does take LIMIT into consideration.


Large `mongodump` followed by `mongorestore`

First of all, you can use a pipe

mongodump -h sourceHost -d yourDatabase … | mongorestore -h targetHost -d yourDatabase …

This reduces the time, as each document read will more or less instantly be restored on targetHost.

However, this has the disadvantage that you might run into problems if the procedure is aborted for some reason, for example of a network failure. As for parallelization, you could do the above for each collection, but I doubt that you will have any performance gain, as the limiting factor most likely is IO, and even if not, the concurrency will most likely be a killer.

What I would do is to create a temporary replica set, consisting of the old server, the new server and an arbiter. The initial sync is rather fast, and even if you run into network problems, the sync mechanism will make sure that everything is fine. After the initial sync is done, you simply have the old server step down as primary and restart the new server without the replSetName option, making it a standalone again. Now you can connect to the new server and all data is transferred.

The advantage is that this works with minimal downtime and no attendance. After you initialized the replica set, your application can still use the old server, and even new data will be automatically transferred to the new server. So you do not have to sit besides it – and we all know that the process is likely to finish at 3am, regardless of the time zone. ;) Any time after the initial sync is finished (even hours or days later), you can make the new server a standalone, and change your application's connection string to the new server. This is a matter of 2 or 3 minutes, if planned properly.

Edit

This method has a drawback, however: You can only upgrade from one release to one release higher. So migrating from 2.4 (archeology as of the time of this writing) to 3.4 (cutting edge), you'd have to repeat the process multiple times:

From 2.4 to 2.6
From 2.6 to 3.0
From 3.0 to 3.2
From 3.2 to 3.4


How to Insert into with postgresql and JSONB

Quickly did lots of testing and i got what you wanted.

INSERT INTO product(data1, job_id)
SELECT json_build_object('__COD__',data2->>'cod','__PAGE__',data2->>'page') data1, job_id FROM product_temp;

Your data1 is one column. Tested with PostgreSQL 9.5


Can I estimate the row count of a large mysql table using the disk space?

You cannot make that kind of approximation work if the InnoDB table experiences DELETE and UPDATEs. With innodb_file_per_table enabled and a table called mydb.mytable, this is what you get

/var/lib/mysql/mydb/mytables.frm : Table Format Info
/var/lib/mysql/mydb/mytables.ibd : This file contains data pages and index pages making up the table and its indexes (Each page is 16KB).

There will be fragmentation amongst the data pages due to DELETEs and UPDATEs
There will be fragmentation amongst the index pages due to BTREE operations (node balancing, node splits, etc).

Even if you defragment the InnoDB with OPTIMIZE TABLE, there is no way of knowing if a single row occupies two or more pages.

ALTERNATIVE APPROACH

You could probably force the index statistics to give you a round figure. You could just run

SHOW INDEXES FROM mydb.mytable;

Look at the Cardinality column of the PRIMARY KEY. That will give you a very close guess at the rowcount. However, if you run SHOW INDEXES FROM mydb.mytable; multiple times on a large table, you get a different number each time. That's because InnoDB will scan the BTREE nodes and give a roundabout number. If you want to go by the Cardinality of the PRIMARY KEY as the rowcount, you should run this

SET GLOBAL innodb_stats_on_metadata = 0;

before running SHOW INDEXES FROM mydb.mytable;

Believe me, this is a much better guess that dumping a subset of the data and guessing from the filesize.


How to count absence days from differents shift?

It seems like you may be going about things in a much more complicated way then they need to be. Generally for figuring out things like Holidays, Weekends, and other Date-centric Facts, a DateDimensions table makes things a lot simpler. There's a lot of articles out there on how to generate one, this article being a good example. Once you have a DateDimensions table, you can join to it in a similar way to this:
SELECT E.GroupId, COUNT(*) AS Abscences
FROM employee AS E
INNER JOIN @tablevarDepDays AS D -- Assuming this is where you store absences?
    ON E.employeeId = D.employeeId
INNER JOIN DateDimensions AS DD
    ON D.SomeDateField = DD.[Date] -- D.SomeDateField would be your absence date
WHERE DD.IsHoliday = 0 -- Filters out Holidays
GROUP BY E.GroupId

Note it would be more helpful if you could please include your table schema for all your tables involved. If so, I can update my answer to be less pseudo-code like.


If you are honestly expecting to have the numbers you say for an all-InnoDB database, you are going to be needing a lot more memory. Here is why:

InnoDB caches data and index pages in the InnoDB Buffer Pool. If you will be doing heavy reads, a large buffer pool is your best friend. Since most people recommend 75-80% of RAM for a dedicated MySQL DB Server, that would about about 3200M (3.2G).

I would go the following (32GB or RAM)

innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_file_per_table = 1

There are too many other aspects for me to just put in the answer. Here are some of my InnoDB posts:

Possible to make MySQL use more than one core?
How large should be mysql innodb_buffer_pool_size?
How do you tune MySQL for a heavy InnoDB workload?
Clarification on MySQL innodb_flush_method variable
Is it safe to use innodb_flush_log_at_trx_commit = 2
My Other InnoDB Posts

Don't forget to tune the per-connection settings and multiply the sum of them by max_connections : See my post Understanding mysqltuner Recomendations w/ Unused Catalogs


That might be a permission issue. When executed as a job step the code runs under the SQL Server Agent Service Account. When you execute it yourself it runs under your account.

Make sure that the QSL Server Service Account has appropriate permissions on the linked server. You can also try to impersonate that account by using

EXECUTE AS LOGIN='Domain\ServiceAccount';

before executing your code. That might reveal additional error detail and should make troubleshooting easier.


Can two SQL “Always on” nodes have different datafiles position on disk?

Having different file paths for secondary databases in an Always On AG setup is possible but not recommended, as shown by below quote from the microsoft docs:

We recommend that, if possible, the file path (including the drive
letter) of a given secondary database be identical to the path of the
corresponding primary database

Source
If you want to do it  anyway, there are restrictions and caveats as mentioned here:

If the file path (including the drive letter) of a secondary database
differs from the path of the corresponding primary database, the
following restrictions apply:

New Availability Group Wizard/Add Database to Availability Group Wizard: The Full option is not supported (on the Select Initial Data
Synchronization Page page),
RESTORE WITH MOVE: To create the secondary databases, the database files must be RESTORED WITH MOVE on each instance of SQL
Server that hosts a secondary replica.
Impact on add-file operations: A later add-file operation on the       primary replica might fail on the secondary databases. This
failure    could cause the secondary databases to be suspended.
This,in turn,    causes the secondary replicas to enter the NOT
SYNCHRONIZING state.


Oracle Databases - need to export nightly for backup/DR purposes

if your database is a serious production database, it should be running in archivelog mode, no question about that. This is assuming that users are constantly doing transactions and that you don't want to lose them. If your database is only bulk loaded once a day, an export dump might be a valid solution as backup, if you know how to use it to re-create a new database, once you lost the prod database.

For now, logon as a database administrator on your server and switch to archivelog mode

sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

database is open for production and you can make online backups. This does not mean your database is out of the danger zone, there are still a few things that need to be setup to keep it running without problems.

First thing that will happen is that the filesystem where your archivelog files are written will get filled up, causing a full halt of the database.

best advice: hire a dba to do the job for you, it is easier to make a disaster than to prevent, or even worse, to recover from one. Without the proper config recovery can be close to impossible. There are lots of consultancy firms that offer adhoc services via some pre-paid system. Maybe giving the application to a service provider might be smarter. This al depends on what you do and what kind of protection against disaster[s] you want.

second best: start reading the docs Oracle® Database 2 Day DBA


Table name with special character in the end

You can try the dump function to get the actual byte values stored. For example, I just created a table "OOUPS ", with a space at the end.

> select table_name, dump(table_name) from user_tables;
...
OOUPS   Typ=1 Len=6: 79,79,85,80,83,32
...

You can see 32 (0x20 hex) as the last char - that's an ASCII space.

Once you've found what the extra character is, you can query the table using double quotes around the name. This is case-sensitive, so make sure you match that too. e.g.:

create table "OOUPS " (a int);
insert into "OOUPS " (a) values (42);
commit;
select * from "OOUPS ";

And of course:

alter table "OOUPS " rename to more_sane_name;

If you can't type the strange character easily, simplest way would be to create an SQL script, or pipe the exact character sequence to SQL*Plus:

OOUPS   Typ=1 Len=6: 79,79,85,80,83,13

$ echo -e 'alter table "OOUPS\r" rename to saner_name;\nexit;' | sqlplus user/pass
...
Table altered.


It is possible to rollback wrong updates without any downtime using ApexSQL Log. For maximal UPDATE reconstruction, the database should be in the Full recovery model, and a full chain of transaction logs is needed.

A full chain of transaction logs starts with a full database backup and is followed by all subsequent transaction log backups up to a point of the rollback.

How to recover SQL Server data from accidental UPDATE and DELETE operations

How to recover SQL Server data from accidental updates without backups

Disclaimer: I work for ApexSQL as a Support engineer


Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios

Log shipping is not a backup scenario. It's a semi high availabiliy scenario.
For backups there's full, differential and transaction log backups.
They should all be used together. Your SLA dictates how you use them.
Most typical scenarios are full backup say at midnight, diff backup at noon and transaction log backups every 30 or 15 minutes.

And remember: you don't have a valid backup until you restore from it to test if it's ok.


Dropping a schedule whose name contain '.'

Double quoting works:

EXECUTE SYS.dbms_scheduler.drop_schedule('"MYSCHEMA."');


Schedule Job to Start at Different Steps

A schedule has no dependencies on a job. In fact, a schedule is a shared resource which can be used by several jobs.
There's no such thing as a "local" schedule. There's nothing stopping you, of course, from having only one job using a schedule.
I.e., there is no place in the Agent architecture for a schedule to kick off a certain job step.
So, yes, you are limited to having a schedule starting the job at the step defined in the job.
The way I read your scenario, there is nothing in common between the steps except that they share the same subsystem of your application (or however you would phrase it). I'd love for agent to have some namespace/folder functionality for jobs, but no. IMO, the closest you can come is to have a healthy naming standard for your jobs, using some prefix in the job name to identify which system each set of jobs belong to. And, of course, comments inside the job.


SQL filter only if each unique value has more than N records

Welcome.

Here is what you want. But please follow the notes given below too.

SELECT day,
       ip_address,
       user_name,
       timestamp,
       count(ip_address) AS tct
FROM my_table
WHERE day = current_date()
GROUP BY ip_address
HAVING tct> 10
ORDER BY ip_address ASC

Do not use select * instead give specific column names.
You are speaking of count per name in your question but your code groups by DAY, name, value - check if it is fine for you.
It is better (always) to list your data structure and the output needed along with your code to get best answer.


Is it possible to Increase storage performance in azure vm using disk striping?

I'm going to preface my answer by indicating I tried this out a few years ago during a demo of Azure's initial IaaS offerings so things have almost certainly changed.

Yes, you can provision multiple disks and create a spanned volume but I'd also check the segment size of the LUNs. If you're looking for high throughput to scan in tables like for building OLAP cubes on entire fact tables, be sure to indicate a high segment size like 256K. My first IaaS test was with the default 64k segment size better used with OLTP workloads and performance was negatively impacted as a result.


How to do a one-time log shrink on a database with transactional replication

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:

ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT

Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.

If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1


Is there an equivalent to OPENROWSET in Oracle?

Answer provided by John Eisbrener in a comment

The effective equivalent is a Database Link.  Sadly there's not an identical equivalent to what OPENROWSET does in one Oracle command or statement.  You have to declare the db link first before accessing it.  If you don't want to edit a tnsnames.ora file, you can create the dblink using an in-line statement, which allows you to programmatically emulate OPENROWSET functionality.


Create/Delete file using SQL Server 2008

I have got one very useful link which i have not tried. But, its a great stuff by author...


Necessity of backing up tail log when restoring transaction log?

At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?

No. If your transaction log backup ran at the top of the hour as expected, and you are certain you don't need any data later than that point, it is not necessary to take a tail of the log backup.


Do non-relevant columns affect query time of select statements?

It depends, on the table structure and the available indexes.

Case A: Common (rowstore) table, no index on (order_value).

The only possible execution plan is to read the whole table (which is of course much different when it's 2 vs 200 columns, so a few vs a few thousand bytes wide).
Case B: Common table, there is an index on (order_value) or some other indexes that include that column.

There is a better plan now, scan the whole index (one of them) - which is of course much more narrow than the whole table, just a few bytes. Which makes irrelevant if the table has 2 or 200 columns. Only the index is scanned.
Case C: It's a columnstore table.

As the name implies, the structure of these tables are column-wise oriented, not row-wise. There is no need for any index, the table design itself is suited for reading entire columns.


Storing overridable hierarchical settings

You don't give a lot of detail on your current structure, so I'm assuming each node in the hierarchy is in a simple table with a single parent relationship like so and the path being a string of ID like /<rootNodeID>/<GPNodeID>/<PNodeID>/<LeadNodeID> and that you intend to store the settings in a property bag arrangement rather than needing to update the DB structure when a new setting is added to the applcation logic, resulting insomething like this:

Nodes                 NodeSettings
=============         ============      Settings
NodeID   (PK) <--.--  NodeID  (FK)      =========
ParentID (FK) ---'    Name    (FK) ---> Name (PK)
FullPath              Value
OtherProp1
OtherProp2

In this arrangement you can pull all settings that have a value anywhere up the tree from a given location (with values closer like so:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 ns.value
                FROM   NodeSetting ns
                JOIN   Nodes n ON n.NodeID = ns.NodeID AND '<PathToTargetNode>' LIKE n.FullPath+'%'
                WHERE  ns.Name = s.Name
                ORDER BY LEN(n.FullPath) DESC
               )
FROM   Settings s

The use of a sub-query like this my not be efficient enough if you have many settings but whould be fine otherwise. Of more concern is using LIKE that way: it will not be able to properly use any index on Nodes.FullPath so an index scan (or worse, a table scan) could be performed for each call of the sub-query. Off the top of my head I can't think of a way to significantly optimise this as a single query without adding new structure to your storage of the relationships between nodes such as keeping map between ascendants and descendants like:

RelationshipDistance
=======================
Ascendant  (FK->NodeID)
Descendant (FK->NodeID)
Distance

(making sure to store the node's relationship with itself, as AscNode='NodeID', DescNode='NodeID', Distance=0). Then you can do:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 s2.value
                FROM   RelationshipDistance r
                JOIN   Settings s2 ON n.NodeID = s.NodeID AND s2.Name = s.Name
                WHERE  r.Descendant = '<IDOfNodeYouAreLookingFor>'
                ORDER BY r.Distance DESC
               )
FROM   Setting s

The disadvantage here is needing to maintain the extra structure (through triggers or work in another layer of your code).

Of course this is assuming that you want to return the settings from a single statement. You could always define a procedure that walks up the path collecting the settings in a temporary table or table variable, adding new settings found at each step towards the root - this is likely to be more efficient than a single statement version that causes index/table scans.


I have a query which does not return result with the desired alias names of columns in PostgreSQL

You have to define the aliases for the columns outside the subqueries:

with d as (
  select distinct emp.ahrims_id, rank.name as rank
  from hr_employee as emp, ana_rank as rank,
       unit_identification_code as uic, record_record as rec
  where emp.unit_identificatin_code_id = uic.id and
        rec.hr_employee_id = emp.id and
        rank.id = emp.rank_id and
        uic.code in (
          'Z00100','Z00200','Z00300','Z00400','Z00450','Z00500','Z00698','Z00600',
          'Z00699','Z00680','Z00700','Z00697','Z00800','Z00900','Z01000','Z01100',
          'Z01200','Z01300','Z39000','Z39012','Z10000','Z10005','Z10007','Z10009',
          'Z10100','Z10101','Z10200','Z10300','Z10301','ZACA97','ZOCG01','ZACA99',
          'ZACA98','ZOCG02','Z10400','Z10500','Z10600','Z10700','Z10800','Z11000',
          'Z36000','Z37000','Z20000')
  )
select (select count(*) from d where rank in ('MSG','SFC','SGM','SGT','SSG','G1','G2','G3','G4','G5')) as "GSG1 Military NCO",
       (select count(*) from d where rank in ('SDR','G6')) as "GSG1 Military SDR",
       (select count(*) from d where rank in ('Civ11','Civ12','Civ13','Civ14','Civ15')) as "GSG1 Civilian NCO",
       (select count(*) from d where rank in ('Civ16')) as "GSG1 Civilian SDR"


You appear to be missing the distinction between "Percona XtraDB" and "Percona XtraDB Cluster," also known as PXC.  XtraDB is Percona's compatible drop-in replacement rewrite of the InnoDB storage engine, which is included in Percona Server and MariaDB.

Percona XtraDB Cluster and MariaDB Galera Cluster both use XtraDB as their storage engine, but the important difference from standard MySQL replication is that they both use the Galera Replication Provider, which provides true synchronous replication among all the nodes.  You can also use Galera with MySQL.  Oracle may not mention that, since it wasn't their idea.

MySQL's built-in asynchronous replication can be configured for circular replication (and is included in Percona and MariaDB) but it has no mechanism for handling conflicts.  If queries make conflicting changes on different servers, your data will be inconsistent and replication will stop.  Galera resolves this by requiring all nodes to concur on each commit.  It's replication mechanism is fundamentally different than what is built in.

There are no issues with circular replication that are specific to MySQL 5.6 and 5.7 as your question implies.  These issues apply to all versions of MySQL, Percona, and MariaDB when standard replication is used in a circular configuration, if you allow writes to be done to more than one of the masters.

See http://galeracluster.com


Your question implies that you're using a Kimball Star Schema approach.  Option 1 implies a star schema while option 2 implies a more snowflaked design.  Kimball argues against snowflaking your design.

Therefore, go with option one.  It will be easier for your report writers long term and newer in memory database technologies will take advantage of it.  While you're implementing option one, switch to a surrogate key instead of the multi-field key you're concerned about creating.


I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.


When is the right time to use MariaDB instead of MySQL, and Why?

I think now is the time to play with MariaDB (just like now is the time to play with anything like this). Personally, I would hold off until I know a few more devs who have deployed MariaDB onto a live customer site before doing it myself.

Some situations could call for a change of application but something as serious as changing your database engine is not something that should be considered lightly.


These are the general rules for the data (mdf) and log (ldf) files:

Tune your mdf files for random read/write access in 64 KB chunks.
Tune your ldf files for sequential write access in 64 KB chunks.

Because the mdf files are accessed randomly, and the log files accessed sequentially, you can see substantial write performance drops if the logs are sharing disks with other data that's frequently accessed. This is largely because of the time lost waiting on the heads to seek around the disk. Obviously with an SSD you don't have this seek penalty, but the disk will still have a maximum write throughput to consider.

In a data warehouse/OLAP type of server, this may be less of an issue, as most day-to-day use will probably be read-only, and ETL batches will probably run during non-peak hours. The data files and log files likely won't compete with each other as much.

So, the moral of the story is if you're not happy with the write performance of your database, I'd recommend moving the logs to a dedicated volume with higher sequential write throughput. Often, this will mean using a RAID10 volume with traditional spinning disks (not SSDs). The more disks you use, the faster it will be. Don't use RAID5 for logs, as the write performance is generally quite bad, unless your array controller has a large enough cache to mask the effect.