Duplicate rows with a few changes in a table with a composite primary key, but only if target does not exist

Taking what your query says literally, you don't have to worry about what exists in xxx.myTable - because your statement will create it, therefore it won't have anything in it (or, if it already exists, your statement will fail). Presumably, that's not really what you were going for.

If we assume that xxx.myTable does exist, and may already have some rows whose values match your defaults, then this should work.

INSERT INTO xxx.myTable (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
  FROM #PreviousMonth pm
 WHERE NOT EXISTS (SELECT 1
                     FROM xxx.myTable
                    WHERE period = pm.period
                      AND genusID = pm.genusID
                      AND subjectID = pm.subjectID
                      AND waitingStageID = pm.waitingStageID
                  )
;

In the NOT EXISTS clause, you're checking if the current row from the temp table matches the primary key values for any rows already in xxx.myTable. If you find a match for the current row, then you won't SELECT it (and thus, won't INSERT it).

You can also write this as what's called an "anti-join":

INSERT INTO xxx.myTable (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
  FROM #PreviousMonth pm
         LEFT OUTER JOIN xxx.myTable mt ON (    pm.period = mt.period
                                            AND pm.genusID = mt.genusID
                                            AND pm.subjectID = mt.subjectID
                                            AND pm.waitingStageID = mt.waitingStageID
                                           )
 WHERE mt.period IS NULL
;

Here, we're matching all the rows in the temp table with any matching records in xxx.myTable. via a LEFT JOIN. Because of the left join, if there's no matching row in xxx.myTable, we'll still return columns from that table, but all the values will be NULL. So, if we find a row where period is NULL in xxx.myTable, that should mean that there isn't a matching xxx.myTable row for our temp table row. So, only those rows (temp table rows with no matching xxx.myTable row) will be SELECTed, and thus will be INSERTed.

In most cases, I wouldn't expect dramatically different performance between the two versions; test in your specific case, to see if one seems better than the other.

UPDATE: If you want to use the previous month's data anywhere the key fields don't exists, or where they do exist but the value = 0, you've got a couple of options available.

You could delete all rows in xxx.myTable where the value is 0, then do the insert as above.

I would be inclined to UPDATE the existing rows, and then perform the INSERT as above. The UPDATE statement would be:

UPDATE mt
   SET value = pm.value
  FROM xxx.myTable mt
         INNER JOIN #PreviousMonth pm ON (    mt.period = pm.period
                                          AND mt.genusID = pm.genusID
                                          AND mt.subjectID = pm.subjectID
                                          AND mt.waitingStageID = pm.waitingStageID
                                         )
 WHERE mt.value = 0
   AND pm.value <> 0
;


What is the right way to manage users data?

This is a good "there's more than one way to do it" question.

Start by thinking about your data usage:

Think about which fields are going to be read and written more often.

For example, first name and last name are probably written once and are rarely changed, but depending on your app, they could be read very often.

Last login date could be written frequently, and you might want to keep every login date.

Things like activation key and registration date, depending on how authentication works in your app, might be written once and then very rarely read.

You should also think about what data is pulled at the same time, and try to denormalize to make those reads faster. For example, if you are always pulling username and password and first name together (but no other contact info), put firstname in the login table, too. That is, think about avoiding joins if this data will be large, and be kind to yourself.

Also think about which fields might be in the "where" clause of your queries, and index on those fields.

Here's one way I might do it:

Contact Table
(one insert, rare updates, many reads)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| address    | varchar(50)   | NO   |     | NULL    |                |
| city       | varchar(50)   | NO   |     | NULL    |                |
| state      | varchar(50)   | NO   |     | NULL    |                |
| country    | varchar(50)   | NO   |     | NULL    |                |
| email      | varchar(1024) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Login Table
(one insert, maybe updates, many reads; need first_name to be echoed on login)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | PRI | NULL    |                |
| username   | varchar(32)   | NO   |     | NULL    |                |
| password   | varchar(32)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Login History Table
(many writes, multiple entries per user_id)

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| user_id    | int(11)       | NO   | MUL | NULL    |                |
| last_login | timestamp     | NO   | MUL | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Signup Table
(one write, rarely read)

+------------------+---------------+------+-----+---------+----------------+
| Field            | Type          | Null | Key | Default | Extra          |
+------------------+---------------+------+-----+---------+----------------+
| user_id          | int(11)       | NO   | PRI | NULL    |                |
| registration_date| timestamp     | NO   |     | NULL    |                |
| activation_key   | varchar(50)   | NO   |     | NULL    |                |
+------------------+---------------+------+-----+---------+----------------+


How to find maximum of average grouped by in sql

I cannot check the results but I hope that the query below should work.
In future, please, add the DDL (Data Definition Language) including the scripts for table creation and also to insert a few records that will ilustrate the expected result.

WITH CTE AS (
SELECT Faculty.name AS Faculty_Name,
       Student.name AS Student_Name,
       AVG(mark) AS Avg_Mark,
       ROW_NUMBER() OVER(PARTITION BY Faculty.name ORDER BY AVG(mark) DESC) AS Row
FROM Faculty
INNER JOIN Student On Faculty.id = Student.faculty_id)
INNER JOIN Course_student On Student.id = Course_student.student_id
GROUP BY Faculty.name
)
SELECT Faculty_Name, Student_Name, Avg_Mark
FROM CTE
WHERE Row = 1


Find max date in multiple columns in SSIS

I can think of a couple of ways that it can be done.

The first is using pure SSIS tasks and data flows. Based on your source data, the easiest approach I can think of would be to use an Unpivot Transformation followed by an Aggregate Transformation to get the values you need. Un-pivot on DT1, DT2, DT3, DT4 and DT5 and then aggregate over the date column and group by the CustID field. This would achieve what you need.

I don't have a copy of SSIS in front of me right now, but you can find the documentation for Unpivot Transformation and Aggregate Transformation on Microsoft Docs.

Alternatively, if the data is in a table (or can be put into a table), then you could just write a SQL script and call that via an Execute SQL Task to do the unpivot and aggregation for you like this:

 SELECT CustID, MAX([Date]) AS [Date]
   FROM (SELECT CustID, DT1, DT2, DT3, DT4, DT5) AS p
UNPIVOT ([Date] FOR [Dates] IN (DT1, DT2, DT3, DT4, DT5)) u;

Then you can just return the row set back into SSIS if you need to. Either method would be fine for such a small data set but test them both and see which one performs better and is easier to maintain.


Can you give me one example of Business Intelligence?

Business Intelligence is often a completely separate sect from Database Administration and Database Development.  Business Intelligence, at the highest level, includes three main facets:

Reporting
Integration
Analysis

Reporting

Reporting is the creation, deployment, and management of reports as well as the added ability for users to customize reporting dynamically.

Integration

Data integration and transformation solutions.  On the very simplest level, it is the means of extracting, transforming, and loading data into a data source, from a data source (which could be anything as simple as a flat file).  Integration is a mile deep, but that's the most basic functionality of it.

Analysis

Online Analytical Processing (OLAP) used to design, create and manage structures that contain data aggregrated from source data stores.  A catch phrase for this is data mining.

These are extremely simplified descriptions of what Business Intelligence encorporates.  There is a science behind BI, as well as each of these facets individually.  Database Professionals dedicate their time and careers to mastering these.


Max_connections is The total number of concurrent connections allowed by the MySQL server. If you are getting Too many connections' error, it means that you have reached this limit.

Also, You should observe threads_running variable value for a period of time to determine exact value for 'max_connections'.

You can consider max_connection value as

  MaxValue (threads_running)` + 10 = Max_connections


You should make sure that for the database you're trying to back up, you have at least the db_backupoperator database role assigned to the user that's trying to run the backup.
That should be sufficient.


Postgres 9.3 changes the standard_conforming_strings value

How exactly did you set standard_conforming_strings?

If you did that in your session, it only applies to that session. For a permanent setting change your postgresql.conf and reload.

Also, a pg_dump  cannot reveal anything. The setting in the dump only applies to the dump. To see the current setting of your database, run

SHOW standard_conforming_strings;

Or:

SELECT * FROM pg_settings WHERE name = 'standard_conforming_strings';

Here you also see where the current value is from and whether it's the default etc.

Generally, It's a very bad idea to try and keep standard_conforming_strings off. This was changed for a good reason. It's going to bite you sooner or later. Update to the current (superior) standard.


The innodb_system data file 'ibdata1' must be writable even with full permissions set

After reading this article and getting a main idea about SELinux and Apparmor i have finally managed to solve the issue.
The solution was adding the new path of my new datadir in /etc/apparmor.d/usr.sbin.mysqld like this:
/database2/ r,
/database2/** rwk,

Now restart the apparmor service and start the mysql service.
sudo systemctl restart apparmor
sudo systemclt start mysql


Is there a way to move files by SQL query itself

Yes.  Use BACKUP/RESTORE WITH MOVE instead of OFFLINE/ALTER/File Copy/ONLINE.
eg
use master
go
use foo
alter database foo set single_user with rollback immediate
backup database foo to disk='c:\temp\foo.bak' with init
--restore filelistonly from disk='c:\temp\foo.bak'
use master
restore database foo from disk='c:\temp\foo.bak'
  with move 'foo' to 'd:\mssql\data\foo.mdf',
       move 'foo_log' to 'd:\mssql\data\foo_log.ldf'

Note that when you RESTORE over a database SQL Server deletes the old database files.


Please then modify your database to be in 'SIMPLE' recovery mode instead of 'FULL' or 'BULK LOGGED'.  The full recovery mode never truncates the transaction log, unless you do a transaction log backup.  Only after it's backed up will SQL Server reuse the empty space in the transaction log.

Now setting the DB to simple mode, taking a full backup, and running a checkpoint is easy.  Managing the transaction log itself might take some more work.  You will want to ensure that you don't have too many VLFs (Virtual Log Files), which if you have the default setting of 1 mb growth you most likely do.

Here are the things to do:

 ALTER DATABASE DBNAME SET RECOVERY SIMPLE WITH NO_WAIT GO

Then take a full backup.  Do a checkpoint with a

USE DBNAME
CHECKPOINT;

Now let's see how many VLFs your transaction log has:

USE DBNAME
dbcc loginfo();

The number of rows returned are the number of VLFs.  Assuming the status is 0 on the last VLFs you can shrink and reduce the number to a reasonable one.  How big should your transaction log be you ask?  If you don't know the work load make a educated guess and wait a week, reindex, and see how big it is.  It should probably be about that size.

To shrink the log file you can use the GUI and script it out.  Here is a youtube video on how to use SQL Server Management Studio (SSMS) to use the GUI to shrink:
https://www.youtube.com/watch?v=xcT4DIMG-tI

Once you shrink it run DBCC LOGINFO(); again to see how many VLFs you have.  It should be signifigantly lower.  If it isn't, your final VLF is probably status: active (not 0).  If that's the case you will want to wait for the transactions to finish where it is no longer active, perhaps checkpoint just in case, and run your shrink again.

Now you should be down to a low number of files and a small size.  You don't want more than like 100 vLFs, 50 is the approx unofficial number a lot of people use to keep it around.  Expand your transaction log a few chunks at a time so you have more than 10 but less than 60 VLFs.  In a week, review your transaction log VLFs and size to see what it should actually be set to moving fwd and monitor it.

Notes:
-Please don't use shrink DB or shrink logs unless it's a very specific situation like this, under most conditions this is really bad practice.

-Read up on VLFs and log file maintenance from the best.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/


From MSDN

  *condition_expression* includes one variable. The at sign (@) precedes
  each local variable. The expression refers to the value entered with
  the UPDATE or INSERT statement. Any name or symbol can be used to
  represent the value when creating the rule, but the first character
  must be the at sign (@).

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


How do I map an IS-A relationship into a database?

Way two is the correct way.

Your base class gets a table, and then child classes get their own tables with just the additional fields they introduce, plus foreign key references to the base table.

As Joel suggested in his comments on this answer, you can guarantee that a user will have either a direct login or an OpenID login, but not both (and also possibly neither) by adding a type column to each sub-type table that keys back to the root table. The type column in each sub-type table is restricted to have a single value representing the type of that table. Because this column is foreign keyed to the root table, only one sub-type row can link to the same root row at a time.

For example, the MySQL DDL would look something like:

CREATE TABLE Users
(
      uid               INTEGER AUTO_INCREMENT NOT NULL
    , type              ENUM("DirectLogin", "OpenID") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
);

CREATE TABLE DirectLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("DirectLogin") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
    , FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

CREATE TABLE OpenIDLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("OpenID") NOT NULL
    // ...

    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

(On other platforms you would use a CHECK constraint instead of ENUM.) MySQL supports composite foreign keys so this should work for you.

Way one is valid, though you are wasting space in those NULL-able columns because their use depends on the type of user.  The advantage is that if you choose to expand what kinds of user types to store and those types don't require additional columns, you can just expand the domain of your ENUM and use the same table.

Way three forces any queries that reference users to check against both tables. This also prevents you from referencing a single users table via foreign key.


Import/Exporting certain columns of a table across two databases and excluding duplicate emails in mysql

Toss the db1.tblclients. in the list of columns; it is totally redundant.  And a syntax error.

A suggestion:  Make the SELECT a LEFT JOIN ... WHERE id IS NULL.

And, again, db2.tblclients. is redundant.  (But not harmful.)

(If those suggestions are not enough, show us what you have; there may be more to fix.)


GridFS: why can't I shard chunks with a hashed key?

GridFS is for storing large binary data chunked into smaller documents (by default, 255KB each). The access pattern for GridFS is different from a sharded collection where random document distribution might be more desirable (for example, with a hashed shard key).

With GridFS the documents relating to a single file are normally read sequentially: identified by unique files_id and ordered by chunk number n. The supported shard keys for GridFS enable range queries based on the order that drivers will reconstruct a GridFS byte stream. A hashed shard key does not support range queries so would be extremely unhelpful for read performance (n chunk lookups would be required and data would be randomly distributed).

  In fact, they suggest that the shard key should be the files_id or the files_id + the chunk number. They both increase monolitically.

The default values for these two fields indeed increase monotonically, which will lead to a hot shard for writes if you shard an fs.chunks collection using the default ObjectID value for files_id.

The chunk number is expected to be a sequence, but you can (and should) provide your own custom IDs when uploading files if you want to improve write distribution for GridFS in a sharded deployment. Official MongoDB drivers should provide an API for setting the _id when creating a new GridFS file.

  At least it should be explained in the documentation.

Definitely! I've raised DOCS-10993 to improve this information in the MongoDB manual. I suspect there are also improvements to be made in the driver documentation so will review those as part of documenting the overall recommendation.


You need to apply proper parenthesis ( ). You need to apply parenthesis for the condition (task_assigner = 31 OR task_assigned_to = 31), so the task_status IN ('0', '3') and the task_assigner both conditions will satisfy.

The working query will be:

SELECT task_name, task_id, SUBSTR(task_text, 1, 20) AS task_text,
       task_project_id, task_status
FROM projectboard_tasks
WHERE task_status IN ('0', '3')
    AND (task_assigner = 31 OR task_assigned_to = 31)


Database Mirroring and Replication in SQL Server 2008 R2

You will need to add both the principal server and the mirror server to Replication Monitor.  This is covered in Database Mirroring and Replication (SQL Server):

  Add the principal and mirror to Replication Monitor.

Please see Add and Remove Publishers from Replication Monitor to add the mirror server to Replication Monitor.


A query that lists all mapped users for a given login

Here's one way using dynamic SQL. There's not really any way to do this without iterating, but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb (background here and here).
This will get a list of all online databases, the mapped user (if it exists), along with the default schema name, and a comma-separated list of the roles they belong to.
DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS,
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS,
    STUFF((SELECT N'','' + r.name
      FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
      INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
      ON r.principal_id = rm.role_principal_id
      WHERE rm.member_principal_id = p.principal_id
      FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
    FROM sys.server_principals AS sp
    LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
    ON sp.sid = p.sid
    WHERE sp.name = @name '
  FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

On more modern versions (2017+), I would still use dynamic SQL, but I would use STRING_AGG() instead of FOR XML PATH, probably something like this:
DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max),
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS
(
  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ',
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

In this latter example, if you only want the databases with a user mapped to the named login, just change the first left join to an inner join.


Where do i change the my.cnf on mysql cluster

The answer is indeed the SQL Nodes - the key factor is related to the client - the setting doesn't apply until you close the client session and reopen it


psql command, how to enforce certificate check?

To have psql verify the server certificate, you must specify the appropriate connection string options. This is done by the libpq client shared library, so it will work the same with all clients that use it.

You can directly specify the options in the connection string:

psql 'host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-certificate sslcrl=/path/to/certificate-revocation-list'

If the CA certificate of the revocation list are in the default locations, you can omit the respective options.

See the documentation on sslrootcert and sslcrl.
You can use environment variables to specify the options:

export PGSSLMODE=verify-full
export PGSSLROOTCERT=/path/to/ca-certificate
export PGSSLCRL=/path/to/certificate-revocation-list
psql -h ... -p ... -d ... -U ...

See the documentation on environment variables for details.

For details on how server verification works, consult the appropriate section of the documentation.


You want to filter out those rows for which a row with the same x and the previous y exists:

SELECT *
FROM my_table
WHERE NOT EXISTS (SELECT *
                  FROM my_table AS previous
                  WHERE (previous.x, previous.y )
                      = (my_table.x, my_table.y - 1));


The best MySQL configuration for my server

You may find this surprising but MySQL 5.5 can be configured to access multiple cores for InnoDB. The features are not on by default. In fact, there are occasions when older versions of MySQL run faster "out-of-the-box" than MySQL 5.5.

I have written posts about this before:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jun 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You must set a few options to wake up InnoDB as to its multiple core capabilities

[mysqld]
innodb_io_capacity=2000
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_log_buffer_size=32M
innodb_thread_concurrency=0

Please see my other posts on these settings

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?

BTW your log buffer is a little too big. I lowered it to 32M because while a bigger log buffer saves on Disk I/O, it makes for longer COMMITs.

I also noted that you have all the InnoDB inside ibdata1 because innodb_file_per_table is disabled. Try the above setting anyway. If you want to move data and indexes out of ibdata1 and into their own tablespace files, please see my past posts on cleaning up InnoDB for data and index storage outside of ibdata1:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

This will control excessive growth of ibdata1 and allow you to compress data and indexes.


Which tool enables using SQL query result sets as temporary tables for further analysis and set operations?

It seems to me that you might want one of two things to do what you require.

1) could be global temporary tables (GTTs), see here or here. Check out the Oracle docco here for drier technical detail.

2) might be CTEs - Common Table Expressions (or subquery factoring as Oracle calls it) - also known as the "WITH CLAUSE". Howtos are here and here - (again, rather dry) Oracle docco is here.

One answer to your question is that every tool which supports Oracle supports "using SQL query result sets as temporary tables for further analysis and set operations" - this capacity is native to Oracle and available to all.

As for doing this with a GUI and dragging and dropping lines between either GTTs or CTEs - I'm not sure such a tool exists - but you could experiment with creating your GTT/CTE as a real concrete table and then do the Drag'n'Drop bit and when you're finally getting the result you want, you could just use the GUI tool generated SQL. Better though to learn SQL and be able to do this without a GUI.


Oracle 10g Listener issues after IT changed computer domain name

What is the entry in SQLNET.ora file of following parameter?

names.default_domain = world

If it doesn't contain your myNewDomain domain name then kindly modify it. I hope it will resolve your domain name misconfiguration. Or ask your system admin to check /etc/hosts file for domain name properly enter or not.


Based on some guess-simulations, I think you can slightly improve your query by:

Avoiding the outer DISTINCT clause (although there will be an implicitly DISTINCT).
Sub-selecting a part of the data so that less is needed to JOIN.

The query is as follows:

SELECT
    employees.*
FROM
    employees
WHERE
    employee_id IN
    (SELECT
        -- Choose all employees from companies with website
        employee_id
     FROM
        employee_companies
        JOIN companies ON companies.company_id = employee_companies.company_id
     WHERE
        companies.website IS NOT NULL
    )
    -- Now filter only employees from 'Germany'
    AND employees.country = 'Germany'
ORDER BY
    employees.connections DESC ;

The data used to produce the simulation is the following one:

Table and index definitions:

CREATE TABLE employees
(
    employee_id integer PRIMARY KEY,
    country text,
    connections integer,
    something_else text
) ;

CREATE INDEX idx_employee_country
   ON employees (country) ;

CREATE TABLE companies
(
    company_id integer PRIMARY KEY,
    website text,
    something_else text
) ;

CREATE INDEX not_empty_websites
    ON companies(company_id, website) WHERE website IS NOT NULL ;

CREATE TABLE employee_companies
(
    employee_id integer NOT NULL REFERENCES employees(employee_id),
    company_id integer NOT NULL REFERENCES companies(company_id),
    PRIMARY KEY (employee_id, company_id)
) ;

CREATE INDEX company_employee
    ON employee_companies(company_id, employee_id) ;

1.000.000 companies (changing to 10M doesn't make a big difference). I assume 90% have a website.

INSERT INTO
   companies
   (company_id, website)
SELECT
   generate_series(1, 1000000),
   CASE WHEN random() > 0.1 THEN 'web.com' END AS website ;

80k employees (about 10% are Germans)

INSERT INTO
   employees
   (employee_id, country, connections)
SELECT
    generate_series(1, 80000),
    case (random()*10)::integer
    when 0 then 'Germany'
    when 1 then 'United Kingdon'
    when 2 then 'United States'
    else 'Angola'
    end AS country,
    (random()*10)::integer AS connections ;

200K employees x companies (this means that people have worked in about 3 companies, on average):

INSERT INTO
    employee_companies
    (employee_id, company_id)
SELECT DISTINCT
    (random()*79999)::integer + 1,
    (random()*999999)::integer + 1
FROM
    generate_series (1, 200000) ;

You can check a downsized version of this simulation at dbfiddle here.  If this simulated data is sufficiently similar to your scenario, changing the query makes a 3x improvement with regard to server-execution time. I'd suggest you give it a try.

Simulating data (scaled down by a factor of 25) a scenario more similar to your real one doesn't offer such a nice increase in performance... Nevertheless, it improves by a 1.5 factor.

Check it at this dbfiddle


finding keywords in text using full text search in PostgreSQL

You have two components of this problem

getting your array to a ts_query,
getting the keywords highlighted
getting the keywords out of the returned string

Now first and foremost, this is FTS. All words in the search string get tokenized as a ts_query. From that point on the original search string is lost. You no longer have words you have lexemes -- tokens which have been stemmed and normalized.

Getting your array to a ts_query,

Essentially, a ts_query looks like, 'fox' & 'jump' & 'dog' & 'foo' & 'bar'
You should review plainto_tsquery which does slightly more voodoo to get text to a tsquery, and to_tsquery which assumes the string is already properly formatted.

To get the array to a ts_query, you can do something like this

SELECT to_tsquery(array_to_string(myarray, ' & '))
FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
  AS t(myarray);

Highlighting

To get highlighting working you need something like this,

SELECT ts_headline(
  'quick brown foxes jumped over the lazy dogs',
  to_tsquery(array_to_string(myarray, ' & '))
)
FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
  AS t(myarray);

That returns

                           ts_headline
------------------------------------------------------------------
 quick brown foxes jumped over the lazy dogs
(1 row)

There are quite a few options on ts_headline. All of the docs for this are found in Controlling Text Search

Getting out the words

The only other thing you can do here is to pull out the words which triggered those tokens

SELECT array_agg(rm[1]) AS WORDS
FROM (
  SELECT ts_headline(
    'quick brown foxes jumped over the lazy dogs',
    to_tsquery(array_to_string(myarray, ' & '))
  )
  FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
    AS t(myarray)
)
  AS t(s)
CROSS JOIN LATERAL regexp_matches(s,'(.*?)', 'g') AS rm(matches);

        words
---------------------
 {foxes,jumped,dogs}
(1 row)


Determining whether column in view can only contain unique values

Short answer:  you'll have to do your own dirty work.  Like @mrdenny says, there is no way of automating this task.

Very long answer:  SQL Server doesn't have an easy way of determining unique columns in a view, in part because of how complex (and dynamic) views can be.  There are two methods to make a first approximation, however, and these methods can work in concert.  The first is what you already have:  performing queries against the view to see if you can find anything which already violates uniqueness constraints.  If it does, you can throw that column (or set of columns) out immediately.  If it doesn't, that combination might be a valid unique key, but there is no guarantee.

The second method is to reason from the data model, starting with unique constraints (assuming you have those on your tables!).  There are three nice ways that you can have enforced uniqueness on a table:  a primary key, a unique key constraint, or a unique index.  All three of them show up in the sys.indexes system table and have the is_unique property set to 1.  There are also some not-so-nice ways like using triggers to enforce uniqueness.

You could try messing around with sys.dm_sql_referenced_entities and a query like this might be helpful to give you a starting point:

declare @ViewName sysname = 'MYVIEW';

with viewcolumns as
(
    select * from sys.dm_sql_referenced_entities(@ViewName, 'OBJECT') dsre /* where is_selected = 1   --uncomment if using SQL 2012 */
),
uniquereferences as
(
    select
        i.object_id,
        object_schema_name(i.object_id) as SchemaName,
        object_name(i.object_id) as TableName,
        i.name as IndexName,
        c.name as ColumnName,
        case when vc.referenced_entity_name IS NOT NULL then 1 else 0 end as HasReference
    from
        sys.indexes i
        inner join sys.index_columns ic
            on i.index_id = ic.index_id
            and i.object_id = ic.object_id
        inner join sys.columns c
            on c.column_id = ic.column_id
            and c.object_id = ic.object_id
        left outer join viewcolumns vc
            on vc.referenced_id = c.object_id
            and vc.referenced_minor_id = c.column_id
    where
        i.is_unique = 1
),
sufficientreferences as
(
    select
        object_id,
        IndexName,
        min(HasReference) as HasReference
    from
        uniquereferences
    group by
        object_id,
        IndexName
    having
        min(HasReference) = 1
)
select
    ur.object_id,
    ur.SchemaName,
    ur.TableName,
    ur.IndexName,
    ur.ColumnName
from
    uniquereferences ur
    inner join sufficientreferences sr
        on ur.IndexName = sr.IndexName
        and ur.object_id = sr.object_id
where
    ur.HasReference = 1
    /* Optional:  remove referenced tables; if you have a 1:1 reference, leave this bit out */
    and not exists
    (
        select
            *
        from
            sys.foreign_keys fk
            inner join viewcolumns vc
                on fk.parent_object_id = vc.referenced_id
        where
            vc.referenced_minor_id = 0
            and fk.referenced_object_id = ur.object_id

    );

For SQL 2008, the is_selected flag is not available, so there's no way to tell if the column returned by that function is actually part of the SELECT clause or if it is is used in a join or filter.  With SQL 2012, you could at least limit your query to the columns that actually are part of the SELECT clause.

What you get from this is not a set of unique keys for the view.  What you get is a set of columns which make up unique keys on their underlying tables.  The difference is that you could have a reference table with a unique key constraint on the Name column, and that Name column would show up in the above query even if the view joins the reference table to the base table (thereby causing repeated use of the reference table's Name column).  To help alleviate that, I have a NOT EXISTS clause which removes cases in which the object is the referenced table in a foreign key relationship with another table in the view, so our unique index for the reference data table should not show up.

What this does allow you to do is reduce your possible answer space.  But even then, you'll be doing a lot of spadework.  The more complex your views get, the less valuable this is.  For example, if you have a UNION ALL in your query, the statement above might show you a candidate column set which is wrong, because those columns might be duplicated in the other half of the UNION ALL.  Or if you have cross-server queries, sys.dm_sql_referenced_entities might not even show you any column names.  In other words, the query above is a semi-functional aid and certainly not a method of automating the process.

Note that this does depend upon having unique constraints specified.  If your only unique keys are surrogate primary keys, it might not be quite as easy to find a candidate column set because not even SQL Server knows that the column combination is supposed to be unique.


Ideas for intro SQL presentation / training

Sure, start with my Learn SQL with the Stack Overflow Database.

It's designed for you to demo with the Stack Overflow public data dump. (I just updated it today, coincidentally.)


Count number of occurrences of a phrase in ntext

ALTER FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase NVARCHAR(100) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT
DECLARE @vSerchLength        INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = DataLength(@pInput)
SET @vSerchLength  = LEN(@pSearchPhrase)

WHILE @vIndex <= @vInputLength
BEGIN

    IF SUBSTRING(@pInput, @vIndex, @vSerchLength) = @pSearchPhrase
    BEGIN
        SET @vIndex = @vIndex + @vSerchLength
        SET @vCount = @vCount + 1
    END
    ELSE
    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO

If you do not need NTEXT, try:

DECLARE
    @s1 NVARCHAR(MAX) = 'aaaabbbssss',
    @s2 NVARCHAR(MAX) = 'a'
SELECT (LEN(@s1) - LEN(REPLACE(@s1,@s2,'')))/LEN(@S2)


Login failed in Linking two servers - SQL Server

Your question is quite confused, first you have 2 servers SV1, SV2, then you link ServerB, but you add login to be used to CGCEL-PVQA\SQLSERVER server. So what does all this mean, that the local server is SV1 and you link SV2=ServerB=CGCEL-PVQA\SQLSERVER to it???

From this one:

  Lets assume my login credentials for SV1 are: Username : admin
  Password : admin123

I see that your local login is admin that is not Windows login but SQL Server login, and in this code:

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true

you told the server that the connection will be made using the login's security context, so what did you want to say by adding  @provstr='Integrated Security=SSPI;'?

Sorry if I misunderstood something, but from your code I can make another one that is working code but I'm not quite sure it's what you want:

EXEC master.dbo.sp_addlinkedserver
@server = N'serverB',
@srvproduct=N'blabla',
@provider=N'SQLNCLI',
@datasrc=N'CGCEL-PVQA\SQLSERVER',
@catalog=N'MyDatabase';

EXEC sp_addlinkedsrvlogin 'CGCEL-PVQA\SQLSERVER', true;

This code creates linked server with the name serverB, it points to the server CGCEL-PVQA\SQLSERVER, and all the logins will use their security context.

This one will not give you the error about

  Invalid connection string attribute

But your login admin will fail anyway.
This is because your SV2 is configured for Windows Authentication only.
This means all SQL Server logins will fail until you change it to Mixed mode and map your SQL Server logins to it.

For remote server configured with Windows Authentication only all the logins from local server can be mapped only to themselves.

So there is no way that your local login admin can logged in to the remote server configured with Windows Authentiction only


MySQL join two large tables is very slow

It is generally a good idea to index columns that participate in JOIN predicates or WHERE clauses. A common mistake is to create several one column indexes instead of fewer multi-column indexes. Here we can benefit from both url and time in history (look at all queries against these tables and you may find that you can add additional columns to these indexes):

CREATE INDEX x01_history_url ON HISTORY (URL, TIME);
CREATE INDEX x01_cache_url ON CACHE (URL);

Second, try to unnest the queries. MySQL has limitations on what kind of query rewrites it is able to do, so the nesting may cause unnecessary overhead.

SELECT COUNT(th.url) as total, tc.url, tc.json
FROM history th
JOIN cache tc
    ON th.url = tc.url
WHERE th.time > UNIX_TIMESTAMP()-3600
GROUP BY tc.url, tc.json
ORDER BY COUNT(th.url) DESC
LIMIT 30

Note that this query is semantically different from your query so you might get a different result. If that is a problem you may want to keep the LIMIT 30 construction in a subquery as before. You may also consider if you can add a similar limit to CACHE, is there an upper bound on how many CACHE rows you must investigate to get 30 rows in total?

INNER JOIN (SELECT cache.url, cache.json
            FROM cache
            ORDER BY ? LIMIT ?) tc


Postgresql similar of SQL Server Query Store

Yes there is such an extension: pg_stat_statements

  The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.

Tools like PoWA use this extension and provide others performance metrics that you might be interested in.


MySQL warm up time after restart because of lost connection

After a bit or research, it looks like the '#sql-xxxx.frm' files are just temp files created during ALTER statements, and possibly other MySQL behavior such as OPTIMIZE. If the mysqld service crashes, those files tend to get stranded. There's a few other stack exchange threads asking how to get rid of them, and to be honest, none of them worked for me. There's one rather clunky one suggested by the error logs, but instead I just deleted the temp files and dropped the original table I was working on. Then I reimported the table from an earlier dump.

During the course of an ALTER, '#sql-xxxx.frm' files are created. If the alter runs smoothly and completes, the '#sql-xxxx.frm' file will be deleted on its own.


Using SPID in DB Tables (instead of Table Variable)

It seems to me using the @@SPID like that is asking for trouble.

Session ID's are reused frequently; as soon as a user connection logs out, that session ID is available to be used again, and is likely to be used by the next session attempting to connect.

To make it work at least semi-reliably, you'd need a login trigger that purges prior rows from the table with the same @@SPID.  If you do that, you're likely to see a lot of locking on the table using the @@SPID column.

SQL Server does indeed use row locking, but it also uses page locking, and table locking.  Of course, you might be able to mitigate that via good indexing, but this still looks like an anti-pattern to me.

If the stored procedure is the only method used to access the affected tables, you could investigate using an application lock, via sp_getapplock to essentially serialize access to the relevant parts.  Docs for sp_getapplock are here.  Erik Darling has an interesting post about it here.


MySQL server stops accepting connections partially ("gone away" error)

So the answer is that really you should only use MariaDB on OSX. Neither Percona or official MySQL distribution will handle the gone away error which arises due to a different mechanism of file locking. All detailed here: https://bugs.mysql.com/bug.php?id=79125

MySQL guys are reluctant to issue a fix but apparently MariaDB had this fixed for a long time. I use this in production and never had a problem since. And just in case, I didn't use ports or brew to install it, simply compiled from source after installing cmake. Hope this helps someone.


Which component of SQL Server architecture handles the user login requests?

There is not a dedicated "component of the SQL Server architecture" for this.  The protocol layer, including the whole login process is handled by the Database Engine.  The SQL Server Database Engine is a single large process that hosts

  "the protocol layer, the query processor (also called the relational
  engine), the storage engine and the SQLOS."

--Microsoft SQL Server 2012 Internals, pp 10 Kalen Delaney, et al

This is in contrast to Oracle, which has the "Oracle Net Listener" which is a separate process. https://docs.oracle.com/cd/B28359_01/network.111/b28316/architecture.htm#NETAG209


Here's a few ways.

Use NOT EXISTS for an anti semi join

SELECT Id,
       Event,
       Thing
FROM   dbo.TimeSeries t1
WHERE  NOT EXISTS(SELECT *
                  FROM   dbo.TimeSeries t2
                  WHERE  t1.Thing = t2.Thing
                         AND t2.Event = 'E');

Partition by Thing and find the minimum (alphabetically first) event in each partition. If it is S rather than E this Thing has no end event.

WITH T
     AS (SELECT *,
                MIN(Event) OVER (PARTITION BY Thing) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Evt = 'S';

Partition by Thing and use LAG to look at the preceding row to the current row when ordering by event alphabetically.  If the current row is S and there is no preceding row Evt will be NULL. Just preserve any such rows. This is 2012+ syntax.

WITH T
     AS (SELECT *,
                LAG(Event) OVER (PARTITION BY Thing ORDER BY Event) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Event = 'S' AND Evt IS NULL;


php single table language separated autoincrements?

It sounds like you're saying you have the same posts available in different languages. Assuming that's the case, I'd split the table in two.

post would have the basic info about the post that is not language dependent:

the unique ID for that post's content
the ID of the user who wrote the post
the date the post was created
...

post_language would be the content for each specific post, translated into a specific language:

post_lang_id - unique ID for this post_language row
post_id - foreign key to post; identifies the underlying post that his has content for
language (or language_id): This can either be the name of the language this content is written in, or a foreign key to a language table, with all the languages you've got content in. I'd be inclined to use language_id, but it's up to you.
post_title
post_content
...

In the post table, post_id would be the primary key, and an auto-increment value. In post_language, post_lang_id` would be the primary key, and an auto-increment value.


what you can do is

Find out the database that consumes highest memory in buffer pool using below :

SELECT COUNT(*) AS cached_pages_count ,
    ( COUNT(*) * 8.0 ) / 1024 AS MB ,
    CASE database_id
      WHEN 32767 THEN 'ResourceDb'
      ELSE DB_NAME(database_id)
    END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

Drill down the database using below query :

set nocount on;
set transaction isolation level read uncommitted;
select
   count(*)as cached_pages_count,
   (COUNT(*) * 8.0) / 1024 AS Total_MB_Occupied, -- convert pages into MB -  the page size is 8 KB for sql server
   obj.name as objectname,
   ind.name as indexname,
   obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
inner join
(
    select       object_id as objectid,
                       object_name(object_id) as name,
                       index_id,allocation_unit_id
    from sys.allocation_units as au
        inner join sys.partitions as p
            on au.container_id = p.hobt_id
                and (au.type = 1 or au.type = 3)
    union all
    select       object_id as objectid,
                       object_name(object_id) as name,
                       index_id,allocation_unit_id
    from sys.allocation_units as au
        inner join sys.partitions as p
            on au.container_id = p.partition_id
                and au.type = 2
) as obj
    on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
on  obj.objectid = ind.object_id
and  obj.index_id = ind.index_id
where bd.database_id = db_id()
and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Reference :

A quick look at: sys.dm_os_buffer_descriptors
What's swimming in your bufferpool?
sys.dm_os_buffer_descriptors aggregations -- more detailed info - what objects/structures/files/etc. are consuming the largest amount of space at a given time (or over time) within the Sql Server buffer pool.


InfluxDB batch insert only inserts the last line

I've tested it out and apparently you need the timestamps on the end of each line. Thus the file should look like:

any,mytag=ab value=59 1555003566890381500
any,mytag=ab value=78 1555003566890381500
any,mytag=ab value=102 1555003566890381500

Now I wonder if you can insert them without the timestamp.


Can't open the mysql.plugin table error when installing MySQL on Mac OS Sierra

In my case mysql failed after the update because the user running mysqld (The user mysql) being unable to open the mysql data directory with write permission. The files were instead owned by myself. I fixed this with:

sudo chown -R mysql /usr/local/mysql/*

The orignal cause (for me) was not the Sierra upgrade itelf, but the overall process of updating applications in response. Specifically other instructions online for updating PHP with brew after the Sierra update had included a chown:

sudo chown -R $(whoami):admin /usr/local # DO NOT DO THIS

This had unfortunately altered the file permissions within /usr/local/mysql within /usr/local, causing the error.


Unable to start the services of SQL server after Downgrading from Enterprise to Standard

Go to SQL Server Configuration Manager, highlight SQL Server Services in the left side, then right-click this instance on the right side and choose Properties. On the Startup Parameters tab, make sure the locations for -d, -e, and -l are updated and correct. Right now it sounds like they are set to the defaults, which won't work if you've moved the system databases to D:

Also it's not clear if you followed the proper process to move the system databases in the first place. Please go through these articles and verify that you done this correctly:

Move System Databases
Move all SQL Server system databases at one time
How to move the Master database


Memory is not released after the execution stored procedure followed by a process end

SQL Server doesn't release the memory it consumes unless it gets an order from the operating system to do so.

If you need, you can set the maximum amount of memory SQL Server will consume.


The error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" means that SQL Server is not running under a service account on the new PC.  Open the control panel, go to administrative and last open services.  Look for SQL Server and see what it is logging in as.  Create a Active directory account (sort of like an account for a person, except nobody should use it) and put the "domain\serviceaccount" and password into the login as tab.  Be sure to grant "domain\serviceaccount" access to the remote server.


What is more efficient: text (765) or varchar (765) to store a freeform searchable description?

As far as Postgres is concerned, there is no difference in performance. And there is no length modifier for text to begin with, just (optionally) for varchar.

Unless you need to enforce a certain length, just use text or varchar.

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

To support pattern matching with LIKE, I suggest a trigram index. Details:

How to create an index to speed up an aggregate LIKE query on an expression?

There is also full-text-search with its own set of operators, building on dictionaries. Here's an overview of pattern-matching options in Postgres:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


How currval of sequence being preserved during the backup and restore in postgres

If you look at the SQL script generated by pg_dump you will see that it generates a setval() call for each sequence exported. So if you have a sequence test_seq and the last value it generated was 42, you will see this line in the SQL script:

SELECT pg_catalog.setval('public.test_seq', 42, true);

this makes the sequence return 43, the next time nextval() is called.

See the manual for details on the setval() function.


Unable to install SQL Server Management Studio (SSMS) on server

As per MSDN BOL documentation here  for the SSMS 17.9.1 installation the .NET Framework should be 4.6.1 (or greater).

Make sure you are running an up-to-date version of the Visual C++ 2013 Redistributable Package. Version 12.0.40649.5 (or greater) is required. Only the x64 version is needed. And also all the latest OS updates are installed on the computer.

For further your ref here


phpmyadmin: know the import progress of sql file

Sort of; phpMyAdmin does have an upload progress bar, but there are some additional webserver dependencies:

  To be able to see a progress bar during your uploads, your server must
  have the APC extension [or] the uploadprogress one

  ...

  Moreover, the JSON extension has to be enabled in
  your PHP.

  If using APC, you must set apc.rfc1867 to on in your php.ini.

As some background information, this is a bit difficult to determine; since phpMyAdmin runs on the server that means it has difficulty knowing the size of the file before the upload is completed, which makes it inaccurate to show progress. I've never gone to the trouble to configure these additional requirements, so I'm not sure how well this works in practice.


Microsoft SQL 2008 Cluster with replication

If you want to run read-only queries on the replication server - Have you thought about using database mirroring to a reporting server, and then using database snapshots to create a read-only copy?

You'll need SQL Server Enterprise edition for asynchronous replication, and the other SQL Server will needed to be licensed as you're using it. You'd also need to test carefully, and be prepared for some latency.

I assume that you've performance tuned the database intensive queries?


Stored procedure encryption and master service master key, SQL Server 2008

It is not used. Details about the encryption algorithm used to encrypt database objects you can find here: http://sqlity.net/en/1617/decrypting-encrypted-database-objects/


Reporting services 2008 reports connection to azure sql database

I assume you are talking about migrating ReportServer and ReportServerTempDB.  As per this document, Azure SQL Database is not supported.  Below are the support databases

Azure SQL Managed Instance
SQL Server 2019
SQL Server 2017
SQL Server 2016 (13.x)
SQL Server 2014 (12.x)
SQL Server 2012 (11.x)
SQL Server 2008 R2

I have done one Reporting Server migration and since we will be backup and restoring the reporting databases, the reports are migrated with it.


Can I recover CDC tables from transaction logs?

Transaction logs are used as a source to get the data CDC uses but it would be very difficult to extract data yourself and insert it into CDC tables.

Problem is that transaction log is not well documented and there are basically two functions you can try DBCC LOG and fn_dblog.
Another option is to use a third party log reader such as ApexSQL Log or Quest Toad but even with those tools you’ll be only able to read what’s written in transaction log. It would be up to you to take this data and somehow add it to CDC tables.


Sync of Master Master replication when Primary node comes up MySQL 5.7 Enterprise

Do not write to the formerly-down side until replication is "caught up".

In your scenario, the formerly-down Master will eventually catchup.  Wait.  Only if it can't catch up, or you find corruption of some kind, should you go to the effort of copying the data over and rebuilding.  If you want to seriously worry about that, have a Slave hanging off each Master for such recovery purposes.

SET GLOBAL read_only = ON whenever a server is not the writable master.  In fact, have that as the default in my.cnf on both servers.  That way, if there is, say, a brief power failure that causes the writable master to reatart, you won't be able to write to it until you get in there to see what the situation really is.

"Split brain" is the worst thing that can happen.  This is when the network is dead, but both Masters are alive and each thinks that the other Master is dead (because of the network).  Both happily take writes that will cause duplicate key problems later.  Avoid that at all cost.

In general Master-Master, even when writing to only one Master at any time, is the least desirable HA solution.  See "Group Replication" and "Galera" for better solutions.  (But not "Fabric", it seems to be dying.)


JOIN and GROUP BY with additional columns

Seems you would need to do some CASE statements along with some UNION ALL like this:

SELECT id,
       CASE
              WHEN idcounter <=1 THEN text1
              ELSE ""
       end AS text1,
       CASE
              WHEN idcounter <=1 THEN location1
              ELSE ""
       end AS location1,
       CASE
              WHEN idcounter <=2 THEN text2
              ELSE ""
       end AS text2,
       CASE
              WHEN idcounter <=2 THEN location2
              ELSE ""
       end AS location2,
       CASE
              WHEN idcounter <=3 THEN text3
              ELSE ""
       end AS text3,
       CASE
              WHEN idcounter <=3 THEN location3
              ELSE ""
       end AS location3
FROM  (
                SELECT   t.id        AS id,
                         Count(t.id) AS idcounter
                group BY t.id ) AS results1
UNION ALL
SELECT     t.id       AS id,
           j.text     AS text1,
           l.location AS location1,
FROM       [text] t WITH (nolock)
INNER JOIN text_location tl WITH (nolock)
ON         tl.textid = t.id
INNER JOIN location l WITH (nolock)
ON         tl.locationid = l.id
ORDER BY   l.location
LIMIT      1
UNION ALL
SELECT     t.id       AS id,
           j.text     AS text2,
           l.location AS location3,
FROM       [text] t WITH (nolock)
INNER JOIN text_location tl WITH (nolock)
ON         tl.textid = t.id
INNER JOIN location l WITH (nolock)
ON         tl.locationid = l.id
ORDER BY   l.location
LIMIT      1
offset     1
UNION ALL
SELECT     t.id       AS id,
           j.text     AS text3,
           l.location AS location3,
FROM       [text] t WITH (nolock)
INNER JOIN text_location tl WITH (nolock)
ON         tl.textid = t.id
INNER JOIN location l WITH (nolock)
ON         tl.locationid = l.id
ORDER BY   l.location
LIMIT      1
offset     2

If you provide an SQLFiddle or some such we could play with it.


how to make shapefile in geometry column of ms sql?

The issue is that you are running your GIS programme from a domain that is not trusted by the domain in which SQL Server resides.

That are numerous methods you can use to correct this. You should discuss with your network admin/sql server admin as to the best approach.

Create a SQL Server Account and use those credentials to connect to the SQL Server <-- this might be the easiest
Trust the domain in which your GIS Programme is running <--I would be loath to do this


How to create relationship metadata between tables without a foreign key check?

Adding a new table and proper foreign keys will likely be the more natural way to solve this.

In some DBMS (eg SQL SQL Server), you can create a disabled or disable an existing foreign key constraint with ALTER TABLE .. NOCHECK CONSTRAINT:

ALTER TABLE b NOCHECK CONSTRAINT [a_b_fk] ;

In Postgres, if you really don't want or not allowed to change the schema, you could create a foreign key from A to B and then disable the internal triggers associated with it. There is no way (as far as I know) to mark the foreign key constraint as "disabled" but disabling the internal triggers will have the desired effect.

Create the tables

blue=# create table a (id int primary key) ;
CREATE TABLE
blue=# create table b (id int primary key, aid int not null) ;
CREATE TABLE

Add the foreign key constraint

blue=# alter table b add constraint a_b_fk foreign key (aid) references a(id) ;
ALTER TABLE

Find the trigger names

blue=# SELECT tgname, tgconstraint
FROM   pg_trigger
WHERE  tgrelid = 'public.a'::regclass;
             tgname             | tgconstraint
--------------------------------+--------------
 RI_ConstraintTrigger_a_3657774 |      3657773
 RI_ConstraintTrigger_a_3657775 |      3657773
(2 rows)

blue=# SELECT tgname, tgconstraint
FROM   pg_trigger
WHERE  tgrelid = 'public.b'::regclass;
             tgname             | tgconstraint
--------------------------------+--------------
 RI_ConstraintTrigger_c_3657776 |      3657773
 RI_ConstraintTrigger_c_3657777 |      3657773
(2 rows)

Disable the triggers

blue=# alter table a disable trigger "RI_ConstraintTrigger_a_3657774"  ;
ALTER TABLE
blue=# alter table a disable trigger "RI_ConstraintTrigger_a_3657775"  ;
ALTER TABLE
blue=# alter table b disable trigger "RI_ConstraintTrigger_c_3657776"  ;
ALTER TABLE
blue=# alter table b disable trigger "RI_ConstraintTrigger_c_3657777"  ;
ALTER TABLE
blue=#

we can now insert values as we please, the FOREIGN KEY isn't checked

blue=# insert into a (id) values (1), (2), (3) ;
INSERT 0 3
blue=# insert into b (id, aid) values (1,1), (2,1), (4,4) ;
INSERT 0 3

Show the tables' structure

blue=# \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "a_b_fk" FOREIGN KEY (aid) REFERENCES a(id)
Disabled internal triggers:
    "RI_ConstraintTrigger_a_3657774" AFTER DELETE ON a FROM b NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"()
    "RI_ConstraintTrigger_a_3657775" AFTER UPDATE ON a FROM b NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"()

blue=# \d b
                 Table "public.b"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 aid    | integer |           | not null |
Indexes:
    "b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "a_b_fk" FOREIGN KEY (aid) REFERENCES a(id)
Disabled internal triggers:
    "RI_ConstraintTrigger_c_3657776" AFTER INSERT ON b FROM a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"()
    "RI_ConstraintTrigger_c_3657777" AFTER UPDATE ON b FROM a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"()

blue=#


Can I configure MySQL replication to break when I run a "drop database" or "drop table" command on the master?

The answer to your question is no -- the replication SQL thread implicitly has the SUPER privilege, so any command that was entered in the binary log can be executed by the slave.

You might be able to help your scenario by setting up a slave that is deliberately lagging behind the master. That way if someone accidentally drops or deletes something they shouldn't, you have a grace period to go to the slave, stop the replication thread, and run a backup of the data that was dropped.

In MySQL 5.6, you can configure a slave with delay:

mysql> CHANGE MASTER TO MASTER_DELAY=3600; /* 1 hour delay */

In MySQL 5.5 and earlier, you can simulate this behavior with the free tool pt-slave-delay.

But I agree with the comment from @Phil -- if you have people working on the production master database who can't be careful about which instance they are using before they run DROP TABLE, then you should revoke their privileges.

Another tip: you can set the MySQL client's prompt to indicate which host they're connected to.  Edit $HOME/.my.cnf:

[mysql]
prompt = mysql[\h]>


How can I check which database objects are using certain tablespace in Oracle DB?

I'm using the following SQL quite often:

SELECT * FROM dba_segments WHERE TABLESPACE_NAME='USERS' ORDER BY bytes DESC;

It will find all the objects which consume some space in given tablespace and sorts the output by used space.

TEMP objects are always related to user session. So you need to query another view:

SELECT * FROM v$tempseg_usage;


Oracle 12c - Can not login to Enterprise Manager - XDB Login prompt

You should enable global port for EM Express to Manage a CDB and the PDBs, execute:

exec dbms_xdb_config.SetGlobalPortEnabled(TRUE)


This looks like a bug (though it isn't technically a bug, see my first edit towards the end of the answer):

I see the behaviour on MySQL 5.7.20 and 5.7.21 (on Centos 7.4 if it matters):

mysql> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

And the warning is code 1411 Incorrect datetime value: '09:22' for function str_to_date.

ANSI_QUOTES is not enabled, and I get the same warning if I use single-quotes.

From what I understand reading the documentation, the query should work:

  STR_TO_DATE() returns a DATETIME value if the format string contains
  both date and time parts, or a DATE or TIME value if the string
  contains only date or time parts.

This query, containing a date as well as a time part, does work:

mysql> SELECT STR_TO_DATE("2017-03-17 09:22","%Y-%m-%d %H:%i");
+--------------------------------------------------+
| STR_TO_DATE("2017-03-17 09:22","%Y-%m-%d %H:%i") |
+--------------------------------------------------+
| 2017-03-17 09:22:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

Running the original query on MariaDB 10.2.14 works correctly:

MariaDB [(none)]> select str_to_date("09:22", "%H:%i");
+-------------------------------+
| str_to_date("09:22", "%H:%i") |
+-------------------------------+
| 09:22:00                      |
+-------------------------------+
1 row in set (0.00 sec)

EDIT:
This was reported as a bug previously, see here:
https://bugs.mysql.com/bug.php?id=80064

  If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates
  or part of dates are disallowed. In that case, STR_TO_DATE() returns
  NULL and generates a warning:

These modes are enabled by default in MySQL 5.7 (but not in MariaDB 10.2). So, after removing these from the sql_mode system variable, we get the desired result:

mysql> SET sql_mode='';
mysql> select STR_TO_DATE("09:22", "%H:%i");
+-------------------------------+
| STR_TO_DATE("09:22", "%H:%i") |
+-------------------------------+
| 09:22:00                      |
+-------------------------------+
1 row in set (0.00 sec)

EDIT 2:
We don't want to accidentally remove helpful/important modes from the sql_mode system variable, so ideally we should only remove specific modes, in this case NO_ZERO_DATE and NO_ZERO_IN_DATE. This can be done this way:

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', '');

(Use SET GLOBAL sql_mode... if you want to change it for the global scope rather than just the session.)

If you want to set sql_mode in the MySQL config file, then I think you'll just have to "hard-code" it, i.e. just find out what the default modes are for the version you're using, remove the undesired modes, and add the resulting string e.g. to the [mysqld] section of the .cnf file:

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This should be correct for MySQL 5.7. When upgrading to the next version, you'll have to update the setting again.

Note that stored procedures run with their own sql_modes:

  MySQL stores the sql_mode system variable setting in effect when a
  routine is created or altered, and always executes the routine with
  this setting in force, regardless of the current server SQL mode when
  the routine begins executing.

Source: the MySQL stored procedure documentation.

You can also change the sql_mode within a stored procedure.


Trying to deploy sp_blitzIndex - but fails

You're trying to deploy something other than sp_BlitzIndex. The warning that you're getting is part of a paid product that we offer, and I'm guessing that you copy/pasted that script from another source.
Try downloading a fresh copy of sp_BlitzIndex from one of the official sources, like http://firstresponderkit.org or https://www.brentozar.com.


Old archivelog files present on disk, but not in RMAN

Why does crosscheck archivelog all not detect them and add them back?

Because that's not what crosscheck does. Crosscheck reads the records of the archlogs that are recorded in the repository - the ones that rman reports. It reads a record from the repository, then checks to see if that file exists. If not, the record is marked 'expired'. If rman shows no sign of knowing of these files, I'd say it's safe to delete them.


Generate Database Creation Script Within A Stored Procedure

There are several methods you could use.  In order of difficulty (easiest to hardest IMHO)

Manually create (using generate scripts) a structure only DB populate it with just the data you need for the copy and then backup/restore it.  (you could also do it structure only and code the data copy)

Up side - This is the easiest method to code and should be fairly quick
Down side - This requires constant maintenance as the code/data of your source database changes.

Manually create the scripts and have the SP run them (probably using dynamic SQL) and copy the data.

Up side - Also fairly easy to implement and doesn't require an additional "model" DB that you will copy.
Down side - Also requires constant maintenance

Powershell - You use a POSH script to create the new db, generate the scripts and run them.

Up side - This is the most powerful method.  It won't require much maintenance and you can probably even find a script someone else has written as a starting point.
Down side - This requires that you know Powershell at least a little bit. (Which might be considered an up side as well)

OLE Automation Procedures - sp_Create etc.  This is going to be the most difficult/complicated method (knowing at least a little bit of this and PoSH) but once written also won't require that much maintenance.

Up Side - Minimal maintenance and it can all be run inside of SQL
Down side - more difficult code, you probably can't find a script already built and you have to enable OLE Automation Procedures (a security risk, if minimal).

I should probably note that my original thought was you wanted this to be re-producible.  If you only have to do it once then @MaxVernon is right.  Truncate the tables you don't need data in (or delete what you don't need) and shrink.  You may have to do this a bit at a time depending on how constrained you are for  space.


If the effect isn't required until a later point in time, I would execute the payload at that later point in time and not via trigger. This way you can collect multiple updates on the underlying table and avoid redundant calls of the DDL script.

Depending on your circumstances, a timestamp column or simple boolean flag in the underlying table might suffice to mark rows that should trigger delayed events.

  is it possible to obtain the current schema from the underlying table ..

Yes. You can query practically any possible detail from the system catalogs or from the standardized (but much slower) information schema.  Furthermore, in a trigger procedure you have the TG_TABLE_SCHEMA variable set.

This related answer has more information and an example:
How do I list all columns for a specified table


Will matching SQL Server network packet size to mtu increase performance

TLDR: set it lower if it helps you, monitor your packet sizes to see if you are sending more packets than you need.

The MTU controls how large each network segment can be on all the points between each server, think traceroute hops - the MTU controls how large the segments are between 2 hops roughly. The smaller the MTU on a particular segment the more likely that the packet will be sent over more than one segment. This is normally fine unless you have a segment that is particularly busy.

The packet size controls how much data can be sent per TDS packet in sql. Each TDS packet has a header so some additional overhead. The smaller the packet size, the more packets you need to send your data with this caveat:

If the packets you want to send are always smaller than the max packet size then you could set max packet size to like 16k and it would make no difference because you can fit within one packet.

If you only call stored procs called “a” with no parameters and the response is only ever a stored proc status code then you could set max packet size to something tiny like 50. If you had very large requests (massive multi hundred line select statements) and lots of rows in the response then you would want as big a packet size as possible to avoid the overhead of more packet headers.

When you have a high error connection then TCP gets in the way and sends retransmits which further clog up the network and when packets go missing then everything gets held up until the missing ones sent on, so a high error rate with lots of retransmits is normally really bad for performance. Having a smaller packet size, results in more packets which results in more likelihood of lost packets which in turn means more retransmits and delays waiting for lost bits.

So is a smaller packet size bad? Normally but it really depends on your network.

I would do two things, first grab Microsoft Message Analyzer ( https://www.microsoft.com/en-gb/download/details.aspx?id=44226) and trace on each server and just look for TCP retransmits, if you have lots of these then you have a high error rate which would suggest you need to send less packets so a higher packet size.

In message analyzer you can add a column to show TDS PacketSize so if you have max packet size set to 1000 and you see lots of packets with a size of 1000 then lots of packets with size of 1 then the ideal is probably 1001 or 1002.

The second thing is look at something like tds nitro which compresses TDS so you can end up with less packets for your data which will likely help in high latency high error connections (http://nitrosphere.com/nitroaccelerator/)

Ed


EXPLAIN output suggests that my index is not being used

You misunderstand what the 'rows' field is. It is the number of rows that mysql estimates it shall need to read to satisfy your query. This value can be quite inaccurate. It does not mean this is the number of rows in the result - or the actual number of rows read by mysql


No, there is no built-in way to do this within SQL Server. As Marian stated, you could recompile all of your procedures to be sure they're still valid, but this doesn't prove they'll still work (and don't forget that deferred name resolution makes this validation less than stellar anyway). However you can use other tools such as SQL Server Data Tools (SSDT) to help facilitate unit testing.

Just like changes to your application, if you change your schema, you need to test those changes. How automated you make your unit testing depends on the complexity of your schema, the consuming application(s), and the number of permutations possible for stored procedure outcomes (including both explicit input of different parameters or different parameter values, and implicit input such as time of day, state of the system, specific data at rest, etc.). You have to keep in mind that in some cases you will expect the stored procedure output to stay the same, but in others you will actually expect it to be different.

Some potentially helpful links (a couple culled from a question on SO geared to testing a single stored procedure):

TSQLUnit for unit tests
T.S.T. the T-SQL Test Tool


I am not familiar with Python, but for your current requirement (i.e. backup).
I'd strongly recommend you to use PowerShell for two reasons:

MS in its SQLPS module (since SQL Server 2012), has a native cmdlet called

Backup-SQLDatabase
You may run this cmdlet on one central server to do the backup against multiple sql instances at once because this Backup-SQLDatabase can accept multiple sql instances, like the following example

Backup-SQLDatabase -Database master -Server "my_server1", "my_server2" ...

This 2nd point may help you to centralize all those backup scripts to one administration server (dedicated for DBA use) instead of sparsely stored on each sql server instance.

Also with MS providing Backup-SQLDatabase, you can be assured that it will be supported for all future new features. (For example, using this cmdlet to support Azure SQL Database etc.)


Change month partition to day partition with current data in Oracle 12c

The above affects only partitions created in the future.

If you want to change your monthly partitions into daily partitions, you can use:

ALTER TABLE ... SPLIT PARTITION ...

Example:

ALTER TABLE sales SPLIT PARTITION sales_201701 INTO
( PARTITION sales_20170101 VALUES LESS THAN (TO_DATE('02-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170102 VALUES LESS THAN (TO_DATE('03-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170102 VALUES LESS THAN (TO_DATE('03-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170103 VALUES LESS THAN (TO_DATE('04-JAN-2017','dd-MON-yyyy')),
...
  PARTITION sales_...);

More examples: https://oracle-base.com/articles/12c/online-split-partition-and-subpartition-12cr2


Prevent updating table using procedure in different database

Below are cases where updates will be allowed via the stored stored procedure without the caller having update permissions on the table in the other database.

Ownership chaining

The databases involved have the DB_CHAINING option set or the 'cross database ownership chaining' option turned on at the server level. In the case of dbo-owned objects, the databases must also the same owner (i.e. dbo user maps to same login) to mainain an unbroken ownership chain.

EXECUTE AS

The stored procedure includes an EXECUTE AS USER specification and the user (mapped to same login) exists in the other database with update permissions. Note that the database must be TRUSTWORTHY in order for the EXECUTE AS context to be honored by other databases so this does not apply in your case.

Module Signing

The stored procedure is signed by a certificate and a user created from that certificate exists in the other database with permissions to update the table.

I see from your comment that 'cross database ownership chaining' was the culprit. This also implies the databases also have the same owner. I suggest you change the database owner for databases where non-sysadmin role members can create dbo-owned objects to mitigate security risk.


Where does SQL Server store symmetric keys?

The underlying system tables used by the sys.symmetric_keys catalog view are stored on the PRIMARY filegroup along with other database meta-data.


What are the implications of upgrading statistics SQL Server

Tell me about it?

You are speculating. I recommend that instead you investigate. Read How to analyse SQL Server performance. Find the bottlenecks, address them appropriately, based on your findings. Measure before you cut.


To see open transactions which may be locking rows, execute:

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Issuing a COMMIT or ROLLBACK for those transactions would address your issue.

If you don't mind dirty reads (depends on your business rule for your particular situation) you can

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

And this would allow you to read the table without the wait. It;s important to understand that the data would be inaccurate if the pending transaction was rolled back.


Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

In Postgres 9.6 or later you can have this much simpler. Like a_horse commented, there is a new variant of current_setting() that takes a second parameter missing_ok, to suppress exceptions if the configuration parameter (poor man's "global variable") is not set. So no expensive error trapping required any more.

And you don't need another variable conf_param, you can cast the result of the function call directly:

DECLARE
   num integer;
BEGIN
   num := current_setting('the_setting', true)::int;
   -- more stuff
END;

This is assuming the config parameter is always a valid numeric string if set. If you are not completely sure about that, you can either add an EXCEPTION clause to trap possible casting errors after all. Or test the value before casting to rule out exceptions. Typically much cheaper. Detailed instructions:

Filter strings with regex before casting to numeric


Ensure same tenant in a Many to Many relationship

This is almost an anti pattern if you ask me.

All accounts have a company
All account_groups have a company
They're all linked through a linking table which also has a company.

So, everything has a company: except when it doesn't? And if a company has a subsidiary you're in a world of trouble. It may make sense. But, what you seem to be doing is creating buckets which you can relate to in order to emulate hierarchy; however, the hierarchy is easier to just have in all it's glory.

CREATE TABLE accounts (
  accountid             serial PRIMARY KEY,
  name                  text   NOT NULL,
  email                 text,
  contact_name_given    text,
  contact_name_surname  text,
  parent_accountid      int    REFERENCES accounts
);

INSERT INTO accounts (accountid,name,email,contact_name_given,contact_name_surname,parent_accountid) VALUES
  (1,'ACME', 'company@acme.com',null,null,null),
  (2,'ACME Sales', 'sales@acme.com', 'John D', 'Supervisor', 1),
  (3,'Evan Carroll', 'ecarroll.sales@acme.com', 'Evan', 'Carroll', 2);

Then get the list with a RECURSIVE CTE

WITH RECURSIVE t(accountid, name, email, contact_name_given, contact_name_surname, level)
AS (
  SELECT
    accountid,
    name,
    email,
    contact_name_given,
    contact_name_surname,
    0
  FROM accounts
  WHERE parent_accountid IS NULL
  UNION ALL
    SELECT
      a2.accountid,
      a2.name,
      a2.email,
      a2.contact_name_given,
      a2.contact_name_surname,
      t.level+1
    FROM t
    JOIN accounts AS a2
      ON ( t.accountid = a2.parent_accountid )
)
SELECT *
FROM t;

 accountid |     name     |          email          | contact_name_given | contact_name_surname | level
-----------+--------------+-------------------------+--------------------+----------------------+-------
         1 | ACME         | company@acme.com        |                    |                      |     0
         2 | ACME Sales   | sales@acme.com          | John D             | Supervisor           |     1
         3 | Evan Carroll | ecarroll.sales@acme.com | Evan               | Carroll              |     2
(3 rows)

Now you just need to give logical names to the level = 0,1,2 if that's something the task really requires. or make the schema more accommodating of different types of entities in the hierarchy.


Transactional Replication is not intended as a High Availability/Disaster Recovery solution.  There is no client connectivity support for failover, and it's operationally complex.

You should usually use AlwaysOn Availability Groups instead.

  how should the subscriber server be configured so that when a failover of the publishing node occurs, the data written in the subscriber is replicated later to the publisher.

Either remove replication and re-create it with the subscriber as the new publisher, or use Bi-Directional Transactional Replication, or Peer-to-Peer Transactional Replication.


why will compatibility mode change amount of storage iops

Presumably something is causing the query planner to produce a less optimal plan for some of your regular statements, resulting in more IO being needed (perhaps index scans where seeks were previously used, or maybe the worse plan requires extra sort operations and/or sorts over larger rowsets so is spilling more into tempdb).

A key difference between 2016 and 2012 is the updated cardinality estimator, added in 2014 and made default in 2016. To tell for sure we would need to see query plan output for the two circumstances. The new estimator produces better plans in many cases, but worse ones in a few specific cases.

For more general detail search for "sql server cardinality estimator" and you'll find many articles on the change and its implications.

For more specific help, we would need you to edit query text and query plan output from both circumstances into your question.

It could be that you need to turn the new estimator off, it could be that you have hints in the queries designed to help the older planner which make the newer one have a harder time, it could be that the queries are odd/complex and need hints to help the new planner/estimator and so on.


Oracle 10.2.0.1.0 listener process stopped

There is probably not enough logged at the default level to give you any real insight to root cause, so the best thing to do is restart the listener at a higher logging level. Enter

LSNRCTL> trace admin
LSNRCTL> save_config
LSNRCTL> start

Then if it crashes again, you'll know why. You can also set the variables trc_directory, trc_file and trc_level to customize this behavior.

Also your HOST=0.0.0.0 looks odd to me... Shouldn't that be the IP address of your host? What do you have in LISTENER.ORA?


Unique foreign key constraint between three tables

I would use one table for both completed and missed appointments. Using your notation:

appointments:
id,
visitor_id,
scheduled_time,
status,
CHECK(status in('Scheduled', 'Completed', 'Missed')),
Reason_missed,
CHECK((status='Missed' and reason_missed is not null) or (status<>'Missed' and reason_missed is null)),
unique(id, status)

attendances:
id,
appointment_id (nullable),
arrival_time,
appointment_status,
check((appointment_id is null and appointment_status is null) or (appointment_id is not null and appointment_status in('Scheduled', 'Completed'),
foreign key(appointment_id, appointment_status) references appointments(id, status) on update cascade


How to calculate conditional running additions in SQL Server 2012

Below is a CTE method to facilitate evaluating values from the previous row with LAG.

CREATE TABLE #test
(
      ID int IDENTITY
    , A int
    , B int
);
INSERT INTO #test (A, B)
VALUES
      (9, NULL)
    , (9, 1)
    , (9, 2)
    , (8, NULL)
    , (8, 4)
    , (8, 5);

WITH test AS (
    SELECT
          ID
        , A
        , LAG(A) OVER(ORDER BY ID) AS prev_A
        , LAG(B) OVER(ORDER BY ID) AS prev_B
        , A + COALESCE(LAG(B) OVER(ORDER BY ID), 0) AS C
    FROM #test
)
SELECT
    A + COALESCE(CASE
            WHEN prev_B IS NOT NULL THEN prev_B
            ELSE LAG(C) OVER(ORDER BY ID)
            END, 0) AS C
FROM test
ORDER BY ID;

EDIT

To handle the NULL A values in the edited question, try the recursive CTE method below.

WITH
    curr AS (
        SELECT
              A
            , B
            , ROW_NUMBER() OVER (ORDER BY ID) AS rownum
        FROM #test
    )
    , prev AS (
        SELECT
              A
            , B
            , COALESCE(A, 0) AS C
            , rownum
        FROM curr
        WHERE rownum = 1
        UNION ALL
        SELECT
              curr.A
            , curr.B
            , CASE
                WHEN prev.B IS NULL THEN prev.C + COALESCE(curr.A, 0)
                ELSE prev.B + COALESCE(curr.A, 0)
              END AS C
            , curr.rownum
        FROM prev
        JOIN curr ON curr.rownum = prev.rownum + 1
    )
SELECT C
FROM prev;


Question: Does this mean the query is using indexes?

The Using intersect(FK_DataValues_Sites,FK_DataValues_Variables) means that the query is using two indexes on table datavalues with the Index Merge Intersection Access Algorithm and then (Using where) the rows are additionally checked against some other conditions.

Question: Any other suggestions? (I guess about efficiency)

This is most probably not the most efficient way, especially if the query is retrieving a small perecentage of the table's rows.

For example, lets say that the SiteID = 15 condition narrows the search to 10% of the rows and the VariableID = 8 condition narrows the search to 5% of the rows. The two existing indexes can be used invidually or combined (with tthe help of the index merge intersection algorithm) to narrow the search to (0.10 * 0.05 =) 0.5% of the tables' rows (and that is what the MySQL engine is doing in this case.)

With a 280M rows table, this means that the first index can efficiently give us the 28M keys that match the 1st condition and the second index can give us the 14M rows that match the 2nd condition. Then, the index merge algorithm can further narrow it to the 1.4M keys (at the extra cost of this algorithm). Then, the 1.4M rows have to be retrieved and the 3rd condition (LocalDateTime >= '2009-04-18T00:02' AND LocalDateTime <= '2009-04-20T00:02) - that is not covered by any index - has to be checked for every one of these 1.4M rows.

If this 3rd condition narrows the results to, say 5% or 70K rows, the overall efficiency of the query will not be the best possible.

The best would be if there was an index that covered all 3 conditions. Fortunately, there is a way. A multi-column index on either (VariableID, SiteID, LocalDateTime) or on (SiteID, VariableID, LocalDateTime) would be enough to narrow the search to only the needed keys (with the previous example to only 70K) and then only those rows would be retrieved from the table.

Extra question: Do I have to remove the current index and then replace it with a multi-column index?

No, I wouldn't remove any index, just add the new one, unless I was sure I don't need an existing index.

But analyzing queries (and indexing) should be done after examining all the queries against the database and the requirements (which queries need to be extra fast and optimized, which ones we can afford to wait longer for them, how other operations will be affected if we add a lot of indexes, etc.)


How to set the timeoffset in a Datetimeoffset column without changing the date part

To avoid the extra DateAdd (may be difficult if time zone is a variable), use:

TODATETIMEOFFSET(@datetimeoffset, @timeZone)


PostgreSQL - how to generate an array containing column name and column data...?

The first one can be done using a JSON function

select to_jsonb(n)
from names n;

I have no idea what you want to achieve with the second query.


May postgresql's uuid_generate_v1() conflict when we change the computer date/time?

There is no reason, that I know of to use uuid_generate_v1() over uuid_generate_v4(). That said, Philᵀᴹ is largely correct when he says

  "One scenario is the summer time / daylight savings." -- no, it is not. Everything should be stored as UTC. Time always goes forwards. – Philᵀᴹ

Even if your display time is set to DST, (or something like CSTCDT), the underlying storage mechanism should be UTC.

That said, though everything should be stored as UTC, it's not always that way. There are exceptions, like with XP, or Windows 7, you may see some kind of abnormality, especially if you permit the other OS to modify the RTC.

And, there is always the problem of leap seconds if you really want to nitpick, when some kernels or daemons will roll back the second and it's basically up to them to see if they support this. Linux-proper and OSX does, Android does not afaik humorous NDT on Leap seconds

For more info about leap seconds
For more info about RTC/system clocks in linux

For reference, the spec details the process of generating a UUID v1 time-based UUID, 4.2 Algorithms for Creating a Time-Based UUID.


Here's one attempt that I'm unsatisfied with for several reasons, but it's a start. The idea is to calculate the transitive closure of the graph and then use the minimum field_2 for each field_1 as the group. The latter uses the fact that for two nodes either share the same closure, or the closures are disjoint. As an example:

{(a,1),(a,2),(b,2),(b,3)}

is an impossible transitive closure since if a and b share 2, they must also share 1 and 3:

I used Db2 (which does not support the keyword recursive in CTE as well as ansi join in the CTE)

CREATE TABLE t (x char(1) not null, y int not null, primary key (x,y));
INSERT INTO t (x,y)
VALUES ('A',1)
      ,('A',2)
      ,('B',3)
      ,('C',3)
      ,('D',4)
      ,('E',4)
      ,('E',5)
      ,('F',6)
      ,('G',1);

with tc (field_1,field_2,n) as (
    select field_1,field_2,0 from t
    union all
    select t1.field_1, t3.field_2,n+1
    from tc as t1, t as t2, t as t3
    where t1.field_2 = t2.field_2
      and t1.field_1 < t2.field_1
      and t2.field_1 = t3.field_1
      and t2.field_2 < t3.field_2
      and t1.field_2 < t2.field_2
      and n <= (select count(1) from t)
), min_closure(field_1,field_2) as (
    -- remove duplicates
    select distinct field_1,field_2 from tc
)
select t.field_1, t.field_2
     , ( select min(field_2)
         from min_closure mc1
         where t.field_1 = mc1.field_1 ) as grp
from t
order by grp;

A           1           1
G           1           1
A           2           1
B           3           3
C           3           3
D           4           4
E           4           4
E           5           4
F           6           6

The group can be further adjusted, but I'll leave it like that.

Major problem is that the CTE only sees the previous iteration, so it is very likely that we will add a lot of redundant edges to the tc. For a large graph that won't be very pleasant.

Edit: attempt to minimize impact by only looking in one direction in graph

Example of a longer chain:

delete from t;
insert into t (x,y) values ('F',5),('F',6),('G',6),('G',7),('H',7),('H',8);
with tc (x,y,n) as (select x,y,0 ...)
select distinct t.x, t.y, min(mc1.y) over (partition by mc1.x) as grp
from min_closure mc1 join t
    on t.x = mc1.x order by 3;

F           5           5
F           6           5
G           6           5
G           7           5
H           7           5
H           8           5

EDIT: adjusted for PostgreSQL dialect:

with recursive tc (field_1,field_2,n) as (
    select field_1,field_2,0 from t
    union all
    select t1.field_1, t3.field_2,n+1
    from tc as t1
    join t as t2
        on t1.field_2 = t2.field_2
     and t1.field_1 < t2.field_1
    join t as t3
        on t2.field_1 = t3.field_1
       and t2.field_2 < t3.field_2
    where t1.field_2 < t2.field_2
      and n <= (select count(1) from t)
), min_closure(field_1,field_2) as (
    -- remove duplicates
    select distinct field_1,field_2 from tc
)
select t.field_1, t.field_2
     , ( select min(field_2)
         from min_closure mc1
         where t.field_1 = mc1.field_1 ) as grp
from t
order by 1;


Managing concurrency when using SELECT-UPDATE pattern

Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.

Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when attempting to acquire the locks needed for the update (object IX lock or index RangeS-U respectively) which will lead to deadlock.

The use of an explicit UPDLOCK hint instead will serialize the reads thus avoiding the deadlock risk.


How to delete an endpoint only if exists

IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = 'End_Mirroring')
DROP ENDPOINT End_Mirroring;
IF EXISTS ( SELECT * FROM sys.certificates WHERE name = 'PRIM_cert' )
DROP CERTIFICATE PRIM_cert;
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE [name] LIKE '%DatabaseMasterKey%')
DROP MASTER KEY";

OK, found it :)