Yeah, that's the older version. We released a new one a couple days ago that has a performance improvement for that query.

The join in the new code should look like this (as well as use a table variable (I know, I know), and also READ UNCOMMITTED):

WHERE (s.session_id = b.session_id
OR s.session_id = b.blocking_session_id)

The newer version should complete in roughly the same time as the lovely and talented sp_WhoIsActive.


What is the difference between *data dictionary* and *data dump of SEDE*?

Well, the data dump is the data itself, i.e. the questions, answers, comments, user information etc. of the Stack Exchange sites. Note that on Stack Exchange, 'data dump' usually refers to something else than SEDE: a quarterly dump of the data published in XML format. It contains more or less the same information as SEDE (which is the reason the schema Q&A is shared between them), but in a different format, and it isn't updated as often as SEDE is (SEDE is updated once a week, on Sunday morning).

There is no formal data dictionary for the entirety of SEDE; most of the schema documentation can only be found in the Database schema documentation for the public data dump and SEDE and isn't available as a database/repository/API itself. The schema is rather small and schemas of this size don't benefit as much from a data dictionary as large-scale applications like ERP software does.


Foreign Key as Primary Key when "converting" conceptual entity-relationship elements to logical relations

Apparently, the problem at hand is about "resolving" many-to-many relationships that may occur in ERDs.  A popular approach is: to add an intersection entity - aka as junction and under various other names - that has 1-to-many relationships to the original entities.  Example - entities: NUMBER and LETTER.  Suppose the (integer) numbers 1-26 can be combined with the letters A-Z.  And: we only want to allow unique combinations of numbers and letters.  Initially, we have a M:M relationship between the 2 entities.  In order to resolve this, we add an INTERSECTION (the original M:M relationship is still there, but will not be needed at the later stages)

When using Oracle's "datamodeler", we can forward-engineer the ERD to a relational model, which shows us some implementation details:  1 the 2 columns of the INTERSECTION have foreign key constraints (referencing the tables NUMBER and LETTER, respectively.  This will allow multiple occurrences of (one and the same) letter/number.  2 the 2 columns "NUMBER_number_" and "LETTER_letter_" have a primary key constraint ie (among other things): only unique number/letter combinations are allowed.  (Notice that this particular modelling software even generates an intersection table "Relational_1" automatically ...)

The resulting DDL code, and some code for testing, could be something like (table/column/constraint names differ from the models, MySQL 5.7):

-- test tables and data
-- parent tables
create table t_numbers ( number_ integer primary key ) ;
create table t_letters ( letter_ varchar(1) primary key );

drop procedure if exists populate_n_l;
delimiter //

create procedure populate_n_l()
begin
  declare v1 int default 1 ;

  while v1 <= 26 do
    insert into t_numbers ( number_ ) values ( v1 ) ;
    insert into t_letters ( letter_ ) values ( char( v1 + 64 ) ) ;
    set v1 := v1 + 1 ;
  end while;

end//

delimiter ;

call populate_n_l() ;

Finally:

-- intersection
create table t_intersect (
  inumber integer
, iletter varchar(1)
-- constraints
, constraint fkey_number
     foreign key( inumber ) references t_numbers( number_ )
, constraint fkey_letter
     foreign key( iletter ) references t_letters( letter_ )
, constraint pkey_combination
     primary key ( inumber, iletter )
) Engine=InnoDB;

Testing:

-- okay (valid combination)
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'A' ) ;

-- does NOT work ( as expected! - number/letter combinations must be unique ... )
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'A' ) ;
-- ERROR 1062 (23000): Duplicate entry '1-A' for key 'PRIMARY'

  insert into t_intersect ( inumber, iletter )
    values ( 1, '1' ) ;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...

  insert into t_intersect ( inumber, iletter )
    values ( 0, 'D' ) ;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...

-- fine
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'B' ) ;
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'C' ) ;

mysql> select * from t_intersect;
+---------+---------+
| inumber | iletter |
+---------+---------+
|       1 | A       |
|       1 | B       |
|       1 | C       |
+---------+---------+

Dbfiddle here.


Do not add a comma in front of the string if value is null or empty

Since you are already combining CONCAT and + to concatenate strings you could just turn them around (although I agree Lennert's solution shows the intent a lot clearer).

Update Companies
-- set address4 = concat(address4,','+ t.Eircode)
set address4 = concat(address4 + ',', t.Eircode)
From companies c
Inner Join Temptbl1 t
on c.comp_id = t.Comp_ID
OR address4 = ''

This works because CONCAT converts NULL into an empty string of type varchar(1), but as long as CONCAT_NULL_YIELDS_NULL is on concatenating a string with NULL will yield NULL.

See this dbfiddle for an example


Why backup database by Barman can't reach end(pending)?

The reason was I did recovery after the first backup operation. Then Barman set a string #BARMAN# ahead archive_command = 'barman-wal-archive backup pg %p' command into the postgresql.conf file!

So it can't call that command when do the second backup.

Remove the string, it works.

The design to aim to don't allow user to use archive_command again after recovery?


How to create and send mail in SQL Server 2008?

In SSMS object Explorer, click Management
Right-click Database Mail
Click Configure Database Mail

4.Select Set up Database Mail by performing the following tasks

5.Enter a profile name and click Add

6.Enter the settings for your email account. I could not set the account for my company's email account - it just didn't work. But I had no trouble with setting the Gmail account I use and these are the parameters that you should use for the Server name and Port number

It is not possible to send attachments from SSMS, you have to use T-SQL for that:

EXEC msdb.dbo.sp_send_dbmail
@profile_name='carol Gmail',
@recipients='john@john.com',
@subject='Test email',
@body='Attachment test',
@file_attachments='D:\123\pic.jpg'


How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

I don't know what CREATE STATISTICS does, but statistics for the optimizer are collected using the ANALZYE command when autovacuum is running - which is turned on by default.

Statistics are always collected for all columns, no need to turn it on specifically.

You can control the level of details collected for the statistics on a per-column basis using ALTER TABLE ... ALTER COLUMN column SET STATISTICS integer.

An index in PostgreSQL is always non-clustered, so I'd assume that the above index maps to a regular index on the QuestionId column.

Not sure about the INCLUDE part. I assume this is to support index only retrievals if that index is chosen by the optimizer.  As PostgreSQL does not yet have an index-only retrieval, there is no equivalent technique there.


When is dm_exec_procedure_stats updated?

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.
NOTE: An initial query of sys.dm_exec_procedure_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Resource
Is sp_recompile or WITH RECOMPILE being used? Those can cause the stored procedure to be removed. Also, it could be in regards to the name of the procedure. This answer talks about that.


How can I pass a string as where clause to a function?

EDIT: Following the Horse's answer/comment, this is my final version of my function:

CREATE OR REPLACE FUNCTION public.pointscanner(IN routegeometry text, IN criteria text)
  RETURNS TABLE(way geometry, name text, distance double precision, tags hstore, operator text, admin_level text, z_order integer) AS
$BODY$
DECLARE
    geomRoute geometry;
    routeBB box2d;
    sql text;
BEGIN
    geomRoute = ST_GeomFromText( $1 ,4326);

    routeBB := ST_Extent(geomRoute);

    sql := 'SELECT pt.way, pt.name, ST_Distance( $1, ST_Transform(pt.way,4326) ) * 111195 as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order '
     || ' FROM planet_osm_point pt where $2 && ST_Transform(pt.way,4326) and ' || criteria;

    RETURN QUERY EXECUTE sql USING geomRoute, routeBB;

END; $BODY$
  LANGUAGE plpgsql VOLATILE

Called as: select * from pointscanner(<first_arg>, 'x = 3') where second selection

Performance test: must I put the selecion criteria inside or leave it outside a function?

In this test I create a functio that select some records in a OpenStreetMap table. This is the heart of the function:

Function pointscanner():

RETURN QUERY SELECT pt.way, pt.name, ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) * 111195
as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order
FROM planet_osm_point pt
where routeBB && ST_Transform(pt.way,4326)

The fact is: I must do a second selection (fine tunning) in the function result. Must I do this inside the function and pass the selecion parameters or I can do it outside the function?

Selection exemple: name is not null

Option 1: a where clause inside the function:

Change function to:

RETURN QUERY SELECT pt.way, pt.name, ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) * 111195
as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order
FROM planet_osm_point pt
where routeBB && ST_Transform(pt.way,4326) and pt.name is not null;

Call it as: select * from pointscanner() where round(distance::numeric, 5) < 500

Result:

"Function Scan on pointscanner  (cost=0.25..17.75 rows=333 width=172)
    (actual time=1251.040..1251.792 rows=358 loops=1)"
"  Filter: (round((distance)::numeric, 5) < '500'::numeric)"
"Planning time: 0.074 ms"
"Execution time: 1251.867 ms"

Option 1: a where clause outside the function:

Leave the function untouched.

Call it as: select * from pointscanner() where round(distance::numeric, 5) < 500 and name is not null

Result:

"Function Scan on pointscanner  (cost=0.25..17.75 rows=332 width=172)
    (actual time=4654.345..4655.075 rows=358 loops=1)"
"  Filter: ((name IS NOT NULL) AND (round((distance)::numeric, 5) < '500'::numeric))"
"  Rows Removed by Filter: 238"
"Planning time: 0.114 ms"
"Execution time: 4655.161 ms"

So I conclude I MUST do the second selection criteria INSIDE the function.


How can I categorize entites by their age in an entity relational diagram?

This would be a case for either filtering the data during your query (WHERE clause) or creating a view that already filters it out. In normal practice, a person table is a person table, and unless the fundamental fields are different (like the difference between customers and employees), then all person records go into the same table, and the diagrams reflect that.
When drawing things up, start with just how the data is related across the database, then start worrying about sorting, grouping, and filtering based on the data for when you are planning out stored procedures and views.


Can a MS SQL Server be named different than host computer?

I have been searching for MS best practices but cannot seem to find if you are able to change the name of the SQL Server to be different than the host computer or if it's recommended not to do it.

If you installed default instance then your sql server name will be hostname XYSVC105.

If you installed named instance e.g. sql01 then your sql server name will be XYSVC105\sql01.

Below are few ways that you can use to have your application connect to the name you wish e.g. OPSRES.

  I would like to change the SQL Server name to something like "OPSRES".

You can create an alias and have your client applications use that in connection string.

Also, since you are using sql server 2014 - if you are using AlwaysOn Availablity group then you can just use listener (its a virtual network name (VNN) or Virutal IP (VIP))  and have your application use listener instead of host names.

You can even ask your DNS Admin to create Hostname Alias for a SQL Server Instance


Designing a user authenication (Roles & Rights) module

Based on your stated requirements, your model is in pretty good shape.

Here are some suggestions for improvement:

You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly.  This would be very bad!  If you look at common authentication databases, passwords are stored in encrypted form.  You often see both a password column and a password_salt column.
Your USER_LOGS table has an Event column.  You aren't clear about how this is to be populated.  Should there be an EVENT_TYPE table which USER_LOGS references?  This might make for friendlier reporting.  Typical events would include log-in, log-out, password fail, password change, password reset, lock-out, unlock, ...
Your GROUP_RIGHTS table doesn't indicate who granted the rights.  For audit trail purposes, people often keep a log of who changed what record and when.  That may not be an issue for you.

Here's a couple of questions around your stated business requirements, which differ from the "text book" role-based security pattern in a couple of ways:

Are you sure you want users to be in only one group?  The advantage of role-based security is that the roles tend to be pretty static, whereas the people fulfilling roles come and go pretty often.  Included in this is that some people often "wear two hats".
Your design is grant-only.  Some systems include grant and revoke.  This allows you to say that a widely available right is not available to a particular group.
You have users and accounts comingled as USERS in your design.  There is often a distinction between people and user IDs.  Some user IDs are for teams or machines and some people have multiple user IDs for different purposes.  Is this a distinction that would be helpful to you?


Viewing disk usage of temporary buffers of running queries in Postgres

If you have queries generating temporary files, it could indicate that work_mem is not sufficiently sized or that the query is not optimised.

Try setting the parameter log_temp_files = 0 in your postgresql.conf.  This will log the creation of temporary files and the statement being executed.

Additionally an explain analyze of your query may show disk sort happening.

Taking the log output, you will see the size of temp  files created by your query.  You can use this to either fine tune the query or fine tune the work_mem parameter.


Remove collation from all database columns

With this script here, you can find all columns that don't match the database default:

DECLARE @DatabaseCollation VARCHAR(100)

SELECT
    @DatabaseCollation = collation_name
FROM
    sys.databases
WHERE
    database_id = DB_ID()

SELECT
    @DatabaseCollation 'Default database collation'

SELECT
    t.Name 'Table Name',
    c.name 'Col Name',
    ty.name 'Type Name',
    c.max_length,
    c.collation_name,
    c.is_nullable
FROM
    sys.columns c
INNER JOIN
    sys.tables t ON c.object_id = t.object_id
INNER JOIN
    sys.types ty ON c.system_type_id = ty.system_type_id
WHERE
    t.is_ms_shipped = 0
    AND
    c.collation_name <> @DatabaseCollation

For each of those columns, you can then execute this simple command to change their collation to be the database default:

ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(50) COLLATE DATABASE_DEFAULT

If you really really want, you could probably also combine the two - use a cursor on the SELECT statement from the first script and then iterate over the results and execute the second statement for each column found - I typically prefer to do this manually, to be more aware of what I'm doing (and how much I'm changing):


Separation of users columns in another table

Such a 1:1 mapping is sensible when part of the table is heavily accessed and part of it is lightly used.

It's a good choice.


Changing collation type database-wide in MySQL

I found an answer on this website: http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

I cleaned up the code to print out better from a linux shell and added the $new_charset and $new_collation variables to the alter database statement.

vi change_collation_everything.php

<?php

// Configuration Section
$server = 'localhost'; //probably localhost but change if required
$username = 'root';
$password = 'password';
$database = 'database';
$new_charset = 'latin1';
$new_collation = 'latin1_general_cs';

// Connect to database
$db = mysql_connect($server, $username, $password); if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database); if (!$select_db) die("could not select $database: ".mysql_error());

// change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

// Loop through all tables changing collation
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
    $table = $tables[0];
    mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

    // loop through each column changing collation
    $columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
    while($cols = mysql_fetch_array($columns)) {
        $column = $cols[0];
        $type = $cols[1];
        mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
        print "changed $table collation of $column to $new_collation\n";
    }

    print "changed collation of $table to $new_collation\n";
}
print 'The collation of your database has been successfully changed!\n';
?>

I tested the answer on my own database changing from charset latin7 to latin1 and latin7_general_cs to latin1_general_cs for the collation.

I chose _cs instead of _ci for the collation because if you choose _ci which is the default collation for charset latin_1, the collation no longer shows up in the show create table tablename column output.

Try it out

$> php change_collation_everything.php

You can see all of the available charsets and their corresponding collations with the show collation sql query


A column value depends on the values of two other columns, of which exactly one is nullable

If I'm understanding your description, PersonID_ID is a duplicate of Citizen_IdentityID or Foreigner_WorkPermitID (whichever is not NULL).  If this is true, I'd suggest something similar to what DCook has suggested:

remove Citizen_IdentityID and Foreigner_WorkPermitID (eliminate duplicate data)
add a Person_Type (char(1) not NULL) column; current values could be 'C' or 'F' for Citizen/Foreigner, respectively

Depending on where your data is coming from, this design would allow for the inclusion of future person types (eg, in the US you have citizens, visa holders, green card holders; in some countries there are clear differences in citizen vs resident vs visitor vs non-visitor visa - not to mention different types of visas). Obviously the need for additional person types will depend on what you're trying to model.

Looking at this a bit more ...

Can an individual's type/status change over time (eg, foreigner becomes citizen; citizen relinquishes citizenship and becomes a foreigner)?

The reason I ask is that you'd obviously need to change the value of the Person_Type and PersonID_ID columns ... probably not a big deal ... but based on your model it appears you'll be using PersonID_ID as part of the PK in at least one other table, so that table's PK would also need to be updated ... while certainly doable, this could quickly get messy depending on the number of tables and records that would need their PK modified, plus the hassles of working around RI constraints.  The mess gets a little nastier if you also need to maintain some sort of history/audit-record for a person (eg, having to store/map the changing of PKs ... yuck!).

So now I'm wondering if PersonID_ID should be a separate, truly unique value, independent of the Person_Type? and if so, then you'd probably need to retain the Citizen_IdentityID/Foreigner_WorkPermitID value, but perhaps give it a more generic name.

So, that would change my suggestion to something like:

add a Person_Type (char(1) not NULL) column; current values could be 'C' or 'F' for Citizen/Foreigner, respectively
change PersonID_ID to a be it's own unique value (could be done through something like an identity column or a unique-PK generator); this value would never change, even if Person_Type were to change over time
replace the Citizen_IdentityID/Foreigner_WorkPermitID columns with a single, generic ID that serves as a FK to the Citizen/Foreigner tables, as appropriate); perhaps something like Person_TypeID (int not NULL)?

OK, one more concern/question ...

Can a person ever be associated with more than 1 Person_Type (C/F)?

For example, if a foreigner goes through the naturalization process to become a citizen, how would this be represented in your model?

Would the person now have both a Foreigner_WorkPermitID and a Citizen_IdentityID, thus requiring the storage of both values in the Person table?

Or would you update the Person table to show a change from 'Foreigner' to 'Citizen' (ie, change Person_Type and Person_TypeID)?


Do any SQL dialects permit the logical sequence of SELECT clauses?

implementations of SQL

No, SQL is standardized. There aren't vastly different implementations of a standard. That kind of defeats the purpose. There was/is always a fight over the relational-calculus (which SQL pretends to be) vs relational-algebra approach. It an old debate,

Having selected a model for the data, our next step was to
select a query language. Two families of higher level data sublanguages
for a relational data base are based on, alternatively, the relational
algebra (derived from the algebra of sets) or the relational calculus
[Codd, 197lb , 1971c and Date , 1975] (derived from the predicate calculus). Codd [1971c] and Date [1975] have compared these two and found the relational calculus to be superior, particularly for use as a target language for a - natural language system. The main reason for their choice of the relational calculus as a target language was that the calculus is non-procedural; i.e., a query in the relational calculus conveys little information about how to proceed in searching the data base. The relational algebra is more procedural which makes the automatic construction of a query by a natural language system somewhat more difficult. - Implementation of a Query Language based on the Relational Calculus 1970

And, again from Codd himself,

Early in the development of the relational model (1969-1972), I invented
two languages for dealing with relations: one algebraic in nature, and one
based on first-order predicate logic [Codd 1971a]. I then proved that the
two languages had equal expressive power [Codd 1971d], but indicated that
the logic-based language would be more optimizable (assuming that flow
tracing was not attempted) and easier to use as an interface to inferential software on top of the DBMS. - Codd 1990, The Relational Model For Database Management Version 2

There are certainly a ton of implementations to both approaches. That said, SQL as an implementation is pretty fixed on the calculus approach.
LINQ problems
These problems never really went away with the algebraic aproach, take for instance from this blog,.
context.Cars
  .OrderBy(x => x.Id)
  .Skip(50000)
  .Take(1000)
  .ToList();

Requires hand-optimization as,
context.Cars
    .Where(x => context.Cars
        .OrderBy(y => y.Id)
        .Select(y => y.Id)
        .Skip(50000)
        .Take(1000)
        .Contains(x.Id)
    )
    .ToList();

If you have multiple different relations with different performance characteristics (indexes, most of which are implemented as indirect and abstract) and statistics, it's just hard to write procedural set logic that works efficiently.


How to delete tablespace and table after delete datafiles?

According to the My Oracle Support documents, you might have hit Bug 19579109 : DROP TABLESPACE HANG ON RECURSIVE SQL.

Possible workaround would be to make sure that all paritions have statistics gathered before the drop table or set event 14534 before drop command as shown below.

alter session set events '14534 trace name context forever, level 1';
drop table <table_name>;

Refer to the MOS document for details.

Reference: Drop Tablespace Command Slow or Hanging (Doc ID 2060324.1)


No, your decomposition is noteven in 2nd normal form.

{A -> B,C}
{E -> F}
{D -> I}
{A,D -> G}
{G -> H}

A, D and E cannot be found on the right side of a dependency, so they are members of every key. {A, D, E}  generate all attributes, so it is a superkey and therefore the only minimal key
But your decomposition is wrong AA is not a key of R1A, because G does not depend on A. So the key of R1A is {A,G}. So R1A is not in 2nd Normal Form because B depends on {A} which is a proper subset of the candidate key.
So R should be decomposed to {A,B,C}, {E,F}, {D,I}, {A,D,G}, {G,H}. These relations are in 2nd, 3rd and even in Boyce-Codd normal form.


Keep track of the last read record in MySQL

6 decimal places for lat/lng give a precision of 16cm (1/2 ft).  I would expect the GPS to fluctuate more than that.  In other words, should you really watch the lat/lng at that resolution, or maybe round to 3 decimal places?  That would give you 160m (520 ft) resolution, more like what the weather tracking is capable of.

Back to your question.

It is somewhat messy to fetch the "latest", especially if you want the "latest for each vehicle.  So, instead, I suggest keeping another table of the "Current" location.  (What you have is the "History" of the location.)

Do you need a history of the weather?  Or is the latest all you need?

Caution:  If you have thousands of vehicles and you are collecting lots of data every 10 minutes, you will rapidly fill up the disk.  So try to minimize the size of the datatypes in use.  Also, consider tossing data when a vehicle is 'idle'; this will cut in half the disk requirements?

More on lat/lng representation sizes versus representations:  http://mysql.rjweb.org/doc.php/latlng#representation_choices


you may want to check subscription expiration option or check replication monitor for the error in more details, this can help, resolving your problem.


Why is Postgres on 64-bit CentOS 6 significantly slower than Postgres on 32-bit CentOS 6

Start by using a current PostgreSQL version, preferably a PGDG build. See http://yum.postgresql.org.

Then compare with the same hardware.

There is no known significant performance difference with 64-bit vs 32-bit PostgreSQL on non-Windows platforms.

Personally, I suspect that the 64-bit CentOS might have transparent huge pages enabled, which does cause severe performance regressions with PostgreSQL. See https://dba.stackexchange.com/a/34169/7788, this mailing list post, and Google.

You might also be having issues with the zone reclaim mode for NUMA; Linux's default works very poorly for databases that use shared memory. Set vm.zone_reclaim_mode = 0 in sysctl.conf and apply the changes.


File size when extracting from SQL Server to CSV

Native format (-n) is the most compact, and best if you're moving from one SQL Server to another.  The files will also zip quite nicely.


How to establish a Two-to-One table relationship (PK-FK-CK)?

I'd say it depends on whether there's a connection between People and Project?

From what you've mentioned, I'd properly go with a:
Projects -> ProjectsToMessage <- Message
and
People -> PeopleToMesseage <- Message
type structure

(your bullet point 2)

Unless there's a releation between Projects and People in which case you might end up with some circular dependency which the database wouldn't like.
However in that case, I would then question whether a Message can be associated to a Project without it going through People.


You can use a CTE (WITH clause), that precomputes all the SUM(Costs) and then UNION ALL two queries: one with the "top 5" from the CTE; the other one with the SUM of the rest:

WITH q2 AS
(
SELECT
    q1.*, row_number() over (order by Cost DESC) AS r
FROM
    (
    SELECT
        q.brokerage_id, SUM(c.cost) AS Cost
    FROM
        tblquotesnew q
        join tblcommissionnew c  ON q.quoteid = c.quote_id
    WHERE
        c.calc_date >= Trunc(SYSDATE, 'iw')  AND c.calc_date <= Trunc(SYSDATE, 'iw') + 7 - 1 / 86400
    GROUP BY
        q.brokerage_id
    HAVING
        SUM(c.cost) IS NOT NULL
    ) q1
ORDER BY
    Cost DESC
)
-- The "top 5"
SELECT
    q2.r AS rank, b.brokerage_name AS Brokerage, q2.Cost
FROM
    q2 LEFT JOIN tblbrokeragesnew b ON b.id = q2.brokerage_id
WHERE
    r <= 5
UNION ALL
-- The sum() of the rest (notice there is no group by; we add them all)
SELECT
    6 AS rank, 'Others', SUM(Cost)
FROM
    q2
WHERE
    r > 5
ORDER BY
    rank ;

You'll get a result that looks like:

RANK | BROKERAGE   | COST
---: | :---------- | ---:
   1 | Brokerage 1 | 8351
   2 | Brokerage 5 | 2231
   3 | Brokerage 4 | 2227
   4 | Brokerage 3 | 2223
   5 | Brokerage 7 | 2210
   6 | Others      | 4420

NOTES:

Your LEFT JOIN on tblcommissionnew is actually an INNER JOIN, since you use tblcommissionnew in your where in such a way that it will eliminate all nulls.
You don't need to JOIN with the tblbrokeragesnew table until the end. Oracle probably is good enough to optimize it, but it's cleaner to do it explicitly. (I've assumed that tblbrokeragesnew.id is a PK).

You can check everything (including the table definitions I've assumed) at dbfiddle here


Grant privileges in other table than mysql table

No. Grants are not defined per database (as in mongodb 2.4). mysql database is a centralized system of records for authentication etc... (just like admin database in mongodb 2.6+).

You might want to refer the document for mysql privilege system.


It depends on what you mean by improve performance.  You can't make that query faster, it needs to scan the table.  But you can keep it from using too many resources and blocking other processes by chunking it, which is essentially iterating through batches of  rows to perform multiple smaller updates.

There must be some option to just write it to the target table instead of updating in place.

You could also forego the scrub altogether and use a non-persisted computed column in the target database to reformat the phone number if you don't need to index it.


SQL Server FullText search on non-persisted columns

There isn't any kind of drawback with having a full-text index on a non-persisted column. Creating an index and putting a non-persisted column, the column will be automatically persisted on disk. You can try by yourself creating a nonclustered index and putting a non-persisted column. After this, you can use the command DBCC PAGE for looking the entire data of the nonclustered index and you will see the non-persisted column there.

Have one thing on mind. the non-persisted column stills non-persisted on the clustered-index.


Issues installing patches on SQLserver 2008

Here is a good place to start: https://support.microsoft.com/en-us/kb/969052

I ran into a similar issue as you and this allowed me to fix it. The script on that page ended up showing me all the items that needed to be fixed. In my case, the original install happened over a network share, which no longer existed. Using the output from the script, I manually copied the items to the local Windows installer cache.

Here is example of output of what it found that I did not have to fix:

================================================================================
PRODUCT NAME   : SQL Server 2008 R2 SP2 BI Development Studio
================================================================================
  Product Code: {312E8540-0799-45D5-A02E-DFB8FCA93CCA}
  Version     : 10.53.6000.34
  Most Current Install Date: 20151113
  Target Install Location:
  Registry Path:
   HKEY_CLASSES_ROOT\Installer\Products\0458E21399705D540AE2FD8BCF9AC3AC\SourceList
     Package    : sql_bids.msi
  Install Source: \x64\setup\
  LastUsedSource: n;1;\\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\

 !!!! sql_bids.msi DOES NOT exist on the path in the path \\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\ !!!!

 Action needed, re-establish the path to \\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\

Installer Cache File: C:\Windows\Installer\12b7423e.msi

    Package exists in the Installer cache, no actions needed.
    Any missing packages will update automatically if needed assuming that
    the LastUsedSource exists.

    Should you get errors about C:\Windows\Installer\12b7423e.msi or \\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\sql_bids.msi then you
    may need to manually copy the file, if file exists replace the problem file,
    Copy and paste the following command line into an administrative command prompt:

     Copy "\\server\with\path\that\no\longer\exists\SQL Server 2008 R2 Enterprise\x64\setup\sql_bids.msi" C:\Windows\Installer\12b7423e.msi

For that item, I did nothing. However, here is an example of something that it needed:

SQL Server 2008 R2 SP2 Database Engine Shared Patches Installed
--------------------------------------------------------------------------------
 Display Name:    GDR 1617 for SQL Server Database Services 2008 Core Shared (64-bit) (KB2494088)
 KB Article URL:  http://support.microsoft.com/?kbid=2494088
 Install Date:    20120227
   Uninstallable:   1
 Patch Details:
   HKEY_CLASSES_ROOT\Installer\Patches\17817DD65FBE36A47AA93B8ADF0CE829
   PackageName:   sql_engine_core_shared.msp
    Patch LastUsedSource: n;1;c:\be3c4c3dfe15678dec7048ab989c4f\x64\setup\sql_engine_core_shared_msi\
   Installer Cache File Path:     c:\Windows\Installer\f696075.msp
     Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\17817DD65FBE36A47AA93B8ADF0CE829\LocalPackage

!!!! c:\Windows\Installer\f696075.msp package DOES NOT exist in the Installer cache. !!!!

     Action needed, recreate or re-establish path to the directory:
       c:\be3c4c3dfe15678dec7048ab989c4f\x64\setup\sql_engine_core_shared_msi\ then rerun this script to update installer cache and results
     The path on the line above must exist at the root location to resolve
     this problem with your msi/msp file not being found or corrupted,
     In some cases you may need to manually copy missing files or manually
     replace the problem file,

     Copy "c:\be3c4c3dfe15678dec7048ab989c4f\x64\setup\sql_engine_core_shared_msi\sql_engine_core_shared.msp" c:\Windows\Installer\f696075.msp

     Replace the existing file if prompted to do so.

     Use the following URL to assist with downloading the patch:
      http://support.microsoft.com/?kbid=2494088

As you can see, it tells you what to copy and where to copy it to. In my case, I ignored the source location and just copied the listed files from a known good installation location (iso, installation media, local folder, or whatever has the installation files for that version of SQL Server). After that, I was able to finish patching SQL Server to the point where I could then install the latest service pack.

The difference between the two items above is the first one, even though it cannot find the file in the original location, the file is in the local Windows Installer cache where as in the second one, the original location does not exist AND the file is not in the windows installer cache.


Because back then design controls were much looser than they are today, and the person who was responsible for designing this table thought it would be easier to deal with ints than with separate datetime columns (remember this was back before we had separate date and time data types, and also when the prevailing logic was that dealing with integers was much more efficient than strings or dates). We're all still paying for this design choice. I've been tempted to try creating my own table, replacing the existing table with a view, and seeing how much I could break (not sure if it's possible, but it would make any custom coding around Agent a lot less painful).

Not sure you'll ever track down why now, unless you corner that developer and feed him or her a case of scotch. It's likely the same mentality that led to two SPID columns in sp_who2 output (true story, or so I'm told - the developer got sick of scrolling back to the left to see which SPID was responsible for the metrics on the right, so he or she added another entry at the end of the column list).


If i index two columns (indepentently) do i get the same performance as putting and index on the two

Your question is a bit jumbled.  No, you can't get theoretically the same performance, unless one of the two columns is unique or nearly unique.  But yes, you can get performance that while slower than the composite is still much faster than no index at all.  Will it be fast enough that you don't need the composite index?  No one can answer that but you.  We don't know either how fast you need it to be, nor how selective the condition against each column will be (both independently and jointly).
But if you are going to have indexes on (a) and on (b), you might as well just change the first one to be on (a,b) or change the second to be on (b,a).  The extra cost will be nearly negligible (unless the added column contains wide data values) while the extra value at least has the potential to be large.  The cost might be slightly higher in v13, because having the composite index might defeat the new deduplication logic compared to the one with just the prefix column.
Doing a detailed analysis of whether it is worthwhile is probably not worthwhile.


SQL Calculated Field in both SELECT and GROUP BY clause

I have the same calculation in both the SELECT and GROUP BY clauses. Is SQL server actually performing these calculations twice, or is it smart enough to only do it once?

The simple answer is that SQL Server makes no general guarantees about when, and how many times, a scalar expression will be evaluated at execution time.

There are all sorts of complicated (and undocumented) behaviours within the optimizer and execution engine regarding the placement, execution, and caching of scalar expressions. Books Online doesn't have much to say about this, but what it does say is this:

This describes one of the behaviours I alluded to before, deferred execution of expressions. I wrote about some of the other current behaviours (which could change any time) in this blog post.

Another consideration is that the cost model used by the query optimizer doesn't currently do much in the way of cost estimation for scalar expressions. Without a robust costing framework, current results are based on broad heuristics or pure chance.

For very simple expressions, it probably doesn't make much difference whether the expression is evaluated once or many times in most cases. That said, I have encountered large queries where performance has been adversely effected when the expression is redundantly evaluated a very large number of times, or the evaluation occurs on a single thread where it would have been advantageous to evaluate in a parallel branch of the execution plan.

In summary, the current behaviour is undefined, and there is nothing much in execution plans to help you figure out what happened (and it will not always be convenient to attach a debugger to examine the detailed engine behaviours, as in the blog post).

If you encounter cases where scalar evaluation issues matter to performance, raise the issue with Microsoft Support. This is the best way to provide feedback to improve future versions of the product.


Trim a value in an xml column in MS SQL Server

You could do that using the sql:column function and a Common Table Expression (CTE), something like this:

CREATE TABLE #tmp ( col2 XML )

INSERT INTO #tmp ( col2 )
VALUES ( '<Root>
    <Row>
        <Rowid>trim this value    </Rowid>
    </Row>
</Root>' )

SELECT 'before' s, *
FROM #tmp

;WITH cte AS
(
SELECT col2, RTRIM( LTRIM( col2.value('(/Root/Row/Rowid/text())[1]','VARCHAR(20)' )))  trimmedColumn
FROM #tmp
)
UPDATE cte
SET col2.modify('replace value of (Root/Row/Rowid/text())[1] with sql:column("trimmedColumn")')

SELECT 'after' s, *
FROM #tmp

-- Cleanup
DROP TABLE #tmp

My results:


How Wide-columns databases achieve dynamic columns on the storage layout?

Although Cassandra allows the definition of "columns" within a "table", these are much less strict than a relational database schema.  As it says here

  different rows in the same column family do not have to share the same set of columns, and a column may be added to one or multiple rows at any time.

In this world, the new value is only added to the row when that row is re-written.  The software knows where it has written the row and maintains any forward pointers to be able to return the complete row on demand. It may choose to delete the old version, freeing that disk space for reuse, and write the new row elsewhere in contiguous storage. There is no implication that making a schema change in one place automatically propagates to other, similar rows.

For key-value stores the requirements are even more lax.  What constitues a "column" is entirely up to the applicaiton.  All the storage engine sees is a blob of bits, which it writes to disk and indexes.  How or where on disk it holds these bits is neither here nor there to the application.

Not all relational databases require all parts of all rows to be contiguous.  Oracle allows a single row to span multiple pages.  SQL Server has off-page pointers for long text columns, and Filestream allows for storage in the OS's filesystem outside of the DBMS.


Microsoft SQL Server Error 1475 when Mirroring

On your primary do you have a transaction log backup job running ?

what would be happening is this db is larger by the time restores are completed the primary db would've had another transaction log backup taken, you just need to copy this transaction log backup to the DR and restore and mirroring should initialise.

other option is if you have enough disk space on your log drive you can disable log backups on the primary until you complete mirroring setup and re-enable it.

hope this help.


I usually set it as a trigger and store changes in a separate table. Keep the id_user as a FK in the table and throw in a date field to record when the change was made. Then you can recover by date even if you don't know the exact date.


Foreign keys become untrusted after bulk insert

A BULK INSERT from our application was the cause of untrusted foreign keys, as confirmed by an MSDN question. Similar to BCP, anytime a BULK INSERT is performed then the foreign key is not checked during insert, therefor making it not trusted.

As Kin mentioned, there are simple scripts available to find and fix untrusted foreign keys, but in my scenario the inserts are happening at frequency which makes effort to constantly fix this issue not worth it.

And ypercube suggested using CHECK_CONSTRAINTS option within the bulk inserts which would force the constraint to be obeyed.

I plan to review with the developers to ensure that each usage of BULK INSERT is justified in terms of rows inserted, otherwise it will have to be something to live with.


An extension to @db2's answer with less (read:zero) hand-wrangling:

DECLARE @tb nvarchar(512) = N'dbo.[table]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sys.sp_executesql @sql;


MySQL GTID replication, slave read executed binlogs, failed to get the latest binlog

One workaround for this condition is finding corresponding binlog-file and binlog-pos according to the Executed_Gtid_Set from show slave status.  Change GTID-Based replication to File-Postion replication.


Storing daily yield curve data in SQL table

Your second choice (long) is preferable for the reasons you outline, provided you use the right keys and constraints.
Something like this is probably in the right direction:
CREATE TABLE Currency
(
  CurrencyCd    CHAR(3)      NOT NULL  /* This is the ISO currency code */
 ,CurrencyDesc  VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_Currency PRIMARY KEY (CurrencyCd)
 ,CONSTRAINT AK_Currency UNIQUE (CurrencyDesc)
 ,CONSTRAINT CK_CurrencyCd_Length CHECK (LEN(CurrencyCd) = 3)
)
GO

/* This is solely to prevent invalid values being added */
CREATE TABLE MaturityAge
(
  MaturityAgeYr  TINYINT  NOT NULL
 ,CONSTRAINT PK_Maturity PRIMARY KEY (MaturityAgeYr)
)
GO

CREATE TABLE CurrencyYield
(
  CurrencyCd     CHAR(3)       NOT NULL
 ,MaturityAgeYr  TINYINT       NOT NULL  /* Possibly consider using months if finer detail possible */
 ,AsOfDt         DATE          NOT NULL
 ,Yield          DECIMAL(9,1)  NOT NULL /* Can increase decimal precision if necessary */
 ,CONSTRAINT FK_CurrencyYield_Of_Currency FOREIGN KEY (CurrencyCd) REFERENCES Currency (CurrencyCd)
 ,CONSTRAINT FK_CurrencyYield_At_Maturity FOREIGN KEY (MaturityAgeYr) REFERENCES MaturityAge (MaturityAgeYr)
 ,CONSTRAINT PK_CurrencyYield PRIMARY KEY (CurrencyCd, AsOfDt, MaturityAgeYr)
)
GO

Here the choice of the composite clustered index form by the primary key (CurrencyCd, AsOfDt, MaturityAgeYr) ensures the data will be (more or less) stored sorted according to those columns.  That means any queries of the type WHERE CurrencyCd = 'EUR' will only read the relevant pages.
This choice of clustered index + storing the data in a "long" format will make "widening" the data trivial - so if you need to view the data as you provide in the first example, it's a simple PIVOT.
Other considerations:

If the volume of data is very large, but most of the values do not change, you can explore only storing changed values.  It takes a little more code to retrieve the data for a given date, but you can create date tables/views to simplify that.  If this is the case, let me know in the comments and I'll expand my answer.
Over time the table will fragment (a large number of records will not be co-located according to the clustered index), so the clustered index will need to be rebuilt occasionally.  There are a number of readily available tools/scripts to make this relatively simple.


One Complex Stored Procedure Causes Estimated Cost Exception

It sounds like you're running into query governor cost limits.  What's happening is that the database engine figures that your query will take 5822 seconds (97 minutes) to run.  Somebody, however, has configured a setting to kill any query which is expected to run more than 300 seconds (5 minutes).

To see if this applies to you, exec sp_configure 'query governor cost limit' and see what that run_value is.  My thought is that it's currently set to 300.

If you don't want to change the limit across the board, you should be able to run this query by setting the limit beforehand:  set query_governor_cost_limit 0.  That will adjust the particular session to turn off any cost limits, as you know that your query will blow past the limit and you don't much care.  Do note that you'd need some serious permissions to run that statement (the MSDN doc says sysadmin).


sp_executesql - What is wrong with my Parameter-string?

You seem to be expecting that the parameters will end up being treated as string interpolation arguments.

They won't.

One way of achieving something similar to what you want would be simply to parameterise the stored procedure

CREATE PROCEDURE dbo.Test
@num int
AS
BEGIN
    SELECT 1, @num;
END

Then when you execute it you supply the desired value.

If you definitely want the procedure to end up with @num replaced with a hardcoded 1 you can use.

DECLARE @num int = 1

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

SET @sql = REPLACE(@sql, '@num', @num)

EXEC sys.sp_executesql @sql -- or just EXEC(@sql)

Take care of SQL injection if you go this route.

Also you would need to be careful of parameter/place holder names and order of replacements, so if you had place holders @num and @number the @number one would need to be replaced first.


User cannot query cross-database, despite having permissions for both databases

This is an artifact of your testing methodology.  You impersonated a user.  A user is a database-level principal.
Instead impersonate the login, or test with the target identity actually connecting to SQL Server.


Implications of many materialized views in Postgres?

I know of no such limit, although I have never had more than tens of materialized views, not thousands.  However a way of hedging against such a problem is to make one materialised view with the customer as a column and create an index on the customer column.  That way, Postgres will be able to pick out the relevant parts of the view very quickly.  You should have performance that is not significantly worse than having lots and lots of views.  It will also be a lot less work to maintain.


Throw and RaiseError of level 16 do not cause alert to occur

From reading the BOL article on sp_add_alert

  [ @severity = ] severity - The severity level (from 1 through 25) that defines the alert. Any SQL Server message stored in the sysmessages table sent to the Microsoft Windows application log with the indicated severity causes the alert to be sent. severity is int, with a default of 0. If message_id is used to define the alert, severity must be 0.

If you want to add alerts for user-defined error messages, you need to add your error message to the sys.messages table using sp_addmessage

   EXEC sp_addmessage @msgnum= 90210, @severity=16, @msgtext='Error raised.',  @lang= 1033;

To actually raise the alert for the error, there are a few caveats you must be aware of (seriously...read the online guide...that's all I'm doing here...)

  These are the circumstances under which errors/messages generated by
  SQL Server and SQL Server applications are sent to the Windows
  application log and can therefore raise alerts:
  • Severity 19 or
  higher sys.messages errors

  • Any RAISERROR statement invoked with WITH LOG syntax

  • Any sys.messages error modified or created using sp_altermessage

  • Any event logged using xp_logevent

So a RAISERROR for a severity 16 error will not just alert you by default, you would need to add WITH LOG after it.

RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG


Based on the result from the table, get the result from another table

You need to move the condition on the username into the JOIN condition. Applying a WHERE condition on the outer joined table turns your outer join back into an inner join.

Additionally - given your sample data - %use1r% won't match any row, I assume you meant to write %user1%. As you query only returns columns from the products table, I think you meant to use p.user_id in the group by as ell to remove duplicate products that stem from the join.

SELECT p.*
FROM products AS p
  LEFT JOIN users AS U ON u.user_id = p.user_id
                      AND LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

But given your expected output, it seems you want an inner join as you only want products assigned to the matching user.

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
                 AND LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

With an inner join it doesn't matter where you put the condition on the username. The above is equivalent to:

SELECT p.*
FROM products AS p
  JOIN users AS U ON u.user_id = p.user_id
WHERE LOWER(u.username) LIKE LOWER('%use1r%') --<< HERE
GROUP BY p.product_id, p.user_id;

But given your usage of the group by I wonder if you are trying to remove duplicate products from the result. In that case an EXISTS query is maybe what you really want

SELECT p.*
FROM products AS p
WHERE EXISTS (SELECT *
              FROM users u
              WHERE u.user_id = p.user_id
                AND LOWER(u.username) LIKE LOWER('%use1r%'));

Then you don't need to remove unwanted duplicates with a group by


mongo primary is showing behind the primary?

I don't know that I see a problem.  It says right in the second line shown that the source data is "source: xxxxxx:27017" and if you look further down, in the status results, that server is actually the secondary.  So the way I read this (and let me know if I am wrong) you are submitting the command on the Primary, but it is actually querying the secondary and saying it is over 7 hours behind the Primary.  Then you do a status and it shows the replicaSet with an optTimeDate of the same time difference.

If I were to make a guess, (and this is only a guess) I would say that one of the two machines is time-sync'd via ntp but the other is not (and the arbitrator appears to match the primary, so it is more likely the one that is time-sync'd meaning your secondary is running slow).


As @ypercube already explained, the subquery has no reference to columns in the outer query, it can be processed independently. So it is not a "correlated subquery". Some call that a "derived table", or just "subquery".

SELECT *
FROM   orderinfo o
    , (SELECT * FROM customer c1 WHERE town = 'Bingham') c2
WHERE  c2.customer_id = o.customer_id;

As to your questions:

c1 is a table alias for customer in the subquery, short for customer AS c1. The key word AS has been omitted which is fine since it would be just noise for a table alias. I quote the manual on "Omitting the AS Key Word":

  In FROM items, both the standard and PostgreSQL allow AS to be omitted
  before an alias that is an unreserved keyword. But this is impractical
  for output column names, because of syntactic ambiguities.

The example probably goes to demonstrate visibility: only the outer table alias is visible in the outer WHERE clause, so there is no naming conflict with c used twice.
Otherwise, c1 is useless here since nothing refers to it. You can just drop it.
c2 is another table alias for the "derived table". This one is mandatory since Postgres requires a name for every used table, and a subquery has none until you name it.

The example is not very good overall. Such a query should rather use an explicit JOIN clause and the subquery is just useless. This would be better, shorter and faster:

SELECT *
FROM   orderinfo
JOIN   customer  c USING (customer_id)
WHERE  c.town = 'Bingham';

The only difference: customer_id is listed once instead of twice in the result (due to the USING clause), which would be preferable since it is completely redundant in this case.

Details in the manual about SELECT.


When you create the self signed certificate, you need to use a valid host name in the "Common Name" field, e.g.:

  Common Name (eg, your name or your server's hostname) []: host.domain.com

If you want use a domain certificate, you need to change "host" with "*", e.g.:

  Common Name (eg, your name or your server's hostname) []: *.domain.com


Something similar happened to my BIDS after I installed Visual Studio '15 on my dev machine.  There are registry overwrites when a newer version of BIDS/SSDT/Business Intelligence Templates are installed causing failures in older version.  I fixed this by setting up a vm on my local that I only use BIDS on.
Why Can't SQL Server and Visual Studio Get Along?


How to clear ad hoc queries from plan cache?

So you just want to clear the Ad-hoc query plans and still dont want to clear the whole procedure cache. What you are asking is there in Clearing Your Ad-hoc SQL Plans While Keeping Your SP Plans Intact
The blog asks you to run
DBCC FREESYSTEMCACHE('SQL Plans')
As per the blog

The procedure cache actually consists of 4 distinct cache stores that hold different types of plans. Those cache stores are:

CACHESTORE_OBJCP - these are "Object Plans" - stored procedures, functions and triggers. Generally, the good stuff.
CACHESTORE_SQLCP - these are "SQL Plans" - ad-hoc SQL statements (including parameterized ones) and prepared statements. This is the
stuff that we are after.
CACHESTORE_PHDR - so-called "Bound Trees" for views, constraints and defaults. Irrelevant for the problem discussed.
CACHESTORE_XPROC - not really execution plans but rather pointers to the entry points of your extended SPs.

So you can see selectively clearing the SQLCP would remove prepared and ad-hoc plans. I tested this on my system.
Ran query
select objtype,
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
--where objtype='adhoc'
group by objtype

And the output was

You can see the the picture has 1264 ad-hoc plans and 69 prepared statements.
Now I selectively clear the SQLCP using DBCC FREESYSTEMCACHE('SQL Plans') and rerun the query again which gave me below output

Now you can see the ad-hoc and prepared plans are 2 and 6 respectively. While other are NOT affected.


Resolving sp_Blitz Finding: Many Plans for One Query

Community wiki answer:

If the query is now parameterized, you should be able to ignore all the ones that still show constant literals - those are in the plan cache now, but they'll age out over time.

In addition to the outer join that's not really an outer join anymore, is SELECT * required, or is it possible there is a skinnier pair of indexes that could cover the actual output requirements (usually some subset of *)? You should also encourage your developers to always use the schema prefix e.g. schema.object.

Simple parameterization only applies to trivial plans.

Also consider optimize for ad hoc workloads. Kimberly Tripp has some good guidance on cleaning the cache. Think of forced parameterization as a last resort, because it will affect all your queries, with a high risk of performance regressions.


MySQL - Create a function from another function

You could play some games with mysql.proc, the physical home of all Stored Procedures and Stored Functions.
First, here are the databases on my PC
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| david              |
| dufran             |
| jimelliott         |
| junk               |
| karto              |
| mysql              |
| performance_schema |
| preeti             |
| rolando            |
| sandro             |
| telwit             |
| telwit_french      |
| test               |
| user1162541        |
| yellowseason       |
+--------------------+
16 rows in set (0.00 sec)

mysql>

Let's create a Stored Function in the database rolando called GetUnixTimeStamp:
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetUnixTimeStamp` $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `GetUnixTimeStamp`(x INT) RETURNS int(11)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN UNIX_TIMESTAMP();
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT rolando.GetUnixTimeStamp(9);
+-----------------------------+
| rolando.GetUnixTimeStamp(9) |
+-----------------------------+
|                  1352263567 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

Let's try to sculpt the CREATE FUNCTION SQL Statement based on the function itself
SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `',db,'`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

What does this generate ?
mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `',db,'`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `rolando`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK so far, so good. Let's change the SQL to place this function in the database david
mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `david`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `david`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK Great. We can make the SQL. Can I execute the creation of the new function ???
echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

Does the function exist ???
mysql> show create function david.getunixtimestamp\G
*************************** 1. row ***************************
            Function: getunixtimestamp
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `getunixtimestamp`(x INT) RETURNS int(11)
    DETERMINISTIC
BEGIN     RETURN UNIX_TIMESTAMP(); END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

Biggest questiion of all : Does the newly copied function run ???
mysql> select david.getunixtimestamp(8);
+---------------------------+
| david.getunixtimestamp(8) |
+---------------------------+
|                1352265404 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

HOORAY !!! This is how you can copy a function from one database to another.
FINAL QUESTION : Is this all worth it ???
SUMMARY
Here is the original SQL to generate function creation
SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `david`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

This will create Stored Procedures as well
Here is the copy operation, which must take place from the OS level calling the mysql client
echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt


FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

I have very bad news for you.
Unfortunately, that is the correct behavior.
The MySQL Query Optimizer tends to get sidetracked very easily when using a FULLTEXT index.
I also have very good news for you.
I wrote about this in StackOverflow on how to get around it.
You may have to nest your original query in a subquery and return the MATCH function as a column. Then, evaluate the MATCH column outside of the subquery.
Instead of your query
select * from seeds  WHERE  MATCH(text) AGAINST ("mount cameroon" IN BOOLEAN MODE) = 4;

you must refactor it into something like this:
SELECT B.* FROM
(
    SELECT id,MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE) score
    FROM seeds
    WHERE MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE)
) A
INNER JOIN seeds B USING (id)
WHERE A.score = 4;

Give it a Try !!!


Does a Postgres VACUUM free up space for any database object?

After a VACUUM the table will be as big as before, just more of the space will be free and available for future INSERTs and UPDATEs. In other words, the file won't shrink (1). So the space will not be available for other tables, only for the table that has been vacuumed.

Note (1): Sometimes the file will shrink. If the last block is completely empty after the VACUUM, PostgreSQL will try to truncate all empty blocks off the end of the table.


Update an array element with inc mongo update

I accomplished it with your criteria which was to use the tags field in your query. I of course had to filter on the document further to only update that specific document.
I presumed other documents may have a tag of "famous". I created two documents with tags:famous to prove this worked when updating 1 document. Here's my solution with comments.

//basics
db = test
collection = test

//data looked like this with two documents for testing
/* 0 */
{
    "_id" : ObjectId("5498778567bbbc2fff723515"),
    "articleId" : [
        {
            "articleId" : "9514666",
            "articleCount" : 1
        }
    ],
    "count" : 1,
    "timeStamp" : "1416634200000",
    "interval" : 1,
    "tags" : "famous"
}

/* 1 */
{
"_id" : ObjectId("5498781567bbbc2fff723516"),
"articleId" : [
    {
        "articleId" : "951466622",
        "articleCount" : 22
    }
],
"count" : 1,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}

//confirmed I can query the one document like this
db.test.find({'tags': 'famous','articleId.articleId':'9514666'})

//was able to execute an update as described
db.test.update({'tags': 'famous','articleId.articleId':'9514666'}, {"articleId" : [
        {
                "articleId" : "9514666",
                "articleCount" : 4
        },
        {
                "articleId" : "9514667",
                "articleCount" : 3
        }
],

"count" : 7,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
})

//Resulting update to document is
/* 0 */
{
 "_id" : ObjectId("5498778567bbbc2fff723515"),
 "articleId" : [
    {
        "articleId" : "9514666",
        "articleCount" : 4
    },
    {
        "articleId" : "9514667",
        "articleCount" : 3
    }
],
"count" : 7,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}

//showing both documents, only first one updated obviously
/* 0 */
{
"_id" : ObjectId("5498778567bbbc2fff723515"),
"articleId" : [
    {
        "articleId" : "9514666",
        "articleCount" : 4
    },
    {
        "articleId" : "9514667",
        "articleCount" : 3
    }
],
"count" : 7,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}

/* 1 */
{
"_id" : ObjectId("5498781567bbbc2fff723516"),
"articleId" : [
    {
        "articleId" : "951466622",
        "articleCount" : 22
    }
],
"count" : 1,
"timeStamp" : "1416634200000",
"interval" : 1,
"tags" : "famous"
}


How to deal with `.partial` WAL segments generated by Postgres `pg_receivewal` command?

Hmm, good question.

I suggest that you rename it and fill it with zeros at the end, for example using

dd if=/dev/zero of=000000010000000C00000080 oflag=append conv=notrunc bs=1024 count=16416

This could be part of your restore_command.


Here is the whole query:

Declare @job sysname
Declare jobs_cursor Cursor For
    Select S.name
    From msdb.dbo.SYSJOBS S
    Inner Join (values
        ('UK15SUMMProduct')
        , ('US15SUMMProduct')
        , ('DE15SUMMProduct')
        , ('AT15SUMMProduct')
        , ('FR15SUMMProduct')
        , ('EU15SUMMProduct')
        , ('AU15SUMMProduct')
        , ('UK15SUMSProduct')
        , ('US15SUMSProduct')
        , ('DE15SUMSProduct')
        , ('AT15SUMSProduct')
        , ('FR15SUMSProduct')
        , ('EU15SUMSProduct')
        , ('AU15SUMSProduct')
    ) as n(name) on S.name like '%'+n.name + '%'

Open jobs_cursor
Fetch Next From jobs_cursor into @job

While @@Fetch_Status = 0
Begin
    Print 'Remove: '+@job
    exec sp_delete_job @job_name = @job, @delete_unused_schedule = 0
    Fetch Next From jobs_cursor into @job
End

Close jobs_cursor
Deallocate jobs_cursor

Part of it is similar to previous answers.

You can replace values(...) by a table variable or a real table.


Client application disconnects from database/s it is connected to after 10 minutes of inactivity

pgAdmin keeps throwing up a connection timeout every 10 minutes or so.

I would think that the problem might be related to a router (or, in some cases, a stateful firewall) between your pgAdmin client and the PostgreSQL server.
This is quite common when you are at home (or in the office) and your router is performing NAT (Network Address Translation). After some idle time the connection is closed by the router. It is not something done by neither pgAdmin nor PostgreSQL, this is something that depends on the settings on the router,
If this is the case, you have three options:

Change the connection_timeout setting in your router, if that is feasible and you have the proper documentation. Here is an example for Cisco routers.

Make the router believe the connection is never idle for long enough. To do that: change one server parameter, called tcp_keep_alives:

According to the PostgreSQL documentation on Connections:

tcp_keepalives_idle (integer)
Specifies the number of seconds of inactivity after which TCP should send a keepalive message to the client. A value of 0 uses the system default. This parameter is supported only on systems that support the TCP_KEEPIDLE or TCP_KEEPALIVE symbols, and on Windows; on other systems, it must be zero. In sessions connected via a Unix-domain socket, this parameter is ignored and always reads as zero.

The keepalive message should avoid the NAT connection begin dropped.
Keep in mind the remark in the documentation:

This parameter is supported only on systems that support the TCP_KEEPIDLE or TCP_KEEPALIVE symbols, and on Windows

I really don't know which systems "support TCP_KEEPIDLE or TCP_KEEPALIVE symbols", but I guess that Linux and Mac should support them, and so does Windows according to the documentation.
If your system disconnects every 10 minutes, it should be prudent to have this setting to something around 9 * 60 = 540 seconds, or less.
This setting should be done in the postgresql.conf on your server. You can also test before making the change in the server, by either using the commands:
SET tcp_keepalives_idle = 480 ;

which would alter the setting for your current connection, or
ALTER ROLE (the-one-you-use) tcp_keepalives_idle = 480 ;

which would alter the setting only for this role.

Change the keep_alives on the client. You don't need to change any connection_timeout (that parameter is "how long to wait for a connection before deciding that the other side is not responding). Follow the instructions of the accepted answer of Any way to keep connection alive in pgAdmin without setting it on the server?. It's perfectly explained.

Reference about keep-alives: This video talks about How to Keep Your SSH Sessions Alive. Although this is not exactly the same scenario, it is equivalent (substitute SSH by pgAdmin).


In SQL*Plus, execute external scripts using @filename (http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#i1210918).

This also works in SQL Developer, just be aware of the differences between relative & absolute directory.


In one situation, I found it possible to ingest data faster into InnoDB than into MEMORY.  Perhaps you should simply get rid of the MEMORY table and the TRIGGER.

I discuss that an many other tidbits in my high-speed ingestion blog.

Do you have multiple inserters?  Do you insert multiple rows in a single INSERT?  Are you pulling the data out of the MEMORY table?  How often, how, when, etc?  Is Replication involved?  Is "normalization" happening?  Summarization?


SQL Server just uses the operating system's time. However, if your application code relies on functions like GETDATE(), then your code needs to be able to handle jumps forward (or backward) in time.

For example, if your have code that does something like this:

SET @StartDate = GETDATE()
(Do some work)
IF DATEDIFF(MM, @StartDate, GETDATE()) > 60 ....

That code can fail. Long term, consider using GETUTCDATE() instead and storing dates with DATETIMEOFFSET - but that's a big code change that you can't really do quickly before changing the date on a server.


converting latin to utf8mb4 causes questionmarks

Ah, I was afraid of that.  That hex string is the "double encoding" of  أغنية سم الله Does that look about right?

This is the expression to 'fix' it:  CONVERT(BINARY(CONVERT(CONVERT(UNHEX('C398C2A3C398C2BAC399E280A0C399C5A0C398C2A920C398C2B3C399E280A620C398C2A7C399E2809EC399E2809EC399E280A1') USING utf8mb4) USING latin1)) USING utf8mb4)

This discusses how it probably came about.  You need to fix your code and fix the data:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored  (Look for "double encod")

(utf8 and utf8mb4 work equally well for Arabic.)

That short conversion you found needs to (sort of) be repeated to fix "double" encoding.

Further research

It is OK to be a mixture of Arabic and other languages in a utf8 or utf8mb4 column. It is not ok to have double-encoding, especially if some cells are correctly encoded. Somewhere the Arabic text was encoded an extra time, but the copyright symbol was not. Did they come from different sources? Maybe the problem came before this database we are looking at?

That is, dig into the client(s) you are using and dump the hex of text that is about to be INSERTed. Arabic should be two hex bytes: Dxyy; copyright (and many other popular symbols) should be Cxyy.

Fixing

If you find that some rows have double-encoded Arabic (or whatever) an d some rows have correctly encoded copyright, and if you can distinguish which rows are which, then applying the single-fix vs the double-fix should be 'easy'.

Ditto for columns.  Perhaps the copyright is never in the same column as the Arabic text?

Even messier is when a single cell has both.  That would strongly imply the client is "at fault".


How to get newly inserted row ids when inserting with execute?

To return a set, Use RETURN QUERY EXECUTE in a function that RETURNS SETOF integer:

CREATE OR REPLACE FUNCTION pubilc.test_set(_tbl regclass, _id int)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   WITH (
   SELECT format('INSERT INTO %1$s(%2$s)
                  SELECT %2$s FROM %1$s WHERE budget_id = $1
                  RETURNING id'
                , _tbl, string_agg(quote_ident(attname), ', '))
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   AND    attname <> 'id'   -- exclude id from column list
   )
   USING _id;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call:

SELECT * FROM pubilc.test_set('mytbl', 123);

To return an array, move the INSERT to a CTE and feed the result to an array constructor.

Then we don't need the function to RETURNS SETOF int[], because it always returns a single value. The simplest way is to define an OUT parameter and assign the result of the query:

CREATE OR REPLACE FUNCTION public.test_arr(_tbl regclass, _id int, OUT _ids int[]) AS
$func$
BEGIN
   EXECUTE (
   SELECT format('WITH ids AS (
                     INSERT INTO %1$s(%2$s)
                     SELECT %2$s FROM %1$s WHERE budget_id = $1
                     RETURNING id
                     )
                  SELECT ARRAY (TABLE ids)'
               , _tbl, string_agg(quote_ident(attname), ', '))
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   AND    attname <> 'id'   -- exclude id from column list
   )
   USING _id
   INTO _ids;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT public.test_arr('mytbl', 123);

Related:

RETURN value directly from INSERT with RETURNING clause
Find changed columns for near-identical rows across tables
Why is array_agg() slower than the non-aggregate ARRAY() constructor?


Count the maximum number of clients and their manager ID using three tables

You cannot name a column in mysql with a statement like MostClient = count(*)
Use the AS keyword count(*) AS MostClient.
I think the next problem you are going to encounter is the other columns that are not aggregates are missing from your group by.
Also, I don't see how the count(*) is going to equal the b.buildingid.
Best of luck!


After setting the verbosity level for command as 1, I am able to see the following entries in the log file

  2019-03-01T16:25:56.967-0800 I COMMAND  [conn67] command test.icons
  appName: "MongoDB Shell" command: count { count: "icons",
  $readPreference: { mode: "secondaryPreferred" }, $db: "test" }
  planSummary: COUNT keysExamined:0 docsExamined:0 numYields:0 reslen:45
  locks:{ Global: { acquireCount: { r: 1 } }, Database: { acquireCount:
  { r: 1 } }, Collection: { acquireCount: { r: 1 } } }
  protocol:op_command 0ms

here the collection name is icons and the database is test

I used the following command to increase the verbosity level.

db.setLogLevel(1, "command")


Why does implicit conversion from sql_variant (basetype decimal) not work well with float

From sql_variant (Transact-SQL)

  When sql_variant values of different base data types are compared and
  the base data types are in different data type families, the value
  whose data type family is higher in the hierarchy chart is considered
  the greater of the two values.

The base data type family for @v is Exact numeric and the base data type family for convert(float, 10.0) is Approximate numeric.

Approximate numeric is higher up in the hierarchy chart than Exact numeric so when you compare two sql_variant values where one is Approximate numeric and the other is Exact numeric the Approximate numeric value will always be considered greater.

The full hierarchy order is

+---------------------+------------------+
|  Data type family   |     Members      |
+---------------------+------------------+
| sql_variant         | sql_variant      |
+---------------------+------------------+
| Date and time       | datetime2        |
|                     | datetimeoffset   |
|                     | datetime         |
|                     | smalldatetime    |
|                     | date             |
|                     | time             |
+---------------------+------------------+
| Approximate numeric | float            |
|                     | real             |
+---------------------+------------------+
| Exact numeric       | decimal          |
|                     | money            |
|                     | smallmoney       |
|                     | bigint           |
|                     | int              |
|                     | smallint         |
|                     | tinyint          |
|                     | bit              |
+---------------------+------------------+
| Unicode             | nvarchar         |
|                     | nchar            |
|                     | varchar          |
|                     | char             |
+---------------------+------------------+
| Binary              | varbinary        |
|                     | binary           |
+---------------------+------------------+
| Uniqueidentifier    | uniqueidentifier |
+---------------------+------------------+


Postgres Count with different condition on the same query

If I understand you correctly, you are looking for a filtered (conditional) aggregate:

SELECT a.agent_id as agent_id,
       COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey,
       count(a.id) filter (where disposition = 'Partial Survey') as partial_survey
FROM forms a
WHERE a.created_at >= '2015-08-01'
  AND a.created_at <= '2015-08-31'
GROUP BY a.agent_id;

Edit
for older versions (< 9.4) you need to use a case statement:

SELECT a.agent_id as agent_id,
       COUNT(case when disposition = 'Completed Survey' then a.id end) as CompletedSurvey,
       COUNT(case when disposition = 'Partial Survey' then a.id end) as partial_survey
FROM forms a
WHERE a.created_at >= '2015-08-01'
  AND a.created_at <= '2015-08-31'
GROUP BY a.agent_id;


java.sql.SQLException: Login failed for user 'sa'

You are attempting to pass in a blank password.  Odds are the sa account has a password.

You should create a separate account for the application to use instead of using the sa account.  Using the sa account is a major security issue.


How to search id from one table in another table's nvarchar column

Yes, you can use % with LIKE. Since you are converting the INT "id" into a string, you can then concatenate the % character on both sides. But you also need to include a space separating each of them from the converted "id" so that you don't have an "id" of 23 matching a parameters value of something like "abc 1234 def". And yes, you can JOIN the tables since that is just a different way of expressing the same thing that you are already attempting.

SETUP

-- DROP TABLE #table1;
CREATE TABLE #table1 ( [id] INT NOT NULL PRIMARY KEY, [name] NVARCHAR(30));
-- DROP TABLE #table2;
CREATE TABLE #table2 ( [pid] INT NOT NULL PRIMARY KEY, [parameters] NVARCHAR(500));

SET NOCOUNT ON;
INSERT INTO #table1 ([id], [name]) VALUES (1, N'a');
INSERT INTO #table1 ([id], [name]) VALUES (2, N'b');
INSERT INTO #table1 ([id], [name]) VALUES (3, N'c');
INSERT INTO #table1 ([id], [name]) VALUES (12, N'ab');
INSERT INTO #table1 ([id], [name]) VALUES (23, N'bc');
INSERT INTO #table1 ([id], [name]) VALUES (123, N'abc');

INSERT INTO #table2 ([pid], [parameters]) VALUES (1, N'some text 12345 some text');
INSERT INTO #table2 ([pid], [parameters]) VALUES (2, N'DD 2 DD');
INSERT INTO #table2 ([pid], [parameters]) VALUES (3, N'EEE EE 123 EEEEE');
INSERT INTO #table2 ([pid], [parameters]) VALUES (4, N'NO23 12NO');
INSERT INTO #table2 ([pid], [parameters]) VALUES (5, N'HHHHHH YYY 23 ZZZZZ');
INSERT INTO #table2 ([pid], [parameters]) VALUES (6, N'QQQQQ r2q RRRRR');

CORRELATED SUBQUERY

SELECT r.[id], r.[name]
FROM #table1 r
WHERE EXISTS
(
  SELECT *
  FROM #table2 tmp
  WHERE tmp.[parameters] LIKE N'% ' + CONVERT(NVARCHAR(15), r.[id]) + N' %'
);
/*
id     name
2      b
23     bc
123    abc
*/

JOIN

SELECT t1.[id], t1.[name], t2.[pid]
FROM #table1 t1
INNER JOIN #table2 t2
        ON t2.[parameters] LIKE N'% ' + CONVERT(NVARCHAR(15), t1.[id]) + N' %';
/*
id     name    pid
2      b       2
23     bc      5
123    abc     3
*/


Don't assume that the trigger will always handle a single row inserted in the table. If you're inserting more than one row in Rental, the code you attempted to write will fail.

The logical tables inserted and deleted contain all the rows modified by the DML operation and they must be joined or correlated to the base table (or other tables) in order to take all the rows modified into consideration.

As far as the business logic required is concerned, this is a possible implementation:

CREATE TRIGGER tr_tblRental_ForInsert
ON dbo.Rental
FOR INSERT
AS
BEGIN
    -- Always add this to your triggers: some libraries don't like
    -- triggers altering the number of rows affected by the DML operation
    SET NOCOUNT ON;

    -- a. Does not have more than 2 books rented out:
    -- the books that are rented out are identified by having ReturnDate as NULL
    IF EXISTS (
        SELECT COUNT(*)
        FROM dbo.Rental
        WHERE MemberId IN (
                SELECT MemberId
                FROM inserted
            )
            AND ReturnDate IS NULL
        GROUP BY MemberId
        HAVING COUNT(*) > 2
    )
    BEGIN
        ROLLBACK;
        THROW 51000, 'Members are not allowed to rent more than 2 books at a time.', 1;
    END

    -- b. That the member does not owe more than 50 dollars
    -- to the library (the BalanceOwing attribute of the Member table).
    IF EXISTS (
        SELECT BalanceOwing
        FROM dbo.Member
        WHERE MemberId IN (
                SELECT MemberId
                FROM inserted
            )
            AND BalanceOwing > 50
    )
    BEGIN
        ROLLBACK;
        THROW 51000, 'Members are not allowed to rent more books if they owe more than $50 to the library.', 1;
    END

END

Please do not use this code in your project/homework if you don't understand what it does: your teacher will ask questions and you'd better have answers. If you need help understanding the code, place a comment here.


What is SQL/PSM and how does it differ from other versions of SQL?

PSM is just an addition to the standard SQL language. It's the part that adds procedural extensions: IF, WHILE, ... all structures that are also met in other procedural languages.

From Wikipedia, about SQL/PSM:
"SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute (ANSI) as an extension to SQL.1 It was first adopted in 1996,[2] and it provides procedural programmability in addition to the querying commands of SQL.

The SQL/PSM extension is defined by ISO/IEC 9075-4:2003. SQL/PSM standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., "stored procedures")."

In SQL Server the native implementation is called T-SQL stored procedures and triggers, in Oracle it's PL-SQL.


Select count distinct on key column where multiple others satisfy condition

Create groups with GROUP BY based on account_id. Find the MAX of sat_cond1 and sat_cond2 for each group. If the MAX values equal Y for both condition, keep the account_id. Count the distinct account_id values.

Above translated to SQL:

select
  count(*)
from
  (
    select
      account_id
    from
      data
    group by
      account_id
    having
      max(sat_cond1) = 'Y'
      and max(sat_cond2) = 'Y'
  )
;


does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server

does it also flushes dirty pages which are related to un-committed
  transactions into data file or it only flushes dirty pages related to
  committed transactions?

Yes of course, it flushes all the dirty pages:

  When a checkpoint operation occurs, no matter how it’s triggered (for
  instance through a manual CHECKPOINT, from a database or differential
  backup, or automatically) the same set of operations occurs:

  All dirty data file pages for the database are written to disk (all    pages that have changed in memory since they were read from disk or
  since the last checkpoint), regardless of the state of the
  transaction that made the change.
  Before a page is written to disk, all log records up to and including    the most recent log record describing a change to that
  page are    written to disk (yes, log records can be cached in memory
  too). This    guarantees recovery can work and is called write-ahead
  logging. Log    records are written to the log sequentially, and log
  records from    multiple transactions will be interspersed in the log.
  The log cannot    be selectively written to disk, so writing a dirty
  page to disk that    only has a single log record affecting it may
  mean writing many more    previous log records to disk as well.
  Log records describing the checkpoint are generated.
  The LSN of the checkpoint is recorded in the database boot page in    the dbi_checkptLSN field (see Search Engine Q&A #20: Boot pages, and
  boot page corruption).
  If in the SIMPLE recovery model, the VLFs in the log are checked to    see whether they can be marked inactive (called clearing or
  truncating the log – both of which are terrible misnomers, as nothing
  is either physically cleared or truncated).

How do checkpoints work and what gets logged by P. Randal

  I am interested to how SQL Server does recovery in case of system
  failure and how it depends on CHECKPOINT and are there any other
  factors on which DB recovery depends?

  During crash recovery, the mechanism is more complicated. The fact
  that database pages are not written to disk when a transaction commits
  means that there is no guarantee that the set of database pages on
  disk accurately reflects the set of changes described in the
  transaction log—either for committed or uncommitted transactions.
  However, there is one final piece of the puzzle that I haven't
  mentioned yet—all database pages have a field in their page header (a
  96-byte portion of the 8192-byte page that contains metadata about the
  page) that contains the LSN of the last log record that affected the
  page. This allows the recovery system to decide what to do about a
  particular log record that it must recover:

For a log record from a committed transaction where the database page

  has an LSN equal to or greater than the LSN of the log record, nothing
  needs to be done. The effect of the log record has already been
  persisted on the page on disk.

For a log record from a committed

  transaction where the database page has an LSN less than the LSN of
  the log record, the log record must be redone to ensure the
  transaction effects are persisted.

For a log record from an

  uncommitted transaction where the database page has an LSN equal to or
  greater than the LSN of the log record, the log record must be undone
  to ensure the transaction effects are not persisted.

For a log record

  from an uncommitted transaction where the database page has an LSN
  less than the LSN of the log record, nothing needs to be done.

  The
  effect of the log record was not persisted on the page on disk and as
  such does not need to be undone. Crash recovery reads through the
  transaction log and ensures that all effects of all committed
  transactions are persisted in the database, and all effects of all
  uncommitted transactions are not persisted in the database—the REDO
  and UNDO phases, respectively. Once crash recovery completes, the
  database is transactionally consistent and available for use.

Understanding Logging and Recovery in SQL Server by P.Randal

  Does recovery process depends on Recovery model as well?

No, it does not.
Database should be consistent in any recovery model after crash recovery.


Could not allocate a new page for database because of insufficient disk space

Sql express database will top out at 10g. I believe this is similar to the error message that you get.

I ran into this problem once at a client and it took me a while to figure out.

what version of sql are you running?


You shouldn't do this kind of sorting in SQL Server, you should do it in your application. Here's why:

When you need to order data that doesn't have an index that supports the required sort order, your query will do all the work at runtime with a Sort operator.

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation < 2
ORDER BY u.Reputation;

The Sort operator will ask for memory to sort all of the columns you request by the column(s) you're ordering by (this is greatly simplified, but going deeper isn't really necessary here).

If I add an index that supports the sort order (and in this case, the WHERE clause) I'm asking for, the query plan may change to use it. This isn't guaranteed, since at some point the optimizer may decide that doing a Key Lookup isn't in its best interests.

CREATE INDEX ix_yourmom ON dbo.Users (Reputation);

But in this case, I'm returning a small enough number of rows to make the Key Lookup attractive. It has six pack abs and a cool anime haircut, or something.

Of course, if I add more complexity to the ordering, the plan goes back to the original one that scans the clustered index and uses a Sort operator to give us the ordering.

SELECT *
FROM dbo.Users AS u
WHERE u.Reputation < 2
ORDER BY CAST(u.Reputation AS FLOAT) /
POWER(CAST(DATEDIFF(HOUR, u.CreationDate, '2018-05-01 00:00:00.0000000') AS FLOAT)
+ CAST(2 AS FLOAT(53)), CAST(1.8 AS FLOAT(53)));

The way you'd normally get around this is by adding a computed column:

ALTER TABLE dbo.Users ADD RankingSort_Literal AS
CAST(Reputation AS FLOAT) /
POWER(CAST(DATEDIFF(HOUR, CreationDate, '2018-05-01 00:00:00.0000000') AS FLOAT)
+ CAST(2 AS FLOAT(53)), CAST(1.8 AS FLOAT(53)))

But since you said:

  The literal '2018-05-01 00:00:00.0000000' is dependant on current
  time.

Which makes the DATETIME literal the equivalent of calling GETDATE(), which means that your computed column wouldn't be determinstic. Having a non-deterministic computed column means you can't a) index it b) persist it.

Sometimes that's okay, but not when you're trying to sort data by it. There's no benefit here.

Of course, I'm saying all this with no insight into:

The rest of the query
Current indexes
The query plan
Table definition
SQL Server hardware and settings

It could be that adding memory, or changing your query to use a temp table in a stored procedure would make sense, or simply changing your query to select fewer rows via a TOP would help. I'm not sure if you're able to make any of those changes though.

Which brings me back to my original point: You should do this sorting in the application.

Hope this helps!


Typically I do as much as I can with declarative stuff in the table definition.  Then I may use an exception block if I can't just trust my application to detect and handle standard exceptions normally.

Doing what you are trying to do in plpgsql is likely to be extremely difficult because of the limits of plpgsql expressiveness.  While we can find the attributes, generating a query to check them is not possible without resorting to temporary tables or the like.  So let's talk about temporary tables as an option.  You could:

CREATE TEMPORARY TABLE temp_tblmember OF TYPE toned.tbl_member;

Then you could insert this into that temporary table.

You could then look up the attributes in pg_attribute (joins pg_class on pg_attribute.relid = pg_class.oid).  Then you could use EXECUTE to check the attributes.

Unfortunately this isn't likely to perform very well and it is likely to be very cumbersome to code.

Fortunately you could look at using something pl/perl to do the looping, where you can get access, and then loop through the keys of the hashref.

CREATE OR REPLACE FUNCTION validate(tbl_member) returns int immutable language plperl as
$$
  my ($ref) = @_;
  for my $k (keys %$ref){
      if (!defined $ref->{$k}){
          elog(ERROR, 'tbl_member.' . $k . ' not defined!');
       }
  }
   return 1;
$$;


This works quite fast and 14 k isn't that much data
You should add a unique constraint on UserID so that you will never have to run it again

CREATE TABLE DeviceDetails   (
  `UserID` INTEGER,
  `RegisterDate` varchar(10),
  `UniqueID` VARCHAR(6)
);

INSERT INTO DeviceDetails
  (`UserID`, `RegisterDate`, `UniqueID`)
VALUES
  ('12345', '12-08-2020', 'ABC123'),
  ('12345', '11-05-2020', 'XYZ789'),
  ('23455', '20-08-2020', 'QWE345');

delete d from
DeviceDetails d
inner join (
    select max(`RegisterDate`) as maxdate, `UserID` from DeviceDetails
    group by `UserID`
    having count(*) > 1
) u on u.`UserID` = d.`UserID` and u.maxdate != d.`RegisterDate`;

SELECT * FROM DeviceDetails;

UserID | RegisterDate | UniqueID
-----: | :----------- | :-------
 12345 | 12-08-2020   | ABC123
 23455 | 20-08-2020   | QWE345

db<>fiddle here


How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?

A foreign key does not have an index by default. You have to create one.

In this case, I'd suggest either of these. It depends on relative selectivity of the 2 columns

CREATE INDEX IX_table2fk ON
              table1 (t2_col1, t1_col3)
              INCLUDE (any columns needed for the query)

OR

CREATE INDEX IX_table2fk ON
              table1 (t1_col3, t2_col1)
              INCLUDE (any columns needed for the query)

The INCLUDE will make the index covering to avoid key/bookmark lookups


You can list the running backends with

SELECT * FROM pg_stat_activity;

identify the process trying to rollback something which is working on this table.

Find its pid.

You can terminate a backend with the query SELECT pg_terminate_backend(64738), where 64738 is the pid column in your previous SELECT.

After that, you can likely DROP that table.

If even that won't work, then restart postgresql, but most likely it will.


Identify mysqldump activity in MySQL log file?

SUGGESTION #1
The quickest way to locate any dump activity is the enable the slow query log
You should find queries that resemble something like the following
SELECT /* SQL_NO_CACHE */ from ...

Any large table running a long dump has to show up in the slow log.
If all the tables are small, then lower long_query_time with
SET GLOBAL long_query_time = 0.2;

Let it run for a few hours. Then change it back to 10.
SET GLOBAL long_query_time = 10;

If you don't change it back, the slow log will grow big very quickly.
SUGGESTION #2
Another way would be to check the process list. Please see my 5-year-old post (How to detect a running mysqldump?)
In your case, just run the following query
SELECT * FROM information_schema.processlist
WHERE info like 'SELECT%'
AND   info like '%SQL_NO_CACHE%';

This query will tell you the Process ID, MySQL username, and host doing the mysqldump.
GIVE IT A TRY !!!