MySql: how to remove special characters from column in query

For similar purposes, you can also rely on a series and GROUP_CONCAT:

SET @str:='abc - 123';
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c FROM myseries
    WHERE id BETWEEN 1 AND LENGTH(@str)
) a
WHERE c REGEXP '[[:alnum:]]';

For this you could keep a myseries table for general use, it can be quickly created with 10,000 rows like this:

CREATE TABLE myseries (id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO myseries
SELECT NULL FROM information_schema.columns, information_schema.columns c2 LIMIT 10000;

Or create one on the fly from any table large enough:

SET @str:='abc - 123';
SET @len:=LENGTH(@str);
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c
    FROM (
        SELECT @num:=@num+1 AS id
        FROM (SELECT @num:=0) num, yourtable
        WHERE @num<=@len
    ) myseries
) a
WHERE c REGEXP '[[:alnum:]]';


incompatibility of max_allowed_packet in mysql

16777216 is a reasonable value; 1024 is not.  The error message is just a warning, saying that 1024 is below the minimum allowed value.

Try the mysqldump with 16777216.


How to prevent a record from being affected by the ORDER BY RAND statement in MYSQL

You need to add some flags (or something) to the data.

"None of the above" needs a flag saying it must be last.

Two questions that must be consecutive -- Indicate on the first that a particular question must immediately follow it.

Once you get "business logic" like this, you will probably find that the handling of the flags and the randomization may as well be done in client software, not SQL.

A database is a repository for information.  It is not intended as a programming engine.  There are simple programming things built into the database (ORDER BY date, ORDER BY rand, filter out stuff; etc), but I suggest that your requirements have crossed the boundary into "gotta do it in a real programming language".


Query to change 2 statements into a batching job

Your first while loop

WHILE @i <> 0
BEGIN

Encompasses the second while loop as well.

The docs on break  show that:

  BREAK exits the current WHILE loop

.

Meaning that you are closing the entire while loop including the nested while loop (WHILE @ii <> 0). You have to close the first while loop before opening the second one.

Add an END before opening the next while loop, for example:

    IF(@totalDeleteCurrentCount >= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached on 1st loop';
        BREAK;
    END
    ELSE
        CONTINUE;
END
WHILE @ii <> 0


Determine Nodes in Network with PostgreSQL

Approach 1:
At first sight, it seems that I could apply a basic solution because, according to your sample data, each single connection is included in another connection.
SELECT
    e1.node,
    e1.connections,
    COALESCE(e2.connections, e1.connections) nodes_in_chain
FROM
    example e1
LEFT JOIN
    example e2
    ON e2.node <> e1.node
    AND e1.connections <@ e2.connections;

node | connections | nodes_in_chain
:--- | :---------- | :-------------
a    | {a,b}       | {a,b,c,d}
b    | {a,b,c,d}   | {a,b,c,d}
c    | {b,c}       | {a,b,c,d}
d    | {b,d}       | {a,b,c,d}
e    | {e,f}       | {e,f}
f    | {e,f}       | {e,f}

Approach 2:
But, as @ypercube pointed out, this solution doesn't work if there are 3 or more linear points in a row.
Ex: e -> f -> g -> h
As a reference to solve this question I used the answers in another related question:

Grouping on any one of multiple columns in Postgres

It uses a method called transitive closure to solve the problem.

Transitive closure
In mathematics, the transitive closure of a binary relation R on a set X is the smallest relation on X that contains R and is transitive.
For example, if X is a set of airports and xRy means "there is a direct flight from airport x to airport y" (for x and y in X), then the transitive closure of R on X is the relation R+ such that x R+ y means "it is possible to fly from x to y in one or more flights". Informally, the transitive closure gives you the set of all places you can get to from any starting place.

First let me change your sample data by adding a linear connection of 4 nodes.
DELETE FROM example WHERE node = 'f';
INSERT INTO example VALUES
('f', ARRAY['e','f','g'],     null),
('g', ARRAY['f','g','h'],     null),
('h', ARRAY['g','h'],         null);

Now applying the math:
WITH RECURSIVE al (dst, src) AS --adjacent list or list of all related nodes
(
  SELECT e1.node, e2.node
  FROM   example e1
  JOIN   example e2
         ON e1.node = any(e2.connections)
), tc (dst, src) AS
   (
     SELECT dst, src FROM al -- transitive closure
     UNION
     SELECT a1.dst, a2.src
     FROM   al as a1
     JOIN   tc as a2
            ON a1.src = a2.dst
   )
   SELECT   src, array_agg(DISTINCT dst ORDER BY dst) AS nodes_in_chain
   FROM     tc
   GROUP BY src;

Give us this result:
src | nodes_in_chain
:-- | :-------------
a   | {a,b,c,d}
b   | {a,b,c,d}
c   | {a,b,c,d}
d   | {a,b,c,d}
e   | {e,f,g,h}
f   | {e,f,g,h}
g   | {e,f,g,h}
h   | {e,f,g,h}

db<>fiddle here

NOTE: The original relation has only the immediate connections, which can be seen as paths of length 1 (2 nodes each). Approach 1 finds all paths of length 2 (3 nodes) as it applies a method of connecting once. To find paths of length N, you have to apply the methods N-1 times. To find all paths of arbitrary lengths (alas the transitive closure), you need a recursive solution, or a while loop.
It can't be done with simple SQL. (ie: one query without CTEs.)
@ypercube


Newbie IT student. Question about a homework assignment

I'm no expert, but it seems to me you should be giving this feedback to your tutor.

If you feel like you're not sure how to proceed with the work, clearly they are not communicating their expectations  well and they should find that out - they won't if you don't tell them!


Are these two statements the same performance wise?

According to the official MySQL Documentation (How MySQL Optimizes WHERE Clauses) MySQL will optimize the WHERE clause as follows:

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

(Please read the full documentation)

There is a small disclaimer at the top of the article which states:

Note:
Because work on the MySQL optimizer is ongoing, not all of the optimizations that MySQL performs are documented here.

Based on the quoted article MySQL should run both statements using the same execution plan and the supplied index.


Simultaneous calls to the same function: how are deadlocks occurring?

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table:

  I believe the issue with this is that a SQL function will do parsing (and
  maybe planning too; don't feel like checking the code right now) for the
  entire function body at once.  This means that due to the INSERT command
  you acquire RowExclusiveLock on the "test" table during function body
  parsing, before the LOCK command actually executes.  So the LOCK
  represents a lock escalation attempt, and deadlocks are to be expected.

  This coding technique would be safe in plpgsql, but not in a SQL-language
  function.

  There have been discussions of reimplementing SQL-language functions so
  that parsing occurs one statement at a time, but don't hold your breath
  about something happening in that direction; it doesn't seem to be a
  high priority concern for anybody.

  regards, tom lane

This also explains why locking the table outside the function in a wrapping plpgsql block (as suggested by @ypercube) prevents the deadlocks.


Error 41145 after joining database to availability group

I've seen that recurring error about joining the database when automatic seeding was used to initialize the secondary replicas.  My solution has always been to disable automatic seeding once the secondary databases have all been created and synchronized:
ALTER AVAILABILITY GROUP YourAgName
MODIFY REPLICA ON 'YourReplicaName'
WITH (SEEDING_MODE = MANUAL);

Those information messages are normal, but I haven't ever seen thousands of them in a row.  My experience is that you'll see several of those messages per database per replica when the AG connection to that replica is established.
I just started up some VMs in my home lab environment that have:

1 AG
3 databases in the AG
3 replicas total (primary, sync secondary, async secondary)

I ended up with 90 of those "DbMgrPartnerCommitPolicy" messages (10 messages * 3 databases * 3 replicas).


Oracle 11.2.0.4 -- "sofar" increasing slowly in V$SESSION_LONGOPS

The TIME_REMAINING in V$SESSION_LONGOPS is much like the time remaining on downloads in Internet Explorer or file copies in Windows: It is a guess, massively subject to change, and often not a very good guess. I use SOFAR divided by TOTALWORK to measure progress. As long as SOFAR is moving forward at the expected speed, there is no problem. e.g.

select opname, sid, serial#, sofar, TOTALWORK,START_TIME,round(sofar*100/TOTALWORK,2) pct_complete,
LAST_UPDATE_TIME , round(time_remaining/60) as "Minutes Remaining"
from v$session_longops
where sofar <> totalwork and totalwork > 0
order by START_TIME;


Schema Migration: SQL Server Data Tools vs Liquibase and Flyway

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database.

The SSDT way (and redgate sql compare way) to do a deloyment is to declare what you want so if you want to change a table that looks like:

create table a(id int)

to a table that looks like:

create table a(id int, another_column varchar(12))

with SSDT you just change your table definition to the second one and let SSDT worry about how to upgrade it (can it do an alter table, add column or does the column order change so you will need to rebuild the table etc).

With Liquibase (DbUp, ReadyRoll, manual methods etc) what you do is in this case have to write the alter table yourself and make sure that you run the scripts in the correct order, consider this scenario:

Release 1 - create column hello on table
Release 2 - rename column hello to joe_blogs
Release 3 - rename column joe_blogs to hello
Release 4 - create column joe_blogs

If any of the releases are missed, none of the next ones can continue.

Benefits of upgrade scripts (Liquibase, DbUp, etc):

You have complete control over the scripts
DBA's / Developers are used to this

Benefits of compare / merge (SSDT, Redgate SQL Compare):

Do not have to write upgrade scripts
It is easy to get to any specific version just compare and merge that version

Drawbacks of upgrade scripts:

Must be run in order
Rely on humans no making mistakes
Can be slow especially if you have a lot of changes
Unless your team is very disciplined databases in different environments (dev, test, staging, prod etc) often become out of sync making any testing invalid
Downgrading a release means writing the reverse of all the scripts you have already written

Drawbacks of using compare / merge:

Tools are not 100% trusted, perhaps unfairly
SSDT requires a working project, many many databases have code that doesn't actually compile or run (think dropped tables but not procedures etc), I have seen this in about 8/10 databases I have inherited :)
Many DBA's / developers are hesitant to give up developing in SSMS / notepad

Personally I really think SSDT is a professional development environment and it means that I can concentrate on writing useful code and tests rather than writing upgrade scripts which are in themselves just a means to an end.

You asked for opinions so there you go :)

ed


in sqlite how to check the table is empty or not?

If your table can contain a lot of rows, you can win some time with

SELECT count(*) FROM (select 0 from employee limit 1);


Cannot delete proxy (7). It is used by at least one jobstep. Change this proxy for all jobsteps first

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-proxy-transact-sql

Your problem is:

  If a job step refers to the proxy specified, the proxy cannot be
  deleted and the stored procedure fails.

You should be able to use this query to get the proxy_id.

SELECT * FROM sysproxies WHERE NAME = 'CoreRefData'

Then run this to see what jobs are using the same proxy.  Change the proxy_id from 1 to whatever you get from above query.  If this query does not return any record you are good to delete the proxy but if it does records your delete will fail as explained in the above Microsoft document.

USE msdb;
GO
SELECT sysjobsteps.job_id,
       sysjobs.NAME    AS 'JobName',
       sysjobsteps.step_id,
       sysjobsteps.step_name,
       sysjobsteps.subsystem,
       sysjobsteps.last_run_date,
       sysjobsteps.proxy_id
       --, sysjobsteps.step_uid
       ,
       sysproxies.NAME AS 'ProxyName'
FROM   sysjobsteps
       LEFT JOIN dbo.sysproxies
              ON sysjobsteps.proxy_id = sysproxies.proxy_id
       LEFT JOIN dbo.sysjobs
              ON sysjobsteps.job_id = sysjobs.job_id
WHERE  sysjobsteps.proxy_id = 1

You can do few things here (the goal is to disassociate the proxy with any Job step):

Change those Job steps to run under different account temporarily. It can be SQL Agent account for example.
Script out those job steps and delete temporarily.

Once done you should be able to run your script without any error.


Generate INSERT statement from SELECT query

I wasn't clear where you want the transformed id_estado inserted, so I chose "state" as a destionation.   You can modify accordingly:

insert into courrier_city (country, state, created, modified) select "VE", if(is_estado<10, concat("VE0.",is_estado),concat("VE.",is_estado)), now(), now() from zoom_city

You will probably want to modify the example further to account for specifying all the destination columns with either a literal value such we did for country, a transform like we did for is_estado, or just the basic column name in the select portion to copy values.

You should specify some value for every destination column without a schema default defined.


Transactional Replication between SQL Server 2017(Publisher+Distributor) and SQL Server 2019(Subscriber)

What are the Compatibility Levels for the replicated database of your SQL Server 2017 and SQL Server 2019 instances? Is the 2017 instance set to the default level of 140 and 2019 instance set to the default level of 150? This is a guess but if the 2017 server is set to an old enough level, perhaps that's the issue?
You can verify the Compatibility Levels of the database here: View or Change the Compatibility Level of a Database
Or using this query from the docs linked above:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'YourReplicatedDatabaseName';


SQL Server - Get a new columns with name of the columns have the values X base on rows

If you don't know the table structure, one possible approach is to generate and execute a dynamic statement. Use the information from sys.columns and sys.tables views to generate the statement.

Table:

CREATE TABLE Data (
    id int,
    field_1 varchar(1),
    field_2 varchar(1),
    field_3 varchar(1),
    field_4 varchar(1),
    field_5 varchar(1)
)
INSERT INTO Data
    (id, field_1, field_2, field_3, field_4, field_5)
VALUES
    (1, 'X',  NULL, 'X',  NULL, 'X'),
    (2, NULL, 'X',  'X',  NULL, NULL),
    (3, NULL, NULL, NULL, 'X',  NULL)

Statement:

-- Declaration
DECLARE @stm nvarchar(max) = N''

-- Columns names
SELECT @stm = CONCAT(@stm, ', CASE WHEN ', col.[name], ' = ''X'' THEN ''', col.[name], '-'' ELSE '''' END')
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
WHERE (tab.[name] = 'Data') AND (col.[name] LIKE 'field_%')

-- While statement
SET @stm = CONCAT(
    N'SELECT *, CONCAT(',
    STUFF(@stm, 1, 1, N''),
    N') AS NewColumn FROM Data'
)

-- Execution
PRINT @stm
EXEC sp_executesql @stm

Output:

-------------------------------------------------------------------
id  field_1 field_2 field_3 field_4 field_5 NewColumn
-------------------------------------------------------------------
1   X       NULL    X       NULL    X       field_1-field_3-field_5-
2   NULL    X       X       NULL    NULL    field_2-field_3-
3   NULL    NULL    NULL    X       NULL    field_4-


Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

In this particular situation what did help me:

It was needed just to copy extension directory from /usr/share/pgsql/extension/ to /var/lib/pgsql-root/usr/lib/pgsql/extension/.

That's all folks.


Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

I've tried to do something similar some years ago and the SQL required defeated me as well!

The approach I took was to use gawk to extract all the statements I wanted from the unload file and then run that file. The whole approach could potentially be run in a batch file using the SQLA command line utility dbunload.exe, then gawk, then isql to run the extracted commands. How easy this approach is depends on how happy you are with complex regular expressions etc.

Another approach would be to use the sa_migrate_create_fks() system procedure - it is well documented in the help, but there would be several stages involved.

Alternatively, repost the question on the dedicated SQLA forum http://sqlanywhere-forum.sap.com which is monitored by SQLA developers who may be able to help with a SQL construct.

Good luck


Do I always need boilerplate code to prevent nested transactions?

Unless you like typing it all out by hand, yes, you should use boiler plate code for transactions in procedures, if your need explicit transactions.  The pattern I use to to wrap all my non-boiler plate code within the transaction in a BEGIN/END block, with a comment on the same line after the begin.
I also have a block before the transaction code for all setup/work that can be done outside a transaction.  For example, suppose I need to create a transaction for all accounts in a given state.  I may identify all accounts in a certain state and load them into a temp table.  Then I build out the transaction, line items, etc., in to temp tables doing all the calculations, etc. outside the main transaction.  Note: I do not care if someone if price changes during this process because the business would consider it valid as long as everyone in the list got the same pricing (either just before) or just after, the change.
When I go to insert the transactions and line items, I do all of that in the boiler plate to ensure that it is impossible to have a transaction without the corresponding line items.  Also, I try to make more of my processes "trial-able"... you can try it in production, output what the results would look like, then rollback.
Keep in mind single statements have an implicit transaction, so no need to boiler plate if you are really just running one query.  Or, if you are running multiple queries and a change between is acceptable for your particular case.
Lastly, for some cases, I also add internal testing as well, ensuring the state of things before I start, while in process, and a check at the end that we haven't arrived at an invalid state.  A lot of this has to do with users using a flexible system, then doing something that breaks assumptions.  These are generally high importance/infrequently run scripts (e.g. Billing).  I also tend to run in trial mode just before production mode, to catch any issues first.
Because of that, my boiler plate also includes the code to deal with testing and for the trialing of results.  I just delete it when I don't need it.


The quickest (and cheapest) way to find out what is hitting your databases is to use sp_whoIsActive (http://whoisactive.com/)


Testing query speed in two different RDBMS

You could use HammerDB. With HammerDB you perform a TPC benchmark test. TPC benchmark testing can be used by vendors for official certification, but you can also use it as 'non-offical' for your personal benchmarking. The TPC-E (OLTP) and TPC-H (OLAP) are the more modern version of the TPC benchmark tests. With HammerDB you can emulate an OLAP or OLTP workload with the same characteristics (you can configure this) and run it on different RDBMSes.

You have 2 types of measurements; TPM and NOPM. TPM cannot be compared between different database types, but NOPM can. This tool is fairly easy to use, although you can make things a little more complex if you want to. :-) You can get HammerDB simpy from hammerdb.com.

Although this is not intended to test with a specific query, it could be usefull depending on your scenario.


Total duration "server" was in "In" state per day

Since you are certain the source data is always reliable, you might use this:

;WITH src AS
(
    SELECT t1.ServerID
        , [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate))
        , t1.InDate
        , OutDate = (
        SELECT TOP(1) OutDate = al.DateAndTime
        FROM dbo.tbl1 al
        WHERE al.Reader = 'OFF'
            AND al.ServerID = t1.ServerID
            AND al.DateAndTime > t1.InDate
        ORDER BY al.DateAndTime
        )
    FROM (
        SELECT al.ServerID
            , al.Reader
            , InDate = al.DateAndTime
        FROM dbo.tbl1 al
        WHERE al.Reader = 'ON'
        ) t1
)
, src2 AS
(
    SELECT *
        , Duration = DATEDIFF(MINUTE, src.InDate, src.OutDate)
    FROM src
)
SELECT src2.ServerID
    , src2.Date
    , FirstIn = MIN(src2.InDate)
    , LastOut = MAX(src2.OutDate)
    , Duration = SUM(src2.Duration)
FROM src2
GROUP BY src2.ServerID
    , src2.Date
ORDER BY src2.ServerID
    , src2.Date;

Output (I used the first set of sample data, since you didn't originally provide the INSERT statements):

This code collapses your source table so each row has an "in" and an "out" date, which enables us to use the DATEDIFF function to calculate the duration.

This line, [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate)) strips the time component from the InDate so we can use it to display one row per day per server.  This could be moved into the table definition as a calculated, persisted column.  The table definition would be:

CREATE TABLE dbo.tbl1
(
    DateAndTime DATETIME NOT NULL
    , Reader CHAR(3) NOT NULL
    , ServerID INT NOT NULL
    , DateOnly AS DATEADD(DAY, 0, DATEDIFF(DAY, 0, DateAndTime))
        PERSISTED
);


How to sync a table between oracle and sybase

You could use Oracle Data Integrator for this. See Oracle Data Integrator documentation for a good starting point. If you want you can build this yourself. Change Data Capture can be used. Make a change table in Oracle that receives the change records from the Oracle source and implement the change records to the sybase destination tables.


What does it mean that sp_who2 is not documented?

It is "not documented" means that it is not part of any officially published API so there is no implicit or explicit promise that it will work in any given way, keep working at all, or not just vanish at any time (perhaps a security hole is found relating to it so MS just decide to disable it, temporarily or permanently, because fixing it quickly isn't practical).

It is unlikely that this will happen, it is likely that it will stay around in its current form, but there are absolutely no guarantees of this so if you choose to rely on it you do so at your own risk.

Also as the behaviour of the call is not defined in any official documentation MS have no obligation to fix any bugs in it in a timely manner or at all (as there is no official documentation that describes its behaviour any behaviour it exhibits could be considered correct).

On a related note: all the information it outputs is available from documented system tables and views (you can script out its code from master to see what it does) so you could write your own that collects even more information to output - for years I had my own "sp_who3" that did just that. These day's I use Adam Machanic's sp_whoisactive instead.


pg_dump is not an SQL command. It's a separate command, like psql. So you can't run it from within a psql session, you must run it from a regular shell.

The reason you're not getting an outright error is that you didn't write a semicolon on the end of the line. That's why the prompt changed, too:

postgres=# pg_dump test_site > payroll.dump.out
postgres-# ls
        ^ <-- See how it goes from = to - ?

That indicates that it's a continuation line, so psql is buffering up a statement and hasn't sent it yet.


Recovering mysql innodb table from table.ibd backup file

InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is not enoght.

Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

Have a look at  Recovering an InnoDB table from only an .ibd file.

Also have a look at  This Detailed explanation  from  @RolandoMySQLDBA.


Doing relational things will ways be fastest in the context of the database. Even though Entity Framework does things efficiently, in an apples to apples comparison, it'll always have slightly more overhead.
That being said, the big performance difference comes in with how you implement a database focussed solution or a C# focussed solution. I won't comment on your colleague's recommended alternative as that seems a little bit outside of what I'd suggest. I think there's probably better solutions, situation depending, such as using EF Core's DBContext.Find() methods to apply WHERE predicates on the primary key values, but your post asks a lot of important questions that require a lot more thought to respond to, so I'll update my answer later when I have more time.
One quick one though is yes reads do cause locks against writes in a table, and pulling in the whole table into a consumer like a C# application to only update a few records is usually not performant. Rather you should use WHERE predicates on your data pull to only access the rows you need for the lifetime of the current context of the app. (Assuming your tables are indexed appropriately.)

To address the points you made / other questions you asked:

"rather put all the incoming info in a temp table named #products": While using a temp table to temporarily hold the primary keys of the records you want to update is one way of doing things in a potentially efficient way, it sounds a little roundabout. If you already have the primary keys (IDs) of the records you want to update, then you can just pass in those IDs to the stored procedure (via a couple different options for the parameter's data type) or even better yet just write the update statement itself with those IDs and execute it. (Just a couple of ideas among many other solutions). Again if you want a pure C# solution, you should be able to use EF Core's DBContext.Find() method to only pull in the rows that need to change, and then update them. (Probably the best middleground solution.)

"do reads create locks or degrade the database performance?": Yes they can affect performance of the server by causing resource contention if you are frequently reading a lot of data. Reading from a table also acquires locks on that table that can block write queries, and cause contention. See the afore-linked DBA.StackExchange answer for more in depth information on locking.

"It creates a lot of unnecessary updates, which is very wasteful in my eyes, since only 25 percent of the information changes, so why update all columns.": I don't fully understand your statement here, not all columns are being updated based on the SQL solution your colleague suggested. And assuming he means only send the IDs of the records that need to be updated in a temp table, then only the exact number of rows that need to be updated are being updated.

"My colleague counters, that I can remedy that by adding a where statement...": This indicates to me that your previous statement is under the assumption all the rows should always be passed into the temp table. As per my previous response, you should only pass in the rows that need to be updated.

"I don’t think that this reduces the penalty u pay, I think its still the same effect.": The addition of the WHERE statement your colleague suggested is unnecessary if your temp table only contains the IDs of the rows that need to be updated. (If your temp table did contain every row in the table, then it's hard to say if it would be more efficient without seeing the execution plan of the query. Possibly it would still be more efficient than the process in EF Core of pulling in all the records into the application and then updating them.) Also if you went a different route that didn't use a temp table, and involved filtering in SQL that was logically equivalent to what you wanted to accomplish (e.g. update products set buyboxprice = 'someValue' where someOtherField = 'someOtherValue') then that filtering would be more efficient (assuming you have proper indexing) than your current process all in EF Core.

"Another major worry is that large updates create locks": Apples to apples, if you have the same query plan in both cases, then the same amount of locking is going to occur due to your updates. (Though the amount of rows that need to be updated at one time can affect the query plan.) You can play around with chunking (batches) but generally won't really be necessary until you're updating about 100,000+ records at a time. But batching is a design choice irrespective of C# and SQL, you can accomplish this in either place, so it's a moot point.

"Third point, when SQL has tummy trouble, it throws symptoms all over...I have very little visibility of what’s going on, but in C# whenever something crashed is right there clear for me.": This is not an issue with SQL rather inexperience with the framework and tools available (I don't mean this negatively, just a matter of fact, as someone who was in the same shoes a few years ago). There was likely a time when you didn't know how to use the debugger in Visual Studio right?...and breakpoints...same for using try/catch? Up until you became proficient with the tools for C#, you probably had a harder time debugging crashes and bugs. The same is true for SQL, furthermore performance issues != crashing and bugs. They require a different kind of mindset and process for debugging. If you had a non-database related performance issue in your C# application (for example if your app was slow when saving a file to disk), you wouldn't have as much clarity debugging the issue as a crash using the tools you're familiar with debugging crashes. There are many good tools and scripts available for debugging performance issues in SQL Server, please see the list below:

Brent Ozar's sp_Blitz
Get Running Queries - I fine tuned this query (I forget where I originally found it) and find it immensely helpful for on the spot debugging performance issues as it tells you all running queries on the server, where they originated from, how long they've been running and wait stats and blocking info. (I have it saved in a stored procedure which can be scheduled to routinely run and log the results to a table.)
Query Store
Extended Events
SQL Profiler (Deprecated but worth noting.)
Activity Monitor
Microsoft's Docs on Performance Monitoring
Helpful Queries from PASS
SentryOne's SQL Sentry
RedGate's SQL Monitor

In summary, as a C# developer for 8 years, and a DBA for about 3 years, it has been my experience that managing and manipulating data is most efficient when structured well with good processes that maximize the relational capabilities of the database (as opposed to trying to accomplish the same task in a functional language).


Your WHERE clause must be after your join, perhaps?

SELECT *
FROM CallDetailRecord
    JOIN CallEvent ON EventID = CallEventID
WHERE StartTime >= 1357102799000 AND StartTime <=1357880399000

Note: You may want to prefix the fields with table names or aliases...


Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

If I understand you correctly, a better way is to use TABLOCK hint to force a table level lock in the query that needs it (example: delete from a heap)

UPDATE SomeTable WITH (TABLOCK)
SET ...

INSERT SomeTable WITH (TABLOCK)
...

Personally, I wouldn't set it globally in the CREATE INDEX statement...

Also, the MSDN page for sp_indexoptions states

  This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use ALTER INDEX (Transact-SQL).


It sounds like the transaction is being committed (perhaps automatically) even when the application times out.  Changing the COMMIT behavior would be the best option.

If you can't do that, you could put the database action in a package and then have the package check after it runs the action to see how much time has elapsed.  If the step took more than 45 seconds then it should ROLLBACK, otherwise COMMIT.

Although this answers your question, it is a fragile solution and you really should look into changing the COMMIT behavior or causing a database disconnect when a timeout occurs.


Oracle OLEDB Connections hang in SQL Server: Can I restart Instance?

The roll-back will likely take as long or longer than the amount of time the query was running.

Almost certainly, if you restart the instance, whatever database is affected by the ongoing transaction will need to run recovery, which will take as long or longer than the rollback that is currently happening.

Advice? Don't kill SQL Server.

Also, killing the dllhost.exe process won't stop the rollback, since that process is no longer required by SQL Server to complete the rollback.

You can see if activity is happening against the database in question by checking the following:

SELECT [Database] = d.name
    , FileName = mf.name
    , num_of_reads
    , [io_stall_read_ms]
    , AvgStallPerRead = CASE WHEN num_of_reads > 0 THEN io_stall_read_ms / num_of_reads ELSE 0 END
    , num_of_writes
    , [io_stall_write_ms]
    , AvgStallPerWrite = CASE WHEN num_of_writes > 0 THEN io_stall_write_ms / num_of_writes ELSE 0 END
    , [io_stall]
    , sample_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) iovfs
    INNER JOIN master.sys.master_files mf WITH (NOLOCK) ON iovfs.database_id = mf.database_id AND iovfs.file_id = mf.file_id
    INNER JOIN master.sys.databases d WITH (NOLOCK) ON iovfs.database_id = d.database_id
WHERE d.name = 'database_name_in_question';

Running this several times in a row should allow you to see num_of_writes increasing.

This won't help you know the full progress of the rollback, but will allow you to see if there is disk activity against the database.

You could check the following query to see activity against the database in question:

SELECT login_time
    , host_name
    , program_name
    , start_time
    , r.status
    , command
    , wait_type
    , last_wait_type
    , wait_time
    , wait_resource
    , percent_complete
    , estimated_completion_time
FROM sys.dm_exec_sessions S
    INNER JOIN sys.dm_exec_requests R ON S.session_id = r.session_id
WHERE r.database_id = DB_ID('database_name_in_question');

Look for the session where the rollback is occurring by looking for "ROLLBACK" (or something similar) in the command column.  Unfortunately, the percent_complete and estimated_completion_time columns won't be useful for looking at rollbacks.

If you can determine the SPID for the rollback activity, you can use the following command to check progress on the rollback (new to me!)

KILL xx WITH STATUSONLY;

Where xx is the SPID.

See http://msdn.microsoft.com/en-us/library/ms173730.aspx for further details about that!

sys.dm_tran_database_transactions has very detailed information about ongoing transactional activity, including roll-backs.


Distributed AG on SQL Server 2017 Standard Edition?

No, you can't. That is one of the basic availability groups limitations.

Limitations

  Basic availability groups can not be part of a distributed
  availability group.


All enabled audit rules can be found in the below views:
Standard audit + FGA:
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts;
select * from dba_obj_audit_opts;
select * from dba_audit_policies;

Unified audit (12c and above):
select * from audit_unified_enabled_policies;

Unified audit policy details:
select * from audit_unified_policies;

With the above, you can find whatever audit was enabled, and disable/modify as needed.


Foreign key part of composite primary key of new table

so basically you have something like this (except I renamed your FK table to C since it was already pulling B from somewhere else):

CREATE TABLE IF NOT EXISTS `table_a` (
  `apk` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`apk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `table_b` (
  `bcol` varchar(3) NOT NULL,
  `Location` varchar(20) NOT NULL,
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fk_table_c` (
  `apk` int(10) NOT NULL,
  `bcol` varchar(3) NOT NULL,
  `SomeOtherValue` varchar(20) NOT NULL,
  PRIMARY KEY (`apk`,`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `fk_table_c`
  ADD CONSTRAINT `fk_table_c_ibfk_2` FOREIGN KEY (`bcol`) REFERENCES `table_b` (`bcol`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_table_c_ibfk_1` FOREIGN KEY (`apk`) REFERENCES `table_a` (`apk`) ON DELETE CASCADE ON UPDATE CASCADE;

this is completely normal and how you would form many to many relationships between 2 entities. You may wish to have some other auto generated unique identifier as the primary key but its a good idea to have the 2 FKs as a combined unique key to prevent duplicating rows of the same combinations


How to see special characters in MySQL Workbench query results?

I doubt if there is an easier way than that CONCAT to see the spaces.  (And that will fail for various esoteric characters.)

Here's another way; it is more practical for more complex situations:

SELECT HEX(`code`) ...

It will return 465220. for "FR ".

Declare it this way:

code CHAR(2) CHARACTER SET ascii NOT NULL

The (2) will truncate to 2 characters (perhaps with a warning); the rest is appropriate for a "country code" column.

(When asking questions, specify MySQL version.)


Move Mysql database from Windows Xampp to Linux MySql Server

One way you could do it is by using HeidiSQL, a free MySQL client. You can get it here:

https://www.heidisql.com/

You can configure the connection to the Windows XAMPP server and the Linux server, and then Heidi will be able to transfer the data directly from one server to the other. To do that:

connect to the "source" server
right-click on the database or table you want to transfer and select "Export Data As SQL"
this will open a dialog box
you likely will want to select the checkboxes to create the tables and databases you want to transfer if it's the 1st time you have transferred them
if you want the data and not just the structure, be sure to select the data dropdown to transfer the data as well. I usually use "Delete + Insert", but your needs may vary
in the output dropdown, select the "destination" server. In this case, it should be your Linux server
In the Directory dropdown, you likely will want to leave it as [same as source server]
Then click the Export button and wait


way to prevent queries from waiting for table level lock

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

InnoDB vs MyISAM with many indexes (Jul 05, 2012)
Which is faster, InnoDB or MyISAM? (May 03, 2012)
Any gotchas at all with converting from MyISAM to InnoDB? (Jan 09, 2012)
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks? (Oct 07, 2011)
Benefits of table level locking (Sep 14, 2011)
What are the main differences between InnoDB and MyISAM? (Apr 14, 2011)

With regard to your current question, here is a possible scenario:

article and article_comments are both MyISAM tables
article_comments has one or more indexes with status as a column
Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)


Can an index on one table be used in the query for another table?

Selective XML Indexes does just that for you behind the scenes.

It allows you to specify an XPath expression in the index and when you use that expression in a query SQL Server uses a system table to retrieve the value you are looking for.

Example:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

go

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  Node1 = '/root/node1/text()' as sql int
);

go

insert into dbo.T(XMLDoc) values('
<root>
  <node1>1</node1>
</root>');

go

select T.XMLDoc.value('(/root/node1/text())[1]', 'int')
from dbo.T;

The query plan for the XML query shows a table scan of T and a nested loops join with a seek on the primary key into the system table that holds the value you are looking for.


Is it possible to run MongoDB javascript scripts in DBeaver

subtle differences to the script allows this to run in DBeaver, and (with a small adaptation) as a script for mongo <script.js>

conn = new Mongo();
db = conn.getDB("myDB");

cursor = db.album.find({}, {'k': 1 });

albumKeys = [];

while( cursor.hasNext() ){
    albumKeys.push( cursor.next()['k']);
}

cursor = db.song.find({ 'alb' : { $not : { $in : albumKeys } } } );
songsWithMissingAlbum = [];

while( cursor.hasNext() ) {
    songsWithMissingAlbum.push(cursor.next());
}

return songsWithMissingAlbum; /* printjson(songsWithMissingAlbum) */

Pertinent changes

semicolon line terminations
final return statement (for dbeaver) which must be changed to printjson(..) call in command line script for mongo.
added db and connection setup which works in both.


How to setup large database access/cache on LAMP environment?

50K rows tables are rather small. Before throwing more hardware at it, I'd recommend checking/tuning the following:

my.cnf setting: make sure you have good buffers on InnoDB buffer, Key Cache, Query cache, etc. If website is calling same queries over and over, query cache could help.
Check slow query log and optimize worst queries. It'd say this would result in best result for your efforts.

You may need to normalize some tables, but above two are least invasive options that could take your DB performance to next level.


MySQL: with CREATE_USER_PRIV, GETTING "Access denied" when attempting to change another user's password

According to MySQL documentation, in order to change a password of a user, bmcrae must have UPDATE privilege for the mysql database.

To check the privileges of the user bmcrae, run the following command:

show grants for 'bmcrae'@'hostname';

OR

Show grants for bmcrae;

And check whether, the user bmcrae has UPDATE privilege on mysql database.

If the bmcrae doesn't have that privileges, grant update privilege to bmcrae user.

Login as a root user and run the following statement to grant update statement to bmcrae user.

grant update on mysql.* to 'bmcrae'@'hostname';

Warning: If you grant update privileges on mysql database, the bmcrae user can update all tables of mysql database.


Set PK as a combined string of table 1 and 2 records

I think you just need a proper UNIQUE or PRIMARY KEY constraint:

ALTER TABLE drink
    ADD CONSTRAINT drink_pk
        PRIMARY KEY
        (drink_type, drink_flavor);`

If you often want the concatenation of the 2 columns in query results, you can use a view or a generated column (depending on MySQL version).


Sleeping SPID blocking other transactions

From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling.

So, you need to use SET XACT_ABORT ON or add some client rollback code

See SQL Server Transaction Timeout for all the gory details


Allowing end users to kill their own queries

Your DBAs can grant you elevated permissions so that you can do this.  Normally this wouldn't be needed however.  If you are in SQL Server Management Studio you can simply click on the stop button and 99% of the time the query will simply stop (the amount of time that it takes to stop will depend on the amount of data which has been changed by the query as the transaction will need to be rolled back).  Failing that you could kill the application on your side which would then terminate the query on the SQL Server when the application is closed.


Are InnoDB files binary compatible between Windows and Linux?

It's all in the documentation here. It should be OK but see my final point.

Two important points to note:

Copying Data Files (Cold Backup Method)

You can move an InnoDB database simply by copying all the relevant files listed under "Cold Backups" in Section 14.15, “InnoDB Backup and Recovery”.
Like MyISAM data files, InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format.

(Should be OK unless you are running Linux on Sparc or ARM! :-) )

and

Using Lowercase Names for Cross-Platform Moving or Copying

On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, create all databases and tables using lowercase names. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating any databases or tables:

[mysqld]
lower_case_table_names=1

Finally, I think tat this is also of interest, particularly for you.

A lot of water has passed under the bridge between 5.0 and 5.6.

If at all possible I strongly recommend that you do a mysqldump and copy over your data that way.

Export and Import (mysqldump)

You can use mysqldump to dump your tables on one machine and then import the dump files on the other machine. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.

One way to increase the performance of this method is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.


Understanding the usage of version store in tempdb

There can be many reasons for that.

You're queries run and generate a version, during another query use the same tables. During this phase version 1 will be hold back. If your transaction isn't commited at the end, it may occur that version 1 will last for a long time in your tempdb. Make sure that all transactions are probably commited and closed.
Make sure that all information is stored on the disc. It may occur that your information is still stored in your memory. This could cause the hold of a version in your tempdb. You can force the latch process by using CHECKPOINT after your commit.

Hopefully this will help you.


SQL Developer cannot connect to Oracle database, yet sqlplus can!

Try this:

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

If that doesn't work, replace "SERVICE_NAME" with "SID".

Put this in $ORACLE_HOME/network/admin/listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = COTT3)
          (ORACLE_HOME = /opt/oracle/oracle/product/10.2.0/db_1 )
    )
  )

Then restart the listener with:

lsnrctl reload

Are you 100% sure that the database has started up?

Does:

ps auxww | grep pmon | grep -i cott3

... produce any output?


I have a rather ugly approach that will strip alphanumeric characters from a user variable

STRIPPING ALPHAS

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
    SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L,
    (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
    (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
) alpha;
SELECT @st;

STRIPPING ALPHAS EXECUTED

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
    -> (
    ->     SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    ->     (SELECT 'abcdefghijklmnopqrstuvwxyz' chars) L,
    ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
    ->     (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    ->     WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
    -> ) alpha;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n5d6o7          |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2l-34*n56o7           |
| r1+o2-34*n56o7            |
| r1+o2-34*n56o7            |
| r1+o2-34*56o7             |
| r1+2-34*567               |
| r1+2-34*567               |
| r1+2-34*567               |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
| 1+2-34*567                |
+---------------------------+
26 rows in set (0.00 sec)

STRIPPING ALPHAS RESULT

mysql> SELECT @st;
+------------+
| @st        |
+------------+
| 1+2-34*567 |
+------------+
1 row in set (0.00 sec)

mysql>

STRIPPING NUMERICS

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
    SELECT x+1 ndx,SUBSTR(chars,x+1,1) ch FROM
    (SELECT '0123456789' chars) L,
    (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
) numerics;
SELECT @st;

STRIPPING NUMERICS EXECUTED

mysql> SET @st='r1+o2l-3a4*n5d6o7';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
    -> (
    ->     SELECT x+1 ndx,SUBSTR(chars,x+1,1) ch FROM
    ->     (SELECT '0123456789' chars) L,
    ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X
    -> ) numerics;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-3a4*n5d6o7         |
| r+o2l-3a4*n5d6o7          |
| r+ol-3a4*n5d6o7           |
| r+ol-a4*n5d6o7            |
| r+ol-a*n5d6o7             |
| r+ol-a*nd6o7              |
| r+ol-a*ndo7               |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
+---------------------------+
10 rows in set (0.00 sec)

STRIPPING NUMERICS RESULT

mysql> SELECT @st;
+------------+
| @st        |
+------------+
| r+ol-a*ndo |
+------------+
1 row in set (0.00 sec)

mysql>

STRIPPING ALPHANUMERICS

SET @st='r1+o2l-3a4*n5d6o7';
SELECT (@st:=REPLACE(@st,ch,'')) FROM
(
    SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
    (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
    (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
) alphanumeric;
SELECT @st;

STRIPPING ALPHANUMERICS EXECUTED

mysql> SET @st='r1+o2l-3a4*n5d6o7';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@st:=REPLACE(@st,ch,'')) FROM
    -> (
    ->     SELECT (x*10+y) ndx,SUBSTR(chars,x*10+y,1) ch FROM
    ->     (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
    ->     (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) X,
    ->     (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    ->     UNION SELECT 5 x UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Y
    ->     WHERE SUBSTR(chars,x*10+Y,1)<>'' ORDER BY SUBSTR(chars,x*10+y,1)
    -> ) alphanumeric;
+---------------------------+
| (@st:=REPLACE(@st,ch,'')) |
+---------------------------+
| r1+o2l-3a4*n5d6o7         |
| r+o2l-3a4*n5d6o7          |
| r+ol-3a4*n5d6o7           |
| r+ol-a4*n5d6o7            |
| r+ol-a*n5d6o7             |
| r+ol-a*nd6o7              |
| r+ol-a*ndo7               |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-a*ndo                |
| r+ol-*ndo                 |
| r+ol-*ndo                 |
| r+ol-*ndo                 |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+ol-*no                  |
| r+o-*no                   |
| r+o-*no                   |
| r+o-*o                    |
| r+-*                      |
| r+-*                      |
| r+-*                      |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
| +-*                       |
+---------------------------+
36 rows in set (0.00 sec)

STRIPPING ALPHANUMERICS RESULT

mysql> SELECT @st;
+------+
| @st  |
+------+
| +-*  |
+------+
1 row in set (0.00 sec)

mysql>

EPILOGUE

This SELECT query is meant to hold up to 100 characters for stripping

You can replace (SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L, with any list of characters you wish to strip from a user variable.

GIVE IT A TRY !!!


Moving SQL Server Agent Job Categories and Schedules

Another cool powershell utility that I use everyday for server migrations is Start-SqlServerMigration.ps1 written by Chrissy LeMaire

It is very adaptive with different options.

Eg. In your scenario for migrating jobs with schedule, you can use

  .PARAMETER MigrateJobServer

  Migrates all job server objects, including proxy accounts, job schedules, shared schedules, alert system, job categories, operator categories, alert categories, alerts, target server groups, target servers, operators, and jobs. Existing objects will not be deleted, and no -force option is available.

e.g. The PS below code will move all the databases (overwrite if the same name databases exists since it uses force parameter) from Source sqlserver\instance to Destination sqlcluster. It will exclude Northwind, pubs databases. It will migrate all the logins except nwuser, pubsuser, "corp\domain admins", will migrate all the sql agent jobs, will export the sp_configure options from source to destination server.

.\Start-SqlServerMigration.ps1 -Source sqlserver\instance -Destination sqlcluster -AllUserDBs -ExcludeDBs Northwind, pubs -IncludeSupportDBs -force -AllLogins -ExcludeLogins nwuser, pubsuser, "corp\domain admins"  -MigrateJobServer -ExportSPconfigure -UseSqlLoginSource -UseSqlLoginDestination


Can I use table aliases in a MySQL DELETE statement?

I don't like the USING syntax. The MySQL documentation shows the following as a way to declare which tables to delete from a join, which is almost identical to the SELECT syntax. You can use aliases of course:

DELETE t, p                  -- delete from both tables
                             -- this can be used as well:  t.*, p.*
FROM topics AS t
  JOIN posts AS p
    ON t.id = p.topic_id
WHERE t.id = 5599 ;

Also note that this will delete the topic with id=5599 only if there are (at least one) related posts. If you want to delete it anyway (even when there is no post), use LEFT JOIN instead.

For the record, your (not working) 1st statement is aliasing the tables in the wrong place. This should work:

DELETE FROM t, p
USING topics AS t INNER JOIN posts AS p
WHERE t.id=p.topic_id AND t.id = '5599';

or better, using proper JOIN syntax:

DELETE FROM t, p
USING topics AS t INNER JOIN posts AS p     -- or: LEFT JOIN
   ON t.id = p.topic_id
WHERE t.id = 5599;


Can I load a CSV into a stored procedure that inserts into multiple tables?

Here is my thought:

Create a table with all the fields that exist in the csv file.
Create a trigger (After insert on the table created in step 1) that calls your procedure that you already have, or let the trigger itself insert new values to the corresponding tables.
Load the CSV file to the table you created in step 1
The other tables will be populated automatically (Nothing to do here)
Drop the table that you created in step 1


SQL Server Installation Error: Object reference not set to an instance of an object

The Details.txt log shows an error of:

(01) 2018-02-07 01:38:44 Slp:     Inner exception type: System.OverflowException
(01) 2018-02-07 01:38:44 Slp:         Message:
(01) 2018-02-07 01:38:44 Slp:                 Arithmetic operation resulted in an overflow.
(01) 2018-02-07 01:38:44 Slp:         HResult : 0x80131516
(01) 2018-02-07 01:38:44 Slp:         Stack:
(01) 2018-02-07 01:38:44 Slp:                 at SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO* , SNI_ConnWrapper** )

As the first error that occurs, when it initially attempts to connect to the database engine. This might be the better error to chase down, since it seems to indicate something interfering with the connection. A quick search shows some similar with people running Lavasoft (Adaware, etc) Software - any chance you're running this?


Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs.

The relation is not in BCNF, because the left hand side of the two small FDs is not a superkey.

You can, however, losslessly decompose the relation to
(SubjectCode, SubjectName) and either
(StudentName, SubjectCode, #Exam, Grade)
 or
(StudentName, SubjectName, #Exam, Grade)

This decomposition gives you two BCNF relations and preserves all functional dependencies. This isn't always possible (you can always decompose a relation to 3NF, but not necessarily to BCNF).

2NF

If you want an example of 2NF (and not 3NF), your relation needs to contain transitive dependencies.

For instance, say you have a Score column. Intuitively Score->Grade since all exams with the same score should get the same grade (it would be rather unfair otherwise), but note that we cannot say Grade->Score since several scores can have the same grade (11% and 12% would likely be "Fail", for instance).

Now your relation is:

Gradings(StudentName, SubjectCode, SubjectName, #Exam, Score, Grade)

and you have a new form of redundancy since every time you enter a result with the same score as another Gradings record you also have to repeat the corresponding Grade. To get to 3NF you could therefore decompose to

ScoreGrades(Score,Grade)

with Score as the key, and

Scores(StudentName, SubjectCode, SubjectName, #Exam, Score)


Import a Oracle DMP file into a Fresh install of oracle

You'll need to create a user (or Schema) first

C:\>sqlplus system/password

SQL> create user CLIENT_TEST identified by client_test_password;
SQL> grant connect, unlimited tablespace, resource to CLIENT_TEST;
SQL> exit

Then you can use the fromuser= and touser=  IMP switches to import the data into the new user/schema:

C:\>imp system/password FROMUSER=ABCDE TOUSER=client_test file=c:\database.dmp full=yes

Hope that helps!


Pg_upgrade failed - what does "re-initdb" mean?

The error means that the failed upgrade has left /var/lib/postgresql/9.3/main/ in an unusable state, broken and half upgraded. To attempt to upgrade again you must first remove it, then re-create it by running initdb like you did to create it the first time.

Look at pg_upgrade_dump_27132.log and pg_upgrade_server.log to see why the upgrade failed. Re-running it without changing anything is not likely to make any difference.


Is Network I/O included in the timings for "RPC complete" seen in SQL Profiler?

Network I/O is included in the rpc_completed duration*, so I would expect you'll see improvement in the workload you've described.

I enabled TCP/IP on my local SQL Server 2016 instance, and then ran a series of queries through a .NET application that uses an ORM.  Here's a comparison of sp_statement_completed and rpc_completed Extended Events targets for the exact same queries in that test run:

Each set of two rows is the two events for that same query.

Notice that rpc_completed is significantly higher than sp_statement_completed each time, since the latter doesn't include the network time (it's just how long a statement in a batch took to complete on the server).

For laughs, I then turned off TCP/IP, so that the queries would go through the named pipes provider:

Notice that rpc_completed is still higher, but the difference is significantly less, since there's no TCP overhead going on - the "round trip" is all in-process.

*My testing was done with Extended Events, but I would suspect the same to hold true for Profiler

To be clear, network time can be included in sp_statement_completed as well in the case of "streaming plans" (thanks to David Browne for pointing this out).

Sometimes, especially simple queries with lots of results, the results aren't spooled for sending after the statement is complete. Instead the results are sent as the plan runs. This is sometimes called a "streaming plan", and it's what you'll get with a simple nested loop join or select * from a large table.


I won't post the final answer to your homework exercise but you should become familiar with the SELECT TOP(1) ... ORDER BY construct. For example, the query below could be used to get the birth date of the oldest employee.

SELECT TOP(1) BirthDate
FROM Employees
WHERE BirthDate IS NOT NULL
ORDER BY BirthDate


This is a typical 1 to many relationship (or parent child relationship). The parent would have the fixed infromation and the child would have the many varying information.
The key of the parent must appear in the child.
Example:

Receipt : {ReciptID, Date}
RecieptItem  : {ItemID, CustomerPrice, Quantity,TotalPrice,ReceiptID, Status, Date}
Item:{ItemID, ItemName, Price}

Now each Recipt can have 0,1 or more Recipt Items.
Each Item is references on 0,1, or more ReciptItems.

The column ReceiptID in ReceiptItem is referred to as a Foreign Key. A constraint could be built to make sure that every ReciptID in ReciptItem corresponds to a ReceiptID in parent Receipt table.

Example ERD:


Is it possible in a view to reference a linked server implicitly rather than explicitly?

CREATE SYNONYM dbo.FooView FOR [LinkedServer].Database.dbo.FooView

Then just use dbo.FooView in your SPs and they can be the same between production and dev.

Note that this is only for SQL 2005 and up.

A solution for SQL 2000 might be to use views that ONLY SELECT * FROM [LinkedServer].Database.dbo.FooView with no other code in them. You could have a meta-table with the list of these view names and corresponding named sources. Then, write an SP that loops through the table and (re)creates the views as needed. Then, if you want to change where the views point, you run your SP with the new linked server name as a parameter (or keep it in the meta-table and update that) and all your dependent objects will silently point to the new location now. As long as the column names, positions, and data types are the same, it will work just fine.


Here's how I think I would approach the issue:

TABLE message
    id number,
    message varchar2(8000),
    sent_by_user number,
    etc...

TABLE inbox
    id number,
    user_id number,
    message_id number,
    etc...

Then, when sending to a group, I would go ahead and add an entry to each user's inbox with a pointer at the message. This way you aren't duplicating the message content, but you simplify the system by allowing users to perform their own actions on group messages without affecting the group message for the group as a whole.

Not sure it is a complete answer to your issue, but I would go this route since it means that message management is always a user-level issue; only during send operations do groups need to enter the equation.


To determine which queries are pulling objects into the buffer, use a server side trace or a tool like SQL Server Profiler. This will capture each query that is being ran on the database server, as it is being executed.

You didn't say which version of SQL Server, but if 2005 or higher, I'd strongly recommend reading Aaron Bertrands Determine SQL Server memory use by database and object post. The first query returns the databases which have objects in the buffer, and the number of pages and space they are consuming. Find the top database, and use the second query to determine which of the databases objects are taking up most of the buffer.

If you are on SQL Server 2008 or higher AND CPU is not a bottleneck AND you have memory pressure and can't get more memory added, I'd also review Edwin Sarmiento's Implementing Data Compression in SQL Server 2008. You may try data compression on a few of the top objects from the memory by object query. Compressed data takes up less space on both the storage substem and in the memory buffer, so you can fit more data pages in memory. When more data pages fit in memory, it reduces the number of reads from / writes to disk. As Edwin describes: "Data compression reduces the storage costs and increases query performance by reducing I/O and increasing buffer-hit rates." The tradeoff is a slightly higher CPU Utilization.


Sync data near real time between 2 databases on 2 different servers

It sounds like you want to make a copy of the data from server 'A' on server 'B' that can be can be used as reference data.  The data on server 'B' should be refreshed from server 'A' as often as possible.  Presumably, you don't need to make changes to the data once it is on server 'B'.

I'd recommend using replication to update server 'B' on a schedule; if server 'A' is down, no replication will happen, however once server 'A' comes back online, server 'B' will be brought up to date at the next scheduled replication event.


Storing IPv4 and IPv6 hosts and networks

First: there is no equivalent of the inet type of PostgreSql in MySQL yet.

You should just use the string type to store all kind of IP addresses.
if you need them all in a certain format, convert them before inserting them into the DB.

If you need to do calculations with them, as you mentioned INET_ATON() and INET_NTOA() are the needed functions to be used.


SQL Query: Join Tables using two partial string matches

Not ultra familiar with Access, but I'm pretty sure you have LEFT and RIGHT functions available to you.  Something like this seems likely to work:

    ON  RIGHT( Table2.[Booking], 9 ) = RIGHT( Table1.[Booking], 9 )
    AND LEFT( Table2.[Container], 10 ) = LEFT( Table1.[Container], 10 );


The XE event being used is leading you incorrectly to think the trigger is actually compiling every execution.  There are two extended events query_pre_execution_showplan and query_post_compilation_showplan that have similar descriptions, but differ by one important word:

query_pre_execution_showplan

  Occurs after a SQL statement is compiled. This event returns an XML
  representation of the estimated query plan that is generated when the
  query is optimized. Using this event can have a significant
  performance overhead so it should only be used when troubleshooting or
  monitoring specific problems for brief periods of time.

query_post_compilation_showplan

  Occurs after a SQL statement is compiled. This event returns an XML
  representation of the estimated query plan that is generated when the
  query is compiled. Using this event can have a significant
  performance overhead so it should only be used when troubleshooting or
  monitoring specific problems for brief periods of time.

The events aren't exactly the same in description and occur at different times from further testing using your repro.  Using a much larger event session definition, it is easy to see where compilations actually are happening.

Here you can see the first compilation happening for the insert statements as prepared plans being auto-parameterized in the green box.  The trigger is compiled in the red box and the plan is inserted into the cache as shown by the sp_cache_insert event.  Then in the orange box the trigger execution gets a cache hit and reuses the trigger plan for the second INSERT statement in the batch, so it's not compiling every execution of the INSERT command and the plan does get reused as you can see with the sp_cache_hit event for the trigger.

If we run the two INSERT statements individually again after the first execution the trigger doesn't compile again as shown in the events below:

Here the first statement encounters a cache hit for the prepared auto-parameterized version of the statement in cache but a miss for the adhoc batch that was submitted.  The trigger gets a cache hit and doesn't compile again as shown in the red block of events.  The green block of events repeats this behavior for the second INSERT statement run as a separate batch. However, in every case you still see the query_pre_execution_showplan event firing which I can only attribute to the difference in being optimized vs compiled in the event description, but the trigger isn't compiling for every execution as shown by these series of events.


Filter records that have a certain status

Try the following query (for starters):

select
  P.id
, P.name
, P.prices
, R.revision_status
, R.id
from Product P
  join Revisions R on P.revision_id = R.id
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name
    having Product.name = P.name
  ) > 1
order by P.id, R.revision_status ;

--
-- result
-- "name" of the Product with the ID 4 changed to: "Produkt2"
-- before executing the query
--
+------+----------+--------+-----------------+------+
| id   | name     | prices | revision_status | id   |
+------+----------+--------+-----------------+------+
| 1    | Produkt1 | 10     | 1               | 1    |
| 3    | Produkt1 | 2      | null            | 3    |
| 4    | Produkt2 | 42     | 1               | 4    |
| 5    | Produkt2 | 43     | null            | 5    |
| 7    | Produkt3 | 1      | 1               | 7    |
| 8    | Produkt3 | 3      | null            | 8    |
| 9    | Produkt3 | 4      | null            | 9    |
+------+----------+--------+-----------------+------+
7 rows in set (0.01 sec)

-- MySQL 5.7

If this gives you the required result, maybe we should cover the (revision_status) cases '1' and 'null' explicitly, by refactoring the WHERE clause as follows:

select ... from ... -- same as above
where R.revision_status <> '0'
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = '1'
  ) >= 1
  and (
    select count(*)
    from Product
      join Revisions on Revisions.id= Product.revision_id
    group by Product.name, Revisions.revision_status
    having Product.name = P.name and Revisions.revision_status = 'null'
  ) >= 1
order by P.id, R.revision_status ;

-- ... = 'null' due to your test data, maybe this needs IS NULL in reality.


I have bad news with regard to this table: MySQL does not support Partitioning of a Table with Foreign Keys

According to MySQL 5.7 Docs on Partitioning Limitations

  InnoDB storage engine.  InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.


Either way could be correct.

Choose 1 table if you want to make it difficult to change the number of reviews possible.  It may be counter-intuitive to want to make something difficult, but if it would actually be a bad idea to make it 1:n, then building in some pain to make the change has some logic.

But, you almost certainly want to go with two tables for long term flexibility.  If it was me, I'd put a trigger on there to enforce the 'one review per methodology statement' rule.  If that changes, just modify or drop the trigger.

One question to keep in mind, though:  if you go to multiple reviews, how would you resolve status codes that conflict?  For instance, what if you had one code 4 and one code 5 for the same methodology statement?

UPDATE:

...or you can do the much better thing and use a unique constraint as @Max Vernon suggests.


How to create a graph connecting predecessors and successors in time from a timeseries table in PostgreSQL 10?

SELECT t1.id
     , t2.id
FROM sensor_time_series t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id
                                 AND t1."time" < t2."time"
LEFT JOIN sensor_time_series t3 ON  t1.sensor_id = t3.sensor_id
                                AND t1."time" < t3."time"
                                AND t3."time" < t2."time"
WHERE t3.id IS NULL;

or (thanks to Lennart for the idea)

SELECT t1.id
     , t2.id
FROM ( SELECT id
            , sensor_id
            , lead("time") over(partition by sensor_id order by time) AS leading
       FROM sensor_time_series ) t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id
                                 AND t1.id != t2.id
WHERE t2."time" = t1.leading;

In the last query the join/where conditions can be freely moved between those sections for the best visibility.

fiddle


There would not be a problem if the table variable only ever held one value.  With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company.

Process A reads the destination, finds no row, and inserts the value '1'.  It holds an exclusive row lock on value '1'.  Process B reads the destination, finds no row, and inserts the value '2'.  It holds an exclusive row lock on value '2'.

Now process A needs to process row 2, and process B needs to process row 1.  Neither process can make progress because it requires a lock that is incompatible with the exclusive lock held by the other process.

To avoid deadlocks with multiple rows, the rows need to be processed (and tables accessed) in the same order every time.  The table variable in the execution plan shown in the question is a heap, so the rows have no intrinsic order (they are quite likely to be read in insertion order, though this is not guaranteed):

The lack of consistent row processing order leads directly to the deadlock opportunity.  A second consideration is that the lack of a key uniqueness guarantee means that a Table Spool is necessary to provide correct Halloween Protection.  The spool is an eager spool, meaning all rows are written to a tempdb worktable before being read back and replayed for the Insert operator.

Redefining the TYPE of the table variable to include a clustered PRIMARY KEY:

DROP TYPE dbo.CoUserData;

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL PRIMARY KEY CLUSTERED,
    MyValue integer NOT NULL
);

The execution plan now shows a scan of the clustered index and the uniqueness guarantee means the optimizer is able to safely remove the Table Spool:

In tests with 5000 iterations of the MERGE statement on 128 threads, no deadlocks occurred with the clustered table variable.  I should stress that this is only on the basis of observation; the clustered table variable could also (technically) produce its rows in a variety of orders, but the chances of a consistent order are very greatly enhanced.  The observed behaviour would need to be retested for every new cumulative update, service pack, or new version of SQL Server, of course.

In case the table variable definition cannot be changed, there is another alternative:

MERGE dbo.CompanyUser AS R
USING
    (SELECT DISTINCT MyKey, MyValue FROM @DataTable) AS NewData ON
    R.CompanyId = @CompanyID
    AND R.UserID = @UserID
    AND R.MyKey = NewData.MyKey
WHEN NOT MATCHED THEN
    INSERT
        (CompanyID, UserID, MyKey, MyValue)
    VALUES
        (@CompanyID, @UserID, NewData.MyKey, NewData.MyValue)
OPTION (ORDER GROUP);

This also achieves the elimination of the spool (and row-order consistency) at the cost of introducing an explicit sort:

This plan also produced no deadlocks using the same test.  Reproduction script below:

CREATE TYPE dbo.CoUserData
AS TABLE
(
    MyKey   integer NOT NULL /* PRIMARY KEY */,
    MyValue integer NOT NULL
);
GO
CREATE TABLE dbo.Company
(
    CompanyID   integer NOT NULL

    CONSTRAINT PK_Company
        PRIMARY KEY (CompanyID)
);
GO
CREATE TABLE dbo.CompanyUser
(
    CompanyID   integer NOT NULL,
    UserID      integer NOT NULL,
    MyKey       integer NOT NULL,
    MyValue     integer NOT NULL

    CONSTRAINT PK_CompanyUser
        PRIMARY KEY CLUSTERED
            (CompanyID, UserID, MyKey),

    FOREIGN KEY (CompanyID)
        REFERENCES dbo.Company (CompanyID),
);
GO
CREATE NONCLUSTERED INDEX nc1
ON dbo.CompanyUser (CompanyID, UserID);
GO
INSERT dbo.Company (CompanyID) VALUES (1);
GO
DECLARE
    @DataTable AS dbo.CoUserData,
    @CompanyID integer = 1,
    @UserID integer = 1;

INSERT @DataTable
SELECT TOP (10)
    V.MyKey,
    V.MyValue
FROM
(
    VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, 5),
        (6, 6),
        (7, 7),
        (8, 8),
        (9, 9)
) AS V (MyKey, MyValue)
ORDER BY NEWID();

BEGIN TRANSACTION;

    -- Test MERGE statement here

ROLLBACK TRANSACTION;


At my wits end trying to create my very 1st MySQL stored procedure

When I gave up on creating the stored procedure through SquirrelSQL and installed MySQL GUI Tools, then I was able to create the procedure with the exact same source, by using Catalogs, selecting my schema, switching to the Stored procedures tab, and clicking Create Stored Proc button at the very bottom.


Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?

I suggest you create a custom database role by right-clicking the database role folder.

That will bring up the Database Role that you can then customize. On the first screen you can give the role the name and owner of your choosing. You also can add the user you want to be a member of this role.

Once that is done, you can click on Securables so you can then choose what to grant.

Add Objects will show up and I suggest picking the "All objects of the types..." option and click OK.

In the Select Object Types, scroll down to find Schemas, check the box, and then click OK.

Now you will see a list of schemas so choose the schema(s) that has the objects you want to grant permissions to and it will show the permissions for that schema below.

For this example, I chose dbo schema. I resized the default screen to show more at once.

In this case I would most likely choose to grant Alter because according to Microsoft, "When granted on a scope, ALTER also bestows the ability to alter, create, or drop (emphasis mine) any securable that is contained within that scope." Based on the infographic about database permissions, located here and below granting Alter on schema will grant alter on Aggregate, Default, Function, Procedure, Queue, Rule, Synonym, Table, and View:Since I am at this stage, for this same role, I would probably also grant Execute as well so the role member could also execute any stored procedure so when new tables or stored procedures are added to the database, I would not have to modify any security to allow the new objects to work like the existing objects. So the selections would look like this once complete:

I could have taken it a step further and also granted Select, Insert, Update, and Delete, and it most likely would have covered everything the role would require to read, insert, delete, and update any table as well as execute any stored procedure without needing to grant all the other permissions that the db_ddladmin role grants. By doing this at the schema level it can ease the management of security in some ways, especially when a particular user needs to have a lot of if not all access at the schema level, but also satisfies the requirement of not granting alter to the whole database. If there were other permissions this configuration grants that the role does not need, you could then go add DENY permissions as needed.


XtraBackup Restore Full Backup OK but I can't initialize mysqld

In Manual itself it is explained that

Decompress backup

$ innobackupex --decompress /data/backup/2013-08-01_11-24-04/

When the files are uncompressed you can prepare the backup with the --apply-log option:

$ innobackupex --apply-log /data/backup/2013-08-01_11-24-04/

You should check for a confirmation message:

130802 02:51:02 innobackupex: completed OK!

Now the files in /data/backups/2013-08-01_11-24-04 is ready to be used by the server.


Reasons For Slowness in SQL Server 2008 R2 Express Edition

Without some examples of queries you're experiencing the slowness with, and their execution plans there's not much we have to go off of.
Other good monitoring tools you can use to try to capture something is sp_WhoIsActive and sp_Blitz. You can also check the most commonly occuring Wait Stats to see if there's anything abnormal there.


Permission denied on function after schema copy

After hours of reading of documentation I found that PosgreSQL first searches pg_catalog schema even before all schemas listed in search_path.

So checked pg_catalog catalog and I don't know why but my function was defined there and user postgres was the owner of the function.

I just deleted my function from pg_catalog and it's OK and works perfect as expected.

I still don't know how the function got there in pg_catalog.


PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full?

Here's what You can do to move your data to the new tablespace :

Create the new tablespace pointing to the new location.

postgres=# create TABLESPACE movespace LOCATION '/home/postgres_tbsp';
CREATE TABLESPACE

Alter table or database to move to the new tablespace.

postgres=# ALTER DATABASE slonmaster SET tablespace movespace;
ALTER DATABASE

This process could last long as it moves all the data in the table or database to a new location.


Your script could look like this:

BEGIN;
\i file1.sql
\i file2.sql
COMMIT;

Or you could do something like this:

cat file1.sql file2.sql | psql -1 -f -


SQL Server 2016 DB is SUSPECT - cannot do anything with it!

The SQL Error Log message, "During undoing of a logged operation in database 'MyDB', an error occurred at log record ID (541478:273336:241). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database." proved to be accurate, AFTER freeing up significant quantities of transaction log drive space.

Thanks to all who so swiftly applied their expertise in answer to this (never seen before by me) problem.


Query to get ORACLE_HOME path in Oracle 11G

There is  sys_context function

that works for Oracle 12c

select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;

This does not work for lower versions of Oracle.
For 11g and lower versions, I don't know a simple method.

PS: I read @Balasz' answer and now I know a simple answer for version 11g.


Installation of PostgreSQL-11: Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable

This error is because the Official PostgreSQL PPA for Cosmic is currently building against libllvm6.0. That's an import from Debian Sid, currently Ubuntu is building against the newer libllvm7.0. To get access to this package you'll have to add the universe to your sources.list.

sudo add-apt-repository "deb http://archive.ubuntu.com/ubuntu $(lsb_release -sc) universe"


Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?

Fabian Pascal identifies four pragmatic criteria for choosing a key: familiarity, irreducibility, stability and simplicity. He also notes that sometimes you must make trade-offs.

During the logical design phase you will identify all the candidate keys for an entity.

During the physical design phase you will decide which of them will be the primary key (the one usually used in referential integrity, in other words, foreign keys). If you don't think any of the candidate keys are suitable for the primary key then you choose to use an arbitrary key, and then decide what mechanism will be used to generate the arbitrary key, perhaps the application generates it, perhaps the server does (identity, guid with newid, sequence, something else). If your systems will be multi-master in some way then application-generation might be better, as most of the server-side mechanisms don't handle this.

The choice of primary key is determined by size and type - you will be joining on it, so you want it to be small.

During the physical phase you will also be doing index design, which includes deciding which index will be the clustered index. You want that to be small as well, since the clustered index key is included in all non-clustered indexes. You want it to be unique, otherwise SQL Server will add a uniquifier, which has a small impact on performance.

On the other hand, this is not the 70s. We now have computers that don't fill entire warehouses and take all week to run the payroll. If won't kill your performance to use a guid or a char(8) either as a clustered key or as a primary key. Unless you are UPS or AIM Healthcare (two winners of WinterCorp's 2005 TopTen survey).

Edit: I just had this link arrive in my mailbox. Simple talk Primary Key Primer for SQL Server

Edit: Joel's comment below is very important. Natural keys often aren't stable. Even government-issued keys (tax numbers, drivers licence numbers) that should be static can change. For example, ask Mrs. Hilda Schrader Whitcher about her two SSNs. You control the arbitrary key so you control its stability.

In my (admittedly limited) experience, I can't remember a time we used a natural key. I've done work in healthcare, where there were government-issued id numbers (like NZ's Community Services Card) but not every person had them, so they were not suitable for PKs.


I used the following setup to run some tests against.

CREATE TABLE GeographyPoint (
    ID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
    GeoPoint GEOGRAPHY NOT NULL,
    LegendTypeID INTEGER NOT NULL
    );

INSERT INTO GeographyPoint (GeoPoint, LegendTypeID)
SELECT TOP 1000000
    Geography::Point(RAND(CAST(NEWID() AS VARBINARY(MAX))) * 2,RAND(CAST(NEWID() AS VARBINARY(MAX))) * 2,4326),
    CAST(RAND(CAST(NEWID() AS VARBINARY(MAX))) * 25 AS INTEGER)
FROM Tally;

CREATE INDEX GP_IDX1 ON GeographyPoint(LegendTypeID) INCLUDE (ID, GeoPoint);
CREATE SPATIAL INDEX GP_SIDX ON GeographyPoint(GeoPoint) USING GEOGRAPHY_AUTO_GRID;

This gives a table of 1,000,000 random points with a 2 x 2 degree spread.
After trying a few different options on it, the best performance I could get was forcing it to use the spatial index.  There was a couple of ways to achieve this.  Dropping the index on LegendTypeID or using a hint.You will need to decide which is best for your situation.  Personally I don't like using index hints and would drop the other index if it is not required for other queries.

The queries stacked up against each other

DECLARE @point geography;
SET @point = geography::Point(1,1,4326);
/*
Clustered index scan (PK)
 SQL Server Execution Times:
   CPU time = 641 ms,  elapsed time = 809 ms
*/
SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint]
WHERE 18 = [LegendTypeId]
ORDER By distance ASC
OPTION(MAXDOP 1)
/*
Index Seek NonClustered (GP_IDX1)
 SQL Server Execution Times:
   CPU time = 2250 ms,  elapsed time = 2806 ms
*/
SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint]
WHERE [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)

/*
For the next 2 queries
Clustered Index Seek (Spatial)
 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 11 ms
*/
SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint] WITH(INDEX(GP_SIDX))
WHERE [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)

DROP INDEX GP_IDX1 ON [GeographyPoint]

SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint]
WHERE [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)


Keeping default max memory 2147483647 MB value in SQL Server, a bad practice?

Some details to clear things up

  Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic.

No, it's not written it is a good practice it says it's recommended and I am sure MS books online cannot write it as good, this is because of fact that environment varies and what is good for one environment might not suit other. Its mostly good to have restriction on something which is heavily consumed although managed efficiently. I consider a good practice to define max server memory on system because it will restrict buffer pool and will tell SQL Server how much max a buffer pool can grow although SQL Server can consume memory outside buffer pool/max server memory setting if it heavily uses Third party DLL's extended stored procs and Linked servers.

Page file is used by Windows to hold temporary data which is swapped in and out of physical memory in order to provide a larger virtual memory set. Page file largely depends on how much memory OS is committing and changes accordingly as per min and max value set. if you want to monitor page file you must rely on perfmon counters. Please read this MSDN Blog

Memory: Committed Bytes --Number of bytes of virtual memory that has been committed. This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit-- Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage-- Percentage of the paging file committed

Paging File: % Usage Peak-- Highest percentage of the paging file committed

  Also, why should you have a min value? I know that a too low value can prevent the SQL-Server from starting and I know that SQL-Server keeps stuff in memory so doesn't try to release it's cache.

Min Server memory is minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server. This comes into active picture when windows is facing memory pressure in such case it will raise Low Memry Notification Flag. SQLOS will respond to it, it would ask SQL Server to trim its memory consumption and free its cache then SQL Server will non preemptively trim down till its min server memory value. If pressure is too high SQL Server might not be able to react fast and then SQL Server process would be paged out. Remember min server memory does not say that when SQL Server starts it would minimum take this much memory if not required, SQL Server will start up consuming much less than min server memory.

Other use of Min server memory is if you do not want SQL Server to be paged out to disk because of some misbehaving OS/Third party driver. You set max server and min server memory to almost same value and give SQL Server service account Locked pages in memory privilege and in this case even if OS faces memory crunch SQL Server would max trim down to min server and if OS memory crunch is still there OS process would be paged out and would become extremely slow . But this is bad thing to do and I would not recommend


SELECT L.Id AS Id, L.Dimension AS Dimension,
       sq1.In_Value, sq1.In_Count, sq1.In_Dist,
       sq2.Out_Value, sq2.Out_Count, sq2.Out_Dist
FROM Locations AS L
LEFT JOIN (
           SELECT C.Destination,
                  SUM(C.Value) AS In_Value,
                  COUNT(C.Destination) AS In_Count,
                  SUM(C.Distance_KM) AS In_Dist
           FROM Connections AS C
           GROUP BY C.Destination
         ) AS sq1 ON L.Id = sq1.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count,
                  SUM(C.Distance_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
         ) AS sq2 ON L.Id = sq2.Origin

fiddle


Denormalize only as a last resort

Do not denormalize your table design for imaginary performance problems. Avoid falling into premature optimization.

Design a proper structure. Generate fake data to populate the tables. Run tests under circumstances akin to your deployment scenario. If a significant performance problem is proven:

Research the nature of the problem using the EXPLAIN & ANALYZE features.

Examine use of indexes. Verify that existing indexes were used in queries as expected; if not, re-write your query with a different approach. Add an index where needed.

Study how to tune Postgres.

Learn to size caches.
Learn other ways to tune your Postgres server.
Review some conference talks, with online video or slides.
Search the official Postgres mailing lists.
Attend a Postgres user group meeting, or a Postgres conference.
Search DBA Stack Exchange.

Trying moving some logic from your app to the database server by writing functions in a language such as PL/pgSQL, where all the data is local to the executing code.
Install larger cache unit on your RAID device. Consider tuning the cache to allocate more to reading vs writing, as appropriate to your situation; they often default to 50-50.(By the way, be certain your RAID’s write-cache is battery-backed to avoid ruining your database or other files.)

Or, if using ZFS instead of RAID, learn to tune that to prioritize your database on faster drives.

Consider the use of partitioning to physically segregate rows in storage. Consider adding faster storage such as enterprise-quality solid-state storage on which you store your most frequented data, or store the data you most want to access quickly such as data used by the Big Cheese.

Partitioning comes with restrictions and trade-offs. So be sure you have no better way to solve your performance problem.
If you do go with partitioning, be aware that recent versions of Postgres (10, 11, 12 as I vaguely recall) have been dramatically improved with improvements with declarative partitioning.

Hire a Postgres expert for consultation in your testing and tuning.

Only after having exhausted all avenues to fixing a proven performance problem should you consider denormalizing.

Postgres is a powerful enterprise-quality database system. Several million rows on modern hardware with sufficient RAM and wise indexing should be no problem at all.

On the other hand, if your different types of events represent different entities, then they should be kept in separate tables. How do we know if similar kinds of rows are different entities or not? Clues might be found in asking: Do they have mostly the same columns with the same semantics? Do your users ever want to display or report together? Might you ever want to aggregate (calculate count, average, median, etc) together?

Be aware that as a product with a long history dating back to the days when computer hardware was much more limited in capabilities and configurations that today’s hardware, Postgres by default has quite conservative settings on initial installation. For example, by default Postgres runs on an older Raspberry Pi! So anyone running a larger database on more capable hardware should be doing some tuning.