The question is not the same, but my answer is:  https://dba.stackexchange.com/a/159012/1876

Don't use Triggers; instead batch the data and batch the processing.  It is a lot more efficient than doing everything one row at a time, especially when a spike occurs.

Another issue... If you let "too many" connections get started, yes, the entire system can stall.  This is sometimes because MySQL (and the OS) are carefully parceling out resources (CPU, I/O, etc) to more clients than the resources (cores, etc) can handle.  Throughput may be fine, but latency suffers terribly.


MongoDB 3.6 create local Shard db without replica

Reference:

Sharded Cluster Components
Deploy Sharded Cluster
Configuration File Options

There are few things wrong in your approach. I suggest you first understand the concept and test every step. For example once you create your config server make sure you can connect to your Mongod and then only move to next step. Same is true for each of your shard.

My answer is pointing to those and some more reading material so you can do this by yourself.

First of all starting from MongoDB 3.6 shards must be deployed as Replica set and not stand alone:

  A MongoDB sharded cluster consists of the following components:

  shard: Each shard contains a subset of the sharded data. As of MongoDB
  3.6, shards must be deployed as a replica set.

  mongos: The mongos acts as a query router, providing an interface between client applications and the sharded cluster.

  config servers: Config servers store metadata
  and configuration settings for the cluster. As of MongoDB 3.4, config
  servers must be deployed as a replica set (CSRS).

  For testing and development, you can deploy a sharded cluster with a
  minimum number of components. These non-production clusters have the
  following components:

  A replica set config server with one member.
  At least one shard as a single-member replica set.
  One mongos instance.

What you mentioned below is not possible. You will need Replica Set to use Sharding but for development/test you can have a one member Replica set.

  I'm following this guide, but i understood that the guide create much
  replica for config server and shard server and i don't want that.

You are using same name for Config Server, Shard1 and Shard2 Replicaset. You need to use different name and make sure mongod is running on separate port for all instances.

If all your Mongod is running in one host you need to mention the port in your configuration file. See example here.

See the documentation for using rs.initiate() here.

I do not see you adding your shards to the cluster. See this.


Seeing AWS PostgreSQL-on-Aurora snapshot sizes of 0 GB - is this normal for a db of ~800mb?

AWS support responded and indicated that the misleading snapshot size data is a known issue with snapshots for clusters. They intend to fix it, but do not have an ETA on the fix. The connection issue cleared up without making any changes to credentials or other code.


PostgreSQL converting undefined JSONB fields into a row dynamically

My question : Is there any way to do a select all over the keys of jsonb without specify manually the keys?

No, because there is no way to have a query return an undefined result set. However, if the table is not accepting new queries you can generate a dynamic sql statement.

SELECT FORMAT(
  $$ SELECT * FROM %I.%I CROSS JOIN LATERAL jsonb_to_record(%I) AS rs(%s); $$,
  'public',
  'v',
  'jsonb',
  array_to_string(
                (SELECT ARRAY(SELECT DISTINCT col FROM v CROSS JOIN LATERAL jsonb_object_keys(jsonb) AS t(col) ORDER BY col)), ' text , '
  ) || ' text'
);

Then run that query, or run \gexec in psql.

                                                    format
--------------------------------------------------------------------------------------------------------------
  SELECT * FROM public.v CROSS JOIN LATERAL jsonb_to_record(jsonb) AS rs(a text , b text , c text , d text);
(1 row)

test=# \gexec
 id |             jsonb              | a | b | c | d
----+--------------------------------+---+---+---+---
  1 | {"a": "4", "b": "5"}           | 4 | 5 |   |
  2 | {}                             |   |   |   |
  3 | {"a": "8", "c": "9", "d": "9"} | 8 |   | 9 | 9
(3 rows)

You may or may not want to build some type inference with jsonb_typeof back to pg types: keep in mind, you can never go back to integer or something, but you should be able to store the numbers as double precision.


Trigger function to update data from one table to another table with different servers in PostgreSQL

There are couple of ways to deal with this using triggers

FDW wrapper as stated by a_horse_with_no_name and use PLpgSQL
use an un-trusted PL language such as PLpython.

FDW: depending on the version of PG can be fast or extremely slow.

PLpython requires python language with psycopg library to be installed.

I like to use plpython as its easier to add logic if the connection between the two servers becomes broken,  When the connection is broken use another table to hold onto the pending changes,  then use PgAgent task to retry updating the data.


How to change the data directory on Postgresql 9.1 on Ubuntu

If you create a cluster in Debian/Ubuntu without specifying the owner (option --user of pg_createcluster) then you have to be sure that the directory you specify is accessibile in r/w to the postgres user. Please note that all parent directories should also be accessibile to the same user with bit x (list directory content).

Otherwise, you may specify a different owner using option --user. This should create a cluster ran by that user instead of postgres.

In order to check if postgres user may access that directory, execute this command:

sudo su - postgres -c 'touch /home/fccoelho/Documentos/databases/postgresqldata/test'

and check for any error.


Obtain the sum of count of multiple schema and tables in one query in mysql

Here's an ugly solution:
SELECT MAX(tmp.col1) as col1,
       MAX(tmp.col2) as col2,
       MAX(tmp.col3) as col3,
       MAX(tmp.col4) as col4,
       MAX(tmp.col1) + MAX(tmp.col2) + MAX(tmp.col3) + MAX(tmp.col4) as total
  FROM (SELECT COUNT(*) AS col1, NULL as col2, NULL as col3, NULL as col4
          FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, COUNT(*) AS col2, NULL as col3, NULL as col4
          FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, COUNT(*) AS col3, NULL as col4
          FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, NULL as col3, COUNT(*) AS col4
          FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) tmp

This will give you something like:

col1
col2
col3
col4
total

10
20
30
40
100

It's not very efficient, but it gets the job done.


SSIS proxy/credentials not working from within SQL Agent job step

I appreciate the feedback on this, and I hope this helps people in the future. I finally narrowed down the true root cause of the problem. I had not provided enough detail around the fact that an Excel file was being used. After disabling everything in the package, If I only enabled the Data flow task using the Excel source, I would get the failure, but only when using the SSIS proxy (which launched the job using the Domain1\NewUser account). If I set the job step to use the SQL Server Agent account, everything would work fine.

After some time away from the problem, I tried logging into the server with the NewUser1 credentials, and ran Excel for the first time. It prompted me for initials and then I closed the program. I had also added the account to the server's local Administrators group so I could RDP.

Then I ran the job using the SSIS proxy and everything worked fine. When I removed the account from the local Administrators group it would fail again, but I determined it was the local policy "logon as a batch job" that was being granted in that membership.

Here is what I have learned from this experience:

SQL Credentials can only impersonate a user account, NOT a group.
SSIS proxy is a valid solution for granting necessary privileges for
a batch job. Excel (and possibly other applications) may need to be
launched once using the related credentials, just to complete
application settings in the user's profile on the server. Logon as a
batch job is required for a proxy that runs DTExec to launch a
package from the file system. Excel sources are problematic, and the
OLE DB driver may report this problem when it isn't a layout/format
issue:

  Hresult: 0x80004005 Description: "External table is not in the
  expected format."


Doubts in a Glenn Berry Query ( CPU per Database )

On first glance this looks to me to approximate CPU per database, over the history of sys.dm_exec_query_stats (so usually since the last restart), but only for plans that are currently in the cache. It also relies on a plan cache attribute, dbid, which means that was the context for the query, but not necessarily that that was the database that caused the work. For example, guess where all the CPU gets reported for this query:

USE tempdb;
GO
SELECT CONVERT(DATETIME, CONVERT(CHAR(10), CONVERT(DATE,
  CONVERT(DATETIME, o.create_date)), 120))
FROM msdb.sys.all_objects AS o
CROSS APPLY model.sys.all_columns AS c;

I'll give you a hint: It's not msdb or model.

So, it should be used as a ballpark, but there are no guarantees that it reflects 100% of reality 100% of the time. The more often you query it (e.g. have some automated job that stores snapshots of it every n minutes), the more accurate it will be, but unless you have applications that treat each database like impenetrable silos, it will still be influenced by database context rather than the actual source of queries and data.


How to identify overflow rows in DB2 LUW

You are likely correct to suspect compression as the cause of overflows. Having a clustered index by itself does not cause row movement outside of a reorg.

You can use the db2dart (Database Analysis and Reporting Tool) utility to dump formatted table pages; the "Record Type" field will identify which records have overflown. For example, run

db2dart yourdb /dd /tsi 2 /tn YOURTABLE /ps 0 /np 0 /v n

to dump all pages (/ps 0 /np 0) from the table YOURTABLE (note the uppercase name) in tablespace 2.

Overflown record will appear like this:

        Slot 203:

           Offset Location = 1580  (x62C)
           Record Length = 12  (xC)

           Record Type = Pointer Record (tombstoned) (PUNC)

               Overflow Record Location:  Page 10  Slot 33

with their actual data in the new location:

        Slot 33:

           Offset Location = 3528  (xDC8)
           Record Length = 26  (x1A)

           OverFlow Record with Back Pointer

               Backpointer Location:  Page 7  Slot 203

           Compressed Record:  Row_Compressed

           Actual Record Length = 52  (x34)

           Record Type = Table Data Record (FIXEDVAR) (PUNC)

           Record Flags = 0

You can use the verbose flag (/v y) to include the actual row data in the report. For a large table you probably want to select a subset of pages by specifying a sensible value for /np.

Don't forget to FLUSH BUFFERPOOL ALL before running db2dart on an active database, otherwise the data on disk, where the utility reads them, may not reflect changes still in the bufferpool.


SQL Server tempdb rogue temporary tables

Following what @Damien_The_Unbeliever commented, yes those are Table variables only and if you are still observing them then it's evident that from whichever procedure those got created is still opened (or) not completed yet else those would have already been cleared from TempDB. To do a test try the code snippet and check against  System Databases > tempdb > Temporary Tables: you will see that the table variable is exist.

declare @tab table(col1 int);
waitfor delay '8:10';
insert into @tab values(123);

Once you cancel the query, it gets cleared from TempDB.


Slot Time Challenge - Doctor Appointment database schema

I would suggest an Appointment table which stores the current appointments for each doctor. We can add some constraints on this table which limit the appointment start times to even ten-minute times (e.g. 9.00, 9.10, 9.20) plus add some other common sense checks like EndTime after StartTime and doctor can't have two appointments starting at the same time. Assume that you'd also like doctors to only work between 9am and 5pm, because everyone needs some work-life balance.

CREATE TABLE Appointment (
    DoctorID char(1) NOT NULL,
    [Date] date NOT NULL,
    StartTime time(0) NOT NULL CONSTRAINT CHK_StartTime_TenMinute CHECK (DATEPART(MINUTE, StartTime)%10 = 0 AND DATEPART(SECOND, StartTime) = 0),
    EndTime time(0) NOT NULL CONSTRAINT CHK_EndTime_TenMinute CHECK (DATEPART(MINUTE, EndTime)%10 = 0 AND DATEPART(SECOND, EndTime) = 0),
    Status char(1) NOT NULL,
    UserID char(1) NOT NULL,
    Price int NOT NULL,
    CONSTRAINT PK_Appointment PRIMARY KEY CLUSTERED (DoctorID, [Date], StartTime),
    CONSTRAINT CHK_StartTime_BusinessHours CHECK (DATEPART(HOUR, StartTime) > = 9 AND DATEPART(HOUR, StartTime) < = 16),
    CONSTRAINT CHK_EndTime_BusinessHours CHECK (DATEPART(HOUR, EndTime) > = 9 AND DATEPART(HOUR, DATEADD(SECOND, -1, EndTime)) < = 16),
    CONSTRAINT CHK_EndTime_After_StartTime CHECK (EndTime > StartTime));
CREATE INDEX iDoctor_End ON Appointment (DoctorID, [Date], EndTime);

We can insert some data to this table to see what it looks like. Note that the third insert will fail because it is prevented by our constraint. The doctor can't have two appointments starting at the same time.

INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:10:00', 'P', '1', '0');
INSERT INTO Appointment VALUES ('A', '20170420', '09:20:00', '09:40:00', 'C', '2', '10');
INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:20:00', 'C', '2', '10');

Let's assume that you have a numbers table. If you don't many other people have described how to create one. If all else fails, this could create one for you but it's probably not the best way.

CREATE TABLE Numbers (Number int PRIMARY KEY CLUSTERED);
DECLARE @number int = 0;
WHILE @number < 1000
BEGIN
    INSERT INTO Numbers VALUES (@number);
    SET @number += 1;
END

Now if we want to see free slots for a particular doctor, all we need to do is specify which doctor, and how long the slot is that we're looking for:

DECLARE @doctorID char(1) = 'A';
DECLARE @length tinyint = 20;
WITH Slots AS (
    SELECT StartTime = DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))),
           EndTime = DATEADD(MINUTE, @length, DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))))
      FROM Numbers)
SELECT TOP 15 DoctorID = @doctorID,
    s.StartTime,
    s.EndTime
  FROM Slots AS s
  WHERE NOT EXISTS (SELECT 1
                      FROM Appointment AS a
                      WHERE (CONVERT(time(0), s.StartTime) < a.EndTime AND CONVERT(time(0), s.EndTime) > a.StartTime)
                        AND a.DoctorID = @doctorID
                        AND a.[Date] = CONVERT(date, s.StartTime))
    AND DATEPART(HOUR, s.StartTime) >= 9
    AND DATEPART(HOUR, DATEADD(MINUTE, -1, s.EndTime)) <= 16
ORDER BY s.StartTime;

That looks a little awkward, so if anyone can improve that date logic happy to take suggestions.

If a doctor wants a break, then enter the break as an appointment and it won't be available for booking.

Note that the table constraints don't enforce non-overlapping appointments. This is possible but it's more complicated. If this were my system I'd think about some system (e.g. trigger) to finally verify that the appointment doesn't overlap with an existing one at the time of insert, but that's up to you.


How does JOIN involving database across 2 servers store the intermediate data?

As Dan points out, it's dependent on the execution plan that gets generated, as the processing can occur on either end (the remote server or local server which executed the query). Usually you can determine which server it was actually processed on by seeing if there's a Remote Query vs a Remote Scan operation in your query's execution plan.
A Remote Query usually means the query is sent to the remote server to be processed, and the predicates will be performed remotely, resulting in less data being return back locally.
A Remote Scan generally results in all the data from the remote entities (e.g. the entire tables) to be returned to the local server first, then the query is processed, predicates are applied, etc, on the local server.
Evidently, Remote Query is usually a more efficient operator to have in your query's execution plan as opposed to a Remote Scan operator.
By the way, this is a great website with a quick description of the different query plan operators.
I've seen a lot of adhoc answers on query re-writes to try and fix plans with Remote Scan and force a Remote Query operation instead. I'm not positive on exactly what the SQL Engine is doing under the hood when it chooses one over the other, but I have consistently seen that when your query uses the primary key (and more likely resultantly the clustered index) field of the remote entities, as part of the predicates, it results in a Remote Query operation.


select all tables from dba_table except list

select owner, table_name from dba_tables
where (owner, table_name) not in (('HR', 'DEPT'), ('SCHEMA', 'TAB'));


mysql error 1265 data truncated for column

Not addressing the question, but addressing 2 bugs in your schema.

FLOAT(m,n) takes the decimal input, rounds to n decimal places in decimal, then converts to binary with another rounding error.
FLOAT (with or without (m,n)) can hold only about 7 digits of precision.  Hence, both numbers in your example lose precision at the low end.  As you found out, storing 3535929.559 into a FLOAT(20,3), then retrieving it will yield only 3535929.500.  This is because a FLOAT has only 24 "significant bits of precision".

There are two solutions for both bugs:

DECIMAL(m,n) -- (20,3) will still round to 3 decimal places, but that might be OK for your application.  But it does not have the extra rounding to binary nor the loss of precision.
DOUBLE (without (m,n)) -- This has about 16 digits of precision, more than enough for most applications.  And, even though the decimal input will be rounded to binary, it will be so far out that it is unlikely to cause errors.

Sizes...

FLOAT (with or without (m,n)) takes 4 bytes.
DOUBLE (with or without (m,n)) takes 8 bytes.
DECIMAL(20,3) takes 10 bytes.  Shrinking either number may save space.


In MySQL 8, can we make group replication db as readonly?

The simple answer to this would be ... MAYBE.
Single Primary mode has super_read_only on every node that is not Primary.
When it comes to Multi-Primary mode, one possible workaround would be to add nodes to the cluster that have a higher version of MySQL 8 than the Primary. Such a node should go into read-only mode automatically. Please note the paragraph of the MySQL 8 Multi-Primary Mode Docs under the heading Version Compatibility:

For optimal compatibility and performance, all members of a group should run the same version of MySQL Server and therefore of Group Replication. In multi-primary mode, this is more significant because all members would normally join the group in read-write mode. If a group includes members running more than one MySQL Server version, there is a potential for some members to be incompatible with others, because they support functions others do not, or lack functions others have. To guard against this, when a new member joins (including a former member that has been upgraded and restarted), the member carries out compatibility checks against the rest of the group.
One result of these compatibility checks is particularly important in multi-primary mode. If a joining member is running a higher MySQL Server version than the lowest version that the existing group members are running, it joins the group but remains in read-only mode. (In a group that is running in single-primary mode, newly added members default to being read-only in any case.) Members running MySQL 8.0.17 or higher take into account the patch version of the release when checking their compatibility. Members running MySQL 8.0.16 or lower, or MySQL 5.7, only take into account the major version.
In a group running in multi-primary mode with members that use different MySQL Server versions, Group Replication automatically manages the read-write and read-only status of members running MySQL 8.0.17 or higher. If a member leaves the group, the members running the version that is now the lowest are automatically set to read-write mode. When you change a group that was running in single-primary mode to run in multi-primary mode, using the group_replication_switch_to_multi_primary_mode() UDF, Group Replication automatically sets members to the correct mode. Members are automatically placed in read-only mode if they are running a higher MySQL server version than the lowest version present in the group, and members running the lowest version are placed in read-write mode.
For full information on version compatibility in a group and how this influences the behavior of a group during an upgrade process, see Section 18.7.1, “Combining Different Member Versions in a Group” .

You can also try using super_read_only on the desired node you want as read only, just make sure Group Replication doesn't start acting funny.


How do I keep developer's MySQL DB up to date with production?

For the love of Fortran, stop what you're trying to do!

You should have all your database schema, stored procedures, etc checked into a version control system rather than only in the database.

If the database is only used with a single app, then you'd generally want to put the schema in the same repository as the application's source code, otherwise you should put the in a separate repository. Some version control systems like git makes it easy to work with multiple repositories like with git submodules.

This way the developer had just to run a script that drops the database and reloads the schema. When you need to make changes to the schema, you should write a database migration script. This way the developers can upgrade their own developments database at their own leisure, rather than having you breaking their code while they're in the middle of other works.


Table check contraint to verify unique on specific value

Check constraints are not for enforcing uniqueness.
Unique function-based index can do the job (because Oracle accepts multiple NULL values on unique columns).

create table test1(ID_PROCESS   int , PERIOD int, CUSTOMER    int, STATUS varchar2(20));

create unique index idxu1_test1 on test1
    (case when STATUS ='COMPLETED' then PERIOD else null end,
     case when STATUS ='COMPLETED' then CUSTOMER else null end
     );

insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (1,1,1, 'invalid'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (2,1,1, 'invalid'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (3,1,1, 'invalid'); -- ok

insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (4,1,1, 'COMPLETED'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (5,1,1, 'COMPLETED'); -- ORA-00001: unique constraint violated


Calculating percentage growth month by month

Well, you're close:

I tweaked your statement, and came up with the following. I made one notable assumption - that you want the output sorted by month number, with the rollup value last. If you ultimately need to show a list that runs October of one year through September of the next, you'll need to put in a little more work.

SELECT Coalesce(OrderDate, 'Grand Total') AS OrderDate
      ,SumOrderPrice
FROM (
      SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate
            ,SUM(ORDERS.OrderPrice) as SumOrderPrice
            ,MONTH(ORDERS.OrderDate) as MonthNum
      FROM ORDERS
      WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
      GROUP BY
            MONTHNAME(ORDERS.OrderDate)
           ,MONTH(ORDERS.OrderDate) with ROLLUP
     ) AS T
ORDER BY COALESCE(MonthNum,13);

Here's the modified DB-Fiddle link, including my version of the query.

What I changed, and why:

First, the USE GUITARWORLD at the top of your DB-Fiddle made the set-up code not run. In the future, please ensure that what you've posted in a DB-Fiddle does run successfully - having that just flat-out fail is a bad start :-).
Your GROUP BY isn't grouping by a value in the SELECT list. That isn't always a mistake, but that meant your COALESCE(MONTHNAME()) didn't work. I changed the GROUP BY to GROUP BY the MONTHNAME() results alone, and moved the COALESCE to the outer query, to ensure that the results from that weren't causing problems with the grouping. Since I wasn't using SELECT *, I also needed to explicitly return SumOrderPrice.

Initially, the results listed April, then March, then the Grand Total. I was reasonably certain you'd want to see the months in numerical order, instead of alphabetical (or indeterminate) order, so I added MONTH(Orders.OrderDate) as a new column in the inner query; added it to the GROUP BY (no change to the results, as a given month number will always have the matching given name), and sorted the results by MonthNum (adding the COALESCE to force the Grand Total to show up last.

For the next step, we'll use a variable:

SET @Last_Month_SumOrderPrice := 0;

SELECT OrderDate, SumOrderPrice, Monthly_Pct_Change
FROM (
      SELECT OrderDate
            ,SumOrderPrice
            ,CASE WHEN @Last_Month_SumOrderPrice = 0
               THEN NULL
               ELSE (SumOrderPrice - @Last_Month_SumOrderPrice) * 100.00 / @Last_Month_SumOrderPrice
             END as Monthly_Pct_Change
            ,@Last_Month_SumOrderPrice := SumOrderPrice
            ,MonthNum
      FROM (
            SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate,
                   SUM(ORDERS.OrderPrice) as SumOrderPrice,
                   MONTH(ORDERS.OrderDate) as MonthNum
            FROM ORDERS
            WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
            GROUP BY MONTHNAME(ORDERS.OrderDate),
                     MONTH(ORDERS.OrderDate)
           ) AS T
      ORDER BY MonthNum) as SQ
ORDER BY MonthNum;

We take the basic query you already had, and add in a variable, @Last_Month_SumOrderPrice, to track the SumOrderPrice from the previous month as we go through the data. While the SELECT list data is being processed, the variable will retain its value from the last row.

This gives us access to the current and previous months' SumOrderPrice values. Calculating the percentage from there is trivial. I'm setting the value to NULL to avoid divide by zero errors.

We put another query outside the existing ones; otherwise, the value assigned to the variable would be output in every row.

Note that I've included an ORDER BY in the SQ sub-query, and in the outer query as well, both on MonthNum. The one in the sub-query guarantees the rows are processed in the correct order; the one in the outer query guarantees the final result is displayed in the correct order. It may be likely that the final result would be ordered correctly without the outer ORDER BY; however, the rule in SQL is order is only guaranteed when you specify an ORDER BY on your final result.

As a grand total would be misleading/irrelevant in this case, I've dropped that. If you have more data and want to see year-over-year percentage changes, I'd consider separate queries.

Here's the final DB-Fiddle link, showing both queries running.


This is obviously opinion based:

What is the purpose of Database Tuning?

My answer would be:

  To minimize (within reason) the expense involved of maintaining a set of
  data that has been deemed important.

Database tuning is an expense in itself.  It costs money to tune.  That's where the 'reasonable' part of my statement comes from.

Does it make sense to spend 40 hours to tune something that runs once?  Maybe.  Maybe not.

Does it make sense to spend 40 hours to tune something that runs every second million of times a day?  Probably.

Why do we tune?

We want our queries to run fast because the people waiting for the
results are an expense.  Tuning 'may' allow us to reduce memory, cpu and disk space requirements, saving money.

Tuning is a balancing act - you have to compare the expense involved with 'tuning' against the expense involved in 'not tuning'.

I think searching the Internet for 'What is the purpose of Database Tuning?', you'd find statements similar to this Wiki article.

  Database tuning aims to maximize use of system resources to perform
  work as efficiently and rapidly as possible.

I think, if you took any tuning exercise and kept asking the question

"So, why am I doing this??? Because of this. And why is that important??...", I think you'll ultimately come down to 'money'.

And that's why we tune (within reason).  Hey - my 2 cents...


Update existing rows with squence number/char or any unique data

If you are happy with a number starting from 1 you can use row_number().

update T
set cn = rn
from (
       select cn,
              row_number() over(order by (select 1)) as rn
       from TableX
     ) T


Does Postgresql create a new tuple for an no-change update statement?

Yes, this will modify all rows, even if the value doesn't change.
If you want to avoid that, either use a WHERE condition:
UPDATE app.products
SET "description" = TRIM(BOTH FROM "description")
WHERE "description" IS DISTINCT FROM TRIM(BOTH FROM "description");

or use the suppress_redundant_updates_trigger() trigger function to define a BEFORE UPDATE trigger on the table.
Avoiding the unnecessary updates will improve the performance and reduce table bloat.


can't install citext extension: could not access file "$libdir/citext": No such file or directory

Did you perhaps install multiple instances of Postgres on the same server? Standard Postgres is not built for that, $libdir is compiled into the program. If so, you could fix your problem by uninstalling all other instances and and re-installing your current Postgres version.

Related:

Could not access file “$libdir/plpgsql”: No such file or directory


Is a dropped (or altered) function still available inside already opened transactions?

Interesting question.

From a small test, it appears that function modifications and deletes are transactional. Meaning, that - in any isolation level - when transaction 2 modifies or deletes the function, transaction 1 is oblivious to it and still uses the old version of the function.

Any changes to the function become visible only after the transaction commits and only to transactions that start after that commit. The isolation level is irrelevant, as the function tested was not reading any data from any tables.

-- Create Function
x=# create or replace function f() returns integer as
$$ select 1 ; $$ immutable language sql ;
CREATE FUNCTION

-- TRAN 1
x=# begin ;
BEGIN
x=# select * from f() ;
 f
---
 1
(1 row)
                    -- TRAN 2
                    x=# begin ;
                    BEGIN
                    x=# drop function f () ;
                    DROP FUNCTION
                    x=# commit ;
                    COMMIT
-- TRAN 1
x=# select * from f() ;
 f
---
 1
(1 row)
x=# commit ;
COMMIT

-- After COMMIT
x=# select * from f() ;
ERROR:  function f() does not exist
LINE 1: select * from f() ;
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
x=#

In a slightly different scenario, if both transactions try to modify the function, then only one succeeds and the other gets blocked and then fails if the first commits.


MS SQL Server Cumulative Updates - Best Practices

I am a big advocate of keeping up to the date with the most recent cumulative update, but only if your testing/QA cycle can ensure full and proper regression testing against it. Glenn Berry of SQLskills is also a proponent of this approach.

Microsoft's own recommendation is to only apply CUs that fix issues that affect you, though they have loosened that stance recently. The problem is that you may be affected by one or more of those issues and not know it, or you might be affected tomorrow even if it hasn't hit you yet. Are you going to go through and try to reproduce the issue behind every single fix in every single CU for your branch? Are you going to do this continuously to make sure you still aren't affected?

I'll be quite honest: I've never had an issue applying any CU to my instances. And in fact their CU release process has been a lot more reliable than the service pack release cycle, and in many cases (including most recently with SQL Server 2012 Service Pack 2), you don't want to apply the service pack until the first CU for that branch has been released anyway. In this case there is an interim hotfix to resolve the issue that wasn't fixed in time to make the Service Pack code, but that isn't always true.


How to Join a table to a group_contact in mySql

FROM Customers c
JOIN Orders o USING(order_id)


Is my ER Diagram correct? Why use relationships?

It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources including ad hoc queries, data imports, etc. It is irresponsible to think the data in the database is protected because the application has protections. Sure you think that you will force all changes through a webservice or some such, but the reality is that no one is going to add a million new customer records, from that company you just bought, one record at a time through a service. Your database has to be designed to account for the fact that people will change it directly at the source or through other applications (some of which may not be able to use your web service). Further, the application interface is usually more likely to be thrown away or redesigned than the data and when that happens, you may lose all or some of the data integrity rules. In designing databases, you have to think about how the data needs to be protected over time not about what is easiest and most convenient for the programmer or the intial application.  A data professional sets up PK/FK relationships in the database because that is where the data lives and it is the best place to set it up to protect the data quality.

I see data from a lot of different companies in my current position and I see all too many  that didn't set up their PK/FK relationships in the database because they have data integrity problems that a correctly designed database would not have.


Understanding joined set-returning functions in the FROM list

The piece that you are missing is, that this is not a regular cross join, but a "LATERAL" cross join.

So the fully written equivalent is:

SELECT *
FROM my_table
   CROSS JOIN LATERAL unnest(my_col);

The keyword LATERAL is implicitly assumed if the join is done against a set returning function that's why cross join unnest(my_col) or even from my_table, unnest(my_col) works as well.

The LATERAL will change the cross join such that it takes one row from my_table and does a cross join of that (one) row with all rows returned from the set returning function. Then it does the same with the next row and so on.

So it generates the cross join between the row containing array {a,b} and all its elements. And then another cross join with the row containing the array {c,d,e} and all its elements.

The manual explains it this way:

  When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s)


Trigger before update a fields in a table

The answer will vary depending upon the RDBMS you are using. Also DBAs & developers have different preferences.

Personally i would choose to apply this in a stored procedure rather than a trigger. Triggers dont scale well and can give unpredictable results with bulk updates.


Identify Duplicates in MySQL across columns

You want a query like this one:

SELECT contact_id, contact1, contact2, contact3
FROM contact
WHERE
(
  (contact1 = contact2)
  OR
  (contact2 = contact3)
  OR
  (contact1 = contact3)
);

See the db-fiddle here (see DDL and DML below).

Result:

contact_id  contact1    contact2    contact3
____________________________________________
         3   phone31     phone33     phone33

Of course, if MySQL had CHECK CONSTRAINTs, this problem could be avoided at the DDL level! From here, you have this gem:

  CHECK

  The CHECK clause is parsed but ignored by all storage engines.

============= DDL and DML ============

CREATE TABLE contact
(
  contact_id INTEGER,
  contact1 VARCHAR(20),
  contact2 VARCHAR(20),
  contact3 VARCHAR(20)
);

INSERT INTO contact
VALUES
(1, 'phone11', 'phone12', 'phone13'),
(2, 'phone21', 'phone22', 'phone23'),
(3, 'phone31', 'phone33', 'phone33'), -- << Duplicate number!
(4, 'phone41', 'phone42', 'phone43'),
(5, 'phone51', 'phone52', 'phone53');


Add the CASE expression to the GROUP BY clause.

SELECT   PROJECT_ID,
         CASE WHEN SOURCE_NAME = 'A' THEN 'A'
              WHEN SOURCE_NAME = 'B' THEN 'B'
              ELSE 'OTHER'
         END SOURCE_NAME,
         SUM(AMOUNT) AMOUNT,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID,
         CASE WHEN SOURCE_NAME = 'A' THEN 'A'
              WHEN SOURCE_NAME = 'B' THEN 'B'
              ELSE 'OTHER'
         END,
         YEAR


The fact is that this question cannot be answered as such, so I will reframe it and answer as reframed.

The basic problem is that the database size will depend entirely on implementation details of the db.  It could be the same size, could be more, could be less.  We can't say.  Additionally it may depend on how much repetition is avoided using one approach or the other.

At any rate it is exactly the wrong question to ask here.  The right question is how your data relates to eachother.  You basically have three options:

Second table with 1000000 rows.  Use this if there are normalization issues or if you need to break this off for table space reasons.  It may increase the size of the db because of join conditions.
New column.  Use this if the data does not belong in the same column of the other table due to semantic reasons, but is functionally dependent on the data in the existing rows.
New rows.  Use this if the data belongs in the existing rows based on the internal semantics of the data.

Note that table size is not your biggest concern.  You can probably use indexes to address them but 1-2 million rows is still way too small to worry about size-wise in this way.


Update Ranking Points Based on Date Range

You should always include your schema and sample before and after data, otherwise it's difficult to conceptualize what we don't know.
You can solve this pretty easily without even using a loop in PHP, rather by using a window function in MySQL like ROW_NUMBER to generate the ranks and then use those ranks to update your scores. Here's a partial example (if you update your question with the missing items I mentioned above, I can update this example):
UPDATE ometimeatk.timeatk_has_list
INNER JOIN
(
        SELECT *, ROW_NUMBER() OVER (ORDER BY timer DESC) AS RacerRank -- Generates a unique ID (assuming timer is unique) for each racer row ordered by timer
        FROM timeatk
        WHERE DATE(posted)
            BETWEEN DATE_SUB(DATE(now()), INTERVAL (WEEKDAY(now()) - 1 + 7) % 7 DAY)
            AND DATE_ADD(DATE(now()), INTERVAL 6 - (WEEKDAY(now()) - 1 + 7) % 7 DAY)
        ORDER BY timer ASC LIMIT 0, 10
) c -- Gets the racer rank
    ON ometimeatk.timeatk_has_list.id = c.id -- Fill in your actual key fields here
SET ometimeatk.timeatk_has_list.weekly_score = 11 - c.RacerRank -- Sets the weekly score to 10 for 1st place, 9 for 2nd place, etc


You need several of my blogs:

Why UUIDs can be very slow - Do you need a UUID?
How to efficiently purge "older than X days" using PARTITIONing;
Incrementally augmenting a Summary Table, though you may have this 'right'.

How big is the table?  Which Engine are you using?  What is the value of innodb_buffer_pool_size?  It should be about 70% of RAM.


Restoring master is a bit of a pain.

I assume you are just trying to recover the definition?

I just tried creating a logon trigger, backing up master and opening the *.bak file in notepad and searching for "CREATE TRIGGER" - it was plainly visible in the file.

Alternatively you could restore it as a regular user database (assume master2 below) and see if the following finds it (will need to connect via the DAC)

USE master2

SELECT *,
       CAST(imageval AS NVARCHAR(MAX)),
       CAST(imageval AS VARCHAR(MAX))
FROM   sys.sysobjvalues
WHERE  CAST(imageval AS NVARCHAR(MAX)) LIKE N'%TRIGGER%'
        OR CAST(imageval AS VARCHAR(MAX)) LIKE '%TRIGGER%'

The above worked for me. YMMV and make sure you sanity check any results (the first method definitely won't work if the definition is split over multiple pages and may find ghosted versions).

If the trigger object is encrypted, I imagine it would be visible in sys.sysobjvalues using the DAC, though certainly my LIKE clauses wouldn't find it. You would need to look in sys.sysschobjs and get the objid from the name. Hopefully it isn't encrypted or it would need a good read of this article: The Internals of WITH ENCRYPTION


Find the flowchart that explains what happens when you have a mixture of binlog_do_... and binlog_ignore_... -- it is not necessarily what you would expect.

Be aware that

USE db1;
INSERT INTO db2.tbl

uses db1, not db2 for determining whether to replicate.

Which binlog format are you using?

For further debugging, use mysqldumpbinlog to see if a particular row was inserted into the binlog file on the Master and/or whether it arrived in the relay-log file on the Slave.


How can I update multiple tables at once in Postgres?

You're actually close.. first create some test data..

CREATE TABLE foo_knex_migrations ( name )
  AS VALUES ('test.js'),('test2.js'),('bicycles');
CREATE TABLE bar_knex_migrations AS TABLE foo_knex_migrations;
CREATE TABLE baz_knex_migrations AS TABLE foo_knex_migrations;

Next, we're going to use EXECUTE...FORMAT(), with %I.

DO
$do$
  DECLARE
    i pg_tables%rowtype;
  BEGIN
  FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
  LOOP
    EXECUTE FORMAT(
      $$
        UPDATE %I
        SET name = replace(name, '.js', '.ts');
      $$,
      i.tablename
    );
  END LOOP;
  END
$do$;

TABLE baz_knex_migrations ;
   name
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

test=# TABLE foo_knex_migrations ;
   name
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

As a side note,

In general, you should be using information_schema which is standardized for simple things like this, and where speed doesn't matter.
You should probably be checking to see if the UPDATE needs to run by adding a WHERE clause. Otherwise, you're rewriting the table for nothing.
In SQL, we don't use naming conventions like this. There is no need for that. If knex_migrations has multiple tables, consider CREATE SCHEMA knex_migrations to store them, rather than searing through the catalog based on a naming convention for all tables.


How to restrict multi-tenant MySQL database?

The simplest answer is probably: give each portal a different MySQL database, with a different login and password.

The next-simplest answer is: put each portal's stuff in a separate table.  Review your code carefully to ensure that each portal can only access data in its own table.

If you don't want to change your database schema, the pragmatic answer is: When the code for your portal application sets up a MySQL connection, the first thing it should do is create a view.  Define the view so it provides access to only the subset of the data associated with that particular portal.  You can define the view using a SQL statement with a WHERE clause of the form WHERE portalid = xxx (replace xxx with the id of the currently running portal).  e.g.,

CREATE VIEW webserviceview
AS
SELECT *
FROM webservice
WHERE portalid = xxx

(fill in xxx with the currently running portal).  Now make sure that all of your code only accesses webserviceview and never accesses the webservice table directly.

When a user logs in, you can use the same idea to further restrict access.  Create a view that has only the data for the currently logged-in user.  e.g.,

CREATE VIEW currentuser
AS
SELECT *
FROM webserviceview
WHERE customerid = yyy

Then make sure that your code only accesses the currentuser table, never the webservice or webserviceview table.

If you want to read some research papers that take this sort of idea a bit further, check out the following papers:

Diesel: Applying Privilege Separation to Database Access, ASIACCS 2011.
CLAMP: Practical Prevention of Large-Scale Data Leaks, IEEE Security & Privacy 2009.
Nemesis: Preventing Authentication & Access Control Vulnerabilities in Web Applications, Usenix Security 2009.


You can use ALTERNATE attribute in second archive log destination.

  ALTERNATE

  Specifies an alternate archiving destination to be used when the original destination fails.

Example:

LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY'
LOG_ARCHIVE_DEST_STATE_2=ALTERNATE

Reference:
LOG_ARCHIVE_DEST_n Parameter Attributes


MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?

Here is the nature of table creation with CREATE TEMPORARY TABLE

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

My guess would be you must have done the following sequence of events:

You ran CREATE TEMPORARY TABLE
Terminated DB Connection (which makes the temporary table drop immediately)
You had to run CREATE TEMPORARY TABLE again because he table did not exist

I have seen this problem victimize many developers, especially when it comes to MySQL Replication. For example, if you run STOP SLAVE;, the SQL Thread will close. Any tables create with CREATE TEMPORARY TABLE will be dropped. When you run START SLAVE;, MySQL Replication breaks instantly because the table needed for INSERTs, UPDATEs, or DELETEs no longer exist. I have had to perform mysqlbinlog dumps on relay logs to locate the original CREATE TEMPORARY TABLE statements, run them as CREATE TABLE in order for the table to remain accessible to other DB Connections, including the SQL Thread when START SLAVE; is reissued.
As for your Questions

Is there a way to tell MySQL that the creation of the temporary table and the SELECT query are just executed if the table does not exist?
Maybe with an if-condition?
Or can I check to some information schema if a temp-table exists?

You cannot check the information_schema for temp table existence. In the OS, there is no manifested .frm in datadir. You could find it in the folder defined in tmpdir.
Here is an example: I will create a temporary table called test.junk and try to locate the whereabouts of the table. (Note: I am using MySQL for Windows)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use test
Database changed
mysql> create temporary table junk (a int) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table junk\G
*************************** 1. row ***************************
       Table: junk
Create Table: CREATE TEMPORARY TABLE `junk` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select count(1) from information_schema.tables
-> where table_schema='test' and table_name='junk';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.08 sec)

mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | C:\MySQL_5.5.12\data\ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'tmpdir';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| tmpdir        | C:\Users\redwards\AppData\Local\Temp |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

The table is not visible in INFORMATION_SCHEMA.TABLES. Let's locate the table in the OS
C:\MySQL_5.5.12\data\test>cd
C:\MySQL_5.5.12\data\test

C:\MySQL_5.5.12\data\test>dir junk.*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.5.12\data\test

File Not Found

C:\MySQL_5.5.12\data\test>dir C:\Users\redwards\AppData\Local\Temp\*.frm
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\Users\redwards\AppData\Local\Temp

10/19/2012  11:04 AM             8,554 #sql7ac_3_0.frm
               1 File(s)          8,554 bytes
               0 Dir(s)  190,200,049,664 bytes free

C:\MySQL_5.5.12\data\test>dir C:\Users\redwards\AppData\Local\Temp\*.MY*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\Users\redwards\AppData\Local\Temp

10/19/2012  11:04 AM                 0 #sql7ac_3_0.MYD
10/19/2012  11:04 AM             1,024 #sql7ac_3_0.MYI
               2 File(s)          1,024 bytes
               0 Dir(s)  190,200,049,664 bytes free

C:\MySQL_5.5.12\data\test>

There is the temp table, in the tmpdir folder. When I closed the mysql client and run dir C:\Users\redwards\AppData\Local\Temp\*.frm, the table vanished.
RECOMMENDATION
You can do one of two things

Create the temp table, load it with data, keep the same DB Connection around, and then access the data with the same ProcessID.
Do CREATE TEMPORARY TABLE IF NOT EXISTS (This is really a band-aid to improper design but may circumvent the problem).

UPDATE 2012-12-18 13:06 EDT
Further Observation #1
If you stayed with the same DB Connection and still have this problem, here something else not previously mentioned: You gave the temp table an index. That should be a UNIQUE INDEX or PRIMARY KEY.
Further Observation #2
You could shift the operation from a CREATE TABLE ... SELECT to CREATE TABLE ... INSERT IGNORE
Further Observation #3
If you want to use temp tables, please specify MyISAM. InnoDB has a tendency to leave pigeon holes in the data dictionary with ibdata1 in the event of a crash or upon DB Connection termination.
With these three(3) observations in mind, here is my recommended change
CREATE TEMPORARY TABLE IF NOT EXISTS cache
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO cache SELECT id FROM table WHERE xyz;

CREATE TEMPORARY TABLE IF NOT EXISTS cache
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO cache SELECT id FROM table WHERE xyz;

Give it a Try !!!


How can deleting similar number of rows result in very different log growth?

Way too much for a comment, but just to prove that the amount of data has a much bigger impact on how much gets logged, compared to simply the number of rows.

First, create a database, and back it up so we aren't in pseudo-simple:

CREATE DATABASE RLS;
GO
ALTER DATABASE RLS SET RECOVERY FULL;
GO
BACKUP DATABASE RLS TO DISK = 'c:\temp\rls.bak' WITH INIT;
GO
USE RLS;
GO

Now, create a skinny table and a wide table, and insert 2,000 rows:

CREATE TABLE dbo.skinnyTable(id int PRIMARY KEY);

CREATE TABLE dbo.wideTable(id int PRIMARY KEY,
  dt datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
  n nchar(2000) NOT NULL DEFAULT USER_NAME(),
  x uniqueidentifier NOT NULL DEFAULT NEWID(),
  y uniqueidentifier NOT NULL DEFAULT NEWID(),
  z uniqueidentifier NOT NULL DEFAULT NEWID());

INSERT dbo.skinnyTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

INSERT dbo.wideTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

Backup the log twice:

BACKUP LOG RLS TO DISK = 'c:\temp\rls1.trn' WITH INIT;
BACKUP LOG RLS TO DISK = 'c:\temp\rls2.trn' WITH INIT;

With a clear log, let's delete half of each table (1,000 random rows):

;WITH cte AS (SELECT TOP (1000) * FROM dbo.skinnyTable ORDER BY NEWID())
  DELETE cte;

;WITH cte AS (SELECT TOP (1000) * FROM dbo.wideTable ORDER BY NEWID())
  DELETE cte;

SELECT AllocUnitName, log_rows = COUNT(*)
  FROM sys.fn_dblog (NULL, NULL) AS l
  WHERE AllocUnitName LIKE N'%[ey]Table%'
  GROUP BY AllocUnitName;

I get:

AllocUnitName                                     log_rows
----------------------------------------------    --------
dbo.skinnyTable.PK__skinnyTa__3213E83F83E76BF2    2008
dbo.wideTable.PK__wideTabl__3213E83FEFF3F638      14991

Clearly, there is much more log space required to delete the same number of rows from a wide table, when compared to a skinnier table.

As a logical extension, number and width of indexes can influence this difference as well. And I didn't even try LOB data, nor did I investigate the rows where AllocUnitName was NULL. The above alone demonstrates a 7X difference.


Concatenate one-to-many field in single result?

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result.

You can implement FOR XML PATH and STUFF():

SELECT DISTINCT d.AppId,
  d.AppName,
  d.AppType,
  Tags = STUFF((SELECT ', ' + t.TagName
                FROM AppTags t
                where d.AppID = t.AppID
                FOR XML PATH (''))
                , 1, 1, '')
FROM AppDetails d
WHERE d.AppID = '1';

See SQL Fiddle with Demo.

Or you could use FOR XML PATH with CROSS APPLY:

SELECT DISTINCT d.AppId,
  d.AppName,
  d.AppType,
  tags = left(t.tags, len(t.tags)-1)
FROM AppDetails d
CROSS APPLY
(
  SELECT t.TagName + ', '
  FROM AppTags t
  WHERE d.AppID = t.AppID
  FOR XML PATH('')
) t (Tags)
WHERE d.AppID = '1';

See SQL Fiddle with Demo.

If you want to then query against the tags values, then you can use a CTE similar to:

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '')
  FROM AppDetails d
  WHERE d.AppID = '1'
)
select *
from cte
where tags like '%test1%'

See Demo

If TagName includes certain XML characters (e.g. >, &) they will become entitized (> -> &gt;, & -> &amp;). To avoid this, change FOR XML PATH('') to FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)').


What do columns with a "dot" mean when listing Sql Server user permissions using sp_helprotect?

Stop using sp_helprotect - it's bad news, mostly because it hasn't been updated since before SQL Server 2005 was released. Instead, you can generate the script you need based on sys.database_permissions and possibly sys.database_role_members. For example, let's say you have a user named foo and you want to apply the same permissions as foo to a new user you just created, bar. You can see the permissions foo has (at least for the database itself, tables, views, columns, procedures and functions) and the role(s) they're in using the following script:

DECLARE @pid INT;

SELECT @pid = principal_id FROM sys.database_principals
  WHERE name = N'foo';

SELECT r.name
FROM sys.database_principals AS r
INNER JOIN sys.database_role_members AS rm
ON r.principal_id = rm.role_principal_id
WHERE rm.member_principal_id = @pid;

SELECT p.state_desc,
 p.[permission_name],
 [oschema] = QUOTENAME(s.name),
 [object]  = QUOTENAME(o.name),
 [column]  = QUOTENAME(c.name),
 tschema   = QUOTENAME(ts.name)
FROM sys.database_permissions AS p
LEFT OUTER JOIN sys.objects AS o
ON p.major_id = o.[object_id]
AND p.class_desc = N'OBJECT_OR_COLUMN'
LEFT OUTER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
LEFT OUTER JOIN sys.columns AS c
ON p.major_id = c.[object_id]
AND p.minor_id = c.column_id
LEFT OUTER JOIN sys.schemas AS ts
ON p.major_id = ts.[schema_id]
AND p.class_desc = N'SCHEMA'
WHERE grantee_principal_id = @pid;

So now to generate those scripts to apply to bar:

DECLARE @pid INT, @sql NVARCHAR(MAX) = N'', @trg SYSNAME = QUOTENAME(N'bar');

SELECT @pid = principal_id FROM sys.database_principals
  WHERE name = N'foo';

SELECT @sql += CHAR(13) + CHAR(10)
  + N'ALTER ROLE ' + QUOTENAME(r.name)
  + ' ADD MEMBER ' + @trg + ';'
FROM sys.database_principals AS r
INNER JOIN sys.database_role_members AS rm
ON r.principal_id = rm.role_principal_id
WHERE rm.member_principal_id = @pid;

SELECT @sql += CHAR(13) + CHAR(10) + N''
  + CASE p.state WHEN N'W' THEN N'GRANT' ELSE p.state_desc END
  + ' ' + p.[permission_name] + ' ON '
  + COALESCE(
    N'SCHEMA::' + QUOTENAME(ts.name),
    QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
      + COALESCE('.' + QUOTENAME(c.name), ''),
    N'DATABASE::' + QUOTENAME(DB_NAME())
  ) + ' TO ' + @trg + CASE p.[state] WHEN 'W'
    THEN N'WITH GRANT OPTION' ELSE '' END + ';'
FROM sys.database_permissions AS p
LEFT OUTER JOIN sys.objects AS o
ON p.major_id = o.[object_id] AND p.class = 1
LEFT OUTER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
LEFT OUTER JOIN sys.columns AS c
ON p.major_id = c.[object_id]
AND p.minor_id = c.column_id
LEFT OUTER JOIN sys.schemas AS ts
ON p.major_id = ts.[schema_id] AND p.class = 3
WHERE p.grantee_principal_id = @pid
AND p.class IN (0,1,3); -- database, object/column, schema

PRINT @sql;
-- EXEC sp_executesql @sql;

Result will be something like this (now, PRINT has a limit of 8K, so you may not see the whole script in PRINT's output, but it's all there), but obviously not exactly:

ALTER ROLE [db_owner] ADD MEMBER [bar];
GRANT CONNECT ON DATABASE::[wp] TO [bar];
GRANT DELETE ON [dbo].[repro] TO [bar]; -- table
GRANT SELECT ON [dbo].[whatever].[a] TO [bar] WITH GRANT OPTION; -- column
DENY UPDATE ON [dbo].[whatever].[b] TO [bar]; -- column
DENY ALTER ON [dbo].[x] TO [bar]; -- procedure
GRANT EXECUTE ON [dbo].[x] TO [bar]; -- procedure
GRANT SELECT ON SCHEMA::[y] TO [bar];

Your goal is actually to apply the same permissions to a similar user in a different database, so you may need to adjust the target user (@trg) and replace DB_NAME() with the hard-coded name of the target database.

I am not sure if there are any cases where these permissions need to be applied in any sort of dependency order. I'm also not sure about other types of permissions that may interfere here (hence the WHERE clause - you can see the full set of class values in the documentation) - you may need permissions on things like TYPE, ROUTE, SERVICE_CONTRACT etc. You can expand the above script to cover those if necessary.


Table Partition Function Empty Partitions

I can guarantee that no data older than 2019-10-01 will be inserted to
  the table, and I plan to keep SPLIT partitions when I am reaching
  2022-08-01

As long as no existing data qualifies for the new partition boundary range, no data movement is required for the SPLIT.

  I also plan to regularly TRUNCATE old partitions and MERGE the old
  partitions range.

Similarly, the merged partition will be empty due to the preceding TRUNCATE so no rows will need be moved to accommodate the new boundaries.


How to SELECT rows with time duration stored in varchar column?

SELECT   * FROM table
WHERE (SUBSTRING_INDEX(duration,':',1)*60)+SUBSTRING_INDEX(duration,':',-1)
BETWEEN 180 AND 405

SQL Fiddle

Use SUBSTRING_INDEX to isolate the minutes and seconds,convert minutes to seconds and add them up so the where condition has something to compare.Assuming all durations in the table have minutes and seconds separated by ':'


Cumulative duration between value resets

This is usually called a GROUPING AND WINDOWS solution.

Basically you set reset points according some rules, then you set up groups by SUMming reset points and finally use aggregated functions to get the desired value.

;WITH reset AS
(
SELECT AuditID,
       CreditID,
       TS,
       Val,
       /* Next CASE returns 1 when:
             a) It is the first row of a partition
             b) Current Val=1 and previous = 0
          Otherwise it returns 0
       */
       CASE WHEN Val=1 AND COALESCE(LAG(Val) OVER (PARTITION BY CreditID ORDER BY CreditID, TS), 0) = 0
            THEN 1 ELSE 0 END AS Rst
FROM   Taco_Val
)
  , [group] AS
    (
    SELECT AuditID,
           CreditID,
           TS,
           Val,
           /* Next SUM generates a cumulative sum of reset points.
              That is a correlative number for each partition
           */
           SUM(Rst) OVER (PARTITION BY CreditID ORDER BY CreditID, TS) AS Grp
    FROM   reset
    )
    SELECT
           /* Next select returns, of every group, AuditID corresponding to
              the row with the minimum TS
           */
           (SELECT AuditID FROM [group] WHERE CreditID = g.CreditID AND TS = MIN(g.TS)) AuditID,
           CreditID,
           MIN(TS) as StartTS,
           1 as Val,
           DATEDIFF(MINUTE, MIN(TS), MAX(TS)) as Duration
    FROM   [group] g
    GROUP BY CreditID, Grp;
GO

AuditID | CreditID | StartTS             | Val | Duration
:------ | :------- | :------------------ | --: | -------:
a1      | 1        | 09/08/2018 19:24:39 |   1 |      100
a9      | 1        | 09/08/2018 21:14:17 |   1 |       46
a4      | 1        | 09/08/2018 22:08:26 |   1 |        8
a3      | 1        | 09/08/2018 22:16:24 |   1 |       60
ay      | 1        | 10/08/2018 15:12:02 |   1 |        8

db<>fiddle here

You'll find a brief explanation of every CTE on the previous fiddle.


Creating the database on different file groups

One of the main benefits of utilizing multiple files and filegroups is that you have great control over file growth. Also, prominently you can control and optimize I/O performance, as putting database files on separate physical disks can lead to faster I/O.

If you have an I/O expensive query across two tables, putting then on different disks can lead to better performance as there won't be a single physical disk servicing the query.

Take a look at this resource for more information on the benefits: http://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx


Auto-calculated field which includes letters and numbers

Don't do the SELECT MAX thing; you can run into concurrency problems there. If the patterns are simple enough, and have only a single increasing numeric portion, you can probably get away with using an identity column plus a persisted computed column to create the formatted string value:

CREATE TABLE IDTest (
    row_id int IDENTITY(1,1) NOT NULL,
    part_id AS 'M' + REPLICATE('0', 10 - LEN(CAST(row_id AS varchar(10)))) + CAST(row_id AS varchar(10)) PERSISTED,
    part_name varchar(50) NOT NULL
)

INSERT INTO IDTest (part_name) VALUES ('First part'), ('Second part'), ('Third part')

SELECT * FROM IDTest

If your patterns need to get fancier than that (multiple numeric segments, variations in the letters, etc.) then take a look at using sequences and triggers.


I want to select an increment counter group by medec field please how to do this and i would like the output will be as below

You can emulate ROW_NUMBER() function in this way:

select
    rn, medec
from
    (select
       medec,
       @rn := case when @last_medec = medec then @rn +  1 else 1 end rn,
       @last_medec := medec
     from
       tbl, (select @rn := 1, @last_medec := '') x
     order by
       medec) y

rn | medec
-: | :-----
 1 | medec1
 2 | medec1
 1 | medec2
 2 | medec2

db<>fiddle here

Have a look at this MySql tutorial:


Database design for large amount of sensors at very fast intervals

150 new rows per second?  At least one of these needs to be true:

Using SSD, not HDD.
Batch loading (LOAD DATA, INSERT with multiple rows, etc)

Other notes:

Don't use DOUBLE.  Double takes 8 bytes and has far more precision than any sensor can provide.  This is important because the size of the data, as you noted, is important.  FLOAT gives you about 7 significant digits in 4 bytes, ample for virtually all sensors.  You could even consider DECIMAL or various *INT types, perhaps with scaling, to save even more space.
How much RAM do you have?  (I'll critique your my.ini settings after getting the RAM size.)
What is the problem with taking 2 hours to load 720 hour's worth of data?
What will you do with the data?  There are several directions I will point you, depending on what kinds of queries you will apply to the data.  Some will make a significant improvement in speed and/or space.

Back to your question:

  I've been tasked with designing a backup solution to store sensor data ... in the event that the primary transmission method of the sensors goes down.

If that is the question, the answer is trivial:  A flat file, in the same format as the raw data comes in.  That way, when recovering from the outage, you focus on getting data from a different source, but not a different format.


Does my ERD account for my business logic

There are a few issues with your ERD.

A. Your ERD does not account for these business requirements:

  Certain customers might wish some different information displayed on
  their emails, so my database design need to account for this. E.g.
  Customer John Smith might want his emails to also list the 'Item
  Color', where as Emma Watson is not too fussed about that detail, and
  does not want that listed in the emails.

  If customers have not explicitly mentioned that they want customized
  emails sent, then a Standard Template email is sent.

You need to add a location to maintain your customer email preferences.  The model for that depends on what the rules are for recording the preferences.  Your question isn't explicit enough on this point.

B. You might also want to include an EMAIL_TEMPLATE table with a foreign key from EMAIL to this new table.  That will allow you to record which template was used for each email.

C. You aren't clear about what your m:n relationship is recording.  Are these columns: EMAIL.TO, EMAIL.CC, EMAIL.BCC foreign keys?  If so, are they single or multivalued?  (i.e. can you have multiple email addresses in any or all of these fields?)  If they are single valued foreign keys, then you can do away with the many-to-many and make them each a many-to-one from EMAIL to CUSTOMER (so you will have three 1:m relationships)  If they are multi-valued foreign keys, then you need to create an explicit intersection table instead of using a simple m:n relationship and you will want to have an ADDRESSEE_TYPE column ("TO", "CC", "BCC") as part of the composite key.

EDIT: Entity Relationship Diagram

This would be a logical data model that could support your business rules:

The TEMPLATE_FIELD_TYPE table is optional.  You could use it if you have a standard list of fields that can be added to a template.  If you don't want to lock down this list or reuse fields then you can skip this table and just use TEMPLATE_FIELD by itself.


UPDATE test t1
LEFT JOIN test t2 ON t1.sample_id = t2.sample_id AND t2.id = 2
SET t1.id = 2
WHERE t2.id IS NULL;

fiddle


My best and most speculative guesses are:

A lookup table. Used as a control/pick list for an application or report

A dimension.  Used in a data warehouse.

A log table. Used for logging.

Left over junk fron an adhoc query, data migration, report filter etc.

Aaron was right... But I couldn't resist ; )

To me, the names imply that it's to distinguish between academic and non academic staff. But unless you can test the application exhaustively in order to reverse engineer it you may never know the true/complete purpose for it.


Joining on a M-N lookup table vs keyword searching a single column

It is best to store your tags in a separate table because of the many to many relationship. With something like tagging images you can end up with a very large number of comma separated values that would need to be parsed any time you search your database which will lead to massive performance problems as your data grows.

Going that route and searching for a tag or partial tag will require you to parse through multiple strings multiple times. But if all you are doing is displaying the data to be read then comma separation is a fantastic idea and it would be much faster than joining to another table.

Here is great explanation on why, in general, comma separation is not a good practice.

--EDIT

After looking through the githuh link you have posted and the web site, I think Danbooru is doing a combination of two things. If I had to quickly guess, they are keeping the comma separated values of tags to make it easy and fast to view the text upon a mouse hover. They also seem to be inserting the tags into a well structured set of tables. It may be nice to keep that in mind for your practices but I would recommend leaving that out if you can.


Where can I find the algorithm used in Oracle Range Partitioning

Manuals:

Basic description can be found in the Database Concepts
Some information about administering partitioned tables can be found in theDatabase Administrator’s Guide
Detailed description as you requested can be found in the
Database VLDB and Partitioning Guide

Books:

Expert Oracle Database Architecture,
by  Kyte, Thomas, Kuhn, Darl
contains about 70 pages about partitioning. I did not know it but I assume it contains a very detailed presentation of the topic.

Whitepapers:

Partitioning in Oracle Database 11g

Partitioning with Oracle Database 11g Release 2

Oracle uses a data dictionary to store the structure of the database objects. These data dictionary contains a set of tables that are located in the SYS schema and have a name that ends with '$'. The table SYS.TABPART$ contains a row for each partition. So if before Oracle stores a row in a partitioned table it hast to read SYS.TABPART$ table of the data dictionary to get information about the partitioned table. Oracle is implemented in C, so access to partitiones is implemented in C-code, too. This C-code is proprietary to Oracle and not made publicly available. The data structures that are used in these programs aren't publicly available too and I think Oracle has no interest to make it known to the public. But it is not necessary to know such details if you want to work with Oracle or want to get a deep understanding of how Oracle works.
The partition relevant dictionary objects are created by the $ORACLE_HOME/rdbms/admin/dpart.bsq' script. There are a lot of other bsq-scripts in this directory that create other dictionary objects. These scripts are executed when the database is created. They are referenced by the well known USER_ / ALL_ / DBA_ views that are a more user friendly access to the dictionary data.
You cannot access the Oracle C-code but if your are interested in how a real database is implemented you can study the source code of an open  source database. As far as I know MariaDB (a MySQL successor) is an open source database that has implemented partitioning. Also there are a lot of books about  database systems  and data structures.


Why are query results shortened in pgAdmin 1.18.1?

There is a setting for that in the options: Max characters per column - useful when dealing with big columns. Obviously your setting is 256 characters.

Set it higher or set it to -1 to disable the feature.
Consult the fine manual here for more details.


You could use:

backup software (for automated snapshots of your data),
mongodump/mongorestore,
or a simple copy of the \data\db directory to your backup location.

For option 2, make sure to use mongodump with the --oplog option. If you need to restore, then use mongorestore with the --oplogReplay option.

With option 3, stop the secondary member, and copy the the data out. Or, if you do not desire to stop the secondary, use db.fsyncLock(), backup and then use db.fsyncUnlock().

Secondaries replica set members are ideal for reporting and backup use cases.

Reference: https://docs.mongodb.com/v3.2/core/backups/


Is there any better approach in designing this database?

If it were me, I would make two changes:

1) Create a "PurchaseID" field on the "Purchase" table (which can double as an order number for your customers to reference).

2) Move "Quantity" to a new table called "Purchase_Line" for each line (item) on the order (purchase).  Otherwise, you're going to repeat the CustomerID and PaymentID for each ProductID included on every order.  This way your customers can order as many products as they want, but their order only appears in the "Purchase" table once.

Otherwise, I think your normalization looks very complete.


SELECT DISTINCT in extra space data from a single column ( no duplicates )

As Erik said in the comments, you should fix bad data rather than trying to query around it, but if you absolutely cannot fix the data, the below query will get the distinct list of cities by replacing double spaces with a single space:
SELECT DISTINCT REPLACE(customer_city, '  ', ' ') as cities
FROM customers
ORDER BY customer_city ASC

This is a really basic example, however, if the input data is not being validated, this may not be the only type of whitespace you're encountering causing duplication.
Prior to SQL 2017, you need to daisy chain multiple REPLACE statements to replace multiple characters. For example, this code replaces double spaces and tab characters with a single space:
SELECT DISTINCT REPLACE(REPLACE(customer_city, '  ', ' '), CHAR(9), ' ') as cities
FROM customers
ORDER BY customer_city ASC

In 2017, you can use the TRANSLATE function to swap all of the characters you're searching for with a single character, then replace that character with nothing to ensure you're finding all exact duplicates:
SELECT DISTINCT REPLACE(TRANSLATE(customer_city, CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32), '####'), '#', '') as cities
FROM customers
ORDER BY customer_city ASC

This means you don't have to repeat REPLACE for every character you want to strip, you just add the character code (+ CHAR(?)) to the TRANSLATE function and add another replacement character (#). As you can see, the TRANSLATE example replaces 4 characters for basically the same amount of code as the two-character replacement in prior versions.


Problem with restoring database which has a filestream

Thanks to @Nic.
It seems that there's a bug with Windows 10 Creators Update. After reading this article I could manage to solve the problem by changing the service startup account to built-in account LocalSystem and now it works.


An "Event" has a Creator ("users can create an Event"), Members (those who have "Joined" and not "Left" the event) and a Location.

You should have the entity Event with EventID, Creator, other fields such as Location, description, category, date created (optionally) Represented as an Event table with EventID, CreatorID (foreign key to Users table), maybe other fields as needed.

Then have a table EventUser or similar, which is a "bridge" table between Event and User. Field names EventID, UserID which keeps track of users registered to the particular event.

User table is self explanatory with UserID plus whatever other fields are needed for the user.

Do you need to keep track of the "history" of created/deleted event, joined/left event? Then add appropriate tables accordingly and ensure the event is updated accordingly. e.g. add an entry to a table saying that a User was added or deleted to the particular event. You could do this via the business logic in the application itself, or perhaps with an update/insert trigger.

Presumably only the Creator of an event can change details such as the date and location, so you will need to take that into account in the way your application updates these.


What is the best way to design one-to-many relationship with null value?

I would not model a one->many relationship as a many<->many one, with a junction table as you describe, because you are allowing more in your data model than the system it is modelling. You will need to add extra constraints, either in the DB if possible or other application layers, to enforce the more limited relationship and/or add code to detect and deal with breaches of this constraint that happen due to bugs.

Is there a particular reason you want to disallow NULLs for a value that seems to naturally call for them?

If so then you could create "partial junction table" or "extension table" (I'm making that terminology up, I don't know off the top of my head if this is a pattern that has a commonly accepted name):

TableA_TableB:
TableARef FK, NOT NULL
TableBRef FK, PK

this way the FK ensures that you have a one->many relationship as each ID from TableB can only appear once while rows from table A can be referred to by many entities in TableB. The empty condition then becomes that the row does not exist. This avoids the NULL needing to be "stored" but you now have an extra table to join in every time you need to use this field.

If you want to have a surrogate key as your PK, as in the example in the question, then the definition becomes:

TableA_TableB:
Id        PK
TableARef FK, NOT NULL
TableBRef FK, UNIQUE

though that would be wasteful IMO unless you think that you might in later revisions change the relationship to be many<->many where it is valid for the same two entities can relate to each other more than once.


For proper relational design redundancy should be avoided - anything that can be derived from other data (looking up the user that relates to an advert in this case) should not be stored separately again. This helps enforce the integrity of your data, stopping bugs in your code allowing and with appropriate keys and indexes the performance should not be noticeably impacted.

As we are not in the perfect world assumed by theoretical models with perfect implementations of those models and AI to interpret any query we throw at the database, it is sometimes advantageous to de-normalise your data in the manner you describe for a performance gain - but best practise is to only do this once you have identified a specific performance problem rather than baking it into your design. Never de-normalise your data purely for conveniences sake - instead define your tables in normal form and create views to reduce query complexity in other layers of your code.

Of course if you intend to allow messages about an advert to go to other users, a complaint perhaps which would go to an admin initially and not the user who placed the advert, then you must include the destination user as a specific property as it can no longer be unambiguously derived from other data.


Service Broker User Does Not Have Permission to Send to the Service

I wound up having to grant send on the service to the role that the application was in.  I'm not sure why that is necessary given that the application is only supposed to be receiving notification of changes.   The complete SQL is

CREATE QUEUE dbo.SqlDependencyQueue;
GO
CREATE SERVICE SqlDependencyService AUTHORIZATION [dbo]
ON QUEUE SqlDependencyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
CREATE ROUTE SqlDependencyRoute WITH SERVICE_NAME = 'SqlDependencyService', ADDRESS = 'LOCAL';
GO
GRANT SELECT to [ApplicationRole]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ApplicationRole]
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ApplicationRole]
GO
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [ApplicationRole]
GO

GRANT RECEIVE ON SqlDependencyQueue TO [ApplicationRole]
GO
GRANT SEND ON SERVICE::SqlDependencyService TO [ApplicationRole]
GO


Can column name be "Group" in PostgreSQL or in any databases

group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier.
To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier.
ALTER TABLE test RENAME COLUMN sum TO "group";

Note that when using quoted identifiers, you need to always quote it. And it becomes case sensitive. "group" is a different column name than "GROUP".
For more details please see the manual.
In general it is a very bad idea to use names that require quoting. It will save you a lot of trouble if you can come up with a different name that does not require quoting.


MySQL Indexing and performance when size of index is greater than size of data in table

As you mentioned of 5.6 i guess tables are Innodb... Where we have PK stored as clustered index. The rows are stored in an index or say leaf node of the PK  index is data itself.

Secondary further contains the PK key columns as well. So a long PK key => longer Secondary index (WRT size).

About large indexes, you might need to look out for duplicate indexes.
Also one thing to make sure is having large enough innodb-buffer-pool to make sure indexes are in memory for quicker access.

Refer:

https://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html
https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html


Architecture for low-latency RESTful server on Amazon EC2 with Node.js and a database

"Put the webserver (Node.js in this case) on the same EC2 instance as the database"

Never put your app server and DB server into one machine. Since you are using EC2 there are some white papers on Amazon named "Architecting on AWS" and there is a section called scalability. All your app servers should be state-less. It means it should be no data stored on any machine so you can easily bring a VM up (from your AMI) based on your auto-scaling group or get rid of it when the traffic goes down.

Database servers usually have their own way of scalability like Master-slave or replica-set. So they shouldn't be in the same group of your app servers (auto-scale or ELB).

Now the network latency, actually lately I was testing internal network speed check on AWS VPC and it wasn't that bad at all. Depends on your EC2 type the network has low-moderate-and high type of network but when it comes to internal it's pretty much fast. It will be inside DNS resolving time, HTTP request time, HTTP respond time, etc. You should be more worried about your query time on EC2 machines by choosing SSD volumes (Provisioning your EBS volumes maybe) and maybe RAID your EBS volumes to speed up read/writes. The bottleneck usually is the I/O when it comes to database in architecture not the network latency (unless you are using all the bandwidth so you need more secondaries so your app servers can read from them directly).

Hit me if you have a question,
Good luck


Interestingly, I ran the DROP AVAILABILITY GROUP on the local AG name and it cleaned up the orphaned distributed availability group row in the sys.availability_groups table.


Why does SSMS highlight SUBSTRING in purple but RIGHT in grey?

Because while you're right that RIGHT() is a function, RIGHT is also a keyword, as in RIGHT OUTER JOIN. They have to pick one (SSMS is not smart enough to detect context). I face the same problem in the syntax highlighting plugin I use for my blogs - it either has to color all instances of RIGHT as grey, or all instances of RIGHT as purple.

(Also, if you have doubts about T-SQL syntax, don't waste your time trying to rely on IntelliSense to guide you - consult the documentation, that's what it's for.)


How can I automate the process of checking of column and executing SQL statement?

Question is not clear to me but

update
    MyData
set
    Value = @id
where
    ColumnName in ('MyServiceName', 'MyServiceName2')


MySQL datatype for username of maximum 15 characters

The best way to store any short length string in plain text is to use the varchar datatype, setting the maximum length to 15, only for the purpose of integrity. The length of the username should obviously be validated within the application.

The only thing to keep in mind would be the charset of the database, table, and column. Make sure that you are using a charset which covers all of the expected characters in the username, in whatever human language they are being inserted in.


No. RMAN is aware of that possibility, hence why it creates a snapshot controlfile before performing backup. Rman needs to access control file to map objects to be backed up, and as controlfile is always in use, snapshot is used for that specific point in time. When you restore a backup, you're simply telling oracle to place those files from the backup back, so you don't need to worry about your data getting overwritten.


Social (Classmate like) database schema appreciation

1.   payment (black box)

1.1)  No currency information?
In the Payments table, although you store an amount and date, you store no currency.  Perhaps you plan on only charging in one currency, but I feel that method will only last so long if this is intended to scale.

1.2)  What is this second payments table?
What is the second payments table for?  The one below and slightly left of the actual payments table?
It seems to have only an id column, which is the only column in this diagram to specify a data type!?  I'm guessing this is some diagram artefact.

1.3)  You'll need more than just the name to take payments!
The payment_methods table currently doesn't store the type (i.e. credit card, paypal, BitCoin, whatever) of the method.  There also seems to be no linked table of payment_types for this either.

You will need to store stuff like credit card numbers, etc somewhere  (hopefully not in this database!).  You will need some sort of linking information for those stored payment methods to be kept in the payment_methods table.

2.   social (blue box)

2.1)  Who exactly is me?
I'm assuming the Me table is some sort of façade or profile type of table.  Perhaps allowing a given User to shield themselves from the embarrassment of letting their collage/university friends see the Music they were into in high school? :P

I do not believe it makes sense to double store the first, middle and last names of the user.  It would be much better to go with a solution like that which @beeks suggested in the comments above.

2.2)  My_Profile_Details... field?
I cannot figure out why this is a single field.  I assume it's some sort of memo type, for storing a lot of text (i.e. a CV of sorts).  I think this needs MUCH more decomposition.  At a minimum, into Sections of some sort.

There also seems to be nothing in a Proflie that allows a user to select what Schools/Collages/Universities/Enterprises they studied/worked at, etc?

3.   classmates (red box)

3.1)  Internationalisation?
This part of your design is missing information regarding country. Even if you're only looking for schools/colleges in a particular county, employers (which is what I assume Enterprise is for) may be in any country.

Also, If a classmate starts their own enterprise, do they have the same relationship to that enterprise as someone who works for it?

3.2)  Institution Names
You have assumed Universities (and I suppose high schools, primary schools and collages too) have only one name, and that name is in English (or whatever language you're intending to store).
It is quite common for universities to have different names in different languages.  Institution names can also change over time (renamed, etc).

3.3) Institution Decomposition
Not all Universities divide themselves into Faculties, some are simply a collection of Schools (think Oxford, etc).  These can also change name over time.  Also, both faculties (schools), universities (and probably collages, primary and secondary schools too) can merge (i.e. get taken over/bought out) over time.

3.4) Institution Location Multiplicity
Your design seems to assume a University can only be in one town.  Many Universities have different campuses in different towns.  Some even have campuses across international borders.


Changing the owner of a read-only database which is configured with Log Shipping

When you say "without re setting up LS", are you able to have an outage to fix this?

If so, then look at using tail-log backups and recovering the secondary database to make this change. The high-level process would be:

Stop LSBackup, LSCopy and LSRestore (after ensuring there are no pending copies or restores)
Perform a tail-log backup. The primary DB will now be in recovery and inaccessible
Restore the tail-log backup on the secondary and recover the database. It will now be online.
Change the database owner as required.
Perform a tail-log backup on the secondary. It will now be in recovery and unavailable.
Restore the tail-log to the primary and recover it.
Re-enable your log shipping jobs and manually execute LSBackup, LSCopy and LSRestore

After this, your primary should be back online and your secondary back in a read-only state. As stated, this will cause an outage on your primary but this may be preferable to reinitialising log shipping on a large DB. Also, make sure you test this process in a non-Production environment first, and have the entire process scripted. SQLCMD mode is a great way to run this as you can script the whole process and run from a single script using the CONNECT option to change between your primary and secondary machine.

Tail-Log Backups

SSMS in SQLCMD Mode


How is localhost resolved by Oracle listener?

By definition localhost traffic is never supposed to enter or leave the server.  It is a name for the addresses 127.0.0.1 and ::1.  It will be bound to a loopback device.  localhost is useful to allow IP access to services running on the local machine.

You likely want to use the host name of your virtual server rather than localhost.  This should resolve to you network interfaces.


You will get that any time your input file has duplicates.  In theory the MERGE command is doing everything in a batch so if you have the same row in the input data more than once it will try to insert it into the output table more than once giving you the error.  Either dedupe your input file or use a CTE at the beginning of the MERGE to take care of it for you.

create table #TempTable(Id int
                        , str1 varchar(3)
                        , str2 varchar(15)
                        , str3 varchar(10))

INSERT INTO #TempTable
           (str1
           ,str2
           ,str3)
     VALUES
           ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa');

WITH dedupe AS (SELECT DISTINCT str1, str2, str3 FROM #TempTable)
MERGE dbo.[table] AS t
    USING dedupe AS s
    ON (t.str1 = s.str1
    AND t.str2 = s.str2
    AND t.str3 = s.str3)
    WHEN MATCHED THEN
        UPDATE SET t.str1 = s.str1
    WHEN NOT MATCHED THEN
        INSERT (str1, str2, str3)
        VALUES (s.str1, s.str2, s.str3);

drop table #TempTable


what databases are already set up in the log-shipping, and which ones are not?

that worked fine but there is a much easier way to monitor and have a look at the log shipping settings by using the log shipping tables in the msdb.
for instance:
msdb.dbo.log_shipping_primary_databases

Stores one record for the primary database in a log shipping
configuration. This table is stored in the msdb database.

 select * from msdb.dbo.log_shipping_primary_databases


Send results of autogrowth from default trace to application event log

Well, if you're going to pull this information from the default trace, you need to (1) combine the info such as database name, size of growth, etc. into a single string to pass to xp_logevent, and (2) deal with multiple rows (since multiple events may have happened since the last time you checked). I'll assume that in the case of (2) you want a separate event logged for each growth event rather than one event per polling period.

DECLARE @tracepath nvarchar(500), @msg nvarchar(4000), @cr char(2) = CHAR(13)+CHAR(10);

SELECT @tracepath = substring(path, 0, charindex('\log_', path,0)+4) + '.trc'
FROM sys.traces WHERE is_default = 1;

DECLARE @db sysname, @fn nvarchar(max), @type varchar(10), @etype varchar(4),
  @eg varchar(20), @et char(17), @ed varchar(20), @cag varchar(32), @cfs varchar(20);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT
  g.DatabaseName,
  mf.physical_name,
  FileType = CASE mf.[type] WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN
    'FILESTREAM' WHEN 4 THEN 'Full-text' END,
  [type] = CASE g.EventClass WHEN 92 THEN 'Data' ELSE 'Log' END,
  EventGrowthMB  = CONVERT(varchar(20),convert(decimal(19,2),g.IntegerData*8/1024.)),
  EventTime = CONVERT(char(8), g.StartTime, 112) + N' '
    + CONVERT(char(8), g.StartTime, 108),
  EventDurSec = CONVERT(varchar(20),convert(decimal(19,2),g.Duration/1000./1000.)),
  CurrentAutoGrowthSet= CASE WHEN mf.is_percent_growth = 1
     THEN CONVERT(char(2), mf.growth) + '%'
     ELSE CONVERT(varchar(30),convert(decimal(19,2),mf.growth*8./1024.)) END,
  CurrentFileSizeMB = convert(varchar(20),convert(decimal(19,2),mf.size* 8./1024.))
FROM fn_trace_gettable(@tracepath, DEFAULT) AS g
INNER JOIN sys.master_files AS mf
ON mf.database_id = g.DatabaseID AND mf.name = g.FileName
WHERE g.StartTime >= DATEADD(MINUTE, -360, GETDATE()) AND g.EventClass in (92,93)
ORDER BY g.StartTime; -- want to log the newest one *last*, right?

OPEN c;
FETCH c INTO @db, @fn, @type, @etype, @eg, @et, @ed, @cag, @cfs;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @msg = @cr + N'Autogrow occurred (' + @et + N'): for '
      + @db + N' (' + @etype + N')' + @cr + @fn + N' ('
      + @type + N')' + @cr + N'Duration (sec): ' + @ed
      + @cr + N'Growth (MB):' + @eg + @cr + N'CurrentGrowthSetting (MB): '
      + @cag + @cr + N'CurrentSize (MB): ' + @cfs;

  EXEC sys.xp_logevent 60001, @msg, error;
  FETCH c INTO @db, @fn, @type, @etype, @eg, @et, @ed, @cag, @cfs;
END
CLOSE c; DEALLOCATE c;

Result on my system:


SQL Server AlwaysOn Availablity Group Automatic seeding

You should be able to see the reason automatic seeding didn't complete by looking in the SQL Server error log from the time period where you created the AG (on both the primary and secondary replicas).

One common cause for this failure is that the Availability Group doesn't have the "CREATE ANY DATABASE" permission on the secondary.  To correct that, you need to go to each secondary replica and run this command:

ALTER AVAILABILITY GROUP yourAgName
GRANT CREATE ANY DATABASE

Another place to look is the automatic seeding DMV.  This query is from the MS Docs:

SELECT start_time,
    completion_time
    is_source,
    current_state,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding

Check out the failure reason for clues as to why automatic seeding isn't working.

After correcting any issues you find, you can retry automatic seeding by running this code on the primary:

ALTER AVAILABILITY GROUP yourAgName
MODIFY REPLICA ON 'SecondaryReplicaName'
WITH (SEEDING_MODE = AUTOMATIC);