Should I create multiple tables for different entity states, statuses or stages?

The way I see it, the fact that (a) a concrete Task can present distinct States at different points in time entails (b) the creation of a table that is going to contain a time series, as I will detail below.
Business rules
In order to define the structure and constraints of a relational database, it is paramount to first identify and formulate the corresponding business domain rules (i.e., delineate the conceptual schema) with precision, taking into account the relevant entity types, properties and the corresponding  interconnections. In this way, some of the formulations that are especially significant in the scenario under consideration are:

A Task presents one-to-many StateSpecifications
A Task cannot present more than one StateSpecification at the same Instant
A StateSpecification is considered Current

from the particular Instant in which it is entered
until the exact Instant when the successive
StateSpecification is entered

A State defines zero-one-or-many StateSpecifications
A Task receives zero-one-or-many Comments

Consequently, based on the aforementioned formulations, I created the IDEF1X† diagram shown in Figure 1:

As you can see, Task and State are depicted as individual entity types, each with its own group of properties (or attributes) and its own (direct) associations (or relationships), which are expressed via the corresponding verb phrases, lines, cardinalities and FOREIGN KEY (FK) marks.
The entity type denominated StateSpecification, associated with the two brought up before, is a central aspect in the solution I will explain below.
Expository logical design and sample data
I shaped an expository logical design, based on the IDEF1X diagram above, by means of the DDL structure that follows:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient physical implementation settings; e.g.,
-- a good indexing strategy based on query tendencies.

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

CREATE TABLE Task (
    TaskNumber      INT      NOT NULL,
    Description     CHAR(90) NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Task_PK  PRIMARY KEY (TaskNumber)
);

CREATE TABLE State (
    StateCode   CHAR(1)  NOT NULL,
    Name        CHAR(30) NOT NULL,
    Description CHAR(60) NOT NULL,
    --
    CONSTRAINT State_PK  PRIMARY KEY (StateCode),
    CONSTRAINT State_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT State_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE StateSpecification (
    TaskNumber        INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StateCode         CHAR(1)  NOT NULL,
    --
    CONSTRAINT StateSpecification_PK         PRIMARY KEY (TaskNumber, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT StateSpecification_to_Task_FK FOREIGN KEY (TaskNumber)
        REFERENCES Task  (TaskNumber),
    CONSTRAINT StateSpecification_to_State   FOREIGN KEY (StateCode)
        REFERENCES State (StateCode)
);

CREATE TABLE TaskComment (
    TaskNumber      INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    Body            TEXT     NOT NULL,
    --
    CONSTRAINT TaskComment_PK      PRIMARY KEY (TaskNumber, CreatedDateTime),
    CONSTRAINT TaskComment_to_Task FOREIGN KEY (TaskNumber)
        REFERENCES Task (TaskNumber)
);

--
--

The State table
The State table previously illustrated fulfills a look-up role, and is meant to contain five rows (as per the five State samples you provided):

 +-—————————-+-—————————-+-———————————————-+
 | StateCode | Name      | Description     |
 +-—————————-+-—————————-+-———————————————-+
 | O         | Open      | Indicates that… |
 +-----------+-----------+-----------------+
 | B         | Begun     | Indicates that… |
 +-----------+-----------+-----------------+
 | R         | In review | Indicates that… |
 +-----------+-----------+-----------------+
 | C         | Completed | Indicates that… |
 +-----------+-----------+-----------------+

Note the definition of a PRIMARY KEY (PK) constraint on a column retaining values that are, at the same time, meaninfgul (regarding interpretation by end users and technicians) and small (in terms of bytes at the physical implementation level), which makes it both readable and fast regarding, e.g., data retrieval.
If new business domain States arise, you can of course INSERT the corresponding rows INTO the State table.
The State Specification table
Now, suposse that Task no. 1750 presents the following State History in the form of (unordered) rows held in the StateSpecification table:

 +-——————————-+-———————————————————————-+-—————————-+
 | TaskNumber | SpecifiedDateTime       | StateCode |
 +-——————————-+-———————————————————————-+-—————————-+
 | 1750       | 2016-12-01 16:58:12.000 | O         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-02 09:12:05.000 | B         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-04 10:57:01.000 | R         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-07 07:33:08.000 | C         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-08 12:12:09.000 | R         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-08 19:46:01.000 | B         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-09 06:24:07.000 | R         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-11 07:24:07.000 | C         |
 +------------+-------------------------+-----------+

As demonstrated, each value in the SpecifiedDateTime column indicates the precise point in time in which a given Task (via the TaskNumber) started presenting a particular State (by virtue of StateCode).
Seeing that the StateCode column keeps values that have a quite clear intention, it helps to save the usage of a JOIN clause. It is paramount to make clear that this does not imply that JOINs are something detrimental (they are, in fact, fundamental and powerful instruments in any relational database) but, whenever suitable, reducing the consumption of the valuable resources of the system is decidedly practical.
The Comment table
The Comment table, as exemplified above, can easily be related to the Task table via a FK constraint definition, because the PK value of a Task will remain the same, no matter if its State undergoes zero, one or more modifications.
I assume that, in the business context of relevance, a Comment (a) will always depend on the existence of a Task occurrence and (b) will always belong to the same indidivual Task occurrence; therefore, I defined the Comment table with a composite PK that incorporates the PK column of Task and the exact DateTime in which a particular Comment was created. Apart from that, you might like to evaluate the attachment of a column for system-controled surrogates (e.g., a column with the AUTO_INCREMENT attribute in MySQL) to this table, it may or may not offer a better execution performance (probably not), so carrying out some thorough testing sessions would clarify the case (a non-data addition, as is the case of columns holding system-controled surrogates, should always be well assessed and justified).
Data derivation code samples
There are some important data points that will very likely be required with respect to the scenario in question, but they should be obtained as derived (or calculated) values.
For example, if you need to obtain the CurrentState for Task no. 1750, you can derive it with the next SELECT statement:
SELECT T.TaskNumber,
       SS.StateCode AS CurrentStateCode,
       SS.SpecifiedDateTime
     FROM Task T
     JOIN StateSpecification SS
       ON T.TaskNumber = SS.TaskNumber
    WHERE T.TaskNumber = 1750 -- You can provide a parameter instead of a fixed value.
      AND SS.SpecifiedDateTime = (
                                    SELECT MAX(SpecifiedDateTime)
                                          FROM StateSpecification InnerSS
                                         WHERE T.TaskNumber = InnerSS.TaskNumber
                                 );

When you face the necessity to get the CurrentState for all Tasks, you can declare a statement like the one that follows:
SELECT T.TaskNumber,
       SS.StateCode AS CurrentStateCode,
       SS.SpecifiedDateTime
     FROM Task T
     JOIN StateSpecification SS
       ON T.TaskNumber = SS.TaskNumber
    WHERE SS.SpecifiedDateTime = (
                                    SELECT MAX(SpecifiedDateTime)
                                          FROM StateSpecification InnerSS
                                         WHERE T.TaskNumber = InnerSS.TaskNumber
                                 );

If you have to derive the StartDate and EndDate of all the StateSpecifications that correspond to Task no. 1750, you may utilize:
SELECT T.TaskNumber,
       T.Description,
       SS.StateCode,
       SS.SpecifiedDateTime AS StartDateTime,
       (
          SELECT MIN(SpecifiedDateTime)
                FROM StateSpecification InnerSS
               WHERE T.TaskNumber = InnerSS.TaskNumber
                 AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime
       ) AS EndDateTime
    FROM Task T
    JOIN StateSpecification SS
      ON T.TaskNumber = SS.TaskNumber
   WHERE T.TaskNumber = 1750 -- You can provide a parameter instead of a fixed value.
ORDER BY StartDateTime DESC;

In case you must obtain the StartDate and EndDate of all the StateSpecifications, i.e., the whole period in which they were current:
SELECT T.TaskNumber,
       T.Description,
       SS.StateCode,
       SS.SpecifiedDateTime AS StartDateTime,
       (
          SELECT MIN(SpecifiedDateTime)
                FROM StateSpecification InnerSS
               WHERE T.TaskNumber = InnerSS.TaskNumber
                 AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime
       ) AS EndDateTime
    FROM Task T
    JOIN StateSpecification SS
      ON T.TaskNumber = SS.TaskNumber
ORDER BY StartDateTime DESC;

VIEW definitions
Naturally, one or more of the SELECT statements displayed above can be set up as VIEWs, so that you can arrive at, e.g., the pertinent information directly FROM one single table (a derived one, yes, but it would still be a table).
Demos
I created live demos of the code here deliberated in this SQL Fiddle (running on MySQL 5.6) and in this db<>fiddle (running on Microsoft SQL Server 2014, if someone is interested).
Responses to comments

I think I shall have to go about performing this modification, and think the system will benefit from the additional functionality of the StateSpecification table. It goes to show that good database design can accommodate all our needs without making things messy.

I could not agree more with your second assertion. A database that is designed properly (conceptually, logically and physically), usually becomes a key element in a software development project. If a relational database reflects the business context of interest with accuracy, it can —to be brief— ease its maintenance substantially and offer vast versatility. That is why it is very useful to analyze carefully the structure of the relevant things, to observe them in terms of what they are in the real world, and then represent them with precision in a database implementation.
The data, in and of itself, is a highly valuable organizational asset and, a consequence, it should be administered as such. A reliable manner to achieve said objective is employing technical means that are supported on sound theory, and in the field of data management there is nothing more sound than the relational model. This is in agreement with the Dr. E. F. Codd’s 1981 Turing Award Lecture, entitled Relational Database: A Practical Foundation for Productivity.

Also, just to clarify, the StateSpecification table has to be updated manually on every change of state, correct?

Basically yes, the StateSpecification table has to be brought up to date each time that a Task presents a State change. This procedure should be performed by way of an INSERT operation that introduces a new StateSpecification row that should include a FK value that points to the Task row that applies.
It is opportune to stress that when a Task row is INSERTed, it will have to present a StateSpecification indicating the State deemed as ‘Open’ (by dint of an ‘O’ value retained in the StateCode column), hence you should make use of well defined ACID TRANSACTIONS so that both the Task row and its StateSpecification counterpart are treated as a single unit. In this manner, the two respective INSERT operations can either succeed or fail as a whole, maintaining the involved data consistent with the identified business rules.
Comparable scenario
You might find of help

this Q & A,

as it includes a case that has similar features.

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


The execution plan of your adjacency list example shows that it is able to use the primary key index (RDB$PRIMARY108) for both parts of the UNION. This is very efficient, mainly because you start with a single value, and then recursively look up its descendents (which is probably just a small set of the entire set of records).

For the nested set execution plan it is not able to use an index, because it has to scan all records for the values of p2.tree_left and p2.tree_right. It can only use the primary key index for looking up p1.tax_id = 365612.

You might be able to improve execution if you add a descending index for tax_nodes.tree_left and an ascending index for tax_nodes.tree_right. You may need to swap ascending/descending because I usually do it wrong initially, even if I account for usually doing it wrong. However you may also need to change the query condition so the optimizer picks the indexes for tree_left and tree_right:

p2.tree_left <= p1.tree_left AND p2.tree_right >= p1.tree_left


MongoDB map-reduce impacts insert performance on source collection

Maybe setting the nonAtomic: true flag could help.

Or you can try the Map-Reduce alternatives:

Views
Tailable Cursors
Change Streams


Looks like your oracle directory doesn't exists. You will need to log into the database, create a directory, and retry your import

export ORACLE_HOME=/path/to/oracle/home
export ORACLE_SID=YOUR_SID
sqlplus / as sysdba
create directory DUMPS as '/path/to/your/dump/files';
exit;

And retry your import. Pending on the user you are using to do the import, you may need to grant read/write access.


Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.


Either use autocommit=1 throughout, or use BEGIN and COMMIT.

And, once set, you do not need to set autocommit again.

So, get rid of COMMIT and move the SET out of the loop.

Is some other process changing the value of is_summarized while this proc is sleeping?  I doubt if your technique will catch all edge cases correctly.

Instead of using a declared rows, use a variable:  @rows.  It does not need to be DECLAREd.

Most of the lines of the proc can be pulled out of the loop.  (However, this won't really make any difference.)

As for ROWS -- Most major versions and some minor versions introduce new keywords.  ROWS apparently was added between 5.6 and 8.0.  I avoid some such errors by prefixing any arguments to stored procs and declared variables in stored procs with an underscore.  This convention also helps avoid confusion between variables and column names.


How do I optimize this extremely simple MySQL query?

Try

SELECT t.*
FROM temp_main_working t
WHERE NOT EXISTS ( SELECT 1
                   FROM temp_sf s
                   WHERE t.pm_order_id = s.pm_order_id )

UPDATE

id select_type        table partitions type   possible_keys key     key_len ref                rows filtered Extra
1  PRIMARY            t     null       index  null          PRIMARY 4       null               1    100      Using where; Using index
2  DEPENDENT SUBQUERY s     null       eq_ref PRIMARY       PRIMARY 4       test.t.pm_order_id 1    100      Using index


Primary server 'XYZ' can not write to shared drive during availability group configuration

PORT 5022 was not added to secondary node's firewall

After adding rule, it worked like charm

This is how I found the solution

Open command prompt

     Telnet NODE2 5022

This failed


How to restore operational Oracle 11g Database from full backup?

In short - yes, there are standard operating procedures for doing all of this with Oracle. You should start by looking into RMAN (Recovery MANager). I have put together a high level overview of RMAN as well as an introduction to Oracle backups for SQL Server DBAs. I suggest watching both of those and then heading over to the Oracle Database Backup and Recovery User's Guide to learn more about the options available to you.

Basically, though - RMAN can perform all of these tasks for you. If you need additional tooling, RMAN + Oracle Enterprise Manager can provide you with automation, cloning, and other reporting capabilities. Oracle Enterprise Manager Database Control (dbcontrol) will also be very helpful, it will give you additional web-based GUI tools.

To get to a point where you can get to restoring from a bare bones Windows server and cold backup file is going to take significant training. You need to work with the business to define effective RPO/RTO plans, budget training for the future, and then do what you can right now to mitigate the risks before you're stuck with an empty server.

Improving the original answer slightly...

You should start using Oracle Recovery Manager (RMAN) to back up your databases. Depending on how you configure RMAN, you may be sending this to NFS or to a set of disks on a different SAN. The way you configure this has a lot to do with your RPO/RTO policies. If you need to send this to a cool standby server, you should look into Oracle Data Guard. Depending on the configuration, this may take the place of either log shipping or an AlwaysOn Availability Group.
You can clone a database using RMAN, just make sure you have everything in place and you practice it.


How can I cap the total space that mysql / mariadb binary logs take up?

max_binlog_size has little effect on keeping disk space down; expire_logs_days is the one to worry about.

How fast are the logs filling up?

One way binlogs can fill up extremely fast is when you accidentally have the same server_id on multiple servers in a Replication setup.

Which version?  Which binlog_format?  Lots of multi-row UPDATEs/DELETEs?

More

expire_logs_days is the best available builtin tool.  There may be 3rd party monitoring tools (or you could write one) that watch for disk space.

In any system, you should have some kind of monitoring on disk space -- it is not just the binlog that can go berserk.  The "general log" and the slowlog can, also.  And your application can go berserk.  And so forth.


Your not exists sub-query doesn't work. It will always return a 1 if ANY employee exists that has a null statusChangeEndDate or that meets the criteria you are testing so basically will always exist. To continue using a Not Exists you'd need to make it correlated sub-query by adding e.employeeID = emp.employeeID so it is checking to see if that employee doesn't have a null record or a record for later than this year. Doing it that way probably wouldn't scale well for performance as the correlated sub-query needs to be run for every line in the results.

You could change it to

e.employeeid not in ( select emp.employeeid from Employee emp
join EmployeeStatus emps on emp.employeeID = emps.employeeID
        where
        (emps.statusChangeEndDate is null or
             (year(emps.statusChangeEndDate) = @year
               and month(emps.statusChangeEndDate) > @month))

Your attempt at SQL seems to have more criteria than your description as it is including people that have nothing in the EmployeeStatus table (left join and is null) and is trying to exclude people who have a change end date after the entered date or have a null change end date. That is subtly different than finding people who have a change end date before your entered date since people can have multiple changes.


Report subscription deletion failed due to FK constraint

I've been having the same problem for the last few weeks and had settled on manual deletes. I think I've cracked it today. I've compared the foreign key constraint [FK_ReportSchedule_Subscriptions] on the table Subscriptions to the same on a clean (working) install of ssrs. On my clean install the constraint is set with nocheck


Row level security in SQL Server 2016 with a normalised schema

The function can have table access, just remember that this will add additional overhead to every single query.

CREATE FUNCTION Security.fn_securitypredicate(@SalesRepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
      FROM dbo.SalesReps AS sr
      WHERE sr.SalesRepID = @SalesRepID
       AND (sr.name = USER_NAME() OR sr.name = 'Manager');
GO


Can a stored procedure reference the database in which it is stored?

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure.  In particular,

For static SQL in a stored procedure:

Unqualified object names will resolve relative to the schema containing the stored procedure.
Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).
Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.


You can use a outer SELECT or A cte to accomplish that
SELECT
    Document
    ,type
    ,SUM(Value1) AS Value1
    ,SUM(Value2) AS Value2
    ,OrderNum
    ,Year
FROM
    (SELECT
        a.ma   as  Document
        ,a.ca  as  type
        ,SUM(a.num) as Value1
        ,SUM(a.num2)as Value2
        ,coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum
        ,a.ti as Year
    FROM Orders a
    WHERE ti = 2020) t1

Group by
    Document
    ,type
    ,OrderNum
    ,Year


db_ddladmin vs db_owner

From what I can tell from what I tested and read up on, for the most part your list looks accurate except db_ddladmin DOES allow you to CREATE SCHEMA. I did confirm that the other security permissions you listed were indeed denied.

Denied with DDLADMIN only:

[ALTER ANY USER]

[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]

[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]

[DROP DATABASE]

Noting that the. . .

db_datareader will allow SELECT access to all tables
db_datarwriter will allow INSERT, UPDATE, and DELETE access to all tables
db_executor will allow EXECUTE access to all executable objects

Additonally, having db_ddladmin role permissions may mean. . .

  Note: Since you have so many different versions of SQL Server from 2005 - 2014, it may be best to have a small set of users test
  this initially to see who screams to iron out any kinks, etc.

Objects they own with this role will not be owned by DBO so you may have to deal with ownership chaning issues if there's ever a problem with something at this level. I'm not 100% certain that this would be a problem but it's worth mentioning just in case.

Source: Ownership Chains
With this role (may vary depending on the version of SQL Server) they may be able to add SQL security principles defined in the current DB to objects they own still, just not all objects (ones they do not own) nor add a new server-level defined principal to the DB level.

Additionally, not having DBO role permissions may mean. . .

  Note: Since you have so many different versions of SQL Server from 2005 - 2014, it may be best to have a small set of users test
  this initially to see who screams to iron out any kinks, etc.

Not having the DBO role may prevent certain SSMS designer GUI interfaces (SQL Server version varying) from populating or opening without error (e.g. when modifying tables or columns through the GUI) even though doing it via T-SQL works and the permissions are in place. In some versions of SQL Server this may be resolved by allowing GRANT VIEW DEFINITION where this is an issue and it can also just be a warning only on certain versions of SQL Server.

Resources

You are not logged in as the database owner or as a user that is a member of the db_owner role. You will not be able to save changes to tables that you do not own.
db_ddladmin Role doesn't allow use of "design" functions in SSMS

  "We try to prevent giving users/developers dbo in their QA databases
  as much as we can. One of the problems with this is that they still
  need to be able to create and modify database objects such as user
  tables. Many devs are new to MS SQL and thus tend to stick with the
  GUI (SSMS) for this sort of work. The problem arises when we grant
  them db_ddladmin (not dbo) and they are no longer able to modify
  tables or columns via the table designer GUI. Instead, they have to
  take additional time to learn the TSQL commands and their syntax (that
  they may never need again) or engage the DBA team which takes time
  away from our other activities.

  I don't know if this is a bug or a feature request but I consider it a
  bug since the user has sufficient permissions to alter the table via
  TSQL but the GUI gives them messages stating:

  "You are not logged on as the database owner or system
  administrator. You might not be able to save changes to tables that
  you do not own." AND "Table [schema].[table] is set to read only,
  user doesn't have enough rights on this table."

  A trace seems to point to the check being a is_member('db_owner')
  which will preclude members of db_ddladmin even though they do in fact
  have permissions to modify the object. Microsoft SQL Server Management
  Studio"

  Posted by Agent DBA on 1/25/2010 at 7:06 AM

  I had a similar issue and managed to solve it by performing the
  following grant

GRANT view definition on schema:: <schemaname> to <username>

Other Considerations

Since you state that this is being reviewed on a case-by-case basis

  One of the permissions currently being limited is db_owner permissions.

  This permission is being reviewed on a case-by-case basis, but a common change is to replace the db_owner permissions with the following:

  db_datareader
  db_datawriter
  db_ddladmin
  db_executor

Have you considered creating additional custom roles for more "all object" DB-level access that each person needs rather than granting them the db_ddladmin role as that will probably give them more than they actually need to DB level objects as well.

I usually give what's needed exactly and nothing more for them to do their job and if there's a "usual" or "standard" need for DB level object access to all objects in a DB, I create a custom DB role sort of like the the db_executor but see my below example. This way you can grant the people what they really need to ALL DB object in a particular DB if you're not getting object level explicit in your DBs for their security.

----Custom Database Roles

/* CREATE A NEW ROLE  -- Execute to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Execute
GRANT EXECUTE TO db_All_StoredProc_Execute

/* CREATE A NEW ROLE  -- Alter to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Alter
GRANT ALTER ANY SCHEMA TO db_All_StoredProc_Alter

/* CREATE A NEW ROLE  -- View Definition to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_View
GRANT VIEW DEFINITION TO db_All_StoredProc_View

/* CREATE A NEW ROLE - Any schema alter and create procedure permissions */
-- Database specific
CREATE ROLE db_All_CreateProc_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateProc_AlterSchema
GRANT CREATE PROCEDURE TO db_All_CreateProc_AlterSchema
GO

/* CREATE A NEW ROLE - Any schema alter and create table permissions */
-- Database specific
CREATE ROLE db_All_CreateTable_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateTable_AlterSchema
GRANT CREATE TABLE TO db_All_CreateTable_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create function permissions */
-- Database specific
CREATE ROLE db_All_CreateFunction_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateFunction_AlterSchema
GRANT CREATE FUNCTION TO db_All_CreateFunction_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create aggregate permissions */
-- Database specific
CREATE ROLE db_All_CreateAggregate_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateAggregate_AlterSchema
GRANT CREATE AGGREGATE TO db_All_CreateAggregate_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create view permissions */
-- Database specific
CREATE ROLE db_All_CreateView_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateView_AlterSchema
GRANT CREATE VIEW TO db_All_CreateView_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create schema permissions */
-- Database specific
CREATE ROLE db_All_CreateSchema_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateSchema_AlterSchema
GRANT CREATE SCHEMA TO db_All_CreateSchema_AlterSchema

I also wanted to share a db_DDLAdmin_Restriction role you may want to consider to consider creating otherwise with explicit DENY to restrict what db_ddladmin give access to so you could at least create this on the DBs where you grant them this role and set the explicit DENY for the actual object types, etc. you don't want them to have access to.

For example, if you know they will definitely be creating stored procedures and functions, you can exclude DENY CREATE FUNCTION, DENY CREATE PROCEDURE, DENY ALTER ANY SCHEMA.

---Create ddladmin restriction custom DB role
DENY ALTER ANY ASSEMBLY                    TO db_DDLAdmin_Restriction
DENY ALTER ANY ASYMMETRIC KEY              TO db_DDLAdmin_Restriction
DENY ALTER ANY CERTIFICATE                 TO db_DDLAdmin_Restriction
DENY ALTER ANY CONTRACT                    TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE DDL TRIGGER        TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO db_DDLAdmin_Restriction
DENY ALTER ANY DATASPACE                   TO db_DDLAdmin_Restriction
DENY ALTER ANY FULLTEXT CATALOG            TO db_DDLAdmin_Restriction
DENY ALTER ANY MESSAGE TYPE                TO db_DDLAdmin_Restriction
DENY ALTER ANY REMOTE SERVICE BINDING      TO db_DDLAdmin_Restriction
DENY ALTER ANY ROUTE                       TO db_DDLAdmin_Restriction
DENY ALTER ANY SCHEMA                      TO db_DDLAdmin_Restriction
DENY ALTER ANY SERVICE                     TO db_DDLAdmin_Restriction
DENY ALTER ANY SYMMETRIC KEY               TO db_DDLAdmin_Restriction
DENY CHECKPOINT                            TO db_DDLAdmin_Restriction
DENY CREATE AGGREGATE                      TO db_DDLAdmin_Restriction
DENY CREATE DEFAULT                        TO db_DDLAdmin_Restriction
DENY CREATE FUNCTION                       TO db_DDLAdmin_Restriction
DENY CREATE PROCEDURE                      TO db_DDLAdmin_Restriction
DENY CREATE QUEUE                          TO db_DDLAdmin_Restriction
DENY CREATE RULE                           TO db_DDLAdmin_Restriction
DENY CREATE SYNONYM                        TO db_DDLAdmin_Restriction
DENY CREATE TABLE                          TO db_DDLAdmin_Restriction
DENY CREATE TYPE                           TO db_DDLAdmin_Restriction
DENY CREATE VIEW                           TO db_DDLAdmin_Restriction
DENY CREATE XML SCHEMA COLLECTION          TO db_DDLAdmin_Restriction
DENY REFERENCES                            TO db_DDLAdmin_Restriction
GO


Connection problem different errors .net application

I think you may be experiencing TCP Port Exhaustion on the Web Server. Avoiding TCP/IP Port Exhaustion is a good starting place for understanding the issue. This is generally an application design/implementation issue.

https://msdn.microsoft.com/en-us/library/aa560610%28v=bts.20%29.aspx?f=255&MSPPError=-2147217396


Select first row (grouping) + add aggregate function

Answer to question
SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM   zones
GROUP  BY gid, name, zonedistrict_id, zonestyle_id, longname
ORDER  BY name, zonedistrict_id, zonestyle_id;
It depends on what you are actually trying to achieve and what version of Postgres you are using and the table definition of the underlying table. You chose not to disclose any of that.
This gives you one distinct row per (name, zonedistrict_id), but geom is only the aggregate over (gid, name, zonedistrict_id, zonestyle_id, longname)
In older versions you had to list every non-aggregated column of the SELECT list in the GROUP BY list. This changed somewhat with PostgreSQL 9.1. I quote the release notes:

Allow non-GROUP BY columns in the query target list when the primary
key is specified in the GROUP BY clause (Peter Eisentraut)

My guess
However, I suspect you secretly want this:
SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) OVER (PARTITION BY name, zonedistrict_id) AS geom
     , gid, name, zonedistrict_id, zonestyle_id, longname
FROM   zones
ORDER  BY name, zonedistrict_id, zonestyle_id;
Using a ST_Union(geom) as aggregate window function. So geom is actually the aggregate over (name, zonedistrict_id), which seems more likely.
This should be possible since the PostGis manual tells us about its aggregate functions:

... can be used just like any other sql aggregate function such as sum, average.

And SQL aggregate functions can be used as window functions.


Impact on SSRS of migration of SQL Server from 2008 to 2012

Your SSRS deployment just needs access to two databases ReportServer and ReportServerTempDB. It doesn't matter where they are hosted as long as they are accessible.

When you move these databases to the new server you will need to open Reporting Services Configuration Manager and redirect SSRS to the new SQL 2012 server. You can change the connection on the database tab.

If the connection test goes through OK then you should be ready to go.

One thing to make sure of is that all the logins from the old server are present on the new server before transferring the databases. If they are not the permissions for any missing users will be dropped.


To see how long a report takes to load, and what stage is taking up the time use this in SSMS:

USE ReportServer
SELECT * FROM ExecutionLog3 ORDER BY TimeStart DESC

This will show you a log of the activity for each report being used.
you will be able to see start time, end time (and calculate time taken), which parameters are being used, and the time taken for each of the stages of report loading (TimeDataRetrieval, TimeProcessing, TimeRendering) in milliseconds. Further details relating to the query here

Use this link to troubleshoot each stage, as per your findings


postgresql: How to disable all indexes of a table

From the Comment, you are doing a bulk import using select..

PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows.  The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).

  If you are adding large amounts of data to an existing table, it might
  be a win to drop the indexes, load the table, and then recreate the
  indexes. Of course, the database performance for other users might
  suffer during the time the indexes are missing. One should also think
  twice before dropping a unique index, since the error checking
  afforded by the unique constraint will be lost while the index is
  missing.

You can Check the system catalogue to disable an index:

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass

This means that the index won't be used for queries but will still be updated. It's one of the flags used for concurrent index building.
If you set indisready to false, that will disable updates.

If you want to disable all for a single query

BEGIN;
DROP INDEX foo_ndx;
EXPALIN ANALYSE SELECT * FROM foo;
ROLLBACK;

You can do this in a transaction to make recovering from it dead simple. You can also disable indexscan to disable all indices.
Also, make sure you are doing EXPALIN ANALYSE on your queries.


Remove all values of a key in PostgreSQL hstore

Something like this, pages is the table, setting is the column, and key is what you are setting to null.

update pages set settings = settings || '"key"=>null'::hstore;


How can I have SQL Server send Job Notification alerts to an API

So you need SQL Server to make an HTTP call. There's nothing in T-SQL to do this directly. You will need to engage one of the general-purpose programming embeddings it supports.
Your own suggestion of calling PowerShell from a new job step will work. If there are parameters to pass from the job step that does the work to the one that makes the HTTP call this becomes a little awkward but there are ways to achieve it.
Since SQL Server 2005 stored procedures can be written in any .Net language. The code can implement anything any other .Net program can, including HTTP calls. There are a few things to be wary of, though. It is possible to write code which can crash SQL Server itself. An HTTP request can take a long time to complete. If performed synchronously this will hold open the current transaction and any active locks. This can affect concurrency badly. It may be better to use SQL CLR to write to a queue which then asynchronously calls the API.
More recently support has been added for code written in Python, R and Java. Any of these should be able to make HTTP requests.
For the truly brave COM objects can be instantiated directly from T-SQL using the sp_OA* procedures. Here's an implemetation. It has all the ease of assembly combined with the clarity of linear B. I've used this once in my career. That was enough.
For my two cent's - if the use-case is a simple "this job has ended" I'd go with the job step / PowerShell combination.


Should all queries where you expect a specific order, include an ORDER BY clause?

I had a simpler repro in mind:
CREATE TABLE #x(z CHAR(1));
CREATE TABLE #y(z CHAR(1));

INSERT #x SELECT 'O';
INSERT #x SELECT 'R';
INSERT #x SELECT 'D';

INSERT #y SELECT 'E';
INSERT #y SELECT 'R';

SELECT z FROM #x
UNION ALL
SELECT z FROM #y;

Results:
O
R
D
E
R

Now add an index:
CREATE CLUSTERED INDEX z ON #x(z);

SELECT z FROM #x
UNION ALL
SELECT z FROM #y;

Results:
D -|
O -|- ordered based on the clustered index, not how originally inserted
R -|
E
R

This still produces the first three rows from #x and the last two rows from #y, so still doesn't prove that SQL Server might return those entire queries in a different order than physically laid out in the query (just that you can't rely on the ordering inside those sets). But clearly this mythical "observe an order once, and it will always be true" statement needs to be nipped.
Bottom line: do not rely on observed order. If you want a certain ordering, say so using ORDER BY.
Also, please read this post by Conor Cunningham, a pretty smart guy on the SQL team.


Implementing a one to one (1:1) relationship between Person and User

I have a person table.

Fine (just changed the name of the PK column):

person
------
person_id    PK
firstname
lastname
email
telephone
...
...

  and a user table as a child. They must be 1:1 relationship, because an user cannot be repeated twice.

If the relationship is 1:1 (assuming that person is the supertpe and user is the subtype, you can handle this with having the same column both as Primary Key and Foreign Key to person:

user
----
person_id  PK  FK to person(person_id)
password
username
screenname
...
...

  and so the email in the person row must not be repeated twice.

Add a UNIQUE constraint in the person.email column.

  Then I have this message table which stores messages from anyone,
  but you can see that firstname,lastname, email,telephone are duplicated in message table.
  so I am thinking to refer it to person table like this below.

Fine (adjusted to previous changes). But you also need to store who sends a message and who is the receiver:

message
-------
message_id   PK
sender_id    FK  to user(person_id)         --- or to person(person_id)
receiver_id  FK  to user(person_id)         --- that depends on your requirements
subject
content
...


How to reference categories table in products table?

I see from your diagram and from the columns named lft and rgt that you are using the nested sets technique for organizing the categories into a tree.  So far so good.

Now all you need to do is add a foreign key to the products table that references category id in the categories table.  You might as well call it "category_id", same as the column it references.  The benefit of declaring it as a foreign key is that the DBMS will now prevent the introduction of products with an invalid category_id.  It's up to you in the application to provide a valid category_id at product insert time.

All of this is very elementary relational database design.  If it's covering material you already know, then please reword the question to make it clear just where you need an answer.


Queries are faster in SSMS 2008R2 as compared to SSMS 2016

Changing the below setting and uninstalling some add ons for SSMS 2016 helped me -

HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\13.0\UserFeedbackOptIn

Thanks @Kin.


Finding the most commonly used non-stop words in a column

Your first query was pretty close. To remove the unwanted stemming, create a text search configuration with a simple dictionary that does not do it.
I suggest to use a separate schema for text search objects, but that's totally optional:
CREATE SCHEMA ts;
GRANT USAGE ON SCHEMA ts TO public;
COMMENT ON SCHEMA ts IS 'text search objects';

CREATE TEXT SEARCH DICTIONARY ts.english_simple_dict (
    TEMPLATE = pg_catalog.simple
  , STOPWORDS = english
);

CREATE TEXT SEARCH CONFIGURATION ts.english_simple (COPY = simple);
ALTER  TEXT SEARCH CONFIGURATION ts.english_simple
   ALTER MAPPING FOR asciiword WITH ts.english_simple_dict;  -- 1, 'Word, all ASCII'

Then your query works, and very fast, too:
SELECT *
FROM   ts_stat($$SELECT to_tsvector('ts.english_simple', title) FROM item$$)
ORDER  BY ndoc DESC
LIMIT  50;

dbfiddle here
This operates with lower case words without stemming and doesn't break for non-ASCII letters.
Backgroud
Read the chapter Simple Dictionary in the manual.
The exact definition of a "word" is a tricky matter. The default text search parser (currently it's the only one) identifies 23 different types of tokens. See:
SELECT * FROM ts_token_type('default');

Built-in text search configurations map most of those to (built-in) dictionaries. Mappings for the english config:
SELECT tt.*, m.mapdict::regdictionary AS dictionary
FROM   pg_ts_config_map m
LEFT   JOIN   ts_token_type(3722) tt ON tt.tokid = m.maptokentype
WHERE  mapcfg = 'english'::regconfig  --  'ts.english_simple'::regconfig
ORDER  BY tt.tokid;

The demo above creates a new config based on the simple config, and since all English stop words are of type 'asciiword', we only need to map this type to remove stop words, no stemming or anything else.


ORA-00119 & ORA-00130 when trying to startup Oracle

Well, my teammate found the answer to the problem.

He changed some details in the file 'tnsnames.ora' at ORACLE_HOME\NETWORK\ADMIN. He just replaced “hostname” by “localhost”.

WP5 =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = WP5)
    )
  )

LISTENER_WP5 =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

A new startup (without leaving sqlplus) made it work!

PS: We still have no ideia why/how it was working before, since the file updated date dates from about 2 weeks ago.


Fastest engine for Mass Updates (only updates done on table)

Your question is somewhat like a question I answered back on Sep 06, 2013 : mySQL for analytics and updating same row

In that answer, I recommended the following

Adding a third column to the table to represent a slot
Select an arbitrary value for the number of slots
Making a composite PRIMARY KEY

hour boundary
slot number

Doing an INSERT with ON DUPLICATE KEY UPDATE to increment the counter

Since you are doing 30-40 million updates, you may need to choose a very high number of slots. In your case, I would go with 250,000 slots. The high the number of slots, the lower the incidence of collision (trying to update the same slot).

As far as the choice of storage engine, you cannot stay with MyISAM. Why ? MyISAM will do a full table lock for every update. Doing 40 million updates require 40 million table locks. My posted answer works well with InnoDB's row level locking. Even if you implement the slot paradigm, MyISAM will still do 40 million table locks with 40 million updates.

Definitely, you must go with InnoDB. You also need to tune InnoDB.

Larger Buffer Pool (increasing innodb_buffer_pool_size)
Larger Log Buffer (increasing innodb_log_buffer_size to 128M)
More Write I/O Threads (setting innodb_write_io_threads to 8 or more)

If you are using MySQL 5.1, upgrade to MySQL 5.6.

Give it a Try !!!

UPDATE 2014-09-15 00:21 EDT

I learned the concept of slots for counters from page 140 of the Book

My post (dated Sep 06, 2013) is the first post in the DBA StackExchange to mention this book for your specific issue.
 Someone else mentioned this technique one month later in this same forum (counter table in mysql dates Oct 18, 2013)

I just tried googling for this concept search for mysql counter slot

I found some posts:

Baron Schwartz, one of the authors of the book, has a URL to that very reference
Avoid locks when storing counters in MySQL

The main point I am making is that this concept is not new. You can implement it.

Give it a Try !!!


Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)?

So, firstly have a quick look through this which explains virtual accounts:

TechNet: Service Accounts Step-by-Step Guide

To answer your questions:

1/ You can't list these virtual accounts in that dialog box, I'm not 100% sure of the reason, but you always need to type them in manually, in your screenshot you also have the active directory domain listed, you would never see them in here as they are local accounts.

2/ You can't add permissions on files/shares on another machine by finding these accounts, as they live only on the machine that you installed SQL server on (NVOSIB-1C in your case).  These accounts though can access resources on the network, but they do so in the context of the machine account of the server.

So, an example would be you have NVOSIB-1C with SQL Server installed and NVOSIB-Filer with a share on it that you want the SQL Server account to be able to access.

What you would do in this situation is to add the machine account of the SQL server to the permissions on the file share, you can do this just by assigning the account DOMAINNAME\NVOSIB-1C$ the permissions you want and then as the virtual account accesses the network in the context of the machine it lives on, it would have permission to do so.

Obviously this limits your ability somewhat to assign permissions, as anything running on NVOSIB-1C that authenticated in the context of the server would now have access to the share, if this is a concern, you can do one of 2 things:

Use standard domain service accounts for the SQL Server services, you then need to maintain passwords etc for these, but it makes assigning permissions somewhat easier.
Set up a managed service account in AD, this has the advantage that the local accounts have in that you don't need to worry about passwords, and also the advantage that you can see and manage them centrally in AD like a normal account, the main disadvantage is that you need to have the managed service account created before you install SQL server, so in your case you would have to reinstall to take advantage of this.


Using psql, how to test if I am using streaming, hot_standby or warm replication?

Streaming does use WAL.  It obtains the WAL by streaming it, rather than (or in addition to) shipping entire WAL files at a time.  Also, the choice of hot_standby versus warm standby is independent of the choice of streaming versus log shipping.  It would be unusual to use hot_standby without also using streaming in the modern age, but it is perfectly possible to do so.

On the master, you can see if there are active receivers of WAL streaming by looking at pg_stat_replication.  If there are no entries, than no one is currently streaming wal.

For log shipping, you can look in archive_mode.  If that is turned off (or if it turned on but archive_command is some dummy command), then no one can be receiving the shipped log files as they are not being archived in the first place.  If it is on and archive_command is sensible, then someone might be doing something with the archived logs files.  The master server has no direct way of knowing whether someone is or is not.

In either case, the master doesn't generally know what they are doing with WAL.  They could be using the WAL for warm standby, they could be using it for hot standby, they could be storing it cold for future use, they could be just tossing it in the trash. Some of the fields in pg_stat_replication can give you some clues about what is going on, but who knows, the client programs might be lying.  And for log shipping, there is no way to know anything.


SSMS IntelliSense not working with sa user

When you create a user in sql, it is generally assigned the default bd you want to work with, that's why the IntelliSense works for you, although I think you shouldn't use the SA user for that, you can make the IntelliSense work by calling the bd with which you want to make inquiries ...
use [yourdb]
select * from yourtable


How do you copy a table from MySqlServer_A to MySqlServer_B?

I tend to shy away from the FEDERATED storage engine for three(3) reasons:

It only supports MyISAM
DDL performed on the source table requires manually changing the FEDERATED table design on external services.
Bulk operations against a FEDERATED table can become an instant mightmare !!!

What to do ???

One possibility is to use mysqldump and merge them

EXAMPLE
Assuming all tables have identical structures
Assuming all tables are not using auto_increment IDs
Assuming all tables are InnoDB

On the server that will combine the three tables, create four(4) import databases (one time operation):

CREATE DATABASE ImportDB1;
CREATE DATABASE ImportDB2;
CREATE DATABASE ImportDB3;
CREATE DATABASE ImportCombined;

Import Process Goes Something Like This

mysqldump the three(3) tables

mysqldump -hHOSTIP1 -Pportnumber1 -u... -p... dbname tbname > /root/ImportTable1.sql &
mysqldump -hHOSTIP2 -Pportnumber2 -u... -p... dbname tbname > /root/ImportTable2.sql &
mysqldump -hHOSTIP3 -Pportnumber3 -u... -p... dbname tbname > /root/ImportTable3.sql &
wait

Load the mysqldumps into the separate import databases in parallel:

mysql -hHOSTIP4 -u... -p... -A -DImportDB1 < /root/ImportTable1.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB2 < /root/ImportTable2.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB3 < /root/ImportTable3.sql &
wait
rm -f /root/ImportTable1.sql &
rm -f /root/ImportTable2.sql &
rm -f /root/ImportTable3.sql &
wait

Next, combine the data.

DROP TABLE IF EXISTS ImportCombined.tbname;
CREATE TABLE ImportCombined.tbname LIKE ImportDB1.tbname;
ALTER TABLE ImportCombined.tbname DISABLE KEYS;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB1.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB2.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB3.tbname;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;

Now, perform the task you already have in place against the ImportCombined.tbname table.

"You fill in these steps"

Then, when the table is ready to be exported, mysqldump it and copy 2 times:

mysqldump -hHOSTIP4 -Pportnumber4 -u... -p... ImportCombined tbname > /root/ExportTable1.sql
cp /root/ExportTable1.sql /root/ExportTable2.sql &
cp /root/ExportTable1.sql /root/ExportTable3.sql &
mysql -hHOSTIP1 -Pportnumber1 -u... -p... -A -Ddbname < /root/ExportTable1.sql &
mysql -hHOSTIP2 -Pportnumber2 -u... -p... -A -Ddbname < /root/ExportTable2.sql &
mysql -hHOSTIP3 -Pportnumber3 -u... -p... -A -Ddbname < /root/ExportTable3.sql &
wait
rm -f /root/ExportTable[123].sql &

As show from these steps, these things should be shell scripted and perhaps crontab'd.

Here is another maneuver: If the tables are MyISAM, you could copy the MyISAM tables (.frm, .MYD, and .MYI files for each table) in other folders (databases in the eyes of mysqld) instead of doing mysqldumps, and perform similar operations as mentioned before. Make sure no writes to the tables take place during the copy.


As per my knowledge you cannot reclaim your data using MONGO. But you can go for some other third party tools that can help to recover data from your hard disk.

I hope this link will help linux data recover on unix.stackexchange.com


Deleting with NOT EXISTS vs NOT IN gives different results

Your address_id columns contain nulls, and thus you got caught by the three-valued logic of SQL.

https://www.postgresql.org/docs/current/static/functions-subquery.html#functions-subquery-in describes the behaviour of IN:

  Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.

And of course NOT applied to null is null, not true, see https://www.postgresql.org/docs/current/static/functions-logical.html


How can a MySQL delete statement affect a table with over 30 millions records?

Instead of deleting a large percentage of the rows you might better negate your WHERE-condition and INSERT/SELECT the remaining rows into a new table. Then DROP the original table and RENAME new to old (or TRUNCATE and re-INSERT). This way there's only a very small period (DROP/RENAME) where the table is not available. It's a very common process in a Data Warehouse where you deal with really large numbers of rows.

You need to run both the INSERT/SELECT and the DROP TABLE within a single transaction, so no other session is able to do any DML between the INSER and the DROP. I don't know the exact mySQL/InnoDb syntax and transaction behavior, but this is a skeleton (of course you need to test before):

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; --
START TRANSACTION;
LOCK TABLES newTab WRITE;
INSERT INTO newTab SELECT * FROM oldTab;
DROP TABLE oldTab;  -- might need a COMMIT?;
RENAME newTab TO oldTab;
COMMIT; -- ?


Most double entry accounting systems would implement this using 2 tables. One table that is the "header" table, which stores common fields & a second "lines" table that stores the actual detail of the transaction.

In case you didn't know, in double entry accounting, the sum of all transaction lines in a given transaction always adds up to zero.

Anyway, based upon your current design, I'd do something along the lines of:

Transaction
------------------
TransactionID
CurrencyID
Remarks
TransactionDateTime

TransactionLines
----------------
TransactionID
LineNo
UserID
DebitAmount
CreditAmount

To transfer $5.00 from UserID 100 to UserID 250, you'd have the following entries:

Transaction:

10000, 'USD', 'Test transaction', '2019/09/15 12:34:56'

TransactionLines:

10000, 1, 100, 500, 0
10000, 2, 250, 0, 500

You asked about a case where there might be multiple transaction lines:- To transfer $9.00 from UserID 100, with $8.50 going to UserID 123 & $0.50 going to UserID 456, you'd have the following entries:

Transaction:

10001, 'USD', 'Test transaction', '2019/09/15 12:34:56'

TransactionLines:

10001, 1, 100,   900,   0
10001, 2, 123,   0,     850
10001, 3, 456,   0,     50

It doesn't matter from a mathematical/accounting point of view what order the lines are in, as long at the total is always net 0.

Proper accounting systems would have a G/L Account column in each line, which would dictate where the transaction posted to, along with the posting period and other essential information at the header level.

I'd seriously consider learning some accounting fundamentals if the system you're developing is dealing with real money.

This is obviously at a high level. If you require further help, please just ask.


The best thing to do to resolve your question is to run the lsblk command. Without any arguments, it gives a great tree view of the system's block devices (see below). Check the man page (lsblk(8)).

Then, use df -h on your file of interest (in this example, it's the MySQL server). df -ah gives more information - in this case, the mount point and the filesystem.

[pol@localhost inst]$ df -ah ./bin/mysqld
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-home   17G   16G  189M  99% /home
[pol@localhost inst]$

So, this tells us that mysqld is mounted on /home on the logical volume /dev/mapper/fedora-home. By checking the output of lsblk, it can be seen (check below) that it's on the sd5 partition of the sda device.

[EDIT - I've added new commands - and I'm giving this question a +1, since I've learnt a truckload trying to answer all of its possiblities! Check out the threads here (1, 2)]

[EDIT2]

Doing yet more exploring, I found these two threads (1, 2) - if you really want to get "down and dirty" with your file system :-). I won't list the output of all the commands found in them - just show some of the interesting ones (hopefully from your perspective). Search for the section also marked [EDIT2] below.

[EDIT3]

Check the end of this post for information I could find on SunOS/Solaris. Search for the section marked [EDIT3].

===============================

Sample output of df -h Note that without specifying the file, it gives the result for the entire operating system, but not the entire computer. I also have a Windows OS on the same machine, which does appear with the lsblk command.

[pol@localhost inst]$ df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-root   34G   31G 1015M  97% /
devtmpfs                 994M     0  994M   0% /dev
tmpfs                   1002M   12M  990M   2% /dev/shm
tmpfs                   1002M  9.0M  993M   1% /run
tmpfs                   1002M     0 1002M   0% /sys/fs/cgroup
tmpfs                   1002M  308K 1001M   1% /tmp
/dev/mapper/fedora-home   17G   16G  193M  99% /home
/dev/sda3                477M  141M  307M  32% /boot
/dev/sdc1                466G   53G  414G  12% /run/media/pol/INTENSO
[pol@localhost inst]$ df -h /home
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-home   17G   16G  193M  99% /home
[pol@localhost inst]$ df -h /bin
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-root   34G   31G 1015M  97% /
[pol@localhost inst]$

So, in your particular case, you want:

`df -h  /dev/rdsk/c0t0d0s1`

and

`df -h  /dev/rdsk/c0t3d0s4`

For logical volumes, use the lvs command - as detailled in this post for more info.

i.e.

sudo lvs -o +devices /dev/rdsk/c0t3d0s4 &c.

Sample on my CentOS system:

sudo lvs -o +devices /dev/mapper/fedora-root
[sudo] password for pol:
  LV   VG     Attr       LSize  Pool Origin Data%  Move Log Cpy%Sync Convert Devices
  root fedora -wi-ao---- 34.06g                                              /dev/sda5(4773)
[pol@localhost inst]$

So, /dev/mapper/fedora-root is on sda5.

On further investigation, I also found this thread and found 3 futher commands.

You could also look at the output of

`sudo fdisk -l`

and

`more /proc/diskstats`

And lshw -class disk -short or (verbose) lshw -class disk

Sample of lsblk output (really nice tree output!):

[pol@localhost inst]$ lsblk
NAME                          MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda                             8:0    0 111.8G  0 disk
├─sda1                          8:1    0   100M  0 part
├─sda2                          8:2    0  58.5G  0 part
├─sda3                          8:3    0   500M  0 part /boot
├─sda4                          8:4    0     1K  0 part
└─sda5                          8:5    0  52.7G  0 part
  ├─fedora-swap               253:0    0     2G  0 lvm  [SWAP]
  ├─fedora-root               253:1    0  34.1G  0 lvm  /
  └─fedora-home               253:2    0  16.6G  0 lvm  /home
sdc                             8:32   0 465.8G  0 disk
└─sdc1                          8:33   0 465.8G  0 part /run/media/pol/INTENSO
sr0                            11:0    1   1.5G  0 rom
sr1                            11:1    1    97M  0 rom
loop0                           7:0    0   100G  0 loop
└─docker-253:1-1064987-pool   253:3    0   100G  0 dm
  └─docker-253:1-1064987-base 253:4    0    10G  0 dm
loop1                           7:1    0     2G  0 loop
└─docker-253:1-1064987-pool   253:3    0   100G  0 dm
  └─docker-253:1-1064987-base 253:4    0    10G  0 dm
[pol@localhost inst]$

Sample of sudo fdisk -l:

[pol@localhost inst]$ sudo fdisk -l

Disk /dev/sda: 111.8 GiB, 120034123776 bytes, 234441648 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x000bbe80

Device    Boot     Start       End   Blocks  Id System
/dev/sda1 *         2048    206847   102400   7 HPFS/NTFS/exFAT
/dev/sda2         206848 122882047 61337600   7 HPFS/NTFS/exFAT
/dev/sda3      122882048 123906047   512000  83 Linux
/dev/sda4      123906048 234441647 55267800   5 Extended
/dev/sda5      123908096 234440703 55266304  8e Linux LVM

Disk /dev/mapper/fedora-swap: 2 GiB, 2164260864 bytes, 4227072 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/mapper/fedora-root: 34.1 GiB, 36570136576 bytes, 71426048 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/mapper/fedora-home: 16.6 GiB, 17855152128 bytes, 34873344 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/sdc: 465.8 GiB, 500107859968 bytes, 976773164 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0xf29eb7a1

Device    Boot Start       End    Blocks  Id System
/dev/sdc1       2048 976768064 488383008+  c W95 FAT32 (LBA)

Disk /dev/mapper/docker-253:1-1064987-pool: 100 GiB, 107374182400 bytes, 209715200 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 65536 bytes

Disk /dev/mapper/docker-253:1-1064987-base: 10 GiB, 10737418240 bytes, 20971520 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 65536 bytes
[pol@localhost inst]$

and sample of more /proc/diskstats:

[pol@localhost datamodeller]$ more /proc/diskstats
  11       0 sr0 29 0 232 4629 0 0 0 0 0 4629 4629
   8       0 sda 410458 263717 20392196 10898025 1245895 706584 24393828 59601990 0 10815598 70583361
   8       1 sda1 117 110 1816 3037 0 0 0 0 0 2773 3037
   8       2 sda2 111 110 1768 2632 0 0 0 0 0 2528 2632
   8       3 sda3 246 563 3636 6071 7 1 28 275 0 4779 6346
   8       4 sda4 6 0 30 156 0 0 0 0 0 156 156
   8       5 sda5 409862 262815 20383066 10884215 1066584 706583 24393800 52959483 0 5001674 63942507
 253       0 dm-0 147434 0 1179472 2314861 233219 0 1865752 675534980 0 437014 677849853
 253       1 dm-1 402913 0 16674730 12727464 543716 0 7478728 27512250 0 3889832 40239738
 253       2 dm-2 126146 0 2528176 2158551 1064161 0 15049320 37801569 0 7863269 39960160
  11       1 sr1 0 0 0 0 0 0 0 0 0 0 0
   8      16 sdb 0 0 0 0 0 0 0 0 0 0 0
   8      32 sdc 4698 120153 459862 36086 3 0 3 4 0 12517 36087
   8      33 sdc1 4633 119985 458930 32188 3 0 3 4 0 10451 32189
   7       0 loop0 0 0 0 0 0 0 0 0 0 0 0
   7       1 loop1 0 0 0 0 0 0 0 0 0 0 0
 253       3 dm-3 4412 0 100098 19616 88640 0 1041112 849625 0 46499 869245
 253       4 dm-4 367 0 2936 11243 74438 0 595472 166950100 0 22800 166961349
[pol@localhost datamodeller]$

Sample of sudo lshw -class disk -short:

[pol@localhost inst]$ sudo lshw -class disk -short
H/W path                 Device         Class          Description
==================================================================
/0/100/1d.7/1/2/0.0.0    /dev/sdc       disk           500GB External USB 3.0
/0/100/1d.7/1/3/0        /dev/sr1       disk           Mass Storage
/0/100/1d.7/1/3/0/0      /dev/sr1       disk
/0/100/1d.7/1/3/1        /dev/sdb       disk           SCSI Disk
/0/1/0.0.0               /dev/cdrom     disk           DVD-RAM UJ-850S
/0/1/0.0.0/0             /dev/cdrom     disk
/0/2/0.0.0               /dev/sda       disk           120GB TOSHIBA MK1234GS

Or a bit more verbose:

[pol@localhost inst]$ sudo lshw -class disk
  *-disk
       description: SCSI Disk
       product: External USB 3.0
       vendor: Intenso
       physical id: 0.0.0
       bus info: scsi@67:0.0.0
       logical name: /dev/sdc
       version: 0101
       serial: E1407473
       size: 465GiB (500GB)
       capabilities: partitioned partitioned:dos
       configuration: ansiversion=6 logicalsectorsize=512 sectorsize=512 signature=f29eb7a1
  *-cdrom
       description: SCSI CD-ROM
       product: Mass Storage
       vendor: HUAWEI
       physical id: 0
       bus info: scsi@65:0.0.0
       logical name: /dev/sr1
       version: 2.31
       capabilities: removable audio
       configuration: ansiversion=2 status=ready
     *-medium
          physical id: 0
          logical name: /dev/sr1
          capabilities: partitioned partitioned:mac
  *-disk
       description: SCSI Disk
       physical id: 1
       bus info: scsi@66:0.0.0
       logical name: /dev/sdb
       configuration: logicalsectorsize=512 sectorsize=512
  *-cdrom
       description: DVD-RAM writer
       product: DVD-RAM UJ-850S
       vendor: MATSHITA
       physical id: 0.0.0
       bus info: scsi@0:0.0.0
       logical name: /dev/cdrom
       logical name: /dev/sr0
       version: 1.20
       capabilities: removable audio cd-r cd-rw dvd dvd-r dvd-ram
       configuration: ansiversion=5 status=ready
     *-medium
          physical id: 0
          logical name: /dev/cdrom
  *-disk
       description: ATA Disk
       product: TOSHIBA MK1234GS
       vendor: Toshiba
       physical id: 0.0.0
       bus info: scsi@2:0.0.0
       logical name: /dev/sda
       version: AH00
       serial: X6PHF4EUS
       size: 111GiB (120GB)
       capabilities: partitioned partitioned:dos
       configuration: ansiversion=5 logicalsectorsize=512 sectorsize=512 signature=000bbe80
[pol@localhost inst]$

Doing yet more exploring, I found these two threads (1, 2) - if you really want to get "down and dirty" with your file system :-). I won't list the output of all the commands found in them - just show some of the interesting ones (hopefully from your perspective).

[EDIT2].

filefrag - report on file fragmentation.

stat - display file or file system status.

pvs - report information about physical volumes.

dmsetup - low level logical volume management.

For RAID devices, you might want to look at md/mdmon/mdadm /proc/mdstat.
md - Multiple Device driver aka Linux Software RAID.

[EDIT3]

From here and here, it appears that SCO Unix has very little to do with SunOS 5/Solaris. I assume that you are in fact using Solaris of some sort - SunOS hasn't been released under that name for more than 2 decades.

When you say "database device" - do you mean the data files or the server software itself? What RDBMS are you using? Maybe your RDBMS's documentation could help in this regard? And/or newsgroups/lists specialising in that particular server?

I'm afraid I don't have a working version of Solaris to hand (soon - I mean to challenge myself! :-)). I Googled "Solaris which disk is a file on" and found several links which might be useful. Also, check out @mustaccio 's post and this link in it.

I do mean to get into ZFS (are you using that?) and all of this (horribly complex) disk config stuff in "proper" Unix :-), but that's all the help I can be at the moment I'm afraid.


Unable to change servername after VM Clone

You need to check both sys.remote_logins and sys.linked_logins. You should be able to build a script dynamically to drop the set you find:

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

SELECT @sql += N'EXEC sys.sp_droplinkedsrvlogin @s, N''' + remote_name + N''''
  FROM sys.linked_logins
  WHERE server_id = 0 AND remote_name IS NOT NULL;

SELECT @sql += N'EXEC sys.sp_dropremotelogin @s, N''' + remote_name + N''''
  FROM sys.remote_logins
  WHERE server_id = 0 AND remote_name IS NOT NULL;

PRINT @sql; -- spot check will only show first 8K of command

DECLARE @s NVARCHAR(512) = N'old server name';
--EXEC sys.sp_executesql @sql, N'@s NVARCHAR(512)', N'.\SQL2014';


I can not find any document regarding to shared_heap_size

Based on the description, this is related to Amazon Aurora product, and not PostgreSQL by itself.

See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.AuroraPostgreSQL.html

  Amazon Aurora PostgreSQL is a fully managed, PostgreSQL-compatible, relational database engine that combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. Aurora PostgreSQL is a drop-in replacement for PostgreSQL

So Amazon added some settings.

You will need to contact them directly and ask since their documentation does not provide a result when doing a search, https://docs.aws.amazon.com/search/doc-search.html?searchPath=documentation-guide&searchQuery=shared_heap_size&x=0&y=0&this_doc_product=Amazon+Relational+Database+Service&this_doc_guide=User+Guide&doc_locale=en_us#facet_doc_product=Amazon%20Relational%20Database%20Service&facet_doc_guide=User%20Guide gives Your search for "shared_heap_size" returned no results.


As the error message says.
When you use GROUP BYand ONLY_FULL_GROUP_BY is enabled(which should always be:
Then all Columns of the SELECT have to be in the GROUP BY or has to have a aggregation function
So your query should be
SELECT u.user_id, MIN(uc.first_name), MIN(uc.last_name), GROUP_CONCAT(ur.role_name) roles
 FROM user u
  LEFT JOIN user_contact uc ON u.user_id = uc.user_id
  LEFT JOIN user_role_mapping urm ON urm.user_id = u.user_id
  LEFT JOION user_role ur ON ur.role_cd = urm.role_cd
  WHERE urm.org_id = 1
 GROUP BY u.user_id;


Index partitioned table to prevent Postgres from doing a sequential scan

Try adding the partition key explicitly (redundantly!). Like:

SELECT *
FROM   entries
WHERE  identifier = 'some_identifier_from_subtable_s'
AND    lower(left(identifier, 1)) = 's'  -- 1st letter of above identifier
LIMIT  1;

This should allow Postgres to understand it can prune all other partitions from the query.

Can be derived from $1 of course:

AND    lower(left(identifier, 1)) = lower(left($1, 1))

You'll still see a sequential scan on the one partition, unless you create an index like you had in mind:

CREATE INDEX ident_idx on entries(identifier);

That works in Postgres 11 or later because, quoting the manual:

  When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes.

In Postgres 10 or older you have to create indexes per partition.

You may have to run ANALYZE on the table after creating the index if you follow up with the  query immediately, before autovacuum had time to kick in.


Long full scan hasn't returned but session is gone from V$SESSION

It seems you didn't reference the /*+ FULL(t) */ hint alias while calling target table. You created the table with the same name as the alias. I'm not sure if that might be the problem... But the docs example follow this structure:

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM employees e
  WHERE last_name LIKE :b1;

So, what happens if you instead try:

SELECT /*+ full(t) */  Count(1) n
FROM t t WHERE start_time BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 23:59:59'

?


Build a select query that includes aggregated values from a different table

You can use filtered aggregation for this:
select v.poll_id,
       count(*) filter (where v.option = p.option_a) as option_a,
       count(*) filter (where v.option = p.option_b) as option_b,
       max(p.created_at) as created_at
from votes v
  join polls p on p.id = v.poll_id
group by v.poll_id
order by v.poll_id;

The max(p.created_at) is necessary to make the group by happy.
Online example


Migrating system and user databases to a new server by moving the LUN

You will not be able to detatch system databases. If they are not open to properly migrating the server level objects to another server, then I would opt for restoring system databases over trying to replace them. Ensure you are on the same, exact build number or master will not restore. You will also likely have to update the server name in master, if your new server does not have the same hostname.

I would also insiste on a backup of every user databae before that LUN is moved anywhere. (Just in case.)


When hash indexes should be used in Oracle?

You'd use a hash value as an identifier for rows of data whenever that's "practical", i.e. you actually need a simple identifier for all your rows, there are no candidate (natural) key fields that are practical (too wide for example), and an ordinary (sequential for instance) generated identifier doesn't cut it (for instance you need that row identifier to be "global" - if the same row was created in two distinct databases, they should have the same identifier).

(One non-trivial example of such a thing would be Git. Each object stored in a git repository is uniquely identified by a SHA-1 hash, which is pretty handy to refer unambiguously to a given commit.)

If you need to use something like that, indeed you'll need to create it yourself with Oracle (adding that hash as a column to the table and indexing it, or with a function-based index).

You could go even further by creating your very own index type with application domain indexes if a plain hash isn't good enough – full-text indexing is, I believe, implemented this way.

There is something built-in to Oracle that is hash-based and doesn't actually need an index to get fast row retrieval: hash clusters. You can theoretically retrieve the target row with as little as a single-block I/O (which normal index+table lookups can't match, and even IOTs can't match unless the table is really small). Do read the When to Use Hash Clusters docs though, they are quite peculiar, and you need a good key (one or more columns) in the first place to use them.


Collecting parameter values from Extended event session

Someone mentioned
"I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values."
Please review answer below:

Capture Passed Parameter Values In Sql Server Extended Sessions


What are the common reasons for a Subscription to be deactivated in SQL Server 2005?

Yes that would be the cause.  When the insert fails, the replication will then fail.  Once the replication is in a failed state you've got to fix the problem within a few days before the subscription will expire and you'll need to reinit it.


Should I use two databases or two schemas?

When using one database with two schemas you also share SGA structures(like library cache) and also possibly TEMP tablespace and also backups. In rare cases one application can have negative impact on other's performance. Also if you are asked to revert one schema it will be much harder to "restore" just one schema. The same also applies to patching, it's harder to request a downtime from two different customers.

If you use two schemas then definitely create separate DATA and TEMP tablespaces. (You can not have separate UNDO).

If you use two databases then you can not share RAM between them - for buffer cache. You can partially bypass this limitation by using OS level buffer cache. Simply disable direct file access.
A separate database can also be easily migrated onto another machine using RMAN or simple pure scp/rsync.


Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)'
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )


I ran out of time on this, but I'm posting this here in case someone wants to extend or use my work so far.  The best I could do was find the time(s) that the area was online.  At the moment, there are a few extraneous steps in there, but this should get you close.

/** Build up Sample Data **/

SET NOCOUNT ON

DECLARE @Area TABLE
    (
    AreaID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AreaName VARCHAR(100) NOT NULL
    , IsActive BIT NOT NULL
    )

INSERT INTO @Area
(AreaName, IsActive)
VALUES ('Jumbo House', 1)
    , ('DCC', 1)
    , ('Dubai Mall', 1)

DECLARE @PingTable TABLE
    (
    PingTableID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AreaID INT NOT NULL --REFERENCES @Area (AreaID)
    , UpdatedOn DATETIME2(7) NOT NULL
    )

DECLARE @Start DATETIME2(7)
DECLARE @End DATETIME2(7)

SET @Start = '8/8/2018 00:00:00'
SET @End = '8/8/2018 23:59:59'

WHILE @Start < @End
BEGIN

    --Jumbo House
    INSERT INTO @PingTable
    (AreaID, UpdatedOn)
    VALUES  (1, @Start)

    --DCC, only active after 23:00 hours.
    IF @Start > '8/8/2018 23:00:00'
    BEGIN
        INSERT INTO @PingTable
        (AreaID, UpdatedOn)
        VALUES (2, @Start)
    END

    --Dubai Mall, inactive between 10 AM and 12:30 PM
    IF NOT(@Start BETWEEN '8/8/2018 10:00:00' AND '8/8/2018 12:30:00')
    BEGIN
        INSERT INTO @PingTable
        (AreaID, UpdatedOn)
        VALUES (3, @Start)
    END

    SET @Start = DATEADD(MINUTE, 5, @Start)

END

/** Buil up Query **/
--DECLARE @Start DATETIME2(7)
--DECLARE @End DATETIME2(7)

SET @Start = '8/8/2018 00:00:00'
SET @End = '8/8/2018 23:59:59'

;WITH CTE_Time AS
    (
    SELECT 0 AS RN
        , DATEADD(MINUTE, 0, @Start) AS TimeToUse
    UNION ALL
    SELECT RN + 1 AS RN
        , DATEADD(MINUTE, RN + 1, @Start) AS TimeToUse
    FROM CTE_Time
    WHERE DATEADD(MINUTE, RN + 1, @Start) <= @End
    )
, CTE_LagAndLead AS
    (
    SELECT A.AreaName
        , A.AreaID
        , P.UpdatedOn
        , PriorUpdateOn = LAG(P.UpdatedOn) OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn)
        , NextUpdatedOn = LEAD(P.UpdatedOn) OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn)
        , DR = DENSE_RANK() OVER (PARTITION BY A.AreaID ORDER BY P.UpdatedOn)
    FROM @Area AS A
        INNER JOIN @PingTable AS P ON P.AreaID = A.AreaID
    WHERE P.UpdatedOn BETWEEN @Start AND @End
    )
, CTE_Islands AS
    (
    SELECT LL.AreaName
        , LL.AreaID
        , LL.UpdatedOn
        , GroupDR = DATEADD(MINUTE, -5 * DR, LL.UpdatedOn)
    FROM CTE_LagAndLead AS LL
    )
, CTE_Islands2 AS
    (
    SELECT AreaName
        , AreaID
        , MIN(Updatedon) AS OnlineStart
        , MAX(Updatedon) AS OnlineEnd
    FROM CTE_Islands
    GROUP BY AreaName, AreaID, GroupDR
    )
SELECT AreaName
    , AreaID
    , OnlineStart
    , OnlineEnd
FROM CTE_Islands2
ORDER BY AreaID
    , OnlineStart
OPTION (MAXRECURSION 0)


building a view w/ fields that only contain a certain word

What you have is the simplest and most straightforward.

your WHERE will require every column mentioned somewhere
changing YES into NULL requires a function on each column

For some RDBMS you can create computed column at the table level.

ALTER TABLE PhysicalBuild
   ADD SummaryState AS
     CASE WHEN Labels = 'No' AND
        DNSDRAC = 'No' AND
        DNSHOST = 'No' AND
        PRTGDRAC = 'No' AND
        PRTGHOST = 'No' AND
        LouKVM = 'No' AND
        SwitchPorts = 'No' AND
        FiberSwitch = 'No'
     THEN 'No' ELSE 'Yes' END

Then your query is

...
FROM PhysicalBuild
WHERE SummaryState = 'No'

Note: you don't need aliases the same as the column name...


Is a clustered index on column A the same as creating a table ordered by A?

Like @dezso commented, creating a new table and dropping the old used to be faster in old versions, but not any more with the new implementation in pg 9.1.

The most common problem with CLUSTER is that it requires an exclusive lock on the table, which does not go well with concurrent access to it.

The solution to this problem is pg_repack, which does not lock the table exclusively.

Generally, make sure that your server configuration is fit for the task. High settings (a lot of RAM) for maintenance_work_mem would help both CLUSTER and CREATE INDEX on big tables. Standard setting is way too small for you. Follow the links for details.

You might set it very high temporarily for a transaction with SET LOCAL and leave it at a reasonable setting otherwise:

BEGIN;
SET LOCAL maintenance_work_mem = ????MB; -- find the sweet spot
CLUSTER tbl;
COMMIT;

If possible, set it high enough to fit the whole operation in RAM.

More:

PostgreSQL 9.0 how to reclaim disk space after delete without rebuilding table?
Configuring PostgreSQL for read performance


Prevent Privileged User from Denying Access to SA

SA is a login just like any other.  You may want to do some research on best practices regarding the SA account.  It does not need to have superpowers.

Members of the sysadmin role, however, cannot be locked out of anything and members of this role bypass security checks.  If SA is a member of sysadmin, there shouldn't be anything you need to do.

A programmatic approach could be to create a SQL Agent job that restores the correct (according to you) memberships for administrator accounts.  Set this to run on a schedule and you're guaranteed to have the permissions restored (unless UserA decided to mess with the job).

However, this is really more of a personnel issue.  The people that have this level of permission on a server should be experienced and trustworthy enough not to mess with the administrative level permissions.  If they don't know what they are doing, they should not be able to do it.  If they know what they are doing and do it any way, you have a different problem.  This may best be handled by a business policy indicating that anyone that messes with administrative level permissions may be subject to disciplinary action.


Which packages are required to install Oracle 11.2.0.3 on Solaris 11?

The Solaris 11 (11.2) install uses groups of packages called:
solaris-desktop
solaris-large-server
solaris-small-server
solaris-minimal-server
The group is defined in a install manifest during AI install
Oracle also provides a x86 (64bit) desktop install iso
A SPARC or x86 Text server install
See the install documentation for  the list of files if you really want it.
Solaris no longer uses SVR4 packages to install OS packages, it now uses IPS packages.

http://docs.oracle.com/cd/E36784_01/index.html


Storing many pandas DataFrames in SQLite with metadata

You've created 3 separate tables (well 2, pending the apparent typo?). If you want to unify the data, you probably shouldn't be forcibly overwriting target tables with if_exists="replace"

From the .to_sql() documentation

  replace: Drop the table before inserting new values.
  append: Insert new values to the existing table.

Assuming your similarly named files have the same schema, you can edit the last 3 lines as follows.

exp1.to_sql("exp", engine, if_exists="append")
exp2.to_sql("exp", engine, if_exists="append")
exp3.to_sql("exp", engine, if_exists="append")

This will insert all three datasets to a single table named exp instead of 3 separate tables.

If each csv isn't uniquely identified from the others within itself - for example if exp1.csv looks like this...

Name,Date,Temperature
Peter,2020-01-01,50
Paul,2020-01-01,55
Mary,2020-01-01,53
Jane,2020-01-01,49

...then you can append the experiment identifier to each dataset as needed in the dataframe. For example by...

>>> exp1['ExpName'] = 'exp1'
>>> exp1
    Name        Date  Temperature ExpName
0  Peter  2020-01-01           50    exp1
1   Paul  2020-01-01           55    exp1
2   Mary  2020-01-01           53    exp1
3   Jane  2020-01-01           49    exp1
>>>

...which will allow you to group by experiment in any follow-on SQL you may run against your database.

  ...how could I retrieve all experiments where the mean temperature value is below or above an arbitrary threshold?

...well given an arbitrary additional two datasets of...

➜  /tmp cat exp2.csv
Name,Date,Temperature
Peter,2020-01-02,51
Paul,2020-01-02,56
Mary,2020-01-02,54
Jane,2020-01-02,50
➜  /tmp cat exp3.csv
Name,Date,Temperature
Peter,2020-01-02,52
Paul,2020-01-02,57
Mary,2020-01-02,55
Jane,2020-01-02,51
➜  /tmp

...that you likewise appended the expN identifier to in the dataframe, then you would run the following SQL to retrieve experiments where the average temp was below 53

SELECT
    ExpName,
    AVG(Temperature)
FROM exp
GROUP BY ExpName
HAVING AVG(Temperature) < 53;

Which I'll leave to you to plug into SQLAlchemy as you like :)


Query a view in a procedure in which the view name is a variable

The problem relates to your use of @counter within your dynamic SQL. It's wrong in a couple of ways: You want to assign to that variable, but in the spot you're using it, it's being translated into building the string that is the SQL statement.  You should instead use sp_executesql and pass the count value back as a return parameter.

I'd also recommend that you use QUOTENAME to prevent against SQL injection in your statement. If someone creates a view with a carefully constructed name, they could make you have a very bad day.

Try using this procedure definition:

 ALTER PROCEDURE [dbo].[query_Special_View](@schema_name sysname, @view_name sysname) AS
 BEGIN
     DECLARE @params nvarchar(1000);
     DECLARE @sql nvarchar(1000);
     DECLARE @return bigint;

     SET @params =  N'@count bigint OUT';
     SET @sql = 'SELECT @count = COUNT(*)  FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@view_name) + ';';

     PRINT @sql

     EXEC sp_executesql @sql, @params, @count = @return OUT

     SELECT SchemaName     = @schema_name,
            ObjectName     = @view_name,
            ObjectRowCount = @return

 END
 GO

Testing this out for a single call works just fine:

 EXEC dbo.query_Special_View @schema_name = 'sys', @view_name = 'databases';

Going back to your original issue...

You have a series of views that you want to get the row counts for. You don't even need this as a stored procedure. You can simply put those few lines of code directly into a cursor and execute it directly.

 DECLARE @SchemaName sysname,
         @ObjectName sysname,
         @params nvarchar(1000),
         @sql nvarchar(1000),
         @return bigint;

 --Table to hold the results
 CREATE TABLE #Results (
     SchemaName sysname,
     ObjectName sysname,
     ObjectRowCount bigint);

 --What objects do you want to get the row counts for?
 --I'm just querying sys.views, but edit this query for whatever you need
 DECLARE obj_cur CURSOR FOR
     SELECT SchemaName = schema_name(schema_id),
            ObjectName = name
     FROM sys.views v;

 --Use that cursor to loop through all objects
 OPEN obj_cur;
 FETCH NEXT FROM obj_cur INTO @SchemaName, @ObjectName;
 WHILE @@FETCH_STATUS = 0
 BEGIN
     --Dynamic SQL to get the row count
     SET @params =  N'@count bigint OUT';
     SET @sql = 'SELECT @count = COUNT(*)  FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';';
     EXEC sp_executesql @sql, @params, @count = @return OUT
     --Put that number into a table
     INSERT INTO #Results (SchemaName, ObjectName, ObjectRowCount)
     SELECT SchemaName = @SchemaName,
            ObjectName = @ObjectName,
            ObjectRowCount   = @return
     FETCH NEXT FROM obj_cur INTO @SchemaName, @ObjectName;
 END
 CLOSE obj_cur;
 DEALLOCATE obj_cur;

 SELECT *
 FROM #Results;

 DROP TABLE #Results;


What is the difference between `MAX(column + 1)` and `MAX(column) + 1`

max(column)+1 will compute max value of the column and then adds 1 to the results
max(column+1) will compute max value on column value+1

Tables meta data/index will be used when you use max(column) but max(column+1) needs to scan all the rows and compute the values one by one. So good to use max(column)+1


After reading some more on NOAUDIT, I think I understand my confusion. NOAUDIT does not necessarily cease auditing for a specific action-user pairing, but cancels a matching AUDIT statement. So NOAUDIT select table BY user1 would only cancel a previously made statement of AUDIT select table BY user1. If the AUIDT statement had never been made and all SELECT TABLE statements are already being audited, the NOAUDIT statement would have no effect.
The relevant documentation:

Use the NOAUDIT statement to stop auditing operations previously enabled by the AUDIT statement.
The NOAUDIT statement must have the same syntax as the previous AUDIT statement. Further, it reverses the effects only of that particular statement. For example, suppose one AUDIT statement A enables auditing for a specific user. A second statement B enables auditing for all users. A NOAUDIT statement C to disable auditing for all users reverses statement B. However, statement C leaves statement A in effect and continues to audit the user that statement A specified.


can i secure sql server agent job with password

One alternative method which might help you out is to save your stored procedures and UDFs that the job may use (if any) WITH ENCRYPTION.

However, given the nature of your question, I wonder whether you really need to start a bottom-up review of your database systems security first, liaising with business owners regarding what permissions currently exist, and what the desired position should look like.


Reporting services existing in SQL Cluster instance but unable to see on SSMS?

If you used the Reporting services configuration manager to configure reporting services, which SQL instance did you point Reporting Services at?  Go back into Reporting services configuration manager and confrim which instance it's pointing to, this should be the instance that the ReportServer and ReportServerTempDB databases reside on.


Should I use additional id values as unique values that only applies in junction tables in MySQL?

With just the two foreign keys in there I would leave it as it is.

If other tables have a foreign key pointing to person_image then the debate becomes a lot more nuanced and you will start considering table size and query (plan) complexity.


Depends on the SQL implementation, I don't know any general solution.

Here someone asked for the implementation of this in a Microsoft SQL Server:
https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id

PostgreSQL:

currval('SEQUENCE_IDENTIFIER'::regclass)

In PostgreSQL, the IDs come from "SEQUENCES", and you could use the following as documented in https://www.postgresql.org/docs/8.2/static/functions-sequence.html . Note that this only works if "nextval" has been called in this session, which is also mentioned in the link.

MySQL: (https://stackoverflow.com/questions/3837990/last-insert-id-mysql)

LAST_INSERT_ID()

Suit yourself.


Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

In Oracle, one way to enforce this sort of constraint in a declarative fashion would be to create a materialized view that is set to refresh fast on commit whose query identifies all the invalid rows (i.e. BookAspectRating rows that have no match in BookAspect_view).  You can then create a trivial constraint on that materialized view that would be violated if there are any rows in the materialized view.  This has the benefit of minimizing the amount of data that you have to duplicate in the materialized view.  It can cause problems, however, since the constraint is only enforced at the point that you're committing the transaction-- many applications aren't written to expect that a commit operation might fail-- and because the constraint violation can be somewhat hard to associate with a particular row or a particular table.


How to get sum & latest activity in same result?

This should work:

SELECT
    Name,
    COUNT(CASE WHEN Activity = 0 THEN 1 END) AS TOTALActivity_0,
    COUNT(CASE WHEN Activity = 1 THEN 1 END) AS TOTALActivity_1,
    MAX(CASE WHEN Activity = 0 THEN LogTime END) AS LatestActivity_0_Logtime,
    MAX(CASE WHEN Activity = 1 THEN LogTime END) AS LatestActivity_1_Logtime
FROM
    Table_1
GROUP BY
    Name ;

Test in: dbfiddle.uk


How do I install SQL-Server Tools if I can't use curl?

14.0.3038.14-2+

New versions of SQL Server (14.0.3038.14-2+) no longer conflicts with libcurl3.

  Depends: libunwind8, libnuma1, libc6, adduser, libjemalloc1, libc++1, gdb, debconf, hostname, libssl1.0.0, openssl, python (>= 2.7.0), libgssapi-krb5-2, libsss-nss-idmap0, gawk, sed, libldap-2.4-2, libsasl2-2, libsasl2-modules-gssapi-mit

Versus 14.0.3037.1-2

  Depends: libunwind8, libnuma1, libc6, adduser, libjemalloc1, libc++1, gdb, debconf, libcurl3, hostname, libssl1.0.0, openssl, python (>= 2.7.0), libgssapi-krb5-2, libsss-nss-idmap0, gawk, sed, libldap-2.4-2, libsasl2-2, libsasl2-modules-gssapi-mit


Your understanding is correct. For every connection attempt, each line in pg_hba.conf is checked in turn. The first matching line applies and the connection then either passes with this or fails trying. Much like a firewall. The manual:

  The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Bold emphasis mine.


SELECT all substrings (n-grams) of length n?

This does the trick:

SELECT SUBSTRING('example' FROM n FOR 3)
FROM GENERATE_SERIES(1, LENGTH( 'example' )-2, 1) n;
exa
xam
amp
mpl
ple

Here it is in a function:

CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$
SELECT SUBSTRING($1 FROM n FOR $2)::TEXT
FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n;
$$ LANGUAGE SQL;

Use it like:

SELECT ngrams('example', 3)


Backup MySQL databases to external hard drive then restore

Use mysqldump from the command line.

For example, to dump all your databases to a file, use the --all-databases switch and a result file for output:

mysqldump [options] --all-databases --result-file=dump.sql

If the DBs are big, you can split them out into their own files. The dump command is flexible like that.

(Ref: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html)

To restore, all you have to do is run the dump file as a script, which you can also do from the command line.


That's actually a bug. MySQL Workbench saves the tabs on close of the connection. To get rid of the unwanted tabs you could temporarily switch off the snapshot feature:

If you like to get that bug fixed file a bug report at http://bugs.mysql.com.