Innodb_buffer_pool_size the maximum you can have. If this is a dedicated box to mysql something around 16Gb should be a good start.

innodb_log_file_size depends on the amount of write you have. Run the following commands in your mysql console while database is being used:

> pager grep sequence
> show engine innodb status\G select sleep(60); show engine innodb status\G

Output will be something like this:

Log sequence number 8622728172243
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 8622740654048
1 row in set (0.00 sec)

Now given the numbers run:

> nopager
> select (8622740654048 - 8622728172243) / 1024 / 1024 * 60;

That returns you the amount of MBs written to the log file in an hour which is the rule of thumb for smoothing out write IO efficiently.

To my.cnf:

innodb_log_files_in_group = 2
innodb_log_file_size = [the calculated amount / 2]  # Because you have two logfiles

innodb_log_buffer_size depends mostly on the size of your transactions. If you don't have big blobs in large transactions somewhere around 16M to 64M should be sufficient. See what works best for your workload. Start from a low size and keep an eye on innodb_log_waits status:

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

If that increases than increase the size of the buffer.


How to disable foreign key constraints in Postgres dump file?

the command is:

alter table "tableName" drop constraint "tableName_fk1"

The foreign key must already exist in the database where you want to import, since the dump was made --data-only.


MariaDB subqueries to same table and column resulting in several columns

You are pivoting too soon.  Do the union, then pivot the results.


How can I escape XML field in XML-output query?

The naive approach would be to wrap your query around another SELECT and use replace.

SELECT replace(
    (SELECT XMLAGG(data) FROM
         (SELECT XMLELEMENT(name item, XMLFOREST(
          title,
          body as description,
          date_created as date,
          'http://example.com/news'||id as link
         )
     ) AS data FROM NEWS) AS smthng)::text, '<', '&lt;')

And do the same for >. A easier and more elgant mthod would be to to it with the language that should deliver/process/... the XML.


Best way to capture changes on OLTP for Data Warehouse: Create/Update column or CDC

You've answered your own question!

It's ALWAYS better to go with your vendor's solution rather than "rolling your own"... How many people will test your "home-cooked solution"? How many edge cases will they encounter?

I'm willing to go out on a limb here and state that maybe, just maybe, it might conceivably be within the bounds of possibility that your customer base isn't quite as large as that for Microsoft SQL Server... Microsoft have literally had MILLIONS of people test their solution - and it's likely any bugs will be patched PDQ!

This is a quote from an answer I gave previously - original quote is from Jonathan Lewis) who wrote these books! Not related at first glance, but the relevance will hopefully become obvious!

  Chapter 10: Design Disasters, by Jonathan Lewis

  More war stories, for fans of Chapter 8! "Now prepare yourself to read
  all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.

  This chapter describes some of the most common mistakes in development
  Oracle database applications. You'll certainly recognise some of them,
  because so many people stubbornly cling to certain beliefs. I know I
  like to bring up several of his points when I get into common
  arguments like these:
  1. We want our application to be "Database Independent."
  2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.
  3. We want to use sequences for our primary keys.

Just substitute "implement Change Data Capture" for "check data integrity" in item 2 and you get the picture.

I implore you to think of yourself, your fellow employees (who'll be clearing up the mess long after you've retired!), your company (if they're a decent employer) and your customers (if you care anything about them).

Step away from the keyboard and have a long hard think about this and then come up with the answer towards which I'm gently guiding you! :-)

[EDIT]: In response to points raised in the OP's comment.

No, the industry is not "switching viewpoints"! NoSQL is series of different types of compromise, solving some problems while creating a whole set of new ones! NoSQL is still niche and will forever remain so. See this article. Many NoSQL vendors are scrambling to put a) SQL interfaces over their products (cf. Google's Spanner/F1 system) and b) to implement ACID semantics - who wants an incorrect bank balance (BASE)?

You are being deafened by the sound of the wheels of a big bandwagon rolling through town and the clamouring of those who've hopped on board. Check out the writings of Michael Stronebraker on the topic - a real database pioneer and still going strong with his latest NewSQL gig, VoltDB!

Take a look at where the action is going to be in the future - it will be with systems like Spanner (two of which I have used for College projects are CockroachDB and TitaniumDB, also check out YugaByte and ActorDB) and, although still a bit "raw", they have architectures which are ACID compliant, while simultaneously answering many of the concerns of the NoSQL crowd!

As for the remarks about Azure Data Warehouse and (some) AWS systems not having FOREIGN KEYs - I'll betcha a dollar to a doughnut that this data came from systems which do have FOREIGN KEYs. Those who use them are deliberately choosing to (potentially) compromise on data integrity for the speed tradeoff that they will get in these DWs!


database mirror problem with Network Service account

Use a local system account, or create a local or domain account.

The NetworkService account has its limitations as that documentation explains.


Perhaps AWS has some stuff similar to Azure Serverles' functionality? I.e., it takes a while to spin up the database. I would increase the connection timeout drastically as a first test and then take it from there.


dbfiddle

DECLARE @string VARCHAR(MAX) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created';

WITH abc(i,x,y) AS (
  SELECT
    1 as i,
    @string as X,
    @string as Y
  UNION ALL
  SELECT
    i+1,
    SUBSTRING(Y,1,CHARINDEX(' ',Y)),
    SUBSTRING(Y,CHARINDEX(' ',Y)+1,1024)
  from abc
  where X<>''
  )
select TRY_CAST(CONCAT(a1.X, a2.X , a3.X) as DATETIME) ValidDate
from abc a1
inner join abc a2 on a2.i=a1.i+1
inner join abc a3 on a3.i=a1.i+2
where not TRY_CAST(CONCAT(a1.X, a2.X , a3.X) as DATETIME) is null;

output:

ValidDate
03/07/2019 01:20:00
03/07/2019 01:34:00
03/07/2019 03:22:00

Using LAG, or LEAD, example with dbfiddle does not seem to be less expensive than the INNER JOIN's. Results show almost equal times..

Below the example with LAG:

DECLARE @string VARCHAR(MAX) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created';

WITH abc(i,x,y) AS (
  SELECT
    1 as i,
    @string as X,
    @string as Y
  UNION ALL
  SELECT
    i+1,
    SUBSTRING(Y,1,CHARINDEX(' ',Y)),
    SUBSTRING(Y,CHARINDEX(' ',Y)+1,1024)
  from abc
  where X<>''
  )
SELECT * FROM (
    SELECT
        TRY_CAST(concat(lag(a1.x,2) over (order by a1.i), lag(a1.x,1) over (order by a1.i), a1.x) as DATETIME) ValidDate
    from abc a1
    ) x
where not x.ValidDate is null;


identify queries experiencing sos_scheduler_yield

The Extended Events option is by far the easiest solutions, once you get used to Extended Events.

Explanation

An article on SQLPerformance.com goes into the specifics behind this wait type...

  ...
  So there is one cause for SOS_SCHEDULER_YIELD: a thread exhausting its scheduling quantum and heavily recurring instances can lead to SOS_SCHEDULER_YIELD being the most prevalent wait along with high CPU usage.
  ...
  The only way to prove what's causing SOS_SCHEDULER_YIELD waits is to capture SQL Server call stacks when that wait type occurs, using Extended Events and debug symbols from Microsoft. I have a blog post that describes and shows how to perform that investigation, and there’s a great whitepaper about spinlocks and spinlock investigations that is worth reading if you’re interested in that depth of internals.

Reference: Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD

It also relates to how you can perform an analysis (blog in above quote):

Reference: SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock (SQLSkills.com)

Extended Events

Add an Extended Event to your instance with the following code:

CREATE EVENT SESSION [Capture_Statements_SOS_SCHEDULER_YIELD] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.client_hostname,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([wait_type]=(123)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Capture_Statements_SOS_SCHEDULER_YIELD.xel')
WITH (STARTUP_STATE=ON)
GO

  Caution
  Check the filename and modify to your preference!
  Check the STARTUP_STATE!
  Modify the Extended Event to your preferences when finished.

Reference: Extended Events (Microsoft Docs)

Quick solution

A quick solution is to just run the following script when you are having issues:

SELECT sdow.session_id,
       sdow.wait_type,
       sder.status,
       sder.sql_handle,
       DB_NAME(sder.database_id),
       dest.text
FROM   sys.dm_os_waiting_tasks    AS sdow
       JOIN sys.dm_exec_requests  AS sder
            ON  sdow.session_id = sder.session_id
       CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS dest
WHERE  sdow.wait_type = 'SOS_SCHEDULER_YIELD'

References

Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD (SQLPerformance.com)
SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock (SQLSkills.com)
sys.dm_exec_requests (Transact-SQL) (Microsoft Docs)
sys.dm_os_waiting_tasks (Transact-SQL) (Microsoft Docs)
sys.dm_exec_sql_text (Transact-SQL) (Microsoft Docs)
Extended Events (Microsoft Docs)


MySQL MyISAM REPAIR TABLE lost connection to MySQL server during query

First, I need to ask "Does the OPTIMIZE actually help?"  I ask because it is very rare that it makes enough difference to bother with.  Please describe the activity on the table.  One case that might need the optimize is a huge amount of deletes and/or updates (with different lengths).  If the deletes are of "old" data, then we should discuss partitioning as a much better way.

What setting do you have for myisam_recover_options?  That would let the server deal with the REPAIR automatically.

You really should consider changing to InnoDB which will mostly eliminate the need for OPTIMIZE and REPAIR.  Also MyISAM is going away.

On another topic,...  Have you identified the query/queries that are involved in the CPU spike?  It could be that we can speed them up enough to avoid the problem.  Sometimes it is as simple as adding a suitable composite index.


mysql > create table nsnl(a varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql > insert into nsnl values (null), ('asdf');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql > select * from nsnl;
+------+
| a    |
+------+
| NULL |
| asdf |
+------+
2 rows in set (0.00 sec)

mysql > select * from nsnl where a not like 'as%';
Empty set (0.00 sec)

mysql > select * from nsnl where a not like 'as%' or a is null;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

And that's how you can easily find that out yourself. Plus, when you do it yourself, you memorize it better. At least that works for me.


Differential backup to a specific date in history

If your database is configured for SIMPLE recover model, you're pretty much out of luck.

If your database is configured for FULL recovery model, you might be in luck.  This query will tell you what the recovery model is:

select recovery_model_desc from sys.databases where name='<YourDatabase>'

If your database is configured for FULL recovery, you could attempt to take a transaction log backup (example here). Then, you could attempt to restore your full backup WITH NORECOVERY and then restore your transaction log backup to a point-in-time using the STOPAT option (example here)

Assuming you can attempt a restore to point-in-time, you should probably restore to a test database to prevent overlaying the current one and then retrieve the missing data from the test database.


Is there any character length limitation for LIKE clause?

Yes. From the documentation for LIKE:

  pattern

    Is the specific string of characters to search for in match_expression, and can include the following valid wildcard characters. pattern can be a maximum of 8,000 bytes.

In your case the limit is 4,000 characters because the FOR XML PATH expression returns a Unicode string (two bytes per character). If you check the ProductList column of your temporary table, you will see the data type is nvarchar(max):

EXECUTE tempdb.sys.sp_help
    @objname = N'#tmptable';

Depending on your data, you may be able to use single-byte ANSI characters instead, giving you up to 7,998 characters for the concatenated string of ProductCodes:

CREATE TABLE #SellerProducts
(
    SellerID        integer PRIMARY KEY,
    ProductList     varchar(7998) NOT NULL,
    ProductCount    bigint NOT NULL
);

INSERT #SellerProducts
(
    SellerID,
    ProductList,
    ProductCount
)
SELECT
    s.SellerId,
    STUFF
    (
        (
        SELECT
            ',' + ProductCode
        FROM Stocks
        WHERE
            s.SellerId = Stocks.SellerId
        ORDER BY
            ProductCode
        FOR XML
            PATH('')
        )
        , 1, 1, ''
    ),
    COUNT_BIG(*)
FROM dbo.Stocks AS s
WHERE
    s.ProductCode IN ('30A','20A','42B')
    AND s.StockData > 0
GROUP BY
    s.SellerId;

The code above is deliberately designed to throw the following error if ProductList contains more than 7,998 characters:

You do not need to create a second copy of the temporary table to do the DELETE:

DELETE t1
FROM #SellerProducts AS t1
WHERE EXISTS
(
    SELECT 1
    FROM #SellerProducts AS t2
    WHERE
        t2.SellerId <> t1.SellerId
        AND t2.ProductList LIKE '%' + t1.ProductList + '%'
        AND t2.ProductCount > t1.ProductCount
);

The character limit for the ProductList column is 7,998 characters to allow two for the % characters added before the LIKE is performed - giving a total of 8,000 characters, the maximum allowed for the LIKE pattern string.


In my opinion, you can't remove the relationships you have modeled.

The general reason for avoiding such "circular" models is that in 2 years, when the next person maintains this database, they will think that they can go from group to catalogue via account or role - and technically, they can, but the route they choose determines the meaning of the relationship.

If they go via account, it is saying "This group's account owns the following catalogues".

If they go via role, it is saying "This group is permitted to access these catalogues, with these role permissions".

Both are valid relationships.

In my opinion, set really good column names that describe the relationships. If your DBMS supports adding comments/documentation on DB objects, do so (even though this feature seems little used in practice) - it is very hard to lose the documentation when it is part of the object definition (as opposed to some wiki page or text document).

Consider adding views called 'GroupCatalogues' and 'AccountCatalogues', which give a fairly good indication of what they will show, and encapsulate the joining rules in those views so that correct data is shown if your developers access the views rather than the tables directly.

Consider whether you can revoke direct access to the tables to enforce developers access the views.


MySQL / PostgreSQL: how to call a script when a row inserted / updated

In Postgres, I would do this as a trigger that uses a foreign table to make the remote table appear as a local one.

The trigger function can then query, update or join the remote table as if it was a local one and Postgres will be smart enough to push the work to the remote server (e.g. filtering, grouping, sorting etc).

The table can also be updated directly from within the trigger, no need for some scripts outside the database

If you prefer Python instead of PL/pgSQL you can write the trigger function in Python.


How to fetch the columns of the synonyms for linked servers in SQL server

Your error is that you ask your local server about the object from another, remote server.

OBJECT_ID(sys.synonyms.base_object_name) from your code will be resolved on a local server because OBJECT_ID() operates within local server, but you pass to it the name from remote server, so you'll get NULL here.

If you want to get remote object's object_id, you should do smth like this:

exec ('select object_id(''[remote_db].[remote_schema].[remote_table]'')') at [remote_server]

And in general, if you want to get the columns of remote object, you should execute your code on the remote server, looking for the columns of remote object in the remote sys.columns in the remote database, for example, this way:

declare @obj_name sysname = '[remote_server].[remote_db].[remote_table]';
declare @code nvarchar(4000) =
N'select c.name   AS COL_NAME,
       t.name     AS data_typename
from sys.columns c
     join sys.types t
        on c.system_type_id = t.system_type_id
where c.object_id = object_id(@obj_name)';

declare @param_def nvarchar(400) = N'@obj_name sysname';

execute [remote_server].[remote_db].dbo.sp_executesql @code, @param_def, @obj_name = @obj_name;

UPDATE 0

  Scenario : I have server A which has two database( database1 and
  (database2 ). I have created synonym in the database1. CREATE SYNONYM
  [dbo].[synonym] FOR database2.[dbo].[objectname] How to write query
  commonly to get object_id for this scenario and remote object ?

As Scott Hodgin mentioned, you can use PARSENAME() to get rid of remote_server's part of your object like this:

declare @cmd varchar(8000) = (select
       'select object_id(' +
       quotename (
       PARSENAME(base_object_name,3)  + '.' +
       PARSENAME(base_object_name,2)  + '.' +
       PARSENAME(base_object_name,1), '''')+ ')'
from sys.synonyms )

exec (@cmd) at [server_A];

UPDATE 1

  I want to know whether I can access the stored procedure of the remote
  server(Server B) from local server(Server A).

  ...

  I cannot get the list of the stored procedure.

You can get the list of remote stored procedures using this code:

select *
from remote_server.remote_db.sys.objects
where type = 'p';


For Close, you can use the LAST_VALUE window function to get the last value from a data set (assuming this is the Close value from the last day of the week). You can then put this in an inner select statement and do a simple aggregation on the outer statement to return your intended results.

LAST_VALUE

Note, the below example assumes a last day of the week as Saturday. If this is a different day, look at using SET DATEFIRST to set your preferred day of the week as the start.

Setup:

CREATE TABLE #DailyTable (DateLogged DATETIME,
    High DECIMAL(10,2),
    Low DECIMAL(10,2),
    [Close] DECIMAL(10,2)
)

INSERT INTO #DailyTable (DateLogged, High, Low, [Close])
VALUES ('2019-05-31 00:00:00.000', 2.12, 1.06, 1.59)
,('2019-06-01 00:00:00.000', 2.44, 1.22, 1.83)
,('2019-06-02 00:00:00.000', 0.84, 0.42, 0.63)
,('2019-06-03 00:00:00.000', 0.84, 0.42, 0.63)
,('2019-06-04 00:00:00.000', 1.24, 0.62, 0.93)
,('2019-06-05 00:00:00.000', 1.12, 0.56, 0.84)
,('2019-06-06 00:00:00.000', 0.16, 0.08, 0.12)
,('2019-06-07 00:00:00.000', 3.00, 1.50, 2.25)
,('2019-06-08 00:00:00.000', 3.52, 1.76, 2.64)
,('2019-06-09 00:00:00.000', 3.40, 1.70, 2.55)
,('2019-06-10 00:00:00.000', 3.36, 1.68, 2.52)
,('2019-06-11 00:00:00.000', 2.04, 1.02, 1.53)
,('2019-06-12 00:00:00.000', 3.32, 1.66, 2.49)
,('2019-06-13 00:00:00.000', 3.16, 1.58, 2.37)
,('2019-06-14 00:00:00.000', 0.12, 0.06, 0.09)

Query:

SELECT
    MAX(WeekEnd) AS WeekEnding,
    MAX(High) AS High,
    MIN(Low) AS Low,
    MAX([Close]) AS [Close]
FROM
(
SELECT DATEPART(WEEK, DateLogged) YearWeek,
    LAST_VALUE(DateLogged) OVER (PARTITION BY DATEPART(WEEK, DateLogged) ORDER BY DATEPART(WEEK, DateLogged)) AS WeekEnd,
    High,
    Low,
    LAST_VALUE([Close]) OVER (PARTITION BY DATEPART(WEEK, DateLogged) ORDER BY DATEPART(WEEK, DateLogged)) AS [Close]
FROM #DailyTable dt
) src
GROUP BY YearWeek

Results:

WeekEnding                  High    Low     Close
-------------------------------------------------
2019-06-01 00:00:00.000     2.44    1.06    1.83
2019-06-08 00:00:00.000     3.52    0.08    2.64
2019-06-14 00:00:00.000     3.40    0.06    0.09


Need ODBC connection to postgresql to include materialized views

I have found that Postgres views are included in the linked tables within Access. Click on any linked table within Access and within the list should be your views.  I always find it useful within Postgres to preface a view name with 'VIEW_' so that they are all readily visible and accessible within Access.


SQL*Loader-926, ORA-04031 unable to allocate x bytes of shared memory

If this was a one-time error, just try running SQL*Loader again.

If this happens repeatedly, you will need the help of the DBA.

The above views barely contain anything informative related to this issue. (Also there is no such view as V$SGA_TARGET or V$PGA_TARGET.) When this error occurs, the database instance creates a trace file on the database server (alert and file location can be found in the alert log), that is what you will need for further investigation.


Oracle 10g - How to prevent one user from hogging all resources?

There is a built-in utility called Resource Manager exactly for that purpose:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776


Having issues with postgresql, what should I log?

could not translate host name "localhost" to address

Implies a failure in your DNS resolver. You can see the error generated in libpq's fe-connect.c :1661. There is a call to pg_getaddrinfo_all which is a wrapper for getaddrinfo which is provided by glibc.

See man getaddrinfo.

Does your local /etc/hosts file have an entry for localhost, or are you relying on a remote DNS server to resolve localhost to 127.0.0.1? What are you providing in PGConn to connectDBStart?


Login permission on Availability group database

If the database is in high availability group, then No, you do not need to failover, once you do it on the primary replica, it will be picked up and replicated to to other secondary replicas.
Of course, we assume the sql login exists already on all replicas.


SQL Query to find the location of the running query?

I would use sp_whoisactive  with @get_full_inner_text and @get_outer_command :

--- download it from http://whoisactive.com
EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 1,
    @get_plans = 1,
    @get_outer_command = 1,
    @get_transaction_info = 0,
    @get_task_info = 1,
    @get_locks = 0,
    @get_avg_time = 0,
    @get_additional_info = 0,
    @find_block_leaders = 1,
    @delta_interval = 0,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
    @sort_order = '[start_time] ASC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0


Yes, PostgreSQL performance is influenced by the amount of RAM available to PostgreSQL, among many other factors.

PostgreSQL benefits from RAM for:

The buffer cache in shared_buffers. Think of this as the database's first level cache.
work_mem for performing sorts, hash joins, etc
maintenance_work_mem for index building etc
Operating system disk cache - RAM that appears to be "unused" will be used by the OS to cache disk blocks, so they're much faster for PostgreSQL to read. This is estimated with effective_cache_size.

It's not as simple as "more RAM good" though.

Lots of slow RAM will be worse than less fast RAM for a database that fits entirely in RAM on both systems. Once you have enough RAM to cache the indexes and tables in heavy use, more RAM generally makes little difference.

NUMA (non-uniform memory architecture) machines can sometimes have performance issues related to NUMA page migration because the Linux kernel doesn't understand PostgreSQL's use of shared memory properly. This can have a drastic influence on performance, so a "bigger" machine can be slower. Appropriate kernel tuning will solve this issue.

There's a cost to maintaining shared_buffers, so too big a shared_buffers can slow things down. It's a balance between having enough workspace and the maintenance cost of that workspace.

effective_cache_size tells the PostgreSQL query planner how much system RAM is thought to be used for disk cache, and will help it choose better plans. If unset or incorrectly set the planner is less likely to pick the correct plan for a system.

A machine with tons of RAM and a pathetic disk subsystem might still be slower than one with little RAM and really fast disks - depending a lot on the workload.

RAM makes less difference for write-heavy workloads, it's mostly beneficial for read-heavy workloads.

You might be noticing a theme here - it depends on what you're doing, and no single system spec like amount of RAM can be taken in isolation. It'd be nice if it was simpler, but it isn't.


Database: what design to have a “document table” referencing others tables

Its pretty easy to do this but I wouldn't recommend it.  To implement a single "document" table for all possible documents and parents, you just abstract the id into a "parent_id" column, and add a "parent_table_name" column.

There are a lot of issues with this approach.  Some are:

Referential Integrity can no longer be enforced by the DBMS.
Query expression is now more complex.
Surrogate keys of a single numeric type are required for all the parents, even if that isn't the best choice for a given parent.
You cannot show the relationships visually on an ERD.  Instead, you now just have table boxes with no lines as there are no enforceable relationships.

The perceived simplicity of a single table with an abstract reference is seductive but ultimately proves to be the opposite of simple due to the above issues.  I think the appeal results from a misapplication of the principle of cohesion.  The cohesion here is really between the documents and the entity which they are about, not their structural similarity in that each table stores a "document."  Applying the principle of cohesion with respect to structure instead of functional meaning can get you into some other ill-advised practices such as OTLT and EAV.

My recommendation is to stick with the design you already have.  It is correct and doesn't need "improved."


PostgreSQL: mark current session as "unimportant"

Is there a way to mark the LISTEN-statement or this session as "unimportant"?

Unimportant to you?  Perhaps.

Unimportant to Postgres?  No.

Every connection is as "important" as every other.

If you want to kill off an active connection, look to the pg_cancel_backend function.


Mysql query to find nth largest entry in a column

SELECT salary FROM employee ORDER BY salary DESC LIMIT 7,1;

Will find the 8th highest salary.

Closer to what you did is

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 7,1;

To explain your extremely inefficient query,...

Foreach salary in employee (in any order), test (where) to see if the subquery (( select ... )) returns (n-1).  The subquery searches the same table again (that's called a "self join") to count how many different (distinct) salaries are bigger (where e2.salary > e1.salary).  If there are 1000 rows in the table, it will have to do 1000 scans of 1000 rows each -- a million operations.

If you need to find who has the nth largest salary, that is more complicated.  If you are not worried about dups, then it is simply:

SELECT name, ... FROM employee ORDER BY salary DESC LIMIT 7,1;

If you need the DISTINCT, the a subquery is necessary:

SELECT name, ...
    FROM ( SELECT DISTINCT salary
              FROM employee ORDER BY salary DESC LIMIT 7,1
         ) AS e2
    JOIN employee AS e1 USING(salary);


Look at the original query

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

You should do a couple of things.

First, create to compound indexes

ALTER TABLE page_views     ADD INDEX started_at_itemid_ndx    (started_at,itemid);
ALTER TABLE playpack_media ADD INDEX playpack_id_media_Id_ndx (playpack_id,media_Id)

Try to refactor the query

SELECT COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId;

Perhaps breaking down the views by ItemID

SELECT IFNULL(A.media_id,'Total') Media,COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId
GROUP BY A.media_id WITH ROLLUP;


Difference between worker threads and sessions in SQL Server

It depends on the session's execution plan. If a session executes a query with a serial plan then yes, you can say 1600 queries, but if it runs a query with a parallel plan, then a session can have multiple worker threads per session.

Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?

  Worker (worker thread) – This is the logical SQL Server representation of a thread (think of it as a wrapper on top of the OS thread). It is a structure within the Scheduler which maintains SQL Server-specific information about what a worker thread does. sys.dm_os_workers. Workers are the humble servants who carry out the task assigned to them by the Master (scheduler).

  Sessions – when the client application connects to SQL Server the two sides establish a "session" on which to exchange information. Strictly speaking a session is not the same as the underlying physical connection, it is a SQL Server logical representation of a connection. But for practical purposes, you can think of this as being a connection (session =~ connection). See sys.dm_exec_sessions


How to add PK on every id column in Postgres

You can generate the SQL to add the primary key with:

select concat('ALTER TABLE ',table_schema,'.',table_name,' ADD CONSTRAINT ',
              table_schema,'.pk_',table_name,' PRIMARY KEY (id)')
from   information_schema.columns
where  column_name ='id'
and    table_schema='xxx';

Replace the xxx with the schema for which you want to create the primary keys. You only need to write the output to a file and check and execute it.


The CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backupbackup true or false?

No it does not map all the time. It depends on the interval of checkpoint happening. You can test this with the code below.

To explain what I said in my comment you can test with this code.

Use the code from here to load 300 rows.

Create a new table.

USE TestChkPoint
GO

CREATE TABLE dbo.TestTableSize
(
    MyKeyField VARCHAR(10) NOT NULL,
    MyDate1 DATETIME NOT NULL,
    MyDate2 DATETIME NOT NULL,
    MyDate3 DATETIME NOT NULL,
    MyDate4 DATETIME NOT NULL,
    MyDate5 DATETIME NOT NULL
)
GO

You will see different CheckpointLSN between diff and log1 backup if you perform below action.

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--insert new data
CHECKPOINT
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'

You will see same checkpointLSN in you diff and log1 backup if you perform this actions.  Notice I commented out manual checkpoint.

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--insert new data
--CHECKPOINT
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'

Now if you set TARGET_RECOVERY_TIME to 1 second and perform below steps without manual checkpoint you will see different checkpointLSN between diff and log backup.

ALTER DATABASE TestChkPoint SET TARGET_RECOVERY_TIME = 1 SECONDS;

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--no manual checkpoint
--insert new data change 300 to 30000
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'


What is the best solution to prevent picking the same value (by multiple requests)?

There are several approaches, but a simple one in pseudo code is:

Retry:
    SELECT tour_id based on (color, priority, pickedUp = false)
    if none selected, then no vacancy error
    UPDATE tour set PickedUp=true where tour_id=:tour_id and PickedUp=false
    If none updated, then retry
Do something with :tour_id

But pickedUp should probably be more than a boolean, recording who picked up the tour.  Otherwise if the "Do something" fails then the tour will appear booked in the database, but no one will know why.


With COPY, the server formats the data, and there is no formatting effort on the client.
You should test it – that should be simple to benchmark.


Specify NLS-Settings during Oracle installation under Windows

From the Oracle forums:  https://community.oracle.com/thread/777001

It appears that NLS_DATABASE_PARAMETERS is essentially a permanent copy of the values in NLS_INSTANCE_PARAMETERS at the time of database creation and cannot be changed later.  Reasoning given in link above is that NLS_DATABASE_PARAMETERS is used by the data dictionary to validate CHECK constraints and changing these parameters could violate those constraints.

NLS_LANGUAGE and NLS_TERRITORY need to be set to the desired values in your parameter file at the time the CREATE DATABASE statement is issued for the values to be stored in NLS_DATABASE_PARAMETERS.


Is it OK to have an entity in an ER diagram without a relationship?

An ER diagram is merely a tool to aid you in development. An ER diagram doesn't even by mandate require you to show all the entities in the system.

So to answer your question by one route: It's perfectly acceptable to have an entity on a diagram that has no relationship to any other entity. Consider a settings table that is used by a simple application to store the settings for the program. It would not need any other entities to do its job. But it would still be an object which existed within the scope of the datastore.

So to answer your question by another route: You can display as many or as few diagrams on your ER diagram as are required to show the information you're trying to represent. But if you choose to omit a relationship for purposes of clarity, it would still make sense to make a note that the R is omitted. I would personally but a -{#} with a number inside the related circles for each entity, with a footnote for that # at the bottom of the page. That way other readers won't have to ask if there should be another relation.

That's my $0.05 worth on this one anyways. The point here is that this is partly a matter of style. Some people will tell you that you always have to put the relationships (like, for instance, your boss might require that, and that would trump my advice). But the final truth is what's in the database.


What happens if you accidentally insert data into a replication target table?

Delete the rows to not cause any conflicts later. If you still need the data insert them on the publisher after you deleted them on the subscriber.

You do not need to worry about the identity seed. Replication inserts its own values and does not rely on the identity-auto-value-generation. As this is a table on the subscriber you should not insert data ever again, so to you it should not matter what the identity seed is either.


Not a direct answer about storage of the passwords, but I generally use at least two database connections when building webapps -- one's used 99% of the time for user-related activities, with restricted privileges, and the other's used for 'admin' functionality (delete users, etc.).

In a few cases, where I'm installing someone else's package, I'll install two instances ... a public facing instance which only has database access to do the general user-type stuff, and a second instance that's IP-restricted to my local subnet (possibly even on a different machine) that has to be used for any 'admin' type activities.  Neither one has access to modify tables, etc, though ... I'd rather go in via the native database tools than allow the webapp to run its own update tasks that haven't been vetted.

You can take it even further though, and add more connections specifically for given tasks ... so the user creation & password management tasks go through a user that has extra privileges on the user tables, login has database privs to authenticate and not much else, etc.

In this way, if there's a sql injection attack, on most webpages it can't really do anything significant -- can't see the password hashes, can't add a new admin user (not that they'd be able to do anything anyway), etc.  It still won't help if they manage to get a shell on your machine, but it'll slow them down.


is there any way to left join the select result in MySQL 5.7

You have an incorrect mix of operators in the wrong order, specifically your LEFT JOIN operator can't come after the WHERE or GROUP BY operators. (Normally what you're trying to achieve would be easy with CTEs but your version of MySQL doesn't have CTEs yet.)
Re-writing the query with sub-queries like this is how you can achieve your goals:
SELECT a.dt, a.groupCount, b.login_count
FROM
(
    select date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d') as dt,count(*) as groupCount
    from action_record a
    where user_id = 16459
        and action_type = 1
    group by date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')
) a
LEFT JOIN
(
    select date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') as dt,max(login_count) as login_count
    from report_user b
    where user_id = 16459
    group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
) b
ON a.dt = b.dt


Best Way to Partition By City / State in MySQL 5.5

Why not add two indexes:

a) A two column index for (state, city) - handles queries for state only, then state and city

b) An index on city - queries on cities only

To tune for performance further you may be able to add lookup tables for state and city then use numeric keys to speed up query performance.


Secure MySQL-Server completely (disable ability to reset root-password)

As indicated there is no option.

The person that has the ability to trigger the mysqld with --init-file is either root or the mysql user who can overwrite all of the mysqldatadir including all permission tables of the mysql. That is why there isn't even a considered option to achieve this.


10 minutes is an extremely high setting for deadlock_timeout, which may also explain why you even get the chance to intervene manually. the default is 1 second and the manual advises:

  The default is one second (1s), which is probably about the smallest
  value you would want in practice. On a heavily loaded server you might
  want to raise it. Ideally the setting should exceed your typical
  transaction time, so as to improve the odds that a lock will be
  released before the waiter decides to check for deadlock. Only
  superusers can change this setting.

I don't expect your typical transactions exceed 10 minutes. Try something like 20 seconds if you have long running queries.

The best defense against deadlocks is also documented here:

  The best defense against deadlocks is generally to avoid them by being
  certain that all applications using a database acquire locks on
  multiple objects in a consistent order.

If you do this consequently, there is no chance for a deadlock.


How do I dump a Postgres table and restore under another user/role?

Use the --no-owner option of pg_dump to skip the ALTER ... OWNER TO ... statements.


Can I index all columns of a table in MySQL safely?

Indexing all columns (index on each column) may or may not improve overall performance. That depends index selectivity. Consider creating covering index for your typical query. For instance, if you usually join all 3 tables, the query will mostly benefit on composite index (f1,f2,f3) . Also, if combination of these 3 fields is unique, you can define primary key on f1,f2,f3, so you don't need surrogate PK (assuming there are no other fields on this table)


Why am I getting an error about truncation when running an SSIS job?

If I remember correctly SSIS samples the first 1000 rows of a data set to determine the output datatype. If then row 1001+ exceeds the datatype that SSIS chose, you'll get a truncation error. You will need to update offending column to have a longer data type. Look in the advanced menu in your connection.


What or Who is taking SQL Server backups

The reason why you don't is because an agent is taking the backup. Look at Windows Services for something with "backup" in it's name like NetBackup or Tivoli (TDP, TSM) the service would be running using System account. The virtual device is a temporary mount point. The backup agent is sending it to an external storage then should put it to tape. Agent don't always have a GUI and a shortcut in the Start Menu. You can look also in the installed programs for a hint.


MS Access does not support CASE expressions. The most generic equivalent would be the Switch function. Using that function, a SQL CASE expression like this

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ...
END

can be rewritten like this:

Switch(
  condition1, value1,
  condition2, value2,
  ...
)

A more specific, and probably more appropriate for your situation, equivalent is the IIf function. It is intended for use when you need to return one of two values based on a single condition. In other words, it replaces the following CASE pattern:

CASE WHEN condition THEN true_value ELSE false_value END

and goes like this:

IIf(condition, true_value, false_value)

In your particular situation, the CASE expression does not have an ELSE clause. That means that ELSE NULL is implied, although ELSE 0 may be more appropriate here. So, you can rewrite the query like this:

sql = "SELECT sr.itemcode, sr.itemname, " & _
             "SUM(IIf(sr.transdate='4/14/2016', sr.temunits, 0)) AS [Units Sold], " & _
             "SUM(sr.itemunits) AS [Total Units Sold] " & _
      "FROM [tblINV_SalesRecord] AS sr WHERE sr.transdate <= '4/14/2016' " & _
      "GROUP BY sr.itemcode, sr.itemname"


Unfortunately this DMV is not accessible.

Adam Machanic has made a user feedback item, you can cast your vote to urge Microsoft to make it available.

It has been picked up by Microsoft, and has been under review since  april 6 2018.

You could get your DTU count or CPU count, depending on your model from a certain reporting interval from the sys.dm_db_resource_stats DMV.

SELECT  dtu_limit ,MAX(end_time) as end_time
FROM sys.dm_db_resource_stats
GROUP BY dtu_limit; -- DTU Model

SELECT  cpu_limit ,MAX(end_time) as end_time
FROM sys.dm_db_resource_stats
GROUP BY cpu_limit; -- VCpu Model

The maximum of 5 Dtu's on my basic tiered database is returned by the query

dtu_limit   end_time
5   2019-03-21 19:17:05.237

And as a result of being on the DTU model, VCpu count will return NULL.

cpu_limit   end_time
NULL    2019-03-21 19:17:05.237


Deploying SQL Server database changes from test to production server

There was excellent tool Adept SQL Diff, lightning fast, but it's not supported anymore and does not work on Windows 2008 onwards. So Redgate is the choice.

Maybe it's a good time to consider Continuous Integration (CI) and Agile methodology. You will need SSDT (SQL Server Data Tool) and a build software -Team Foundation Server, Bamboo, Teamcity etc. We use TeamCity, it allows you to have up to 20 projects for free. And of course, you will ned source control software, like Git, SVN or TFS.

It's a bit tricky to setup that kind of environment but once you are done and all is tested and works, your deployment will be flawless and literally "at one click".

First of all you will need to do reverse engineering and covert your production database into SSDT project. If you have many databases, you need to convert each one separately. After that you create your CI projects using your chosen build software.

You will need 3 or 4 separate environments:

Integration or test. If you do no want to run unit tests you don't need it. But most people in modern world have it to reduce regression testing. So here fresh sparkling database is created at each build and there you automated tests are run with some predictable results. So you need to fill that database with some test data. We usually do it by SQL scripts or SSIS packages. If you have small project you can do build every hour, if there are commutes (changes). Or run it on nights, if you have 20K unit tests to do.
UAT or Staging. This is where you test your deployment on live data. Before making deployment you restore production backup to the UAT database and run your deployment there. You may want to distort your sensitive customer data somehow. All errors of the deployment are noted and fixed. QA team does user acceptance testing there, if necessary. You run your deployments until you have green tick against run results.
Production. After all acceptance stuff and testing is done you can roll out your changes to production. Since you tested everything before all should go smooth. At the time of deployment you cut access to the website or database, but usually deployment the schema and small data changes take seconds.

Your typical deployment project comprises many steps, so you can deploy your website(s) and database(s) altogether and the best thing out there is project templates for centralized setup.

I did not mention 4th type of environment which is ‘Development’. Every developer has his own copy of everything (it is actually Integration environment) so can he can run/check deployment and unit tests on local workstation before he commits code to the central repository.

You don’t need 3 physical servers to have all above. You can either use virtual machines, SQL sever instances or just name your databases differently for each environments using project parameters.


Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present?

I'm not sure there's a "good reason" why the query transformation doesn't happen, other than it hasn't been implemented.
You can see a potential benefit in queries like
SELECT m.fk_val, count(f.junk)
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
group by m.fk_val

Which can have the aggregate pushed before the join if it's an INNER JOIN.
SELECT m.fk_val, count(f.junk)
FROM dbo.tbl_main AS m
INNER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
group by m.fk_val

But in the case of the conversion of OUTER JOIN + Predicate on outer column, the outer column predicate is either eliminated entirely, or can be pushed to before the join.
EG, with
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.fk_val IS NOT NULL

This query is logically equivalent to the simpler query
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
INNER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val

which has one less predicate.
And if you try to push the outer table predicate before the join without changing it to an INNER JOIN you would still have to apply the predicate after the join.
For something like
SELECT m.fk_val, m.Id
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.junk = 'a';

If you apply the f.junk = 'a' predicate before the join, you'll filter out some of the rows before the JOIN.  But then the inner rows corresponding to outer rows with f.junk <> 'a' would survive the  OUTER JOIN, and you'd have to apply f.junk = 'a' again after the join.


How do I choose a Mariadb version to install, and what is the difference between mariadb.com and .org?

On downloads, I'd go with the MariaDB.org repository management tool. There is only one 20.04 focal release for the 10.5 Beta branch at the moment (so not recommended yet for production use). The reason is that the releases include what distro builders where available at the time of release so as time goes on there will be a focal repository for every major release.

Until that happens, focal includes a 10.3.22 package version which is a good starting point.

On a normal fresh install I'd pick the latest GA stable (10.4) however since there isn't easy packages for focal yet, stick with the 10.3.22 distro package.

MariaDB.com releases are tarball only installs making upgrading harder.

On MySQL vs MariaDB, there is a large (and dry reading) compatibility page describing differences.

As its a green fields install there isn't a lot to worry about with compatibility. The main thing to look at is if MySQL/MariaDB has the features you need.


How to select records that meets specific criteria

To solve this problem, I did the following:

Created table and data:

CREATE TABLE table1
(
  id INTEGER NOT NULL,
  stage VARCHAR (10),
  nextstage VARCHAR (10),
  value1 INTEGER
);

INSERT INTO table1
VALUES
(1, 'prospect', 'qualify' , 200),
(1, 'solution', 'develop' , 200),
(1, 'prospect',  NULL     , 200),
(2, 'prospect', 'qualify' , 100),
(2, 'develop' , 'solution', 200),
(2, 'prospect', 'qualify' , 100);

Then ran the following query:

SELECT id, timeP2Q FROM
(
  SELECT
    id,
    SUM
    (
      CASE
        WHEN stage = 'prospect' and nextstage = 'qualify'  THEN value1 END
    ) AS timeP2Q
  FROM table1
  WHERE id NOT IN
  (
    SELECT id
    FROM table1
    WHERE stage = 'prospect' AND nextstage IS NULL
  )
  GROUP BY id
) AS stuff  -- subqueries require an alias, not used.
ORDER BY id;

Which gives the desired result:

 id | timep2q
----+---------
  2 |     200
(1 row)

The main query simply does the sum by id, and then the WHERE id NOT IN removes the unwanted ones. I think that there should be a date or a timestamp in there somewhere to be able to better extract information from this data! I see this as a process with stages?


Netezza Error Record size 120000 exceeds internal limit of 65535 bytes

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name,
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17)
  as varchar (100)) as StepName
FROM Table


Understanding postgres reason for picking this query plan

It looks like Postgres had no idea how your values are distributed. It guessed that there's 229117 rows with orderid = 12081976 when it really was 5 of them. It has then chosen a plan which was optimal for 229117 rows.

When you ran analyze it was able to generate and save statistics about distribution of values in your table. And then it knew that there's about 4 rows with this orderid. And this allowed it to pick a better plan.

So it is analyze that caused this. Remember to run it after large changes to database data — for example after importing from external source.

Also — when you analyze your plans try to use explain analyze [query]; instead of just explain [query];. You can paste it to explain.depesz.com for easier to understand format.


Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?

Renaming a table does not change the underlying object_id for that table.  Foreign Key relationships are tracked using object_id's.

Take a look at the information in select * from sys.foreign_keys.

Assuming the following parent/child tables

DROP TABLE IF EXISTS [dbo].[QueueDatabase];
DROP TABLE if exists [dbo].[Queue];

GO
CREATE TABLE [dbo].[Queue](
    [QueueID] [int] IDENTITY(1,1) NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [ObjectName] [sysname] NOT NULL,
    [Parameters] [nvarchar](max) NOT NULL,
    [QueueStartTime] [datetime] NULL,
    [SessionID] [smallint] NULL,
    [RequestID] [int] NULL,
    [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
    [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[QueueDatabase](
    [QueueID] [int] NOT NULL,
    [DatabaseName] [sysname] NOT NULL,
    [DatabaseOrder] [int] NULL,
    [DatabaseStartTime] [datetime] NULL,
    [DatabaseEndTime] [datetime] NULL,
    [SessionID] [smallint] NULL,
    [RequestID] [int] NULL,
    [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_QueueDatabase] PRIMARY KEY CLUSTERED
(
    [QueueID] ASC,
    [DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QueueDatabase]  WITH CHECK ADD  CONSTRAINT [FK_QueueDatabase_Queue] FOREIGN KEY([QueueID])
REFERENCES [dbo].[Queue] ([QueueID])
GO

ALTER TABLE [dbo].[QueueDatabase] CHECK CONSTRAINT [FK_QueueDatabase_Queue]
GO

Now, begin a transaction.  Select object_id's and object_names before and after executing sp_rename.  The object_names change, but the underlying object_id's remain the same.

begin transaction
--select object_id's and names before table rename
SELECT parent_object_id AS child_table_id
    ,object_name(parent_object_id) AS child_table
    ,referenced_object_id AS parent_table_id
    ,object_name(referenced_object_id) AS parent_table
FROM sys.foreign_keys

--rename the table
exec sp_rename 'dbo.queuedatabase','newqueuedatabase'

--select object_id's and names after table rename
SELECT parent_object_id AS child_table_id
    ,object_name(parent_object_id) AS child_table
    ,referenced_object_id AS parent_table_id
    ,object_name(referenced_object_id) AS parent_table
FROM sys.foreign_keys

--rollback

--before

| child_table_id | child_table   | parent_table_id | parent_table |
|----------------|---------------|-----------------|--------------|
| 1100687119     | QueueDatabase | 1068687005      | Queue        |

--after

| child_table_id | child_table      | parent_table_id | parent_table |
|----------------|------------------|-----------------|--------------|
| 1100687119     | newqueuedatabase | 1068687005      | Queue        |

Additionally, it should be noted that nothing magical happens to the name of the foreign key (assuming you used table names as part of the foreign key name)

Selecting from sys.foreign_keys reveals that the name remains the same even though it's now a little confusing since there is no table called QueueDatabase after the rename.

FK_QueueDatabase_Queue


Dropping person while their connection sleeping in SQL Server

When I try to drop a person that recently had a connection with SQL Server

Why are you doing this?  What drove the need to have to do this?

It sounds like you're running into connection pooling.  But connection pooling is there to help you, it's a great optimization.  By clearing the pool you're wiping out this caching mechanism.

My recommendation, without knowing more about what you are trying to do or what you need, would be to leave it as it is and let connection pooling do what it does best.


What information from v$ views should be included when asking questions about Oracle Problems?

It depends on what specific problem you are asking. The two queries you provided can give others some general information of your database, which should be provided in almost all your questions.

BTW, I suggest you add *open_mode* column of v$database in your query.


Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

Based on my experience with such issue and as mentioned in This Blogs.msdn article

  You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE).  The release notes also documents the fixes.

So I suggest to suppress regression you enable it.

Trace flag 9481 solved your problem because if forced SQL Server to use old optimizer. So basically you are using SQL Server 2014 but not new CE which comes with it.

Trace flag 4199 makes sure the optimizer is using all changes/fixes made to SQL Server optimizer since SQL Server 2005.

From this support article

  Trace flag 4199 was used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form. Starting with SQL Server 2016 RTM, the database COMPATIBILITY_LEVEL setting will be used enable trace flag 4199-related hotfixes on-by-default. This article describes the mechanics and policy of how plan-affecting hotfixes will be delivered for SQL Server 2016 and later versions.

There are three ways to do it depending on need.

By enabling the trace flag in a batch (by using DBCC TRACEON command) right before the target query, and then disabling the trace flag (by using DBCC TRACEOFF command) right after the query.
Starting with Microsoft SQL Server 2005 Service Pack 2 (SP2) and Microsoft SQL Server 2008, the query-level option "QUERYTRACEON" is available. This option lets you to enable a plan-affecting trace flag only during single-query compilation.
You enable it in starup parameters.

The first and second point will only make sure query/batch runs with 4199 trace flag while third point makes sure any query which runs sees this trace flag enabled.

NOTE: Enabling trace flag requires sysadmin permission so if you ask your developer to use it in query for instance he might not be able to assuming developers have limited access.

I would suggest you to use querytraceon or dbcc traceon to see if queries are performing up to mark.


If the WAN link breaks, the cluster will split into 2 clusters. The bigger cluster will be a PRIMARY cluster, meaning that it will work properly. The other partition won't accept any write until it re-joins with the PRIMARY cluster and the data in the nodes are updated.

Note that it is very important to set a good value for evs.suspect_timeout. Before this timeout occurs, the whole cluster will wait a response from unreachable node. After that timeout occurs, the cluster is partitioned. If the value is too low, slow nodes may cause a cluster partitioning. If the timeout is too high, working nodes may wait uselessly for a response from unreachable nodes.


Have you tried something like this?  Since I don't have your actual tables (nor do I have time to fake them up), I cannot test this.  However, this might help point you in the correct direction.  Failing that, you may want to look at common-table-expressions, or CTE's.

SELECT a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, MAX(rev_num) AS MaxRevNum
FROM mv_outage_duration a
    INNER JOIN mv_outhist_transformers b
        ON a.restore_operation_id = b.phase_restored_operation_id
    INNER JOIN mv_aeven d
        ON d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    INNER JOIN mv_we_outage_premise e
        ON e.xfmr = b.xfmr_name
WHERE e.we_premise = '995184'
GROUP BY a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code

EDIT;  perhaps this will help

SELECT a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, MAX(rev_num) AS MaxRevNum
FROM mv_outage_duration a
    INNER JOIN mv_outhist_transformers b
        ON a.restore_operation_id = b.phase_restored_operation_id
    INNER JOIN mv_aeven d
        ON d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    INNER JOIN mv_we_outage_premise e
        ON e.xfmr = b.xfmr_name
WHERE e.we_premise = '995184'
    AND d.weather_code IN (SELECT d.weather_code FROM mv_aeven WHERE mv_aeven.rev_num = MaxRevNum)
GROUP BY a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code;


Migrating differential backup restoration

Welcome to the forum.  You do not need to do log restores between a FULL and a DIFF backup in SQL Server.  If you do you'll break the LSN (Log Sequence Number) used to mark the delta's between the restores.  A differential backup is all the transactions from the last full backup.  SQL Server notes the last full backup in a data page when it backs up so it knows what data pages have changed.  At that point any DIFF backup from the last FULL backup will work.

Note the newer versions of SQL Server have a 'COPY ONLY' option which will NOT affect your LSN or modified pages since last FULL backup.  So basically don't use COPY ONLY.

Finally you'll want to do a restore with NORECOVERY.  I know this sounds counter intuitive at first but basically you're telling the engine do not play back the log files.

--This restores the full DB but does not replay the open transactions at the time, leaving it consistent for a DIFF or transaction log restore
RESTORE DATABASE DATABASE FROM '<pathofDBBackup>' WITH NORECOVERY;

--This is your DIFF restore and at the end you can recover.
RESTORE DATABASE DATABASE FROM '<pathofDIFFDBBackup>' WITH RECOVERY;

In case you needed more info:

Understanding How Restore and Recovery of Backups Work in SQL Server

The excellent backup internals videos by Paul Randall.

Backup Internals Demo


The user "guest" is never assigned to a server login, even on a fresh install it is classified as a SQL user without a login. As you can only set the SID of a login (upon creation), and not a user I don't believe that this is possible; sp_change_users_login does not work precisely for the reason that the guest account should never be mapped to a server login. As a result, the user "guest" is always an orphaned user. Probably not the answer you wanted though :)


I tried this on a collection with millions of documents, and hope this is going to work in a multi-billon document collection:

Create the index in the background. This can seriously hurt your .finds. We got timeouts for the duration of the index creation.
Clear the plan cache.
Create a script to scan all your data with hint to warm the index.
Delete the old index.
Clear the plan cache again.
Try to find some data with the new index, and ensure it's used with explain. You must use different keys each time to ensure explain does not cache your query.


use SQLcl with EZconnect and multiple hostnames (ADDRESS_LIST=)

EZconnect was not meant for things like that.

SQLcl can use tnsnames.ora as well. Just create one and use it:

sql -nohistory -noupdates -S $username/$password@test1 ...

Or you can do this, if you really want to provide all that stuff as a commandline argument:

sql -nohistory -noupdates -S $username/$password@"(DESCRIPTION= (CONNECT_TIMEOUT=4) (TRANSPORT_CONNECT_TIMEOUT=3) (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=example1.example.com)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=example2.example.com)(PORT=1521))) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=EXAMPLE.EXAMPLE.DBS)))" ...

Using tnsnames.ora:

[oracle@o71 ~]$ export JAVA_HOME=/u01/app/oracle/product/19.0.0/dbhome_1/jdk
[oracle@o71 ~]$ export PATH=/home/oracle/sqlcl/bin:$JAVA_HOME/bin:$PATH
[oracle@o71 ~]$ export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@o71 ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
MIN19=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  min19_o71)
    )
  )

[oracle@o71 ~]$ sql bp/bp@min19

SQLcl: Release 18.4 Production on Tue Mar 26 09:28:11 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 09:28:12 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL>


How does unnest() treat NULL values and empty arrays?

To quote Tom Lane in the thread "Should array_length() Return NULL" on pgsql-hackers:

The thing is that that syntax creates an array of zero dimensions, not
one that has 1 dimension and zero elements.  So "0" would be incorrect.

There is a related TODO item in the Postgres Wiki.
Hard to say whether array_length() should return NULL or 0 for an empty array dimension ...
Solution
You could invert the logic and process the original source array only if the length is >= 1:
WITH tbl(id, arr) AS (
   VALUES
     (1, '{a}'::text[])
   , (2, '{NULL}'::text[])
   , (3, '{}'::text[])
   , (4, '{x,y,z}'::text[])
   )
SELECT id, elem
FROM   tbl t
     , unnest (
          CASE WHEN array_length(t.arr, 1) >= 1
               THEN t.arr
               ELSE '{null}'::text[] END
       ) elem;

Returns at least one row for each source row.
Or, much simpler, use an actual LEFT JOIN (the comma in your FROM clause is short notation for CROSS JOIN):
SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;

This fills in NULL values automatically for columns of the right table if no match is found. Exactly what you are trying to achieve.
Related:

What is the difference between LATERAL and a subquery in PostgreSQL?

db<>fiddle here


You've got a couple of questions in here:

1) Any ideas on why the SQL Server process would not be following the limit set on the server?

Max server memory refers to the buffer pool, but not everything that the server uses memory for. For example, SSIS isn't part of the SQL Server engine - it's another application that just happens to come free in the box with SQL Server. (Same thing with SSAS and SSRS.) Max server memory has no impact on SSIS. This is one of the reasons you'll often hear recommendations that you should separate SSIS onto its own instance - especially if you're using SQL Server Enterprise in virtualization. At $7k USD per core, you're talking about $28k worth of licensing here, and you're working with something like $500 of RAM.

2) Does anyone know a good way of profiling memory usage within SQL server? We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).

Yes, inside the SQL Server engine, you can use DMVs like sys.dm_exec_memory_clerks. If you're using the free Opserver tool from the StackExchange guys for SQL Server monitoring, look at the memory clerks listing. If not, you can start with the memory clerks query it uses.

However, outside of the SQL Server engine - like with your SSIS packages - SQL Server DMVs can't help because this is happening out of process. You'll need to use conventional systems administration tools to do process monitoring and watch which processes use RAM.


impdp - ORA-39000: bad dump file specification

The syntax for impdp for Oracle 11 is here.  The FILE parameter that you have used should be DUMPFILE. Log should be LOGFILE.  Here is an example from the documentation

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=dpump_dir2:expfull.log


MySQL optimizing - use ram instead of CPU?

Verify that you tables are using the InnoDB engine and not the MyISAM or ARIA engine. Concurrent writes on non InnoDB tables can cause huge CPU spikes from attempts to get full table locks.


Does MySQL do UPDATE in place on NOT NULL column?

I can confirm that the comment by ypercube above is correct :)  Here are some additional details:

Updates occur in place, with the older version of the columns relocated to UNDO space.  The UNDO is used for providing MVCC, but also in the event of a rollback.  As InnoDB pages are initially written they are only 15/16ths filled, so there is some free space to reduce page splits.
Indexes contain all 'versions' of a column.  This is required to be able to perform index-only-scans while still offering MVCC.
Exception to this rule is overflow text/blob pages, which are always freshly written and not updated in place.


How much of a security risk are published conceptual schema?

Agreed with gbn (so +1), but I think there are two other possibilities at play:

It is quite possible that their conceptual schema has a lot of overlap with their physical schema.  Knowing table names gives you a decent head start in planning your SQL injection attacks.
It is very likely they don't have their conceptual schema documented.  Organizations that let programmers design their own databases often don't have any rigour in their database design process, going straight to physical implementation without any initial design.  They may not want to admit this, or they may not want to go to the time and trouble of back-creating a conceptual document that never existed.

Edit: OP has commented that the organization being asked for their conceptual schema is a governement agency.  This to my mind adds another likely possibility:

Civil servants aren't known for their love of risk-taking and so a mid-level functionary in a government department is unlikely to stick their neck out and release information just in case it might draw the attention or ire of someone further up the hierarchy.

I do still think that #2 is the most likely.


Grant an AD Group permission to edit all MSSQL Agent jobs

Only users in the sysadmin role can edit jobs they aren't the owner of via the SSMS object explorer so if you add all of the users to an AD group that is in the sysadmin server role, it will work, but you probably don't want to do that.


Recursively find the path to all leaves descending from a given parent in a tree

WITH RECURSIVE
cte AS ( SELECT id,
                parent_id,
                id::TEXT path,
                NOT EXISTS ( SELECT NULL
                             FROM graph gr
                             WHERE graph.id = gr.parent_id ) is_leaf
                FROM graph
                WHERE id = 2 /* initital node id */
         UNION ALL
         SELECT graph.id,
                graph.parent_id,
                cte.path || ',' || graph.id,
                NOT EXISTS ( SELECT NULL
                             FROM graph gr
                             WHERE graph.id = gr.parent_id )
         FROM cte JOIN graph ON cte.id = graph.parent_id)
SELECT path
FROM cte
WHERE is_leaf

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2e40ff454f302033bf5e8cba8b0b0d85
Multiple initial nodes may be applied too (WHERE id IN (0, 1)).


Perform update and raise notice in Postgres Function

The problem can be, that level for messages is too high
so try this appproach

CREATE TABLE id ( "id1" INT)

INSERT INTO id VALUES (1)

1 rows affected

do
$$
declare
  newnumer int = 90;
begin

  UPDATE id SET id1 =  newnumer WHERE id1 = 1;
SET client_min_messages TO INFO;
  raise info   '"NOTICE" is the proper RAISE (Why not "INFO"?)' ;
        -- catch notification

end;
$$
language plpgsql;

✓

SELECT * FROM id;

| id1 |
| --: |
|  90 |

db<>fiddle here


mysqldump has a"WJERE" clause in which can specify which data or number you want
mysqldump --opt --where="1 limit 1000000" database table > yourdumplimited.sql

or you can LIMIT byy time
 --where "your_date BETWEEN '2020-01-01' AND '2020-06-30'"

You can divide your table into fragmentss which you need.


"Using index condition" does not mean the same as "Using index".
Run this:
EXPLAIN FORMAT=JSON SELECT ...

If you need further help, post that in your Question.  Also show us the SELECT; it will help us go into more detail.


Proper tuning for 30GB InnoDB table on server with 48GB RAM

MyISAM Key Cache
You said you have key_buffer_size at 8GB.
Question: Do you really have 8GB of MyISAM indexes?
Please run this query
SELECT
    KBS/power(1024,0) KBS_BB,
    KBS/power(1024,1) KBS_KB,
    KBS/power(1024,2) KBS_MB,
    KBS/power(1024,3) KBS_GB
FROM
(
    SELECT SUM(index_length) KBS
    FROM information_schema.tables
    WHERE engine='MyISAM' AND table_schema NOT IN
    ('information_schema','performance_schema','mysql')
) A;

This will reveal how big your key_buffer_size would need to be IF ALL MYI PAGES WERE LOADED. To get a realistic picture run these please:
SELECT
   FLOOR((A.variable_value * B.variable_value)/power(1024,2)+0.5) KBS_USED_IN_MB
FROM
   information_schema.global_variables A,
   information_schema.global_status B
WHERE
   A.variable_name = 'key_cache_block_size' AND
   B.variable_name = 'Key_blocks_used'
;

This will give you how many MB (megabytes) the key_buffer_size is in use. You should round this up to the nearest GB.
Log File Size
5MB is the default size for innodb_log_file_size. Percona's mysqlperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out
SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

Whatever number comes back for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?


Performing Backup in 2 different ways in parallel?

There is no way to take additional transaction log backups without affecting the LSN chain. Even taking COPY_ONLY transaction log backups would be completely pointless, as the log chain of the COPY_ONLY backups would be incomplete. You can read more on that here.

You could take additional FULL backups to a local disk without affecting the log chain (if you don't take DIFF backups, the COPY_ONLY flag is not needed, as it only affects the differential base).

If the third-party tool is unreliable in some way, you could back up to a local or network disk and have the backup tool take copies of the backup files instead of taking database backups.

However, if you have issues with that product, I would rather focus on fixing those issues. Many backup tools support a limited subset of the SQL Server backup options and try to treat databases as "files" to better fit in the general purpose backup model they already have. If this is the kind of issues you're hitting, you could also consider dropping the tool altogether and use it to backup your backup files. The main advantage of such tools is centralized management and scheduling for all backups across all instances, which is something you would lose if you switch to SQL Server Agent controlled schedules.


Clustered composite key high fragmentation and fill factor

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control.  The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential.  Only after an index has more than eight pages will it be allocated additional pages from uniform extents.

At fewer than 1,000 rows, your clustered index would be well under 64K and entirely allocated out of mixed extents (unless you start forcing the issue by adjusting the fill factor, as you have seen).

You can get an idea of how this clustered index's pages are allocated by issuing this command:

DBCC EXTENTINFO( DB_NAME, systemUserLangPreference, PK_systemUserLangPreference)

Once the index is large enough that it is allocated more than eight pages, you can ensure that they are all allocated out of uniform extents by dropping the index and then creating it again.  Again, though, for such a small table, fragmentation is not going to have a performance impact, and you probably shouldn't try to solve it by fiddling with fill factor.

Lowering the fill factor might actually have detrimental results, as fewer records fit on each page, so more pages needed to be loaded into memory.  Here's more on why to be careful with the fill factor.

For more details on pages and extents, see this article.


This actually does the job by changing int to nvarchar(10) from the script that was posted by Mikael Eriksson:

select cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]','nvarchar(10)')SiteID


What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

If the Windows user account was added to an Active Directory (AD) group in the meantime, and that AD group had permission to run the procedure, then that could create this scenario where a user gained access to a procedure with no changes within SQL Server.

Also get the network admin to check if the user logged off and on again. Group membership only gets assigned at logon so if the user was added to a group and had not logged off he would not have had that group membership in his token. -  Spörri

Related Q&A: How do I assign an entire Active Directory group security access in SQL Server 2008?


How to deal with unavoidable circular references

A reasonable solution is the second one, that is to remove the MakeId FK from Vehicles, for two reasons:

The information about the Maker of a certain Model is already contained in the Model record, so storing it inside Vehicle is redundant.
Having both attributes in Vehicle is prone to errors, since one could have inconsistent information inside a Vehicle.


Is this table really in first normal form (1NF)?

If all the attributes/columns of that relation/table always contain atomic values (i.e., they accept exactly one value —neither no value, nor two or more, nor NULL marks— of the corresponding simple domain1, 2 per tuple/row), then yes, that relation/table meets first normal form (1NF); otherwise, no, it is not in 1NF, it is unnormalized.

Naturally, I do not know the informational characteristics of the business environment of relevance (e.g., what meaning is ascribed to each attribute/column by the end users and business experts, how each attribute/column is associated with the others, etc.), so who knows.

The question so far lacks any sample values (paramount factor to determine the respective domains), lacks any description about the business scenario at hand, lacks details about how the data points of significance are associated with each other, etc. (yes, the attributes/columns are represented by certain words, but the same word may carry different meanings in different contexts, thus an unrelated reader cannot know with exactitude what their connotations are in the scenario under consideration); therefore, as the post stands, it is impossible to evaluate properly the relation/table included in it.

The fact that the question does not contain that kind of necessary information is understandable if you are starting to learn about normalization according to the relational paradigm, but be aware that making guesses when laying out a database is counterproductive. In this regard, it is worth to point out that working closely with the business experts is indispensable in any professional database design project (including normalization at the logical layer, of course).

In case you are involved in a training/school course, I would highly recommend that you request an appropriate contextualization of the exercises from your teacher. If, on the contrary, you are learning on your own, you should look for sound materials in the relational field to optimize your efforts (this advice is more fitting now that you have clarified via comments that you are learning by yourself).

In agreement with the deliberations above, it is opportune to remark that relational database design is a craft that demands high precision.

1 Basically speaking, a domain is a set of values of the same type. N constraints can be attached to a domain. N relations/tables of a database can have n attributes/columns which draw their values from the same domain. An attribute/column can have, in turn, specific constraints only applicable to itself.

2 A domain is simple if (a) it is not comprised of relations/tables and (b) its values cannot be decomposed by the database management system. Avoiding non-simple domains when delineating a database is useful so as to take full advantage of the declarative power of a data sublanguage, which in practice facilitates the implementation of constraints and manipulation operations.


% would be a percentage of number of transactions (check against Total column).

90th is a percentiles - 90% of transactions finished in this time (so maximum rather than average).

Your dodgy negative results could be result of running ntpdate as a cron job or some other extreme time adjustment so discard those results and try again.


difficulties working with powershell Invoke-SQLCmd

So it turns out that the get-help invoke-sqlcmd -example shows the array variable as using 'dbname="MyDatabase"' as the syntax.  However this gives the error above but if you swap the quote positions such that the variable uses "dbname='MyDatabase'" then the script works.


DMV queries for capturing most frequently used queries and worst performing queries

'Worst performing' query depends on how you would define the word 'worst'. Is it by Duration? CPU? I/O?

For measuring highest CPU utilization of my cached query plans, I use the dm_exec_query_stats and dm_exec_sql_text DMVs.

The following query gets everything from the plan cache that was executed within the last day. It then calculates the percentage of overall CPU utilisation of that query and orders the results by that percentage.

It's an idea that I borrowed from an Itzik Ben-Gan post, although he used Extended Events to capture data

  SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext

  FROM sys.dm_exec_query_stats AS deqs
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE())
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')
  GROUP BY
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time, deqs.query_hash, dbid

  ORDER BY pctcpu DESC

Caveats

It does, however, rely on the plan being cached (not always the case), the CPU utilisation is the total from the PlanCreationTime not just the last day, and I only really use it as a rough guide for where to start looking for my issues.

However, when captured over time, it has enabled me to add/remove indexes and tweak queries to improve their performance.

Have a play with the queries and look up the definitions of the two DMVs I've mentioned above to try and get the results to your questions.


The specification really makes very little sense. Having a "rowversion" is fine and ok. But what makes sense to use for a column that is a "rowversion" is either a timestamp (so we can compare the 2 values in 2 rows to see which is earlier and which is later) or an incrementing (integer or not) value. GUIDs are not incrementing and makes zero sense to be used in this way.

Further, not even the first code sample will work. First because you would need to use the function uuid(), not the string 'uuid()':

`RowVersion` CHAR(36) NOT NULL DEFAULT uuid()

Even in that case though, it wouldn't work because MySQL does not allow anything but constants in the DEFAULT value for column (with one exception, see below). From the documentation, MySQL, CREATE TABLE Syntax:

  The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column. See ...

Check the answers in the questions: MySQL - Alter table to automatically put in a UUID and How can I get MySQL to use the value returned by a function as a default?, for some ways to put the UUID values in the column.

But in any case, this does not solve the "rowversion" problem. Whether you use triggers, procedures or modify your statements to update the UUID values, I don't see how those can be used as rowversions.


Order results from query to have a certain result at the top

Use this syntax:

ORDER BY name,CASE WHEN country='UK' THEN 0 ELSE 1 END,country


Procedure doesn't have compatible arguments

You say that your procedure has two parameters: an input INTEGER and an output VARCHAR, however, you call a procedure with only one argument, therefore DB2 is looking for the procedure that has only one INTEGER argument.

You should call it like so:

call ADMINIST.LOG_MSG_TEST(1,?);

where the question mark is a placeholder for the output argument.


How do I design this Draft Table and select from it?

With ts (u,t) as (
SELECT d.user_id, t.title t
FROM drafts d
 JOIN titles t ON t.id = d.tp_id AND d.t = 1),
ps (u,pc,pv) as (
SELECT d.user_id u, p.content pc, p.version pv
FROM drafts d
Left JOIN posts p ON p.id = d.tp_id AND d.t = 2)
select ts.t, ps.pc, ps.pv from ts
Left outer join ps on ts.u = ps.u /*
Union
select ts.t, ps.pc, ps.pv from ps
Left outer join ts on ts.u = ps.u */
Where ts.u = 1
and ps.u = 1
Order by ts.t, ps.pc, ps.pv

See [ https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/2 ]


Are having TRIGGERs for every upvote/downvote detrimental in terms of performance?

This is called an aggregate table. This is a valid strategy.

You could also defer the calculation of user reputation if you are not bothered with real-time updates. Some sites do DB maintenance during the night, rebuild indexes etc. Since we are talking about user reputation, it's no big deal if the current stats are only reflected the day after. I don't think people will even notice.

User_Reputation could be a field in your table Users. It is simply an integer value that is incremented or decremented when an upvote or downvote is recorded. If you are writing more than one records, then wrap it up in a transaction:

Add a record in Voting_Records
Update table users: increment/decrement the value of User_Reputation

It doesn't have to be a trigger, it could be a standalone SQL statement or a block of code in a stored procedure, as long as there is a transaction to ensure consistency and as mentioned above, you have to consider concurrent updates.

You can even make the DB read-only for a short while when maintenance is in progress, I think that's what Stack Exchange does (sometimes).


Is it possible to reference multiple foreign keys with one primary key?

This is a typical 1-to-n relationship: one parent store is related to several child stores.

You don't need separate junction tables to model such a relationship, and it is best to avoid unnecessary tables.

You'd add a foreign key column parent_store_id to the store table that points to the parent store. If there is no parent store, the column contains NULL.


SQL Server 2005 database log size increasing drastically?

Probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default"  recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough).

refer this link, hope it will help you


Getting deadlock on insert and select statement(sql server 2014) in java application

How to resolve this kind of deadlock?

As you have not provided your deadlock graph it will be impossible to give an answer about how to resolve the deadlock. Even with just deadlock graph sometime it is not possible to give a solution.  You might have to trace your calls and see the sequence of calls (involved in the deadlock), look at the execution plan, isolation level etc. Based on what you said (insert and select statement) I suggest you read this post which will give you a good start.

SELECT/INSERT Deadlock

How to get more clear understanding to capture deadlock?

As suggested by @spaghettidba in comment section you can look at the article mentioned there.

Few more articles for you:

Detecting and Ending Deadlocks
SQL Server Deadlocks by Example


Slow window function query with big table

Is there any way I can speed this up?

Yes. Don't use a varchar column for an integer number. Use integer or bigint if you burn that many IDs - much smaller in table and index and faster to process. Since you are ranking 10 million rows in your test, this is going to make a substantial difference.

player_id    VARCHAR(200) NOT NULL,
player_id    int NOT NULL,

Or a uuid if you must (I doubt that):

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

Your query ranks 10 million rows. This is going to take some time, even when read from the index directly and no sort step.

Side note: If you bulk-insert rows first and add index and PK constraint (and FK constraint) after, that's going to be much faster, plus you get perfect indexes without bloat without running REINDEX or VACUUM FULL.
Do make sure ANALYZE has been run on the table before testing performance, though.

What you didn't ask

.. but, going out on a limb here, what are probably looking for.

The EXPLAIN output reveals that you filter the top 20 rows: (t.rank <= 20). Your presented query does not show that. The query actually matching your EXPLAIN output would be:

SELECT * FROM (
   SELECT player_id
        , rank() OVER (ORDER BY value DESC, player_id ASC) AS rank
   FROM   player_stat
   WHERE  stat_id = 1
   ) t
WHERE t.rank <= 20;

Which can be improved dramatically:

SELECT row_number() OVER (ORDER BY value DESC, player_id ASC) AS rank
     , player_id
FROM   player_stat
WHERE  stat_id = 1
ORDER  BY value DESC, player_id
LIMIT  20;

Explanation

The important part for performance is the LIMIT clause in combination with ORDER BY matching the index: now the query reads exactly 20 rows from the top to the index, where it had to read 10000000 in your original version. We only use player_id and value, so we can still have an index-only scan. The rest is peanuts.
That's all due to the sequence of events in a SELECT query: window functions are applied before LIMIT. Only if the sort order agrees, we don't have to consider the rest of the applicable 10000000 rows.
We can use LIMIT 20 because the top 20 ranks are guaranteed to span no more than 20 rows. The PK on (player_id, stat_id) guarantees unique player_id per stat_id and since that is included in the ORDER BY, each rank is only assigned once - which also means we can use the slightly cheaper row_number() instead.


SQL Agent still attempting replication for deleted publication

It looks like you have some orphaned replication bits and/or orphaned replication agent jobs.

Note that Distribution.dbo.sp_MSremove_published_jobs will not remove your Distribution Agent jobs.  You will have to manually delete the orphaned job(s).

Locate the job(s) under SQL Server Agent -> Jobs.  The Distribution Agent job names will have the format Publisher-PublicationDatabase-Publication-Subscriber-integer.  Right-click the job -> Delete.

I hope this helps.


How to find view dependencies in Netezza?

This is actually pretty hard to do on Netezza, because there are no system tables keeping track of table dependencies in views. Therefore you need to parse every view to get the referenced objects. There is a Unix support script on the server which does exactly this, and will dump the results in a table. You might need to install the support package. Replace databasename by your database and run this command.

nz_view_references -create -schemas ALL DATABASENAME

When the script is finished (be patient), the results are located in table SYSTEM..NZ_VIEW_REFERENCES


how to remove 6 months old data automatically from MYSQL innodb table

The Event Scheduler allows to execute regular events according to a schedule. There is detailed example in my post on the Stack Overflow, you just need to change time interval value from 24 hours to 6 months.

Firstly, make sure the Event Scheduler is enabled. To enable it use

SET GLOBAL event_scheduler = ON;

After that you could crate event that will check rows creation time and delete old records. For example

CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  DELETE FROM MyTable
  WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 6 MONTH;

If there is no column with timestamp of a row creation in your table, then you can create trigger that will insert current timestamp and inserted row identificator to auxiliary table.

CREATE TRIGGER logCreator AFTER INSERT ON MainTable
  FOR EACH ROW
  INSERT INTO LogTable (MainID, Created)
  VALUES(NEW.id, CURRENT_TIMESTAMP);

Then you can use this log to get keys of main table rows that was created before specific time and delete corresponding records.

delimiter |
CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  BEGIN
    DECLARE MaxTime TIMESTAMP;
    SET MaxTime = CURRENT_TIMESTAMP - INTERVAL 6 MONTH;
    DELETE FROM MainTable
    WHERE id IN (SELECT MainID FROM LogTable WHERE Created < MaxTime);
    DELETE FROM LogTable
    WHERE LogTable.Created < MaxTime;
  END |
delimiter ;


SQL Server Reporting Services 2017 Cant connect and doesnt show in Configuration Manager

Have you done all the required steps of the installation?

Could you compare your steps with this link post?
Installing SQL Server Reporting Services 2017 (MSSQLTips.com)
Do you notice any differences?

Also check this resource:
What’s New in Reporting Services (SSRS) 2017 (SQLShack.com)

  ...because if you try to connect to a SQL 2017 Reporting Services instance or Power BI Report Server instance with anything less than Management Studio 17 (grab it, its free!) you’ll receive this nasty error:

  The error message on the web site resembles your own error message

  Don’t worry, your instance is there & (likely) online. Once you have SSMS 17 or higher you’ll be able to connect ...


Implementation of SSL ON SQL SERVER DEFAULT INSTANCE

Ok this is what i've referenced thesqldude.com/tag/makecert for creating self signed certificate. JIST :

1) MAKECERT tool used for preparing certificate with command mentioned in link above –

FYR :
     makecert -r -pe -n "CN=cs020.classic.classicinformatics.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Where "cs020.classic.classicinformatics.net" is my system FQDN.

2) Then open mmc and added console to view certificate, And it was automatically restored in the certificate store under "personal"
3)Then opened SCM and view properties of SQL SERVER protocal.

FYI: My system hostname was "cs020" and its in domain "classic.classicinformatics.com".
     If it was in workgroup then creating certifcate with "CN=cs020" would have worked,
     but i check my fullcomputer name and it was stating something else
     i.e. "cs020.classic.classicinformatics.net" , so there was it, i've used the above command and got my results.


Not a solution, but an improvement:

mysqldump ... | gzip >$DEST/$DATE.$db.sql.gz

This would avoid the I/O due to the write and reread of the .sql file.

You could also consider having N connections -- N-1 doing the biggest databases, 1 doing all the rest.  N=4 might be reasonable; there is no use making it too high -- once the I/O is saturated, no further speedup can be had.

A minor note:  Since you are invoking date more than once, there is a chance of getting a different value somewhere in the middle of the script.  Recommend capturing it in a variable.

Does the du show a significant difference from one time to the next?


Does a computed column break 3NF (third normal form)?

Computed columns are not a construct of the relational model, so it doesn't make sense to try and evaluate them using relational model terms such as 3NF.
Computed columns are a proprietary extension to SQL added by some vendors, and since they aren't logically stored (although you may persist them physically) they are not part of the tuple as they are not an attribute of the relation, but some expression based on it.

BTW - if that's the only modeling flaw you find in AdventureWorks, and most other sample databases for that matter, look closer :-)

HTH