Could you give us some info on the replication config in your config files.

For sure if you replicate one db at a time and you need to add one, you won't have a choice to change your config then to restart your instances.

If you can I'll suggest more a full replication. less trouble and if you need to add dbs, there is no problem there.


SQL Database mirroring \ replication capabilities?

Mirroring won't do this...its one way.   You need to investigate either Transactional or Merge Replication for the two-way communication, if you want to stay in the SQL Server capabilities.   merge helps if you have bad bandwidth or network drops.   Both Tx and Merge have their own unique requirements that are well documented on Technet :)


Both sessions have locks on the second key.  The reader has an S and the writer has a U and wants to convert to X.  Consider changing the database to use READ_COMMITTED_SNAPSHOT mode, or add an XLOCK hint to the UPDATE so it doesn't read with U locks and then convert to X.

  What are these modes.

I think that is the most restrictive lock locks currently held on the resource, as opposed to the locks requested.  This is often the lock that is incompatible with the blocked session's request, but not always.

  Why is this mode not S.

Because the most restrictive lock currently held on the resource is the U lock held by the writer session, and which it has requested to "convert" from U to X.


How to insert values into a table from a select query in PostgreSQL?

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

dbfiddle here


1. How to understand estimated operator cost? Tb1 which don't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%.

The heap table is only fully scanned once, but the index seek is executed 1,000,000 times. The optimizer estimates that a million seeks in this case will represent 98.4% of the total cost of executing the query, whereas a single parallel scan of the heap table will represent 0.9% of the cost.

These are just estimates used for internal plan choice reasons; they do not generally reflect real-world performance on modern hardware, and are never anything more than an estimate - even in a post-execution ("actual") execution plan.

In Management Studio:

In SQL Sentry Plan Explorer:

  2. From the above snip of table scan (whose cost is 2%),number of executions are 24,will that means sql read rows in batches and stored in memory and for each row it did a seek operation from tbl2.

No, it means 24 parallel threads co-operated to perform a single scan of the heap table. Each thread still reads a row at a time from the scan, performs a seek into the indexed table, then gets the next row from the scan, and so on until the task is complete.

Rows are not read in batches and stored in memory in this plan. SQL Server reports 24 scans because 24 threads each performed a partial scan of the table, resulting in one full scan overall.

  3. Also any pointers to know more about force scan,force index when I pressed F4 after clicking an operator

The ForceScan, ForceSeek, and ForcedIndex properties are set to true if the query specifies a FORCESCAN, FORCESEEK, or INDEX hint - or if the query optimizer decides that a particular access strategy is required for correctness (for example, when checking foreign key constraints).


SQL Server 2012 Enterprise: Copy Databases to New SQL Server, Same Domain, Users, Etc

Run this on your current instance:

select
    'CREATE LOGIN ['+p.name+'] FROM WINDOWS;'
from sys.server_principals as p
where type in ('U','G')

Take it's output and run it on the new instance.  Those AD accounts will now have logins on both instances. (This is a good opportunity to audit your access and ensure all those users really are needed.)

Backup your databases (a proper SQL Server backup, not a copy of mdf and ldf files) and restore these to the new instance. You may have to get creative with the restores if the drive names have changed.  If there are a lot of DBs some RESTORE FILELISTONLY and a script will save much time.

Finally, on each DB, script an ALTER USER to reconcile LOGINS to USERS.  This can be meta-scripted as above.

Test connections to the new instance.  Update the applications' connection strings.  Take the old DBs offline and, after a few days, tidy away the old server.


reduce network traffic for MySQL queries

A few considerations:

Ensure your tables have indexes which serve the queries well. The best designs have an index on any column which appears in a where clause.

You can also have multiple columns in an index. See composite indexes.

If your accessing the same data multiple times you can look into caching, with something like memcached.

If you regularly summarise the data and access that same summarised data regularly you could put stored procedures in your database to update a summary table every time you enter new data into your fact table.

By no means the best possible answer but definitely the first things you should consider on a growing dataset.

I'll add some referencing links later but I'm on mobile right now.


Use the system views for this:

select * from sys.check_constraints
where is_disabled = 1;


Merge two applications using different NoSQL databases

The important thing to remember here, is that a commonality NoSQL databases tend to share, is that a query-based approach needs to be taken with respect to table design.  The main driver for that, is going to be around how the data is queried.
Another consideration is around each data store's adherence to the CAP theorem.  NoSQL tends to side with the P of partition tolerance, and then also choose another side.  If the NoSQL products support different sides of CAP (AP vs. CP), you'll need to figure out if the overall application is more dependent on consistency or availability.
I would also consider if search is a part of the application, or rather, if the queries require any pattern matching.  Some data stores handle this better than others, so it would be good to understand how each functions in those use cases.


Better defining a parent-child relationship on a table

There are several models for hierarchical data in relational databases. These include

Adjacency List. This is your suggestion above.
Path Enumeration. This is the DBA's suggestion.
Nested Set
Closure Table

Each has a different insert, delete, neighbour-read and set-read characteristic. You will have to determine which, on average, is best for your application performance and development needs. For example, nested sets is great for reading a whole sub-tree but is expensive for inserts, and can be difficult to comprehend. Adjacency list is simple to understand and code, read/write is fast but frequent, large recursive queries can be expensive.

Since you're using SQL Server be aware of the hierarchyid data type.

I have found Joe Celko's book "Trees and Hierarchies in SQL for Smarties" to be most readable and informative.

There's a discussion on Stackoverflow which discusses this widely.


How to represent a many to many relationship with Crow Feet diagram

Yes that is correct.

Of course you no longer actually have a many-to-many relationship in your model.  You are no longer "representing" the relationship, as you say in your question.  You have, in fact, resolved the relationship into an association entity type with two one-to-many relationships.


Find missing folders paths in split hierarchies

I'd try something like this:

First, create a new table and copy the existing data there.
Then iterate, and in each iteration, remove the last node of each filepath (e.g.: 'A\B\C\D\E' becoming 'A\B\C\D') and add these new filepaths in the table, if they aren't already there.
Stop when an iteration doesn't produce new rows.

The initialization:

-- step 0
CREATE TABLE filepaths
  ( BaseDirID int NOT NULL,
    DirLevel int NOT NULL,
    DisplayPath varchar(1000) NOT NULL,   -- adjust the size according to your data,
    ReverseDisplayPath varchar(1000) NOT NULL,
    Iteration int NOT NULL,
    PRIMARY KEY (Iteration, ReverseDisplayPath),
    UNIQUE (ReverseDisplayPath)
  ) ;

INSERT INTO filepaths
  ( BaseDirID, DirLevel, DisplayPath, ReverseDisplayPath, Iteration )
SELECT
    BaseDirID, DirLevel, DisplayPath, reverse(DisplayPath), 0
FROM
    existing_table ;

And the iterations:

DECLARE @new_items bigint ;
DECLARE @iter int ;
SET @iter = 0 ;

-- repeat
repeat:
    INSERT INTO filepaths
        (BaseDirID, DirLevel, DisplayPath, ReverseDisplayPath, Iteration)
    SELECT DISTINCT
        f.BaseDirID, f.DirLevel - 1, reverse(r.rdp), r.rdp, @iter + 1
    FROM
        filepaths AS f
      CROSS APPLY
        ( SELECT substring(f.ReverseDisplayPath,
                           1 + charindex('\', f.ReverseDisplayPath),
                           1000) AS rdp
        ) AS r
    WHERE
          f.Iteration = @iter
      AND f.DirLevel > 1
      AND NOT EXISTS
          ( SELECT *
            FROM filepaths AS ex
            WHERE ex.ReverseDisplayPath = r.rdp
          ) ;
    SET @new_items = @@ROWCOUNT ;

    SET @iter = @iter + 1 ;
    -- until new_items = 0
    IF (@new_items > 0) GOTO repeat;

After the process is finished, you can get only the missing filepaths with a simple query:

SELECT DisplayPath FROM filepaths
WHERE Iteration > 0
ORDER BY DisplayPath ;

Tested in dbfiddle.uk.

The reverse strings are not strictly needed but they simplify the splitting of the strings. You could go without them (and a bit more complex code). You could also have only the reverse paths stored in the new filepaths table and when the iterations are complete, reverse them again into the target table (the existing one I guess or wherever you want them to go).

The whole thing could also be done with a single pass - using a recursive CTE - but I don't know if that would be more or less efficient.


Postgres: Get nextval in sequence without actually incrementing sequence?

I would assume that SELECT currval('seq_name') + 1 or SELECT last_value + 1 FROM seq_name would be the answer, but that's only if you're incrementing by 1, or you know the increment off-hand.

If you're not incrementing by 1 and you don't know the increment, then try this:

SELECT currval('seq_name') + i.inc
  FROM (SELECT seqincrement AS inc
          FROM pg_sequence
         WHERE seqrelid = 'seq_name'::regclass::oid) AS i;

or

SELECT last_value + i.inc
  FROM seq_name,
      (SELECT seqincrement AS inc
         FROM pg_sequence
        WHERE seqrelid = 'seq_name'::regclass::oid) AS i;


What is going on with my user and max date query?

I've tried to rid of the excessive parentheses and backticks so I've got the next query:

  SELECT
    t1.uid,
    t1.LogDate AS touchdate
  FROM      ref_source_page_log_report AS t1
  LEFT JOIN ref_source_page_log_report AS t2
         ON t1.uid = t2.uid
        AND ( t1.LogDate < t2.LogDate OR
            ( t1.LogDate = t2.LogDate AND t1.id < t2.id ))
                 -- Here was the problem: ^^^^^^^^^^^^^ (uids instead of ids)
  WHERE t2.uid IS NULL
  LIMIT 10000


all_tables and dba_segments giving different space occupied by the same tables

According to Oracle documentation, dba_segments.bytes is the number of bytes used by the database segment. all_tables.blocks is the number of blocks used inside a table (docs). Tables don't grow block by block, they grow extent by extent. There is also a third interesting field, dba_segments.blocks, which shows how many database blocks are there in a segment.

You can run the following query and see that the number of used blocks in table is generally not the same as the number of blocks in corresponding segments:

select s.segment_name, s.blocks segment_blocks, t.blocks table_used_blocks
from dba_segments s
    inner join dba_tables t on s.owner = t.owner and s.segment_name = t.table_name
where t.table_name = 'PARAMETER_STATUS'

Also note that due to partitioning, a single table may consist of more than one segment.


DB2 - what happens when SQL archive log file reches its maximum value (S9999999.LOG)

According to the link that @mustaccio put in the comments:

The database manager resets to S0000000.LOG if:

A database configuration file is changed to enable rollforward
recovery
A database configuration file is changed to disable
rollforward recovery
S9999999.LOG has been used.


How to extract dynamically data from another database in function with DBName as parameter?

It is not possible to do this dynamically via EXEC, as IIRC you can't do that in a function. You can't use an ad-hoc distributed query via OPENROWSET (https://msdn.microsoft.com/en-us/library/ms187569.aspx) either as OPENROWSET explicitly requires its inputs including the query text to be string literals not more general string values (so you can't use an expression or variable).

The only ways I can think of involve either having one function per remote database or the CASE/IF suggestion from Marian's comment. You could pragmatically create such a function by cursoring through a view based in sys.servers and other DMVs to put the function together through string concatenation, then replace the old version with the new using a call to EXEC. You still need to run the generation procedure every time the list of linked servers changes, but at least it would just be one procedure call (or script/statement run) and not a more manual code change.

This all sounds very hacky though. Such a "code smell" would suggest a redesign is in order if you have such power!


Get result from select exists, execute insert query based on the response of it

Create a UNIQUE index or constraint on (col1, col2, col3) - if you don't have one already.

Then use INSERT ... ON CONFLICT ... DO NOTHING. That's simpler, faster and more reliable, and unlike the route you had in mind, it's also safe against race conditions.

Related:

What does a CONSTRAINT have to do with my unique index?
What happens with duplicates when inserting multiple rows?

Be aware of NULL handling:

PostgreSQL multi-column unique constraint and NULL values


What are the pros and cons of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE

What are the benefits? - Faster data access with less disk access.

How will the client side cache be kept in sync?  From the Oracle Call Interface Programmer's Guide:

  The client cache transparently keeps the result set consistent with
  any session state or database changes that can affect its cached
  result sets.

  When a transaction modifies the data or metadata of any of the
  database objects used to construct that cached result, invalidation is
  sent to the OCI client on its subsequent round-trip to the server. If
  the OCI application does no database calls for a period of time, then
  the client cache lag setting forces the next OCIStmtExecute() call to
  make a database call to check for such invalidations.

  The cached result sets relevant to database invalidations are
  immediately invalidated, and no subsequent OCIStmtExecute() calls can
  match such result sets. The OCI statement handles currently fetching
  from these cached result sets, at the time such invalidations are
  received, can continue fetching from this (invalidated) cached result
  set.

  The next OCIStmtExecute() call by the process may cache the new result
  set if there is space available in the cache. The OCI client result
  cache periodically reclaims unused memory.

  If a session has a transaction open, OCI ensures that its queries that
  reference database objects changed in this transaction go to the
  server instead of the client cache.

  This consistency mechanism ensures that the OCI cache is always close
  to committed database changes. If the OCI application has relatively
  frequent calls involving database round-trips due to queries that
  cannot be cached, (such as DMLs, OCILob calls, and so on) then these
  calls transparently keep the client cache up-to-date with database
  changes.

  Note that sometimes when a table is modified, a trigger can cause
  another table to be modified. OCI client result cache is sensitive to
  all such changes.

  When the session state is altered, for example, if NLS session
  parameters are modified, this can cause the query results to be
  different. The OCI result cache is sensitive to such changes and on
  subsequent query executions, returns the correct query result set. The
  current cached result sets are kept (and not invalidated) for any
  other session in the process to match; otherwise, these result sets
  get "Ruled" after a while. There are new result sets cached
  corresponding to the new session state.

  If the application must keep track of all such database and session
  changes it can be cumbersome and prone to errors. Hence, OCI result
  cache transparently keeps the result sets consistent with any database
  or session changes.

  The OCI client result cache does not require thread support in the
  client.

*Do we have an auto mode for RESULT_CACHE_MODE?* - The Oracle Database Reference shows that the two options for this setting are MANUAL and FORCE.

What is the recommended mode for this? - This would depend on what your needs are.

To understand the benefits of the result cache, look at the Validation of the Client Result Cache of the Oracle Call Interface Programmer's Guide referenced earlier.


SELECT id
FROM table1
WHERE EXISTS (SELECT 1
              FROM table2
              WHERE table1.id = table2.id)
  AND /* OR */
      EXISTS (SELECT 1
              FROM table3
              WHERE table1.id = table3.id)

When you need ids which are present both in table1 and table3, use AND.

When you need ids which are present at least in one of these tables, use OR.


Your error message is useful to know that the database you are trying to remove is not in mirroring. It is part of Availablity group.

You can use below command to remove the database from Availablity group :

ALTER AVAILABILITY GROUP YourAG REMOVE DATABASE DBthatYouWantToRemove;


BI-DIRECTIONAL Oracle Golden Gate for Oracle 12c and SQL Server 2014

Install GoldenGate for SQL Server on the server with the SQL Server database.
Install GoldenGate for Oracle on the server with the Oracle database.

Here is an example of setting this up: http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html

It is outdated, but the point is, you can see what goes where in this.


DPM transaction log backups of SQL Server partially contained databases

I eventually figured out that the problem was not with all contained databases.  It was fixed by removing and re-adding the problem databases to the DPM protection group. I have no explanation.

Martin


How do I create a table with a column that uses a sequence?

Assign it as the default property for the column

CREATE TABLE [MyTable]
(
    [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
    [Title] [nvarchar](64) NOT NULL
);

Future readers, a Sequence can have a gap if the service stops unexpectedly, the value of the entities in CACHE can be lost. Here, they are specifying no cache to mitigate that with the tradeoff of slower performance for the sequence object.

CREATE SEQUENCE reference


Why does RDS PostgreSQL always recover on restart?

An answer to this question was provided on the AWS Developer Forums thread to which it was originally posted.
Summary
RDS PostgreSQL (as of the versions available in April 2018) doesn't always recover after restart; however, if the RDS instance does not shutdown within an unspecified timeout, RDS will force terminate the instance, requiring PostgreSQL to recover on reboot.
Full text of reply

Thanks for using RDS!
I saw this thread referenced somewhere else and thought I'd give a quick input. I work closely with the PostgreSQL service team and I can confirm that the behavior you're observing is correct. As of today (April 2018) it's the case that sometimes, if shutdown hasn't completed quickly enough, the RDS automation will time-out and force termination. Various behaviors like this have in fact existed for a long time, so you may have observed it before. In the handful of specific cases that have been discussed on this thread, I can't really comment on why PostgreSQL (or other database engines) took longer than usual to shut down - there are probably many different possible causes for this.
To be clear, the specifics about if and how and when a force-termination occurs might change over time depending on many variables. What will never change is the RDS team's commitment to durability and availability of customers' data. Meaning that (1) our engineers carefully design with safety in mind, avoiding things that might increase risk of corruption in any way and (2) our engineers are always mindful that downtime - whether measured in minutes or seconds or milliseconds - is worth minimizing as far as safely possible.
I hope this helps, to hear that what you're observing is there by design!
-Jeremy


How do I manage two versions of a stored procedure at the same time?

This is typical use case for edition-based redefinition. You initially create an edition and a service that the clients use for connecting, to that speficic edition:

SQL> alter user bp enable editions for procedure;

User altered.

SQL> conn bp/bp
Connected.

SQL> create edition e1;

Edition created.

SQL> alter session set edition=e1;

Session altered.

SQL> create or replace procedure p1 as
  2  begin
  3    dbms_output.put_line('Edition e1.');
  4  end;
  5  /

Procedure created.

SQL> exec dbms_service.create_service('myservice', 'myservice', edition=>'e1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('myservice');

PL/SQL procedure successfully completed.

This is what you get if you execute the procedure:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> set serveroutput on
SQL> exec p1
Edition e1.

PL/SQL procedure successfully completed.

This session remains alive and continues its work. Now you want to deploy a new version of the procedure. In another session:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> create edition e2;

Edition created.

SQL> alter session set edition=e2;

Session altered.

SQL> create or replace procedure p1 as
  2  begin
  3    dbms_output.put_line('Edition e2.');
  4  end;
  5  /

Procedure created.

Then modify the service to use the new edition:

SQL> exec dbms_service.modify_service('myservice', edition=>'e2', modify_edition=>true);

PL/SQL procedure successfully completed.

New sessions connecting through this service will use the new version of the package:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> set serveroutput on
SQL> exec p1
Edition e2.

PL/SQL procedure successfully completed.

While back in the original session:

SQL> exec p1
Edition e1.

PL/SQL procedure successfully completed.

If the old session ends then simply reconnects, using the same service it used for connecting earlier, it will start using the new procedure as well:

SQL> conn bp/bp@localhost:1521/myservice
Connected.
SQL> set serveroutput on
SQL> exec p1
Edition e2.

PL/SQL procedure successfully completed.

More about this: Using Edition-Based Redefinition


Guidance on strategy for serving files remotely using SQL Server

Putting files inside your database tables is almost always a bad idea. Your database server is not a file server.
Store the files on a file server of some sort, and then store the location of the files along with other metadata you might need in the database. Which you're already doing - so keep doing that, you're just going to change where/how you store the files (maybe).
And please, for the love of all you find precious, do not use FTP. It's broken and insecure. Use SFTP, Azure blob storage, or other secure, sustainable, scalable file storage.


With your current structure, your query may be doing >1,000,000,000 row scans. This is probably due to the FIND_IN_SET - meaning the column cannot be used in an index, resulting in the optimizer choosing other indexes with very bad selectivity (you have to read 500000 rows instead of 1 million, that is very bad selectivity). The fact that an index is being used does not mean good performance, if it is the wrong index. To understand why, you must know that function(column) = value cannot be indexed directly in MySQL.

I would start by changing the column items.ordernumber and putting that information on a separate table, with only one item per column, instead of the denormalized arrangement you have currently.

With that, and the proper indexes, you will be able to do the query in the items -> inventory direction and use ordernumber to filter adequately your rows.

As a secondary thing, you have many redundant indexes, like (guideid, orderNumber) and (guideid). You are in need of a good cleanup in terms of design.

To measure the number of row operations that you are doing for a particular query, do the following:

FLUSH STATUS;
-- Execute query
SHOW STATUS like 'Hand%';


Multiple columns from one table rely on one single table

I had to use two JOINs here but I think that this will work. You can join on the same table multiple times

SELECT
  a.t_code,
  a.t_waste_water,
  a.t_drinking_water,
  b.perim_name AS waste_water_perim,
  c.perim_name AS drinking_water_perim

FROM schema.town a
  JOIN schema.perimeter b ON a.t_waste_water = b.perim_code
  JOIN schema.perimeter c ON a.t_drinking_water = c.perim_code;


query output shows ##,###,### instead of right output

If the output looks like ##,###,###, the number is too big for the format.
Use a longer format string like
FM99,999,999,999


FILE privilege still allows for file writing on server (localhost)

Please note the FILE privilege as described in the Documentation

  The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access. This includes the server's data directory containing the files that implement the privilege tables. As a security measure, the server will not overwrite existing files.

The context here refers to the DB User being able to read and write files on the DB Server using

LOAD DATA INFILE
SELECT ... INTO OUTFILE
LOAD_FILE()

These commands have their fopen and fwrite commands issued from the mysqld server daemon, not the client program. Your web application is not restricted in any way from reading and writing ordinary files because you are doing so from the application layer, not the DB layer. If you click on each link to those three(3) commands, the FILE privilege is specifically mentioned.


Is it possible to write out images from SQL Server to a mapped drive?

SQL Server can usually see a UNC path like \\server\share\subfolder

But only if the service running SQL Server has rights to that share. For example, if you're running a scheduled job, the job runs as the user for the SQL Agent Service. If you're running code directly in SSMS or a stored procedure called from your program, then the SQL Engine Service must have rights to that shared folder.

If that user is a local user only, then it won't have rights to the shared folder. SQL Server engine and/or agent must be running as a domain user to access the volume.

For example, we run our backups to a \\server\DBs\SQL\<server name> shared drive. The SQL Agent Service account has to have full rights to that shared folder for the backups to succeed. There's no mapped drive involved in these backups.


xml parameter to distributed query - difference between SQL2005 & SQL2012

It's not supported, but you can get around it..

select * from openquery(
 LinkedServerName,
 'exec [database].dbo.sproc @Id=@Id,
                       @PItems=@PItems output'',''
                       @PId bigint,
                       @PItems xml output'',
                       valueOf@PId,
                       valueOf@PItems output'
)

There are many pitfalls. which are well explained here, as you are on 2012 you will want to look into executing the remote query with results set. some version of this.

SELECT * FROM OPENQUERY(LinkedServerName,
          'EXEC msdb..sp_helpindex sysjobs
           WITH RESULT SETS ((index_name        sysname,
                              index_description nvarchar(500),
                              index_keys        nvarchar(500)))')


Moving MAX and MIN calculation into a sub query to preserve the full result set in the outer query

Since you have aggregate functions in your SELECT clause, you need to specify what groups to apply them to. If you add:

GROUP BY   p.id
 , p.date
 , p.title
 , v.total_views
 , r.total_rating
 , r.module
 , r.module_id

before ORDER BY in your query, you will get 1 row per such group.

Without a GROUP BY clause, MySQL will randomly pick 1 row for you. This is a well-known bug in older MySQL versions.


How and when to use sys_refcursor in oracle

A cursor is a pointer to a result set for a query. By returning a sys_refcursor you allow the client to fetch as many or few of the rows from the query as it requires. In stateful applications this could be used to page through results.

A cursor can allow more flexibility than writing a PL/SQL function that returns an array as it's completely up to the client how many rows to fetch and when to stop. That said, I've not found many cases where this additional flexibility is useful.

It's worth noting that the sys_refcursor is weakly typed, so you can return pointers to queries which not only have different from or where clauses, but different numbers and types of columns as well. Alternatively you can use strongly typed cursor where the columns in the result set are fixed.

This enables you to write functions which return different queries, like so:

create function get_data ( type varchar2 ) return sys_refcursor as
  ret_cur sys_refcursor;
begin

  if type = 'EMP' then
    open ret_cur for select * from emp;
  elsif type = 'DEPT' then
    open ret_cur for select * from dept;
  end if;

  return ret_cur;
end;

However, if you're using sys_refcursor to create a generic "open a query" function like the above you're probably doing something wrong!


SSAS OLAP Semi-Additive Measure over Time Dimension

Solution
The easiest way to accomplish the above is to set the CustomRollupColumn in your date dimension for both your Quarter and Year dimensions to the following equation:
'TAIL(NonEmptyCrossJoin(
    DESCENDANTS([Date].[Date Hierarchy].CurrentMember,1),1)
).item(0)'

Where:

[Date] = Dimension Name
[Date Hierarchy] = Your custom date hierarchy dimension

Important Considerations
You cannot manually set the CustomRollupColumn equation in Visual Studio editor. This field needs to provided by a column in your dimension dataset. While this seems a little strange, it actually provides you with a ton of flexibility.
To do this, create a custom column in your DSV (or include it in your dimension table). Here is an example:

Finally, once you've completed this step navigate to your date dimension and set your CustomRollupColumn parameter equal to this newly created field. Here is an example:

Make sure you do this for both your Quarter and Yearly dimension.
Final notes
I still need to conduct some tests, but if everything works I will come back and mark this as answered. So far, everything is working great. Partially completed quarters will reference the most recent provided month (data point). Same thing with the year dimensions.
Resources that were helpful in figuring this out:

MSDN Forum Post - Get Last Value
How to Determine the First or Last Member with Data
Using the descendants function
Creating custom roll-ups Major help!
Last child aggregation MSDN forum conversation


How do I identify functional dependencies and normalize a table based on the attributes?

A set of attributes Y is functionally dependent from another set X if and only if Y is uniquely determined by X, that is you cannot have two different values of Y associated to the same value of X. So, for instance, assuming that a phone number is tied always to only one person, and that a person has always only one address, you can say that the functional dependency phone -> address holds, since, given a certain phone number, only the address of the person with that number can appear together with it.
So a functional dependency specifies in a formal way an important fact of the reality that is modeled in your database.
A problem with functional dependencies is that you can have a lot of them, even for small relations. A clever way of solving this problem is to define only a small set of dependencies, sufficient to “capture” all the essential information about a certain reality, since we can derive all the other dependencies from this set in a mechanical way (and there are programs that can do this, for instance by applying the well-known “Armstrong’s axioms”). However, sometimes it is not so easy to define such “essential” set of functional dependencies and we must look carefully at the specification of the problem that we are modeling.
Usually we start from the so-called “candidate keys”, that is the attributes or the sets of attributes that are “naturally” unique inside a relation. Since they are unique, we know that they determine all the other attributes. In your example, you have already identified four candidate keys, id (the primary key), (name, address), phone and email (declared unique), so we can already say that:
id -> name, address, email, phone
name, address -> id
phone -> id
email -> id

(note that is enough to say that an attribute determines a candidate key, because from this fact we can derive that it determines all the other attributes, that is that it uniquely determines a person).
Then we can see if some other attribute of combination of attributes uniquely determines either other attribute(s), or a candidate key, (which is equivalent to determines all the other attributes).
There are other (non-trivial, interesting) functional dependencies in your relation? Probably no, given the obvious semantics of the data (for instance two different persons can have the same address, so an address does not “uniquely determines” anything, or two different persons can have the same name, etc.). So we could have a certain degree of confidence that the four FDs are an “essential” set of FDs (technically this is called a “cover” of the set of FDs of the relation).
And so from this cover we can start to normalize the relation (that is already normalized, in this case).


It is possible to ORDER BY using the ordinal value of selected fields (even a calculated value) instead of using the field name. It is hard to tell from the query above, but a generic example:

SELECT field1,field2,function(field1) FROM table ORDER BY 3;


Best index strategies for read-only table?

If the ID column is not used for data retrieval, I suggest you place the clustered index on the PROVINCE_ID, CANDIDATE_NUMBER columns instead.

If some columns are read very often compared to others, consider adding an index with PROVINCE_ID, CANDIDATE_NUMBER plus that column. That way the queries can get everything they need from the index itself and don't need to look at the table at all.


How do you select*from with multiple conditions?

Try it:

SELECT *
FROM Flights
WHERE Departure_IATA_Code = 'STN'
AND Arrival_IATA_Code = 'DBN'
AND Departure_Date = '2018-04-16';


So there are two ways to do this:

If you have keys that guarantee that combinations of (targetId,start_date) are unique, a correlated subquery will return consistent results
If you cannot guarantee uniqueness, you will have to use a partitioning function in order to guarantee the same result is returned each  time the query is executed

If a unique index exists:
SELECT
  a.id
 ,a.userid
 ,a.targetid
 ,b.id AS b_id
 /* And other columns from Table B */
FROM
  table_a a
LEFT JOIN
  table_b b
    ON b.targetid = a.targetid
        AND b.start_date =
          (
            SELECT
              MAX(start_date)
            FROM
              table_b
            WHERE
              targetid = a.targetid
                AND start_date <= a.start_date
          )

No unique index exists:
For this I'm assuming Id on Table B is a row identifier and unique.  If it isn't, you will need to add additional columns to the SORT portion of the PARTITION statement to guarantee the query is deterministic.
SELECT
  id
 ,userid
 ,targetid
 ,b_id
 /* Other columns from table b */
FROM
  (
    SELECT
      a.id
     ,a.userid
     ,a.targetid
     ,b.id AS b_id
     /* Other columns from Table B */
     ,ROW_NUMBER()
        OVER
          (
            PARTITION BY
              a.id
            ORDER BY
              b.start_date DESC
             ,b.id DESC
          ) AS rownum
    FROM
      table_a a
    LEFT JOIN
      table_b b
        ON b.targetid = a.targetid
            AND b.start_date <= a.start_date
  ) x
WHERE
  rownum = 1

Notes:
As I mentioned in my earlier comment, the desired results you provided are inconsistent with the requirement tableb.start_date <= tablea.start_date, specifically for Ids 22, 33, 44.


DB locks can exist on rows, pages or whole tables or indexes.  When a transaction is in progress, the locks held by the transaction take up resources.  Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover resources taken up by large numbers of fine-grained locks.

It will do this automatically, although you can set flags on the tables (see ALTER TABLE in the books on line) to control the policy for lock escalation on that particular table.  In particular, premature or overly eager lock escalation used to be a problem on older versions of Sybase and SQL Server when you had two processes writing separate rows into the same page concurrently.  If you go back far enough (IIRC SQL Server 6.5) SQL Server didn't actually have row locking but could only lock tables or pages.  Where this happened, you could get contention between inserts of records in the same page; often you would put a clustered index on the table so new inserts went to different pages.


Recursive Parent-Child Location Relationship Cache as View

I believe you can achieve the desired result with this query:

;WITH CTE_Locations (LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName)
AS
(
    SELECT LocID,
        LocName AS CampusLocName,
        CONVERT(varchar(250), NULL) AS BuildingLocName,
        CONVERT(varchar(250), NULL) AS AreaLocName,
        CONVERT(varchar(250), NULL) AS FloorLocName,
        CONVERT(varchar(250), NULL) AS RoomLocName
    FROM Locations
    WHERE ParentID IS NULL

    UNION ALL

    SELECT L.LocID,
        CampusLocName,
        IIF(L.LocationTypeID = 2, LocName, BuildingLocName) AS BuildingLocName,
        IIF(L.LocationTypeID = 3, LocName, AreaLocName) AS AreaLocName,
        IIF(L.LocationTypeID = 4, LocName, FloorLocName) AS FloorLocName,
        IIF(L.LocationTypeID = 5, LocName, RoomLocName) AS RoomLocName
    FROM Locations L
        INNER JOIN CTE_Locations CTE ON L.ParentID = CTE.LocID
)
SELECT LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName
FROM CTE_Locations;

According to the WITH common_table_expression doc:

  This clause can also be used in a CREATE VIEW statement as part of its
  defining SELECT statement.


The requirement that a database have a full backup is only enforced by the Wizard. If you just Add the database after the Availability group is already created you can restore the same backup on both the Primary and Secondary and then add the database to the Availability Group. To save time during the maintenance window we usually restore Full backups ahead of time and take a differential backup during the maintenance window.

Take Full backup of the database being moved.
Restore the full backup on the New primary WITH NORECOVERY this ensures the database isn't used until the secondary is ready.
Restore the same full backup on the new secondary WITH NORECOVERY this is needed to add them to the AG.
Restore the primary database WITH RECOVERY. RESTORE DATABASE [MyBigDB] WITH RECOVERY;
On the Primary instance navigate to AlwaysOn High Availability > Availability Groups > MyAG > Right-Click on Availability Databases > Add Database
Select your database
Select Join only
Connect to the secondary instance
All the validation should be skipped
Verify everything and click finish or Save the script.
Verify syncronization is happening by viewing the Availability Group Dashboard


AUDIT_TRAIL

If you want the SQL text to be captured, you need to use the extended option for audit_trail (db, extended or xml, extended).

alter system set AUDIT_TRAIL=db, extended scope=spfile;

or

alter system set AUDIT_TRAIL=xml, extended scope=spfile;

Changing this setting requires a database restart to take effect.

For FGA you can specify a different audit trail, for example: audit_trail => DBMS_FGA.DB_EXTENDED, that way you can leave audit_trail on DB. If you use FGA, you do not even need to use AUDIT, just create the policy you need.


Minimize locking on UPDATE with multiple WHERE columns

You will need to lock all the rows yourself before each UPDATE.

See the MySQL Documentation on SELECT ... LOCK FOR UPDATE. This performs an exclusive lock on all the rows you pass through. Then, you can follow up with the needed UPDATE against the table.

In your particular case, you would do this:

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100

INDEXING

You should index the table fully to support all possible ways you will be querying the data. Notwithstanding, you must alternate between SELECT ... FOR UPDATE and UPDATE.
Since you have both row1 and row2 in the WHERE clause, you should have an index with both columns in it.
There is one warning: If these columns are indexed, expect some slowness because the column is being updated and the BTREE index pages are being updated per row. You should also expect rapid growth of the insert buffer section of ibdata1 (See InnoDB Map)

I have many posts on the subject of SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARED MODE.

Case Study I went through with @RedBlueThing

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
Oct 18, 2011 : Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
Jan 02, 2012 : LOCK IN SHARE MODE
Nov 19, 2012 : How long can Deadlock info hold in innodb status page?
Dec 13, 2012 : MySQL InnoDB locks primary key on delete even in READ COMMITTED
Feb 03, 2013 : MySQL InnoDB locking on combined UPDATE-JOIN statements
Mar 12, 2013 : How I prevent deadlock occurrence in my application?

UPDATE 2013-03-17 19:21 EDT

Since you have 9 WebServers hitting the DB Server, try this

On WebServer1 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 0,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 0,100;

On WebServer2 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100,100;

On WebServer3 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 200,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 200,100;

All the way to WebServer9, run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 800,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 800,100;

You will have to place some PHP header file that unique identifies which machine runs which version of the query.


Extended Events with Histogram target, event counter target or a bucketizer target. You can start from the How to: Find the Objects That Have the Most Locks Taken on Them example on MSDN, which offers almost exactly what you want. You can modify the example to get the lock types (the example 'as is' filters out S and U locks) and you can refine it to filter per session etc etc.

That being said, I must point out that approaching a performance investigation from counting the locks acquired is highly unusual. The focus is usually be on wait stats, as described in the Waits and Queues methodology. See for exampleCapturing wait stats for a single operation for how to use, again, Extended Events to capture the relevant info for a specific query.

On the other hand if you are concerned about locks from the point of view of concurrency  (will the new CTEs create more contention due to locking?) then I would recommend investigating deploying snapshot isolation.


QUESTION 1 : How do I check the number of allowed connections?

ANSWER

You can set the number of connections in my.ini. For example, to set it to 500 do this:

[mysqld]
max_connections = 500

However, keep in mind the mysqld wil dictate the variable open_files_limit

Run this query : SHOW VARIABLES LIKE 'open_files_limit';

You can raise this, BUT AT YOUR OWN RISK !!! Also, keep in mind that the number of max_connections will multiply the sum of per-connection parameters (sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size)

QUESTION 2 : How do I turn on query logging to see performance of queries executed?

ANSWER

To turn on the slow log and set the minimum slow query time to 2 seconds (default 10)

[mysqld]
slow-query-log
slow-query-log-file = slow-queries.log
long-query_time=2

This should place the slow-queries.log file in the same location as datadir. You can see where datadir is set by running SHOW VARIABLES LIKE 'datadir';

QUESTION 3 : As I understand MySQL is multithreaded, will increasing the number of allowed connections increase performance on a Quad Core system?

ANSWER

This answer depends on the storage engine of all your tables.

If you are using MyISAM, there is nothing to configure because the MyISAM storage engine does not utilize multiple CPUs.

If you are using InnoDB

MySQL 5.5 can be configured to utilize multiple CPUs
MySQL 5.1 InnoDb Plugin (MySQL 5.1.38+) can be configured to utilize multiple CPUs
MySQL 5.1.37 and prior does not utilize multiple CPUs

In order to configure InnoDB for multicore engagement, please read my past posts on this subject:

About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Multi cores and MySQL Performance
MySQL Config for Large MyISAM tables
MariaDB XtraDB Performance Tuning
Insert-heavy InnoDB table won't use all my CPU

QUESTION 4 : Will having a cluster setup increase performance?

QUESTION 5 : How can I setup load shedding with multiple MySQL servers in a cluster?

ANSWERS

It can, but you must do two things

Configure a Replication Topology to have one Write Master and Multiple Read Slaves
Do the leg work of coding your app to access the Cluster as follows

Do all INSERTs, UPDATEs, DELETEs on the Write Master
Do all SELECTs on Read Slaves using some Network-Based Load Balancing


What could be the cause for my high disk IO latency (from sys.dm_io_virtual_file_stats DMV)?

My problem with this DMV is that it misses the critical context of time, so it's difficult to pinpoint when the spikes in latency occur.   If you're running a VM rather than an RDS instance, I'd recommend setting up a Perfmon Trace to collect disk latency metrics so you can narrow down the times.  If that's not an option, push the results of your query to a user table every 5 minutes or so and then trend out the deltas so you can see what periods of time experience spikes.

Once you figure out when spikes are occurring, you'll have an easier time identifying the cause of the latency via Extended Events (e.g. capturing long-running queries), Job History, etc.  Who knows, maybe you've run into a situation where you've got nothing locally directly affecting the I/O during the spikes at which point you need to escalate to Amazon with this information in hand.  Noisy neighbor scenarios happen, so don't rule anything out.

On a side note, if you want to further break down the DMV stats to their database files, check out Paul Randal's post on this DMV explaining how to do that.


Mysql How to insert a variable as field value in a table

You should insert hash as a parameter following this example:
http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

Something like this

insert_stmt=(
       "insert into meanu (username,pwdhash1)"
       " values('BV2',%s)"
        )
data=(hash)
cursor.execute(insert_stmt, data)


Your usernames are to be unique. Good, they should be. But now you seem to think that Username would make a good primary key. Not necessarily. One important characteristic of a PK is stability. Unless you wish to implement a "one you've chosen a username you're stuck with it forever" policy (and that is a decidedly user-unfriendly policy), someone may want to change their username. Doing so when that username is scattered all over the database is going to be difficult.

I am not a fan of design rules that demand a surrogate key for every table. But typically 50% or more of tables should have surrogate keys. This is one of them.

And I certainly hope that when you say the login table will contain the password, you do mean the hash of the password, don't you?

@ypercube speaks for all of us about the "for each question, there will be separate table" idea. Not good.

Now as for the user's code. For that you have choices. You don't mention the target DBMS (if you even have one at this point) but some handle text or clob fields better than others. The question of handling actual disk files is one you will have to research yourself. Will those files reside in a file server, within a version control system, just a dedicated folder on some disk somewhere? In any case, what are the OS-based and/or network access privileges that will be needed? You get the idea. There are a lot of questions unrelated to databases that must be answered.

Not to say that one is superior to the other, but unless you or someone on your team has done this before, you may want to start with storing the code in the database. That will probably get you started faster and you can move to system files (if you determine it to be the better option) more or less at your leisure.


Backup without password

If you run the script as OS user postgres it should not request a password in a standard setup, because password-less peer access (or ident on older versions) is enabled in pg_hba.conf for the postgres user.

So make it a cronjob of postgres if you can. Or enable password-less access for the OS user running the job. You can do that in pg_hba.conf but, as @Richard already pointed out, the password file .pgpass file may be a more elegant solution.

More on how to connect without password in this related question.

Restore in pgAdmin

I quote the pgAdmin3 FAQ:

  pgAdmin III uses PostgreSQL's pg_restore tool, which supports only the
  COMRESS and TAR options of pg_dump which is used for backup creation.
  The PLAIN format can't be interpreted by pgAdmin III and pg_restore
  (it can be edited manually, and executed with psql and pgAdmin III's
  query tool in many cases), and thus isn't accepted as valid file.

  We recommend using the COMPRESS format for daily backup tasks. The
  PLAIN format is for advanced manual processing before executing as SQL
  script, and has some restrictions (no blobs) which makes it less
  usable for standard backup tasks.

To restore a plain SQL backup execute it with psql similar to this:

psql mydb -p 5432 -U myuser -f backup.sql

For a single database backup you have to connect to the right database. For a cluster-backup with pg_dumpall, you might as well connect to the maintenance db "postgres".

You could also load and run such a backup with the pgAdmin query tool. It's plain SQL.


You are better off dropping and recreating. Why ?
ANALYSIS
Let's take the hypothetical example from your question
CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB );

This is literally a pure Cartesian Product.
Let's say tableA has 2,000 rows and tableB has 5,000. A Cartesian product would result in result_table having1,000,000 rows.
Now let's say you added the column col_1 to tableA.
If you had to fill in tableA into result_table, what would the join look like ?
First, you would have to graft col_1 into result_table.
ALTER TABLE result_table ADD COLUMN col_1;

That's a pass through 1,000,000 (1 million) rows to inject a column.
Then, you have to perform a join of the ugliest kind
UPDATE result_table JOIN tableA
ON result_table.column_key_from_table JOIN tableA.column_key_from_table;

Remember I said joining 2,000 rows to 5,000 rows made result_table 1,000,000 rows ?
To perform the UPDATE JOIN, you would have to create a temp table with 2,000,000,000 rows. Yes, 2 billion rows (1,000,000 times 2,000). The temp table has to be created. Then, you have to perform pass through the 2 billion rows to populate the col_1 column.
CONCLUSION
I think you want to be finished working with the table so you can have time to raise a family, collect your pension, etc.. So, I'd go with the dropping and recreating the table.


Ola Hallengren Index maintenance on servers with Availability Groups

Yes, the script is intelligent enough.

This is explicitly stated in the FAQ:

  How should I configure IndexOptimize for an Availability Group?

  Indexes can only be rebuilt or reorganized and statistics only updated
  on the primary replica in an availability group. IndexOptimize has a
  check for this.

  The jobs should be configured identically and be enabled and scheduled
  on all the replicas.


MySQL InnoDB Cluster - Add instance issue with local port

Your IP ranges most probably don't fall in the range of IPs that would be automatically added to the allowlist: https://dev.mysql.com/doc/refman/8.0/en/group-replication-ip-address-permissions.html
For that reason, you must manually configure the allowlist: https://dev.mysql.com/doc/mysql-shell/8.0/en/configuring-innodb-cluster.html#create-allowlist-servers


What Geopartioning or distributed SQL database can I use?

Since this is medical data, I am going to make the assumption that you can afford a bit of a budget on this project?

I would suggest that you could do worse than taking a look at CockroachDB - from here:

  With CockroachDB’s distributed SQL engine, your business can grow
  without ever manually sharding. Keep your services running with a
  database that automatically scales, rebalances, and repairs itself.

Their SQL engine is compatible with the PostgreSQL client, so anything that connects to PostgreSQL, can connect.

and with respect to Geo-distribution, you have from here:

  The easiest way to maintain compliance with global data regulations
  Keep customer data where it needs to be. With CockroachDB’s
  policy-based data controls like geo-partitioning, serving customers in
  ever-changing geopolitical environments has never been easier or more
  affordable.

and also this:

  Control Data Residency at the Row-Level Implement table and row-level
  policies that dictate where a user’s data resides using CockroachDB’s
  geo-partitioning.

They also have a managed offering if that would suit. I've used it for a Masters project and found it to be excellent - their documentation is superb (lovely graphic design!) and their support even for a lowly student on a free tier was great!

I also found that they were open about their product's limitations, but appear to be working well to overcome these. I've used it on AWS, GCP and Azure with no problems.

Also, when I said "budget" above, I meant over and beyond what you'd be paying for a cloud provider anyway. With CRDB, there is a free option, but I would suggest that with sensitive medical data, you should pay a bit more for the extra features of the pay version.

One of the guys who founded the company is Spencer Kimball who, as a student, began writing the Gimp (Gnu Image Manipulation Programme), Open Source's answer to Photoshop! He then worked on the Spanner/F1 system in Google!

If this sounds like a commercial, it's not! I'm just a happy user who was blown away with what you got on the free (and Apache licenced Open Source) tier!

If you already have a system that works off MySQL, you might want to look at TiDB! It's similar in scope to CockroachDB (not suprising as they're both based on Google's Spanner/F1 architecture). That is, if you can live with its limitations which are, IMHO, a bit severe for production still - but, hey, have a look - I've also used it on AWS, GCP and Azure also


Getting error message when opening report server home page ( found 163 but 162 needed)

Talked through with the Asker in chat but in essence:

Database Engine (SP3) and Reporting Services (RTM) are now installed on the same server but experiencing incompatibility since the "naïve" Reporting Services (installed from a RTM media - overwriting the now-uninstalled updated version) is seeing a more recent (updated) Database Engine ReportServerDB version than it can handle. Database Engine has been upgraded since the original install (via Windows Update or whatever applies to that environment) to SP3 but RS has now been reset to "naïve" RTM version.

Update the installed Reporting Services with SP3 and any CUs to match the installed Database Engine version, restart and re-try. There may be some downtime for users depending on whether it requires a restart so advise/schedule that accordingly.


SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days

I've seen this behaviour on a client site running SQL2012 SP1.
The specifics here were NUMA, and PLE demonstrating a 'sawtooth' pattern but on an hourly cycle.

A couple of threads on SQLServerCentral discussed around this:

http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx
http://www.sqlservercentral.com/Forums/Topic1424826-2799-1.aspx

the end result being that applying SP1 CU4 seemed to fix the problem.

CU4 contains the innocent looking fix An update is available for SQL Server 2012 Memory Management KB2845380

Worth a try?


BEFORE INSERT validation trigger for GPS coordinates

Load the new data into a temporary table.
Batch compute distances (one UPDATE the temp table)
One INSERT..SELECT to copy the rows that are within the desired distance.

If you need to write an expression for distance, suggest using Pathegorus, with an adjustment to the longitude of COS(latitude).

Batching things is often a faster way to do bulk operations.


Query to Find Out How Many Days Until Database Is Full

You will need to start building a history of the dbsize vs current_usage metrics by day (or more incremental if needed).  Only then will you be able to make educated calculations for projected dates.

Otherwise you'll essentially just be setting a "hard-coded" linear curve that won't really be precise enough.

Basically, have a stored procedure that takes a snapshot of the current dbsize, current_usage and inserts it into a historical table.  Once you have enough data, you'll be able to use that data to make your projections.


How do I resolve "ORA-23293: Cannot rename a column which is part of a join index?"

There is no such index/object type as JOIN INDEX in the USER_INDEXES/USER_OBJECTS views. A join index is a BITMAP index, search for that. Also you can query what columns are indexed from USER_IND_COLUMNS view. Thinking backwards, you can filter to one specific column and check whether it is indexed or not (this will also reveal the index name):

select * from user_ind_columns where column_name = 'ID' and table_name = 'TABLENAME';

When an index name starts as SYS_.... and has a seemingly random identifier on the end, then that index was generated automatically, which is quite unlikely for a bitmap join index :)

You can confirm the index being a bitmap join index e.g with the use of dbms_metadata.get_ddl, which will show you the actual join condition used:

select dbms_metadata.get_ddl('INDEX', 'INDEX_NAME', 'INDEX_OWNER') from dual;


Oracle: using "as of" clause with table aliases?

The table alias follows the "as of" clause.

select x from a as of timestamp sysdate foo;


Very slow simple PostgreSQL query on RDS

Matching index
After re-reading your question I realized you are not running Amazon Redshift, but Amazon RDS, which seems to be running unsullied Postgres, at least according to the documentation:

Amazon RDS supports DB instances running several versions of
PostgreSQL. Currently we support PostgreSQL versions 9.3.1, 9.3.2, and 9.3.3.

This would mean you have index-only scans at your disposal. If you meet some preconditions (basically if vacuum can keep up with write operations) and if some_field is not updated to often and reasonably small (which seems to be the case for a numeric column), the perfect index would include some_field in last position (like @zerkms first mentioned):
CREATE INDEX ON pages(some_id, first_action_at, some_field);

Note that some_id should come before first_action_at, because it is typically more efficient to have columns with equality checks first and ranges later. Details:
Multicolumn index and performance
If you don't see "index-only scan" in EXPLAIN ANALYZE, the last column is just ballast and better left away:
CREATE INDEX ON pages(some_id, first_action_at);

(Like you have now, according to your question update.)
Either way, another index on just (some_id) only offers very little over this multicolumn index:
Is a composite index also good for queries on the first field?
Server configuration
All the usual advice for slow queries and proper server configuration applies and a work_mem setting of 1 MB is much too low for a DB with millions of rows. But this particular setting should not be crucial for this particular query, since work_mem is (per documentation):

memory to be used by internal sort operations and hash tables.

Neither applies here.


Issue with running multiple mysql commands in a bash for loop

Is something like this anything like what you want?

This is how to run through all the databases in a server and backs them up.

#!/bin/bash

USER="your_user"
PASSWORD="your_password"
OUTPUT="/Users/rabino/DBs"

rm "$OUTPUT/*gz" > /dev/null 2>&1

databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

And in order to cycle through files of a particular type, you could look here.

As an example from this site, you could make use of this script

  Filename Expansion You can do filename expansion in a loop such
  as work on all pdf files in current directory:

#!/bin/bash
for f in *.pdf
do
  echo "Removing password for pdf file - $f"
done

So, you want something like

#!/bin/bash
for f in *.sql
do
  echo "\nworking on filename"
  mysql -u root -ppassword < $f
  echo
done

note: gap between -u and root, no gap between -p and password

Now, the script above isn't very sophisticated (and untested), but that combined with the database script above should give you stuff to work with. In any case, you can Google the terms here and similar and you'll get loads of stuff. A few more bash backup scripts (Rolando is a big hitter on this forum).

Here's a post about a lot of MySQL backup solutions that don't require bash.


How to find out the content differences between 2 SQL tables and produce sync SQL

Other than tablediff and powershell mentioned in the previous answers, you can also use SQL with the UNION ALL statement to find the records that don’t match in 2 identical tables:

SELECT MIN(TableName) AS TableName
   ,ID
   ,NAME
   ,lastname
   ,Address
   ,City
FROM (
SELECT 'Table A' AS TableName
    ,Customers.id
    ,Customers.NAME
    ,Customers.lastname
    ,Customers.Address
    ,Customers.City
FROM Customers

UNION ALL

SELECT 'Table B' AS TableName
    ,CustomersOld.id
    ,CustomersOld.NAME
    ,CustomersOld.lastname
    ,CustomersOld.Address
    ,CustomersOld.City
FROM CustomersOld
) tmp
GROUP BY ID
   ,NAME
   ,lastname
   ,Address
   ,City
HAVING COUNT(*) = 1
ORDER BY id;

Another option you can try is using Data Compare in Visual Studio itself. It compares data in the source database and the target database and creates a synchronization script for the tables you’ve selected for synchronization.

And last, but not least, you can use SQL data comparison tool - ApexSQL Data Diff, to set all synchronization options, map the tables and columns with different names, create your own keys for comparison in the GUI.
You can schedule it to run unattended and all you have to do is check SQL Server job history in the morning.
If you need more details regarding these options, I recommend reading this article: http://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/


Your dynamic SQL should end up looking like this.

   ;WITH CTE AS
         (SELECT * FROM OPENQUERY(XXX.XXX.X.X,
        'Select
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempID = jb.arempID
               WHERE xr.hiredate BETWEEN BETWEEN (''01/01/2017'')
               AND (''01/31/2017'') GROUP BY cm.arcustname'
            )--End OPENQUERY
        ) --END CTE
            INSERT INTO Data(EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE;


Firebird 3.0 service won't start on Windows 7

This could be that your system just has too much going on at initial boot or maybe the 3.0.2 service shares some resources with the 2.5 service and is running into a timeout that clears itself by the time you are able to manually start the service.  Honestly, I don't know enough about Firebird Database to know for sure, but one thing you can try is delaying the automatic start of the 3.0.2 service and see if that gets the process going automatically.
To make this change, open up services.msc, find  your Firebird 3.0.2 service and switch the startup type to Automatic (Delayed Start) and see if that gets this service working automatically.  Here's an example screenshot, of what I'm talking about (but with SQL Server instead of Firebird).

The functionality of the delayed start is best described by CoreTech's post from the SuperUser forum:

A service marked as Automatic (Delayed Start) will start shortly after all other services designated as Automatic have been started. In my experience, this means that they are started 1-2 minutes after the computer boots.
The setting is most useful in lessening the "mad rush" for resources when a machine boots.
Note that when you have 20 services all being started at the same time, each will start up slower as it competes with the others for slices of the machine's precious resources (CPU/RAM/Disk/Network). That is, each service takes longer to become available!
If you have a few services that are critical, then you may want to set those few to Automatic and set as many of the others as you can to Automatic (Delayed Start). This will ensure that the critical services get the most resources early and become available sooner, while the non-critical services start a bit later (which by definition is ok).

No guarantees this works for you, but my fingers are crossed it does.


Question on compression for very large tables in sql server

It is impossible for us to answer whether you want to compress your indexes or not. Only you have access to your data, server and queries. Considerations includes:
Page compression is pretty expensive to crack ("decompress"). Pages lives in memory in compressed form, meaning that every time a page is accessed (including in memory), it has to be cracked. You can probably imagine that pages in an index can be navigated (cracked) very frequently.
Continuing on above reasoning. If the data tend to live in cache (even in uncompressed form), then you will notice more of the CPU overhead. But if the data tend to live on disk, then the CPU overhead might drown compared to the potential I/O savings.
People tend to discard row compression as an option. In some cases it can give a pretty decent compression ratio, and it has close to none overhead.


How to find out which records are causing an exception in a select statement?

Try this query:

SELECT tc.pushedClientReference as ClientReference, tc.pushedStatusId as [Status],
tc.createdOn
FROM tablebackups.dbo.tmp_colagbgdata tc
where TRY_CAST(tc.createdOn as datetime) is null
and tc.createdOn is not null

The TRY_CAST function returns null when a CAST operation is impossible, so the above query will return the rows where converting the createdOn column to datetime is causing an error.

If you have a particular preference there's also TRY_CONVERT.

Now that we know the SELECT query is feeding an INSERT, I'd suggest that tc.createdon in the SELECT list be explicitly converted using TRY_CAST or TRY_CONVERT.


How do I round time to the upper multiple of an arbitrary time interval?

Assuming data type timestamp. Some details are different for date or timestamptz.
A general solution for any time interval can be based on the epoch value and integer division to truncate. Covers all your examples.
The special difficulty of your task: you want the ceiling, not the floor (which is much more common). Exercise care with lower and upper bounds to avoid corner case bugs: you don't want to increment exact floor values. (Or so I assume.)
For common time intervals built into date_trunc() (like 1 hour and 1 day in your examples) you can use a shortcut.
The definition of days depends on the time zone setting of the session with timestamptz (but not with timestamp).
A natural alternative is with ceil(). A bit slower in my tests, but cleaner.
Short demo

-- short demo
WITH t(ts) AS (SELECT timestamp '2018-05-17 22:45:30')  -- your input timestamp
SELECT t2.*
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1 -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min' , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min', to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'  , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'   , date_trunc('day' , ts1) + interval '1 day')
   ) t2(interval, ceil_ts);

interval        | ceil_ts
:-------------- | :------------------
input timestamp | 2018-05-17 22:45:30
5 min           | 2018-05-17 22:50:00
10 min          | 2018-05-17 22:50:00
hour            | 2018-05-17 23:00:00
day             | 2018-05-18 00:00:00

The "trick" for the '5 min' calculation is to subtract the minimum time interval of 1 µs before truncating, and then add the respective time interval to effectively get the ceiling. EXTRACT() returns the number of seconds in the timestamp, a double precision number with fractional digits down to microseconds. We need trunc() because the plain cast to integer would round, while we need to truncate.
This way we avoid incrementing timestamps that fall on the upper bound exactly. It is slightly dirty, though, because the minimum time interval is an implementation detail of current Postgres versions. Very unlikely to change though. Related:

Ignoring time zones altogether in Rails and PostgreSQL

The '10 min' calculation is simpler with ceil(), we don't need to shift bounds by subtracting 1 µs. Cleaner. But ceil() is slightly more expensive in my tests.
Extended test case

WITH t(id, ts) AS (
   VALUES
     (1, timestamp '2018-05-17 22:45:30')  -- your input timestamps here
   , (2, timestamp '2018-05-20 00:00:00')
   , (3, timestamp '2018-05-20 00:00:00.000001')
   )
SELECT *
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1  -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min'  , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min' , to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'   , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'    , date_trunc('day' , ts1) + interval '1 day')
    , ('alt_day', ts1::date + 1)
   ) t2(interval, ceil_ts)
ORDER  BY id;

id | ts                         | ts1                        | interval        | ceil_ts
-: | :------------------------- | :------------------------- | :-------------- | :-------------------------
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | input timestamp | 2018-05-17 22:45:30
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | 5 min           | 2018-05-17 22:50:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | 10 min          | 2018-05-17 22:50:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | hour            | 2018-05-17 23:00:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | day             | 2018-05-18 00:00:00
 1 | 2018-05-17 22:45:30        | 2018-05-17 22:45:29.999999 | alt_day         | 2018-05-18 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | input timestamp | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | 5 min           | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | 10 min          | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | hour            | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | day             | 2018-05-20 00:00:00
 2 | 2018-05-20 00:00:00        | 2018-05-19 23:59:59.999999 | alt_day         | 2018-05-20 00:00:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | input timestamp | 2018-05-20 00:00:00.000001
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | 5 min           | 2018-05-20 00:05:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | 10 min          | 2018-05-20 00:10:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | hour            | 2018-05-20 01:00:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | day             | 2018-05-21 00:00:00
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00        | alt_day         | 2018-05-21 00:00:00

db<>fiddle here
I added an alternative shortcut for full days: ts1::date + 1. The cast to date truncates to the full day and we can add integer 1 to add a day.
Function wrapper
You later disclosed you work with timestamptz, so we can drop AT TIME ZONE from the expression.
In my tests declaring the function STABLE yielded best performance because it allowed function inlining. I would have expected IMMUTABLE to be best, but that declaration is more picky about what's allowed inside to be inlined. Related:

Function Performance

A bit faster in my tests:
CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling2(_tstz timestamptz, _int_seconds int)
  RETURNS timestamptz AS
$func$
SELECT to_timestamp(trunc(extract(epoch FROM ($1 - interval '1 microsecond')))::int / $2 * $2 + $2)
$func$  LANGUAGE sql STABLE;

Cleaner IMO:
CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _int_seconds int)
  RETURNS timestamptz AS
$func$
SELECT to_timestamp(ceil(extract(epoch FROM $1) / $2) * $2)
$func$  LANGUAGE sql STABLE;

Call:
SELECT f_tstz_interval_ceiling1(my_tstz, 600);  -- 600 = seconds in 10 min

For convenience, you could overload each function with an alternative taking an interval as $2:
CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _interval interval)
  RETURNS timestamptz LANGUAGE sql STABLE AS
'SELECT f_tstz_interval_ceiling1($1, extract(epoch FROM $2)::int)';

Just invoking the first version with extracted seconds. Then you can also call:
SELECT f_tstz_interval_ceiling1(my_tstz, interval '10 min');


How to write a query for exact pairs of groups

This is a question of Relational Division Without Remainder. Joe Celko has a very good article on this.
There are a number of ways to slice this cake. Here are a couple:
1. Get a windowed count of each employee's projects (you can also do this as a scalar subquery).
Left-join employees with each other, matching on Pno.
Group up by each pair of employees, exclude any which either have too many or too few Projects than the other.
Then do a final grouping of each employee, counting up the matching employees.
WITH WORKS_ONwithCount AS (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY Essn) AS CountPno
    FROM WORKS_ON
),
EmployeesCrossJoin AS (
    SELECT this.Essn,
        other.Essn
    FROM cte this
    LEFT JOIN cte other ON other.Essn <> this.Essn AND other.Pno = this.Pno
    GROUP BY this.Essn, other.Essn, other.CountPno
    HAVING COUNT(*) = COUNT(other.Pno) AND COUNT(*) = other.CountPno
)
SELECT emp.Essn, COUNT(*) AS CountMatching
FROM EmployeesCrossJoin emp
GROUP BY emp.Essn;

2. Group up each employee, with their project numbers in an array.
Then take the count of other employees who: after un-nesting and full-joining both arrays, there are no NULL values on either side.
WITH cte AS (
    SELECT
        Essn,
        array_agg(Pno ORDER BY Pno) AS AerPno
    FROM WORKS_ON
    GROUP BY Essn
)
SELECT Essn,
    (SELECT COUNT(*)
    FROM cte other
    WHERE this.Essn <> other.Essn
        AND NOT EXISTS (SELECT 1
        FROM unnest(this.AerPno) AS nThis(value)
        FULL JOIN unnest(other.AerPno) AS nOther(value) ON nOther.value = nThis.value
        WHERE nOther.value IS NULL OR nThis.value IS NULL
    ) AS
FROM cte this;


Full text search through many tables in MySQL

Sure, it's fine to copy the searchable content to your full_text_search table.

MySQL supports FULLTEXT indexes only in the MyISAM storage engine (until MySQL 5.6, but fulltext in InnoDB in MySQL 5.6 still seems a little unstable).  So you can store your canonical data in InnoDB for safety, and a copy in MyISAM for indexing.  MyISAM is susceptible to data corruption, but if it's only a copy then you just need to repopulate the MyISAM table if it ever gets corrupted.

Your use of master_id as distinct from the primary key of each table is a little strange.  Why not use the primary key, and add another column to your full_text_search table for the type of content?

CREATE TABLE full_text_search
(
    id INT NOT NULL,
    content_type ENUM('news','polls','comments','galleries','articles') NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( id, content_type )
);

full_text_search table:
+----+--------------+---------------+
| id | content_type | content_text  |
+----+--------------+---------------+
|  1 |         news |     ...       |
|  1 |     articles |     ...       |
|  2 |     articles |     ...       |
+----+--------------+---------------+

Another option is to create a fulltext search index in another specialized technology such as Sphinx Search or Apache Solr.  But the same pattern would be useful -- store the primary key field and a field for the type of content.


What you have described is modeled as follows:

What you are attempting to do is a design flaw (logic bomb); it's called a Fan-Trap in relational database design.

To get around the logic flaw that you are bumping into, you must collect (resolve) the relation from all three tables, Tasks, Roles and Tools into a single associative entity as follows:

Each row of Access collects the relationships between Tools, Tasks and roles.

As far as complexities of constraints is concerned, I think you are confusing the implementation method with design. Good design will clarify the intent, help the developers and maintain performance. The constraints are declarative (and in the SQL standard) to keep things simple while offering verification, validation and enforcement of the design. When you get to the point where you are considering implementing your constraints in code; take a moment to reflect (can an alternate design address this issue in a more elegant way? That's the fun part of design anyway).

Sometimes it cannot (Business Rules come to mind), and the use of triggers/functions is supportable; but your job just got a LOT more complex and bug prone.


Interested in counting number of threshold crossings

See if this meets your specs:

SELECT SUM(turned_on), SUM(turned_off)
    FROM (
        SELECT
                ( @prev <=  5 AND EngineOil_P >  5 ) turned_on,
                ( @prev >= 15 AND EngineOil_P < 15 ) turned_off,
                @prev := EngineOil_P
            FROM ( SELECT @prev := 0 ) init
            JOIN tbl
            ORDER BY timestamp
         ) x;

Note that boolean expressions evaluate to 0 (false) or 1 (true).  Hence the SUM() gets what you want.

There could be a problem with the initial state -- if the first row has > 15, it will be treated as a 'turn on'.


storing query results in a variable in mysql and using it in another query

The post you link to only discusses capturing a single scalar value into the variable, not an entire result set.

You are trying to capture more than one column and presumably more than one row.  MySQL variables can only hold one scalar value.

It seems like what you are looking for is a temporary table, but on the other hand, your explanation is difficult to understand... why would you group by state when the source data is grouped by state?  And what do you mean by the union of the above "two" qieries?  The result of the last query should be identical to the one before it, if such  construct were possible.


Error Authenticating Proxy Account when Executing SSIS job

Just in case others have gotten here for the same reason.  Make sure that the account your proxy/credential is using is a user in its default database in SQL Security.

We went through all the other suggestions (checking that the password hasn't changed, switching to a local service acct for SQL Agent User, rebuilding the credentials and proxy, etc.) and until we made sure the account was a user in its default DB, for some reason, that was causing it to pass a Null SID when it was trying to authenticate to AD.


Should I use WHERE NOT EXISTS or LEFT JOIN for getting the ids of relationship table?

The subquery should be correlated:

SELECT uid
FROM users
WHERE flname LIKE '%john%'
  AND NOT EXISTS
      ( SELECT *              --- doesn't matter what you put here for EXISTS subqueries
        FROM relationship
        WHERE `from` = 60
          AND `to` = users.uid        --- this line added
     ) ;

(unrelated to the issue)

Whoever told you that it's a good idea to use reserved words like to and from as column or table names, deserves a kick.

You can have the same results with a LEFT JOIN / IS NULL or a NOT IN query:

SELECT uid
FROM users
WHERE flname LIKE '%john%'
  AND uid NOT IN
      ( SELECT `to`
        FROM relationship
        WHERE `from` = 60
     ) ;


Case Sensitive Ordering within the CMS Server List

I believe this is a bug and I could reproduce it.
I entered a connect item for it: https://connect.microsoft.com/SQLServer/feedback/details/3136521


Select the newest data (inner join + group by maybe)

select idTopic, author, deleted, idText, datePosted
from
(
    select t.idTopic, t.author, t.deleted, x.idText, x.datePosted,
           row_number() over (partition by t.idTopic order by x.datePosted desc) rn
    from topics t
    left join texts x on t.idTopic = x.idTopic -- or INNER JOIN
) z
where rn=1

Check out the ROW_NUMBER() function which is used to give a unique sequential number to each row in each "partition".  In this case partition by t.idTopic tells the function to independently number each set of records of the same t.idTopic.
Within each partition, the records are numbered 1,2,3... based on a specific order, which in this case is order by x.datePosted desc - this puts the newest posts first.
LEFT JOIN also known in full as "LEFT OUTER JOIN" is used to keep all records of the table on the left side even if there are no records from the right. If you don't need to see a topic if there are no texts for it, simply drop the "LEFT" keyword.
The query is moved into a derived table and aliased (unimportantly as z) so that we can filter on it for where rn = 1.  Recall that each partition can only have one row of value rn=1, which is by definition of our ORDER BY clause in ROW_NUMBER(), the row with the latest text.


SQL Server : Included columns in an Index: is there something like "too many"?

Suggested indexes always tend to include as many columns as needed by queries accessing the data, in order to have covering indexes and eliminate the need for lookups.

Whether this is a good thing or not, only you can tell. It highly depends on the shape of your workload. Some queries will highly benefit from covering indexes, some others will barely improve.

Having columns included in your nonclustered indexes will also mean that whenever you change the data in those columns, the changes will have to be written to the index as well. Broad indexes have a cost.

It is also possible that some of those columns are better placed inside the index key rather than included. Some queries might benefit from ordered data in the index.

The only way to know for sure is benchmarking:

Capture a production workload and sync it with a backup
Restore the backup to your test server
Run the workload against the test server and analyze performance indicators and execution times
Apply the changes you identified as sensible to your indexes
Replay the workload
Compare performance
Rinse and repeat

There is no other way to know for sure.


PostgreSQL Drop Database With Connected Users

Once you've set datallowconn = 'false', it does exactly what it says on the tin, and doesn't allow connections to that database.

Connecting as the postgres user to the template1 database should allow you to drop that database.

For more information check the documentation on template databases. They're quite useful. =)


Capturing queries run by user on SQL Server using extended events

You'll want to use the sqlserver.sql_statement_completed event, which includes the statement action field by default. Also, add the package0.collect_system_time action and there are several options for capturing the username.

This example uses sqlserver.session_nt_username to get current session NT user. It will output the results into a file and you will likely need to adjust some of the options as necessary for your environment.

CREATE EVENT SESSION [XE_QUERY_CAPTURE] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.session_nt_username)
    )
ADD TARGET package0.event_file(SET filename=N'C:\Test\XE_QUERY_CAPTURE.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF
    );
GO

ALTER EVENT SESSION [XE_QUERY_CAPTURE] ON SERVER
STATE = START;


Series of questions about ER diagram and database?

Q1:  Why do you need a ternary relationship?  Test is a relationship between a patient and a doctor.  What is the third entity you need to specify in order to pin down a specific instance of a test.  I assume that a doctor can order more than one test for a given patient.
Q2:  They carry different information.  One of them is appropriate in any given situation, and the other does not carry the needed information.
Q3:  In the Chen ER model,  relationships are generally not given a primary key attribute.  Instances of the relationship may be identified by identifying each of the participants in the instance.
Q4: There is more than one way to skin a cat.
Q5: Make an entity weak if it can have no existence outside the context of the related strong entity.  Invoice detail line is a weak entity.  There will never be an invoice detail line without a corresponding invoice.
Q6:  There are hundreds of good textbooks to learn more about database design.  The ones by CJ Date are classics.  There a re a few good sources on EER,  but most good sources on ER will  incorporate EER concepts.  Here is a link to ternary relationships, but it repeats stuff you already know.  https://www.tutorialspoint.com/N-ary-Relationship-in-Database#:~:text=N-ary%20Relationship%20in%20Database%201%20Unary%20Relationship.%20When,a%20binary%20relationship.%20...%203%20Ternary%20Relationship.%20  the primary value to you is the terminology of N-ary relationships.  This should help you google.
You didn't ask this,  but you need to know it anyway.  There is a difference between analyzing the subject matter  (hospitals),  and designing a database to hold the relevant data.  People who do not understand the difference often end up producing the right solution to the wrong problem.


More efficient SQL for different databases with same schema

There are various ways to write the query in shorter form. I'm not sure if they could improve performance (or make it worse!)

The best lines of attack if the query isn't efficient is to change it into UNION ALL (provided that the results are equivalent) and examine/optimize the parts separately, make sure indexes are used, etc.

One way to rewrite is to move the UNION or UNION ALL inside a derived table and then join:

SELECT a.col1, a.col2, a.col3, b.colX, b.colY, b.colZ
FROM
    ( SELECT col1, col2, col3, DATE, someID
      FROM db_JAN.tab1    WHERE col1 = 'variable1'
      UNION ALL
      SELECT col1, col2, col3, DATE, someID
      FROM db_FEB.tab1    WHERE col1 = 'variable1'
      UNION ALL
      ---
    ) a
INNER JOIN db_OTHER.tabX b
ON (a.DATE = b.DATE AND a.someID = b.someID) ;


DECLARE @dtDate date = GETDATE();

-- month before last:
DECLARE @TwoMonthsAgo date = DATEADD(MONTH,-2,@dtDate);

-- first and last of that month:
SELECT
  FirstOfMonth = DATEFROMPARTS(YEAR(@TwoMonthsAgo),MONTH(@TwoMonthsAgo),1),
  LastOfMonth  = EOMONTH(@TwoMonthsAgo);

But then what are you going to do with that? You can't say:

SELECT cols FROM dbo.table
WHERE datecol BETWEEN @FirstOfMonth AND @LastOfMonth;

Why? Because if datecol also has time, you miss everything after midnight on the last day of the month.

Much better to just find the beginning of the current period, and from there it is trivial to find the beginning of the next period, and use an open-ended range instead of close-ended BETWEEN:

WHERE datecol >= @FirstOfMonth
  AND datecol <  DATEADD(MONTH, 1, @FirstOfMonth);

Incidentally, I just gave a whole presentation on this topic this morning.

Some tips for further reading:

Don't use variable types like varchar without specifying the length
Don't use shorthand like m - if you mean month, type MONTH
Don't use BETWEEN for date range queries


How can I retrieve the column names of a table in mysql?


The optimizer can consider many plan alternatives (including ones with multiple seeks) but for disjunctions (OR predicates) it does not consider plans involving index intersections by default.  Given the indexes:

CREATE CLUSTERED INDEX cx
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

We can force index seeks (assuming SQL Server 2008 or later):

SELECT *
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

Using your sample data, the seek plan costs at 0.0332551 units compared with 0.0068057 for the scan plan:

There are all sorts of possible query rewrites and hints we can try.  One example of a rewrite to promote an option the optimizer does not consider for the original plan is:

SELECT *
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

This execution plan does not seek the second index if it finds a match on the first:

This may perform very slightly better than the default FORCESEEK plan.

Without adding any new indexes, we can also force a seek into the Dispatch table:

SELECT *
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

This may be better or worse than the first example depending on things like how many rows are in each of the tables.  The APPLY + TOP improvement is still possible:

SELECT *
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;


Restore all IDENTITY seeds (out of sync since database restore)

Next script generates a DBCC CHECKIDENT command for each table in your server adding 1 to the current IDENT_CURRENT value.

Notice I've commented:

--EXEC (@SCRIPT);

within the CURSOR, I strongly suggest you to check each generated COMMAND before to execute it.

Additionally you can un-comment within the SELECT statement

--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'

just to execute it for one catalog.

Instead of execute each command, you can get results as text, and execute commands from SSMS.

CREATE TABLE T1 (ID INT IDENTITY, FOO INT);
CREATE TABLE T2 (NID INT IDENTITY, BAR INT);
INSERT INTO T1 VALUES (1),(2),(3);
INSERT INTO T2 VALUES (1),(2),(3),(4),(5);
GO

8 rows affected

DECLARE @TABLE_CATALOG NVARCHAR(128),
        @TABLE_SCHEMA NVARCHAR(128),
        @TABLE_NAME NVARCHAR(128),
        @COLUMN_NAME NVARCHAR(128);

DECLARE @SCRIPT NVARCHAR(MAX);
SET @SCRIPT = '';

BEGIN TRY

    CREATE TABLE #SCRIPTS
    (
        COMMANDS NVARCHAR(500)
    );

    DECLARE CURIDENT CURSOR READ_ONLY LOCAL FAST_FORWARD FOR
        SELECT   C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
        FROM     INFORMATION_SCHEMA.COLUMNS C
        JOIN     INFORMATION_SCHEMA.TABLES T
        ON       C.TABLE_CATALOG = T.TABLE_CATALOG
        AND      C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND      C.TABLE_NAME = T.TABLE_NAME
        WHERE    TABLE_TYPE = 'BASE TABLE'
        AND      COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
        --AND      T.TABLE_CATALOG = 'YOUR_CATALOG'
        ORDER BY C.TABLE_NAME;

    OPEN CURIDENT;

    FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SCRIPT = N'DBCC CHECKIDENT('''
                        + (@TABLE_SCHEMA + N'.' + @TABLE_NAME)
                        + N''', RESEED, '
                        + CAST(COALESCE(IDENT_CURRENT(@TABLE_SCHEMA + N'.' + @TABLE_NAME) + 1, 0) AS VARCHAR(30))
                        + ');'

        INSERT INTO #SCRIPTS VALUES (@SCRIPT);

        --UNCOMENT TO EXECUTE FOR EACH ROW
        --EXEC (@SCRIPT);

        FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME;
    END

    CLOSE CURIDENT;
    DEALLOCATE CURIDENT;

    SELECT * FROM #SCRIPTS;

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END TRY
BEGIN CATCH

    IF CURSOR_STATUS('LOCAL','CURIDENT') > -1
    BEGIN
        CLOSE CURIDENT;
        DEALLOCATE CURIDENT;
    END

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END CATCH

GO

| COMMANDS                              |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |

dbfiddle here

If don't want to execute the whole script, you can get the same result by executing next query:

SELECT   N'DBCC CHECKIDENT('''
         + (C.TABLE_SCHEMA + N'.' + C.TABLE_NAME)
         + N''', RESEED, '
         + CAST(COALESCE(IDENT_CURRENT(C.TABLE_SCHEMA + N'.' + C.TABLE_NAME) + 1, 0) AS NVARCHAR(30))
         + ');'
FROM     INFORMATION_SCHEMA.COLUMNS C
JOIN     INFORMATION_SCHEMA.TABLES T
ON       C.TABLE_CATALOG = T.TABLE_CATALOG
AND      C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND      C.TABLE_NAME = T.TABLE_NAME
WHERE    TABLE_TYPE = 'BASE TABLE'
AND      COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'
ORDER BY C.TABLE_NAME;
GO

| (No column name)                      |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |

dbfiddle here