I would prefer not to use this backup solution but I have no choice
  but to cope with it.

You are thinking absolutely correct. Please note that VMware backups take backup using snapshot mechanism its not like backup you take using TSQL Command. Its not a good idea to rely on VM backups I strongly suggest you to start takign TSQL backup specially if database recovery model is full and you want to take transaction log backup to truncate logs

  SQL Server thinks it has performed a full backup of the databases to a virtual device (physical_device_name = GUID).

This is how SQl Server thinks with any other third party tool. If you use any othet third party tool to take backup the VIRTUAL_DEVICE would be the location where you took backup and this would be logged into errorlog.

  Do you know why it works this way? Is VMware aware of SQL Server existence? Or maybe SQL Server has wrong data in msdb?

To explain how it works you must read Information Shedding Light on VDI VSS backups

  Can I run into any problems with that solution (other than trying to restore the DB from the virtual device)?

These are just snapshot backups which are not useful when you want to do point int time recovery.


Mysql Workbench 8 doesn't detect the my.ini file for my MySql 5.7 server. How can I point it to the correct file?

MySQL Workbench tries to find the config file at a common location. If there's none then it will create it, when you edit the configuration and save it (and only then).
However, you can set the ini location in your connection details. For that open the connection editor (e.g. by right clicking on a connection tile on the home screen and selecting "Edit Connection...". Once the dialog is open switch to the "System Profile" tab. On this page you will find places where you can set an own config file path and even the config file section, if that differs from the default ("[mysqld]").


Newbie question: database for a language school

This is a very broad question - you're essentially asking us to comment on an entire system design. Speaking from experience, that's virtually impossible to do on a first pass - you have some tables and you're going to put data in them. Great.

Whether it will "hang together" as a coherent system once you start working with and querying it is another kettle of fish altogether!

In order to help you, I Googled "open source language school software" and the first non-advertisement was this which might either serve as a basis for your own system or you might decide to abandon the dev route and just use it. There are a shedload of other interesting links there. You could also visit this page and search for the word "school" - there's goodies there also!

As I said above, you're asking for a comment on an entire system before it's even been written - this is more of a "I'm developing a system, I have these tables with structures x, y and z and I'm having a problem with doing a, b or c" kind of site.

Your question requires more of a position paper than a simple answer. I know myself from having developed systems that it's above all else an iterative process and that you'll go down many roads which will turn into dead ends for your particular requirements (and that's fine!), but to ask anybody to just cast an eye over a series of table definitions and even be able to comment on how well they'll work is basically impossible!

I suggest you look at the software from the Google and then move from there - if you can find programmes that meet (or come close to meeting) your requirements from the already existing F/LOSS (Free/Libre and Open Source) world, then great! The other great thing about F/LOSS is that you can adopt and adapt. HTH, and welcome to the forum! :-)


What happens if a client connection is killed while holding a lock?

The session along with the spid attached will be killed/rolled back.

https://stackoverflow.com/questions/3978227/how-to-kill-or-rollback-active-transaction

https://technet.microsoft.com/en-us/library/ms173730%28v=sql.110%29.aspx

EDIT:

I would check your logs to verify, but in essence, the socket would be forcibly closed.

It's not a database issue from what I can see...but do you have an error message you can post to clarify?

Also see Errors During Transaction Processing

  If an error prevents the successful completion of a transaction, SQL
  Server automatically rolls back the transaction and frees all
  resources held by the transaction. If the client's network connection
  to an instance of the Database Engine is broken, any outstanding
  transactions for the connection are rolled back when the network
  notifies the instance of the break. If the client application fails or
  if the client computer goes down or is restarted, this also breaks the
  connection, and the instance of the Database Engine rolls back any
  outstanding connections when the network notifies it of the break. If
  the client logs off the application, any outstanding transactions are
  rolled back


Replace space (" ") with no space ("") in one column

Try this:

To show without space:

select trim(kota) from yourtable

To change your data:

update yourtable set kota = trim(kota);

TRIM function is different to REPLACE. REPLACE substitutes all occurrences of a string; TRIM removes only the spaces at the start and end of your string.

If you want to remove only from the start you can use LTRIM instead. For the end only you can use RTRIM.


Cannot view SQL Server Agent in SSMS v17

I ended up deleting my profile in MSSQL and recreating it to fix it.


How can I set a column to NOT NULL without locking the table during a table scan?

Another answer from another postgresql contributor.

PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.

Proper implementation of index scan is difficult part. We cannot just do something like this query

select exists(select from foos where bar1 is null)

from alter table command for various reasons. Such feature will require big piece of code (and, probably, fragile code under some edge cases), much work, only for limited use cases. Something what developers do not like. Actually, pgsql-hackers community does not like how NOT NULL is stored in system catalog. It would be cleaner with a redesign of this part of the catalog. After that, it would be possible to do SET NOT NULL NOT VALID with a short lock and table validation without an exclusive lock. Similar to alter table .. add constraint ... not valid + alter table .. validate constraint for check constraint or foreign key. But such redesign is much more work, and there is no one who wants to do it.

But I have good news: in PostgreSQL 12 (and above) scan the entire table is not the only option. alter table set not null can prove correctness of NOT NULL by existing check constraints. So, one can do:

-- short-time exclusive lock
alter table foos
  add constraint foos_not_null
  check (bar1 is not null) not valid;

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

That was my patch. Yeah, this looks like a workaround. But, fortunately, it was merged and now it is possible to set not null without a long exclusive lock.


How will Oracle fill a datafile added to a tablespace?

In general, extents are allocated in a round-robin fashion among all data files in a tablespace so long as those data files have enough free space to allocate the extent.  So if you add a new data file, you would generally expect that half of the new extents would be allocated in the existing data file and half of the new extents would be allocated in the new data file.  Assuming the two data files are the same size, you would generally expect that by the time the first data file got to 100% full, the second data file would be roughly 50% full.

I'm not sure, though, how this helps you deal with increasing tablespace size without wasting physical space.  I'm hard-pressed to imagine in what circumstances knowing how extents are going to be allocated is going to help you use less physical space.  It shouldn't matter from that perspective whether you double the size of the existing data file, add a new data file, or add multiple data files and it shouldn't matter whether one data file is filled up before the next one starts being used.  In any case, you've allocated the same amount of space at the operating system level, you've allocated the same number of extents in the tablespace, and your segments are all the same size.

You would decrease the amount of space allocated at the operating system level by letting the data files autoextend rather than allocating fixed size data files but then you have to monitor the free space at the operating system level to ensure that the data files all have enough space to grow and you may complicate your ability to move data files to different mountpoints.  You may also decrease the amount of parallelism you can get in your backups forcing the backup job to run a bit longer.


How do I know which binary log file to open for continous backup?

You need them all.  Or at least any that contain the "point in time" that you need, plus all that come after that file.

When one binlog gets "full" (according to a setting), it stops being written to, and another one starts.

FLUSH LOGS also stops the writing to one binlog and starts another.  Presumably, your full backlog does such.  That becomes the point at which you can toss all older binlogs.


how to convert a master cluster to a slave cluster?

This means that I have to wait until all of my files are copied back from the new master the old one, which means a lot of data traffic

When using pg_basebackup, yes.

But, if you're confident that the existing files at the destination are almost identical to the source, rsync may be used. The rsync remote-update protocol is able to identify what parts of files changed to minimize the amount of data to transfer.

See examples of uses on the postgres wiki: Binary Replication Tutorial / Starting Replication with only a Quick Master Restart

My personal experience with rsync versus pg_basebackup is that rsync is good if the "new master" has not drifted that much from the state when it became master, like a few minutes of transactions on a moderately busy system. Otherwise it still works but pg_basebackup appears to be faster.


signal_wait_time_ms > wait_time_ms for SOS_SCHEDULER_YIELD in sys.dm_os_wait_stats

From the documentation for sys.dm_os_wait_stats (emphasis mine):

Column name
Data type
Description

...
...
...

wait_time_ms
bigint
Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.

If you see signal_wait_time_ms higher than wait_time_ms, then per the current documentation, that is a bug.  You should report it to Microsoft if it's causing you problems so they can investigate where things got off track.  It could be rounding errors in the underlying data that lead to a slight difference (you didn't mention how far off the two values were).
You said this:

the example I am looking at is from the book "Pro SQL Server 2019 wait Statistics" by Van de Laar. In a screenshot on page 120 signal wait is higher for SOS_SCHEDULER_YIELD.

Since the example is from a screenshot in a book, it's quite possible that editorial processes resulted in invalid information making it into the final published result.  In that case, I wouldn't worry about it too much.  You could contact the author to see if it's possible or likely that something happened like that (maybe they were splicing screenshots from multiple tests together for simplicity).


Is it possible to disable use of fast forward-only cursor on server side?

No, it is not possible to disable fast forward cursors on the server side, as far as I know.

If your application requests a fast forward cursor, that is what it will get, at least in 'modern' versions of SQL Server (2005+). From Using Implicit Cursor Conversions:

  Fast forward cursors are never converted.

I have long since forgotten what might or might not have worked on SQL Server 2000 and earlier in this scenario. You might find one of the implicit conversion cases in the document you reference can be made to work for you temporarily.

Back to the modern world. The fast forward cursor may compile to a static-like or dynamic-like plan, but the restriction to only generate a non-parallel plan will remain in either case.

There is generally no single class of cursor or set of options that will perform acceptably well in all situations. For best performance, the type of cursor and the options set need to be evaluated for each case.


The SQL Server Network Interface library could not deregister the Service Principal Name

I ran in to same issue and I wander for about hours to fix it, finally I ended up repairing the existing database server 2016.

download SQL Server Installation setup package from this link:
SQL Server 2016_x64_ENU
Go to setup.exe >> Maintenance >> Repair

While repairing, error prompted saying it could not restart the servers, yu can cancel that and let the repair finish. Once finished, start the SQL Servers

Right click on start and go to Computer Management >> Services and Applications >> SQL Server Configuration Manager >> SQL Server Services >> right click on all the servers and hit on start.

This fixed my issue and was back on track. I am still looking for a better answer on why or what can cause that but for now, here's the fix!

Good luck guys!


I wrote about something similar on MySQL replication: most important config parameters for performance on slave server?
Basically you should focus on the follow variables on slave (see the other post for more information):

innodb_buffer_pool_size
innodb_log_file_size
innodb_flush_log_at_trx_commit
innodb_doublewrite
innodb_flush_method
innodb_max_dirty_pages_pct
sync_binlog
sync_master_info
sync_relay_log
sync_relay_log_info

One thing to have in mind is that the slave have just a single thread to process all writes from master, let's say you master are able to handle 300 connections simultaneously, it's 300 threads writing at the same time (respecting locking issues, disk, ...)  but on the slave you have just a single one to deal with all this transactions.
Suggestion:
If you use MySQL 5.6 and have your data partitioned per database, you can take advantage of slave-parallel-workers


Error: set_valued function called in context that cannot accept a set. What is it about?

The error message isn't very helpful:

regress=> SELECT * FROM  compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM  compute_all_pair_by_craig(100);

but if you rephrase the query to call it as a proper set-returning function you'll see the real problem:

regress=> SELECT * FROM compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM compute_all_pair_by_craig(100);

If you're using SETOF RECORD without an OUT parameter list you must specify the results in the calling statement, eg:

regress=> SELECT * FROM compute_all_pair_by_craig(100) theresult(a integer, b integer);

However, it's much better to use RETURNS TABLE or OUT parameters. With the former syntax your function would be:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns table(a integer, b integer) as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;
end;
$$ language plpgsql;

This is callable in SELECT-list context and can be used without creating a type explicitly or specifying the result structure at the call site.

As for the second half of the question, what's happening is that the 1st case specifies two separate columns in a SELECT-list, wheras the second returns a single composite. It's actually not to do with how you're returning the result, but how you're invoking the function. If we create the sample function:

CREATE OR REPLACE FUNCTION twocols() RETURNS TABLE(a integer, b integer)
AS $$ SELECT x, x FROM generate_series(1,5) x; $$ LANGUAGE sql;

You'll see the difference in the two ways to call a set-returning function - in the SELECT list, a PostgreSQL specific non-standard extension with quirky behaviour:

regress=> SELECT twocols();
 twocols
---------
 (1,1)
 (2,2)
 (3,3)
 (4,4)
 (5,5)
(5 rows)

or as a table in the more standard way:

regress=> SELECT * FROM twocols();
 a | b
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)


Postgres 13 - how to downgrade password requirements?

An attacker can try to enumerate which users exist by attempting to log in as a bunch of different user names and seeing what error message is reported, or how long it took to report the error message (or in some cases, how much power the server used before reporting the error or how much noise it made or things like that) for each username.  This can be useful to the attacker because he can then not waste time guessing passwords on for users which don't exist, or can spearphish for users which  he knows do exist.
To make user enumeration hard, PostgreSQL goes through a mock authentication if pg_hba says scram authentication is needed but the requested username doesn't exist; or if the user does exist but either has no password set or has a password stored in the wrong format.  The point of a mock authentication of course is that the client doesn't know it is mock.  So if the client doesn't support scram but scram is required by pg_hba, then you get this error message, regardless of how the password is actually stored.


Is there a way to validate XML using variable as schema?

I don't think this is possible using T-SQL, however you can achieve what you need using C# and .NET CLR assembly.

Steps

Create a new C# class library project in Visual Studio.
Write a public static method that will do the work for you.
Add CLR assembly to Microsoft SQL Server.
Create a scalar function that will call your method.
Call your method from T-SQL, just like any other command.

Public static method to validate an XML document against an XSD

using System;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.Schema;

namespace SqlHelperClr
{
    public static class Xml
    {
        [SqlFunction]
        public static SqlString XmlValidate(SqlXml InputXml, SqlXml ValidationXsd)
        {
            if (InputXml.IsNull)
                throw new ArgumentNullException("InputXml", "InputXml is NULL.");
            if (ValidationXsd.IsNull)
                throw new ArgumentNullException("ValidationXsd", "ValidationXsd is NULL.");

            using (XmlReader xmlSchemaReader = ValidationXsd.CreateReader())
            {
                XmlReaderSettings settings = new XmlReaderSettings();

                settings.Schemas.Add("http://schemanamespace.example.com/", xmlSchemaReader);
                settings.ValidationType = ValidationType.Schema;

                using (XmlReader xmlReader = XmlReader.Create(InputXml.CreateReader(), settings))
                {
                    XmlDocument doc = new XmlDocument();
                    doc.Load(xmlReader);

                    ValidationEventArgs capturedEventArgs = null;

                    // Declare anonymous validation event handler to save the validation event arguments to a 'captured variable'
                    ValidationEventHandler eventHandler = new ValidationEventHandler(
                        (obj, e) =>
                        {
                            capturedEventArgs = e;
                        });

                    try
                    {
                        doc.Validate(eventHandler);
                    }
                    catch (Exception ex)
                    {
                        return String.Format("ERROR; Severity {0}: {1}", capturedEventArgs.Severity.ToString(), capturedEventArgs.Message);
                    }

                    return "OK";
                }
            }
        }
    }
}

Add CLR assembly to Microsoft SQL Server

Build the assembly, then copy the output DLL file to a location accessible by the SQL Server instance. Then run this command to register a CLR assembly in a database:

USE [Mydatabase]
CREATE ASSEMBLY [SqlHelperClr]
FROM 'C:\SqlAssemblies\SqlHelperClr.dll'
WITH PERMISSION_SET = UNSAFE;

Create a scalar function that will call XmlValidate

USE [Mydatabase]
CREATE FUNCTION dbo.fn_ValidateXsd (@InputXml XML, @ValidationXsd XML)
RETURNS NVARCHAR(1000)
AS EXTERNAL NAME [SqlHelperClr].[SqlHelperClr.Xml].[XmlValidate];
-- [AssemblyName].[NamespaceNames.StaticClassName].[MethodName]

Use fn_ValidateXsd

DECLARE @XmlDoc Xml = '<Root><NodeA>Blah</NodeA></Root>'
DECLARE @XmlSchema Xml = '' -- Schema goes here

DECLARE @Validation_Message NVARCHAR(1000)
SET @Validation_Message = dbo.fn_ValidateXsd(@XmlDoc, @XmlSchema)


Mariadb: Cannot decrease max_connections to 1, ERROR 1231 (42000): Variable 'max_connections' can't be set to the value of '1'

This sounds like a regression as the MariaDB documentation for max_connections says the range is 1 to 100000.

I can do SET GLOBAL max_connections=1; in MySQL 5.7 and 8.0, but not in MariaDB 10.2 and 10.3 (tested on 10.2.21, 10.3.10 and 10.3.12). I get a warning rather than an error, though:

| Warning | 1292 | Truncated incorrect max_connections value: '1' |

And the value gets set to 10:

SHOW GLOBAL VARIABLES LIKE 'max_connections';
-> | max_connections | 10    |

(I've taken the liberty of reporting the issue here.)

EDIT

The documentation has been updated to say the range is now 10 to 100000, and the bug report has a comment from Monty himself explaining the reason for the change - it was deliberate, so not a bug. (Basically, it was done to prevent unexpected behaviour/results.)


Can i have one foreign key to both parent and it's childs in postgresql?

this is not possible out of the box. inheritance in PostgreSQL is based on individual tables, which are only logically connected through pg_inherit. Each table is still somewhat independent. Therefore you cannot create a key on the entire structure. It is only possible to reference individual tables. What you can do, however, is to write a trigger ensuring integrity. A foreign key is basically the same behind the scenes anyway.


How to select from a table where in a list and then diff that against the same list?

If you only need your list to have the keys (I'm using minus to find the difference between the two lists, you could modify it to use NOT IN instead if you'd rather)

create type tbl_keys2 is table of varchar2(10);
/

create table tbl (
  key varchar2(10)
);

insert into tbl values( 'list' );
insert into tbl values( 'of' );

with keys as (
  select *
    from table( tbl_keys2( 'list', 'of', 'some', 'keys' ))
)
select k.column_value key
  from keys k
minus
select t.key
  from tbl t;

If your list potentially needs to have multiple columns (i.e. a key and a value where you're looking for everything that doesn't match), you can create an object type that can have those additional columns and do

create type obj_keys is object( key varchar2(10) );
/

create type tbl_keys is table of obj_keys;
/

with keys as (
  select *
    from table( tbl_keys( obj_keys('list'),
                          obj_keys('of'),
                          obj_keys('some'),
                          obj_keys('keys') ))
)
select k.key
  from keys k
minus
select t.key
  from tbl t;


best practice for 'staging' relation in stored procedure: use temp table, or view?

If you are doing this to aid debugging, rather than to facilitate restart after a failure.  I would suggest temp tables.

Temp tables are visible only to the scope in which they are created.  In other words, if two users execute the same stored procedure (SP) simultaneously they will be given different temp tables and most importantly their data will be completely isolated from each other's.  Views are globally visible (permissions not withstanding) and data written by one can be seen by all, with the risk of leakage.  If you define a view and write your working values to it you have to add extra columns to separate your values from other users'.

Temp tables are dropped automatically by the system when they go out of scope.  This makes your debug code simpler since you don't have to program around dirty restarts after crashes.

Temp tables can be created on-the-fly with the SELECT..INTO syntax.  Thus their schema will automatically keep up with changes to the surrounding schema.  Views do not have this advantage.

If you are processing many rows you can index Temp tables with only the same drawbacks that indexing normal tables incurr.  In SQL Server at least - which is my speciality - there are many restraints on a view's defintion before it can be indexed, which could affect their perormance or applicability in your case.

SQL Server makes table-valued variables available as a lighter-weight alternative to temp tables.  See this SO question for the differences.

If you do happen to be on SQL Server, and are lucky enough to have 2014 available, and your row counts are modest, these working tables are an excellent candidate for non-persisted, in-memory tables.


PostgreSQL importing thousands of columns as an array

You don't need any auxiliary tools. This works on any platform.
COPY to a simple temporary ancillary table:

CREATE TEMP TABLE tmp(txt text);

COPY tmp FROM '/path/to/your_file.csv' (FORMAT csv, ENCODING 'utf8', DELIMITER E'\b');

I chose backspace as delimiter (E'\b') which never occurs. This way you get one text column.
Assuming UTF8 encoding. Adapt if necessary.
If you can't use SQL COPY, consider the psql meta command \copy instead:

Postgres client copy (\copy) command doesn't have access to a temporary table?

Create the target table by parsing the row in the next step:

CREATE TABLE target AS
SELECT split_part(txt, ',', 1) AS patient_id
     , split_part(txt, ',', 2) AS diagnosis
     , string_to_array(substring(txt, '^(?:[^,]*,){2}(.*)'), ',')::real[] AS arr
FROM   tmp;

Simple and fast.

The temporary table dies at the end of the session automatically.
The first line of integer values can be stored in real[], too.
About split_part():

Split comma separated column data into additional columns

The regular expression in substring(): '^(?:[^,]*,){2}(.*)' - it basically says: omit the first two fields and take the rest.
This assumes no white space between values else you might want to use trim(). Related answer with more explanation and links for regular expressions:

Filter strings with regex before casting to numeric

Since an array does not seem useful for your purpose, you might instead just keep the comma-separated list:

     substring(txt, '^(?:[^,]*,){2}(.*)') AS list


Which TLS protocol and ciphers are supported by SQL Server 2005?

(Note: extended support for SQL Server 2005 ended on April 12, 2016.)
From this Microsoft support page:

Will SQL Server 2005 be supported for TLS 1.2?
TLS 1.2 support is offered only for SQL Server 2008 and later versions.

From this SQL Protocols blog entry, for SQL 2000 and 2005:

The currently recognised protocols are, from highest to lowest: TLS1.1, TLS1.0, SSL3.0, SSL2.0.

and

[The] cipher suite to use is negotiated by SSL/TLS and depends upon the cipher suites supported by the OS on the client and the server.


This is because VIEW [ANY] DEFINITION doesn't have anything to do with retrieving results that are exposed to public by default; this has to do with viewing the definition of objects, for example this should no longer be allowed:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.databases'));

Try:

DENY SELECT ON sys.databases TO public;

Or, more broadly:

DENY SELECT ON SCHEMA::sys TO public;

Note that this may cause other issues, for example I am not sure what will happen if the user tries to connect to SQL Server using Management Studio - Object Explorer probably will not load completely; IntelliSense and other features may stop working as well, since they rely on metadata access.


set variable result in case statement to be used again in the same select statement

You need to either repeat the expression, or derive it in a separate scope, e.g.:

SELECT name, tag, CASE
   WHEN tag = 'a' THEN 'a2'
   ELSE 'b2' END AS tag2
FROM
(
   SELECT  name, CASE
        WHEN name = 'a' THEN 'a'
        ELSE 'b' END AS tag
   FROM dbo.table_name
) AS derived;


When you run into a problem like this one, be sure to double-check your logs, the barman check command, and make sure you set a barman cron entry to start barman receive-wal. That needs to be running before you can execute barman switch-wal and finish your setup. =)


Conformed Fields or Snowflake? - Shop and Customer Location

This is more of an outrigger dimension. You area allowed to do this with the Kimball methodology (star schema, not snowflake) but it does add complexity to your data model (happens with date dimensions often when you have a date in another dimension).

I would simply add location information to both the shop and customer dimensions. If you want to do complex querying around which shops are visited by which customers, you could create a denormalised customer/shops dimension which has every used combination of customer and shop. This will make it much easier to query - which is what a good data warehouse will provide. Hope this helps.

Here's some stuff on outrigger dimensions.
http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/


Normalization: Is it considered compliant to split static, numeric values like a year into their own table?

The other database designer is simply wrong, but your reasoning is wrong as well. Assume you start with this table, which has a single candidate key, "game_title".

Table: game_titles

game_title                      year_first_released
--
The first game                  1998
The second game                 1999
Best game: the third one        2001
The fourth game                 2003
Forty-two, the end of games     2011

You evaluate whether it's in 2NF by asking yourself these questions.

Q: First of all, is it in 1NF?

A: Yes, it is.

Q: What are the prime attributes (attributes that are part of a candidate key)?

A: "game_title" is the only prime attribute.

Q: What are the non-prime attributes?

A: "year_first_released" is the only one.

Q: Is "year_first_released" functionally dependent on the whole of "game_title", or on just a part of it?

A: The sole candidate key, "game_title", is a single column; it doesn't even have parts. So "year_first_released" is functionally dependent on the whole of "game_title".

Voilà. You've found 2NF.

You can cut through some of the formal terms by asking first whether it's in 1NF, and then answering this question.

Q: Are there any composite candidate keys?

A: No.

Voilà. You've found 2NF again.

By definition, for a table to violate 2NF, it has to have at least one candidate key that has more than one column.

Here are your reasons for rejecting your friend's opinion.

A year is just a non-primitive numeric value.
A year is static by its very nature.
A year serves as its own identifier.
A table of years introduces additional maintenance.
A table of years might have extra rows that aren't referenced.
A table of years increases database size.

None of these reasons have anything at all to do with whether a table is in 2NF.

In designing a database, it's not wrong to consider maintenance issues, database size, unreferenced rows, range constraints, and so on. It's just wrong to call those things normalization.

Oh, and that two-column table that I provided above--it's in 5NF.


Returning Right of a string with no specific known length

If names have blanks separating First, Middle, and Last, you want to use SUBSTRING_INDEX. The function takes three parameters: the string, the delimiter, and the token position.

If the token position > 0, you collect substring from the left.

If the token position < 0, you collect substring from the right.

Let's pick one of the name from your question : G. L. Potter

Using this query

SET @str = 'G. L. Potter';
SELECT
    SUBSTRING_INDEX(@str,' ',-3) token_3,
    SUBSTRING_INDEX(@str,' ',-2) token_2,
    SUBSTRING_INDEX(@str,' ',-1) token_1,
    SUBSTRING_INDEX(@str,' ', 0) token0,
    SUBSTRING_INDEX(@str,' ', 1) token1,
    SUBSTRING_INDEX(@str,' ', 2) token2,
    SUBSTRING_INDEX(@str,' ', 3) token3
\G

Please note how you can iterate and parse tokens with SUBSTRING_INDEX:

mysql> SET @str = 'G. L. Potter';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     SUBSTRING_INDEX(@str,' ',-3) token_3,
    ->     SUBSTRING_INDEX(@str,' ',-2) token_2,
    ->     SUBSTRING_INDEX(@str,' ',-1) token_1,
    ->     SUBSTRING_INDEX(@str,' ', 0) token0,
    ->     SUBSTRING_INDEX(@str,' ', 1) token1,
    ->     SUBSTRING_INDEX(@str,' ', 2) token2,
    ->     SUBSTRING_INDEX(@str,' ', 3) token3
    -> \G
*************************** 1. row ***************************
token_3: G. L. Potter
token_2: L. Potter
token_1: Potter
 token0:
 token1: G.
 token2: G. L.
 token3: G. L. Potter
1 row in set (0.00 sec)

mysql>

If you want the second token from the right, you have to do this:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);

It runs like this:

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1) |
+-----------------------------------------------------+
| L.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

How about the third token from the right ?

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-3),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-3),' ',1) |
+-----------------------------------------------------+
| G.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

You will have to spend some time setting up iterative code around SUBSTRING_INDEX, but at least a parsing mechanism is possible when done as I described.


in what all scenarios SQL Server update statistics

For the large tables, we require to update 20% of a row to auto-update the statistics. For example, a table with 1 million rows requires 200,000 rows updates. It might not be suitable for the query optimizer to generate an efficient execution plan.

SQL Server 2016 onwards, it uses dynamic statistics update threshold, and it adjusts automatically according to the number of rows in the table.
Threshold = √((1000)*Current table cardinality)

For example, a table with one million rows we can use the formula to calculate the number of updates after which SQL Server will automatically update statistics.

Threshold = √(1000*1000000) = 31622

SQL Server updates the statistics after the approx. 31622 modifications in the object.
Note: the database compatibility level should be 130 or above to use this dynamic threshold statistics calculations.
Regarding the sampling rate, yes - automatic statistics updates do not do full scan, they do % sampling which might not always be optimal... you have to implement your own solution that will do more frequent statistics update with fullscan. Check Ola Hallengren's maintenance scripts


I'm not exactly sure what you re trying to achieve, but if you want the rows from the first leg to be ordered first, then you can add an attribute that determines from which leg the row belongs:
select myint from (
    select 1 as origin, myint from table_a where myint < 5
    union
    select 2 as origin, myint from table_a where myint > 5
) as t
order by origin, myint desc;

myint
4
3
2
1
9
8
7
6

Fiddle


How to restore mysqldump of one database in different databaseName with content of data in the same host machine

I would like to say that as I am able to restore the backup of DB1 in DB2 with the same host. When I took the mysqldump backup of DB1 from the SQLyog ultimate tool. During the mysqldump backup window just exclude these statements. Which is as follows:

Exclude USE database statement
Exclude CREATE database statement

After excluding these statements in dump file , I am able to restore the dump backup file at DB2 (Different database name with mysqldump database name) in same host name.

Hope it will helpful to other person.


How to upgrade a MySQL database using numbered scripts based on a version field

Create "main script" which detects current DB version and shells only scripts proper for it. For example, it can look like

drop procedure if exists main_proc;
delimiter @@;
create procedure main_proc
    select version into @version from service_table;
    if @version < 2 then
        source 001.sql;
        update service_table set version = 2;
    end if;
    if @version < 2.5 then
        source 02.sql;
        update service_table set version = 2.5;
    end if;
    if @version < 3 then
        source 3.sql;
        update service_table set version = 3;
    end if;
    -- and so on
    end;
@@;
delimiter ;
call main_proc;
drop procedure main_proc;

Don't forget error handlers (no script file or access error, error during script execution, etc.).


Is there such thing as an Availability Group failover trigger?

No. Triggers are not at an instance or AG level. You'd need to base something on the failover event or better yet, as part of a SQL Server Agent job, just check to see if that replica is the primary and then do (or not do) something.


Send email only if no backups within 24 hours

Instead of e-mailing the results of the query as a file attachment (which, SQL Server won't know it's empty until after it runs the query as a part of sending the e-mail), I suggest instead building up the body of the e-mail as follows, and then using a simple IF condition to only send the e-mail if at least one database has contributed to populating the string:

DECLARE
  @dblist NVARCHAR(MAX) = N'',
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT
   bs.database_name AS db,
   MAX(bs.backup_finish_date) AS d,
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs
  WHERE [type] = 'D'
  GROUP BY database_name
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,
    NULL AS d,
    9999 AS age
  FROM
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS
  (
    SELECT 1 FROM msdb.dbo.backupset
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  --EXEC msdb.dbo.sp_send_dbmail
  --  @recipients = 'dba@someemailaddress.com',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END


How to create a synonym for a column in SQL Server?

If you want to retain the table name but refer to an existing column by a new name a computed column will do this for you. The computed value of the column will be just the value of the existing column.

alter table [dbo].[Product]
add [ProductTest] as ([ProductId]);

Now you can write

select
    [ProductId],
    [ProductTest]
from [dbo].[Product];

The two columns will have the same value, always.

If you reference only the computed column my guess is there won't even be a run-time overhead.


If at least a negative number, sum; otherwise just show

You can accomplish what you're after using window functions, but I can't make any promises about performance if you're running this query over a lot of rows. The idea is to calculate the sum, minimum, and an unordered row number for every partition. Keep all rows with a minimum > 0 but only keep the first row of a partition if the minimum < 0.

-- put data into temp table for illustration purposes
select accountId, type, amount into #t220618
from (
    values
     (1, 'R', -10)
    ,(1, 'R', 30)
    ,(1, 'V', 10)
    ,(2, 'R', 20)
    ,(2, 'R', -5)
    ,(2, 'V', 5)
    ,(3, 'R', 20)
    ,(3, 'R', 30)
)   x (accountId, type, amount);

SELECT
  accountId
, type
, CASE WHEN part_min < 0 THEN part_sum else amount END amount
FROM (
    SELECT
      accountId
    , type
    , amount
    , SUM(amount) OVER (PARTITION BY accountId, type) part_sum
    , MIN(amount) OVER (PARTITION BY accountId, type) part_min
    , ROW_NUMBER() OVER (PARTITION BY accountId, type ORDER BY (SELECT NULL)) part_rn
    FROM #t220618
) q
WHERE q.part_min > 0 OR (part_min < 0 AND part_rn = 1);


UPDATE with CAST(meta->>"$.field") failing due to "null"

I think it is a bug. I cannot find any such bug report in MySQL developer zone so you should report it.

demo 1
demo 2


How do you combine multiple update statements for the same row using MySQL trigger

You can use prepared statemnts for this purpose to have only one UPDATE command.
A danger from SQL injection isn't possible, because there is no user input.
DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
  BEFORE UPDATE ON first_table
  FOR EACH ROW
BEGIN
    SET @switch = 0;
    SET @sql = 'UPDATE second_table SET ';
    IF NOT(OLD.firstname <=> NEW.firstname) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'firstname = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.middlename <=> NEW.middlename) THEN
            if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'middlename = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.lastname <=> NEW.lastname) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'lastname = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.nickname <=> NEW.nickname) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'nickname = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.dob <=> NEW.dob) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'dob = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.email <=> NEW.email) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'email = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.address <=> NEW.address) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'address = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.city <=> NEW.city) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'city = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.state <=> NEW.state) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'state = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.zip <=> NEW.zip) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'zip = CURRENT_TIMESTAMP');
    END IF;
    IF NOT(OLD.phone <=> NEW.phone) THEN
        if @switch = 0 THEN
            SET @switch = 1;
        ELSE
            SET @sql = CONCAT(@sql,',');
        END IF;
        SET @sql = CONCAT(@sql, 'phone = CURRENT_TIMESTAMP');
    END IF;
    SET @sql = CONCAT ( @sql,' WHERE id = OLD.id;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

That said, a GMP audit requieres also to have the old value saved.


CAP is basically a continuum along which BASE and ACID are on opposite ends.

CAP is Consistency, Availability, and Partition tolerance.  Basically you can pick 2 of those but you can't do all 3.

ACID focuses on Consistency and availability.

BASE focuses on Partition tolerance and availability and throws consistency out the window.


The data in a CSV file is encoded one way; the table in MySQL is encoded another way.  The difference could be 2x either way.  That is, the 25GB csv file might, for example, expand to 50GB, then have trouble adding on the indexes.

When MySQL runs out of disk space, it waits for you to free some space.  This might explain 40 hours.  Or it dies.

Does one of you have SSDs, the other have HDDs?

What was the value of key_buffer_size when using MyISAM?  Keep in mind that innodb_buffer_pool_size and key_buffer_size are competing for RAM, so you should be careful when switching between engines.

InnoDB wants a PRIMARY KEY; you don't have one.

How big is the TEXT column typically?  InnoDB stores them off-record in some situations.

When did things go sour?  During the LOAD?  Or during the CREATE INDEX?

When adding an index to a MyISAM table, it will either "repair via key buffer" or "repair by copying" (or something like that).  The clue is visible in SHOW PROCESSLIST during the CREATE INDEX.  If it is by "key_buffer", then 8M is much too small.  1G may be suitable for your machine.  If it is the other one, then I am surprised that it took so long.

Another trick (depending on the indexes you have or will have) is to first sort the data by the main key (field3? in your case) before doing the LOAD DATA.

If field1 is always 12 characters or fewer, then declare it VARCHAR(12).  (If it might get a bit bigger, pick some other limit.)  TEXT has some drawbacks compared to VARCHAR.  I am not sure whether it is relevant for this case, but it should not hurt.


ALTER DEFAULT PRIVILEGES - permission denied

The problem here is the ALTER DEFAULT PRIVILEGES statement you issued will apply only to tables that are created by user_name:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

and

target_role
The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.

So in your case you either use
ALTER DEFAULT PRIVILEGES
    IN SCHEMA schema_name
    GRANT SELECT ON TABLES TO user_name;

(without the FOR USER clause), or create the table as user_name (in which case they'll be the owner, therefore no need to grant anything).
In any case, it's a good practice to always create DB objects as a given user, so that the access privileges are predictable.


Added R Services to SQL Server 2016 Enterprise - Cannot get past Msg 39021

I know it is quite a while, but I had the very same error yesterday after
adding SQL R system Services to an SQL2016.

Besides the need to manually

add / grant  "NT Server\MSSQLFDLauncher" "LogonAsService"  (secpol.msc, or create a approriate Group)
add / grant "\SQLRusergroup"  "Allow logon locally" (secpol.msc)
to make SQLServer laucher start at all,

it turned out in my environment that the long name (I had something very similar to
"M:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData") was causing a fuss -
despite it did not include a blank as described in some findings in WWW concerning this.

I did the following:

created a short folder name "E:\MSSQL_R"
xcopied the directories from e.g. E:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData there (/s/e/v to include empty dirs)
Gave NT Service\MSSQLFDLaucher "FullControl" NTFS rights to this
changed the "WORKING_DIRECTORY" path in rlauncher.config to point there

et voila - test query worked after that.


Bad execution plan when having partial index and big In-clause in Postgres

This will be fixed in version 12, which will be released soon.

I think the summary here is that we are only willing to do so much work to try to prove a partial index can be used, because all queries have to go through that work even if they don't end up using the partial index.  In this change, they just found a more efficient way to do that work in this particular case, and so no longer impose the 100-element limit to it.


You can use the Modulo operator for this purpose.

  Modulo
  Returns the remainder of one number divided by another.

In this case:

WHERE (Num - 1) % 4 = 0

I've set up a sample, generating a series of 1K integers.

--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
  p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
select row_number() over(order by i) as val
into integers
from p1;
GO

1024 rows affected

select top 25 *
from integers
where (val - 1) % 4 = 0
GO

| val |
| :-- |
| 1   |
| 5   |
| 9   |
| 13  |
| 17  |
| 21  |
| 25  |
| 29  |
| 33  |
| 37  |
| 41  |
| 45  |
| 49  |
| 53  |
| 57  |
| 61  |
| 65  |
| 69  |
| 73  |
| 77  |
| 81  |
| 85  |
| 89  |
| 93  |
| 97  |

dbfiddle here


The column 'Date Offset' in table 'Date' has invalid bindings specified

The problem in this case was that I was trying to replace an existing column also named 'Date Offset' - this was not a calculated column, but taken from the source table.

Resolution was to do the deployment in 2 phases, first remove the old column, then deploy again to add the new calculated column.


How can I query for existing database snapshots in SQL server?

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new.

IF NOT EXISTS(
    SELECT
        *
    FROM
        sys.databases
    WHERE
        name = 'DatabaseA_Snapshot'
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot]
    ON (NAME=DatabaseA, FileName='<whatever>')
    AS SNAPSHOT OF [DatabaseA]
END


If you want to setup slave from another slave steps would be as follows

Flush tables with read lock to lock the slave so it should not get updates
show slave status\G and note master co-ordinates
Take backup now using mysqldump
unlock tables to unlock the slave
transfer that backup and restore it to new one
Hope you have issues grant replication slave on master so need to use change master on the new slave and give co-ordinates noted in step-2

For details regarding master-slave version difference, shouldn't be a big issue but a good idea is to go through MySQL documentation.

Hope it helps


Why does MySQL forbid LOCK TABLES in stored procedures?

I couldn't find a "here's why" statement either, but from reading the items at the links below, it appears to be a combination of:

preferred best practice: use SELECT...FOR UPDATE
preference for row-level-locking over table-level locking
performance issued related to concurrency and the finer-granularity of row-level-locking
other alternatives appearing on the MySQL Stored Procedure Forum included switching the storage engine (though that still wont get a LOCK TABLES statement into an SP) if table-level-locking is truly justified or using transactions rather than table-level-locking. Those elections probably come down to other considerations not appearing in the original question.
Also saw a statement on stored procedures declaring that "arbitrary SQL Statements" were not permitted. I didn't hunt for a list of "arbitrary SQL."

Good luck


All the information regarding statistics gathered with dbms_stats can be found on dba_tab_stats_history.

Ref.: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2103.htm#BGBBBGCC


Split database for security and privacy reasons

A logical separation by databases is a sensible move.

There is no performance impacts of this compared to table separation.

Programming becomes referencing by database.table rather than just table.

Eventually you'll need to raise the table cache sizes in your server.

Be careful with permissions and provide guidance for your programmers. Too often they open up permissions to try to make things work.


Postgres performance difference on insert

Very likely your problem is that you have an index on the source table which is making the first hits easy.  However at some point you are hitting rows that are not in the cache and so this is requiring a sudden increase in disk I/O accounting for the performance drop.  In essence suddenly you start missing the cache and so you end up having to read a bunch of things from disk again.


If you are shrinking a log file, you won't be able to shrink it any less than the active log portion.  In other words, if you are in Full Recovery Model, and you haven't done any transaction log backups (which is why it could have grown that large), then running a DBCC SHRINKFILE will do absolutely nothing.

BOL has an example on how to do this:

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

That was taken straight from BOL on DBCC SHRINKFILE.

It is worth noting in the above example that changing the Recovery Model to simple breaks the log chain.  So once this operation is complete, you need to immediately start a new log chain by running a database backup.

It is also worth noting that if your log file is growing like this, you are not taking [enough] transaction log backups (provided you are indeed in Full Recovery Mode, as this would only happen in very rare situations in Simple Recovery).  You may need to either start taking transaction log backups, or backup your log more often so it truncates the trans log and creates a portion of the file reusable so  you don't have this file growth.


I suggest you build seperate tables for all repeatable data. This is good database design and the DBMS you are using should be able to handle it.
It provides you with these benefits:

You arrange data into logical groupings such that each group describes a small part of the whole.
You minimize the amount of duplicate data stored in your database.
You organize the data so when you modify it, you make the change in only one place.
You build a database in which you can access and manipulate the data quickly and efficiently.
Your database is better maintainable in case your data model changes.


Eithers of these 3 should work:

SELECT color
    , HEX_1 = '#'
        + CONVERT(varchar(6),
            CAST(ABS(color) as varbinary(1))
            + CAST(ABS(color/256) as varbinary(1))
            + CAST(ABS(color/256/256) as varbinary(1))
        , 2)
    , HEX_2 = '#'+
        +CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2)
        +CONVERT(varchar(2), CAST(ABS(color/256) as varbinary(1)), 2)
        +CONVERT(varchar(2), CAST(ABS(color/256/256) as varbinary(1)), 2)
    , RGB = 'rgb('
        + CAST(ABS(color)%256 as varchar(3)) + ','
        + CAST(ABS(color/256)%256 as varchar(3)) + ','
        + CAST(ABS(color/256/256)%256 as varchar(3)) + ')'
FROM (
    values
        (-65280)
        , (-65535)
        , (-460293)
        , (-13606962)
        , (-3678732)
) as colors(color)

Output:

color       | HEX_1     | HEX_2     | RGB
-65280      | #00FF00   | #00FF00   | rgb(0,255,0)
-65535      | #FFFF00   | #FFFF00   | rgb(255,255,0)
-460293     | #050607   | #050607   | rgb(5,6,7)
-13606962   | #32A0CF   | #32A0CF   | rgb(50,160,207)
-3678732    | #0C2238   | #0C2238   | rgb(12,34,56)

The CONVERT with style 2 requires SQL Server 2008 or later.

The expression could be used in a computed column definition, or inline table-valued function.


On docker volume restore: could not access file "$libdir/timescaledb-1.6.0": No such file or directory

Docker images tagged with latest contain the latest release at the time you retrieve it (this is Docker's convention), thus the image in the source contains different TimescaleDB's version (1.6.0) than in the current latest image (2.1.0 is latest now). This leads into the error of missing binary, since the images are not the same and the current latest image doesn't contain 1.6.0, which was latest some time ago.
The easiest solution is to download the image with the same TimescaleDB version as in the source. I believe timescale/timescaledb:1.6.0-pg11 should work in your case.
You can double check, which TimescaleDB's version is in use, by calling \dx in each database.
Note that a TimescaleDB's image usually contains 5 older versions before the tagged one. So if you use image tagged 1.7.3-pg11 it is likely to work too, since it expected to contain binaries for 1.6.0-pg11. This is important to know if you are going to update TimescaleDB version from 1.6.0 to later, since you will need to use several images to get to the current latest version.
In general to avoid confusion it is better to use an image with specific version tag.


The slickest way to shutdown mysql when it does that is simply to run

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

Here is why:

The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard service mysql stop from working.

It is not enough to say

mysqladmin -uroot -p -h127.0.0.1 shutdown

because mysqld will route a user coming in as root@127.0.0.1 to root@localhost if TCP/IP is not explicitly enabled. By default, mysqld will choose the least path of resistance and connect root@127.0.0.1 to root@localhost via the socket file. Yet, if there is no socket file, root@localhost will never connect.

Even the MySQL Documentation on mysqladmin says this:

  If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

That is why it is imperative to enable TCP/IP:

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

Back on Sept 30, 2011, I scripted my own version of mysqld_multi called mysqlservice (See my post : Running multiple instances on the same host). It serves as a virtual engine for connecting to mysqld from different ports. You just have to bring your own my.cnf with customized parameters. In that script, I issue shutdowns like this:

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQLD_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY}   ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${MYSQLD_HAS_BEEN_SHUTDOWN} -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

But what is ${MYSQLD_STOP} ?

MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1 --protocol=tcp"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"

Please notice I use 127.0.0.1 and an explicit port. That way, I am not relying on a socket file.

I have always used mysqladmin --protocol=tcp shtudown as the proper alternative to shutdowns of mysql if service mysql stop hangs. Doing kill -9 on mysqld and mysqld_safe should the last of the last of the last resorts. (Yes, I said last three times).

Many times, mysqld has deleted mysql.sock without warning. Other people have had this issue as well over the years:

Problem With Shutdown

Original Post : http://forums.freebsd.org/showthread.php?t=28924
Solution using mysqladmin shutdown : http://forums.freebsd.org/showpost.php?s=8d095ca69da3daf2ad4b157c8ad95f1f&p=161653&postcount=10

http://forums.cpanel.net/f354/cant-connect-local-mysql-server-through-socket-var-lib-mysql-mysql-sock-111-a-78444.html

EPILOGUE

The secret is just as I stated: Connect to mysql using mysqladmin via TCP/IP (--protocol=tcp) and issue shutdown. This has to work because the shutdown privilege is in mysql.user for the exclusive purpose of authenticated shutdowns. This has saved my workday a few times when I was able to issue a remote shutdown from my Windows machine when shutting down mysqld on a Linux server.

UPDATE 2013-03-06 22:48 EST

If you are worried about what's going on during the shutdown, there is a way to manipulate  the shutdown time and the way the data is flushed to disk, especially if you have lots of InnoDB data in the Buffer Pool

SUGGESTION #1

If you have a lot of dirty pages, you can lower the innodb_max_dirty_pages_pct to 0:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Set this about 15-30 minutes before shutdown. This will give mysqld the least possible amount of dirty pages to write to disk.

SUGGESTION #2

By default, innodb_fast_shutdown is 1. There are three values for this option

0 : InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down.
1 : InnoDB skips these operations at shutdown, a process known as a fast shutdown.
2 : InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

Documentation further says this :

  The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

  Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

Defaults for innodb_max_dirty_pages_pct and innodb_fast_shutdown should be just fine in most cases.


SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only

Create a stored procedure that uses EXECUTE AS OWNER to run the procedure in question.


How can I save a severely fragmented database/table?

If you have a clustered index on the table the fastest way to defragmanet your table would be to drop and recreate the clustered index. On versions prior to SQL Server 2005 you can defrag standard indexes using the DBCC INDEXDEFRAG command:

http://msdn.microsoft.com/en-us/library/ms177571.aspx

Or the more prefered way on SQL Server 2005 and above is by using the ALTER INDEX statement:

http://technet.microsoft.com/en-us/library/ms188388.aspx

If you have no indexes at all on the table then you will need to defragment the file using Windows (although this does not move the data in the file - it only moves fragments of the file closer to each other.)

As for the filegroups question; multiple files can help improve performance when the files are on separate disks. This is because you can take advantage of concurrent read/writes. It's generally accepted as good practice to put large indexes in a different file on a different drive to the table that they belong to.

I hope this helps you.


How to update mysql 5.7 to 8.0 Amazon linux 1, ec2

Please check this.
https://mysqlserverteam.com/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/
it might help you................


You have two issues here:

large offset: PG will need to scan through the provided offset number of rows and will prefer an index to do this above a table scan.
how to index the data: I think a partial index might help you here. Can you try

create index xxx_idx on latest_channel_snapshots(view_count DESC NULLS LAST) where network_id is null;

See http://www.postgresql.org/docs/9.2/static/indexes-partial.html for more information on partial indexes. In particular, example 11-2 on unbilled orders seems to apply to your case.


how to get last date of today in datetime datatype?

Why on earth do you need the last possible value of today? Stop using BETWEEN; it is much easier (and much more reliable, and consistent across various date/time data types) to use an open ended range:

DECLARE @today SMALLDATETIME;

SET @today = DATEADD(DAY, DATEDIFF(DAY, '20000101', GETDATE()), '20000101');

SELECT ... FROM ...
WHERE date_column >= @today
AND date_column < DATEADD(DAY, 1, @today);

The problem with what you're doing is that the data type can affect what happens. If the underlying data type is SMALLDATETIME, it rounds up; if the underlying data type is DATETIME or DATETIME2, subtracting a second is not going to capture all of the data from that day. This is actually one of my favorite slides in my T-SQL : Bad Habits and Best Practices talk. For a lot more details, please read these blog posts:

What do BETWEEN and the devil have in common?
Bad Habits to Kick : mis-handling date / range queries


Increasing enough RAM for OS from Total Physical RAM

(Full disclosure: I created the Max Server Memory Matrix and associated script that you are referring to in your question.)

Per this excellent blog post, which contains a detailed explanation of the issue you're experiencing, you'll find this little quote:

  The recommended Page reads/sec value should be under 90. Higher values
  indicate insufficient memory and indexing issues.

Without knowing too much about your environment, as you did not give us very much to go on, you may benefit from more RAM. For instance, your database is 400GB in size, and data access patterns require large scans to access lots of data.

On the other hand, your indexes might need attention. For instance, your database is 400GB in size, and data access patterns require large scans, when in fact you only need to read one or two columns in that table.

Instead of worrying about buying hardware, perhaps it would benefit you to start with the basics:

Have you analysed the missing index DMVs to identify possible new indexes?
Have you considered using filtered indexes?
Have you rewritten your queries to make better choices with data?

The point is, there's a lot you can do before considering purchasing more RAM. Figure out what your queries are doing, and help them make better life choices.

Brent Ozar has just open-sourced his course on how the database engine works, and I reckon you would benefit from that, as well as many other free online resources.


Can't update "CO2" to "CO₂" in table row

The subscript 2 is not part of the varchar character set (in any collation, not just Modern_Spanish). So make it a nvarchar constant:

UPDATE test SET description = N'CO₂' WHERE id = 1;


This code is from a system stored procedure called sp_spaceused, which returns the amount of allocated space (pages) for database objects.

Look for home-brewn monitoring jobs or maintenance scripts that may be running a bit too frequently.


Because we have the candidate keys AB, BC and BD, we have no non-key attributes. This means that the second normal form is approved, because no non-key attribute partially depends on a key. It follows that the relation scheme R must be in the third normal form!


MySQL DB as Master for some tables and Slave for others

Yes you can. MySQL 5.7 and later support filters for replication which allow you to exclude arbitrary tables:
https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
Caveat: This is a slave-side option, which means that sensitive data will still be in the relay logs on the client side.


"invalid transaction termination" in Postgres when trying to update a value using a function

You can not commit in a trigger, and you forgot the return statement.
CREATE OR REPLACE FUNCTION increment_count()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
    BEGIN
      UPDATE posts SET count=count+1 WHERE pid=NEW.pid;
      return null;
    END;
  $$;

In case of a BEFORE trigger, "return NEW" should be used!


I highly recommend you use an .accdb file, not an .adp file. Support for .adp has been removed since Access 2013.

Migrating will vary depending on the application, but quite often isn't an issue at all. Access has high compatibility between versions, and so has MS SQL server.

You can ask about specific problems here or on Stack Overflow depending on the nature of the problem.


Query optimizer triggering statistics update seems to be slowing down queries

So its not that you get a bad plan, your issue is that you have somebody becoming a victim of the auto-stats, right?
Since you probably know what table(s) we're talking about, I suggest you disable auto-update at the table (or even stats) level. You can do this using sp_autostats. Now you don't affect the rest of the database. And of course handle the stats update on this stats yourself.
This assumes that you get OK plans even when stats are a bit out of whack. If not, then async can be an option, but you won't have control over when the load of this stats update will take place.
I wouldn't go the hint route unless you identify that you indeed get bad plans with old stats. So consider the plan quality a different aspect. They are related of course, but out-of-date stats doesn't necessarily mean bad plans.
If it is helpful, you can capture when auto-update stats happens with an XE trace. Either just count how many you have over a day, say. That wuuld be using the Event Counter XE target. Or capture the actual event (to a file target) if you need more details than "I had this many in total over the time of the trace running".
For instance, I have my "Looking for strange" trace for things like this (just counting how many - then modify the trace as you want to limit the events and/or add a file target).


Stale Statistics and View performance on SQL Server 2016

What could be the reason that even with Auto Update Statistics enabled and level 130, the statistics are stale?

Automatic statistic updates are not enough
You should be proactively updating statistics  (more often than Auto Update Stats does) with FULLSCAN, to get better cardinality estimations and better query execution plans.
For more info watch this (Brent talks statistics sometime later in the video):
https://www.brentozar.com/training/think-like-sql-server-engine/

Is it advisable (or a good idea) to have a utility job every night, post nightly ETL batch completion to update the statistics of all tables?

Yes, update statistics with FULLSCAN for tables affected by the ETL batch processing


I would try the following:

Install an oracle 10g database  software that I download from the oracle software download page (i have the appropriate license)
create an empty database with this software
using the exp-tool (classical export, not datapump) to make an export from the database
import the dumpfile (with the classical imp tool) in the empty 10g database I created
now i do the export from this database and import this in the 11g database

step 3. is possible because the classical export does not create any table in the database in contrast to the datapump export. so i think i will not hit the 4g LIMIT ERROR

step 5 is possible because now the expdp is not constrained by any 4G limit.

it is not supported to import in step 4 into an 11g database but one can try if this is possible.


You're using a local variable, not a parameter. So because this is part of your query text:

DECLARE @id INT=43865

It gets included as part of the query hash (which converts the query text, including 43865, to binary), and thus produces a different hash every time you change the text. This is how it will work in a Management Studio query window, but it might be different if you send properly parameterized ad hoc queries from a C# application for example.

You can try changing the database setting Parameterization to forced, but this is kind of like killing a mosquito with a bulldozer. Better yet (IMHO), instead of ad hoc SQL, use stored procedures where you pass the value of your local variable @id via a proper parameter (or skip the local variable altogether and just pass 43865 directly to the stored procedure parameter).

Kendra Little has written a little bit about this too, and she suggests using #temporary procedures to mimic your actual procedure (e.g. for testing changes without changing the base procedure). But that is only relevant if you already have a stored procedure in the first place:

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)


How can I enter Mongo as a superuser or reset users?

If you have locked yourself out then you need to do the following:

Stop your MongoDB instance
Remove the --auth and/or --keyfile options from your MongoDB config to disable authentication
Start the instance without authentication
Edit the users as needed
Restart the instance with authentication enabled


Instant File Initialization | Lock Pages in Memory Issues

Try setting the services to run as domain/machine account, and give the permissions to that account


Why does a table of varchar(255) columns take up less space than an identical table using the correct data types

So this turns out to be because of the shape of the data I am storing, IN THIS INSTANCE a varchar genuinely takes up less space.

Many of the columns in the table are ints and numeric(38,12) (this is so huge because the data source uses this type). However, many of the values in these columns are 0 or whole integers. As a result a varchar(255) would store this value as 3 bytes (1 byte, plus 2 the two overhead), whilst a int would use 4 and the numeric would use 17. This results in using the correct types to be less storage efficient over the whole table.

To decrease the storage space used, I would need to check if I can change the typing (from int to smallint or numeric(32,12) to a numeric (19,8) etc.) alternatively it looks like sparse columns would be a good solution where there are lots of 0's or null expected.


AWS Athena: syntax for grouping data for week ending on a Saturday

select substring(
          cast(date_add(
                  'day',
                  5+(cast(dow(
                             date_parse('2020-04-12','%Y-%m-%d')
                             )=7
                          as integer)
                    )*7,
                  date_trunc(
                     'week',
                     date_parse('2020-04-12','%Y-%m-%d')
                  )
               ) as varchar
          ),
          1,
          10
       ) as week_ending;


I have experianced a similar issue a few of times in the last year, and each time it has been due to an external issue, normally anti-virus software with incorrectly configured exclusions. I would check this.

In the mean-time, the following query will let you examine the level of physical IOs comming from SQL Server:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN 'Log'
                   ELSE 'Data'
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC

Pay particular attention to IO_Stalls, Reads and Writes. This query cam from http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx


Postgres equivalent of SQL Server profiler

Set log_statement = 'all' in postgresql.conf and reload PostgreSQL. Then all SQL statements will be written to the log.


SQL Server Timeout exception for multiple Users using SQL Stress

The timeout is with the connection pool, which is a collection of connections maintained by the client.  All of the connections in the pool are in use, and it is timing out getting a connection object from the connection pool, not timing out trying to connect to the SQL Server.  For example, you may have 30 connections in the connection pool, and a 5 second timeout for obtaining a connection from the pool.  A connection pool timeout will occur if all 30 connections in the pool are busy and none are freed up within 5 seconds of a request for a connection pool object.

So you can increase the number of connections in the pool, increase the connection pool timeout, or work to see if the stored procedure execution time can be improved.  It is likely that all of the connections are busy due to blocking, and if that's the case, increasing the connections in the pool and the timeout won't increase the number of times the stored procedure can execute per minute and you'll need to find out how to make the stored procedure execute faster.  So a good first step would be to look at the execution plans and missing indexes for all of the queries in the stored procedure.

Perhaps obvious, but since you state it works better on the "local" server, what are the configuration differences between the two servers?  If read committed snapshot is enabled on the local server but not the other, that one config change could have a huge impact.


Pattern for copying hierarchical data from one table to another?

In this scenario I would not use IDENTITY for primary keys, but switch to sequences.
This way you can reserve sequence numbers to a temporary table, which became your mapping table, something like:

CREATE SEQUENCE TreeNodes2Sequence START WITH 1 INCREMENT BY 1 ;

CREATE TABLE TreeNodes2 (
    ID int PRIMARY KEY DEFAULT (NEXT VALUE FOR TreeNodes2Sequence),
    ...
)

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO #InsertedRows
(
    Id,
    OldId
)
SELECT
    NEXT VALUE FOR TreeNodes2Sequence,
    tn1.ID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

Then use this temp table to insert all values with null parent and then update parents.


SQL Server slows down on one expensive query

When I run a query with a bad plan the server slow down and all other query,

That says they're contending for the same resource, and

  non problems on disk I/O

doesn't say much about why the disk wouldn't be the resource they're contending for.

Logically we know that a query with a "bad plan" is going to read a lot of data from the disk, perhaps scanning one or more tables, because the definition of a "good plan" is one that uses indexes and minimizes I/O.  So your first suspicion should be that query is bandwidth-limited, and your second guess should be that the I/O cache is being flushed by the bad query.

If you check your I/O queues in SQL Server when the bad query is running, I expect you'll see the other queries have non-zero queue lengths.


is it possible to populate a primary key of a table based on a column of another table?

Sounds like a simple INSERT ... SELECT statement:
insert into target_table (the_pk_column)
select u.key
from regexp_split_to_table('12 5 7 19 30', '\s+') as u(key)
on conflict (the_pk_column) do nothing;

or based on the column of the source table
insert into target_table (the_pk_column)
select u.key
from source_table st
   cross join regexp_split_to_table(st.graph_path, '\s+') as u(key)
where st.id = ... --<< whatever you want to select
on conflict (the_pk_column) do nothing;


Here is an example of how that could be done:

--demo setup
Declare @Orders table (OrderNumber int, UserLogin varchar(20))
Declare @CustomerOld table (UserLogin varchar(20), UserFullName varchar(30))
Declare @CustomerNew table (UserLogin varchar(20), UserFullName varchar(30))

insert into @Orders (OrderNumber,UserLogin) values(1,'SillySam')
insert into @CustomerOld(UserLogin,UserFullName) values('SillySam','Sam Snyder')
insert into @CustomerNew(UserLogin,UserFullName) values('SillySamUpdated','Sam Snyder')

--solution
UPDATE o
SET o.UserLogin = cn.UserLogin  --update orders.UserLogin with value retrieved from CustomerNew
FROM @Orders o
JOIN @CustomerOld co        --join CustomerOld to Orders using UserLogin
    ON co.UserLogin = o.UserLogin
JOIN @CustomerNew cn        --join CustomerNew to CustomerOld using UserFullName from both tables
    ON cn.UserFullName = co.UserFullName

--verify
select * from @Orders

OrderNumber UserLogin
1           SillySamUpdated


What if we skip the slave by counter 1 when it failed at event 1 of a transaction which has events 2 and 3

Correct, skipping skips the transaction - not the statement - and that is why it is better to investigate the failure than blindly skipping.

You should use pt-table-checksum and pt-table-sync to resolve drift after a skip since you will likely have diverged.


your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:

SELECT
    Message.messageContent, Message.messageDateTime
FROM
        Message
    INNER JOIN
        ( Contact INNER JOIN contact_m2m_message
            ON Contact.contactID = contact_m2m_message.contactID
        )
      ON Message.msgID = contact_m2m_message.messageID ;

Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.

The 2nd query is not valid because you have not provided an alias for the derived table:

SELECT
    Message.messageContent, Message.messageDateTime
FROM
        Message
    INNER JOIN
        ( Select Contact.contactNumber, Contact.contactName
          FROM Contact INNER JOIN contact_m2m_message
               ON Contact.contactID = contact_m2m_message.contactID
        )     --<-------------------------------------- no alias
            ON Message.msgID = contact_m2m_message.messageID;

To make it  work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID in the SELECT list of the derived table (and remove columns that are not used):

SELECT
    Message.messageContent, Message.messageDateTime
FROM
        Message
    INNER JOIN
        ( Select -- Contact.contactNumber, Contact.contactName,
                 contact_m2m_message.messageID
          FROM Contact INNER JOIN contact_m2m_message
               ON Contact.contactID = contact_m2m_message.contactID
        ) AS cm
            ON Message.msgID = cm.messageID;

But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:

SELECT
    m.messageContent, m.messageDateTime
FROM
    Message  AS m
  INNER JOIN
    contact_m2m_message  AS cm
      ON m.msgID = cm.messageID
  INNER JOIN
    Contact  AS c
      ON  c.contactID = cm.contactID ;


Issue with connecting to the mysql database

Based on your old_passwords setting, I would ask you to look at three things

What password functions return
What passwords are stored in mysql.user
What authentication style is the client launching

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.


Efficient way to DELETE whole partition's data in table partitioned by HASH(DAY(datetime))?

To answer the update, the TRUNCATE PARTITION will not reset the auto_increment field. This makes sense since it would be sticky to handle in relation to the other partitions in the auto increment scenario.

However, a table truncate will reset the auto increment key.


Merging a .bak to an existing SQL database

A single database restore is not the operation you need if your goal is the merge data. You are looking for a data level solution not a database level solution.
SQL tooling can help you with this task, but it is not required (it depends on knowledge and skill level). The 2 data compare tools I have used Red Gate SQL Data Compare and Apex SQL Data Diff have the ability to compare data using a .bak file. After the comparison the tool can generate the 'deployment' or 'change' script that you could use to "merge" in your changes.
TSQL solution: Craft a merge statement for each table. This would require restoring your backup file to another Database name.  This will result in 2 databases (source and target) living on the SQL Server.
TSQL Merge docs:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
To avoid a database overwrite specify a new destination (non-existing) database name in your restore command. (included SSMS screen shot)


MS SQL Server using SSL Connection over the Web?

According to Books Online, the certificate used for encrypting connections must meet the following requirements:

The certificate must be issued for Server Authentication
The name (common name or CN) on the certificate must match the full-qualified domain name used by clients to connect.

If those two conditions are not met, the certificate will not show in the drop-down listing. Additionally, the service account for SQL Server must have read access to the private key of the certificate. Without that right, you will be able to select the certificate, but the service will not start.

Since this is a test system you intend to use as proof-of-concept, a self-signed certificate should be adequate. I am not sure what language you are using for your client application, but this link provides options available for connection strings in .Net. You should review at least the following two options.

Encrypt - Tells the client application to force encryption.
TrustServerCertificate - Tells the client application to skip validating the certificate chain. This is important with a self-signed certificate as the chain likely will not be trusted.


Insert Data Into NULL Rows from Previous Row (t-SQL)

Previous Requirement

Below query is far improve version of previous one.

 ;WITH CTE as
 (
  select CalanderDateTime,PtID,EventDateTime ,GapinMinutes
 ,ROW_NUMBER()OVER(ORDER BY CalanderDateTime)rn
 from #t
 )
--,CTE1 AS(
-- select c.* from cte c
--cross apply(select rn from cte c1
-- where c1.GapinMinutes>3 and
-- (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1)) )ca
-- )
 select CalanderDateTime,PtID,EventDateTime ,GapinMinutes
 from cte c
 where not exists (
 select rn from cte  c1
 where c1.GapinMinutes>3 and
 (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1))
 )

let the index be commented now.you don't need the commented CTE1.

New Requirement, (Here your output require is quite change and I am only working on part of your dataset).

Main trick is to implement it seamlessly as part of your main query.

create table #ttt (RN int,CalanderDateTime datetime,PtID int,EventDateTime datetime,GapinMinutes int)
insert into #ttt values
(1  ,'2017-09-23 08:34',NULL ,        NULL          ,   NULL)
,(2  ,'2017-09-23 08:35',NULL ,        NULL          ,   NULL)
,(3  ,'2017-09-23 08:36',NULL ,        NULL          ,   NULL)
,(4  ,'2017-09-23 08:37', 1   ,  '2017-09-23 08:37'  ,     1)
,(5  ,'2017-09-23 08:38', 1   ,  '2017-09-23 08:38'  ,     4)
,(6  ,'2017-09-23 08:43', 1   ,  '2017-09-23 08:43'  ,     3)
,(7  ,'2017-09-23 08:44', NULL,       NULL           , NULL )
,(8  ,'2017-09-23 08:45', NULL,       NULL           , NULL )
,(9  ,'2017-09-23 08:46', NULL,       NULL           , NULL )
,(10 ,'2017-09-23 08:47', 2   ,  '2017-09-23 08:47'  ,     3)
,(11 ,'2017-09-23 08:48', NULL,      NULL            ,NULL  )
,(12 ,'2017-09-23 08:49', NULL,      NULL            ,NULL  )
,(13 ,'2017-09-23 08:50', NULL,      NULL            ,NULL  )

CREATE clustered INDEX x_ix11 ON #ttt(rn);
CREATE INDEX x_ix1 ON #ttt(ptid);

--drop index x_ix11 on #ttt

select t.RN ,t.CalanderDateTime
, case when t.PtID is not null then t.ptid else ca.ptid end ptid
,t.EventDateTime ,t.GapinMinutes
from #ttt t
outer apply(select top 1 t1.ptid
from #ttt t1
where t.rn>t1.rn and t1.PtID is not null
and t.ptid is NULL
 order by t1.rn desc)ca

Minor change in query.

Index should not be created by understanding just part of dataset.

Here dataset is very small.But after creating index aim getting one Index Seek and one index scan i.e. query plan seem improve.

IMHO, given outer apply is one best way to do so (80%) .

But with million of rows already,  outer apply increase the  the rows by 50%. inequality operator t.rn>t1.rn is doing problem

May be it can be implemented from view itself.

It belong to which table and how many rows this table returns ?

Tried to optimize your main query as well as implemented new requirement

This part of query only indicate that what correct should be done,it may not run as it is.you have to fix the bug.

With VentHours   AS
(
    SELECT sig.PatientID ptID, convert(DateTime,sig.Time) t,

        LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) LeadTime,

         LEAD(sig.PatientID) OVER (ORDER BY sig.PatientID, Sig.Time) LeadPatient,
        p.rAdmitDate adt,
        p.rDischDate dit
        FROM Reporting.dbo.VIEW_Vent_Signals sig
        inner join Reporting.dbo.ICU_Adult_Patients p ON sig.PatientID = p.ptID

),

    Select ts.ID rn
    , case when vh1.ptid is not null then vhi.ptid else vh.ptID end ptid
    , ts.DateTime,  case when vh.t is null then ts.DateTime else vh.t end t, vh.adt, vh.dit
    , Case When DateDiff(MINUTE,sig.Time,LeadTime BETWEEN 2 AND 720 THEN 1 ELSE 0 END et
    ,Case When sig.PatientID = LeadPatient AND DateDiff(MINUTE,sig.Time,LeadTime) between 0 and 720
        THEN DateDiff(MINUTE,sig.Time,LeadTime) ELSE 0 END VGM
        ,vh1.ptid
    from Reporting.dbo.AUX_DateDimension ts
    left outer join VentHours vh ON convert(DateTime,ts.DateTime) = vh.t
    outer apply(select top 1 vh1.ptid
from VentHours vh1
where vh.ID>vh1.ID and vh1.PtID is not null
 order by vh1.id desc)ca


Scale vs. Precision - Why this definition?

Precision and Scale are based on the mathematical concept of Significant Figures.  You're not alone in that the current definition/usage for these terms is confusing.

  How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?

First, you must remember that everything in computers is represented in binary; however not all numbers are easily represented using binary syntax, specifically when it comes to certain fractions.  One such fraction, ⅓, is actually impossible to represent in a computer.  Even with the largest amount of memory possible, a computer will never be able to accurately represent ⅓ in binary form.  However, it can get more precise (e.g. close) to ⅓ with the more bits (e.g. and therefore decimal places) that are thrown at it.

Precision can therefore be thought of as how exact a number you want to represent.  The more digits the more precise that number becomes.

  How can I think of the number of decimals in terms of scale (how 'big' something is)?

Since Precision is a measure of how exact the number you wish to represent is and we know we're limited in how much we can store, you have to sacrifice precision for scale.  So basically the smaller the scale the larger the final value can become.

Scale may best be explained with how we look at maps.  A map of a state or province shows a much larger scale of area with less detail, whereas a map of a city shows a smaller area at greater detail.

Scale can therefore be thought of as the amount of detail.  The more detail we want to convey comes with the cost of showing less actual stuff.

I don't know if that helps you at all, but hopefully that helps provide some context behind the definitions of these terms.


Can a factless fact table be a bridge table?

Disclaimer, I'm not into DW, so I may have completely misunderstood the question. That said, a factless fact table sounds like an oxymoron, but I googled for it and according to Factless fact table | James Serra's Blog it's an intersection between two or more dimension tables. A bridge table is typically used to implement an n-n relationship:

CREATE TABLE d1
( d1key ... not null primary key
, ... )

CREATE TABLE d2
( d2key ... not null primary key
, ... )

CREATE TABLE f
( d1key ... not null references d1(d1key)
, d2key ... not null references d2(d2key)
,    primary key (d1key, d2key)
);

so I don't see any controversy in such table being both a bridge table and a factless fact table.