How should I model a worflow of status/activities ? (rules like "if is 3 go to 4, if is 4 go to 5" etc)

What you have can work, given a few constraints:

You move between states in a linear fashion
You have multiple states with multiple activities.
You have only one consideration for what "color" the alert indicator should be.
You have three colors and only three colors (no more and no fewer).

Based on what you mentioned as requirements, that solution fits most of the constraints, but not #2.  There are a couple of things I would look at, though.

One thing I would question is whether you need that Activity table at all.  If it really is a 1:1 correspondence between states and activities, you could wrap it up into one table without loss:  your activity is just the action which pushes you to the next state.  If, however, there are multiple activities per state, this can make sense.  If you have an overarching status category (e.g., when the row is in "Vehicle Assigned" status, there are 5 separate activities which need to occur to get it to "Closed" status), then this general idea is alright.

So if you have one activity per state, the Status table would have the following attributes:

Name
Activity required to get to this state (if you, for example, have a web application and you want to populate the button text with a verb instead of a noun)
The next status ID (assuming this is a linear workflow)
GreenMax (the maximum number of minutes during which the row can be "green")
YellowMax (the maximum number of minutes during which the row can be "yellow")

RedMax is implied:  it's red whenever the difference between the current time and whichever time you want to use (DateOfRequirement, LastUpdate, etc.) is greater than YellowLimit.

Alternatively, if you have different categories for display such as "step 1 has red-yellow-green, but step 2 is just red-green and step 3 is red-pink-orange" (or if you believe that you will), I would move the colors out to their own table and create a Status-Color bridge table with status ID, color ID, and maximum time.  That makes retrieval a bit more complicated, though.

Now, if you do have multiple activities per status, then I would turn Status into a parent-child relationship with Activity, instead of a many-to-many relationship like you have now with Rules.  So Status would have Name, and Activity would look like:

Name
Status ID (to get the current status)
The next Activity ID (assuming this is a linear workflow)
GreenMax
YellowMax

And on your main object, you would actually hold the activity ID instead of the status ID, because you can derive the status from the activity.

The other thing I would do is drop the Rules table.  The only time that you would need a Rules table is if you have multiple statuses, multiple activities, and there are multiple relationships between these (for example, that both the New and Closed statuses both be able to perform the Assign Vehicle activity).  Based on your example, this does not appear to be the case.  It is possible that you actually do that many-to-many relationship between activities and statuses, if the relationship is not like your example.  In that event, your Rules table looks okay, with the caveat that you don't need the Red limit.

Incidentally, I highly recommend that you put the actual workflow logic in a stored procedure, whether you keep the linear flow (like you have now) or move to a non-linear workflow model.  You can use a combination of triggers and check constraints to force a workflow to operate in a particular manner, but it's a lot easier to understand if you just do it in a stored procedure:  all of the code is in one place not in an easy-to-forget area like a trigger.  Putting this in a stored procedure also abstracts it away from the application and business object layers, so they just need to call a method which says "go to the next workflow step" (and maybe pass in a detail to help you figure out which workflow step, in the event that this is not a linear process).

EDIT (post question update)

Based on your question update and comment, I'm okay with a three-table approach.  I'm not positive that you need it, but this could simplify things at the application level.

At this point, what you have is a state machine, with a State (status) table and a Transition (activity) table.  Your rule table then defines which transitions are valid for a particular state.

I would change the Rule table slightly, to look like this:

Surrogate key
Rule Name
Starting State ID
Transition (Activity ID)
Ending State ID
Green Limit
Yellow Limit

There is one minor change:  the position of the different keys.  Your rule table now reads like a book:  starting from one state, perform a transition and end at a different state (or the same one--you have an example in which the final state is the same as the initial state).

You would have a unique key constraint on starting state ID and activity ID, to guarantee that a particular transition from a particular state always ends in a single state.

On your vehicle table, I would put idRule instead of idStatus, to make it easy to tell which set of limits you need.  You might also want a history table showing the vehicle ID, rule ID, and time added, so you can track what happened in the workflow.

This means that your "go to the next workflow step" stored procedure is actually pretty simple:  given a vehicle ID and activity to perform, you can do a quick select against the Rule table to see if there is a valid transition from the vehicle's rule's state via the activity ID you get in the proc.  If there is, perform that transition:  update the vehicle table accordingly (setting idRule and LastUpdate); if not, return an error indicating that something must have gone wrong.

There is also no problem with having a table with two foreign keys back to another table.  idCurrentState and idNextState can both be legitimate foreign keys back to State.  You just need different names for the two foreign keys (e.g., FK_Rule_State_CurrentState and FK_Rule_State_NextState).


YOUR SAMPLE DATA

DROP DATABASE IF EXIST johnc;
CREATE DATABASE johnc;
USE johnc
CREATE TABLE scores
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    points INT NOT NULL,
    PRIMARY KEY (id),
    KEY (city)
);
INSERT INTO scores (name,city,score,points) VALUES
('John', 'Boston', 127, 6),
('Mark', 'Boston', 120, 5),
('Paul', 'Newark', 110, 4),
('Carl', 'Boston', 105, 3),
('Matt', 'Newark', 101, 2);
select * from scores;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS johnc;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE johnc;
Query OK, 1 row affected (0.01 sec)

mysql> USE johnc
Database changed
mysql> CREATE TABLE scores
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     city VARCHAR(20) NOT NULL,
    ->     score INT NOT NULL,
    ->     points INT NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY (city)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO scores (name,city,score,points) VALUES
    -> ('John', 'Boston', 127, 6),
    -> ('Mark', 'Boston', 120, 5),
    -> ('Paul', 'Newark', 110, 4),
    -> ('Carl', 'Boston', 105, 3),
    -> ('Matt', 'Newark', 101, 2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

TABLE CONTENTS

mysql> select * from scores;
+----+------+--------+-------+--------+
| id | name | city   | score | points |
+----+------+--------+-------+--------+
|  1 | John | Boston |   127 |      6 |
|  2 | Mark | Boston |   120 |      5 |
|  3 | Paul | Newark |   110 |      4 |
|  4 | Carl | Boston |   105 |      3 |
|  5 | Matt | Newark |   101 |      2 |
+----+------+--------+-------+--------+
5 rows in set (0.00 sec)

mysql>

PROPOSED QUERY

SET @x1=0;
SET @x2=0;
SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
FROM
(SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
LEFT JOIN
(SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
USING (k);

PROPOSED QUERY EXECUTED

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM
    -> (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
    -> LEFT JOIN
    -> (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
    -> USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| John | 127 | 6 | 4 | Paul | 110 |
| Mark | 120 | 5 | 2 | Matt | 101 |
| Carl | 105 | 3 |  |  |          |
+---------------------------------+
3 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2016-06-23 18:08 EDT

PROPOSED QUERY #2

SET @x1=0;
SET @x2=0;
SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
(SELECT COUNT(1) x FROM scores WHERE city='Boston' UNION
SELECT COUNT(1) FROM scores WHERE city='Newark') AAA) AA JOIN
(SELECT h*100+t*10+u+1 k FROM
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
WHERE k <= maxnum) k
LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A USING (k)
LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B USING (k);

EXECUTED WITH Boston/Newark

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
    -> (SELECT COUNT(1) x FROM scores WHERE city='Boston' UNION
    -> SELECT COUNT(1) FROM scores WHERE city='Newark') AAA) AA JOIN
    -> (SELECT h*100+t*10+u+1 k FROM
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
    -> WHERE k <= maxnum) k
    -> LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A USING (k)
    -> LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| John | 127 | 6 | 4 | Paul | 110 |
| Mark | 120 | 5 | 2 | Matt | 101 |
| Carl | 105 | 3 |  |  |          |
+---------------------------------+
3 rows in set (0.01 sec)

mysql>

EXECUTED WITH Newark/Boston

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
    -> (SELECT COUNT(1) x FROM scores WHERE city='Newark' UNION
    -> SELECT COUNT(1) FROM scores WHERE city='Boston') AAA) AA JOIN
    -> (SELECT h*100+t*10+u+1 k FROM
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
    -> WHERE k <= maxnum) k
    -> LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Newark' ORDER BY id) A USING (k)
    -> LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Boston' ORDER BY id) B USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| Paul | 110 | 4 | 6 | John | 127 |
| Matt | 101 | 2 | 5 | Mark | 120 |
|  |  |  | 3 | Carl | 105         |
+---------------------------------+
3 rows in set (0.00 sec)

mysql>

SECRET SAUCE OF QUERY #2

The subquery k generated a numbered sequence 1..N where N is the maximum number distinct city entries. Since Boston appear three times and Newark only 2 times, subquery k yields the sequence 1..3.

I then LEFT JOIN that sequence to both subquery A (Boston) and subquery B (Newark). As shown, reversing the city names reverses the output.


inserting data from one table to another after casting the values

I would be writing some code to do this, I'm not sure whether it would be achievable using purely SQL. You'd need to dynamically create a (or some) SQL statements, which may be a question for the chaps over in the programming section, however:

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename' AND COLUMN_NAME = 'columnname'

Would give you the datatype; you could then select the data from your other table and cast it to the datatype, you can also pull column_name from information_schema but without a table name.. That's another question!


Compatibility level of databases based on sql server version

As mentioned in comment, you need to query sys.databases to get the relevant information.

Below would be straight forward query:

select name from sys.databases
where compatibility_level not in(130) -- you can hardcode this based on version of SQL server
and database_id > 4

If you want to generalize it then, you may use below query:

select name from sys.databases
where compatibility_level not in(select compatibility_level from sys.databases where database_id = 1)
and database_id > 4

Above would work fine except in scenario where DB upgrade(in-place) has taken place or if you are working on Azure. You can read about it more here.

Hope above helps.


If you want to create a user that can admin any of the pluggable databases, prefix the username with C##.

For example:

create user c##name identified by password;


Can we change database owner from [NT SERVICE\SQLSERVERAGENT] to [sa]

Answering your first question

  Can we change the owner of that particular database to [sa]?

Yes, you can change the owner of any database using the ALTER AUTHORIZATION ON DATABASE ... command.

ALTER AUTHORIZATION ON DATABASE::Test_db_repli TO sa;

Answering your second question

  If changed, will there be any problems in the future?

This is something we cannot answer, because we don't know your environment. There might have been a reason in the past to run the database using the NT SERVICE\SQLSERVERAGENT account. Providing additional details on what the database was used for could give the community an idea why the database was set up to have that specific account defined as database owner.


Ubuntu Multiple MySQL instances and Apparmor

I ran into apparmor headaches getting multiple instances going on ubuntu myself.  Your configs look legit but I'm not super familiar w/ apparmor to spot a subtle problem if one lied there.  In my case I was just running multiple copies of the same binaries w/ different cnfs

Your initial error cited

name="/run/mysqld/mysqld-5-5-20.sock"

It seems maybe there's a config option that didn't come through in what you pasted that's looking to work with /run (instead of /var/run) ?


Assign root user password after installation

The problem is with incompatible data files created by a different version installed in the past. The errors show the old datadir was left from an old installation that is not compatible with 5.7.

If your database does not contain any useful data yet, simplest way to fix it is to clear the datadir and let the new version initialize it fresh.

Uninstall 5.7, purge the datadir, and then reinstall. Normal uninstall won't clean it, gimp does not delete your images on uninstall either. You need to delete and reinitialize the datadir, not the application packages. It might be at /var/lib/mysql/ (the default).

If you already have some data in the database you want to keep, you should instead follow the instructions for upgrades.


How to Determine Optimal Disk Cluster Size for Firebird 2.5

A good question to ask here would be: are you using the server for OLTP or OLAP type queries?

If you are using OLTP and your queries are carefully crafted to retrieve and modify only one record at a time, then use a 4kb block size (I'm assuming that in OLTP, you don't have any records with record sizes in excess of 4kb?).

Personally, I would go with what it says here. This article recommends a 4kb block size for Interbase systems - I know they're not the same, but they are "kissing cousins..." :-). Unless you have a compelling reason not to, then choose 4kb.

Despite this, by far the most important thing in this article is what it says at the end:

  Although 4KB seems to be the best page size for most databases, the
  optimal size depends on the structure of the specific metadata and the
  way in which applications access the data. For this reason, you should
  not consider the 4KB page size guideline to be a magic value. Instead,
  you should perform testing with your application and database under
  several different page sizes to analyze which configuration gives the
  best performance.

This is a very nicely phrased, technical way of saying YMMV, make sure and kick the tyres before you buy! FWIW, you could also look at this article - it popped up! BTW, not using Firebird 3? Check what the article says about 3.

Your question got me interested - unfortunately, there aren't many Firebird benchmarks on the internet - but I did find this about Postgresql. Again, not the same thing, but it is an MVCC system and its numbers might indicative?


Rather than try to guess, you should download Percona Tools.

You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it:

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

If you not sure, look at the content of the index removal SQL script first

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
vi -R ${ETL_INDX_SCRIPT}

If the resulting script has no ALTER TABLE commands, you don't need anything done.

If there are ALTER TABLE commands and if you are ready, run the script with this

mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

GIVE IT A TRY !!!


I think there's a lot of wrong information about the query cache out there.

The best case for the query cache, is when you have to examine a very large number of rows, but only return a few to a client.  A typical situation where this is to be common, is a system where no proper optimization or indexing has been applied.

In a situation where many of the queries are primary key lookups, or otherwise very well optimized, the query cache can cause negative scalability.  Yes: it makes things worse!

The reason for this, is that the design adds some internal locking, which limits your MySQL server from scaling on multi-core machines.

The query cache is a cause for many "sudden stalls" in MySQL - not all of them obvious.  In Percona Server, we added a new state to the processlist (Waiting on Qcache mutex):
http://www.percona.com/docs/wiki/percona-server:features:status_wait_query_cache_mutex

(Disclaimer, I work for Percona.)


Hundreds of millions of rows: Index by composite BIGINT, VARCHAR, or CHAR of HASH?

There is no advantage in any form of PARTITIONing based on what you have said.

The simplest, most obvious, index is also the best:  Have the PRIMARY KEY be the email address (if it is unique), or start with the email address plus something to make it unique.

This is possible

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT -- for uniqueness
...
PRIMARY KEY(email_addr, id)  -- id tacked on for uniqueness
INDEX(id)  -- to keep A_I happy.

What makes you think that "one huge table" is a problem?  No, PARTITION BY HASH does not buy you anything.

You said "shard" and mentioned 4096 buckets.  Technically that means you have 4096 servers to put parts of the data on.  I assume you really meant PARTITION or (yuck) 4096 manually-handled tables, which is limited to a single MySQL instance.

If you really did mean spread across multiple servers, then, sure, the bottom chunk of the SHA256 is a good idea.  MD5 is probably faster and just as good for this purpose.  I would then use a dictionary lookup with 4096 elements to point to however many servers you currently have.


Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db...

Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop and don't recreate the table, and try to enable IDENTITY_INSERT on another table? What happens then?


How can I calculate future interest charges and loan balance

Your sample data script has an error, it uses 4.2 and 5.1 instead of 4.29 and 1.54.
This threw off the numbers so please take care next time to make sure you get it right...
Also, after the edit the desired result is missing.
It is unclear whether the payment is from the 'current' row or the previous one, and in either case, for the first customer the balance drops and then increases even though the payment is more than the interest... Please check that as well.

Here is my solution, and while there is still some variance from your original desired result which I believe is wrong, this should get you on the right path so you can adjust it to fit your needs.
I chose to use ARN.Payment in the balance calculation in the recursive part of the CTE for the 'current row', change that to 'RI.Payment' if you want to use the 'previous row' payment. It is impossible to tell from your original table which one you meant.

The solution adds row numbers per account to allow the following recursive CTE to calculate the interest and payments row by row.

WITH Added_Row_Numbers AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Account_Number ORDER BY LastDateOfMonth) AS Rn
FROM   #Test
)
, Recursive_Interest AS
(
SELECT *
FROM   Added_Row_Numbers
WHERE  Rn =1
UNION ALL
SELECT ARN.Account_Number,
       ARN.Interest_Rate,
       ARN.LastDateOfMonth,
       RI.Capital_Balance_Calculated + (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) - RI.Payment
       AS Capital_Balance_Calculated,
       (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) AS Interest_Paid,
       ARN.Payment,
       ARN.Rn
FROM   Recursive_Interest AS RI
       INNER JOIN
       Added_Row_Numbers AS ARN
       ON ARN.Rn = RI.Rn + 1
          AND
          RI.Account_Number = ARN.Account_Number
)
SELECT RI.Account_Number,
       RI.Interest_Rate,
       RI.LastDateOfMonth,
       RI.Capital_Balance_Calculated,
       RI.Interest,
       RI.Payment
FROM   Recursive_Interest AS RI
ORDER BY Account_Number, LastDateOfMonth

Here is the DBFiddle script.

HTH


Block SSRS Developers from embedding SQL Statements in Report

create a database schema (lets say "reporting"), and add only the stored procedures you want the developer to have access to that schema, grant the allowed users access only to that schema.


When are COLUMN aliases in FROM clauses needed?

If the columns don't exist (VALUES clause)

PostgreSQL gives them default names, but that's besides the point. You can't alias them in a COLUMN list that doesn't exist.

SELECT t.*
FROM (VALUES ('row1',1), ('row2',2)) AS t;

That would be a great example of the VALUES LIST syntax which requires you to alias in FROM list.

SELECT t.*
FROM (VALUES ('row1',1), ('row2',2)) AS t(english,rownum);

From there you can even use the alias in the select list.

SELECT rownum
FROM (VALUES ('row1',1), ('row2',2)) AS t(english,rownum);

And, that brings us to a complex example like this one found on page 190 of PostGIS in Action, Second Edition.

SELECT
  oid --**only possible because of FROM ALIASING.**
  , lowrite(lo_open(oid, 131072), img) As num_bytes
FROM (
  VALUES (
    lo_create(0),
    (
      SELECT
        ST_AsGDALRaster(
          ST_Band(rast,1)
          , 'USGSDEM'
          , ARRAY[
            'PRODUCER=' || quote_literal('postgis_in_action')
            , 'INTERNALNAME=' || quote_literal(rast_name)
          ]
        ) As dem
      FROM ch07.bag_o_rasters
      WHERE rast_name = 'Raster 1 band heatmap'
    )
  )
) As v(oid,img); --**FROM ALIASING**

If that's hard to swallow there is one other awkward construct there and that's a SELECT as a COLUMN.

SELECT ( SELECT 1 ), ( SELECT 2 ); -- one row "1,2"

SELECT lo_create(0), ( SELECT 2 ); -- one row "oid,2"

lo_create returns an oid. That's beyond the scope of this answer though.

For another real world example, see this question I just answered.

In a SELF JOIN

This may be worth mentioning since it is in the docs,

SELECT * FROM temp;
 foo |  bar
-----+--------
   1 | evan
   2 | dba.se
(2 rows)

And you self-join you'll get two bar columns.

SELECT * FROM temp JOIN temp AS t2 USING (foo);
 foo |  bar   |  bar
-----+--------+--------
   1 | evan   | evan
   2 | dba.se | dba.se
(2 rows)

But, with FROM aliasing you can label them separately,

SELECT * FROM temp JOIN temp AS t2(foo,bar2) USING (foo);
 foo |  bar   |  bar2
-----+--------+--------
   1 | evan   | evan
   2 | dba.se | dba.se
(2 rows)

However, that's not really better than the COLUMN-aliasing method,

SELECT foo, temp.bar, t2.bar AS bar2
FROM temp JOIN temp AS t2
  USING (foo);

The only difference is that uniquely in the FROM-aliasing method you refer to all instance of t2.bar as bar2.


Are newly inserted data automatically stored in InnoDB buffer pool

Yes and no. Yes, buffer pool will contain the recently inserted data. No, the buffer pool doesn't contain SELECT statement output, that is what the query cache does, but it is generally harmful, deprecated and removes in MySQL 8.0. The buffer pool contains InnoDB pages (both index and data).


PostgreSQL is running locally but I cannot connect. Why?

I found that I had an extremely similar problem, namely that postgres was opening a socket in /var/pgsql_socket_alt where none of my software expects to look, but the solution to my problem was not only a problem with my $PATH.

I had to create the directory /var/pgsql_socket, chown it to myself, and set unix_socket_directory in postgresql.conf (located in /usr/local/var/postgres) to that directory, then use the pg_ctl binary in /usr/local/bin to start the right postgres server successfully (which is where $PATH comes in – make sure which pg_ctl resolves to /usr/local/bin/pg_ctl, or just always call it explicitly).

This might help other users who find this question via the /var/pgsql_socket_alt mention.


stored procedure vs. function in postgresql and which is better for executing dml statements

There is no such thing as a stored procedure in PostgreSQL. All PostgreSQL has are SQL-callable functions defined in a number of languages - LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plperl, etc.

Additionally, there's no RENAME TABLE command. You want ALTER TABLE ... RENAME TO ....

This particular example must be written in PL/PgSQL using EXECUTE because it relies on dynamic SQL. See the numerous existing examples on Stack Overflow of using PL/pgSQL EXECUTE. Something like a PL/PgSQL function with:

EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
    'FOOBAR_'||_schema_id, 'table1_org',
    'FOOBAR_'||_schema_id, 'table1_old');

Edit: OK, more complete example:

CREATE OR REPLACE FUNCTION myfunc(_schema_id integer)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
        'FOOBAR_'||_schema_id, 'table1_org',
        'FOOBAR_'||_schema_id, 'table1_old');

    -- Now TRUNCATE the table and do your second `RENAME`
    -- in subsequent EXECUTE statements.
END;
$$;


Re-adding constraint after ALTER fails on row that exists

If you get that message although the image_id is present in mcqueen_base_image, you must be dealing with data corruption.

Try to

REINDEX mcqueen_base_image;

and see if the problem persists.

If there are still problems, you'll have to repair the data manually. Then

dump and restore the database to a new cluster to get rid of any lingering data corruption.
investigate the cause of the corruption.


What is the purpose of the "territory" configuration parameter?

"Territory" is essentially equivalent to country code or locale. This setting affects default behaviour of the database when handling the date format, as well as the default SYSTEM collation (sorting) in some locales. Note that the client locale overrides this, so, when connected to a database with territory ru from a client with a US locale, the default date format will be taken from the client setting.

It is also used by federation to determine the necessary conversions when retrieving data from remote servers with different territory (locale) settings.


Im trying to group two queries by year so that it will show the amount of tours done(by count of tour id) in that year

Assuming [Date] contains "year" values (i.e. 2018, 2017, 2016, ..)

;With SchoolTours AS
(
SELECT [Date] AS YEAR, COUNT(ID) AS [SchoolCountOfTour ID]
FROM [school tours done showing year]
GROUP BY [Date]
)
, VisitorTours AS
(
SELECT [Date] AS YEAR, COUNT(ID) AS [VisitorCountOfTour ID]
FROM [Visitor Tours Done Showing Year]
GROUP BY [Date]
)
SELECT t1.Year
, t1.[SchoolCountOfTour ID]
, t2.[VisitorCountOfTour ID]
FROM SchoolTours t1
LEFT JOIN VisitorTours t2 ON t2.Year = t1.Year

Plenty of other ways to do it but I'm partial to CTEs

B


You should be fine as sp_blitz will highlight if jobs are owned by users since if their login is disabled or not available due to Active Directory problems, the job will stop working.

you can create your skip table and specify that to the sp_blitz @SkipChecksTable to skip checks.

BTW, sp_blitz is on github and you can view the code as well.


Mysterious conditional where clause index choices

What you're seeing is constant folding.

More info at this link:

  Constant folding is a technique the optimizer uses to remove any unnecessary code to help improve performance. Constant Folding does this by removing unnecessary variables and simplifying the query before compiling the plan.

OPTION (RECOMPILE) allows constant folding of @Step and @FindNo in your second query. Since @Step is known to equal 1, the line OR (@Step = 2 AND CustNo2 = @FindNo) will be optimized away. Notice that this affects estimated rows.

If you want to see a more drastic example of this, try the below code. Notice how constant folding (enabled by option recompile) allows the second query plan to completely avoid the union, since the optimizer knows no rows could be returned.

CREATE TABLE #t (
id int)

INSERT #t
VALUES (1)

DECLARE @v char(5) = 'false'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'
OPTION (RECOMPILE)


What happens when CREATE INDEX gets interrupted (manually or accidentally)?

Index creation is done within a transaction.  If you interrupt it, SQL Server will rollback the transaction.  This might take a while, but it's harmless.  Of course, there are locks held while the transaction is active so this can impact your server's performance.

If you lose power in the middle, SQL Server will automatically rollback the partially completed transaction when the server boots.  Again, there's a performance hit but there's no harm done to your data.

http://technet.microsoft.com/en-us/library/ms188317(v=sql.105).aspx

http://technet.microsoft.com/en-us/library/ms190925.aspx

http://technet.microsoft.com/en-us/library/aa224747(v=sql.80).aspx


PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

You can drop the table (and dependent DB objects) with the pg_dump parameter --clean, from the pg_dump manual:

  -c, --clean
     Output commands to clean (drop) database objects prior to
     outputting the commands for creating them. (Restore might generate
     some harmless errors.)

So in your case this should be

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 --clean -t my_table myDB > D:\backup\myFile.sql

you should then be able to restore the dump file with your current restore command in the cron job.

Hope that helps.


How big of an impact does NUMA really have?

Jonathan Kehayias did a good blog post on NUMA at SQLSkills.com.

I believe server wise most of the vendors out there note if they are NUMA capable servers or not. From reading Jonathan's post it seems the focus is on the motherboard and processor capabilities. Then I would imagine how they interconnect would be an important thing to note as well, since that would be the bottleneck in most cases.

I have not really seen anyone write up on when NUMA becomes beneficial. I think it would just be a analysis on what your system is going to need. If one server maxed out to the nines is not going to cut it for the workload, then look at the alternatives.


How to do a ranked search based on number of multiple columns matched

In general:
SELECT *, (  (phone = :phone) * 4
           + (email = :email) * 2
           + (first_name = :first_name AND last_name = :last_name)) AS score
FROM customers
-- HAVING score
ORDER BY score DESC limit 10;

fiddle


I am Trying to Write an Access Query to Get a person having a First Name and Last Name but not all People having any of those names

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer.  In pseudo-code, though, you can use something of this form:

WHERE
    (   (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value)
     OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>))
AND (   (Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value)
     OR (Forms![Search_frm]![LastName_tbx].Value = <an empty value>))
AND (Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value)
     OR (Forms![Search_frm]![Last4_tbx].Value = <an empty value>))

In the above replace <an empty value> with whatever Access delivers from your search field when you don't type anything there explicitly.

To force the user to enter at least one search field add another AND to the query (again, pseudocode):

AND (FirstName Is Not NULL OR LastName Is Not NUll OR Last4 Is Not Null)


Non-Unique Clustered Indexes Administration

This one is hard to answer without knowing your query patterns, but you will most definitely cause side effects when dropping all clustered indexes and replacing them with something else.

I also don't think you are necessarily correct in assuming that having a non-unique clustered index is sloppy table design or by definition sub-optimal.

When you add a non-unique clustered index, SQL Server will add a 4-byte uniqueifier  to the index to make sure the key is unique if there are duplicate values in the key. Calculating this uniqueifier adds a bit of overhead but nothing too dramatic. This uniqueifier also has to be in the non-clustered index since the index bookmark has to point to a single row.

On the other hand, the example you give could be a good clustered index depending on the situation:

  For example, a table named Transaction will have a Clustered Index
  with the following keys: date, is_deleted. Also, the table will have a
  Non-Clustered Primary key on id column.

This could be a very good design decision if there are a lot of transactions on the same date, and almost every query is a  SELECT * FROM transactiontable WHERE date = <somedate> AND is_deleted=0.

A query like that could hurt badly if it has to use a non-clustered index and resort to key lookups.

It's impossible to say what side effects you will trigger, but you'll surely trigger some, and your reasons for this endeavour might be the wrong ones.

If you insist on doing this, make sure you test it first in a testing environment, and if you do you should be able to tell if this method works and if it will cause fragmentation or not (but that will probably be the least of your worries).


Is this a valid query that could allow to detect whether compression is supported or not?

That syntax is for Hybrid Columnar Compression. HCC is a feature that requires special hardware, like Exadata, Pillar Axiom storage, or Oracle ZFS Storage Appliance.
You can not do this on any database and certainly not in most of them. Instead of the above you can use  this for basic or advanced compression:
CREATE TABLE D ROW STORE COMPRESS BASIC AS SELECT * FROM dual;
CREATE TABLE D ROW STORE COMPRESS ADVANCED AS SELECT * FROM dual;

If you want to check the availability of compression options, use this:
SQL> select parameter, value from v$option
     where parameter in ('Advanced Compression', 'Basic Compression');

PARAMETER            VALUE
-------------------- -----
Basic Compression    TRUE
Advanced Compression TRUE

The values will be FALSE in Standard Edition.
Keep in mind, Advanced Compression is an extra cost option and despite this, it still can not be properly disabled, so the above does not mean you are licensed to use it, it just means th option is available.


Listing SQL Server instance startup parameters

In SQL Server 2008 R2 SP1 or later, this is made considerably easier via the sys.dm_server_registry DMV:

SELECT
    DSR.registry_key,
    DSR.value_name,
    DSR.value_data
FROM sys.dm_server_registry AS DSR
WHERE
    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

From: An easier way to get SQL Server startup parameters


Stored procedure that accepts an id as a parameter - how to execute for multiple ids list?

If you can pass in the string `"1,22,443", then you construct the query:

SET @sql := CONCAT("... IN (", f22, ")...");

then PREPARE it and EXECUTE.

Not clean, but it should work.

If there is only one element to send in, the IN will still work fine.  (An empty string would lead to a syntax error.)


Decomposition In Boyce-Codd Normal Form may lose relations?

Your decomposition is not correct, since in R2 you still have dependencies that violates the BCNF, for instance UtilisateurID → Nom (UtilisateurID is not a key of that relation).

The problem is that your algorithm is not correct. When you find a dependency X → A that violates the BCNF, you should decompose a relation in two relations, the first with X+, not XA, and the second one with T – X+ + X. Then you should repeat the algorithm, if you find in one of the two decomposed relation some other dependency that violates the BCNF.

So, in your example, a correct decomposition is:

R2 < (AdresseEmail ServeurMail UtilisateurID) ,
{ AdresseEmail → UtilisateurID
AdresseEmail → ServeurMail } >

R3 < (Nom Prenom UtilisateurID) ,
{ UtilisateurID → Nom
UtilisateurID → Prenom } >

R4 < (Login Passwd ServeurMail UtilisateurID) ,
{ ServeurMail UtilisateurID → Login
ServeurMail UtilisateurID → Passwd } >

Note that this decomposition preserves the functional dependencies.

Addition

How the decomposition is obtained? Starting from the original relation:

R(AdresseEmail Login Nom Passwd Prenom ServeurMail UtilisateurID)

let's consider a dependency that does violates the BCNF, for instance:

ServeurMail UtilisateurID → Login

the closure of ServeurMail UtilisateurID is (Login Nom Passwd Prenom ServeurMail UtilisateurID, so we decompone initially in two relations:

R1(Login Nom Passwd Prenom ServeurMail UtilisateurID)
R2(AdresseEmail ServeurMail UtilisateurID)

R1 is not in BCNF, since the key is ServeurMail UtilisateurID, so for instance the dependency UtilisateurID → Nom violates the normal form. Applying the algorithm, R1 is decomposed in:

R3(Nom Prenom UtilisateurID)
R4(Login Passwd ServeurMail UtilisateurID)

Both relations are in BCNF, and the final decomposition is given by R2, R3, and R4.

Finally, note that sometimes the algorithm can produce different solutions, depending on the functional dependency chosen at each step.


SQL Server replication service in Amazon RDS

Found the answer in the Amazon documentation. The replication feature is currently not supported.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html


What is the purpose of having a table NOT LOGGED INITIALLY

The classic real-world example is where the table I am creating is a copy from a static external source.  If the DB fails during the initial load, it will be faster to just drop the partially loaded table and start my import operation over from the beginning, than to suffer the overhead of fully logging the transaction.

For example, I am loading my reporting environment with a list of customers from my transactional environment's backup file.


MYSQL REPLACE string content with field value

String literals must be enclosed by quotes '.

Quote symbols within a literal must be quoted by slash as \'.

SELECT REPLACE(html_input,'!value',field_value)
FROM table
/* WHERE html_input = '<input type=\'text\' value=\'!value\'>'
     AND field_value = 'test';
*/


Excessive amount of data received from server

Use of TOP without an ORDER BY means you cannot guarantee which rows are returned.  The result may be different across servers, or even on the same server at different times of day.


Increase the time to live of a cached plan

SQL Server doesn't evict plans from the cache unless there's some sort of memory pressure, generally speaking.  Have a look here for more information:

Plan Cache Internals

This is a little out of date, but the same principles apply.


Performance Counters for SQL Server Related to iSCSI Disk Access

I agree with Mr. Denny.

When analyzing disk performance issues I would look into this counters.

Avg. Disk sec/Read - is the average time, in seconds, of a read of data from the disk.

Avg. Disk sec/Write - is the average time, in seconds, of a write of data to the disk.

Ideally you would be looking less than 15ms latency but in reality I have seen it as high as 30ms in Tier II storage.

Another counter is Avg. Disk Queue Length and you want see that number lower if possible.


If you are running MySQL8.0, you could drop the column position, and create a view like this:

CREATE VIEW view_amariadbtable AS
SELECT
  id,
  `key`,
  year,
  RANK() OVER (PARTITION BY year ORDER BY year DESC,id DESC) as position
FROM amariadbtable

BTW, you should NOT use reserved words as column name (key)


SSRS 2016 Report Portal - KPI option no available in drop down

KPIs are an Enterprise feature. They are only available in the Enterprise and Developer Editions. https://msdn.microsoft.com/en-us/library/mt742500.aspx

You will also need to do two things in order to have KPIs show up in the "New" dropdown.

You must have Content Manager or Publisher privileges in the folder you will be creating the KPIs in.
You must have at least Browse privileges to a shared data source and shared data set.  These do not have to be in the same folder as where you want to publish the KPI.

Keep in mind local admin isn't a privilege/role. I set up my own SSRS 2016 instance. But I still needed to add my self(and the admin group) to the Content Manager role.


Help with understanding programming time/difficulty - SQL Data dump vs SSIS Packaged report

I've been working with SQL Server for about 17 years. If I need to extract data on a repeating basis, I will almost always do it with an SSIS package.

At the risk of ticking off your SQL guys:

SSIS packages can be executed on a schedule (either using SQL Server jobs, or Windows scheduler if necessary). they are my first choice for automated data extraction.
SSIS can output data to CSV files, Excel files, or can even move it directly to other databases.
SSIS packages can be configurable, allowing changes to certain parameters without having to change the actual package.
For that matter, SSIS package files (*.dtsx) are actually XML files, allowing for certain changes to be made with a text editor (like, changing the name of a variable everywhere in the package); not that this is a feature that would be likely to help you, but....

Obviously, I can't tell you why adding a few additional fields to an existing export routine is outside their capacity right now. In most cases, I wouldn't expect adding a few additional fields to an existing extract process to be a major issue or to take much time. However, if (due to the nature of the extract process, or of the additional fields) a change to the format of the data were involved, or if the data required special handling (for example, a "notes" field with embedded tabs, carriage return, or newline characters), more time and effort might be required than you would expect.

It's possible that the problem is less the complexity of the change you need, than the amount of work already on their plate, and its priority level. If letting you get the data for yourself lets them stay on task, then it may be viewed as an acceptable compromise.

You mentioned that the extract you had been using came from the "front end" of the database. Generally, that's some sort of application (whether a compiled .exe, a script of some sort, or something web-based). Modifying an application does take time, and usually requires some degree of testing beyond just the immediate area of change (to make sure that none of the new code inadvertently changes anything outside that area, causing something to break).

If other development work on the application is under way, it can be difficult and time-consuming to bypass that to come out with a minor update to change a report. An SSIS package, being a standalone entity, is less likely to break anything else (as long as you aren't changing data), and thus can be developed and stood up without interrupting or interfering with other work.

NOTE: I'm interpreting the difference between a "data dump" and a "report" as being a matter of formatting. There are some things you can do to (exporting to a preformatted Excel template) that allow a certain amount of formatting, and some more complicated things (adding the appropriate HTML tags to your data to actually create an HTML file, instead of a CSV file or some such). However, it may often be easier (and faster, in the short- and even mid-term) to apply formatting after the fact.

Another NOTE: While this may have improved in later versions of SSIS, there are some issues when working with Excel files. These tend to be worst when importing data from them, but some of the issues (default limit of 255 characters per cell (unless one of the first eight rows has more than that), for example) do apply to exports. I've always tried to keep my life simpler by using .CSV files instead of actual .XLS files, for input or output.

For a simple one-off data extract, I would probably put a query together in SQL Server Management Studio, and copy the results to a spreadsheet of save them to a CSV file. For anything that will be repeating on a regular basis, I'll turn it into an SSIS package.  Either way, I'm hitting "live" data.

It's also possible to run an SSIS package from the command line (dtexec.exe), or with a simple, direct user interface (dtexecui.exe), rather than having to open it up in SQL Server Data Tools (or Business Intelligence Development Studio, depending on your SQL version). So, an SSIS package can be scheduled or "on demand". Whether the data is live or not depends on the database you're querying, not on SSIS itself.


Filtering previous month data with case statement

Try to change the where clause to the following:

where DT_CURRENT_DT >= trunc (sysdate, 'MM') - interval '1' month
  and DT_CURRENT_DT < trunc (sysdate, 'MM')
  and (
       trunc(sysdate, 'MM') != to_date('01-NOV-2018')
       or extract(day from DT_CURRENT_DT) >= 15
      );


Will this trigger significantly degrade performance?

The best way to evaluate performance is by measuring it- you are the only person that can evaluate that on the real application, or, much better, on a staging environment with a copy of the traffic or an emulation of such.

Hardware, other code running, throughput, how slow are those new queries, amount of data you have, etc. will tell you what you want to know- to the point that the same code can make it or break it under different circumstances.

Having said that, if you just want a high level opinion of the idea will work, without knowing much about your system- I would personally would do this kind of analysis asynchronously based on external logs (or your login_att table), as normally you don't want to punish your normal users if the occurrences are not frequent enough. Also triggers are not my favorite way to handle application logic. However, if you cannot modify the source code, it doesn't add much to the total latency, and you test it and it seem acceptable, go on- but please test it doesn't break your application when there are thousands of parallel attempts per second due to the extra writes.


So you are looking for Employees that earn below the average in their current department but above the average in their prospective new department.

One possible way of getting all employee transfers that would meet this would be

WITH departments
     AS (SELECT AVG(salary) AS AvgSalary,
                department_id
         FROM   employees
         GROUP  BY department_id)
SELECT e.employee_id,
       dept_current.department_id AS current_department_id,
       dept_new.department_id     AS new_department_id
FROM   employees e
       JOIN departments dept_current
         ON e.department_id = dept_current.department_id
            AND dept_current.AvgSalary > e.salary
       JOIN departments dept_new
         ON dept_new.AvgSalary < e.salary


The database is an encoding of the business rules. Without understanding the rules there is no way of making sense of the database. Take it piece-by-piece, focusing on one area until you have a reasonable understanding of it.

Even if documentation exists I like to draw ER diagrams from scratch as it helps me remember the connections. It also allows me to compare how I would have done it to how it is currently implemented. The differences are an opportunity for further learning.

Reading the code will bring out further "virtual" FKs and alternate keys that are not declared explicitly.


32-bit Enterprise SQL 2005 on 64 bit Windows 2008 Server Standard. Memory configuration

The server has 64GB RAM installed, however in taskmgr there are only 32GB available. I believe this is a Standard Windows Server limitation, just want to confirm.

You are correct max memory limitation for Windows Server 2008 Standard is 32 GB. This is mentioned in Memory Limits of Windows Server Machines.

  On other side, we have checked the SQL Server memory counters on perfmon, and the value we get for target and total memory is about 3.500.000,

When 32 bit SQL Server is installed on 64 bit machine it is called WOW System. For this the Virtual Address Space limit is 4GB. And in normal and default configuration SQL Server cannot see/utilize more memory than the VAS limit so you are seeing 3.5 GB in Perfmon counters.

  The service account is Local System, but I cant see the "Using locked pages for buffer pool" in the SQL Server logs

Since you are on 32 bit system you should not look for this message instead look for "Address Windowing Extensions is enabled", if you see this then it is enabled. If you have 64 bit system then the message "Using Locked pages for buffer pool" makes sense. Actually this is how AWE works in 32 and 64 bit respectively.

  As this is 64 bit OS, no need to enable AWE.

No, this is true for 64 bit SQL Server not if you have 32 bit SQL Server.

Now for SQL Server to see more memory, you have to enabled AWE so that SQL Server data and index pages can see more memory than 4 GB. Since your windows can see more than 4 GB( In your case 32 GB) AWE would help SQl Server data and index pages accessing larger part of memory. Please note I mentioned data and index pages, apart from this no other memory consumer can see those extra memory.

PS: At the time of writing the answer most of the blogs.msdn links are not opening as they are being migrated to new platform, I will add the links in answer as soon as they start working so that you have some reference to read


How to capture runtime parameters with extended event?

If the procedures are being called, you should absolutely see the parameter values in the 'statement' column. I have a blog post that demonstrates exactly this issue. There's literally nothing magical to make this happen. It's just a part of the statement.
Now, to make the statement appear in the grid in the Live Data Viewer, you can either right click in the details pain and select the menu choice to show the statement, or, you can select the column picker from the toolbar. Here's a video showing how that works.


Sql Server view with very limited access

As soon as someone has access to SELECT from a view, he can also copy the result set and join it with other views or tables, he has access to.

If you need to follow those requirements, you would need to create a custom application, that does not allow copying of data.


The sort column must be the last column used in the index - MongoDB Indexing Advice

Think of the index like the business section of the phone book.

There are multiple levels of sorts:

a would be category, like "Doctors"
b would be last name, like "Smith"
c would be first name, like "John"

The listings (rows) are sorted in order of Category -> Last Name -> First name

Using this model, how can you find doctors named John without knowing last names?  The answer is, you can't, without looking at every single record (which means not using the index).

In your working examples, you use all the fields left to right without skipping any levels.

In your non-working example, you are looking for the last field and the first without knowing anything in the middle.  You can't get to the last level without knowing what's in between.


Can I create a DOMAIN on a range of years

A domain is a constraint. It doesn't hold any values whatsoever. It can only restrict a type to exclude values. If you need to store a range, use the range types.

As a side note, you can in fact, create a domain on a range type. In the event you want all of your ranges to start on a Wednesday

CREATE DOMAIN foo AS daterange
  CHECK ( EXTRACT(dow FROM lower(VALUE)) = 4 );

SELECT daterange('2018-01-02'::date,'2018-02-05'::date);

SELECT daterange('2018-01-02'::date,'2018-02-05'::date)::foo;
ERROR:  value for domain foo violates check constraint "foo_check"

Moreover, you can also ensure the range is always one year:

CREATE DOMAIN bar AS daterange
  CHECK (
    (lower(VALUE)::timestamp without time zone - upper(VALUE)::timestamp without time zone) = '-365 days'
  );

But if you're going to do that -- make it such that your range is always one year and a fixed size, I suggest you just store the year that you start as ::smallint and be done with it.


create new MySQL database and user using OS root credentials

I believe you will need to install another, separate instance of MySQL, configure it to run on a different port, specify that port in your login screen, and then do as you like.

You can do this by simply installing MySQL to another disk location, and then editing the ini file.  On Windows and Mac installations, this is called my.ini

Alternatively, I think your only choice is to get permissions granted to you on the currently installed MySQL instance.  The root password to the OS and the root password to the MySQL server instance are entirely distinct (as I'm sure you know).


MySQL collate error occurs when sent by PHP app but does not occur in phpMyAdmin

Thank you google.

Apparently, I have been using PHP with MySQL without ever needing to worry about client collation.

I don't know if this will lead to other problems, but for the moment I've solved this problem by adding the following query to the main controller (so that the query is run for every script on the site).

SET NAMES "utf8" COLLATE "utf8_general_ci";

Now my query works through my PHP script. I suppose that phpMyAdmin might take the collation of whatever tables you are querying and set the client collation accordingly. I'm just guessing on that one.

Here's where I found the answer:
http://dev.mysql.com/doc/refman/4.1/en/charset-collation-charset.html


Your code shows 3 tables, could you describe their structure?

Also, your code is using for xml on Chapter table, which, I assume, is the reference of the codes, not the codes that are actually used in chapter. So, may be better to use xml for path on CodeChapterLinks table.

Like:

select  distinct
       Code,
       STUFF((SELECT ', ' + c.chapter
             FROM CodeChapterLinks  c
             where c.code = t.code
             ORDER BY c.chapter
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From CodeChapterLinks  t

And by the way, stuff is not the function that concatenates the results, for xml does.


Oracle out-of-place upgrade on same host: impdp issues

Check the Oracle Utilities documentation.  When using Datapump tools, you can reset paths using the REMAP_DATAFILE parameter for import jobs.

Also, insure your OS session variables reflect the new DB.  This is often over-looked when working with a new DB on a server that already has a DB working on it.


Try doing the regular split, getting strings that look like 20151002SERVER1. Then you can just refer to CONVERT(date,LEFT(Item,8),112) and STUFF(Item,1,8,'') as your date and server name.


Search the web for

mysql apparmor

There are lots of solutions out there.  A couple of possibilities:
https://askubuntu.com/a/953988
https://serverfault.com/questions/896653/how-do-i-get-the-right-apparmor-profile-for-mysql-on-ubuntu

You should not need to disable or remove apparmor


SSIS is the way to go on this. If you've never built a package before, and you know your source files (read also: spreadsheets) are always going to be the same ones, what you can do is use SQL Server's Import/Export wizard. In SSMS right-click database and select Tasks > Import (or Export) Data...

This opens a wizard which walks you through the steps of selecting your source/destination files as well as destination tables (can create tables if they don't exist already). You'll have to map which columns go where but the wizard is pretty straight forward. When you're done it will ask you to run or save the package (or do both). Save the package. This will save your package in .dtsx format if you choose to save it to file system. Your other option is to save the package in SQL Server itself, which would then keep them in the msdb system database.

Once you've saved your package, you can create a SQL Agent job to run that package periodically (you specify the schedule) so that you can always load your tables using SSIS. If you'd like to learn more about SSIS, check out my company's free webinars (we cover the whole BI stack) at PragmaticWorks


Filtered index is not used when variable in WHERE condition

Filtered indexes can't use variables/parameters, unless you're building your query with dynamic SQL so that the query ends up getting executed with a literal.

A good article on the topic is Filtered Indexes and Dynamic SQL by Jeremiah Peschka.

OR

As Martin suggested, you could add WITH (RECOMPILE) to the query, but that should not be used without understanding the potential repercussions (see RECOMPILE Hints and Execution Plan Caching).


Hi Max and welcome to DBA.SE.

  "when reviewing the execution plan since I have many records, it takes
  too long to go through all the records of both tables, so how can I
  optimize it?"

Are you having a real performance issue or are you assuming you are going to have one? If your query needs the data from all rows, there is no magic trick that MySQL can do and not scan each and every one. If this is a real production challenge, we will need to see your execution plans for the real data. If it's not, I wouldn't worry about it too much, but there are several things you can do to help the optimizer and allow more efficient access methods.

Instead of the derived table for the months, I would create a permanent table for months with just 12 rows. probably the month number and its name and index both. This will make the query more readable, and might change the optimizer choice when the set is indexed. you will most likely use it in other places as well. I also like to create full calendar tables in my databases, see for example this script. It's a SQL Server T-SQL script, so you may need to adjust it slightly to work in MySQL.
The optimal index for your query is on (date, amount). The latter key is needed to cover the query so the optimizer can get all it needs directly from the index without performing lookups.
If you can upgrade to MySQL 8, it offers functional indexes where you can create an index on (MONTH(date), amount) directly, which will speed up this query, but may hurt modification performance like any other index.

HTH


Is it possible to disable new version checking in pgAdmin 4?

Add this line to config_local.py and restart the pgadmin4:

UPGRADE_CHECK_ENABLED=False

Note:
The location of config_local.py is same as config.py. You can find the location of config.py with this command in linux:

$ find / -wholename "*/web/config.py" 2>/dev/null

If the config_local.py file doesn't exist yet, you can create one.


Primary key added automatially to seconday indexes on MySQL 5.5?

Yes, this applies to MySQL 5.5 too, but only for InnoDB tables.


Ranking not correct in sql server 2012 full text search

Since you are using FREETEXTTABLE the ranking is very loose because its interpretation of the string is loose.
The text you supplied for row 2 only has one direct match ("Engineer"), but row 3 has 2 matches("trail" and "Engineer").
But you need to understand the difference between FREETEXT and FREETEXTTTABLE operators and the more precise CONTAINS and CONTAINSTABLE operators.
See https://msdn.microsoft.com/en-us/library/ms176078.aspx which explains in part:

• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Because FREETEXT freely interprets your data using a thesaurus it is likely that "trail" and "road" are interpreted as equivalent in meaning and therefore the two rows get the same score.
If you need more precise answers you should use CONTAINSTABLE.


Oracle big datafile restore fails using Netbackup (ORA-19511, ORA-27190)

7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) socket read failed, An existing connection was forcibly closed by the remote host.  (10054)

This shows that it is trying to read from a socket that has been closed. It's a networking problem. Ask your network administrator to raise the firewall connection timeout between the database host and the netbackup host.


AlwaysOn Secondary readable - can't connect with applicationintent=readonly

Your connection string likely doesn't specify the database. It has to include the database name so that SQL Server can tell which routing list to use. (Your server could have multiple Availability Groups running on it.)


Analysis Services not allowing other/remote connections other than localhost

We always run our Analysis Services on a named server, e.g. AnSrv123, and being run by a Domain Login such as DomainName\ManageAnSRV124.

Regarding connecting to Remote Analysis Servers using SQL Server authentication please note the following.

Connect from client applications (Analysis Services)
https://msdn.microsoft.com/en-us/library/dn141153.aspx

This says, in part: "Authentication is always Windows authentication, and the user identity is always the Windows user who is connecting via Management Studio."


Why does the READPAST hint cause indexed views to be ignored?

Reusing the example table and indexed view from my article Another Reason to Use NOEXPAND hints in Enterprise Edition:

CREATE TABLE dbo.T
(
    col1 integer NOT NULL
);
GO
INSERT dbo.T WITH (TABLOCKX)
    (col1)
SELECT
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.type = N'P';
GO
CREATE VIEW dbo.VT
WITH SCHEMABINDING
AS
SELECT T.col1
FROM dbo.T AS T;

Repro

This query matches the indexed view (albeit with a redundant aggregate):

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT;

Adding a READPAST hint results in accessing the base table:

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT
    WITH (READPAST);

Explanation

The READPAST hint is semantic-affecting. The optimizer resists rewriting queries such that the results change. To illustrate:

The following query executes without problems:

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT
    WITH (READPAST);

However:

SELECT DISTINCT
    VT.col1
FROM dbo.VT AS VT
    WITH (READPAST)
OPTION
    (TABLE HINT (VT, FORCESCAN));

Produces the error:

Msg 8722, Level 16, State 1, Line 42
Cannot execute query.
Semantic affecting hint 'readpast' appears in the 'WITH' clause of object 'VT'
but not in the corresponding 'TABLE HINT' clause.
Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints
match the WITH clause.

When you reference the indexed view without the NOEXPAND hint, the view is expanded (before compilation and optimization begins) to reference the underlying objects instead. Later in the process, the optimizer may consider matching the query tree back to an indexed view, in whole or in part.

When READPAST is used without NOEXPAND, the hint propagates to the base table, preventing view matching (different semantics).

With NOEXPAND, the hint applies to the view directly, so there is no problem.


can a table in 2nd normal form have a FD in which the non prime attribute depend on a super key having an extraneous attribute?

You are asking:

  But can the non prime attribute be dependent on a super set of the candidate key ? I mean can the left side contain an extraneous attribute.

If a set of attributes is a candidate key, then it determines all the other attributes, including non prime attributes. So, if you add attributes on the left side nothing changes with respect to the definition of 2NF, the only important thing is that a non prime attribute cannot depend on a proper subset of a candidate key.

Then, you are asking:

  Can't one non prime attribute just be dependent on another non prime attribute?

If in a relation schema a non prime attribute depends from another non prime attribute we can only say that this dependency does not violates the original Codd’s definition, that requires only that every non prime attribute must be “fully dependent on each candidate key of R”, which can also be expressed as: does not exists a dependency in which the left part is a proper subset of a candidate key and the right part is a non prime attribute.

So, for the same reason, if a dependency contains in the left part a proper subset of a candidate key plus a non-prime attribute, this does not fall under the Codd’s definition, meaning that it does not violate such definition.

Finally, note that the 2NF has not interest neither in practice nor in theory: it  has only historical interest. In fact, a relation is said properly normalized if it is in Boyce-Codd Normal Form or in Third Normal Form (or in Fourth or greater normal forms). In fact in many important database books you can find formal algorithms to bring a relation in BCNF or 3NF, but not in 2NF.


Mysql Database Design - identifying required patterns

I don't know if there is a design pattern beyond "normalize properly", but I don't think you're too far off from a workable solution.
Given your description I don't see a need for a Subgroup entity - we can just have Groups and denote one of those as "The Board".
For specific GroupFunctions, we'll consider those to be a special case for GroupMembers, i.e. once someone is assigned to a group they can be assigned Chair/Vice Chair/Special Assistant to the Jr Vice Chair/etc.  Each GroupFunction can be assigned to at most one GroupMember.
As for active/passive/etc., it's entirely possible to have one level for the Member and different levels for each Group, but you might need some transaction logic if a "passive" member cannot be an "active" member of a group.  Alternately, you could derive the MembershipType for each Member based on the groups they are members of.
I'll leave the datatypes and full DDL to you (including audit tables), but this should work:
CREATE TABLE MembershipType
(
  MembershipTypeCd
 ,Name
 ,CONSTRAINT PK_MembershipType PRIMARY KEY (MembershipTypeCd)
 ,CONSTRAINT AK_MembershipType UNIQUE (Name)

);

CREATE TABLE Member
(
  MemberId
 ,MembershipTypeCd
 ,CONSTRAINT FK_Member_Has_MembershipType FOREIGN KEY (MembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
 ,CONSTRAINT PK_Member PRIMARY KEY (MemberId)
);

CREATE TABLE MemberFunction
(
  FunctionShortName
 ,Name
 ,CONSTRAINT PK_MemberFunction PRIMARY KEY (FunctionShortName)
 ,CONSTRAINT AK_MemberFunction UNIQUE (Name)
);

CREATE TABLE Group /* A reserved keyword in most DBMS - may make sense to rename */
(
  GroupId
 ,Name
 ,CONSTRAINT PK_Group PRIMARY KEY (GroupId)
 ,CONSTRAINT AK_Group UNIQUE (Name)
);

CREATE TABLE GroupMember
(
  GroupId
 ,MemberId
 ,GroupMembershipTypeCd
 ,CONSTRAINT FK_GroupMember_Member_Of_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
 ,CONSTRAINT FK_GroupMember_Is_Member FOREIGN KEY (MemberId) REFERENCES Member (MemberId)
 ,CONSTRAINT FK_GroupMember_Has_MembershipType FOREIGN KEY (GroupMembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
 ,CONSTRAINT PK_GroupMember PRIMARY KEY (GroupId, MemberId)
 ,CONSTRAINT AK_GroupMember UNIQUE (MemberId, GroupId)
)
;

CREATE TABLE GroupFunction
(
  GroupId
 ,FunctionShortName
 ,CONSTRAINT FK_GroupFunction_Available_For_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
 ,CONSTRAINT FK_GroupFunction_Is_MemberFunction FOREIGN KEY (FunctionShortName) REFERENCES MemberFunction (FunctionShortName)
 ,CONSTRAINT PK_GroupFunction PRIMARY KEY (GroupId, FunctionShortName)
);

CREATE TABLE GroupFunctionAssignment
(
  GroupId
 ,FunctionShortName
 ,MemberId
 ,CONSTRAINT FK_GroupFunctionAssignment_Assigned_To_GroupMember FOREIGN KEY (GroupId, MemberId) REFERENCES GroupMember (GroupId, MemberId)
 ,CONSTRAINT FK_GroupFunctionAssignment_Assigment_Of_GroupFunction FOREIGN KEY (GroupId, FunctionShortName) REFERENCES GroupFunction (GroupId, FunctionShortName)
 ,CONSTRAINT PK_GroupFunctionAssignment PRIMARY KEY (GroupId, FunctionShortName)
);

Pictures are usually easier to understand (if you are unfamiliar with IDEF1X, read this):


Problems during migration from SQL Server to MySQL via MySQL Workbench

I think F3B3B4AF is beyond the end of established UTF-8 characters.  Perhaps MSSQL allowed, but MySQL did not.

Please provide SHOW CREATE TABLE for the target table.  It had better be using utf8mb4 for the target column.

Do any of these look correct?

                              gb2312, gbk   6  2 '蟪疮'
                                    euckr   6  2 '車눕'
                                     big5   6  2 '馧敞'
                            eucjpms, ujis   6  2 '鶻姦'


mysql 5.6 replication log-slave-updates doesn't work

First, you might want to explicitly set those options to on and restart the instance (enabling binary logging requires restart)
log_slave_updates=ON
log_bin=mysql-binlog

Second, you are not supposed to replicate from a master which is at a higher version to a slave at a lower version because of incompatible changes in binary log formats across versions: https://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html


Given this is a Windows installation, @DTest still provided the initial proper direction.

Apply the following formula:

Most people use this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections

I prefer this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

These variables are the ones you need to adjust until the formula yields 80% of installed RAM or less.

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections


If you have 100-150 columns, it is likely some of those are repeating groups.  Those should be normalized into their own child tables (PK: user_id, sqn). Review your design. You should normalize to at least 3rd normal form.

Joins are usually very efficient, and in many cases only rows required for the result are joined.  With that many columns, it is unlikely you would ever have to return all the columns, so you would likely be joining a subset of the tables.

You should be concerned with the cost of searching for matches on columns that aren't indexed.  The higher the column number, the more expensive it is to find the column to compare it.  This cost is likely to far outweigh the cost of joining.

The main reasons I use a one-to-one tables are:

Separate large tombstone data from volatile information.
Separate data with higher security requirements, from more public data.
Store sub-type data (one to zero-or-one).


Why are you using a cursor for this type of operation?

Most RDBMS are optimised for set-based operation, a cursor or a loop are iterative operations which perform row-by-row operations.

Whenever possible, you want to use declarative set-based code.

Try this:

DELETE sb
FROM stockbatches sb
INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
AND sb.batch_part = btd.batchpart


Did you end the command with a semicolon?

CREATE USER username WITH PASSWORD 'test';


SELECT statement with an explicit list of columns and then an asterisk (*)

Only if you prefix the asterisk with the table name:

select dummy, dual.* from dual;

DUMMY DUMMY
----- -----
X     X

Which limits it's use to tables not joined with the using syntax:

select dummy, d1.* from dual d1 join dual d2 using(dummy)

SQL Error: ORA-25154: column part of USING clause cannot have qualifier
25154. 00000 -  "column part of USING clause cannot have qualifier"
*Cause:    Columns that are used for a named-join (either a NATURAL join
           or a join with a USING clause) cannot have an explicit qualifier.
*Action:   Remove the qualifier.

select d2.dummy, d1.* from dual d1 join dual d2 on(d1.dummy=d2.dummy);

DUMMY DUMMY
----- -----
X     X


Fetch all rows and leave column value empty if no equality match

I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists

So given two joined tables, "left" and "right", you want to include every row from the "left" table and matching rows in the "right" table if they happen to be there?
That's called a Left [Outer] Join.
You'll have to use explicit join syntax, not the "comma" form that you currently have, because that always does an "inner" join, requiring matching values in both tables.
Try something like this:
SELECT
  h.*                   <-- Personally, I'd avoid this and state every column name explicitly
, k.customer_id
FROM events h
LEFT JOIN customers k
     ON h.ident = k.id
WHERE h.eventid BETWEEN 400 and 600
ORDER BY h.eventid
;


Is there a cross-platform way to compare data in one columnd on each sitde of replicated data, like a checksum or hash?

The Oracle function for hashing is called
Standard_hash()
which supports these methods :- SHA1, SHA256, SHA384, SHA512, and MD5
https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647
In SQL Server you can use Hashbytes()
which supports these methods :- MD2, MD4, MD5, SHA, SHA1, or SHA2_256, SHA2_512
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15
As they both support SHA2_256 or SHA2_512 I would start by setting up a few rows of test data and check that they produce the same result.
a search also throws up the following link
http://roelantvos.com/blog/hashing-in-sql-server-and-oracle-for-the-same-output/
EDIT :  I reread the comments & saw that you were concerned that standards for conversions of decimals to strings etc were not standard - I think you could fairly easily address this by using the schema & type views to dynamically construct conversion queries using CASE statements etc, & at least it only has to be done on one side.


Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query?

In your case it's a choice between bad and worse. gender is low selective (read MySQL has to read at least a half of index), if age comes first, gender part won't be used at all (because of < ). (gender, age) seems to be a bit better anyway.

Check with SHOW STATUS LIKE 'Handler%' which index makes MySQL to read less rows.

mysql> FLUSH STATUS;
mysql> SELECT name WHERE gender='M' and age<18;
mysql> mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 38    |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 154   |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 35    |
| Handler_read_key           | 62    |
| Handler_read_last          | 0     |
| Handler_read_next          | 153   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 5947  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 1     |
| Handler_write              | 617   |
+----------------------------+-------+


Update a table using summed values from another table in MySQL/mariaDB

Assumptions:

L.L_EXTENDEDPRICE should be L.L_EXTPRICE
L_DISCOUNT and L_TAX are percentages, in which case the values stored in LINEITEM need to first be divided by 100.0 before performing calculations

Some sample data:

drop table if exists LINEITEM;
drop table if exists ORDERS;

create table ORDERS
(O_ORDERKEY      int
,O_TOTALPRICE    decimal(10,2));

insert into ORDERS values
(1,0.0),
(2,0.0);

create table LINEITEM
(L_ORDERKEY      int
,L_EXTPRICE      decimal(10,2)
,L_DISCOUNT      decimal(10,2)
,L_TAX           decimal(10,2));

insert into LINEITEM values
(1,12.99,20, 8.5),
(1, 8.99,30,11),
(2,99.99,10,10),
(2,49.99, 0, 8.5),
(2, 3.99,50,14.7);

Contents of ORDERS before the update:

select * from ORDERS;

O_ORDERKEY  O_TOTALPRICE
----------  ------------
         1          0.00
         2          0.00

Proposed update statement:

update ORDERS O
set    O.O_TOTALPRICE  = (select sum(L.L_EXTPRICE * (1 - L.L_DISCOUNT/100.0) * (1 + L.L_TAX/100.0))
                         from   LINEITEM L
                         where  L.L_ORDERKEY = O.O_ORDERKEY);

Contents of ORDERS after the update:

select * from ORDERS;

O_ORDERKEY  O_TOTALPRICE
----------  ------------
         1         18.26
         2        155.52

Here's a rextester fiddle of the above.


Why is this relation not in Boyce-Codd Normal Form (BCNF)?

C is a non-prime attribute (not part of the candidate key), and C depends on (A,B), which is a proper subset of the candidate key.

So the table is not in second normal form by definition.

Since BCNF implies 3NF and 3NF implies 2NF, this implies that the table is not in BCNF.


Find first non-matching character between two strings

First you should have a Numbers table.

create table dbo.Number(N int not null);
go

alter table dbo.Number add constraint PK_Number_N primary key clustered (N);

go

;with C(N) as
(
  select 0
  union all
  select N+1
  from C
  where N < 11000
)
insert into dbo.Number(N)
select N
from C
option (maxrecursion 0);

Use the numbers table to split your strings into rows. Get the matching characters and position using intersect. Use row_number to get only the matching characters from the start and finally rebuild the string with a for xml trick or string_agg.

declare @Str1 varchar(20) = 'Interesting0a';
declare @Str2 varchar(20) = 'Interested00a';

with C1 as
(
  select N.N,
         substring(@Str1, N.N, 1) as C
  from dbo.Number as N
  where N.N between 1 and len(@Str1)
  intersect
  select N.N,
         substring(@Str2, N.N, 1)
  from dbo.Number as N
  where N.N between 1 and len(@Str2)
),
C2 as
(
  select C1.N,
         C1.C,
         row_number() over(order by C1.N) as rn
  from C1
)
select string_agg(C2.C, '') within group(order by C2.N)
from C2
where C2.N = C2.rn;

/*
select (
       select C2.C as '*'
       from C2
       where C2.N = C2.rn
       order by C2.N
       for xml path(''), type
       ).value('text()[1]', 'varchar(20)');
*/


Accessing a SQL Server Denali CTP1 database with Management Studio 2012 SP1 Express

You'll want to upgrade the database to the full version of SQL Server 2012.  I'm not surprised that the beta database engine doesn't work with the full release of the management tool.  There's no guarantee that beta and RTM products will work together.


Database backup over sqlcmd call from batch file

Do this:

1.> Open a Command Prompt window and type:
cd\
dir sqlcmd.exe /s

Our Command Prompt window returned the following:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn

Note: if no sqlcmd.exe is found something happened with your SQL install.
To get you up and running I'd simply download the utilities themselves here:

https://www.microsoft.com/en-us/download/details.aspx?id=36433

2.> From the Command Prompt window command menu Mark and highlight that path that was returned and press Enter to put it in the paste buffer.

3.> Edit the System path (not the user path) within Windows and paste the path you just found just after all the %SYSTEMROOT% path references and click OK. You may need to restart your server for this to take affect.


SQLCMD command is not able to insert accents

Another option, one which I've just learned, comes from the sqlcmd documentation. You need to set the codepage for sqlcmd to match that of the file encoding. In the case of UTF-8, the codepage is 65001 so you'd want:

SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log
-f 65001


How to point tablespace in Oracle's pre-built Developer Day VM to another mount point

NFS
I highly recommend you change your design to use a supported file system : NFS.
The database can use its own NFS client driver (Direct NFS) to talk to the NFS Server.  It will use the OS's if it can't use DNFS.
UNDO/TEMP
I'd keep these on the SSD and set AUTO EXTEND to NO.


What are the files with the extensions .1, .2 or .3 in the data directory

The PostgreSQL documentation describes this:

  When a table or index exceeds 1 GB, it is divided into gigabyte-sized segments. The first segment's file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc.


Not as part of MySQL, but tools like Percona Xtrabackup makes the process a bit faster for exporting/backing up the tables, allowing things like using regular expressions or lists for filtering:

Partial backups
Restoring individual tables

For importing a list of tables, you can use some automation oneliners like this one found on Bill Karwin tools:

mysqldump --no-data $schema > schema-ddl.sql
mysql -N -B <<'EOF' > discard-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` DISCARD TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF
mysql -N -B <<'EOF' > import-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` IMPORT TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF

Basically, you can use the information_schema.tables table for listing the tables you want using "dynamic sql". For example, change $schema above for the desired database name.


This could be solved with an EXISTS subquery

select
    rs.ID,
    rs.event1,
    [Exists] =
        case when exists
                ( select *
                  from #bigponyhorses as bph
                  where rs.uuid = bph.uuid
                    and bph.empid = @empid
                )
            then 'Yes' else 'No'
        end
from
    #robschneider as rs ;

or with OUTER APPLY operator:

select
    rs.ID,
    rs.event1,
    [Exists] = coalesce(b.result, 'No')
from
    #robschneider as rs
  outer apply
    ( select top (1) result = 'Yes'
      from #bigponyhorses as bph
      where rs.uuid = bph.uuid
        and bph.empid = @empid
    ) as b ;

The TOP 1 is needed when using OUTER APPLY to make sure you get in the result one row for each #robschneider, even if there are 2 or more matching rows in the #bigponyhorses table. If there is a UNIQUE constraint on #bigponyhorses (uuid, empid), then it's not needed.


We use MongoDB 3.6 community version, How can i get the number of hits on single collection as well as bandwith usage?

There is a documentation section for Analyzing Performance, another for Monitoring, and one specifically for free monitoring.

You can also run the command db.serverStatus() to retrieve server statistics.


Does MySQL 5.5 support shared database with separate schema?

My understandng of MySQL is that what they call a "database" is a schema on other db's.  So the answer to your question is "yes" or "no" depending on the specifics you are asking.  In fact "create schema" just maps to "create database" on MySQL.

So yes, you can do one schema per tenant but that is not any different on MySQL than one database per tenant.  So not sure if that is a yes or no.


Mysql dump restore doing some funny stuff

I think you meant this:

mysql -u root -p fms < /device.sql

This will import SQL from /device.sql into the fms database

mysqldump only outputs and is not designed to input anything

Doing this

mysqldump -u root -p fms

will echo the mysqldump all all tables currenly in the fms database


Availability groups taking time to change states

I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node ...

Just as others have pointed out in the comments section, this isn't a good way of testing as you've nicely and cleanly taken the service down. I mean, it is a great test if your disaster case is "someone shutdown SQL Server nicely", however outside of that it doesn't help much.

Having a two-node cluster also isn't very helpful for testing HA (or production) as there are just two possible nodes. While you can argue the usefulness of this setup one way or another based on certain factors, you won't really get to experience AGs until you have 3 or more nodes. This is mainly to do with 2-node clusters being special cases since there are so little resources.

  ... the one in the secondary went into resolving state and it took a long time for it to become a primary AG.

Resolving, in AG terms, means it is trying to find and connect to the primary. Since you've shut it down nicely, it'll take some time (assuming the cluster has quorum and the failover policy has been met) to fail.

  Does it have to do with networking issues?

Nope.

  I also want to know if it is a good practice to have a separate network interface for the heartbeat network.

No such thing as a "heartbeat" network since Windows Server 2008+. The cluster will decide which route to take.

What do you really want?

If you want to see how long automatic failover takes then you'll first need to figure out what scenarios you'd like to test. Here are some examples:

OS Disk fails
Cluster partitioning
Data disk fails
Out of memory
Bugcheck
log disk fails
Overwhelmed disk
slow networking
windows patching
sql patching
server power failure

You can add more, but for each possible issue there will be a different amount of time it takes.

The amount of time is roughly:

total = Detection + Mitigation + Redo Queue drain + Undo

Each of the above will have different detection and mitigation times, but the redo and undo should be roughly the same for each test. So, at the end of the day, really you're testing detection times.

If you only care about how long it takes to do a failover, then run: ALTER AVAILABILIY GROUP MyAG FAILOVER on the new primary. Time it. That's how long it takes.


Looping through JSON and insert data to Postgres DB

You need to combine two jsonb_array_elements() calls:

select c.cases ->> 'id' as id,
       c.cases ->> 'class_name' as class_name
from the_table
  cross join jsonb_array_elements(the_column -> 'suites') as s(suite)
  cross join jsonb_array_elements(s.suite -> 'cases') as c(cases);

Online example


Distinguishing data from different sources

1) Add a column to the table which holds the source system. Change the primary key of the table to a compound key including source system and source id. Then value ('System A', row 1) is distinct from ('System B', row 1).

There are a few downsides to this. If the source system were ever to revert to previous values (say a DB was restored, or a sequence reset, or a programming error or ... ) our uniqueness would be compromised.

It is generally advantageous to make keys (and hence indexes and foreign keys) narrow for performance reasons. So a one-column key is, other things being equal, preferable to a two-column key. If you're not running your hardware to its limits this is unlikely to be a problem in real life.

Similarly having the system name - a character string - in the key makes it wider. That can be mitigated by introducing a table SourceSystem(SystemName varchar(<whatever>), SystemId smallint) and using SystemId in the actual data table. Now you have to join to the new table should you ever need the human-readable name.

The advantage is simplicity. The loading process need only hard-code a simple string (or a table look-up) and everything else remains the same. It is easy to reference back to the source since all the IDs are intact.

2) Add a surrogate key to your table. You allocate values to this key as you insert rows. This surrogate key becomes the primary key. Say Source A sent rows 1 & 2 and Source B sent rows 1 & 5 the table becomes

SurrogateId  SourceId
-----------  --------
1            1
2            2
3            1
4            5

Advantages are we have complete control over the primary key values in our system. Whatever changes the developers of the source choose to deploy, and however their ID scheme fluctuates, it will not affect us.

Unfortunately we have no direct reference telling us which was the source for each row, if that is important in your world. That can be addressed by adding a new non-key column to hold that information.

3) Put a offset for each source. Source A's rows all get 10,000 (or 10 million, or 100 million or whatever) added, Source B's have 20,000, Source C's have 30,000 and so on. The obvious problem is that each range could be exhausted. If that happens you can move to a new interval for that source.

Option 2 - the surrogate key - is the my preferred solution.


How do I Create Tablespace in Oracle 11g?

The answer to your question can be found in the SQL Language Reference (excerpt follows).

  Creating Basic Tablespaces: Examples

  This statement creates a tablespace
  named tbs_01 with one data file:

  CREATE TABLESPACE tbs_01 DATAFILE 'tbs_f2.dbf' SIZE 40M     ONLINE;

It sound like you are new to Oracle databases.  Oracle provides a wealth of documentation at http://docs.oracle.com/en/database.  Specifically, I recommend reading their excellent introduction to the database called the Concepts Guide.


The OUTPUT has to go before the FROM.

DELETE tb
    OUTPUT Deleted.TransactionID INTO #TransactionIDs
    FROM dbo.TransactionBatch tb
    INNER JOIN @BatchTransactionIDs tid
        ON tb.TransactionID = tid.TransactionID


Please follow the steps from the below links, If you face issues in setting up the replica set or any questions on the blog. Please add a comment with your issues.

How to Setup MongoDB Replication

Deploy a Replica Set


Yes! we can do that.

Which can be observed from the following demo.

I have two hosts Server1(192.168.30.132) and Server2(192.168.30.142) and ORCl database is on Server1.

I skipped some of the steps, creating standby redo logs for example, and configuration to make the answer short.

On Server1

Enable archivelog

SQL> alter database archivelog;

Database altered.

Enable force logging.

SQL> alter database force logging;

Database altered.

Create standby controlfile.

SQL> alter database create standby controlfile as '/home/oracle/Desktop/orcl_stby.ctl';

Database altered.

SCP standby control file to Server2.

 [oracle@server1 Desktop]$ scp orcl_stby.ctl oracle@192.168.30.142:/u01/app/oracle/fast_recovery_area/ORCL2/controlfile/

SCP password file to Server2.

[oracle@server1 dbs]$ scp orapworcl oracle@192.168.30.142:/u01/app/oracle/product/11.2.0/db_1/dbs/

Set log archive config and log archive dest parameters.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl2 NOAFFIRM lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2';

On Server2

Make necessary directories.

$ORACLE_BASE/admin/db_unique_name/adump
fast_recovery_area/db_unique_name/controlfile
fast_recovery_area/db_unique_name/archivelog

Create initSID.ora filewith the following parameters OR you can create pfile on Server1 and SCP to Server2.

[oracle@server2 dbs]$ vi initorcl2.ora

db_name= orcl
db_unique_name=orcl2
compatible=11.2.0.4
control_files='/u01/app/oracle/fast_recovery_area/ORCL2/controlfile/orcl_stby.ctl

[oracle@server2 dbs]$ sqlplus / as sysdba
SQL> startup nomount

SQL> create spfile from memory;

File created.
SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size          2251816 bytes
Variable Size         159384536 bytes
Database Buffers       50331648 bytes
Redo Buffers            5189632 bytes
SQL> alter database mount;

Database altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';

System altered.

SQL> alter system set db_recovery_file_dest_size=500M;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/';

System altered.

On Server1

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

On Server2

[oracle@server2 archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/ORCL2/archivelog
[oracle@server2 archivelog]$ ls
2017_02_22
[oracle@server2 archivelog]$ cd 2017_02_22/
[oracle@server2 2017_02_22]$ ls
o1_mf_1_36_dbtc52mg_.arc
[oracle@server2 2017_02_22]$

As long as LGWR/ARCn process on Primary and RFS process on Standby are running we can transfer archived logs without having datafiles in place on standby database.

On Server2

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ----------- -------- ----------    ---------- ----------
RFS       IDLE          0      0             0           0


You are appending the backup into same file so it is actually accumulating the backups. You need to give a new name each time you trigger backup from Tasks->Backup.

Use maintenance plan as it has timestamp for each backup file or else go with Ola Hellengren's solution.

https://ola.hallengren.com/


Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.

You should verify the directory you are trying to place the database in.  The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".

Edit

If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1

You should get results similar to this, highlighted the path for my instance:

You can also verify this path through the restore prompt in SSMS, clicking on the "files":

However also noting that the error message  operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like

  The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'


Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key

Casting to text should not add much overhead, however you may want to consider using UNION ALL instead of UNION if you're looking for performance (UNION attempts to filtering duplicates).


Selecting from all databases from multiple tables

I would start by using information_schema.TABLES and COLUMNS to find which databases (table_schema) has what tables with the desired columns.

Then I would construct (via SELECT CONCAT("SELECT ", ...)...) the SELECT statements that contain references to all the tables/columns desired.

From them, copy and paste to run them.  Or put all of this in a Stored Procedure so that a single CALL can do all the work including executeing the constructed SELECTs.