SQL Query to look for nulls from two different tables while adding in a new column that states what table its from

Is there a reason you can't simply hardcode a literal of the name of the SourceTable in each select?

--Demo setup
Declare @OrdersTable table (
  OrderName VARCHAR(4),
  OrderNumber INTEGER,
  OrderStatus VARCHAR(8)
);

INSERT INTO @OrdersTable
  (OrderName, OrderNumber, OrderStatus)
VALUES
  ('xx', '1', 'Active'),
  ('xx', '2', 'Active'),
  ('bbb', '3', 'Active'),
  ('abc', '4', 'Active'),
  (NULL, '5', 'Inactive');

Declare @ProcessTable table (
  ProcessTitle VARCHAR(4),
  ProcessNumber INTEGER,
  ProcessStatus VARCHAR(8)
);

INSERT INTO @ProcessTable
  (ProcessTitle, ProcessNumber, ProcessStatus)
VALUES
  ('abc', '1', 'Active'),
  ('abc', '1', 'Active'),
  ('db', '2', 'Active'),
  ('BG', '3', 'Inactive'),
  ('BG', '3', 'Inactive'),
  (Null, '4', 'Active'),
  (Null, '4', 'Inactive');

--Solution
Select OrderName,
       OrderNumber,
       OrderStatus,
       'OrdersTable' as SourceTable
From @OrdersTable
where OrderName is null
UNION ALL
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus,
       'ProcessTable' as SourceTable
From @ProcessTable
where ProcessTitle is null

| OrderName | OrderNumber | OrderStatus | SourceTable  |
|-----------|-------------|-------------|--------------|
| NULL      | 4           | Active      | ProcessTable |
| NULL      | 4           | Inactive    | ProcessTable |
| NULL      | 5           | Inactive    | OrdersTable  |


MongoDB Reconnects to Secondaries Too Fast

I don't have a specific answer, however, you can consider the following. Once a member is crashed, you can change it to be hidden. Once it is hidden, clients will not try to connect. Once it is up, let it complete the data from the oplog and change it back.


Multiple Oracle Instances --  is this a good practice?

Isaac,
preferably we run one instance on one server and implement the different applications as schemas and services in that single datatabase. If the server has enough memory, there won't be a problem, if the applications all behave like good Oracle citizens. As soon as there is an application that does not use bind variables there is a growing pain on the host. The same for applications that keep creating new connections every few seconds, instead of re-using existing connections. Things like auto commit also does not help to improve the situation.
What is the normal behavior of your application?
Does it do large updates?
When does it do a commit?
How much redo is generate/hour?
Does your app use bind variables?
In many cases, the location of the online redolog files is critical. If the app does generate a lot of redo (or competes with an other app that does this), the logwriter will slow down and cause a big performance degradation because the session is waiting for the logfile write to complete.
Give you customer a disk layout where the redo's are on dedicated raid-10 disks and see how much this helps.
Also find out the memory allocation situation. There might be a need for a fair redistribution of available memory.

I hope this helps


How to select distinct? not select duplicated row

You can use ROW_NUMBER() ranking function and a subquery (derived table or CTE):

WITH
  cte AS
  ( SELECT
      B.MAIN_ID, A.NAME, A.NAME_ID, A.CREATED_DATE,
      ROW_NUMBER() OVER (PARTITION BY B.MAIN_ID
                         ORDER BY A.CREATED_DATE DESC)
        AS RN
    FROM
      FAMILY A JOIN ADDRESS B ON B.F_ID = A.N_ID
  )
SELECT MAIN_ID, NAME, NAME_ID, CREATED_DATE
FROM cte
WHERE RN = 1 ;


Establishing referential integrity in a generic table?

You can use this design, which requires one more table:

 - DropDown:
     DropDownID  PK,
     Name nvarchar(25)

 - DropDownOption:
     DropDownID  FK (DropDown)  UQ1,
     DropDownOptionID  PK       UQ1,
     Name nvarchar(25),
     Description nvarchar(155)

 - Estimate:
     EstimateID  PK,
     etc.

 - EstimateOption:
     EstimateID  PK,   FK1 (Estimate)
     DropDownID  PK    FK2 (DropDownOption),
     DropDownOptionID  FK2 (DropDownOption)

For every row in Estimate, you'll have to add (up to) 13 rows in the EstimateOption table (one for each dropdown menu).

The additional Unique constraint in the DropDownOption table is needed for the foreign key (FK2) from the EstimateOption table.

What you have in your current design as Estimate, can then be constructed with a pivot query from the two tables.


MYSQL - 'Select distinct' - different results on two exact versions of Mysql, databases synced

This is just a hunch, but make sure your collation settings are the same between the dbs.  I know you said they're synced, but check.

If you can't find anything there run each select into their own outfile

select bdtag3_.* into outfile '/tmp/db1.data' ....

and

select bdtag3_.* into outfile '/tmp/db2.data' ....

You have an order by so running a basic commandline diff should quickly highlight differences.

The pt-table-checksum tool is also handy for finding differences.


In SQL-Server 2012 version, looks straightforward with the LAG() function:

SELECT
    Action,
    Number = COUNT(*)
FROM
    ( SELECT
          Action,
          Diff = DATEDIFF(day, LAG(Date) OVER (PARTITION BY Action ORDER BY Date), Date)
      FROM
          a
    ) AS t
WHERE
    Diff > 3 OR Diff IS NULL
GROUP BY
    Action ;

This should work, even in 2008 version:

SELECT
    Action,
    Number = COUNT(*)
FROM
    a
WHERE
    NOT EXISTS
    ( SELECT *
      FROM a AS b
      WHERE b.Action = a.Action
        AND ( (b.Date >= DATEADD(day, -3, a.Date) AND b.Date < a.Date)
           OR (b.Date = a.Date AND b.ID < a.ID)
            )
    )
GROUP BY
    Action ;


What is the behavior of null/empty value in compound shard key?

In MongoDB 'null' is just another value so two documents that have the same values for field1 (some non-null) and same value for field2 (null) have the same value for the shard key and will always be in the same "chunk".


How to determine if postgres database contains GiST indexes and what type?

Whenever you need to examine the structure of your database via code, always think "I should look at information_schema or pg_catalog". information_schema contains a standardized schema (66 views), whereas pg_catalog is PostgreSQL-specific, but contains more info (97 tables or views).

select
  *
from
  pg_catalog.pg_indexes
where
  indexdef ~* '\ygist\y'

Will show you all the gist indexes and their tables, names, and definitions.

~* means match a regular expression, case-insensitive.

\y means word boundary, so it would find ' gist ' but not 'logistics' in that column


how to reset value of auto incremented id to a specific value in mysql?

The following should do what you want where x is a value greater than the maximum value that appears in the column

ALTER TABLE table_name AUTO_INCREMENT = x

The cause of the skipped values is probably either failed inserts (where the insert fails a unique key check or similar) or insert on duplicate update queries both of which increment the auto_increment without creating a row.

If this is a problem for your migration you may be better off creating two copies of your new table table_name and table_name_temp.  Use your stored procedures to populate table_name_temp ignoring the gaps in the auto increment. Once table_name_temp is fully populated you can populate table_name with an insert select query from the table_name_temp of the table.

INSERT INTO table_name SELECT <all columns except the auto increment> FROM table_name_temp;

This will create a table with a continuous auto_increment


Query Performance When Dropping Multiple Columns

Yes, but it's too small to be noticed unless you plan to drop hundreds or even thousands of columns ;)

Each drop column is executed separately regardless if it's a separate statement, or part of a statement. The performance gain would be in the work postgres do before and after each statement, for instance taking a lock on the table, creating implicit transactions, and parsing the statement which it would only have to do once if all columns are dropped in one statement.


Enabling SSL in MariaDB running on Synology NAS

Please, check the following configuration files and variables:

File: /etc/mysql/my.cnf

[client]
...
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/client-cert.pem
ssl-key=/var/lib/mysql/client-key.pem
ssl-verify-server-cert
...

[mysqld]
...
ssl
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
...

File: /etc/hosts

127.0.0.1 localhost CNSERVER

MariaDB Command-Line:

$ mysql -u root -p -h CNSERVER
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 10.1.14-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| have_openssl        | YES                             |
| have_ssl            | YES                             |
| ssl_ca              | /var/lib/mysql/ca.pem           |
| ssl_capath          |                                 |
| ssl_cert            | /var/lib/mysql/server-cert.pem  |
| ssl_cipher          |                                 |
| ssl_crl             |                                 |
| ssl_crlpath         |                                 |
| ssl_key             | /var/lib/mysql/server-key.pem   |
| version_ssl_library | OpenSSL 1.0.2g-fips  1 Mar 2016 |
+---------------------+---------------------------------+
10 rows in set (0.00 sec)

MariaDB [(none)]> SHOW SESSION STATUS LIKE '%Ssl_version%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)


That's just how your locale defines sort order. Obviously  upper case letters act as tiebreakers if the string is otherwise identical - then they sort after lower case equivalents. But 'ba' still sorts after 'B' (and 'BA' sorts after 'b').

Compare to results without collation rules:

SELECT   'b' > 'B'
      , 'ba' > 'B'
      , 'ba' > 'C'
      , 'b'  > 'B' COLLATE "C"
      , 'ba' > 'B' COLLATE "C"
      , 'ba' > 'C' COLLATE "C";

 ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------
 f        | t        | f        | t        | t        | t

(My current collation setting German_Germany.1252 happens to behave just like your en_ZA.UTF-8.)


Oracle case insensitive passwords seemingly ignoring SEC_CASE_SENSITIVE_LOGON

Create a password file, set it to insensitive. You should have a password file, even on windows, even if all it does is define who can connect as a sysdba. I just installed a default Standard Edition and a password file was created. THe last time I built a new EE system it was the same way.

Here's the doc for how to set one up if you don't know. Make sure you use the case sensitive switch and turn it off:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm#ADMIN10241


Can we set LOCK_TIMEOUT instance- or database-wide?

No it isn't possible to set this as a default and change it from -1 (no timeout).

If it needs to be changed, you have to be explicit with a call to SET LOCK_TIMEOUT <number of ms for timeout>.

As for why there is no way to alter the default, we can only speculate.  But my guess would be that the lack of a timeout would satisfy the majority of requirements for queries, and need to have a lock wait timeout is a pretty corner case scenario, requiring a simple SET statement.

Regardless, you're talking about client-side handling.  Not server-side.  In fact, most providers that I work with by default enforce a command timeout (i.e. System.Data.SqlClient.SqlCommand.CommandTimeout is set to a default of 30 seconds).  Although a bit of a nested requirement with lock requests, it's the same idea.


Try

CREATE TRIGGER tg_ai_tablea
AFTER INSERT ON tablea
FOR EACH ROW
  INSERT INTO tableb (userid, value)
  VALUES (NEW.userid, CASE NEW.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END);

Here is SQLFiddle demo

You can also consider instead of storing value just evaluating it on the fly

SELECT b.userid,
       CASE a.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END value
       -- , other columns from tableb
  FROM tableb b JOIN tablea a
    ON b.userid = a.user.id;


I'm starting with the assumption that you believe one query gets cached and not the other based on response time the second time you issue the query.  If you're determining that the query isn't cached based on something else, please comment to that effect, explaining your precise observations and what you're expecting instead.

There are several reasons that come to mind why a query would not be cached (or would appear not to be cached).  Here are some thoughts.  Forgive me if I have stated the obvious:

If the 2nd table is particularly busy, anything that gets cached will be cached for such a short time that you'll never see it.  As I mentioned in my response to "Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes" (where I went in to some detail about my finding while investigating the internals of the query cache on stock MySQL) --

  Any time the data in a table referenced by a cached query changes, all of the queries that involved that table are removed from the cache -- even if the change doesn't impact the cached results. This is even true if a table changes, but doesn't change, as in the case of an InnoDB transaction that is rolled back. The query cache entries referencing that table were already purged.

`query_cache_limit` = 10485760

This setting would prevent a resultset larger than 10MB from being cached.  Adding the 2nd table will obviously increase the size of the resultset but without more information, it isn't apparent how much.

If the 2nd table is partitioned, the query will not be cached because the query cache does not work with partitioned tables (at least in MySQL 5.6; unsure about Percona Server).

The query cache operates on the query string submitted by the client before it reaches the
parser -- for a subsequent query to be served from the cache, it must be byte-by-byte identical to the prior query that was cached.  If you don't submit precisely the same query each time, you won't get a cached result.


How to compare all rows in a table and extract data using triggers

Supposing id is primary_key and auto increment column for both t1 and t2, I think this is what you need:

CREATE TRIGGER trig1
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
  IF @latitude = NEW.latitude AND @longitude = NEW.longitude THEN
     SET @end_time = NEW.received_time;
     SET @iddle_time = TIMEDIFF(NEW.received_time, @start_time);
     UPDATE t2 SET iddle_time = @iddle_time, end_time = @end_time
               WHERE id = @last_id_t2;
  ELSE

     INSERT INTO t2 VALUES (NULL, NEW.uid, NEW.latitude, NEW.longitude,
                            NULL, NEW.imno, NEW.received_time, NEW.received_time);
     SET @last_id_t2 = LAST_INSERT_ID();
     SET @latitude = NEW.latitude; SET @longitude = NEW.longitude;
     SET @iddle_time = NULL;
     SET @start_time = NEW.received_time; SET @end_time = NEW.received_time;
  END IF;
END;

You can see the whole scheme here : http://sqlfiddle.com/#!2/a70ac/1


Relational tables should contain data and not logic - correct?

It Depends

The purpose of a database is to store valid data in a secure fashion for multiple users across multiple applications.

Some of the logic will focus on the valid data portion of that statement.  These will be your constraints and data types.

Other parts of code within the database will focus on the multiple applications portion.  For example, a VIEW will hide some complex logic to ensure that all applications see the data in an identical manner.  One application could be a web based application, the other application could be a 3rd party report generator.

OLTP

Within an OLTP system, the complex SELECT statement and the last modified date logic doesn't make much sense.

If this is suppose to be an OLTP system, you should try to change things so that they follow normal relational database practices.

Data Warehouse

The complexity of your pseudo-code SELECT statement and your date of last edit logic makes perfect sense in a Data Warehouse [DW] environment.  However, they are probably part of an ETL/ELT process for materializing the data into a denormalized table for faster report generation.

The TABLES and VIEWS and MATERIALIZED VIEWS are built to solve a very specific set of business problems.  From my experience, those objects are used as the source for various Business Reports.  In some cases, one table/view per report is created to solve a Business Requirement.

No FKs?  That's probably normal for a DW.  The data in the tables could be refreshed with TRUNCATE followed by INSERT...SELECT.  In this situation, a Foreign Key could do more harm than good.

As I read the comments, it seams your company needs to move to a more formal OLTP hierarchical design.  I wish you luck.


How can I alter the type of a money-column to decimal in PostgreSQL?

Try providing USING clause as described in docs:

  A USING clause must be provided if there is no implicit or assignment cast from old to new type.


How to restore SQL Server database to a specific day?

>I am requested to restore the db to a day one month ago. how can I do that?

you can't as you only have 7 days of backups due to disk space shortage. If you are/were moving the backups away and storing them for X amount of days then you would be able to go back that X amount of days to recover your data.

also, your database recovery model plays an important role in whether you can do a point-in-time restore or not - provided you are taking transaction log backups in full or bulk-logged recovery mode.

>Also can I shrink or truncate log file much smaller?

yes you can do it, but with some caution.

shrinking database is not a good idea unless you are sure that it won't grow to that point again.

refer to I Need to Shrink My Database - I just freed a lot of space for more details as it is covered is a nice and precise way.


Copy SQL Server DB into another instance without SA access

Is there any better way to copy the database without SA access?

Yes, you do not have to be sa or sysadmin member to perform the activity that you described, but following are the least privileges required to do so.:

You must be member of

Database role: db_ddladmin or Database role: db_owner at source server
Server role: dbcreator at destination server

You can verify current membership with following query (1=YES, 0=NO):

---- Source server
select  IS_MEMBER ('db_ddladmin' ) as Is_DDLAdmin,
        IS_MEMBER ('db_owner' ) as Is_DBOwner
go

---- Destination server
select  IS_SRVROLEMEMBER ('dbcreator' ) as Is_DBCreator
go

I haven't used dbForge studio, but using SSMS free tool provided by Microsoft, and while having permissions (as mentioned above), you can achieve what your looking for (export data along with objects into .sql format)

via  SSMS -> Tasks -> Generate script option. focus on following options at Advanced scripting options

Types of data to script = Data and schema
Script for server version = your desired version


Cannot add new Server Role SQL Server 2014

This is a known bug in Management Studio, and has existed since at least SQL Server 2012. Unlike other missing things in Management Studio (like SQL Server Agent), this has nothing to do with the version or edition of Management Studio you're using, but rather the edition of SQL Server that Object Explorer has connected to. Here's confirmation from one of the lead SSMS devs:

You will need to use T-SQL for this functionality until they fix it (which I think after 2016 SP1 they'll need to do a full audit of any instance-specific filtering or restrictions).


Impact of Log backups restore process after file\file group restore on a database

When we restore file\file group(backup1) backup on a database is it necessary to restore all log backups that were taken after backup1 to get database in consistent state??

Yes - if you want to completely bring your database online (with all file groups) or you want to do a point-in-time restore of your database.

No - if you want to do a piece meal restore for specific file group.

I have explained in detail by giving an example at Backing up file groups with restore to earlier backup.


The reason that your index rebuild isn't completing is because of the LCK_M_SCH_M wait type.  What happens when you try to rebuild an index, a Sch-M lock is requested on the object that you're trying to rebuild.

Please see this below chart for lock compatibility:

As you can see here, a Sch-M lock has a conflict with almost every locking scenario (shared, exclusive, update, schema stability, etc.).

Here's a small example showing what may be happening in your environment.  To create the test object in a test database:

use TestDB;
go

create table dbo.ConcurrencyTest
(
    id int identity(1, 1) not null
        constraint PK_ConcurrencyTest_Id primary key clustered,
    some_int int not null
        default 1
);
go

insert into dbo.ConcurrencyTest
default values;
go 100

Now if one session is executing a query, and it keeps the lock open (I'm using an update query and not committing the transaction):

use TestDB;
go

begin tran;

    update dbo.ConcurrencyTest
    set some_int = 2
    where id = 7;

--commit tran;

And if another session attempts to rebuild the clustered index on that table:

use TestDB;
go

alter index PK_ConcurrencyTest_Id
on dbo.ConcurrencyTest
rebuild;
go

It's going to be blocked by the initial UPDATE query.  We can see this through a little diagnostic query below:

select
    l.resource_type,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    st.text as blocked_sql_text,
    r.blocking_session_id,
    stb.text as blocking_sql_text
from sys.dm_tran_locks l
inner join sys.dm_exec_connections c
on l.request_session_id = c.session_id
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
inner join sys.dm_exec_connections cb
on r.blocking_session_id = cb.session_id
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) st
cross apply sys.dm_exec_sql_text(cb.most_recent_sql_handle) stb
where l.resource_database_id = db_id('TestDB')
and l.request_status = 'WAIT'
and r.blocking_session_id is not null
and r.blocking_session_id > 0;

My output looks like the following:

As you can see here, my ALTER INDEX ... REBUILD command is being blocked by the UPDATE query.  The request lock is the Sch-M lock that is required on the OBJECT in order for this operation to complete.

Due to this concurrency conflict, it is advisable to schedule your index maintenance (as well as other maintenance tasks) during a window where there is little to no user load.


Check progress of alter index reorganize / rebuild

It's really hard to say how long your rebuild will take, as SQL itself doesn't really know in advance and cant give you an estimate.

You can use the following query to use the dm_exec_requests dmv to view how long your index rebuild has been going on for, and to verify that SQL doesn't really have an estimate:

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('Alter Index')

However when it comes to a real estimate on time required you can read this nice blog post from sqlmunkee, which summarises this by saying "..it depends. ":

  And since we're not all on the same hardware, using the same software,
  or looking at the same data, the answer has to be… ..it depends

Frustrating, but true, sadly.


It's me :P

Good point on not having checksum for the sql file, I'll get some.

Your hesitation is commendable. And it's quite difficult to review the code line by line; there are stored procedures involved which are actually being executed -- I wouldn't want to review any single line of code of, say grep, or apache or... anything.

Noteworthy is that the project has moved to GitHub. The authenticy of the code in this link is as good as any SHA1 checksum I would publish on that same page.

So what next? This is known (to me) to have been downloaded tens of thousands of times; I've installed it in production on hundreds of servers; also I'm a nice guy.

It's actually a wonderful question; how can I make one trust my code? Providing a checksum is more for you to know no one else tampered with my code. I suppose you rely on the wisdom of the masses, on Google, on author's reputation (see my blog, my GitHub profile, BTW I work for GitHub). I got aware of this question having been notified by a friend in the MySQL community who is also a great stackeschange member... Thanks @RickJames

Oh: you can always import common_schema onto a replica, without endangering your master; then you are able to checksum your entire data to prove that common_schema hasn't done anything evil. The fact you can do it is likely to give you confidence. Or just do it.

I never had more fun answering a question on stackexchange.


Best index for case insensitive match on one field and ordering by another field

A multicolumn expression index should serve your particular query best:
CREATE INDEX ON public.core_user (upper(email), last_login DESC);

If last_login can be NULL, consider NULLS LAST - in index and query.
The rule of thumb is: equality first, range later. See:

Multicolumn index and performance
Get the last 5 distinct values for each ID


Document database structure for auditors

In SQL Server Management Studio Object Explorer, expand a database and look at "Database Diagrams" to create one.

This require db_owner rights which you should have as a DBA (a.k.a sysadmin)


First I'd say you want to know what tablespace it is. A lot of times I connect to the database and use LIST TABLESPACES SHOW DETAIL to figure out what is happening. Look at each tablespace and see what value is set for the "State" field on each. You want a "zero" value for the tablespace to be fine.

And here is a handy query:

select substr(tbsp_name,1,30) as tbsp_name, substr(TBSP_STATE,1,18) as TBSP_STATE
from TABLE(MON_GET_TABLESPACE('',-2)) with ur

This gets all tablespaces with a human readable state. Found this off of Ember Crooks' blog, which I follow. Very useful.

Anyway first you want to know which space it is an what state it is in. Once you know that, you can either deal with it or dig further. For example, if the tablespace is quiesced, then you only need to unquiesce it or reset the quiesce. I'd still ask why....Perhaps you have an ETL job or something that quiesces the tablespace and doesn't release it properly if it fails? Or take a peek at your the diaglogs to see what was going on at the time (very key here).

Try those out and let us know what it going on.


SQL Server Rollback nested Stored procedures and linked servers

Yes you must use a distributed transaction for this since what you described is a distributed query (transaction across servers). You may want to post a new question regarding your MSDTC issue in EC2.


Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

Since there is a limited number of resources (cpu, memory, disk) and you want to use them as efficiently as possible.

For OLTP you typically have many small transactions and concurrency is important. Your data model is typically normalized and most data access is done via index lookup. A subset of your data is used over and over so you want that to be cached in memory.

For OLAP/DW you typically have fewer transactions, but in return, they are larger. Writes are usually done via some ETL process. Your model is often de-normalized and in some sense prepared for your OLAP queries. Keeping all used data in memory is often not possible so it has to be read from disk, sorting is often involved so this memory area is often important here.

Realtime updates to cubes is not a problem as long as there is a limited number of users that update. Throw in millions of users from the OLTP scenario, concurrency will come in to play.

That said, most systems I've seen contains a little bit of both worlds. You are likely to see some compromises in both models as well as physical implementations of those.

When it comes to SQL vs NoSQL, the trend for the last years has been that they are approaching each other. NoSQL has changed from No SQL, to Not Only SQL. Most SQL databases on the other hand now have support for unstructured data like XML and JSON that can efficiently be queried from within SQL.


How to get a list of postgresql indexes that have not been used in any queries recently?

The time of last use is not stored, but you can use the query from my blog to find all indexes that have never been scanned and server no other purpose either:

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Indexes can serve other purposes than scanning:

implement constraints
make autoanalyze gather statistics on the indexed expression


If you include PLUS ARCHIVELOG in backup command, transactions that happened during the backup will be recorded in a separate file.


A relation is in BCNF when, for all FDs that apply to the relation, the left hand side is a superkey. That is, it has to contain all the attributes of at least one key. Your relation must have at least one candidate key (that is, you can't have repeating rows in the relation), else it wouldn't even meet the conditions for 1NF.

Given this, your relation can have zero, one or two FDs. The case for zero FDs is trivial (ab->ab). For one FD (a->b) the left hand side attribute is clearly the key.

In the case where you have FDs in both directions (a->b and b->a) you simply have two different keys, and so the left hand side of each FD remains a superkey.


The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers).  It also allows you to log in regardless of errors thrown by a login trigger as a failsafe.  If you inadvertently coded your login trigger to throw an error no matter who was logging in, for example, you need to allow someone to log in to fix the trigger.

It appears that the trigger is behaving as expected in this case.  You wouldn't in reality create a database link from one database to another using a DBA account.


With a clustered columnstore index, the data and index are one and the same so the reported data size includes both data and index.

A traditional b-tree clustered index also has non-leaf index pages which is reported separately by the index space.


MySQL compare two columns value at the same row

First of all, you shouldn't call "table" your table as it is a reserved word and can cause some confusion.

Have you read any documentation about querying ? You should start from here Query information

If you want to select all rows where data_a is bigger than data_b you can do it with

SELECT * FROM table where data_a > data_b


If you migrate from RDBMS to NoSQL then you will also have problems. They are not interchangeable for all types of usage.

When you start a project then just before you start the creation of your data objects you must choose the one that suits best to your needs. Changing later will nearly always cause problems as soon as you use more then the 'standard' options.

When you choose to use a third-party extension then there is always the risk that:

Support/development of the extension stops. When you upgrade your database then the extension might no longer work;
Another better third-party solution is created. When you want to use it then you face incompatibility problems with the current third-party extension;
The third-party extension gets integrated in the database that you use.

The last option is the best one.


How to determine Oracle LOB storage footprint?

You can get the size in bytes of the BLOB column via DBMS_LOB.GETLENGTH(...).

To get a total size in bytes of column for the entire table SUM it up like this:

SELECT SUM(DBMS_LOB.GETLENGTH(my_blob_column)) as total_bytes
FROM my_table

If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual size in bytes used. Compare the used_bytes value before/after your enable settings that could change the size of the BLOB column (eg. dedupe, compression, etc).

declare
    p_schema_name           varchar(256) DEFAULT 'THE_SCHEMA_NAME';
    p_table_name            varchar(256) DEFAULT 'THE_TABLE_NAME';
    --
    l_segment_name          varchar2(30);
    l_segment_size_blocks   number;
    l_segment_size_bytes    number;
    l_used_blocks           number;
    l_used_bytes            number;
    l_expired_blocks        number;
    l_expired_bytes         number;
    l_unexpired_blocks      number;
    l_unexpired_bytes       number;

begin
    FOR rec IN (select segment_name
                from dba_lobs
                where owner = p_schema_name
                  and table_name = p_table_name
                order by 1)
    LOOP
        dbms_output.put_line('Segment Name=' || rec.segment_name);

        dbms_space.space_usage(
            segment_owner           => p_schema_name,
            segment_name            => rec.segment_name,
            segment_type            => 'LOB',
            partition_name          => NULL,
            segment_size_blocks     => l_segment_size_blocks,
            segment_size_bytes      => l_segment_size_bytes,
            used_blocks             => l_used_blocks,
            used_bytes              => l_used_bytes,
            expired_blocks          => l_expired_blocks,
            expired_bytes           => l_expired_bytes,
            unexpired_blocks        => l_unexpired_blocks,
            unexpired_bytes         => l_unexpired_bytes
        );

        dbms_output.put_line('  segment_size_blocks       => '||  l_segment_size_blocks);
        dbms_output.put_line('  segment_size_bytes        => '||  l_segment_size_bytes);
        dbms_output.put_line('  used_blocks               => '||  l_used_blocks);
        dbms_output.put_line('  used_bytes                => '||  l_used_bytes);
        dbms_output.put_line('  expired_blocks            => '||  l_expired_blocks);
        dbms_output.put_line('  expired_bytes             => '||  l_expired_bytes);
        dbms_output.put_line('  unexpired_blocks          => '||  l_unexpired_blocks);
        dbms_output.put_line('  unexpired_bytes           => '||  l_unexpired_bytes);
    END LOOP;
end;
/

This code is a slightly modified version of the code in Oracle's post explaining how deduplication works. The original is available here: http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html.


(macOS/Docker) "Cannot bulk load. The file path does not exist or you don't have file access rights."

For anyone else who is new to running SQL Server in a macOS Docker container, you first need to find out your Docker container ID. The commands will be like this:

List Docker info:

$ docker container ls
47940489ee07

Copy from the macOS directory to the Docker container directory:

$ docker cp /Users/Trump/snp500-companies.csv 47940489ee07:/snp500-company-by-sector.csv

https://docs.docker.com/docker-for-mac/


To get the full domain you need a cartesian product (cross join in sql lingo) between the two sets:

select distinct d.customer_id, i.item_id
from deliveries d
cross join items i;

from this set you can subtract the actual deliveries:

select distinct d.customer_id, i.item_id
from deliveries d
cross join items i
where not exists (
    select 1 from deliveries d2
    where i.item_id = d2.item_id
    and d.customer_id = d2.customer_id
);

I'm not clear on how this null comes into play, but you can add a flag that says that this product has not been bought by this customer as:

select distinct d.customer_id, i.item_id, 'NOT BOUGHT' as status
from deliveries d
cross join items i
where not exists (
    select 1 from deliveries d2
    where i.item_id = d2.item_id
    and d.customer_id = d2.customer_id
);

Finally, you can add the deliveries table to this set as:

select distinct d.customer_id, i.item_id, 'NOT BOUGHT' as status
from deliveries d
cross join items i
where not exists (
    select 1 from deliveries d2
    where i.item_id = d2.item_id
    and d.customer_id = d2.customer_id
);
union all
select d.customer_id, d.item_id, 'BOUGHT' as status
from deliveries d;

Not sure if it answers your question, but I think it will give you a start.


Checking the docs for Amazon Redshift shows this:
DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} )

datepart: The specific part of the date or time value (year, month, or day, hour, minute, second, millisecond, or microsecond) that the function operates on. For more information, see Date parts for date or timestamp functions.

Specifically, DATEDIFF determines the number of date part boundaries that are crossed between two expressions. For example, suppose that you're calculating the difference in years between two dates, 12-31-2008 and 01-01-2009. In this case, the function returns 1 year despite the fact that these dates are only one day apart. If you are finding the difference in hours between two timestamps, 01-01-2009 8:30:00 and 01-01-2009 10:00:00, the result is 2 hours. If you are finding the difference in hours between two timestamps, 8:30:00 and 10:00:00, the result is 2 hours.

date|time|timetz|timestamp:  A DATE, TIME, TIMETZ, or TIMESTAMP column or expressions that implicitly convert to a DATE, TIME, TIMETZ, or TIMESTAMP. The expressions must both contain the specified date or time part. If the second date or time is later than the first date or time, the result is positive. If the second date or time is earlier than the first date or time, the result is negative.

So, your query should be:
select
    email,
    createddate,
    lastloggedin,
    datediff(minute, createddate, lastloggedin)
from udb.user
where createddate >= '2019-09-01' and createddate <= '2019-09-30'


Yes, if you use phpMyAdmin on your WAMP server, you can use the Export tab to dump your database and all the data in it.

On the Export tab, choose the Custom export method. This opens a bunch of options. Under Save Output to a File, choose Compression and you get to choose zipped from a drop-down.

Then you can Import that zip to another MySQL instance on another computer.


So I was never able to determine why the allocated pages couldn't be linked to the underlying object using sys.allocation_units, however, I was eventually able to identify the objects using a modified version of a script from Paul Randal and DBCC PAGE.
Firstly, running this script (which is based on Paul Randal's sp_AllocationMetadata but modified to filter based on filegroup name and not object name):
SELECT
    [sa].[allocation_unit_id] AS [Alloc Unit ID],
    [sa].[type_desc] AS [Alloc Unit Type],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 6, 1) +
            SUBSTRING ([sa].[first_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 4, 1) +
            SUBSTRING ([sa].[first_page], 3, 1) +
            SUBSTRING ([sa].[first_page], 2, 1) +
            SUBSTRING ([sa].[first_page], 1, 1))) +
    ')' AS [First Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 6, 1) +
            SUBSTRING ([sa].[root_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 4, 1) +
            SUBSTRING ([sa].[root_page], 3, 1) +
            SUBSTRING ([sa].[root_page], 2, 1) +
            SUBSTRING ([sa].[root_page], 1, 1))) +
    ')' AS [Root Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 6, 1) +
            SUBSTRING ([sa].[first_iam_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 4, 1) +
            SUBSTRING ([sa].[first_iam_page], 3, 1) +
            SUBSTRING ([sa].[first_iam_page], 2, 1) +
            SUBSTRING ([sa].[first_iam_page], 1, 1))) +
    ')' AS [First IAM Page]
FROM sys.system_internals_allocation_units AS [sa] (NOLOCK)
INNER JOIN sys.allocation_units au (NOLOCK) ON au.allocation_unit_id = sa.allocation_unit_id
INNER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = au.data_space_id
WHERE ds.[name] = '<filegroup name here>'

The output gave me a list of allocation units and the associated page identifiers:
Alloc Unit ID       Alloc Unit Type First Page      Root Page   First IAM Page
72059231029821440   IN_ROW_DATA     (44:2579440)    (21:2832)   (21:2812)
72059231073271808   IN_ROW_DATA     (25:4532384)    (21:3888)   (21:3883)
72059240244248576   IN_ROW_DATA     (21:656)        (21:656)    (21:1040)
72059240310046720   IN_ROW_DATA     (44:1047088)    (21:2592)   (21:2572)
72059240312340480   IN_ROW_DATA     (25:2516760)    (21:1880)   (21:1859)

These results can then be checked against DBCC PAGE to identify the underlying object:
DBCC TRACEON (3604);
DBCC PAGE (19, 21, 688, 0);
DBCC TRACEOFF (3604);

This outputs some Metadata, specifically a line that says Metadata: ObjectId = <object id>. This object ID references the object this data page is related to.
In my case, I had around 300 allocation units to work through. The concerning part is the objects identified were built on partition schemes that never referenced the filegroup in question as a destination data space, but somehow the data ended up allocated in these files. In any case, rebuilding the indexes deallocated the pages from the filegroup in question and the files and filegroup could be removed.


Display column title as in the query without 'AS' statement

There is no built-in method for modifying the column names displayed in the results of a query.  This is by-design.

Use the AS <alias> construct to control the column-names.  Format your query for readability, as in:

SELECT u.id         AS "u.id"
     , u.name       AS "u.name"
     , d.id         AS "d.id"
     , d.name       AS "d.name"
FROM user u
    RIGHT JOIN dog d ON...;

Alternatively, you could rename all the columns, prefixing them with a different prefix for each table, or making them unique more descriptively. For example: dog_id, dog_name, user_id, user_name. This would work for almost all cases (except self-joins).

Be aware that modifying the column names after you've implemented your database into production will result in a vast amount of work ensuring you've changed all affected code both inside and outside the database. On the other hand, renaming generic names like id or name would certainly be beneficial in the long run. I would expect the benefits to outweigh the trouble of going through code to fix the names after the renaming.

As an aside, the practice of using id as the name of the surrogate key for every table results in a spaghetti mess of unreadable and unintelligible code that is error prone.  Take for example:

CREATE TABLE T1
(
    id int PRIMARY KEY
);

CREATE TABLE T2
(
    id int PRIMARY KEY
    , t1_id int
);

CREATE TABLE T3
(
    id int PRIMARY KEY
    , t1_id int
    , t2_id int
);

SELECT *
FROM T1
    INNER JOIN T2 ON t1.id = t2.id
    INNER JOIN T3 ON t1.id = t2.id AND t2.id = t3.id
WHERE t2.id = 1;

Pretty clearly, the above query should be written as:

SELECT *
FROM T1
    INNER JOIN T2 ON t1.id = t2.t1_id
    INNER JOIN T3 ON t1.id = t2.t1_id AND t2.id = t3.t2_id
WHERE t2.id = 1;

If you do this instead, it becomes far clearer from the outset what is intended:

CREATE TABLE T1
(
    t1_id int PRIMARY KEY
);

CREATE TABLE T2
(
    t2_id int PRIMARY KEY
    , t1_id int
);

CREATE TABLE T3
(
    t3_id int PRIMARY KEY
    , t1_id int
    , t2_id int
);

SELECT *
FROM T1
    INNER JOIN T2 ON t1.t1_id = t2.t1_id
    INNER JOIN T3 ON t1.t1_id = t2.t1_id AND t2.t2_id = t3.t2_id
WHERE t2.t2_id = 1;

Added bonus; the output now shows somewhat more explicit column names:

╔═══════╦═══════╦═══════╦═══════╦═══════╦═══════╗
║ t1_id ║ t2_id ║ t1_id ║ t3_id ║ t1_id ║ t2_id ║
╚═══════╩═══════╩═══════╩═══════╩═══════╩═══════╝

And as you are using PostgreSQL, you can also benefit from making the join conditions more compact by using the USING clause:

SELECT *
FROM T1
    INNER JOIN T2 USING (t1_id)
    INNER JOIN T3 USING (t1_id, t2_id)
WHERE t2.t2_id = 1;

Not only do your queries become arguably more readable this way, there is also effect on the output: the join columns are not repeated. The above statement would produce output like this:

╔═══════╦═══════╦═══════╗
║ t1_id ║ t2_id ║ t3_id ║
╚═══════╩═══════╩═══════╝


Just Join the table to itself and make the appropriate sum.

This query will match each row in the table to all rows in the table with a smaller ID and the same category. You can then easily sum these rows and filter out all rows with a total greater than your limit.

Using sub queries in the where clause like you do might result in many unnecessary query executions if your sub-queries are not optimized to an INNER JOIN by the database engine:

DECLARE @Limit FLOAT
SELECT @Limit = 30

SELECT
    o1.Id
    , o1.Cost
    , o1.Category
FROM
    MyOrders o1
    -- Join all orders with same category and inferior ID
    INNER JOIN MyOrders o2 ON o2.ID <= o1.ID AND o1.Category = o2.Category
GROUP BY
    o1.Id
    , o1.Cost
    , o1.Category
HAVING
    SUM(o2.Cost) < @Limit
ORDER BY
    ID


Why are NoSQL databases not ACID compliant?

How can a database work well if there is no atomicity, consistency, isolation and durability?

They can't. This is why those features were created.

Sometimes you can work around the lack of these features because you need to scale horizontally or scaling becomes your first and primary concern. You may not need need consistency now, you may be able to handle it later.. You may not need all the data returned in order: you may want the queries executed in parallel over clusters. Or your data may itself be fairly un-valuable.

Look at major products that implement these types of systems though -- generally, they suck and they have lots of errors. Ever post something to your wall on Facebook just to see it disappear and reappear multiple times? Or to have a conversation you're commenting on vanish for an extended period of time and come back? That's hundreds of people working to manage data using "NoSQL" and compiling PHP to C++. It doesn't especially work well. It works, and for most companies that's further than they can get developing an alternative.


SSIS package deployed to SQL Server hangs when running a Powershell script

The SSIS element was a red herring. The issue was the execution policy being passed to PowerShell when running the script.

Changing from

  -ExecutionPolicy Unrestricted

to

  -ExecutionPolicy ByPass

has resolved the issue.


You can set any global variables to any value on the fly using below statement in MySQL prompt:

set global <variable> = <value>;

In your case use the following to turn on slow query log:

 set global slow_query_log=1;

By default the "slow wait time" is set to 10 sec, but you can change it as well.

Hope it helps.


MongoDB: Manual failover and failback between two datacenters?

I do not understand why the failover to DC2 has to be done manually (even if other parts have to be done manually: one thing less on your to do list in case of a major failure is always a good thing!).

In general, my feeling is that there are conceptual flaws in your setup.

Here is how I would do it and why.

I would not have manual failover. It is better to have slow access than none. What will happen in the current configuration is that if the primary fails, there will be a tie and therefor the whole set would enter secondary state, effectively turning the cluster into read-only mode. So even when everything else is fine in DC1 and there is no need for failing over to DC2, a failing primary will be a show stopper. With setup, you are artificially creating a single point of failure, effectively gainst the whole idea of a cluster, let alone a multi DC setup. Sounds like a Very Bad Idea™ to me. Automatic failover, even to DC2 sounds like a better idea. Slower reads and (depending on your write concern) slower writes still are better than read only mode.
I would have a third datacenter with only one instance: an arbiter. An arbiter can easily be run on a micro-machine as it will only be called in case of an election and an election is a cheap task in terms of RAM and computation power. The arbiter will help the set to always have a majority: If one DC gets disconnected for whatever reason, the other DC and the arbiter will form a majority. So if one DC goes down, you have only to worry about your other parts of your application. You don't have to wait
I am pretty sure that automatic failover for the other parts of your application can be achieved with some time and effort. Especially if you store all data in mongoDB and you have some sort of session replication available, it should be quite easy. Whether implementing automatic failover is worth the effort is pretty easy to calculate: Get your average downtime, find out how big the losses are created by this downtime in terms of money and customer satisfaction (if applicable). If the costs of implementing automatic failover is below or equal, go for automatic failover. I can help you with that if needed.


SQL Server Transaction Log Initial Data Load

Use bulk operations - the bcp command-line utility or BULK INSERT commands, they can generate much smaller transaction logs and they are way way faster. See this for details about the transaction log generation.

(I am answering for Microsoft SQL Server, but equivalent utilities and command are available for all databases)


How to Prevent a Many to Many When Number of Rows Shared Is Unknown

The better solution is creating the intermediate table subject_user:

table name: User_Subject
Fields: idUser, idSubject
PK: idUser, idSubject

Make both ids primary key, so one user cannot have two or more times the same subject. In other words, a subject cannot have the same student two or more times.


Log file sync occurs when a commit is made and the redo buffer needs to be flushed to disk. The session has to wait for that to happen.

An increase in the number of log file syncs generally means that one of your developers has gone commit-happy, and is committing far too frequently -- every row, for example.

Here you probably have a process that performs around 1.5 million DML statements with a commit being issued after each one, so look out for some process that loads a few million rows of data.


Best index plan for a username column inside an innodb table

Enforcing username uniqueness
If you want to enforce uniqueness for the values stored in your username column and you also have a user_id column set up as the PRIMARY KEY (PK) of the relevant table, you might be interested in the implementation of a

UNIQUE index

for the username column.
Such situation implies that, at the logical level, the username would be an ALTERNATE KEY (AK). An AK is a column, or a combination of columns, that holds values that uniquely identify a certan row but was not chosen as the PK of the pertinent table; each table can have zero, one or more AKs. As suggested above, they are usually defined in a SQL DDL structure via a UNIQUE constraint which is served, at the physical level, by a UNIQUE index.
Logical and physical levels
As it is paramount to distinguish logical from physical aspects when constructing a relational database —independently of the platform of choice—, you might find the concepts and links included in this answer of help.
Displaying a warning
If you want to display a message to the people trying to enter username values that have already been taken, you should develop such functionality in the code of the application programs that have access to your database.
Setting up the length of a column
If you don’t want to allow username values with a length greater than 18 characters, then you might like to define the column size accordingly, i.e., VARCHAR(18), so you may find of value the following content from the MYSQL reference manual about

the CHAR and VARCHAR Types,

from which I will stand out the following paragraph:

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

Prefix and Fulltext indexes
@Ypercube has enriched this answer with responses to your individual questions about some types of indexes:

Can I use Prefix index?

Technically you could but there is no reason for that. Prefix indexes are rarely - or never - useful in MySQL. If you want to restrict the length of the usernames to 18, the column should be VARCHAR(18) and not (50). If you allow only ASCII characters in the usernames, the column should have an ascii character set and not UTF-8. And even if you keep the VARCHAR(50), the index should still be in the whole column, not a prefix.

What about a Full Text index?

No, you don’t need them either. Full text indexes are for when one wants to search words or phrases in large texts. Not useful in your case. A simple (unique) B-tree index would be the best.


Update new column with value from old columns for all the rows

The solution is very simple.  If you do

UPDATE exchange
SET emp_id = {some expression here on the params column}; -- no WHERE clause!

it will do exactly what you want: pick the params from the row to be updated, calculate the expression and update emp_id with its value.  As there is no WHERE clause, it will go through the whole table.  You don't need the ID of the row or anything else.


What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?

I'd suggest to use the id and not the username, because if you start to use the username as a join column in multiple tables, you've to remember to update all of them.

The foreign key for the users table, becomes the primary key of addressdata table and the primary key must remain stable. It's a good practice to not change primary key fields. A primary key must exist when the record is created, and has to remain unchanged for the whole lifetime of the record.

If you'd like further insights The great primary-key debate is a great article.


Unable to restore database backup because it is accessed by other connection

Running this all at once does not guarantee that no one will open a connection between you putting it in single-user mode and you starting the restore. Instead, you can put the database in offline mode rather of single-user mode to avoid this problem.

E.g.

ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;


Saving all sql server transactions to txt for a transactional replication to another database

This probably isn't the best idea. The only way that comes to mind to do this is to capture the queries in a profiler or extended events trace and transforming the output to do what you want. That's going to add overhead and ultimately be quite hacky and unreliable.

You'd probably be much better off using something like log shipping, so you can take log backups from your main database, copy them to your off site server, then restore them against the database(s) on that server. Then you get all the changes in a manner you can rely on with minimal overhead.

In answer to follow-up question:
There would be no downtime. Taking transaction log backups (like any other backup) creates some overhead, but it is generally good practice to be doing this regardless of needs to replicate the data.

Log shipping is generally easy to set up:
https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-2017

But you may need to craft your own equivalent if you don't have easy connectivity between the two servers.

Before you start you need to make sure the source database is in FULL recovery model (you can't take transaction log backups otherwise) and restore a copy of it to the destination server using a full backup with the STANDBY option. STANDBY will put the database in the state where it is readable (but not writeable) and transaction log backups can be applied.

Then your log shipping consists of three components:

1) Have a process on the source box (a SQL Agent job usually) that takes log backups on your source database. The more often you take these, the closer to real-time your destination database can be.

2) A process that copies the transaction log backups to your destination server.

3) Another process, this time on the destination box (again usually SQL Agent) your restores the copied logs to your destination server. Again, using the STANDBY option.

Apart from that you just need some housekeeping, e.g. to remove the log backups once they have been restored or after a certain length of time.

When you use the "built-in" version of log shipping you get some extras, such as monitoring and logging of what is going ob, but overall it is a simple process, so not too hard to replicate on your own.


What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG

"I could use a tranlog backup, and then roll it to 12:01am"
Do that.  You can restore the last full backup, then apply log backups with NORECOVERY in the days or hours before the first of the month, then apply the last log backup with STOPAT and RECOVERY or STANDBY.
Or you could create a Database Snapshot at midnight on the first of the month.  That would avoid copying all 5TB at the beginning, and the snapshot will only store pages that change over the course of the month.


How do I stop a query from running if the row is locked?

you can use GET_LOCK() to avoid the query to run if other query is running on the same lock name.

you will basically do:

SELECT IS_FREE_LOCK('maintenance');
#IF RESULT IS 1
SELECT GET_LOCK('maintenance', '10');#it will get a lock named maintenance
#PERFORM ALL YOUR MAINTENANCE
SELECT RELEASE_LOCK('maintenance'); #it will release a lock named maintence

what will happen is that the thread 1 will get a lock named maintenance, while this thread don't release this lock, the command SELECT IS_FREE_LOCK('maintenance'); won't return 1.


Backup and restore of TDE Databases to different server

You need to refer to architecture of TDE encryption

  Do I have to backup the Database Master Key and restore it? Or is it enough to recreate it with the same password?

Yes and NO. Always backup your DMK !

Yes

  The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.

Yes - when you have to restore the database during a DR scenario (rebuild the server).

No - when you dont have the cert Private key password and want to restore the cert to different server, you can simply backup the cert with a new password and restore on the different server.

Remember that - A DMK does not encrypt the database. It is used to encrypt the certs and other keys to keep them protected.

  Similarly to #1, do I need to backup and restore the Service Master Key?

A rare scenario, but its good to have it backed up as part of best practice. A SMK (##MS_ServiceMasterKey##) is the root of encryption and is generated automatically the first time it is needed to encrypt another key. If SMK is corrupted, then you might need to restore it.

For 3 and 4 ..

You can, but why would you do it ?

You have to turn off encryption, create a new cert with new Private key and then turn on encryption. Depending on your database size, this can take some time.

Remember that a DEK is the one that does the encryption and decryption of the database.

As a side note, refer to this script for info on the databases, encryption status along with other useful information.


A PIVOT that will work in most DBMS is:

select name
     , count(case when Result = 'Win' and Month(dt) = 1 then 1 end) as Jan
     , count(case when Result = 'Win' and Month(dt) = 2 then 1 end) as Feb
     , count(case when Result = 'Win' and Month(dt) = 3 then 1 end) as Mar
from t
group by name

I used dt as name for your date column since date is a reserved word. As others have mentioned, there may be better solutions for a specific vendor so you should always tag your question with that, unless you are looking for a general solution.


SQL Server 2008 R2 Reporting Services: "Exception of type System.OutOfMemoryException was thrown"

Upgrading the server to a 64 bit version of Windows, and a 64 bit version of SQL Server might help.

However, what are you doing with a report with 600,000 rows?  If you are using this to export data, you should use SSIS or some other tool designed for the job.  If you are summarizing the data inside the report, re-write the source query to summary the data at the server prior to it arriving at the SSRS instance.


Couchbase 4.5.1: What is beam.smp and why it is consuming too much memory

beam.smp is the Erlang vitual machine used by CouchDB (as CouchBase) for serving and indexing your DB. So it's where the magic happens.


As with all database design don't think just about what you want to store but also about how you wish to retrieve and modify the information.

If you never need to refer to or search by individual parts of the contents then your first schema is fine. Similarly if you do need to do those things but you are suing a database that allows efficient indexing and manipulation of JSON blocks.

As a general rule though I would normalise the data which means breaking it down as you have in separate tables rather than using one column to store many values.

BTW: the pattern you seem to be heading towards in your second example is called table inheritance - you have a general "posts" object which contains properties common to all your object types and a detail table for each object containing properties that are unique to that object (or at least are not common to other object types). Unless one post could contain multiple photos (or videos, or ...) which is implied by your first design then you don't need a separate ID for each detail table: there will be zero or one rows per row in posts so the post_id is valid as both the primary key and foreign key. Also there is no need to repeat user_id in each table (as that can be derived from the link to posts) unless a different user could add the photo/video to someone else's existing post.


T-SQL bulk insert fails on specific word "DENY" in string-data

After some experimentation I found the following:

The word DENY is indeed a red herring. The problem is caused by the (xx,xx,xx,xx) sequence behind it.
For some obscure reason this causes errors if the bulk insert is run from a dynamic SQL string.
(I was using dynamic SQL as I need to iterate through a list of input filenames derived from another table. bulk insert doesn't allow the use of a variable for the input filename. So I had to programmatically generate each bulk insert statement and then EXEC() it.)

The bulk insert with the same input file works fine if run directly. BCP import of the same file is OK too.

If the column containing this sequence is NOT the last column of the input row, it works as expected.

As I have full control of the script that generates these files I could add a 4th dummy column containing just a =. This solved the issues with the bulk insert.

Obviously I still don't understand the root-cause of this. At least I have a workaround.

Microsoft states on MSDN numerous times that

  "bulk insert of CSV files is not supported, but may work in some
  cases"

They NEVER say why or what the exact problem is...
Makes me wonder if I have run into a known problem...


Fixed length text file insert into SQL table

As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.

Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step would be to apply a split function. Here is an excellent article about the popular options for such a function. You will need to choose or adapt a function to handle (n)varchar(max) if your files exceed the standard limits. You'll just use your single row of data as the input parameter, and the carriage return as the delimiter.

Once your data is broken into rows, you should be able to liberally apply substring() to slice out your columns for each row type.

This probably won't perform well, but if you need to show a proof of concept to get access to SSIS, it might suffice.


Speeding up linking Oracle tables in Microsoft Access

What if you create a special Oracle user that Access would use to connect? That user would only have the ability to access the tables that are relevant, plus it would have a synonym for every table it had access. Then when you connect from Access to Oracle, Access would only populate the table list with the tables that you want to see. I'm going to guess that you have at least tens of thousands of tables in your schema if not hundreds of thousands of tables. Are you running Peoplesoft?

You might want to find other tools to work with besides Access. There must be other BI tools that you can work with. Possibly even a few open source tools.


Oracle text search filter is capable of automatically parsing non-plain text documents, such as MS Word or PDF, while Postgres' text search can only handle plain text documents. You'll have to convert whatever is stored in your BLOB column to plain text before loading those data into a Postgres TEXT column, then build the Postgres text search index on that.


Should I clean up my old table statistics?

Check out this article.
I think it will answer your questions and give you the tools to deal with over-inflated number of auto-statistics.
The author suggests dropping ALL auto-created stats occasionally, as the one that are required will be recreated when the first query that needs it is executed. It explains the risks / benefits.


XQquery for specific attribute of all specific elements in XML

Almost get it:

select
    d.lin.value('./@NetAmount', 'decimal(10,2)') as Amount
from
    t
cross apply
    data.nodes('ROOT/CATEGORY/PRODUCT') as d(lin);

| Amount |
| :----- |
| 10.50  |
| 20.50  |
| 30.50  |
| 40.50  |
| 75.50  |

db<>fiddle here


Find rows in a single table where values in a certain column are near-duplicates

You need to exclude the rows from each other:

SELECT c.email
  FROM schema.table c,
       schema.table d
 WHERE lower(c.email) = lower(d.email)
   AND c.email <> d.email;

One question you will need to answer (and I will update my answer if relevant) -- are you using ILIKE because you want partial matches too?  Like, are bert@foo.com and RObert@foo.com supposed to be considered duplicates?  (I would assume not, but when people use ILIKE, it's usually to patch parts of strings, as in the bert/robert example)


I need to find what tables have been changed in the last period

You will need dynamic SQL for that. For example a table function, that loops through the table names in the schema, build each query, execute them and return their results.

CREATE
 OR REPLACE FUNCTION xmins
                     (_schemaname name DEFAULT 'public')
                     RETURNS TABLE (tablename name,
                                    maxxmin xid)
AS
$$
DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT t.tablename
                   FROM pg_catalog.pg_tables t
                   WHERE t.schemaname = _schemaname) LOOP
    RETURN QUERY EXECUTE '
SELECT ''' || r.tablename || '''::name,
       max(xmin::text::integer)::text::xid
       FROM ' || r.tablename || ';';
  END LOOP;

  RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM xmins();


PostgreSQL - CAST vs :: operator on LATERAL table function

It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression.
So that is indeed a trick you can use if you want an arbitrary expression in a FROM clause: surround it with an unnecessary CAST expression.
PostgreSQL will happily treat anything that looks like a function as a table function in such a context.


Will 'sleeping' connections open files other than the socket in MySQL?

SHOW VARIABLES LIKE '%open%';

Probably one of those is too low.

At a shell prompt, do

ulimit -n

That will tell you what the OS is letting you have.  In some recent OS builds, it was the ridiculously low value of 1024.  If so, increase it to, say, 16000.  Then restart mysqld and see if the SHOW increased the values.  (Some default values are computed.)  If it did not, then go into my.cnf to increase some things.

"Sleep" processes may be hanging onto open files, but they won't be getting more.

Do you have a large max_connections?

Do you have lots of tables?  Lots of PARTITIONs?  Each PARTITION uses 1-3 files.  (This is one of several reasons for not breaking a table into more than, say, 50 partitions.)

Oh.  You are using MyISAM.  It needs 3 files per table.  Switch to InnoDB, with innodb_file_per_table = 1 -- that way you need only 1 file per table.


SELECT SUM(pm_jan > 0) count_pm_jan,
       SUM(actual_jan > 0) count_actual_jan,
       SUM(pm_feb > 0) count_pm_feb,
       SUM(actual_feb > 0) count_actual_feb
FROM pm


A user that was explicitly created with WITHOUT LOGIN cannot be mapped to a login after the fact.

A user that was created with a login and then lost its association, e.g. because the database was moved to another server, can be remapped by first creating a new login (any name) and then using the ALTER USER ... WITH LOGIN = ... command.

To demonstrate I am going to create a database tst and then create a login tstlogin and an associated user tstuser within tst:

CREATE DATABASE tst;
GO
CREATE LOGIN tstlogin WITH PASSWORD = 'passw8rd';
GO
USE tst;
GO
CREATE USER tstuser FOR LOGIN tstlogin;

To show correct association we can use the following query:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

This results in

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin

Now we will drop the login tstlogin

DROP LOGIN tstlogin;

and recheck our user:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

which does not show an associated login anymore:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        NULL

Next step is to create a new login. (I chose a diffenert name for demonstration purposes, but it could be the same name too.)

CREATE LOGIN tstlogin2 WITH PASSWORD = 'passw8rd';

To associate that new login with our user we can use the ALTER USER statement:

ALTER USER tstuser WITH LOGIN = tstlogin2;

Checking the association once again:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

shows that the ALTER statement was successful:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin2

If you actually need to change a "WITHOUT LOGIN" user to be a "with login" user, you will have to drop that user and recreate it. Make sure in that case to script out the permissions first, as dropping a user causes SQL Server to forget those.


Pause and resume execution of stored procedure

There's no clean way to just pause and resume a stored procedure, but you can have it sleep for a fixed period of time using WAITFOR DELAY 'XX:YY:ZZ' (where X is replaced with hours, Y minutes, and Z seconds for how long you want the thread to sleep).
It really depends on what p2 is doing and if there's multiple batches of queries you can break up with a check for if p1 is running. If p2 is just one single long running query then you won't be able to pause it when p1 starts, you'd only be able to make p1 wait until p2 was finished. (In this case you shouldn't have a single query running as long as a day though, so I'd recommend optimizing p2 anyway.)
If you do have a way to break up the queries in p2 then you can periodically check if p1 is running between each batch of queries, use a WHILE loop and the aforementioned WAITFOR DELAY to sleep the thread (whatever time  amount is acceptable to you) of p2, checking if p1 is done running on each iteration of the WHILE loop.
I don't think there's a perfect solution if you're trying to avoid any overlap, but if some overlap of the jobs is ok then you should be able to rig something with the above up.
You might be able to help prevent any data overlap (at the cost of potentially making p1 wait a mininal amount of time) by using transactions around each batch of queries in p2 that way p1 will have to wait until p2 finishes it's active transaction. If you want to avoid overlap for performance reasons instead, then you'll need to implement a check system in p1 as well to loop and sleep while waiting on p2 to finish it's current query batch before it sleeps itself once it checks p1 is now running.


How does DYNAMIC Row format handle NULL storage MYSQL 5.7?

The second bullet point 'The variable-length part of the record header contains a bit vector for indicating NULL columns'.

So when a row has a field that is NULL, the bit in the bit vector of the header is set.

AFAIK this is the same for DYNAMIC/COMPRESSED that really only changed the handling of variable length fields.


How to get the Oracle Text Index's underlying Virtual XML Document?

Probably not

The view that may display native stored compiled indexes is CTX_INDEX_VALUES Or CTX_USER_INDEX_VALUES (depending on permissions and querying user). However, given that the index value length is only 500 characters, it is unlikely that it is stored there. (Just start selecting randomly from those indexes, if it exists, it will likely be there.)

Looking at the view design, however, it is unlikely that those virtual XML elements are stored, as it's much easier to compile them on the fly and then load them into memory.

Alternatively, CTX_DOC has a number of markup functions, but none indicate the ability to access native XML.

You may also want to explore the discussion around metalink: 122255.1 (Unfortunately, I don't currently have metalink access).

It may be easier to write your own parser-to-XML (as oracle has quite a few excellent native XML functions) than try to hack around the full text index.


On your indices

Ok, first things first. Assuming some structure like this

{
  _id: new ObjectId(),
  date: new ISODate(),
  message: "Hello, Multikey Indices!",
  tags: ["MongoDB","Indices","Multikey"]
}

indexing tags would result in a multikey index. For the document above, the index would have three entries: "MongoDB", "Indices" and "Multikey", all pointing to the same document. The index would have around 200 bytes. Now let's assume the worst case scenario that each document had tags distinct of the tags of each other document, resulting in three new index entries per document. Even if we multiply those 200 bytes with 100,000,000, the result is just 20Gb.

And here comes the good stuff: MongoDB keeps indices in RAM as long as they fit. And for memory access, we are talking of somewhere of 75ns (mind you, a nanosecond is a billionth of a second). So even in our worst case scenario, our index will be searched pretty fast.

As for the sorting: As a rule of thumb, one should assume that MongoDB can use only one index per query. While this is not entirely true as there are index intersections as of MongoDB 2.6, it can be a rather delicate topic. Since the individual fields of compound indices can be used independently, it is pretty reasonable to create a compound index here. But: order matters. You query for tags and then sort by date, so your index should be created like this

db.collection.ensureIndex({tags:1,date:1})

in case you sort in ascending order when doing the query or this

db.collection.ensureIndex({tags:1,date:-1})

for a descending sort order.

That being said, we can now have a look how to ensure performance for a MongoDB cluster.

On the real problem (IMHO)

IMHO, your mistake is to assume that it is a technical question to ensure performance. I strongly disagree with that. There are a few things which can be done from the technical side (such as proper indexing), but at the very bottom, ensuring the performance of a MongoDB cluster is an administrative task. Let me explain this a bit more detailed.

From my experience, you need to adapt the dimensions of your hardware to your needs. If you need to have certain queries to be fast, that's easy to achieve. But if your hardware lacks the necessary resources, your options are either to scale up/out or accept performance impact. (In your scenario, your hardware seems to have sufficient RAM, btw.)

Here is what I do when I expect that I need to scale.

For my initial hardware, I see that I get the most bang for the buck RAM- and IOPs-wise. This way, I am most cost efficient and scaling up does not make any sense any more.
Since I know that my only cost efficient option is to scale out, I make a written plan for it (what to do, who has to make the "go" decision, how to migrate, when to put a maintenance window, etc) and discuss it with the stakeholders. If service interruption is not an option, I start with a sharded cluster with a single shard. That's often viable, since config servers can easily be run on extremely cheapo VMs, which you often can run for years (or even – admittedly theoretical – decades) before this approach becomes more expensive than a service interruption. I strongly suggest the latter approach, since scaling out becomes as easy as adding a shard to the cluster and you don't need to deal with the intricacies of sharding when you already are in sort of a pinch (data migration included for free, too). But make sure you have a written plan for this, too, agreed on by all stakeholders.
I set alerts for disk and RAM usage. As a rule of thumb, an alert when either usage of reaches 85% should give you plenty of time to execute your scaling plan.

From my experience, this is the best practice to keep the performance of MongoDB high.


Testing if temp table exists in iSeries SQL stored procedure

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement.  This will create your table in QTEMP regardless of whether one already exists or not.


How to connect to a local SQL Server instance by IP Address in SQL Server Management Studio?

One thing you could try is to set Enabled to "no" for all individual Ip Addresses, and at the very bottom specify the desired port. I generally use the same port for all IPs, so I catch them all with just one config. If for nothing else, as a troubleshooting measure.


The BrentOzar team's new PowerBI dashboard functionality for the First Responder kit is pretty awesome.  I use it every day to monitor/diagnose issues and it's super popular as an iPad/Surface dashboard.
https://www.brentozar.com/first-aid/first-responder-kit-power-bi-dashboard/

**I should elaborate that I use their tools to gather data from many different servers into one central reporting server, and that's the one that runs the PowerBI Dashboard.  So I can monitor all of my servers at once and from the same dashboard using these features.  This means that if the offending server crashes, I can still monitor all details until (at most 15 minutes) right before it crashed.

Other than that, I've found Dell/Quest's Spotlight on SQL Server useful too.
https://www.quest.com/products/spotlight-on-sql-server-enterprise/


PostgreSQL: COUNT(*) uses a sequential scan, not index

The official wiki pages give an answer to that:

  [...] The reason why this is slow is related
  to the MVCC implementation in
  PostgreSQL. The fact that multiple
  transactions can see different states
  of the data means that there can be no
  straightforward way for "COUNT(*)" to
  summarize data across the whole table;
  PostgreSQL must walk through all rows,
  in some sense. This normally results
  in a sequential scan reading
  information about every row in the
  table. [...]

Furthermore, you could try an ANALYZE to rebuild the info for the query planer.

You should get a better performance using COUNT(an uniquly indexed field) but if this is very big, a seq scan is the only way to do it.

If you need very quick numbers and are not afraid of querying the schema, you can do the following

SELECT reltuples FROM pg_class WHERE oid = 'your_table'::regclass

But don't rely on this values as it is only an "estimated" (although often the exact) number of tuples in the table.


What is wrong in debunking myths about clustered indexes?

That script has a fairly wide, varchar clustered index. And it needs an index rebuild after populating it with random data too: you'll have massive fragmentation.

A good clustered index is narrow, numeric and strictly monotonically increasing: which is why folk use surrogate keys...

A table without a clustered index is called a "heap" because it exactly that: a pile of data lying on disk. And it will stay that way no matter how much you rebuild your NC indexes. Outside of something like staging tables (with a load/truncate usage pattern) there is no reason not to have a clustered primary key.

Edit:  The link does not debunk a clustered index myth, but rather shows how to create an unsuitable clustered index and why index maintenance is important. Parts 1 and 2 mentions bookmark lookups (now key lookups in SQL Server 2005+): a good NC index will be covering so they don't happen.

To learmn about indexes, I suggest Simple Talk's many articles. Like this one


What is faster - insert into new table, or delete from existing table?

It very much depends on the details of your database.

Factors include:

Number of records to be copied/retained;
Whether you're keeping all columns or not; and
Number of indexes on old and new table (as alluded to by @a_vlad).

If I needed to delete most of the records in a table, I have definitely had times where it was faster to copy the records to another table, TRUNCATE the first table, and then copy the records to be saved back to the first table (with all indexes, constraints, etc.; the other table would usually be stripped down to just the raw data).

That said, if you're keeping notably more records than you're deleting, and if you don't have a lot of indexes on the original table, it's entirely possible that deleting would be faster.

NOTE: If you don't need to keep all columns, then the INSERT method is almost certainly your best bet.


Composite Foreign key to single primary key

The answer is a little bit like the old joke: "Doctor, doctor! It hurts when I do this."

If you want a foreign key to an entity then that entity needs to have a unique key defined to be referenced.

The fact that your Expectations table has a unique candidate key in Expectations.ExpectationId should not prevent you from defining another candidate key that combines ExpectationId and BranchId.  This seems like a violation of third normal form, and technically it is.  However, that is only true because you happen to define ExpectationId as unique.  You could have defined ExpectationId as a sequence within BranchId, such that ExpectationId is not unique on its own.  As it is, you can think of it as the Expectation table defining the combination of two unique values as being the fact of interest.

In any case, if you want to define a foreign key to Expectation that is the combination of ExpectationId and BranchId then there is no way around it but to define the primary key that way.


SQL Server 2016- send automatic email after store proceedure executes

There are a number of ways to do this.

Edit your SP to include a call to sp_send_dbmail upon successful completion of whatever logic you have.  I would suggest nesting this into some TRY/CATCH logic.  This will be the most direct route and will allow the notification(s) to occur regardless how the SP is called.
Call your SP via a SQL Agent Job, and configure it to send a notification to an operator upon conclusion of the job (e.g. completion, success, or failure).
Call your SP via a SQL Agent Job and include a secondary step calling sp_send_dbmail.  This is helpful if there are more than one party or email distribution list you want to notify as the built in notification methods within a SQL Agent job are rather limited.


"No Loop" Constraint for hierarchical data in postgresql

You can't express the constraint that an edge-list must be acyclic as a CHECK constraint.

It would have to be an ASSERTION constraint, which PostgreSQL does not support.

You'll have to LOCK TABLE to prevent concurrent INSERTs or UPDATEs and check it with an AFTER trigger.

The resulting lock upgrade issues will result in frequent deadlocks. To prevent them your app will have to lock the table before inserting/updating, e.g.

BEGIN;
LOCK TABLE my_table IN EXCLUSIVE MODE;
INSERT INTO my_table (...) VALUES (...);
-- Trigger runs to sanity check table here
COMMIT;

You might find it useful to make the trigger a deferred constraint trigger, so the table is allowed to temporarily enter invalid states so long as it's valid again by the time you commit.


Convert date yyyy-mm-dd to integer YYYYMM

On version 2012 or higher you can use the format function to get just year and month, then cast it as an int.

On versions prior to 2012 you can do the formatting with the convert function, then cast as int.

declare @dateb datetime
set @dateb = getdate()

select cast(format(@dateb,'yyyyMM') as int) --2012 or higher
select cast(convert(varchar(6),@dateb,112) as int) -- all versions


Optimal query pattern to count related entries in PostgreSQL

nested selects  can be slower or be faster depends on how many records are being processed.

select *, (select count(*) from categories b where a.id=b.parent_id)
   from categories a

This can be faster or have the same speed if only a few records or with where clause filters this down

select *, (select count(*) from categories b where a.id=b.parent_id)
   from categories a  where a.id = 5

A faster solution for all the records would be something like this

Select categories.*, coalesce(counted, 0) from categories
   left join ( select parent_id, count(*) as counted from categories group by parent_id) child
   on categories.id = child.parent_id

with this query  planner has an easier time figuring out what to do.  Nested selects the planner has harder time with as it has to treat them as VOLATILE.  meaning it has to keep calling the nested query for each record


According to your SQL statement you always get a unique date from a group VesseleID since you are using MAX from group. By using join statement on date you can get your desired result.

Explanation:

-- Complete Data Set
1. Select * from DailyYardBalTable;

-- Identical to your Query
2. SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable
   WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID

--Desired Result Set

3. SELECT T2.VesseleID,T2.CargoDate,T1.Balance FROM DailyYardBalTable T1
   INNER JOIN
    (SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable
    WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID) T2
  ON T1.CargoDate=T2.CargoDate GROUP BY T2.VesseleID,T2.CargoDate,T1.Balance

Here is the data set respective to the SQL statement #1, #2, #3.


Is there any risk in SELECT ... FOR UPDATE outside transaction scope in MySQL?

A query is always in a transaction even if you don't start it explicitly.
SELECT FOR UPDATE acquires an exclusive lock on rows it matches. There is a possibility that other transactions want to modify same records and will have to wait until the SELECT FOR UPDATE finishes and release the lock.

So. Nothing bad will happen if you run SELECT FOR UPDATE, but overall performance may be lower due to possible lock waits.

Deadlocks are possible, too. If other transaction holds a shared lock and than wants to update the records while SELECT FOR UPDATE is waiting.


The Apache logs show what the Apache Web Server has been asked to do.
It generally knows (nor cares) not a jot about SQL.  Of any flavour.
Please, please, please tell me you're not submitting raw SQL statements to your web application through the URL and/or QueryString and executing them!
Obligatory XKCD Reference: Little Bobby Tables.
This is a huge Security Risk and, if Apache really is logging queries, including Personal Data into its own, largely unsecured, log file then you're heading into very dangerous waters with regards to Data Protection legislation (e.g. GDPR).


Catching two error messages/Throw into table

You can enhance error handling with Extended Events to overcome the TRY...CATCH shortcoming you are experiencing.  The design pattern involves these steps:

Create and start an Extended Events Session: it will capture sqlserver.error_reported events, filtered primarily by SPID.
Execute a statement in a TRY block.
Within (or after) the CATCH block, read the XEvents session data.
Use the available data to respond to the error(s) as appropriate.
Stop and drop the XEvents session.

Here's an example that can be run manually in a single batch from SSMS. Just make sure to replace all occurrences of "2016" in the script with your SPID.

CREATE EVENT SESSION [Error Handling Session(2016)]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION(
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE [package0].[not_equal_unicode_string]([message],N'''''')
    AND [severity]>(10)
    AND [sqlserver].[session_id]=(2016)
)
ADD TARGET package0.ring_buffer
WITH (
    --ALLOW_SINGLE_EVENT_LOSS
    --NO_EVENT_LOSS
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_MEMORY=4096 KB,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=OFF
);

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=START;

BEGIN TRY
    BACKUP DATABASE master
    TO DISK = 'master.diff.bak'
    WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
    DECLARE @XEData XML

    SELECT @XEData = CAST(xet.target_data AS XML)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
        ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Error Handling Session(2016)';

    /*
    Check value of "totalEventsProcessed" to ensure events have been
    dispatched to event session target (ring_buffer).
    If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
    */
    IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
    BEGIN
        WAITFOR DELAY '00:00:02';

        SELECT @XEData = CAST(xet.target_data AS XML)
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'Error Handling Session(2016)';
    END

    --Comment/uncomment as desired to show the formatted XML session data.
    SELECT @XEData;

    --Shred the XML. Do whatever you want with this data:
    -- log it to a table, create and send an email alert, etc.
    SELECT
        x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
        x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
        x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
        x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
        x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
        x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
        x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
        x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
        x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
        x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
        x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
        x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
    FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
    --WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'

END CATCH

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(2016)] ON SERVER;
GO

After running the script, your SSMS output should look similar to this:

There's a lot of moving parts there. But you might be able to make it work for you. I wrote a few related blog posts about TRY...CATCH that may be helpful:

The Unfulfilled Promise of TRY...CATCH

Enhanced T-SQL Error Handling With Extended Events

Part 2: Enhanced T-SQL Error Handling With Extended Events


Perhaps you need to run this as a GROUP BY aggregation using the GROUP_CONCAT function.

SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER;

CAVEAT #1

In your DB Session, you may need to extend the length of [GROUP_CONCAT} output because its default limit is 1024. Run this before each connection to set limit to 1M:

SET group_concat_max_len = 1048576;

CAVEAT #2

The above query will only give you ticker_returns if the stock symbol was traded with the specified TDATE range. If you want all tickers to show up regardless of whether or not it had returns in the specified TDATE range, do this:

SELECT A.TICKER,IFNULL(ticker_returns,'<No Returns>') ticker_returns
FROM
(SELECT DISTINCT TICKER FROM STOCK_DATA) A
LEFT JOIN
(SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER) B
USING (TICKER);

If you want a count for each TICKER, just count the number of commas in the ticker_returns and add 1 if the ticker_turns is not null. If you want a query with the count for each ticker, do this:

SELECT A.TICKER,IFNULL(ticker_return_count,0) ticker_return_count
FROM
(SELECT DISTINCT TICKER FROM STOCK_DATA) A
LEFT JOIN
(SELECT TICKER,COUNT(1) ticker_return_count
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER) B
USING (TICKER);

GIVE IT A TRY !!!

For increased query performance, please create this index during your off hours:

ALTER TABLE STOCK_DATA ADD INDEX (TDATE,TICKER,RETURN1);

This should speed up the query.


Load test with databases. Best practices , tools

Take a snapshot of your production database and at the same time enable the general log. Record 24 hours of a general log on what is a fairly typical day.

Restore the data to clean server.

Write a few lines of your favourite $scripting_language to parse out the general log and split it up into n files where n is at least the number of CPU threads you have in the server. Also, separate SELECTs, INSERTs, UPDATEs, and DELETEs into separate sets of files.

Replay them in parallel, SELECTs only, to prime the caches. Then do it again and time the 2nd run.

Now you have a baseline to measure everything else against. From there on, triple up the number of rows in the table by adding more rows, and replay it again to measure the performance.

No generic benchmark will ever come close to producing as meaningful a benchmark number as this method.


Connection Pooling for PHP based website using Oracle 11g database

where would I be setting the number of connections?

You can specify the min and max number of connections in the pool using

SQL>execute dbms_connection_pool.configure_pool(null, minsize=>10, maxsize=>100);

  If the database servers are unix boxes, can we do any ODBC connection pooling?

I'm not quite sure why you're asking this.  You can take advantage of DRCP via OCI in PHP, as long as you have the Oracle 11g client.  Just specify your connection string like myhost.dom.com:1521/sales:POOLED and make your connections using oci_pconnect().

Here's a few resources that may be of help:

Oracle DRCP Whitepaper
DRCP entry on OraDBPedia
PHP OCI manual


Access service management console via Run->services.msc.
Locate  OracleXETNSListener service from the list, Right click and open 'properties' window.
See Log Ontab, update Log On as account, normally Network Service will do the trick if Local System account is not working for you.


Start by watching Brent Ozar's  introduction into the subject SQL on SSDs: Hot and Crazy Love. Follow up with the extensive and exhaustive benchmarking series from Paul Randal: Benchmarking: Introducing SSDs (Part 1: not overloaded log file array). There are many details, depending on workload type, dat vs. log, placement of tempdb etc etc etc.