Specific user can't view database contents

To clear things up, language-wise: In SQL Server, the login is global for the SQL Server instance (a "server principal") and allows a client to log on to the server with very limited permissions.

Once you've set up a login, you can assign users to this login. A user is the login's security principal in a specific database ("database principal"), which means that a login can have multiple users, one for each database where he holds permissions or role memberships.

You might have added a login, but not the user, or you may not have assigned the user enough permissions in the database. To view the definition of database objects, your user will probably need VIEW DEFINITION, either on the database, the schema or on the individual object. These permissions are inherited. If you want to be able to read the data of tables, add SELECT permissions. Membership in the database role db_datareader gives the user complete read-only access to all tables and views in the database.

I've recently written a series of blog posts on SQL Server security that might give you a little more details.


Try creating a dummy database as documented in:

Creating, detaching, re-attaching, and fixing a SUSPECT database by Paul Randal

This sometimes can "trick" the server into mounting a corrupt database.


What is better? Delete and insert or update?

There's a lot more to worry about with a DELETE/INSERT methodology. From the top of my head:

Transactions will need to be manually handled. An UPDATE is a single operation that is atomic. With DELETE/INSERT it's possible to have the delete succeed but the insert fail, in which case you have no data. You need to handle this manually and the engine handles the update automagically.
Foreign keys/references. If you use any data in that table or it's referred to elsewhere, the DELETE gets a lot more complicated, as does the INSERT with linking back to the correct record from the child table if you dropped the key.
Log file growth. Depending on your settings, the statement, and the table the 2 operation method could log more to the transaction log.
Indexes. It's a lot more expensive to D/I through indexes than to update. Could also lead to fragmentation in the indexes more quickly.


Run the same query on a backup on both systems that matches production as closely as possible.  Show us the queries, execution plans, execution times, and perfmon counters.  This is the best way to find out what's happening.  I'm sure we can see what's going on.  During 1 DB migration I had a major networking issue with a firewall pegged at 100% CPU between the web servers and SQL.  End user experience was horrible, and NETWORK_IO was the highest wait stat, so maybe it's not SQL Server related.

I recently went from 2x E 4core / 48gb ram to 4x E7 8 core CPUs /256GB ram and saw some interesting results as well.

I had to tweak:
-Parallelism threshold and set maximum parallelism setting per query.  Check your
SELECT *
FROM SYS.DM_OS_WAIT_STATS
ORDER BY 3
Seeing CXPACKETS way high won't mean you have a parallelism issue, but I found it helpful to increase the threshold and CXPACKETAS fell from #1.


This looks like a failed assertion in SQL Server code.  Basically, you've uncovered a bug in SQL Server.  You could submit it to Microsoft.

A quick solution is to add option (maxdop 1) at the end of your query; this instructs SQL Server not to spread the query over multiple processors (aka parallelizing.)


perform non locking select when updating another table

I think I solved it by adding SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. Each event invocation opens a fresh session so changing the session inside an event handler seems to be safe. Here is full code snippet:

CREATE EVENT IF NOT EXISTS MY_EVENT ON SCHEDULE EVERY 1 DAY STARTS '2019-01-02 09:00:00'
    DO BEGIN
      /*  READ UNCOMMITTED should disable read locking during subquery selects */
      SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
      update A set status='INACTIVE' where status='ACTIVE' and b_id in
        (select id from B where ...);
    END;


Would re-importing reduce the DB/backup size?

Short answer:  It depends.

Long answer:

It depends on what Engine you are using.

It depends on the value of innodb_file_per_table when you created the tables.

It depends on whether you have inserted about as many rows as you have deleted.

It depends on what you mean by "changelog".

It depends on what you do to clear the disk space between the dump and the reload.

But most likely the answer is:  The disk footprint won't shrink any, or at least not much.

YMMV -- Try it.


Why is SQL Server using a clustered index scan for a self referencing FK cascade delete

It needs to validate that the row you are trying to delete is not a parent of an existing row.

You don't have an index on ParentTestId.

So it must do the scan.

CREATE NONCLUSTERED INDEX ix ON  [dbo].[Test](ParentTestId)

Then you see a seek.

BTW: The 20% estimated cost of the scan is likely to be an underestimate in this case.

The FK validation is under a left semi join and SQL Server costs it as though only a partial scan will be needed and it will find a matching row and the delete will fail.

Presumably the rows you are actually deleting will succeed more often than not and so a full scan will be required in order to validate that there are no conflicting rows.

Using trace flag 4138 to turn off row goals

DELETE FROM dbo.Test
WHERE  TestId = 200
OPTION (querytraceon 4138 )

The re-costed plan shows the CI scan at 100% rather than 20% (as it now assumes a full scan will be needed)

This difference in estimated cost is sufficient for the missing index suggestion to show up.

The costs shown in this plan are still not very representative however. You might notice that they add up to 219%.

Also the overall plan cost of the queries with and without the trace flag are both identical at 0.0168268. The full CI scan ought, in fact, to be costed at 0.152373 (0.0485075 + 0.103866)

but it seems to be capped at no more than the original plan cost (and the overall plan cost doesn't get adjusted upwards either hence incorrect percentages)


MS Access M:N Relationship query: exclude records with more than the requested values

The key is learning to write queries which directly answer the questions.  Start with getting books with only one author ("and nobody else").  This will require an aggregate query which counts the authors per book:

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) = 1

Now join the results to the other tables to get detail you desire/require.  I personally prefer saving such intermediate queries then referencing them by name in the next query (since you can then use the Access query designer to edit them individually), but to be explicit I'll include it as a subquery:

SELECT book.title, author.lastname
  ((SELECT lab.book, first(lab.author) AS author
    FROM link_author_book AS lab
    GROUP BY lab.book
    HAVING Count(lab.author) = 1)
  AS SingleAuthor
  INNER JOIN book ON SingleAuthor.book = book.book_id)
  INNER JOIN author ON SingleAuthor.author = author.auth_id

Really, you could also write a query which answers your other questions like getting records with "more than the queried values"...

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) <> 1

and then "exclude" such records:

SELECT book.title, author.lastname
  ((book
  LEFT JOIN (SELECT lab.book FROM link_author_book AS lab
  GROUP BY lab.book
  HAVING Count(lab.author) <> 1)
  AS OtherAuthors
  ON book.book_id = OtherAuthors.book)
  INNER JOIN link_author_book AS lab ON book.book_id = lab.book)
  INNER JOIN author ON author.auth_id = lab.author
WHERE OtherAuthors.book Is NULL

This will initially select all records in [books] due to the left join, but will subsequently exclude books with more than 1 author by keeping only records which are NOT in the [OtherAuthors] subquery. But as you see this is an overly complex way to get the same results.  I only included this to show that it is possible to exclude records using other types of joins and appropriate conditions.  This is a useful technique especially when there is no way of selecting the desired records directly. (Sometimes excluding records really is the best approach, but it just isn't necessary in this case with the link table.)


How do I use an OR in SQL Profiler for Column Filters

You can't. SQL Server Profiler is a bit restricted in the way it displays and filters data.

Solution

You would be better off storing the traced data into a separate table on the same server and then querying the data at given intervals. The big benefit being you have the data in the table and can perform any complex SELECT statement you wish. Ensure you don't capture too much data by specifying one or two filter criteria.

You could also consider running the SQL Server Profiler from the command prompt which will reduce the overhead of displaying the data constantly in an interface.

The parameters are:

---------------------------
SQL Server Profiler
---------------------------
Usage: PROFILER.EXE
    [/S<SQL Server instance name>]
    [/A]<Analysis Services instance name>
    [/D<database>]
    [/B<trace table to load>]
    [/E] trusted connection
    [/U<login id>]
    [/P<password>]
    [/F<trace file to load>]
    [/T<template file to load or use when starting a new trace>]
    [/L<locale id>]
    [/M<trace stop time>]
    [/O<trace output file name>]
    [/Z<maximum output file size (MB)>]
    [/R<use rollover files>]

Reference: Profiler (Microsoft Docs)

Steps

You would create a template for your standard profiling and use this in the command-line startup
Start SQL Profiler via command-line referencing your template
Query data stored in table

Alternatives

Seeing as you might be looking at a migration in the near future and because SQL Server Profiler is going out of fashion as mentioned by Microsoft, you could consider bringing your skills up-to-date by looking at Extended Events.

  IMPORTANT!!
  We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features are available in SQL Server 2016 but will be removed in a later version.+
  The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects will also be deprecated.

Reference: SQL Server Profiler (Microsoft Docs)

Extended Events

Extended Events is a light weight performance monitoring system that uses very few performance resources. Albeit with the slight drawback that there was no GUI for SQL Server 2008 (ok, there is an add-on for SSMS, which can be found on codeplex.)

An introduction into Extended Events can be found on Microsoft's site.


ORA-00922 encountered in Oracle 11gR2 installer

I found a solution on my own!

It seems that there is a bug in the Oracle installer - it doesn't like it if you start your system password with a number.

I completely uninstalled Oracle using the deinstall utility and then reinstalled Oracle, this time specifying a system password beginning with a letter. This solved the problem!

I hope this helps anyone else encountering this annoying issue.

Additionally, user HanFromBs found that the installer doesn't like special characters (- or +) at the start or end of the password, so watch out for this too.


SQL query - Do I need a full join to produce this combined result?

I don't use amazon-presto, but I'm wondering if you could adapt this SQL Server solution to your needs.

--demo setup
drop table if exists table1;
drop table if exists table2;
CREATE TABLE Table1
    (id int, time int, x int)
;

INSERT INTO Table1
    (id, time, x)
VALUES
    (1, 1, 1),
    (1, 2, 2)
;

CREATE TABLE Table2
    (id int, time int, y int, z int)
;

INSERT INTO Table2
    (id, time, y, z)
VALUES
    (1, 2, 2, 2),
    (1, 3, 3, 3)
;

------------------
--solution
;with cte as
(
select id, time from Table1
union
select id, time from Table2
)
SELECT c.id, c.TIME, t1.x, t2.y, t2.z
FROM cte c
LEFT JOIN table1 t1
    ON t1.id = c.id
        AND t1.TIME = c.TIME
LEFT JOIN table2 t2
    ON t2.id = c.id
        AND t2.TIME = c.TIME;

| id | TIME | x    | y    | z    |
|----|------|------|------|------|
| 1  | 1    | 1    | NULL | NULL |
| 1  | 2    | 2    | 2    | 2    |
| 1  | 3    | NULL | 3    | 3    |


Want to get executed query users log in SQL-server

Yes, we had a similar issue as well--for payroll data! Try the default trace as shown in this blog (it appears that this is available for Linux as well):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/80938ab2-28a4-46eb-8b09-84ab9ae22079/how-to-find-the-user-who-performed-a-truncate-statement?forum=transactsql

Linux default trace location:

http://www.dharmendrakeshari.com/main-file-directory-structure-sql-server-linux/


What is the modern way to partition PostgreSQL across machines, when the data is "naturally partitionable"

Postgres-XL is attempting to solve this as of 2014. They're aiming directly at big data on PostgreSQL, and they have developers from Stado onboard.


Inner Joins between Subsets of the Same Table

You are trying to do self join which is perfectly fine in this case. You can create another identical table and rename it, but is not necessary.
You should write something like this:

    SELECT m.employeeid AS ManId, m.empfirstname AS ManFirstName,
    m.emplastname AS ManLastName,
    s.employeeid AS StaffId, s.empfirstname AS StaffFirstName,
    s.emplastname AS StaffLastName
    FROM Employee s
    JOIN Employee m
    ON s.ReportsTo = m.employeeid


Query a table with linked objects as columns

This is not the best way of writing this Query but this should do the trick.

SELECT
    *
FROM YourTable
WHERE ((Line IN (SELECT DISTINCT
    LINE
FROM YourTable
WHERE [Route] = 12)
AND [Rout] IS NULL)
OR [Route] = 12)
OR (
Network IN (SELECT DISTINCT
    Network
FROM YourTable
WHERE [Route] = 12)
AND (LINE IS NULL
OR LINE IN (SELECT DISTINCT
    LINE
FROM YourTable
WHERE [Route] = 12)
))

You can change those where conditions to joins. As you can see, its performing 3 select statements in IN Clause (one of which is a repetition) which may affect performance. Something like:

SELECT
    Main.*
FROM Subscription as Main
LEFT OUTER JOIN Subscription as Lines ON Lines.[Route] = 12
LEFT OUTER JOIN Subscription as Networks ON Networks.[Route] = 12
WHERE
    Main.[Route] = 12
    OR (Main.Line = Lines.Line and Main.Route IS NULL)
    OR (Main.Network = Networks.Network AND (Main.Line IS NULL OR Main.Line = Lines.Line ))


Get current table name trigger was fired on

Is there any way I can obtain inside a trigger the name of the table on which the trigger is defined?

No, there is no such capability in any of the current (as of year 2020) Db2 versions, for reasons that I won't go into. For a comparison, consider why a C function cannot know its name or the name of the function that called it.
For each trigger that you write you must know the corresponding table name when you write it, simply for the fact that it is part of the trigger definition:
CREATE TRIGGER <name> ... ON <table name>

and it is trivial to also add that name, as a literal, to the INSERT statement.
If you face a tedious task of creating an inordinate amount of nearly identical triggers, use your favourite scripting language and one of the template engines available for it to define a trigger template and generate DDL statements for all your triggers from it.
Alternatively, consider using the Db2 audit facility, which is perfectly capable of doing what your triggers are meant to do.


How can I improve my table design for different types of an entity?

Sorry, but is an entrepreneur not also an individual? You should record the name etc. in individual, then the additional info in another table. Now you may have one person double. And stuff like address should be linked to "entity" - not even client, you may end up having suppliers there too.

The "Data Model Resource Book" has perfect diagrams for address and contact handling including relationships of entities with each other. There is a lot of detail issues you get seriously wrong in your approach.


PostgreSQL 9.2 - 9.6 upgrade with zero downtime

Upgrading a database will need downtime on your servers. To minimize the downtime, i would suggest you install the newer version in a new server, create a replication setup on that and dump all data from old instance here, and change server pointing so that application points to new server to connect to database.


You need to join the mentors to the students, which translates here to a self join of students and then join the districts twice, once for the students and once for the mentors. Then you can filter by the countries.
SELECT ss.student_id
       FROM students ss
            INNER JOIN students sm
                       ON sm.student_id = ss.mentor
            INNER JOIN districs ds
                       ON ds.district_id = ss.district_id
            INNER JOIN districts dm
                       ON dm.district_id = sm.district_id
       WHERE ds.name = '<the country the students should be from>'
             AND dm.name = '<the country the mentors should be from>';

Some notes:

It is recommended to use modern, explicit JOIN syntax over old style joins with a comma separated list in the FROM clause. The former are easier to write without errors, to read and understand and maintain.
Do not use double quotes for string literals. In SQL string literals are quoted using single quotes. MySQL does accept double quotes but your code will be more portable when you use single quotes. And you'll get used to it and aren't in for surprises should you write a query for another DBMS.
In this case the question was easy enough to understand without it. But next time make sure to include sample input, i.e. sample data for the tables you work with, preferably as INSERT statements. Your current result as well as the desired result is absolutely meaningless without it. Of course you should also include your desired result. But never omit sample data, if you want to help those who try to help you.


Oracle: Why is DBW terminating my Instance?

I exected startup nomount with explicit pfile paremeter and it works.

I have the init < SID >.ora in the $ORACLE_HOME/dbs/  directory, and always was working.


How to get the first two octets in an IP address Oracle SQL

I know you have an accepted answer but just for completeness here's an option without REGEX;

SELECT SUBSTR(your_field,1,INSTR(your_field,'.',1,2)-1)
FROM dual


My personal preference is to create triggers by functionality, not by DML operation. So I have one handling auditing for insert/update/delete, one doing some kind of business rules or referential integrity (again, for insert/update/delete), another doing some ugly hack to work around undesirable behavior in our client software (possibly only for insert or update), etc.

Keeping each "behavior" in one place reduces the chances of forgetting to update logic that's been duplicated across three separate insert, update, and delete triggers.

The biggest pitfall with triggers is that the INSERTED and DELETED tables are not indexed, and you can't index them directly. Joining them against large data sets performs horribly, and things get really out of hand when you've got many triggers on a table, especially when trigger cascading starts coming into play.

Most triggers I write start out by creating table variables @i and @d with the columns from INSERTED and DELETED that will be needed, giving them both a clustered primary key (since that's the only way you can index a table variable), then filling them with the data from INSERTED and DELETED.

I wrote a short (noncommercial) article about triggers, and it also covers a few other points.

http://dave.brittens.org/2012/03/29/writing-well-behaved-triggers/


Does "use AWE to allocate memory" do anything on a SQL Server 2008 R2 64bit Windows 2003 system?

For 64 bit OS, you dont need AWE and even enabling AWE using sp_configure awe enabled has no meaning.
Refer to Great SQL Server Debates: Lock Pages in Memory by Jonathan Kehayias :

In 64-bit SQL Server, the SQL Server account still requires the Lock Pages in Memory permission in order to be able to allocate locked pages, via AllocateUserPhysicalPages(), but there are a couple of big differences:
The underlying reliance on AWE-mapped memory is removed. You do not need AWE in 64-bit SQL Server; the awe enabled sp_configure option has no meaning. The continued use of the same AWE API function is purely to ensure that the allocated pages are locked.
Memory allocated via AllocateUserPhysicalPages() can be used for both the data cache and plan cache. In 64-bit SQL Server, the plan cache is no longer allocated separately (it now uses stolen pages from the buffer pool)

The memory allocations for AWE in 32bit use the AllocateUserPhysicalPages() API.  The memory allocations for lock pages in 64bit use the same API, but they aren't using AWE because the VAS in a 64-bit process is 8TB for user mode space and it doesn't require special mappings through AWE.
Queries timing out has least to do with AWE. There might be missing indexes, outdated stats, poorly configured memory settings and many other stuff that can lead to queries timing out.
What specific version of sql server are you running (along with patch level) ?


mysql crashed, after start again, the new data lost, only old records of some day ago

Do you know how to fix this?

Yes, Apply the latest backup.

If you can not do that because you do not take backups - look please for a job where you are not responsible for managing data. Backups is "professional databases for beginner admins, day 1, hour 1". Fundamental Like not driving a car totally drunk level of fundamental.

That being said, this sounds "crazy". As in - I am sure you overlook something. This is not a behavior I would expect on a crash UNLESS it runs on a VM and was rolling back to a backup or fixed start state.... corruption DOES sometimes make things bad or result in lost data, but rarely in a consistent state some days back. I would investigate whether you have a larger problem. I remember a company loosing money because the (not so smart) admin had set up vm's to roll back to a known state in case of a backup... which wiped access keys that can not be recovered to some serious funds (http://siliconangle.com/blog/2011/08/01/third-largest-bitcoin-exchange-bitomat-lost-their-wallet-over-17000-bitcoins-missing/). I would expect your problem to be something along this line. A rollback without corruption to a state some days ago is very very unlikely.


How would you implement conversation groups/ locking for multiple users of a Service Broker Queue?

You cannot use conversation groups to exclude application instances, if that's what you're trying to do. If Instance A needs to receive messages from the queue only for Instance A and Instance B needs to receive messages only for Instance B then instance A needs to use queue A and instance B needs to use queue B. Conversation group can be used only when Instance A and Instance B can both process any messages, but you want to exclude them from processing correlated messages concurrently. Every time I've seen someone trying to pre-generate conversation group IDs, the idea was always bad.

Update

The initiator cannot control the conversation group of the destination (target). Even if A and B were in the same conversation group at the initiator site, this does not mean they would be the same at the target site since conversation group is a local concept and doe snot travel with the message. If you want the messages sent by A and B to belong to the same conversation group on the target side, then the dialog has to be started in the 'reversed' order: the destination begins the dialog(s) and places them in a single conversation group, then the target(s) start sending messages on this dialog(s). So conversations act like an 'invitation' to send messages, the logical 'target' acts as the actual 'initiator'.

Update #2.

this is how it would work, in theory: say you have 10000 products. To do the 'reverse' pattern, when a user want to participate in the application it needs to 'join' in. So he starts a conversation with the server and send a message 'I want to join' (lts call this 'conversation 0'). The server processes this message by starting a conversation with this new user for each product. Now the user has 10000 conversations on which it can send 'bids'. For product A it uses conversation 1, for product B conversation 2 etc. When user B wants to join in it also starts a new conversation with the server and sends an 'I want to join' message. The server responds by starting 10000 conversation with this new user, again one for each product, and it makes sure each one is in the corresponding group for the product, so conversation for product A with user 1 is in the same group as conversation for product a for user 2.

Now obviously anyone will figure that this scheme is flawed: it requires number_of_products X number_of_user conversations, it makes adding and removing products a pain (must maintain all those user conversations!) and so on. One alternative is to multiplex products per conversation. Say the server only starts 10 conversations with each user and the user uses the conversation corresponding to the last digit of the product id (so product 1 goes to conversation 1, but so does product 11 or 101). This is more viable, it requires far fewer conversations, and requires no special conversation management when products are added or removed. You may consider that is a downside that now the server locks not all messages for product 1, but also all messages for product 11, all for 101 etc, but consider this: it only matters if you have more processing threads on the server than the number of conversations per user. If you have 5 threads, then it doesn't matter that you locked 1, 11, 101, there still are messages to process for the other 4 threads. Only if you'd have 11 threads it would matter, since the 11th thread has nothing to process.

Now I'm not advocating to deploy exactly this, I'm just pointing out some possibilities. In most cases the CG locking would be per user, not per product, and adding this extra dimension of using CG locking to avoid concurrency problems on each product is a little unorthodox.

And don't forget that the only construct that guarantees order in SSB is a conversation. So if users have to send bids for A on conversation 1  followed by bids for B on conversation 2 then there is no guarantee that the bid for A is going to be processed after the bid for B is processed. The only guarantee is that if user 1 sends two bids for A, they will be processed in the order sent.

Also, if two different users send bids for product A then there is no guarantee of the order of processing of these bids. However, if the bids for product A end up on the same CG then there is a guarantee that only one 'processor thread' will see both bid from user 1 and bid for user 2, but be careful because there is no guarantee that the bids are presented in the RECEIVE result set in the order they were received. RECEIVE only guarantees that:

all messages in the result set are from the same CG
the messages belonging to a conversation are in order

but the order of conversations in the result is basically random (is the driven by the conversationhandle ordering, a GUID).

Before I forget: remeber there is the also MOVE CONVERSATION but relying on MOVE (rather than starting conversations directly in the correct CG) is very very very deadlock prone.


T-SQL brain teaser: find non-overlapping sets

This is a sort of bin-packing problem, so you'll most likely need to choose from one of the available approximate solutions, rather than attempting an exhaustive search.

One very straightforward idea is to pack projects into groups choosing the project with the largest number of employees each time. As soon as the current project no longer fits into the current group, we start a new group.

This is relatively easy to express iteratively, though it may not perform well, depending on the characteristics of the real data set. It might be quite challenging to express the same logic in a single T-SQL statement. Anyway, as an example, here is an iterative T-SQL solution using the simple greedy algorithm outlined above:

SET NOCOUNT ON;
CREATE TABLE #Groups
(
    GroupID integer NOT NULL,
    ProjectID integer NOT NULL,

    CONSTRAINT PK_Groups
    PRIMARY KEY (GroupID, ProjectID)
);

-- First group
DECLARE @GroupID integer = 1;

-- Choose the largest project to start with
-- and assign to group 1
INSERT #Groups
SELECT TOP (1)
    @GroupID AS GroupID,
    P.ProjectId
FROM #Projects AS P
GROUP BY
    P.ProjectId
ORDER BY
    COUNT_BIG(*) DESC,
    NEWID(); -- Random order tie-breaker

WHILE 1 = 1
BEGIN
    -- Add the next largest project to the current group
    INSERT #Groups
    SELECT TOP (1)
        @GroupID AS GroupID,
        P.ProjectId
    FROM #Projects AS P
    WHERE NOT EXISTS
    (
        -- Project not already allocated to a group
        SELECT *
        FROM #Groups AS G
        WHERE G.ProjectID = P.ProjectId
    )
    AND NOT EXISTS
    (
        -- Employees for this project
        SELECT EP2.EmployeeId
        FROM #Employee_Projects AS EP2
        WHERE EP2.ProjectId = P.ProjectId

        INTERSECT

        -- Employees already in the current group of projects
        SELECT EP.EmployeeId
        FROM #Groups AS G2
        JOIN #Employee_Projects AS EP
            ON EP.ProjectId = G2.ProjectID
        WHERE G2.GroupID = @GroupID
    )
    GROUP BY P.ProjectId
    ORDER BY
        COUNT_BIG(*) DESC,
        NEWID(); -- Random order tie-breaker

    -- No project found for this group
    IF @@ROWCOUNT = 0
    BEGIN
        -- Finished when no more projects left to process
        IF NOT EXISTS
        (
            SELECT P.ProjectId FROM #Projects AS P
            EXCEPT
            SELECT G.ProjectID FROM #Groups AS G
        )
        BEGIN
            BREAK;
        END;

        -- Next group number
        SET @GroupID += 1;
    END;
END;

SELECT
    G.GroupID,
    G.ProjectID
FROM #Groups AS G;

This code is non-deterministic because any ties encountered (when sorting by the number of employees per project descending) are broken randomly using NEWID. Nevertheless, a typical output is:

╔═════════╦═══════════╗
║ GroupID ║ ProjectID ║
╠═════════╬═══════════╣
║       1 ║         3 ║
║       1 ║         4 ║
║       2 ║         2 ║
║       2 ║         6 ║
║       3 ║         5 ║
║       3 ║         7 ║
║       4 ║         1 ║
╚═════════╩═══════════╝


Constrain input to a few different strings

Change your constraint to

CHECK (type IN ('email','post','IRL','minutes'))

This will be converted by the parser into:

CHECK (type = ANY( ARRAY['email','post','IRL','minutes']))

That should do what you are looking at.

However I have to wonder if it wouldn't be better to do this:

CREATE TABLE comlog_types (
     type text
);

And then add a foreign key to enforce the constraint.  This would make it easier to add types in the future.


Group Data and show group as separte column

You want to use a PIVOT and ROW_NUMBER function in order to accomplish your objective.  See the example below.

The Setup

DECLARE @Seats AS TABLE
    (
    RollNo VARCHAR(10) NOT NULL
    , Seat CHAR(3) NOT NULL
    );

INSERT INTO @Seats
        ( RollNo, Seat )
VALUES  ('A1234','GN')
        ,('A1233','GN')
        ,('A1235','GNF')
        ,('A1238','GN')
        ,('A1233','GNF')
        ,('A1231','GN')
        ,('A1232','GNP');

The Query

SELECT GN, GNF, GNP
FROM (
    SELECT RollNo
        , Seat
        , ROW_NUMBER() OVER (PARTITION BY Seat ORDER BY RollNo) AS GroupID
    FROM @Seats) AS T PIVOT (MAX(RollNo) FOR Seat IN (GN, GNF, GNP)
    ) AS pt


PostgreSQL: How does the implicit index work when using "COPY" for loading bulk rows into a newly created table?

will the rows be indexed during "COPY" since the table has an implicit index?

Yes.

  if the answer is yes, the "COPY" will slow down?

Yes.

You can LOCK TABLE ... IN ACCESS EXCLUSIVE MODE then drop the PRIMARY KEY constraint for the duration if you need to.

PostgreSQL doesn't have a way to mark indexes as invalid then revalidate them for use without dropping and recreating them (yet).


Trying to setup Postgresql 9.5 with different data directory

The command I typed:

sed -i 's#.*data_directory = .*#data_directory = "/blah/data"#' /etc/postgresql/9.5/main/postgresql.conf

should be:

sed -i "s#.*data_directory = .*#data_directory = '/blah/data'#" /etc/postgresql/9.5/main/postgresql.conf

The quotes around the directory path were the wrong sort. Can you believe it!


Is it better to store calculated values or recalculate them on request?

It depends.

Storing derived values can speed up processing of later read requests as the values do not need to be computed again.

There is one key problem with denormalising your data in this way though: you have to ensure that it is not possible for the derived value to become out-of-date undetected. If your data is not in a sufficiently normal form inconsistencies can creep in and cause trouble. This means that everywhere that updates the data the value is derived from must also refresh the derived value or somehow mark it as needing to be updated before being relied upon. Updating the derived values via triggers may mitigate having to add this logic to many places in your code, but be aware that triggers have their own set of potential gotchas (unexpected locking issues, code in the business logic layer not expecting values that it doesn't explicitly reference to update, triggers can be disabled, and so on). Another solution is to discourage direct access to the tables at all and have all updates via an API based on stored procedures - those procedures can know to recompute the calculated values as needed and obviously can't be disabled.


Sounds like my Cassandra Cluster Admin is exactly what you want! Take a look at it here: https://github.com/sebgiroux/Cassandra-Cluster-Admin


AWS Aurora Mysql different instance types for writer/reader

All the writes to a Primary are sent to each Replica and then applied there.  For this reason, the Replica should (in general) be at least as beefy as the Primary.
Often, you need to do reads on the Primary in support of what you then write.

Hence, the characteristics of the Primaries and Replicas are similar.
If the database is sort of "write once and read many times", then you might want the Replicas to be beefier in general, but still a "database" type, as categorized by the cloud provider.
You may find that db.r5.large is overkill for your Primary, especially if you move a lot of the read traffic off to at least 2 Replicas.  (Based on my first bullet item, having only one Replica is not very helpful.)
If you want a deeper review of your application, provide RAM size, SHOW GLOBAL STATUS and SHOW VARIABLES.


SQL Server: cold backup, shutting down services?

Never heard such rubbish.

FULL or BULK LOGGED mode means you require log backups. SIMPLE means you don't.
You always require full backups.

Copying the MDF and LDF files isn't a backup and can potentially lose** you data if you are in a transaction as you shut SQL Server down.

If the mdf files are missing it means they aren't there because of finger trouble or this "backup" method. You can't lose mdfs unless someone removes the files or removes a drive when SQL Server is shut down

** Edit:

By losing data I mean "open transactions won't be committed". Typically, client code won't handle a retry and, say, log the error but not the data to be written.

Any data in committed transactions, no matter how old or new, will be safe


simple question about group by and custom groups

Directly using GROUP BY, no. You can still get the results you want, though, and there are many ways to accomplish it. Such as UNION:

SELECT 'red', COUNT(*) FROM color_table WHERE color_name='red'
UNION
SELECT 'colors different than red', COUNT(*) FROM color_table WHERE color_name <> 'red'


Mariadb clustering Index design question

First, to clarify: "cluster" in this Question refers to the "clustering" of the PRIMARY KEY with the data, not the "clustering of servers".

You have provided the 3 choices that InnoDB makes to picks the clustered PK.  Let me rephrase it from a user point of view.

If you have a column, or combination of columns, (all non-NULL) that are inherently UNIQUE, then use it as the PK.  This is a "natural" PK.
If choice 1 is not available, use an AUTO_INCREMENT.
Though available, do not use the third choice of an auto-generated hidden PK.  Some day, you will need to uniquely identify rows, perhaps for maintenance.

Unlike some other vendors, there is no ROWNUM, nor is there a non-clustered PK.  The lack of those is not important.

Secondary keys let you get at the data in other ways.  A secondary key contains a copy of the PK so that it can get to the data.  Both PK and secondary keys are organized as BTrees.  BTree is the best all-around indexing method; the lack of Hash, etc, is not important.

A natural PK lets you do a range scan over the PK column very efficiently.

If find that two-thirds of tables have a "natural" PK; only one-third need an auto_increment instead.

Back to your 'task'.  It sounds like you want

PRIMARY KEY(org_id, task_id)

but would like task_id to be automatically generated.  This is possible by saying

task_id, INT UNSIGNED AUTO_INCREMENT NOT NULL

but there will be gaps -- since the auto_inc is table-wide, not specific to a given org_id.  If you can live with the gaps, then that is your answer.

This was a feature that existed in ENGINE=MyISAM.  See this for a simulation of it using InnoDB.

You mentioned "ported nationally".  Are you trying to create unique ids that are locally, not centrally, generated?  See UUID and its evils.

If you are summarizing large quantities of data, then build and maintain Summary tables.

If you want to discuss further, please provide prototypes of schema and queries.


PLSQL using cursors or functions to query repeated data

This is a 4th option, using out parameters?

create or replace function getLicenseStatus
    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)
    as
begin
    SELECT status, status_dt
       into into l_status, l_status_dt
    from tbl_license
    where licence_no=in_license_no;
end;


Postgres Logical Replication - "replication slot "mysub" was not created in this database"

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testha host=192.168.150.210 user=postgres password=postgres port=5432' PUBLICATION mypub;

What was the output of this command?

You should have gotten something like either:

NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION

Or

ERROR:  could not create replication slot "mysub": ERROR:  replication slot "mysub" already exists

It sounds like what happened here is that at the time you created the subscription, the publisher was running as expected.  But then you did something weird, like disconnect the subscriber, rename the database "testha" on the publisher to something else, make a new "testha" and try to reconnect the subscriber to that.


AlwaysOn Geo Dispersed Automatic Failover

There is a max of 8 secondary replicas for AlwaysOn in SQL 2016, I do not believe there is a change for that in 2017

  Always On Availability Groups on 2016 supports up to eight secondary replicas including one primary replica and two synchronous-commit secondary replicas (no Enhancement on this compared to SQL 2014). Also use SQL Server 2016 Always On Availability Groups to provide high availability for SQL Server databases hosted in Azure, etc. Source

In any case adding a local secondary would not add value from a SQL failover scenario.  Only the instance with votes for quorum can be primary, of these there can only be three potential primaries

  Another enhancement is an increase in the number of Automatic Failover partners which means you can have two more failover partners apart from the primary. In a three node scenario, if one node fails still we can have high availability with other two nodes. Source

Automatic failover requires synchronous-commit, Spreading synchronous-commit over geographic distance can have an impact on performance.

If you have a 3 node cluster (file share and other quorum considerations excepted ...), any node that can not talk to the at least one of the other two will be down. If all three loose connection, there will be no auto failover (all three will be down). If any two can talk, one of those two will be primary.

Any of the replicas that can communicate with the current primary can have asynchronous copies that are current.   So if corporate goes down, one of your two stores will be primary, and you could have several stores with asynchronous read only copies.

Given your scenario you probably want the primary at corporate, and your two synchronous-commit automatic failover partners at the stores that provide the fastest and most dependable connections to corporate.


Challenge. Split database 500 records in two smaller datasets, randomly

Easiest solution would be to store the datasets into tables.
After you use your query to get the first dataset:

 INSERT INTO dataset1 SELECT * FROM foo ORDER BY rand() LIMIT 125

then do:

 INSERT INTO dataset2 SELECT foo.* FROM foo
   LEFT JOIN dataset1 d1 ON foo.key=d1.key WHERE d1.key IS NULL


SQL Server 2017 I can't make a procedure

First off, the word "GO" is the default batch separator. That tells SQL Server, "Alright, I'm done with this command, and go execute it."

So when you run this:

Create Procedure RecoveryScript
as
    Alter Database SDE_SQL
    SET Single_User with
    Rollback IMMEDIATE

    use master
    go

    Restore Database SDE_SQL
    FROM DISK = 'C:\Users\HF\Desktop\SQL\SQLDatabaseBackup.bak'
    WITH REPLACE, STATS = 5

What you're really doing is this:

Create Procedure RecoveryScript
as
    Alter Database SDE_SQL
    SET Single_User with
    Rollback IMMEDIATE

    use master
    go

SQL Server does that, and then thinks you're done, and starts this in a separate command:

Restore Database SDE_SQL
FROM DISK = 'C:\Users\HF\Desktop\SQL\SQLDatabaseBackup.bak'
WITH REPLACE, STATS = 5

If you want to create a stored procedure to restore a database, put that stored procedure in master itself (or in another database, but just not the one that's about to get restored, since you can't kill a connection while still running a query from it.) You won't need any "use database" commands in there at all.

Finally, as long as we're talking about restoring databases, check out sp_DatabaseRestore in the open source First Responder Kit. If you're using Ola Hallengren's excellent maintenance scripts to take your backups, you can just point sp_DatabaseRestore at your backup folder, and it'll automatically restore the newest available backups of your database. (Disclaimer: I'm one of the contributors on the First Responder Kit repo.)


Tuning queries and indexes using index analysis tool

Sybase 3rd party tools are sparse unless you spend money it seems.

The usual vendor suspects have Sybase offerings:

Embarcadero's DB PowerStudio for Sybase ASE which claims to have index analysis (+ lots of other stuff)
Quest have some tools but not sure what you would be best


Migrating data in SQL Server from source to target with schema change at target

Did you

Copied all the data to SQL2008
Updated the schema on SQL2008 according to the upgrade script?

Copy data shouln't be the problem here, schema compare neither ;)

For the scheme compare from sql 2005 to 2008 you may use the free Open DbDiff from http://opendbiff.codeplex.com and apply the resulting script to sql 2008.

Otherwise use the new version of the SQL Server Data Tools http://msdn.microsoft.com/en-us/data/tools.aspx that allows SQL 2005 to 2008/2012/Azure migration and scheme update out of Visual Studio 2010+


What do I make of unlinked tables in the same database?

No, there is no requirement for tables to be related to one-another.  The fact that you do have two tables separate from the other thirteen would be a code smell and you should be sure that you have not forced two different business objectives into a single database.  If the two really do support genuine use cases for the others then there's no problem.

A typical example are "staging" tables.  Here data from an external source is loaded into a stand-alone table.  Then that data is validated.  Only then is it copied across to inter-related tables.  Another is to hold system configuration or user options.  These have no foreign keys to the "business" data but support the operation of the system which uses that business data.

Lastly I would point out that the logical and physical designs can be different.  The data model (logical) may document foreign keys but the data base (physical) does not declare foreign key constraints for one reason or another.  This would be a very specific choice for particular circumstances and relatively rare, however.


How to apply a patch in PostgreSQL on Windows?

This answer applies to modifying the PostgreSQL source code by applying a "diff" or "patch". It's not about installing minor version updates; to do that, just download and run the installer.

To alter the PostgreSQL server its self or its procedural language runtimes, you will generally need to recompile PostgreSQL from scratch.

On Windows that's a bit of a rough job. The postgresql documentation covers the process, but it doesn't really deal with the dependencies etc.

I've written some tools and documentation to make it easier, but they don't yet compile all the dependencies like OpenSSL, so the resulting binaries aren't as feature-complete as the stock installer. They're also not guaranteed to be binary-compatible with extensions.

If you just need to patch an extension that's a little bit easier, though far from trivial. I spent some time a while ago figuring out how to build PostgreSQL extensions on Windows without recompiling all of PostgreSQL. A similar approach might work for recompiling things like pg_dump and pg_restore but I have not tested this.

Add-ons, clients and utilities like PostGIS, PgRouting, PgAdmin-III, psqlODBC, pgJDBC, psycopg2, the Ruby Pg gem, DBD::Pg, PgPool, etc etc etc are all compiled separately using different procedures. You'll want to look up the tool in question for details.

Frankly, if you're doing any kind of development work on PostgreSQL, I don't recommend Windows as a platform.


Select a table with multiple columns according to IN clause

If you don't need a column for each answer you can use group_concat() to build a single string with all answers per user.

drop table if exists survey_answer;
create table if not exists survey_answer(user int, question_id int, answer varchar(200));
insert into survey_answer values
(1,60,'answer 1'),
(1,61,'answer 2'),
(1,62,'answer 3'),
(2,61,'answer 4'),
(2,52,'answer 5'),
(3,40,'answer 6'),
(3,62,'answer 7'),
(4,61,'answer 8'),
(4,62,'answer 9'),
(4,10,'answer 10'),
(4,32,'answer 11');

select distinct(t1.user),
       (select group_concat(t2.answer, ',')
        from survey_answer t2
        where t2.user = t1.user) answers
from survey_answer t1
;

drop table if exists survey_answer;

This is the result:

| user | answers                                   |
|------|-------------------------------------------|
| 1    | answer 1,,answer 2,,answer 3,             |
| 2    | answer 4,,answer 5,                       |
| 3    | answer 6,,answer 7,                       |
| 4    | answer 8,,answer 9,,answer 10,,answer 11, |

Rextester here


Find all unique columns that are allowed null

Try this one:

SELECT ind.OWNER AS INDEX_OWNER, ind.INDEX_NAME,
    tab.OWNER AS TABLE_OWNER, tab.table_name,
    tab.column_Name,
    UNIQUENESS, NULLABLE
FROM ALL_INDEXES ind
    JOIN ALL_IND_COLUMNS col
        ON ind.OWNER = INDEX_OWNER
            AND ind.INDEX_NAME = col.INDEX_NAME
    JOIN ALL_TAB_COLUMNS tab
        ON tab.owner = col.table_owner
            AND tab.table_name = col.table_name
            AND tab.column_Name = col.column_Name
WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE';


Multi-column and single-column index on the same column

Get rid of OR

(s.deleted = 0 OR s.deleted IS NULL)

Make up your mind.  Either use 0 (and make it NOT NULL) or use NULL.  It is probably better to have

TINYINT UNSIGNED NOT NULL DEFAULT '0'

Until you get rid of the OR, an index including deleted is not useful.

Composite index

After that, INDEX(deleted, store_id), in either order, becomes optimal.

As a rule, INDEX(a) is unnecessary if you also have INDEX(a,b).

Even better is

INDEX(deleted, store_id,   -- in either order
      created_at)          -- last

This will avoid the sorting that is currently necessary.  (See EXPLAIN SELECT ...)

I can't remove the single-column index.

Do you mean that your fingers don't work?  Your boss will slap your hand if you try?  You don't have permissions?  It comes back when you do remove it?  The FOREIGN KEY gets in the way?  What?

Cardinality

Fake news.

Within a composite index, cardinality is irrelevant.

When comparing two indexes, cardinality is important, but there are usually better ways to optimize, such as using a composite index.  So, again, fake news.

Back to the question

Your experiments showed that either INDEX(store_id) or INDEX(store_id, deleted) would be used.  Given a choice between the two of them, the Optimizer will pick the smaller one (on the presumption that the amount of effort is slightly less).  Without a choice (ie, when you had deleted the smaller one), the Optimizer happily picked whichever one is left.

So, to avoid wasting disk space, DROP the shorter (single-column) INDEX.  Meanwhile this query will continue to work efficiently.

"Why isn't store_deleted_idx used in the first case?" -- Because of the OR.

More on indexing

http://mysql.rjweb.org/doc.php/index_cookbook_mysql discusses how getting all the way through the WHERE (after getting rid of OR) lets the index get to the ORDER BY, and if you get through the ORDER BY (by tacking on created_by), you can get to LIMIT.  Then it will read only 10 rows, not the whole table!


Postgres : Multiply length of array to count aggregate result

sum was an obvious choice :

select sum(array_length(students,1)) from rooms;


Your first resultset comes from

... WHERE updated_at >= ...
      AND updated_at <  ...

Your second resultset comes from adding this on

... GROUP BY DATE(updated_at)

The third ("weekly") gets trickier:

... GROUP BY FLOOR(TO_DAYS(updated_at) - 2) / 7

(Where the "-2" is adjusted to make a "week" start on Sunday or Monday or whatever you prefer.)

Yearly:

... GROUP BY YEAR(updated_at)

Monthly:

... GROUP BY LEFT(updated_at, 7)

(because the LEFT will give you something like the string "2011-03").


What should be checked after an Oracle migration

Are you trying to migrate the data between different versions of Oracle?

Generally, you can also look at your environmental parameter settings (init.ora parms) and your optimizer statistics after doing data migration.
See also Oracle tips for migration click here


Error installing SQL Server 2008 - all componets installed successfully except database engine

Make sure the account doing the installation is a member of the following groups in local security policy:

Adjust Memory quotas for a process
Bypass Traverse checking
Log on as a batch job
Log on as a service
Replace a process level token
Backup files and directories
Debug programs
Manage auditing and security log

Additional Items to check:
Make sure the account doing the install is a local admin on the server, make sure you are not installing to a compressed drive or folder, make sure you are installing the right version, IE 32 or 64 bit based on your OS


Backup is getting failed with following error

The error message "Not enough server storage is available to process this command" is a Windows error message that can be extremely misleading.  The error message indicates there is not enough RAM available to complete the operation.

It is highly likely that Windows is very low on free memory - check the SQL Server "max server memory" setting to ensure there is a reasonable amount of memory left for the operating system.

This command shows how much memory SQL Server is configured to use:

EXEC sys.sp_configure 'max server memory (MB)';

See What is a deterministic method for evaluating a sensible buffer pool size? for details about what this value should be.

If you see this error, open Windows Task Manager and check how much free memory Windows has.


sp_executesql changes non standard characters to?

You should pass your unicode characters within SELECT as UNICODE using N:

DECLARE @text nvarchar(30) = N'select N''J こんにちは C 你好'''
SELECT @text
SELECT N'J こんにちは C 你好'
exec sp_executesql @text
exec sp_executesql  N'select N''J こんにちは C 你好'''


Alter your JSON column to be jsonb and try a gin index on the data field like:

create index ginner on Visitor using gin(data);

They can be slow to create and big but will allow arbitrary queries. Here's a demonstration:

https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/


Incorrect query results for execution plan, unsure of exact cause of problem

I don't believe that result was incorrect at all, your query is ambiguous. Semantically your subquery enumerates all rows and then you pick the top 25 rows without specifying an order by. That means that SQL server is at liberty to return any rows it feels like and those rows do not have to be the rows enumerated 1 to 25. It can be just any random rows and in your case four of the rows was enumerated in the range 1 to 25. The rest was filtered out in the main query.

You can fix the query by removing the top statement in the subquery.

  In your opinion based on the query what would be the best course of
  action?

I would test all the options you have and pick the one that performs the best.

I will give you one more option to test that assumes ID is the primary key column in TableC and that AddedDate is not the clustered key.

Add an index on AddedDate including the columns you need to filter out rows before the enumeration happens.

create index IX_TableC_AddedDate on TableC(AddedDate)
  include(AccountID, Processed, Deleted, Source)

Rewrite your sub query so that the only returns ID and row and then join back to TableC in the main query to fetch the rest of the columns.

SELECT E.Row,
       C.*, -- don't use * here, include the columns you actually need
       (
       SELECT COUNT(*)
       FROM TableA as A
         LEFT OUTER JOIN TableB AS B
            ON A.GRRecID = B.GRRecID
       WHERE A.AccountID = @P1 AND
             A.Email = C.Email AND
             A.Deleted = @P2
       ) AS Matches
FROM (
     SELECT ROW_NUMBER() OVER (ORDER BY C.AddedDate DESC) AS Row,
            C.ID
     FROM TableC as C
     WHERE AccountID = @P3 AND
           Processed = @P4 AND
           Deleted = 0 AND
           Source = @P5
     ) AS E
  INNER JOIN TableC as C -- Join back to TableC
    ON E.ID = C.ID
WHERE E.Row >= 1 AND E.Row <= 25
ORDER BY E.Row

There might be an even more efficient index you can try but the datatypes for your parameters are somewhat confusing. Is AccountID really a varchar(8000)? If not you should change the data type to match that of the column and in that case a better index to use would be to lead of with AccountID.

create index IX_TableC_AccountID_AddedDate on TableC(AccountID, AddedDate)
  include(Processed, Deleted, Source)

The same goes for Source depending on what datatype it is. If the datatype appropriate you could add that one as well before AddedDate in the columns list of the index.

You could even use all the columns from the where clause in the index if like to avoid any residual predicate.

create index IX_TableC_ on TableC(AccountID, Source, Processed, Deleted, AddedDate)

Just make sure AddedDate is the last column in the index and that you don't have any columns before AddedDate that is not used in the where clause.

A blog post by Itzik Ben-Gan on the same lookup problem (for offset-fetch):

Avoid Unnecessary Lookups when Using ROW_NUMBER for Paging


A question about foreign keys and many to many relationships

Given that a school can have many students, and a student can attend many schools

.. you should have a many-to-many relationship between school and student - implemented as separate table. (school_id is misplaced in the table student.) I'll simply name it school_student:

CREATE TABLE school_student (
   school_student_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
 , school_id  int NOT NULL REFERENCES school
 , student_id int NOT NULL REFERENCES student
 , ...
);

And then use school_student_id in tables behavior and attendance.
Alternatively, you can use a multicolumn PK on (school_id, student_id) and carry over (school_id, student_id) as FK - effectively implementing what you had in mind - with standard SQL. Like:

CREATE TABLE school_student (
 , school_id  int NOT NULL REFERENCES school
 , student_id int NOT NULL REFERENCES student
 , ...
 , PRIMARY KEY (school_id, student_id)
);

You don't need that for grade, since that references a course bound to a school already. But you might implement something to enforce integrity so that students can only attend courses at schools they are enrolled at. If you use (school_id, student_id) as FK, that can be enforced automatically - using  a single copy of school_id for both FKs in table grade.

Related:

Enforcing constraints "two tables away"


pg_stat_monitor: see placeholders instead of actual query

While the docs do say that the default is 0, the default is clearly not 0.  Looks like a bug.  After you set it to really be 0, did you execute the reset so you wouldn't be looking at old data?
But the entire pgsm_normalized_query=0 feature seems misbegotten.  With it, the query is still normalized, it just that the text reported is the parameterized text of an arbitrary (the first?) execution of that normalized query in that bucket, but all the stats seem to have been aggregated across the normalized query even though the text reported is not normalized.
Also, it seems pretty flaky even within those constraints:
select bucket, calls, query  from pg_stat_monitor where query like 'UPDATE pgbench_accounts%' order by total_time desc;
 bucket | calls  |                                   query
--------+--------+----------------------------------------------------------------------------
      3 | 95,109 | UPDATE pgbench_accounts SET abalance = abalance + 3008 WHERE aid =
      4 | 92,216 | UPDATE pgbench_accounts SET abalance = abalance + 3186 WHERE aid = 421795
      8 | 93,381 | UPDATE pgbench_accounts SET abalance = abalance + 1998 WHERE aid =
      5 | 90,735 | UPDATE pgbench_accounts SET abalance = abalance + -2438 WHERE aid = 574540
      9 | 81,484 | UPDATE pgbench_accounts SET abalance = abalance + 3594 WHERE aid =
      6 | 86,158 | UPDATE pgbench_accounts SET abalance = abalance + -41 WHERE aid = 1
      0 | 90,503 | UPDATE pgbench_accounts SET abalance = abalance + -2664 WHERE aid =
      7 | 93,446 | UPDATE pgbench_accounts SET abalance = abalance + -3997 WHERE aid =
      1 | 55,960 | UPDATE pgbench_accounts SET abalance = abalance + 4804 WHERE aid =

Why is the final parameter sometimes there and sometimes not?
Overall, I would take the caution on that page "pg_stat_monitor is beta software and currently unsupported." to heart.


Where can I get a SQL Server Trace Dataset?

Pattern analysis would be entirely dependent on workload.

Every workload is different. Not just differences between transactional (OLTP) and analytics (OLAP), but even a dozen OLTP applications will produce entirely different patterns.

If you want to build a synthetic one, capture a trace while benchmark tests run. You can start with Benchmark Factory or HammerDB as mentioned in this Stack answer.


Optimising MySQL query with lots of self-joins

How to fix it in MySQL

As can be seen from line 3 of the EXPLAIN statement, a [edit:]covering index is not being used here. It needs to join on the entity_id and the type and no key is available for that.

ALTER TABLE entity_relationship ADD KEY e_t_r
   (entity_id, type, relationship_id);

This makes the key available, but MySQL chooses not to use it. It can be forced with: USE INDEX (e_t_r) :

SELECT lt.entity_id entity_id,
       py.relationship_id relationship_id,
       'implied-constituent' `type`
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt USE INDEX (e_t_r),
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id
  AND pt.type='constituent'
  AND pt.relationship_id = py.relationship_id
  AND py.type='constituency';

This now runs in 0.8s. (compare to 17.7s without forcing it to use that index.) This is with MySQL 5.1.63.

How to fix it in MariaDb

Well, you don't have to!

MariaDb executes the query very fast with or without the USE INDEX intervention (0.25s, but it's on a different host so I would expect this to be nearer the 0.8s in the optimised version above).

Also, MariaDb does not use the new index and is quite happy with the t_e_r index, including using it as a covering index (i.e. uses the index for the entire data fetch).

I'm getting more and more impressed with MariaDb and now considering switching.


MySQL Query Optimization : Bets and Averages

SELECT A.*
FROM    jos_bet_1x3_best A
   INNER JOIN
      (SELECT bet_id,
              q1,
              AVG(`q1`) AS betavg,
              COUNT(bet_id) AS nr,
              MAX(q1) AS qmax,
              (MAX(q1) - AVG(`q1`)) AS diff
         FROM jos_bet_1x3_best
       GROUP BY `bet_id`
       HAVING (q1 >= betavg AND nr > 2 AND diff < .5 AND q1 < 3)
        LIMIT 0, 30) AS tb1
   USING (bet_id)
GROUP BY `bet_id`;

Please try this.


There are a couple of approaches you can take.  The better one is to grant VIEW SERVER STATE.  This gives you access to the DMOs that give you information about the server.

The other version (which I don't recommend) is to grant CONTROL SERVER.  This permission is very similar to SYSADMIN but you can do DENYs.  So you could DENY CONNECT on each of the user databases.  The down side here is that you are giving someone (for example) the ability to stop & start the SQL Services.

Unless you have a VERY good reason to go beyond VIEW SERVER STATE just go with that one.

You can also look at the list of server level roles and see what matches your needs.  Just remember that someone with securityadmin for example could create a login with sufficient permissions to affect the databases.


How triggers are called when we have two triggers?

If I am understanding your question correctly, you are looking for the sp_settriggerorder system stored procedure (http://msdn.microsoft.com/en-us/library/ms186762.aspx). Through the used of this stored proc you can set whether a trigger fires first or last (or undefined, and any trigger in between first and last has no guaranteed order).

(Note: this only applies to AFTER triggers, and cannot be used with INSTEAD OF triggers)


Are you familiar with the concept of a Key/Value Pair? Presuming you're familiar with Java or C# this is in the language as a map/hash/datatable/KeyValuePair (the last is in the case of C#)

The way it works is demonstrated in this little sample chart:

Color        Red
Age          18
Size         Large
Name         Smith
Title        The Brown Dog

Where you have a key (left) and a value (right) ... notice it can be a string, int, or the like. Most KVP objects allow you to store any object on the right, because it's just a value.

Since you'll always have a unique key for a particular object that you want to return, you can just query the database for that unique key and get the results back from whichever node has the object (this is why it's good for distributed systems, since there's other things involved like polling for the first n nodes to return a value that match other nodes returns).

Now my example above is very simple, so here's a slightly better version of the KVP

user1923_color    Red
user1923_age      18
user3371_color    Blue
user4344_color    Brackish
user1923_height   6' 0"
user3371_age      34

So as you can see the simple key generation is to put "user" the userunique number, an underscore and the object. Again, this is a simple variation, but I think we begin to understand that so long as we can define the part on the left and have it be consistently formatted, that we can pull out the value.

Notice that there's no restriction on the key value (ok, there can be some limitations, such as text-only) or on the value property (there may be a size restriction) but so far I've not had really complex systems. Let's try and go a little further:

app_setting_width      450
user1923_color         Red
user1923_age           18
user3371_color         Blue
user4344_color         Brackish
user1923_height        6' 0"
user3371_age           34
error_msg_457          There is no file %1 here
error_message_1        There is no user with %1 name
1923_name              Jim
user1923_name          Jim Smith
user1923_lname         Smith
Application_Installed  true
log_errors             1
install_path           C:\Windows\System32\Restricted
ServerName             localhost
test                   test
test1                  test
test123                Brackish
devonly
wonderwoman
value                  key

You get the idea...  all those would be stored in one massive "table" on the distributed nodes (there's math behind it all) and you would just ask the distributed system for the value you need by name.

At the very least, that's my understanding of how it all works. I may have a few things wrong, but that's the basics.

obligatory wikipedia link http://en.wikipedia.org/wiki/Associative_array


ORA-15081: failed to submit an I/O operation to a disk

If your are using multi-path make sure that your O.S. does not point to the file system instead of the disk groups.
  it happened to me 2 times after box reboot.

This is to answer your question:

SQL> set pagesize 800
SQL> select name, label, path from v$asm_disk;
NAME                           LABEL
------------------------------ -------------------------------
PATH

DGER_0000    /dev/oracleasm/disks/DSK5

DGSTBY_0000 /dev/oracleasm/disks/DSKPATHY

run the follwing command :

 /etc/init.d/oracleasm querydisk -d <your disk here>

post the output !


SQL server 2019 adaptive join poor performance

The fast plan features a row goal.  This ends up favoring nested loops joins, which deliver 100 rows to the Top operator fairly quickly, satisfying the query.
The slow plan also has a row goal, but really only on the adaptive join operator.  In the case the adaptive join needs to run as a hash join, all the results from the upper input must be consumed (the "build" step of the hash join).  See Forrest McDaniel's blog for a great visualization of how this works: The Three Physical Joins, Visualized

Why Adaptive join caused the query to slow down?

The adaptive join does in fact run as a hash join, since it exceeds the threshold of 88 rows (by quite a lot).  This leads to the query having to read every row from dbo.APP, joining all the matches from dbo.PRS - around 30 GB of reads, according to the execution plan.

How does changing the join order change the execution plan?

The optimizer has the ability to reorder joins in order to filter down a resultset earlier and more efficiently, as long as the query will still produce correct results.  But it doesn't do this very much in the face of a mix of OUTER and INNER joins.  See this Q&A for details on that: Inner join elimination inhibited by prior outer join
When you manually rewrote the join order, it allowed for a plan where the join to dbo.EDUBranch came before the join to dbo.Country - which doesn't have an adaptive join, utilizes the row goal mentioned above, and turns out much better (as you noticed).


How locks in Postgres behave when quitting the JVM abnormally

The answer depends on a few things and my Java knowledge isn't up-to-date regarding session handling.

However, the lock isn't held by the database, the lock is held by a session.

This means if the session no longer exists, then the lock is removed from the database object (table, row, ...).

If you were to run the Java code on a different server/computer, then you might observe a delay before the second message is displayed. This will be because the JDBC connection could be using different parameters.

socketTimeout

  The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems. The timeout is specified in seconds and a value of zero means that it is disabled.

Answering Your Question

  I need some insights on row locking during these kind of scenario. Does it depends on transaction isolation level?

Session terminated = Lock removed

If you remove the for update part, then the second statement would be able to read the data at the same time. As soon as you are expecting to update something, then you will have a ROW EXCLUSIVE lock.

  The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

and

  Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

Reference: 13.3. Explicit Locking (PostgreSQL 9.4 Documentation)

Further Reading/Reference Material

PostgreSQL Concurrency Model

  PostgreSQL provides a rich set of tools for developers to manage concurrent access to data. Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session. MVCC, by eschewing the locking methodologies of traditional database systems, minimizes lock contention in order to allow for reasonable performance in multiuser environments.

Reference: Chapter 13. Concurrency Control - 13.1. Introduction (PostgreSQL 9.4 Documentation)

PostgreSQL JDBC Driver

  PostgreSQL™ provides a type 4 JDBC driver. Type 4 indicates that the driver is written in Pure Java, and communicates in the database system's own network protocol. Because of this, the driver is platform independent; once compiled, the driver can be used on any system.

Reference: The PostgreSQL JDBC Interface- Chapter 1. Introduction (PostgreSQL JDBC Driver Documentation)


Pg_restore getting stuck for a particular server

I discussed with my network guys and they said that the server for which restore is working, is accessed using private IP through VPN. Another server is accessed using public IP without any VPN.  There is Difference in latency between public and internal IP addresses.

When I copied the backup file on the same server where Postgres server is hosted and tried restoring the database, it worked within seconds.


Database Tuning Advisor CLI "dta" missing

DTA used to be a part of SQL Server, but it moved to SSME.  During the upgrade from SQL Server 2014 to SQL server 2017, two things happened:

SQL Server 2014 DTA.EXE was left behind
SSMS 18 did not add the directory containing DTA.EXE to the path

Therefore, dta.exe was brining up the SQL Server 2014 DTA, which no longer had license information and was failing.
The fix is to add the SSMS directory containing DTA.EXE to the path, before any other SQL Server directories.  See https://stackoverflow.com/q/44272416/238886 for how to edit the path in Windows 10.  Here's the directory to add.  Make sure it's above the other SQL Server directories, as shown here:


Where in pg_catalog is the database or catalog information stored about relations?

You can't it's hard coded. The _catalog columns in the table schema literally cast from a call to current_database()

CAST(current_database() AS sql_identifier) AS table_catalog,

You can see them defined in the source code postgres/src/backend/catalog/information_schema.sql

The docs on information_schema_catalog_name make it explicit

  information_schema_catalog_name is a table that always contains one row and one column containing the name of the current database (current catalog, in SQL terminology).

The docs on information_schema.tables also say,

  Name of the database that contains the table (always the current database)


Compile a PLSQL package code that was passed to an sqlplus session via a shell script?

Running XX_PKGb.sql (a package create statement) from with your PL/SQL block is not allowed. If you must do it there then it needs to be dynamic SQL.

The better way is to simply run it from SQLPlus;

set term on

set echo off

set feedback off

set define on

set verify off

ALTER SESSION SET CURRENT_SCHEMA = XXEOM
/
@XX_PKGb.sql
/
exit


Specifying Order of Executing Stored Procedures

SQL Server will execute the statements in a batch one at a time, from top to bottom. Each statement will complete before the next is executed. In your case the this means the first SP runs to completion, then the second one starts and runs to completion, then the third starts and runs to completion. There is no circumstance in which anything else can occur.
Your error is with how you define stored procedure sp_ETL_db1_CreateSchemas. You use the GO command as if it were part of the stored procedure definition. It is not. As the documentation says

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.

When you run file sp_ETL_db1_CreateSchemas.sql through SSMS (or any other utility) the statements from the beginning of the file to the first GO are sent to the SQL Server engine. When that call returns the statements between the first GO and the second GO are sent, and so on. Eventually all the batches in that file are processed and the result is what you expect.
Or it is mostly what you expect. The definition of the stored procedure is just a stub consisting of the three lines
CREATE PROCEDURE sp_ETL_db1_CreateSchemas
AS

You can verify this by looking at what SQL Server has stored internally as the definition of that SP:
exec sp_helptext 'sp_ETL_db1_CreateSchemas';

You'll see just those three lines.
When you run the batch consisting of the three SPs together it is this empty stub which is executed. Syntactically it is correct so you do not get an error. But it has no statements so no tables are dropped and all the data remains in tact. Hence when a subsequent SP tries to re-load the same data there are key violations.
If you need to batch statements within a stored procedure you can use the EXECUTE('...'); syntax.


How connect to DB PosgreSQL in pgAdmin4 with port 84?

There is a very good reason this port restriction should be removed.  When using a Platform As A Service (PAAS) that creates endpoints based on services, it is typical to create the service on port 80 or 443 regardless of what is behind the URL.  Since this is just a host name, there is no reason why this should be blocked.  The landscape is different then the data center level as these services could be spun up at any time.  This requirement is arbitrary.


MySQL Server :: My C Code can remotely create a database, but not "INSERT INTO" it

User error, my apologies.  I had put a "close_connection()" line in my code for troubleshooting purposes, then forgot about it.  This wasn't a problem until I added an "INSERT INTO" command after the "close_connection()".  So of course the connection was closed when my code tried to INSERT.

The online documentation for the "**** MySQL server has gone away ****" error assumes that the sending code wasn't closed the connection, which is what confused me.  Now I know...


How to get best sellers per headquarters

To get the best selling item per headquarter, you first need to calculate the total sales per product per HQ and then rank them:

WITH TotalSalesByProductAndHq
AS
(
    SELECT h.id AS hq_id, p.product_type, SUM(p.value) AS TotalSales
      FROM Sold_products AS p
      JOIN Employees AS e
        ON e.id = p.employer_id
      JOIN Headquarters AS h
        ON e.headquarters_id = h.id
  GROUP BY h.id, p.product_type
),
RankedSalesByProductAndHq
AS
(
    SELECT hq_id, product_type
         , RANK() OVER(PARTITION BY hq_id ORDER BY TotalSales DESC) AS rnk
      FROM TotalSalesByProductAndHq
)
SELECT h.country
     , h.city
     , h.street
     , r.product_type
  FROM RankedSalesByProductAndHq AS r
  JOIN Headquarters AS h
    ON e.headquarters_id = r.hq_id
 WHERE r.rnk = 1;

Once you are able to do this, you can then bring back in the employee details to obtain the number of products sold in that category per employee:

WITH TotalSalesByProductAndHq
AS
(
    SELECT h.id AS hq_id, p.product_type, SUM(p.value) AS TotalSales
      FROM Sold_products AS p
      JOIN Employees AS e
        ON e.id = p.employer_id
      JOIN Headquarters AS h
        ON e.headquarters_id = h.id
  GROUP BY h.id, p.product_type
),
RankedSalesByProductAndHq
AS
(
    SELECT hq_id, product_type
         , RANK() OVER(PARTITION BY hq_id ORDER BY TotalSales DESC) AS rnk
      FROM TotalSalesByProductAndHq
)
SELECT e.first_name
     , e.last_name
     , h.country
     , h.city
     , h.street
     , r.product_type
     , COUNT(*) AS TotalNumberOfProductsSold
    FROM Sold_products AS p
    JOIN Employees AS e
      ON e.id = p.employer_id
    JOIN Headquarters AS h
      ON e.headquarters_id = h.id
    JOIN r
      ON r.hq_id = h.id
     AND r.product_type = p.product_type
 WHERE r.rnk = 1;

You should check out the documentation for CTE's and the RANK function for more details.


If you haven't verified that Named Pipes is enabled on the SQL instance, do this:
Open SQL Configuration Manager, usually found in Start > Microsoft SQL Server 2012 > Configuration Tools (in Server 2008/R2. If using Server 2012, just search for it with WinKey+S)
Go to SQL Server Network Configuration on the left. Open Protocols for .
Right-click Named Pipes if it's disabled (will be red) and choose Enable.

.Net seems to require named pipes (and your error above mentions it). There are probably other ways to connect that would not require this.


Subquery uses ungrouped column "s.student_id" from outer query

I found the issue, don't need the subquery, just a simple case statement and count.

Fixed query:

select t.teacher_id as id,
       t.name as name,
       case when
           count(h.student_id) > 0
         then 't'
         else 'f'
       end as teacherHasHomeworkToVerify
from teacher t
       left join student s on t.teacher_id = s.teacher_id
       left join homework h on s.student_id = h.student_id
       group by t.teacher_id
       order by t.name collate "en_US"

Fixed SQL Fiddle version link: http://sqlfiddle.com/#!15/a00ac/5


Grant create permission on a specific schema in Oracle 11g

You are correct that there is no way to grant a user create/drop/etc permissions on an entire schema.  I suggest you look into proxy authentication.  This basically involves altering user A to allow user B to proxy as A:

ALTER USER A GRANT CONNECT THROUGH B;

Then the connection uses user B's authentication, but gets the permissions of user A.

connect B[A]/Password@Database

This question was somewhat covered by my more specific question here.

Note on Roles: Roles work well for giving Object Privileges to another user since the privileges are tied to a specific object.  While Roles can grant System Privileges, they apply either to the users own schema or to the entire database and therefore can't apply to another schema.  For example, the user B could be granted CREATE TABLE which would allow it to create tables in its own schema or CREATE ANY TABLE which would allow it to create tables in any schema.  These permissions could be granted directly or through a role, but the former wouldn't allow create privileges in the A schema.  The latter would, but would also allow create privileges in any schema including sys, which would be a security concern.


Index fragmentation problem after disabling page-level locking for an Index

The answer to your query is buried in some documentation:

  Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options.

The reasons for this are explained in detail in the following MSDN blog post:

How It Works: Online Index Rebuild – Can Cause Increased Fragmentation

Some options to resolve the problem in your situation, where page locks are disabled for the index, are:

rebuilding offline
rebuilding at MAXDOP 1


Selecting row multiple times, as specified by a field in that row

If you have a numbers table, you can use that. If you don't have a numbers table, just create one:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED);

INSERT dbo.Numbers WITH (TABLOCKX) (Number)
SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1
  CROSS JOIN sys.all_objects AS s2;

(You probably don't need 1 million rows in your numbers table, but it's a decent ballpark that handles most scenarios. You can also reduce space by applying compression if you have an appropriate edition.)

Then you can do this:

SELECT t.columns
FROM dbo.[a table] AS t
CROSS JOIN dbo.Numbers AS n
WHERE n.Number >= 1 -- in case you have a 0-based numbers table
  AND t.[Number of reports written] >= n.Number;

More info on the power of Numbers tables:

Why are numbers tables "invaluable"?
Generate a Set - Part 1 | Part 2 | Part 3
Why should I consider using an auxiliary numbers table?


Oracle SQL 11gR2 => find out all dates where "impdp" was performed

Every time a Data Pump job like that is started, the database notes this in the database alert log. If you have the alert log, you can find the entries there.

Other than that, the database does not store a history of the Data Pump jobs, unless you specifically instruct Data Pump to keep the metadata by adding the KEEP_MASTER=yes parameter to your job.


Second instance of query designer for a single table in SSMS (Express)?

I don't believe there is a way to do this. SSMS seems to prevent you from opening more than one instance, and the only workaround I can think of is not to kick SSMS but rather just open a new instance of the entire application (as you've already guessed as well).

In any case, I strongly recommend you revert to your previous behavior - the query designer is full of bugs and your "edit data in place" feature actually means that the grid has to place unnecessary locks on the underlying table. On a concurrent system this can be disastrous - what kind of concurrency handling do you think is there? You should try to open two instances of SSMS and try to edit the same row in each copy. Fun fun!

Even though it is slightly more work to write, using proper DML (update/insert/delete) in a proper query window is a much better approach to ad-hoc data modification IMHO. And I have long been advocating to stay away from the SSMS visual designers in all their various forms. The team tried but some of the faulty code has been in there since Query Analyzer and it will never be fixed.


SUGGESTION #1

If your machine has multiple cores, you need to increase the following:

[mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

What are these?

innodb_read_io_threads - The number of I/O threads for read operations in InnoDB.
innodb_write_io_threads - The number of I/O threads for write operations in InnoDB.
innodb_io_capacity - An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer.

SUGGESTION #2

In order for separate data and indexes from the system tablespace (ibdata1), you need to do perform a complete restructuring of InnoDB. Sounds complicated, but is very straightforward. I wrote about this in the DBA StackExchange (Aug 29, 2012) and in StackOverflow (Oct 29, 2010). The basic steps are

Run SET GLOBAL innodb_fast_shutdown = 0;
mysqldump all data to a SQL dump
service mysql stop
Delete the following files

ibdata1
ib_logfile0
ib_logfile1

service mysql start

Before you run service mysql start, add this line to my.cnf

innodb_open_files=32768

That way, there will be file handles dedicated to the each individual table. The default is 300. File handles have been known to get cached. There will be a slowdown if you set this  very high and hit the ceiling quickly. This should not be the case if you are working a small number of tables.


Arithmetic overflow error for data type smallint, value = xxxxx

You mentioned database and server-level settings are the same, but how sure about that are you?  And is it possible something is changing a setting at the session level?

This produces the error for me (of course):

SELECT CAST(40000 AS smallint);

But this does not - it just returns NULL as the value:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

SELECT CAST(40000 AS smallint);

Check to make sure the session doesn't have these SET options disabled by running this on both machines and seeing if the value is different:

SELECT @@OPTIONS;

You could also check to see if this is set at the server level using sp_configure:

EXEC sp_configure 'user options';

The more likely situation is that, due to having a different execution plan on the different servers, the higher value is being filtered out before being cast.  Solutions to that are covered here (and in the linked duplicates):

CAST throwing error when run in stored procedure but not when run as raw query


To my knowledge, this limitation pertains to SQL specifically in MySQL. All the other SQL products that I am aware of do not have it.

There does not appear to be a solution to this problem in general: you cannot force a column reference to be recognised deeper than one level in MySQL. However, there is an easy workaround in your specific case: replace the elements.id in the innermost query with craft_w8_a.elementId:

SELECT elements.id, [ ... ],
(SELECT
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0)
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b`
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = craft_w8_a.elementId)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

This is an equivalent substitute because whatever craft_w8_a.elementId will be passed to the IN subquery is going to match elements.id based on the filter in the query that passes the value.


With MyISAM is there any index size savings when using INT vs BIGINT?

Decided this was easy enough to investigate myself, even though I still do not understand the underlying specifics of why (feel free to elaborate?)

The answer is:  YES  int most definitely creates smaller indexes than BIGINT

I made two tables, first with four unsigned INT columns, second with four unsigned BIGINT

I made a compound index across all four columns for each table.

Then I added a million rows of random unsigned smallints 0-65535  to each table.

(each table has identical data, both numbers and row order)

Then I optimized and flushed both tables just to be certain.

INT

Data    17,000  KiB
Index   31,610  KiB
Total   48,610  KiB

BIGINT

Data    33,000  KiB
Index   56,921  KiB
Total   89,921  KiB

17,408,000 int.MYD
32,368,640 int.MYI

33,792,000 bigint.MYD
58,287,104 bigint.MYI

added:

I was concerned the random data repeated (I found some cases).

So I added a primary column with auto-increment to each table and emptied them. Then I filled each with the numbers from 1 to 1,000,000 in each column, incrementing for each row sequentially.

INT

Data    20,508  KiB
Index   32,301  KiB
Total   52,809  KiB

BIGINT

Data    40,039  KiB
Index   54,178  KiB
Total   94,217  KiB

So not quite 50% savings but definitely adds up, even for index storage.


If you happen to find that SQL Server is consuming the resources a few things you can check within SQL Server:

Adam Machanic's sp_whoisactive. It documents using it very well and it can provide a wealth of information about what your system is doing at times.

A quick query with the performance DMVs can also shed some light on what is going on.


Assigning empty string to INT NULL field in MySQL

It depends on the sql_mode you're running as. If the mode isn't strict then you'll get the inserted value to be the same as the coercion to that type,

SELECT CAST('' AS int);
+-----------------+
| CAST('' AS int) |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

This however is insanity, as you can see..

CREATE TABLE foo ( id int, a int, b int NOT NULL );

INSERT INTO foo VALUES
  (1,1,NULL),
  (2,NULL,''),
  (3,'',NULL),
  (4,NULL,NULL);

SELECT * FROM foo;
+------+------+---+
| id   | a    | b |
+------+------+---+
|    1 |    1 | 0 |
|    2 | NULL | 0 |
|    3 |    0 | 0 |
|    4 | NULL | 0 |
+------+------+---+
4 rows in set (0.00 sec)

So we can tell MySQL to try to be less of a joke, and more like a real database, like PostgreSQL

SET sql_mode='strict_all_tables';

And then,

TRUNCATE foo;

INSERT INTO foo VALUES
  (1,1,NULL),
  (2,NULL,''),
  (3,'',NULL),
  (3,NULL,NULL);

But this time we get,

  ERROR 1048 (23000): Column 'b' cannot be null

For more information see,

What is MySQL's "Implicit Default Value" and how does it work with "strict mode"?


RHEL 6: postmaster dead but pid file exists

service postgresql92-postgresql status checks if the file postmaster.5432.pid instead of postmaster.pid.

I'd recommend to use only pg_ctl as postgres and check with ps aux as root.
Or use only service postgresql92-postgresql start/stop/status as root. Mixing the two methods would turn into conflicts.


PostgreSQL: Switching schema name in a loop

This happens when you raise a WARNING try a INFO instead
Additional you need dynamic sql see https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
DO $$
DECLARE schema_name TEXT;
BEGIN
SET client_min_messages TO INFO;
FOR schema_name in
        select "name" from test.public.tenants
    Loop
        RAISE info 'The current schema is %', schema_name;
        EXECUTE CONCAT('INSERT INTO ', schema_name , '.campuses (select * from public.campuses where code =  $1);')
   USING  schema_name;
    End Loop;
END $$;


How to add External data source into MySQL?

You cannot do what you're asking (i.e.: replicate content from MySQL to FileMaker). FileMaker doesn't have (as of current version) any way to automatically replicate data from external sources.

Instead of that, you can do something different with equivalent result: access your MySQL data directly from FileMaker. This means FileMaker never has a copy of the data. You just have one data source (MySQL), and have FileMaker SELECT, INSERT, DELETE or UPDATE that MySQL table directly.

You can do that for as many tables on as many MySQL databases as necessary.

You do this by using in FileMaker an "external data source". Check Connecting to data sources from FileMaker.

NOTE: This has been available in FileMaker since (as far as I can remember) version 12. FileMaker allows also connections to other RDBMS such as Microsoft SQL server and Oracle. Newer versions of FileMaker allows also external sources to be PostgreSQL and others (See here)


DB2 PURGE (old Online Backups and Archive logs) - some of the archive logs are not removed

You can always clean them up manually or use a script to clean them up (maybe looking for files older than seven days or something).


What's the MySQL version?

MariaDB 10 can do the "merge" - automatically transforming the first query to the second one internally.

MySQL 5.6 cannot do that afaik, but it can materialize the derived table into a temporary one and add automatic indexes - manual.

And you could emulate that even in older versions. If you always get some code for a  then you can do

create temporary table t_indexed as <subquery>;
alter tably t_indexed add key(id);

SELECT * FROM table1 t1
INNER JOIN t_indexed t2
ON t1.id = t2.id;

If you always know the structure of the  results, you can create the temp table with this structure and proper indexes in place and use insert ... select instead.


For just the two pages, a compromise could be:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  200   -- 100 + 100 (outer limit + offset) per source_id
   ) p
ORDER  BY p.external_created_at DESC
OFFSET 100
LIMIT  100;

Which will be considerably slower than the first 100 rows, but still much faster than your original query in your previous question.

For just 107 rows total in your example, performance will be almost identical.

Performance decreases with every later page, obviously. OFFSET is a pain for performance, generally. But it becomes a PITA when combined with your difficult query.

I would consider a materialized view with pre-calculated row numbers or the more complex (but also more potent) solution here:

Can spatial index help a "range - order by - limit" query

Also related:

Improve performance for order by with columns from many tables


Postgres: Insert distinct values from one table into another table with constrains in place

There are 3 possible kinds of duplicates:

Duplicates within the rows of the bulk insert.
Duplicates between inserted rows and existing rows.
Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.

Just like I explained in this closely related answer:

Using EXCEPTION to ignore duplicates during bulk inserts

But things have become easier for 2. and  3. since Postgres 9.5 introduced UPSERT (INSERT .. ON CONFLICT DO NOTHING).

INSERT INTO emails(tag,email)
SELECT DISTINCT 655, email
FROM   emails_temp
ON CONFLICT (email) DO NOTHING;

If your duplicates only stem from duplicate entries in the source (1.), like you indicated, then all you need is DISTINCT. Works in any version of Postgres:

INSERT INTO emails(tag,email)
SELECT DISTINCT 655, email
FROM   emails_temp;


BIT columns all "1" after a phpMyAdmin export/import

I think we'll need some more information to pin down your problem. I just tried to recreate your issue and I could not.

I created a table named bitTest with two columns:
textName VARCHAR(5)
bitValue BIT(1)

I inserted two rows into the table using phpMyAdmin's "Insert" feature

Then I did a phpMyAdmin "Export" using the "Quick method". The resulting .sql file was

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 01, 2013 at 03:34 PM
-- Server version: 5.5.29
-- PHP Version: 5.3.10-1ubuntu3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `zzz`
--

-- --------------------------------------------------------

--
-- Table structure for table `bitTest`
--

CREATE TABLE IF NOT EXISTS `bitTest` (
  `textName` varchar(5) NOT NULL,
  `bitValue` bit(1) NOT NULL,
  PRIMARY KEY (`textName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bitTest`
--

INSERT INTO `bitTest` (`textName`, `bitValue`) VALUES
('False', b'0'),
('True', b'1');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Then I dropped the table and used phpMyAdmin's "Import" feature ro re-create and re-populate it using that .sql file. The BIT values were (correctly) imported as 0 and 1.

Perhaps you could update your question with some excerpts from the .sql file you exported to see what differences might exist between your export file and mine.


SQL Server 2008 onward - Globalization with Date Time

With stand-alone systems which will never be merged using the local system clock's time has appeal. There is no need for conversion going into nor coming from the database. What you see is what you get. No matter how you access the DB, whether application, reports or ad hoc query the results need no post-processing.

However .. some places observer daylight saving. This means that once (?) a year there will be an hour-long gap in the data. And once (?) a year there will be an hour-long time reversal where, say, sorting on identity column and sorting on a datetime column will produce different ordering.

Now, we don't know your application. We don't know how important any of this could be, or what other work-arounds you might think up, or whether a crazy hour here or there is a problem or not, or any of the other factors that might come into play. But if it is then UTC or time offset is the way to go.