Index not used with `=any()` but used with `in`

Internally, there are two separate forms of IN, as well as for the ANY construct.

One of each, taking a set, is equivalent to the other and expr IN (<set>) also leads to the same query plan as expr = ANY(<set>) that can use a plain index. Details:

IN vs ANY operator in PostgreSQL

Consequently, the following two queries are equivalent and both can use the plain index t_a_b_idx (which can also be the solution if you are trying to get your query to use the index):

EXPLAIN ANALYZE
SELECT *
FROM t
WHERE (a,b) = ANY(VALUES (1,1),(1,2));

Or:

...
WHERE (a,b) IN (VALUES (1,1),(1,2));

Identical for both:

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.33..16.71 rows=1 width=8) (actual time=0.101..0.101 rows=0 loops=1)
   ->  Unique  (cost=0.04..0.05 rows=2 width=8) (actual time=0.068..0.070 rows=2 loops=1)
         ->  Sort  (cost=0.04..0.04 rows=2 width=8) (actual time=0.067..0.068 rows=2 loops=1)
               Sort Key: "*VALUES*".column1, "*VALUES*".column2
               Sort Method: quicksort  Memory: 25kB
               ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8) (actual time=0.005..0.005 rows=2 loops=1)
   ->  Index Only Scan using t_plain_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=2)
         Index Cond: ((a = "*VALUES*".column1) AND (b = "*VALUES*".column2))
         Heap Fetches: 0
 Planning time: 4.080 ms
 Execution time: 0.202 ms

However, this cannot easily be passed to a function, since there are no "table variables" in Postgres. Which leads to the problem that started this topic:

Pass multiple sets or arrays of values to a function

There are various workarounds for that problem. One being the alternative answer I added there. Some others:

Insert values from a record variable into a table
Declare variable of table type in PL/pgSQL

The second form of each is different: ANY takes an actual array, while IN takes a comma separated list of values.

This has different consequences for typing the input. As we can see in the EXPLAIN output of the question, this form:

WHERE (a,b) = ANY(ARRAY[(1,1),(1,2)]);

is seen as shorthand for:

ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)])

And actual ROW values are compared. Postgres is not currently smart enough to see that the index on the composite type t_row_idx is applicable. Nor does it realize that the simple index t_a_b_idx should be applicable as well.

An explicit cast helps to overcome this lack of smarts:

WHERE (a,b)::int_pair = ANY(ARRAY[(1,1),(1,2)]::int_pair[]);

Casting the right operand (::int_pair[]) is optional (though preferable for performance and to avoid ambiguities). Once the left operand has a well-known type, the right operand is coerced from "anonymous record" to a matching type. Only then, the operator is defined unambiguously. And Postgres picks applicable indexes based on the operator and the left operand. For many operators that define a COMMUTATOR, the query planner can flip operands to bring the indexed expression to the left. But that's not possible with the ANY construct.

Related:

Is there a way to usefully index a text column containing regex patterns?

WHERE (a,b) IN ((1,1),(1,2));

.. values are taken as elements and Postgres is able to compare individual integer values as we can see in the EXPLAIN output once more:

Filter: ((b = 1) OR (b = 2))

Hence Postgres finds that the simple index t_a_b_idx can be used.

Consequently, there would be another solution for the particular case in the example: since the custom composite type int_pair in the example happens to be equivalent to the row type of the table t itself, we could simplify:

CREATE INDEX t_row_idx2 ON t ((t));

Then this query would use the index without any more explicit casting:

EXPLAIN ANALYZE
SELECT *
FROM   t
WHERE  t = ANY(ARRAY[(1,1),(1,2)]);

                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=40.59..496.08 rows=1000 width=8) (actual time=0.19
1..0.191 rows=0 loops=1)
   Recheck Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   ->  Bitmap Index Scan on t_row_idx2  (cost=0.00..40.34 rows=1000 width=0) (actual time=0.188..0.188 rows=0 loops=1)
         Index Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 2.575 ms
 Execution time: 0.267 ms

But typical use cases won't be able to utilize the implicitly existing type of the table row.


You may be interested in a standard approach to solve this problem. It is something called short circuit. To illustrate it, here's the code:

DECLARE @chooseDate datetime2
--SET @chooseDate = '1966-04-08'
SET @chooseDate = NULL
SELECT *
FROM DimCustomer
WHERE @chooseDate IS NULL OR BirthDate = @chooseDate

This way you can explicitly set variable to NULL, do not set variable to anything, or specify a value.

The reason it is called short circuit is that based on the implementation of ANSI SQL this line of code:

WHERE @chooseDate IS NULL OR BirthDate = @chooseDate

has a possibility to evaluate only the first part of the expression (the IS NULL part) thus excluding the need to evaluate the second part, which makes the code slightly faster.


SQL 2008 Server agent cant see perfmon counters

You'll have to work through this "SQL Server – Missing Performance Counters: A consolidated list of known issues" sorry.

I'm surprised this still happens: I last saw this with SQL Server 7/2000 years ago.


If you have the Partitioning Option then consider hash partitioning the two tables into 32, 64, or 128 etc partitions on the join key. Whether you use parallel or serial query, your memory consumption will be very much reduced.


Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

Maybe Brandon's idea here will help:
How to add an article to existing replication

Summary of his suggestions to fix:

execute sp_refreshsubscriptions for pull subscriptions
or in push subscriptions use the sp_addsubscription as you mentioned (worked
for me)

I wrote some code to help identify the "orphaned articles on subscriber's":

--Run from distributor
SELECT  pub.data_source AS Publisher ,
        a.Publisher_db ,
        p.Publication ,
        sub.data_source AS Subscriber ,
        s.Subscriber_db ,
        a.Article
FROM   distribution.dbo.MSarticles as a
LEFT JOIN distribution.dbo.MSpublications AS p ON a.publisher_id = p.publisher_id AND a.publication_id = p.publication_id
JOIN sys.servers AS pub ON p.publisher_id = pub.server_id
LEFT JOIN distribution.dbo.MSsubscriptions AS s ON a.publisher_id = s.publisher_id AND a.publication_id = s.publication_id AND a.article_id = s.article_id
LEFT JOIN sys.servers AS sub ON s.subscriber_id = sub.server_id
WHERE s.subscriber_db IS NULL --Leave off for great documentation, but this will show the "orphaned" subscribers effected by this issue.


Rollback Committed transaction in SQL Server

No. Committed transactions modify the contents of the database tables first in the transaction log, then in the data files. Unless triggers or other techniques are explicitly set up upfront, there is no way to recover the values stored in the database before the transaction was committed.

In order to do that, you have to restore a copy of the database before the transaction ended.

Other techniques rely on reconstructing the data reading the transaction log, but I don't recommend them: restoring a copy of the database is much easier (and supported).


Are there side effects to Postgres 12's NOT MATERIALIZED directive?

It's explained very well in the documentation.

  A useful property of WITH queries is that they are normally evaluated
  only once per execution of the parent query, even if they are referred
  to more than once by the parent query or sibling WITH queries. Thus,
  expensive calculations that are needed in multiple places can be
  placed within a WITH query to avoid redundant work. Another possible
  application is to prevent unwanted multiple evaluations of functions
  with side-effects.

So far, so good, BUT:

  However, the other side of this coin is that the optimizer is not able
  to push restrictions from the parent query down into a
  multiply-referenced WITH query, since that might affect all uses of
  the WITH query's output when it should affect only one. The
  multiply-referenced WITH query will be evaluated as written, without
  suppression of rows that the parent query might discard afterwards.

So, as pointed out in the example given, if you have a query like this:

WITH w AS (
    SELECT * FROM big_table  -- big_table has an INDEX on a field called key!
)
SELECT * FROM w AS w1
  JOIN w AS w2 ON w1.key = w2.ref  -- w is called TWICE, so DEFAULT is MATERIALIZED
                                   -- PostgreSQL can't take advantage of big_table.key
WHERE w2.key = 123;

So, in this case:

  the WITH query will be materialized, producing a temporary copy of big_table that is > then joined with itself — without benefit of any index

Far better to have:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

So that the optimizer can "fold" the CTE query "into" the main query and make use of the INDEX on the key field of big_table!

Re. the DEFAULT of NOT MATERIALIZED:

  However, if a WITH query is non-recursive and side-effect-free (that
  is, it is a SELECT containing no volatile functions) then it can be
  folded into the parent query, allowing joint optimization of the two
  query levels. By default, this happens if the parent query references
  the WITH query just once, but not if it references the WITH query more
  than once.

So the DEFAULT is NOT MATERIALIZED if:

    the_query IS NOT recursive
AND the_query is_side_effect_free
AND the_query is_run_only_once

otherwise you have to tell PostgreSQL to use NOT MATERIALIZED.

The only small problem that I see is that testing will be required to see if
NOT MATERIALIZED is an improvement or not? I can see circumstances where the balance will swing between the two depending on table size, fields selected and indexes on the fields and tables used in the CTE - in other words, there's no substitute for knowledge and experience. The DBA isn't dead and gone yet! :-)


Database SQL Script very, very slow (string manipulation over 700k rows)

You have a performance question so I recommend you read How to analyse SQL Server performance to begin with.

As about your script: you're doing it as slowly as possible. Row by painfully slow row. Not even a cursor slow, is worse than a cursor slow. What you have here is a single update:

update original_test_dataset
set duplicate= case when PATINDEX('%dup%', id)=0 then 0 else 1 end,
 id_only = REPLACE((LEFT(REPLACE(id,'rec-',''), CHARINDEX('-',REPLACE(id,'rec-',''),0)),'-','');

That's it, nothing else.

What you have in the original script is slow because:

it operates on row by row as opposed to sets. SQL is designed to manipulate sets, not rows.
it perform a lot  of writes in standalone commits resulting in huge flush commit waits. Batch commit should be used.
it performs multiple unindexed end-to-end scans on  #ControlTable (every operation on  #ControlTable is an unindexed end-to-end scan, it should had an index on id, yes #temp tables do need indexes)
it does two updates per row instead of one update of two fields
I can't know whether the access to original_test_dataset is in any way optimized, in lack of an index on id is likely is not.

declare @TableID varchar(20) vs.  [id] [varchar](50) NULL: you're silently truncating data. Basically, your script corrupts the table.


mysqld_safe not run on RHEL7, while it runs on RHEL6 when starting mariadb

It is stated in the mariadb and even mysql docs:

https://mariadb.com/kb/en/mariadb/systemd/

systemd services are packaged on RPM and Debian based Linux distributions. When systemd is used, mysqld_safe is not used and settings in the mysqld_safe section of configuration files will not be read or applied.

https://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd.html

On platforms for which systemd support is installed, scripts such as mysqld_safe and the System V initialization script are not installed because they are unnecessary. For example, mysqld_safe can handle server restarts, but systemd provides the same capability, and does so in a manner consistent with management of other services rather than using an application-specific program.


Find out what queries are causing the biggest amount of network traffic

You can find this from the DMVs:

SELECT session_id, num_writes, st.text AS statement_text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
ORDER BY num_writes DESC

Unlike tracing this should be perfectly safe to run on a Production server.


User switching accounts, can't log into MS SQL Server anymore

So after some more researching, we decided to reboot the server after-hours. This morning the person is able to log in. Looks like it is some sort of user caching that was causing the problems. We are going to do our other user moves and see if it works.


SQL Server Management Studio - Why is Dark Theme Disabled by Default?

Because it is a beta feature according to this blog, and this suggestion as well as this one for 18.1.
As a workaround, you can get some custom themes from the web. A favorite of mine is Aaron Bertrand's. He explains how to do it manually (so you can roll your own) and also provides a file you can just upload. Other blogs mention this as well.

Basically I'm curious if there is some sort of consequence I'm unaware
of by turning this feature back on.

Like all BETA features, it isn't supported and thus if it breaks something else that's on you.


How do I shrink the MASTER log file in SQL Server 2008?

The first question that is going to come to mind for many readers is, "why is your master database log so big that you are thinking of shrinking it?"  If user tables were accidentally placed in master that caused log file growth, they need to be removed.

If the master log file has become overly large for some reason and there is now free space in it, you can shrink it like any other database log file.  Master should already be in simple recovery mode, so your shouldn't need to change it.

USE master
GO
DBCC SHRINKFILE (N'mastlog', 0, TRUNCATEONLY)
GO

You may want to resize it after this if it shrunk down to less than a couple MB.  Not sure what the default is, but I'm looking at a later version of SQL Server and it is 3 MB, so anything in that range should be a sufficient.


Get the list of tables used in a stored-procedure for a database

If you use dynamic SQL to construct table names (or accept them as arguments), or use nested views, it becomes very difficult to automate this. However if you stick to traditional T-SQL and proper references you can get there with this:

DECLARE @procid int = OBJECT_ID(N'dbo.procedurename');

;WITH src AS
(
  SELECT name =
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name)
  FROM sys.sql_expression_dependencies AS d
  WHERE d.referencing_id = @procid
)
SELECT name FROM src GROUP BY name;

And if you just want a catalog of all references, you can do this:

;WITH src AS
(
  SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
      ref =
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name)
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
  ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE o.[type] = N'P'
)
SELECT [procedure],ref
  FROM src
  GROUP BY [procedure],ref;


Issue With SSRS Emailing Reports As PDFs

Another part of this puzzle was that the files that weren't able to send were created in a temporary location where they would then be deleted.

This issue is now completely resolved - in brief (after several false starts such as checking file/folder permissions and discovering reports would be sent via subscription as pdf if they contained a graph and not a matrix); we ended up using SMTP (set up on SQL Server) to send the email from the SQL Server to Exchange, which would then handle the rest of the journey.

Thank you all for your suggestions.


In SQL Server 2008, in DMV sys.dm_db_index_phsicial_stats, what does compressed_page_count mean in comparision with page_count

The clustered index has 140992 pages, from which only 1410 are page compressed. Page compression is attempted only when it fills up, see When Page Compression Occurs:

  When a new table is created that has page compression, no compression
  occurs. However, the metadata for the table indicates that page
  compression should be used. As data is added to the first data page,
  data is row-compressed. Because the page is not full, no benefit is
  gained from page compression. When the page is full, the next row to
  be added initiates the page compression operation. The whole page is
  reviewed; each column is evaluated for prefix compression, and then
  all columns are evaluated for dictionary compression. If page
  compression has created enough room on the page for an additional row,
  the row is added, and the data is both row- and page-compressed. If
  the space gained by page compression minus the space that is required
  for the CI structure is not significant, page compression is not used
  for that page. Future rows either fit onto the new page or, if they do
  not fit, a new page is added to the table. Similar to the first page,
  the new page is not at first page-compressed.

It seems that in your case very few pages got compressed. Note that you still benefit from row compression.


replica set in MongoDB using docker, primary has error and stops being primary when another member is added to the set

Your primary is trying to auto-configure itself as ee3c41ef76b2:27017 and that then resolves to the loopback (127.0.0.1) which is then not responding on the container as it expects.  Depending on what the second container does to resolve ee3c41ef76b2, and especially it it does not resolve to 172.17.0.2, it will probably not be able to talk to the primary either.

Assuming you are correct about the connectivity (and you have verified that the instances are listening on the IP and not just the loopback) then you need to override the automatic detection and be explicit when you are calling rs.initiate(), something like this:

rs.initiate(
   {
      _id: "rs0",
      version: 1,
      members: [
         { _id: 0, host : "172.17.0.2:27017" },
         { _id: 1, host : "172.17.0.4:27017" }
      ]
   }
)


SQL Server AlwaysOn: Lost of heartbeat and connection with secondary replica

why heartbeat was lost?
why connection with secondary replica was lost ?

Depending on which instance of DHS.exe it could be Network Problems or a failure in the local SQL Server instance.

Any workaround ? increase lease timeout, performance of storage or RAM and CPU can help?

Double-check your cluster quorum so that the primary node does not need to communicate with any far-off resources to maintain cluster quorum.
You also have a storage problem that needs to be addressed, but if you use iSCSI or other network-based storage, that might have the same root cause.


Keys are for identification and data integrity. A key defines how tuples (rows) in a table can be uniquely identified. The integrity of keys is assured because the DBMS prevents users from entering duplicate information into the table. Database users can therefore rely on the keys to identify in the real world the things recorded in the database.

A "primary" key is fundamentally no different from any other candidate key in the same table. It's just a convention used to designate one key per table as significant in some way. Usually it is the "preferred" or "most important" key of a table and the one used in foreign key references.


Can I make my distribution database use In-Memory OLTP?

Is it possible to have all the distribution tables use In-Memory OLTP (formerly known as Hekaton) for enhancing performance of the distribution database?

This is a good question and made me thinking. But .... the answer is NO as of SQL Server 2016 CTP 3.0.

The documentation says :

  Replication tables on the distributor cannot be configured as memory-optimized tables.


count null values of table from information_schema tables

No, you cannot use information schema for this. Information_schema.TABLES only stores an (approximate) count of the number of rows in a table. Not counts of values or nulls.

You should have an index on the column and use the simple query instead:

-- if there isn't an index in place, add one:
alter table my_table
  add index (my_column_idx)
    (my_column) ;

select count(*)
from my_table
where my_column is null ;

  But can i add an index on a column if column_type = mediumtext?

Well, no.

  MySQL version 5.1.73 and table is MyISAM.

OK, now we have 3 problems.

First, you are in a very old, unsupported version of MySQL. Upgrade. At least to 5.5, if not to 5.6 or 5.7. This is not urgent of course and only partially related to the problem. Even at 5.7, we still can't have an index on a mediumtext column.

Second, you are using MyISAM? Still, in 2016? I guess it matches well with 5.1. Anyway, convert the tables and application to use InnoDB. That's not urgent or related to the issue either but you'll get benefits (transactions, foreign keys, etc). But no, even with InnoDB, we still can't have an index on a mediumtext column.

So, what to do? various ways - thrown not in any specific order:

Add a TINYINT computed (GENERATED) column on something like CASE WHEN column IS NOT NULL THEN 1 END and index it. Requires upgrade to 5.7
Add a TINYINT computed (VIRTUAL) column, as the previous suggestions, and index it. Requires replacing MySQL 5.1 with MariaDB 5.2 or 5.3 (or upgrading to 5.5 and then replacing with MariaDB 5.5 or 10+)
Add a TINYINT column (like the computed ones) but fill the values and keep them correct by your application or triggers. This does not require upgrading but enforcing integrity in the application is not recommended. Considering the use of MyISAM though, integrity does not seems a priority.
Banish the Nulls! Split the table into two, moving only the mediumtext column into the 2nd table (and the primary key). The 2nd table will only have rows for the non-null values of the column. Then you only need to count rows of that table. You can use the information schema for that! (or just count, MyISAM is fast in counting the whole table.) Does not require any upgrade but your applications will have to change to deal with the new, slightly altered table structure.
use a prefix index (thnx to @Michael-sqlbot for the suggestion). This does not require any upgrade or modifications at all:

-- a prefix index on the mediumtext column:
alter table my_table
  add index (my_column_1_idx)
    (my_column(1)) ;      -- one is enough

I haven't tested this but should work as it is. It may require to use WHERE LEFT(my_column, 1) IS NULL in your count query but you can test.


The best advice would be to rebuild the slave. Missing 8 binary logs means you may be missing a lot of transactions. Another option would be to execute CHANGE MASTER to and move to the next available binary log. You may need to skip slave errors along the way until the slave 'catches up' BUT you have to use pt-table-checksum and pt-table-sync to "resync" the data between these nodes.


How can I compare the schema of two databases?

If you cannot use one of the many tools out there because of connectivity problems and want an "offline" compare, you can use SSMS to generate scripts for all database objects by right clicking on the database and using the "Tasks.../Generate Scripts" function, and make sure you select to create one file per object.

When you have done that for both databases, get the two sets of scripts onto a local machine in two separate folders and use WinMerge (or similar) to compare the two.


Derive daily delta from total daily counts? Tsql

If this is for SQL Server 2012 or later version, you could use the LAG analytic function to produce the required result:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount, 1, 0) OVER (PARTITION BY tableName
                                                                     ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

The above query assumes that the first entry in a partition is indeed the very first addition of rows. (LAG(totalRowCount, 1, 0) means the absence of the previous totalRowCount will be treated as a zero.) If that cannot be assumed safely in every case and you would prefer to return NULL for first entries, you could use just LAG(totalRowCount) OVER ... instead:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount) OVER (PARTITION BY tableName
                                                               ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

If you insist on storing the results in a table, you can just add an INTO clause to the query. A possibly better idea, though, might be to save the query as a view.


Proper indexes or anything else to optimize

According to the MySQL docs, you can't avoid "using temporary" and "using filesort" when you have different ORDER BY and GROUP BY expressions. You need to rewrite the query--which isn't always possible--or live with the pain. (Or install faster hardware.)

About "using filesort",  MySQL docs say,

  In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

  (many lines snipped)

  You have different ORDER BY and GROUP BY expressions.

About "using temporary" MySQL docs say,

  To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

There are many different and related reasons you probably can't avoid "using temporary" and "using filesort". The docs are pretty good about explaining the reasons.


I need to create SYNONYM with name 'END'

END and REFERENCES are both reserved keywords.

So if you really want to use them as names (of tables, columns, schemata, synonyms, ...  whatever), you have to double-quote them, the same way as in Oracle:

CREATE SYNONYM "END" ... ;
DROP SYNONYM "END" ;

CREATE tab1
( name character varying(250),
  "REFERENCES" character varying(250)
) ;

The problem is that you have to always double-quote them everywhere and match the case. "END" is not the same as "End" nor "end" nor "eNd", etc.

It would be much easier to use non-reserved words for all identifiers.


How to set a filesystem size limit for DB2 database? Is it possible?

Yes.  Tablespaces in DB2 have a MAXSIZE attribute that you can use.  You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER TABLESPACE to set them later.

create database mydb
   automatic storage yes
   on C:\mydb
   user tablespace managed by automatic storage
      initialsize 20 M increasesize 10 percent maxsize 100 M

You could specify similar options for the system catalog tablespace and the default system temporary tablespace, but I would not recommend doing so.


Is DB Normalization done purely based on the primary key or is it done based on all the candidate keys?

Normalization is concerned with all Candidate Keys. A Primary Key is just a candidate key. Primary keys are no different to any other candidate key.

Potential confusion arises because in the early days of relational database theory the term Primary Key used to mean any and all candidate keys whereas modern usage is that Primary Key means only one key that is "preferred" or deemed to have some special significance for database users. Unfortunately many of the "definitions" you will see online or even in print are wrong as a result.


Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

For posterity:

SELECT
     execution_datetime
    ,string_formatted_date
    ,STR_TO_DATE(string_formatted_date,'%Y%m%d%H%i%s')
FROM
    (
        SELECT
              day(execution_datetime) * POWER(2,41)
            + hour(execution_datetime) * POWER(2,36)
            + minute(execution_datetime) * POWER(2,30)
            + second(execution_datetime) * POWER(2,24)
            + (CASE WHEN DATE_FORMAT(execution_datetime,'%f') >= 8388608 THEN 1 ELSE 0 END) * POWER(2,24) /*extra second*/
            + DATE_FORMAT(execution_datetime,'%f') as string_formatted_date
        FROM execution_history
    ) a

It appears that there is some math error in the original import that misses a second when the 23 bit is 1.

Answer was arrived at by using the MySQL doc provided above to reverse engineer the bits and put them back in the old-style datetime format.  From there, STR_TO_DATE nicely parses the decimal representation into a datetime.  Extra second error was found through trial and error, while obsessively staring at the data and cursing.  I would not recommend this approach.


Since you are using SQL Server Express, you will have to get creative.  You don't have any SQL Server native scheduling tool, you can utilize Task Scheduler, and schedule a daily task to run SQLCMD, with query text to backup your database.

I'd recommend creating a script that does some string manipulation to generate a day-unique filename for the BACKUP DATABASE T-SQL.

Your script could resemble something like this:

-- declare the backup filename (with path) without the file extension
declare @DestFile varchar(128) = 'C:\BackupDir\BackupFileName_';

-- get the current date as a nice and sortable string
declare @CurrentDate varchar(64) = '';
set @CurrentDate = replace(convert(varchar(10), getdate(), 111), '/', '');

set @DestFile += @CurrentDate + '.bak';

backup database YourDatabaseName
to disk = @DestFile;
go

Save that T-SQL to a script file (for example's sake, C:\YourScriptDir\BackupDatabase.sql).  To run this script using SQLCMD, you could do:

sqlcmd -S YourServerName\YourInstanceName -i C:\YourScriptDir\BackupDatabase.sql

Then just schedule that with Task Scheduler to run daily.  It's one giant spaghetti workaround, but that's what you get with a free version.

(the above process would be tailored to a database in Simple Recovery mode.  If you are in Full, then you need to consider transaction log backups as well)


Database design -- companies, employees, individuals

Read up on Table Inheritance.

Here's your data model in pseudo-syntax. : means inherits. Indentation means that thing is a property of the type.

AbstractParty
  BirthDate -- companies have "created" dates too
  Name

Individual : AbstractParty
  Gender

Organization : AbstractParty
  CompanyType

AbstractPartyRelationship
  FromAbstractParty
  ToAbstractParty

Employment : AbstractPartyRelationship

User
  Username
  Password
  Party (make this a one-to-one relationship)

AbstractAddress

PhoneNumber : AbstractAddress

EmailAddress : AbstractAddress

URL : AbstractAddress

MailingAddress : AbstractAddress

AbstractContactInfo
  AbstractParty
  AbstractAddress

PhoneContactInfo : AbstractContactInfo
  Extension

Picture

PartyPicture
  AbstractParty
  Picture

  An employee can't work for multiple companies.

That's a business rule to be enforced by a trigger, not your data model

  An employee can't be the same person as some individual.

Please elaborate.


Index options and applying partitioning to an index

Not all index create options are persisted as meta-data; these (e.g. SORT_IN_TEMPDB, ONLINE, etc.) are specified only as run-time DDL options.

Rather than drop and recreate the index entirely, an index can be partitioned by using CREATE INDEX...WITH (DROP_EXISTING=ON).

CREATE INDEX YourTable_Index ON dbo.YourTable(IndexColumn)
    WITH (DROP_EXISTING=ON) ON PS_YourTable(PartitioningColumn);

EDIT:

Be aware that SQL Server does not consider the existing index FILLFACTOR with CREATE INDEX...DROP_EXISTING=ON because a new index is created. Although, the existing index FILLFACTOR can be used with ALTER INDEX...REBUILD (by omitting the FILLFACTOR clause), that method does not allow changing partitioning or the filegroup whereas CREATE INDEX...DROP_EXISTING=ON does and avoids sorting the rows during the index recreate. The desired FILLFACTOR needs to be explicitly specified when using CREATE INDEX...DROP_EXISTING=ON when a non-default value is desired.

You'll need to generate the DDL dynamically if you want to retain the old index FILLFACTOR (and other persisted options of the original index) without manually specifying the value.


how to save an image column in MSSQL to file system?

The easiest way is to use SSIS: the Export Column component does
what you are looking for. Here is an example: Export images from a
SQL Server Table to a Folder with SSIS

This is not only the easiest way, it requires minimal permissions:
only SELECT permission on the table and access to file folder.
Also you can write a powershell script: Export SQL Server BLOB
Data with PowerShell

The user should be able not only access a table (SELECT
permission) but the rights to execute powershell scripts.
It can be done using bcp but only for 1 file at a time, so for
multiple files you need to write a cursor and use xp_cmdshell. Use of
xp_cmdshell is not a best practice, it should be enabled on your
server and you should have elevated permissions to be able to use
xp_cmdshell.
In case of bcp you should use format file, otherwise your files will contain additional 8 bytes and have a wrong format.


ASPECT #1 : InnoDB

What immediately catches my eye is the fact that the table has no explicit PRIMARY KEY. This means that an internal rowID Clustered Index (gen_clust_index) is being used.

With each UPDATE command, you may encounter moments when the row is actually locked in conjunction with the Clustered Index. Consequently, the data page gets locked.

Here is a question: Is it possible for the PRIMARY KEY (or rowID) of two different rows to deadlock? Surprisingly, the answer is Yes. Rather than explain why, I will refer you to three questions asked by one person: RedBlueThing.

I managed to answer his three questions about deadlocking pages:

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

It is possible that two or more rows occupy the same data page. In fact, the rowID-based PRIMARY KEY coexists with the data on the same data page. While crisscrossing rows, perhaps some miscellaneous garbage collection (data structures within the InnoDB Buffer Pool that are released and reallocated) may be happening.

ASPECT #2 : Your C# Code

Inside the loop, you are also doing garbage collection and reallocation for the DB Connector. That needs to be reorganized, perhaps placing it outside the loop.

SUGGESTION

Try setting up the test using the transaction paradigm with BeginExecuteNonQuery()

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.BeginExecuteNonQuery();
for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}
cmd.EndExecuteNonQuery();

Give it a Try !!!


When databases are upgraded either using backup restore method or via attach/detach process, the compatibility level of the databases do not get changed automatically, and show as old comp. level. However this is not a major problem, but point to note is that you may not be able to use various features , that are available on newly upgraded SQL server.

As mentioned by owen, you should refer the MSDN doc to refer the difference between the Compatibility-level setting of 100 or lower *with*
Compatibility-level setting of at least 110 .

Also,  running an upgrade advisor  will help you in identifying for any issues while you do that upgrade or change in compatibility level.

If you have the test environment you can always test the scenario before making any changes in prod and once you are good to go, just make sure you have the latest backup of system databases on production server. In case anything goes wrong, you can recover or rollback from the backups.

Also, an additional read here Bad habits : Clinging to old compatibility levels


Here's the pre-Extended Events way to do this.

Instead of using Profiler interactively, use it to generate a Server Side SQL Trace that writes to a file.  After running the trace for a bit, you can query the trace files and/or load the trace files into a table and query those.  Find a session that has the rollback, and then query for all the events for that session, in order.


How to enable NULL values in MySQL timestamp fields?

ALTER TABLE table_name
  MODIFY dt TIMESTAMP NULL;

This will alter the table with name table_name to set the column dt to nullable.


Populate ID Field by Converting Group Name Values

I would use the CHECKSUM function to determine a numeric value for each SystemAccessLevelDesc.  You could make this a persisted, computed column, such that the value is automatically calculated when data is inserted into the column.
From the CHECKSUM page on Microsoft's documentation site:

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.
CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For this definition, null values of a specified type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead.

There is a possibility, however extremely small, that CHECKSUM can return the same value for two different inputs.  I've created a UNIQUE constraint on the table such that an insert where the checksum results in two identical values will fail.  If you have a relatively small number of values for the SystemAccessLevelDesc column, I would not expect you to experience any checksum collisions.
Sometimes an example is helpful:
USE tempdb;

CREATE TABLE dbo.SystemRights
(
    SystemID int NOT NULL
    , SystemAccessLevelID AS CHECKSUM(SystemAccessLevelDesc) PERSISTED
        CONSTRAINT UQ_SystemAccessLevelID
        UNIQUE
    , SystemAccessLevelDesc nvarchar(100) NOT NULL
    , CONSTRAINT PK_SystemRights
        PRIMARY KEY CLUSTERED
        (SystemId, SystemAccessLevelID)
);

INSERT INTO dbo.SystemRights (SystemID, SystemAccessLevelDesc)
VALUES (1, 'SYSADMIN')
    , (1, 'SYSACCESS')
    , (1, 'SYSREAD');

SELECT *
FROM dbo.SystemRights;

The results from the SELECT above:
╔══════════╦═════════════════════╦═══════════════════════╗
║ SystemID ║ SystemAccessLevelID ║ SystemAccessLevelDesc ║
╠══════════╬═════════════════════╬═══════════════════════╣
║ 1        ║ -32772874           ║ SYSACCESS             ║
║ 1        ║ 1727577266          ║ SYSADMIN              ║
║ 1        ║ 2008724622          ║ SYSREAD               ║
╚══════════╩═════════════════════╩═══════════════════════╝

If you are concerned about checksum collisions, and are not worried about space implications, you could use the HASHBYTES function to generate a 32-byte binary value instead of the int value returned by CHECKSUM:
IF OBJECT_ID(N'dbo.SystemRights', N'U') IS NOT NULL
DROP TABLE dbo.SystemRights;

CREATE TABLE dbo.SystemRights
(
    SystemID int NOT NULL
    , SystemAccessLevelID AS CONVERT(binary(32), HASHBYTES('SHA2_256', SystemAccessLevelDesc)) PERSISTED
        CONSTRAINT UQ_SystemAccessLevelID
        UNIQUE
    , SystemAccessLevelDesc nvarchar(100) NOT NULL
    , CONSTRAINT PK_SystemRights
        PRIMARY KEY CLUSTERED
        (SystemId, SystemAccessLevelID)
);

INSERT INTO dbo.SystemRights (SystemID, SystemAccessLevelDesc)
VALUES (1, 'SYSADMIN')
    , (1, 'SYSACCESS')
    , (1, 'SYSREAD');

SELECT *
FROM dbo.SystemRights;

The output:
╔══════════╦════════════════════════════════════════════════════════════════════╦═══════════════════════╗
║ SystemID ║ SystemAccessLevelID                                                ║ SystemAccessLevelDesc ║
╠══════════╬════════════════════════════════════════════════════════════════════╬═══════════════════════╣
║ 1        ║ 0x09B006A24922040C18013ED244C62245B8DEA81A0AB3829FEF2471299138BEC5 ║ SYSACCESS             ║
║ 1        ║ 0x98AC053FBB8875188E9DCB3F77FDB6245CA9A42B8BECFAA204F2FA3054DACB7A ║ SYSREAD               ║
║ 1        ║ 0xDC61780D08D34D244226A6B414FE382E138819B40362B624888D775D31505C1A ║ SYSADMIN              ║
╚══════════╩════════════════════════════════════════════════════════════════════╩═══════════════════════╝


Getting error while importing large data through csv

It really depends which constraints are violated. You neglected to disclose that in your question.
To disable triggers (including foreign key constraints):
ALTER TABLE tbl DISABLE TRIGGER ALL;

Be sure to revert it afterwards:
ALTER TABLE tbl ENABLE TRIGGER ALL;

More in the manual.
However, this does not disable Check, Not-Null, Unique, Primary Key or Exclusion constraints, which are implemented independently.
You could drop and recreate those. But this is rarely useful, constraints are in place for a reason.
Alternative
An alternative would be to import your CSV file into a temporary copy of the target table and repair any data that would violate constraints before inserting from there. Depends on the size of the file and available RAM. You could also make this a regular table for huge files or longer processing.
CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
COPY tmp FROM '/path/tp/file.csv';

For instance, to remove duplicates from imported data as well as between import and existing data:
INSERT INTO target_table
SELECT DISTINCT *
FROM tmp
LEFT JOIN target_table t0 ON ...
WHERE t0.target_table_id IS NULL;


ON creating BITMAP INDEX on a table in mysql throws error

You are looking at a feature request. See here for this table

Storage Engine  Permissible Index Types
InnoDB  BTREE
MyISAM  BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE (see note in text)

They are not even available in 5.7 - they would be a nice to have, but not yet - maybe in the future. Check this out - pages 32 and 33 - not even the columnar stores (InfiniDB and Inforbright) have them yet - AFAICS.


You have maintenance plans. Try to follow these steps:

Find the maintenance plan name and id that you want to delete.
Write down the id of the one you want to delete.

 SELECT name, id FROM msdb.dbo.sysmaintplan_plans

--Place the id of the maintenance plan you want to delete into the below query to delete the entry from the log table:

    DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ''

Place the id of the maintenance plan you want to delete into the below query and delete the entry from subplans table:

    DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ''

Place the id of the maintenance plan you want to delete into the below query to delete the entry from the plans table:

    DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ''

Now you can delete the jobs from Management Studio.


There are many ways  by which you can transfer data and all depends on this part of question..

  copy them regularly to a SQL Azure database

1.If you want once a day,you can write ssis package and do the logic of checking/merging data in that

2.You also can write C# program which does this for two tables and run this through SQLServer agent..

3.if you are looking for near realtime data transfer,i would go with Transactional replication.This is supported now..

Setting up is very easy task and same as Onpremises ,but with one exception of no pull subsribers .

This article from Joe Sack will help you on how to set up..

There are also some limitations which you need to account for..

The publisher and distributor must be at least at one of the following versions:

SQL Server 2016

SQL Server 2014 SP1 CU3

SQL Server 2014 RTM CU10

SQL Server 2012 SP2 CU8

You can read this link for more details..


Can COALESCE be used with PERFORM/EXECUTE in PostgreSQL?

EXECUTE is a PL/pgSQL statement, not an SQL statement, so you cannot use it as subquery inside an SQL statement.
You could write a function status_calc_by_time, then your code would work if you simply remove the EXECUTE.
If you write the function in LANGUAGE sql and make it VOLATILE, it might get inlined by the database, so you have no performance loss at all.


Insert triggers to eliminate sensitive information

Yes this will prevent the information showing up in the log. The transaction log only needs to record sufficient information to recover the row that was actually written to the data page. You can see this from using sys.fn_dblog as below.

CREATE TABLE dbo.T
  (
     X      int CONSTRAINT PK PRIMARY KEY,
     String varchar(50) NULL
  )

GO

CHECKPOINT;

INSERT INTO dbo.T
VALUES      (1,
             'Private and Confidential')

/*Without INSTEAD OF trigger this phrase found in log record*/
SELECT CASE
         WHEN [Log Record] LIKE '%Private and Confidential%' THEN 1
         ELSE 0
       END,
       [RowLog Contents 0]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Operation = 'LOP_INSERT_ROWS'
       AND AllocUnitName = 'dbo.T.PK'

GO

CREATE TRIGGER TR
ON dbo.T
INSTEAD OF INSERT
AS
  BEGIN
      SET NOCOUNT ON;

      INSERT INTO dbo.T
                  (X)
      SELECT X
      FROM   INSERTED
  END

go

CHECKPOINT;

INSERT INTO dbo.T
VALUES      (2,
             'Private and Confidential')

GO

/*With INSTEAD OF trigger this phrase no longer found in log record*/
SELECT CASE
         WHEN [Log Record] LIKE '%Private and Confidential%' THEN 1
         ELSE 0
       END,
       [RowLog Contents 0]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Operation = 'LOP_INSERT_ROWS'
       AND AllocUnitName = 'dbo.T.PK'

The construction of the INSERTED pseudo table takes place in tempdb so this information may appear in the data files there in passing however.


If you're using MS SQL Server you're much better off downloading a tool like this instead of trying to figure out how to go through the entire transaction log and use those functions.  It has a 14 day free trial and should make it a lot more readable for you.

If you end up having to do that check out this thread on DBCC LOG:


Mysql 1 way replication master lost data replication still has

There is no mechanism in MySQL for a master to recapture "lost" (missing, corrupt) data from a slave.  Unless replication is circular (or master/master, where each machine is both master of, and slave to, the other... don't try this at home) then the interaction between master and slave is strictly one-way.  Even in a circular configuration, there's no "recovery" or resynchronization mechanism.

There is also no mechanism for data to be hidden away and reappear.  The comment referencing InnoDB recovering data from log files seems to overlook the fact that this sort of recovery occurs at start-up, and until it is complete, the server doesn't start accepting connections.

The answer should be discoverable in the master's binary logs (you archive those, right?) and the daily backups from master and slave (you archive those, too, right?).

Absent frequent periodic backups and historical binlogs, it seems difficult to speculate further.


SQL Server on azure virtual machines default instance

Is there a way to provision a SQL Server virtual machine on azure with a named instance or does it always install the default?
Not with the pre-built images.
If you provision your own VM and use IaaS agent extension you can use named instance.
Can I install a second instance of SQL Server on the same VM? Can I change installed features of the default instance?

Yes. The SQL Server installation media is located in a folder on the C
drive. Run Setup.exe from that location to add new SQL Server
instances or to change other installed features of SQL Server on the
machine. Note that some features, such as Automated Backup, Automated
Patching, and Azure Key Vault Integration, only operate against the
default instance, or a named instance that was configured properly.

Can I use a named instance of SQL Server with the IaaS extension?

Yes, if the named instance is the only instance on the SQL Server, and
if the original default instance was uninstalled properly. If there is
no default instance and there are multiple named instances on a single
SQL Server VM, the SQL Server IaaS agent extension will fail to
install.

Ref:

Frequently asked questions for SQL Server on Azure VMs
Automate management tasks on Azure virtual machines by using the SQL Server IaaS Agent Extension


In some cases Query Store can cause writes to occur as an effect of a select statement, and in the same session.

This can be reproduced as follows:

USE master;
GO
CREATE DATABASE [Foo];
ALTER DATABASE [Foo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  INTERVAL_LENGTH_MINUTES = 60,
  MAX_STORAGE_SIZE_MB = 100,
  QUERY_CAPTURE_MODE = ALL,
  SIZE_BASED_CLEANUP_MODE = AUTO);
USE Foo;
CREATE TABLE Test (a int, b nvarchar(max));
INSERT INTO Test SELECT 1, 'string';

Create an Extended Events session for monitoring:

CREATE EVENT SESSION [Foo] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\temp\FooActivity2016.xel',max_file_size=(11),max_rollover_files=(999999))
WITH (MAX_MEMORY=32768 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

Next run the following:

WHILE @@TRANCOUNT > 0 COMMIT
SET IMPLICIT_TRANSACTIONS ON;
SET NOCOUNT ON;
GO
DECLARE @b nvarchar(max);
SELECT @b = b FROM dbo.Test WHERE a = 1;
WAITFOR DELAY '00:00:01.000';
GO 86400

An implicit transaction may or may not be necessary to reproduce this.

By default, at the top of the next hour Query Store's statistics collection job will write out data. This appears to (sometimes?) occur as part of the first user query executed during the hour. The Extended Events session will show something similar to the following:

The transaction log shows the writes that have occurred:

USE Foo;
SELECT [Transaction ID], [Begin Time], SPID, Operation,
  [Description], [Page ID], [Slot ID], [Parent Transaction ID]
FROM sys.fn_dblog(null,null)
/* Adjust based on contents of your transaction log */
WHERE [Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
OR [Parent Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
ORDER BY [Current LSN];

Inspecting the page with DBCC PAGE shows that the writes are to sys.plan_persist_runtime_stats_interval.

USE Foo;
DBCC TRACEON(3604);
DBCC PAGE(5,1,344,1); SELECT
OBJECT_NAME(229575856);

Note that the log entries show three nested transactions but only two commit records. In a similar situation in production, this led to an arguably faulty client library that used implicit transactions unexpectedly starting a write transaction, preventing the transaction log from clearing. The library was written to only issue a commit after running an update, insert, or delete statement, so it never issued a commit command and left a write transaction open.


I would highly recommend that you test your new T:\ drive using Crystal Disk Mark.  Check out the guide from Brent Ozar here:

How to Test Your Storage with CrystalDiskMark

Compare the results from the T:\ drive with

the old RAID 5 disk (where tempdb used to be)
your machine

If the SSD is slower than those other two devices, and nothing else has changed* in your setup, it's likely there is a problem with the disk itself, or with the driver being used, or the controller for the array this disk sits in, etc.

*things that might have changed since you moved tempdb:

the number of tempdb files for the database increased or decreased (someone said "hey, why not, since we have to restart the database to move tempdb anyway")
maintenance tasks were rescheduled to coincide with the now-slow nightly job (especially those that have potential to hit tempdb hard, like index rebuilds or checkdb)
the maintenance window to move tempdb was also used to deploy new code (for the nightly job, perhaps) that makes heavier use of temp tables, or has queries with bad spills, etc

Next Steps

Since it seems like the disk is reasonably fast (per the benchmarks you shared), I think it would be a good idea to log the contents of sys.dm_io_virtual_file_stats before and after the nightly batch job you mentioned.  This will tell you how much I/O is happening on tempdb during that process.  This is important, because maybe there really is more I/O than the disk can handle.  So here's what you do:

Run this query right before your nightly batch job is scheduled to run:

select *
from sys.dm_io_virtual_file_stats((select DB_ID('tempdb')), default);

Save the results somewhere (like Excel or something - probably not in tempdb :P)
Wait 7 hours (until the job finishes)
Run the same query and save the results
Edit your question to include the results

We can then take the difference of the two snapshots and determine how many bytes were read / written during the job.  You can also use those numbers to calculate overall latency during that period.

Note: a more granular approach would be to log the results of that query to a table every 5 minutes (or less if you want)


Cons of using a star schema in a columnar database

You are mixing up three orthogonal concepts: data model (star schema), workload characteristics (OLTP vs. OLAP), and physical data organisation (columnar).

Your data model has no bearing on whether column-organised tables are appropriate for you; however, data organisation must reflect the nature of your workload (i.e. queries). For example, SELECT * FROM... cannot possibly perform well in a columnar database.


Help with query: Get MAX value of rows that previous row has certain value

Make use of LAG analytic function

SELECT xID, MAX(xDATE),xValue FROM
(
SELECT t.*,
       CASE
            WHEN xvalue = 1 AND LAG(xvalue) OVER(
                 PARTITION BY xid
                 ORDER BY xdate
            ) = 0 THEN 1
            ELSE 0
       END
  AS cond
FROM test t
) s WHERE s.cond = 1
GROUP BY xID,xValue

Demo


Copy SSRS Subscriptions from one SSRS server to another

you are using this PowerShell module from the gallery.
You need to use Copy-RsSubscription if the subscription on the destination does not already exist. Set-RsSubscription updates only already created subscriptions.
Get-RsSubscription -ReportServerUri 'http://sql2017/reportserver' -RsItem '/Cooperative Technologies/1035/1035YellowPages/Billing Reports/LookupActivityReport' | Export-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"
Import-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"  -ReportServerUri 'http://sql2017/reportserver'  | Copy-RsSubscription -ReportServerUri 'http://sql2017/reportserver' -RsItem '/Cooperative Technologies/1035/1035YellowPages/Billing Reports/LookupActivityReport'


There is no task scheduling functionality built into PostgreSQL.

You can use an extension like pg_cron, but I don't know how easy it is to build that on Windows.

The simplest way would probably to use the Windows task scheduler to start your job regularly.


Completely uninstall Oracle server on Linux

Oracle provides extensive documentation about how its database software can be uninstalled:

Removing Oracle Database Software - 11.2 - this is the new method for uninstallation, using the deinstall tool. This includes instructions on how to download the tool if your install failed and you can't use it directly from there.
Removing Oracle Database Software - 11.1 - this has step-by-step, part by part clean removal instructions for the various parts of the product you could have installed. These methods still work on 11gR2.

If there's anything of any value in the databases on that machine, a full backup is a must. Ideally make that data available on other database(s) on a separate machine before you start uninstalling anything – that way you're sure you won't actually be losing data even if you re-image the OS. (Re-installing the OS is sometimes the most efficient removal tool if you have good procedures/tooling for that.)

But if you're just having issues starting a database, a re-install isn't usually the right thing to do. Fixing those errors is usually much more productive – you'll actually learn something about how the software works along the way, and that's valuable.
To get help with that, if your searches in the Web and the Oracle support site have not helped so far, you could post a question here that includes:

The exact error message you get when you try starting up your database (or whatever action it is you're currently stuck with)
The relevant logs from your alert.log file (located usually in $ORACLE_BASE/diag/rdbms/<lowercase db name>/<SID/trace/alert_<SID>.log)
What changes you made since the last time the database worked
Anything else that you have noticed looks off

The more drastic option for uninstallation is simply to stop all services, and remove (delete) all the files that were installed. Be very careful if you go that route, as usual - don't nuke unrelated files. And make sure you have backups you know you can restore if there's anything useful in the database(s) you have installed.
The main directories/files that need cleaning up are:

$ORACLE_BASE and anything under it
$ORACLE_HOME and anything under it (you might have several)
/etc/ora* - this is the last thing to delete. /etc/oratab contains a list of your Oracle homes, so use it as a reference.
Scan your system for files belonging to the Oracle owner and dba group and remove what is now unnecessary.


Unable to connect using Azure AD Service Principal on SQL Server

Service principal is not supported in SSMS as well as in other SQL tools. Only application interface is supported for this type of AAD services.Your C# program works and this is the right way to proceed with testing.


Server role with permission only to create server login, not alter one

CREATE and ALTER are tied together into ALTER ANY. What is the actual goal? If you're going to give a person the rights to create logins, why block from alter? Whatever you think they might try to do, they could just create an additional login instead of changing an existing one, or drop/create the original.

You could possibly block ALTER LOGIN commands via a DDL trigger depending on the caller, but that is quite different from permissions.


How to get efficient range index scans with multiple columns with different sort order?

This is a generic (and slightly academic) answer that shows how this problem can be solved in general by defining your own type and operator class.

I'll use strings (type text) as an example.

First, we have to define our own type invtext exactly like text:

/* create a shell type */
CREATE TYPE invtext;

/* copy type functions from "text" */
CREATE FUNCTION invtextin(cstring) RETURNS invtext
   LANGUAGE internal IMMUTABLE PARALLEL SAFE
   AS 'textin';

CREATE FUNCTION invtextout(invtext) RETURNS cstring
   LANGUAGE internal IMMUTABLE PARALLEL SAFE
   AS 'textout';

CREATE FUNCTION invtextrecv(internal) RETURNS invtext
   LANGUAGE internal STABLE PARALLEL SAFE
   AS 'textrecv';

CREATE FUNCTION invtextsend(invtext) RETURNS bytea
   LANGUAGE internal STABLE PARALLEL SAFE
   AS 'textsend';

/* create type like "text" */
CREATE TYPE invtext (
    INPUT = invtextin,
    OUTPUT = invtextout,
    RECEIVE = invtextrecv,
    SEND = invtextsend,
    LIKE = text,
    CATEGORY = 'S',
    DELIMITER = ',',
    COLLATABLE = TRUE
);

We need casts to and from text:

CREATE CAST (text AS invtext) WITHOUT FUNCTION;
CREATE CAST (invtext AS text) WITHOUT FUNCTION AS IMPLICIT;

We use the same comparison functions as text, except we turn around the order:

CREATE FUNCTION invtexteq(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE LEAKPROOF STRICT
   AS 'texteq';

CREATE FUNCTION invtextne(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE LEAKPROOF STRICT
   AS 'textne';

CREATE FUNCTION invtext_lt(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_gt';

CREATE FUNCTION invtext_gt(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_lt';

CREATE FUNCTION invtext_le(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_ge';

CREATE FUNCTION invtext_ge(invtext, invtext) RETURNS boolean
   LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
   AS 'text_le';

Now we can define the corresponding operators:

CREATE OPERATOR = (
   FUNCTION = invtexteq,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "=",
   NEGATOR = "<>",
   RESTRICT = eqsel,
   JOIN = eqjoinsel,
   HASHES,
   MERGES
);

CREATE OPERATOR <> (
   FUNCTION = invtextne,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "<>",
   NEGATOR = "=",
   RESTRICT = neqsel,
   JOIN = neqjoinsel
);

CREATE OPERATOR < (
   FUNCTION = invtext_lt,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = ">",
   NEGATOR = ">=",
   RESTRICT = scalargtsel,
   JOIN = scalargtjoinsel
);

CREATE OPERATOR > (
   FUNCTION = invtext_gt,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "<",
   NEGATOR = "<=",
   RESTRICT = scalarltsel,
   JOIN = scalarltjoinsel
);

CREATE OPERATOR <= (
   FUNCTION = invtext_le,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = ">=",
   NEGATOR = ">",
   RESTRICT = scalargesel,
   JOIN = scalargejoinsel
);

CREATE OPERATOR >= (
   FUNCTION = invtext_ge,
   LEFTARG = invtext,
   RIGHTARG = invtext,
   COMMUTATOR = "<=",
   NEGATOR = "<",
   RESTRICT = scalarlesel,
   JOIN = scalarlejoinsel
);

The required B-tree support function is just the opposite of the one from text:

CREATE FUNCTION btinvtextcmp(invtext, invtext) RETURNS integer
   LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
   AS 'SELECT bttextcmp($2, $1)';

Now we can define a default operator class for invtext:

CREATE OPERATOR CLASS invtext_ops
   DEFAULT FOR TYPE invtext USING btree FAMILY text_ops AS
      OPERATOR 1 <,
      OPERATOR 2 <=,
      OPERATOR 3 =,
      OPERATOR 4 >=,
      OPERATOR 5 >,
      FUNCTION 1 btinvtextcmp(invtext, invtext);

Done! Let's test how it works.

Let's create a test table:

CREATE TABLE stuff(
    foo text,
    bar text
);

INSERT INTO stuff(foo, bar) SELECT
    substr(md5((random()*1000000)::text), 4, 4),
    substr(md5((random()*1000000)::text), 4, 4)
FROM generate_series(1, 1000000);

CREATE INDEX index1 ON stuff(foo ASC, (bar::invtext) ASC);

VACUUM ANALYZE stuff;

Sort foo in ascending order and bar in descending order (which is ascending order for invtext):

SELECT * FROM stuff
WHERE (foo, (bar::invtext)) > ('f000', '8000')
ORDER BY foo ASC, (bar::invtext) ASC
LIMIT 100;

 foo  | bar
------+------
 f000 | 75ab
 f000 | 6d17
 f000 | 6c78
 f000 | 51ed
 f000 | 2d84
 f000 | 2227
 f000 | 0f72
 f001 | f0bf
 f001 | eae8
 f001 | d56a
 f001 | c6dd
 f001 | ba4e
 f001 | 9606
 f001 | 8e2a
 f001 | 7d58
 f001 | 7aed
 f001 | 719c
 f001 | 5ce9
 f001 | 354a
 f001 | 137d
 f001 | 107d
 f002 | ee18
 f002 | e7a8
...

Test if the index is used:

EXPLAIN (COSTS off)
SELECT * FROM stuff
WHERE (foo, (bar::invtext)) > ('f000', '8000')
ORDER BY foo ASC, (bar::invtext) ASC
LIMIT 100;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Limit
   ->  Index Scan using index1 on stuff
         Index Cond: (ROW(foo, (bar)::invtext) > ROW('f'::text, '1'::invtext))
(3 rows)


SQL Server 2014 Database vs. Schema Permissions

I'd like to grant create table to the test schema for a windows group;
  but not have them be able to create a table in the dbo schema.

The correct grants are:

GRANT ALTER ON SCHEMA::test TO [ad\windows_group];

GRANT CREATE TABLE TO [ad\windows_group];

  but not have them be able to create a table in the dbo schema.

They would not be able to do that without ALTER permissions on dbo.

  I think I'm missing something.

Somehow you've granted that user ALTER on the dbo schema or the database.


Do non-PostgreSQL database softwares use roughly the same "structure" for communicating with them?

Most client/servers DBMSes have parameters similar to those, and the handle database indeed is an unknown concept elsewhere. But they might need other parameters; the common way to handle is with a connection string (or URL) that encodes all those parameters, or with a generic list of keys/values.
And for databases that do not use a client/server architecture, it's even more different. If you do not need much concurrency and want to avoid having to install the DB server, you would be likely to choose SQLite, and then the only connection parameter is the database file name.


Here is a solution that uses CROSS APPLY to find the value for the latest quarter that ends on or before the daily date. If your table is indexed by (ID, Date), this query will be quite efficient with a one-row seek to look up the quarterly value for each daily date.

This solution also does not require a calendar table and makes no assumption about the duration of a quarter. However, it does make an assumption that you have no gaps in your quarterly table. For example, if you were missing a year's worth of quarters, the solution would identify the "most recent quarter" as the quarter that happened a year ago. This may be perfectly fine, but you should at least be aware of the assumption.

CREATE TABLE #DailyTable (ID INT, DailyTableDate DATE, Value INT)
CREATE TABLE #QuarterlyTable (ID INT, QuarterlyTableDate DATE, Value INT)
ALTER TABLE #QuarterlyTable ADD UNIQUE CLUSTERED (ID, QuarterlyTableDate)

INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-01-01',10)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-02-01',15)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-03-01',20)
INSERT INTO #DailyTable (ID, DailyTableDate, VALUE) VALUES (1,'2010-04-01',30)

INSERT INTO #QuarterlyTable (ID, QuarterlyTableDate, VALUE) VALUES (1,'2010-01-01',1000)
INSERT INTO #QuarterlyTable (ID, QuarterlyTableDate, VALUE) VALUES (1,'2010-04-01',2000)

SELECT d.ID,
    d.DailyTableDate AS Result_Date,
    d.Value,
    qt.Value AS Most_Recent_Quarterly_Values
FROM #DailyTable AS d
CROSS APPLY (
    -- Find the value for latest quarter that ended on or before the daily date
    SELECT TOP 1 q.Value
    FROM #QuarterlyTable q
    WHERE q.ID = d.ID
        AND q.QuarterlyTableDate <= d.DailyTableDate
    ORDER BY q.QuarterlyTableDate DESC
) qt


I need to reduce the size of Database file -- I am confused between DBCC Shrinkfile and Data compression options

DBCC SHRINKFILE will release unused but allocated space. From books online:
DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
The data compression feature in SQL Server helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.  Compression will not release allocated space. You can do so by shrinking the file once you compress.
Ref: https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
See the answer to this question here asked earlier.


non cumulative differential backup for SQL Server

Transaction log backups are incremental, that is, they only store incremental changes since the previous transaction log backup.
And they do it more or less as you told, using last well know backuped LSN (a checkpoint).
This is why you have to restore the full backup and then all the TLOG backups in the proper order.
But this requires full recovery model. Differential backups work in all recovery models, namely Simple, Bulk-Logged, and Full.
If you want incremental backups (i.e. non-cumulative differential backup), take transaction log backups regularly.


Postgres 11 JSONB - How to get all distinct values of a field across all elements

Use jsonb_array_elements_text:
SELECT DISTINCT a.e ->> 'stopType' AS "stopType"
FROM shipments AS s
   CROSS JOIN LATERAL jsonb_array_elements_text(s.metadata->'stops') AS a(e);


Check if any table has a user defined type

How can I check if there is any table (1000+) on my db that are
  currently using any of these user-defined table types before dropping
  them.

First things first

  A table type is a type from which table variables or table-valued
  parameters could be declared

Source

This means that table types can be used by table variables / TVP's and these can be used in parameter calls. An example of this is a stored procedure calling a TVP.

Table types are not permanent tables or used by permanent tables.

sys.parameter_type_usages

You can use the sys.parameter_type_usages dmv to get the parameters that are mapped to user defined types, these include Table Valued Parameters.

  Returns one row for each parameter that is of user-defined type.

Source

SELECT OBJECT_NAME(ptu.object_id),tt.name
FROM sys.parameter_type_usages ptu
INNER JOIN sys.table_types tt
on ptu.user_type_id = tt.user_type_id;

Testing

CREATE TYPE LocationTableType
   AS TABLE
      ( LocationName VARCHAR(50)
      , CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
   @TVP LocationTableType READONLY
      AS
      SET NOCOUNT ON
      INSERT INTO AdventureWorks.Production.Location
         (
            Name
            , CostRate
            , Availability
            , ModifiedDate
         )
      SELECT *, 0, GETDATE()
      FROM @TVP;
GO

test statements from here

Running the query at the top gives us both the procedure & the table type used

(No column name)    name
usp_InsertProductionLocation    LocationTableType

The call to the procedure simply uses the table type and a self defined table variable:

DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT Name, 0.00
   FROM AdventureWorks.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Statements also from here


Order by query optimization for multi select data (enum)

Below is an example of how you can return the ordered list using your new structure.  What is missing from the database design is an ID table with one row per ID.  This would then provide a one-to-many relationship in 3NF, and avoid the need to select the distinct ID values from the many side.  You could use that parent table instead of the derived table in this example, which would be more efficient.

CREATE TABLE dbo.Alphabets(
    ID int NOT NULL
    ,Alphabets char(1) NOT NULL
    ,CONSTRAINT PK_Alphabets PRIMARY KEY (ID, Alphabets)
    );

INSERT INTO dbo.Alphabets VALUES
     (1, 'A')
    ,(1, 'B')
    ,(1, 'C')
    ,(2, 'B')
    ,(2, 'D')
    ,(3, 'A')
    ,(3, 'C')
    ,(3, 'D')
    ,(4, 'A')
    ,(4, 'C')
    ,(5, 'A')
    ,(5, 'B')
    ,(5, 'D');

SELECT  ID
        , ( SELECT    '|' + Alphabets
            FROM      dbo.Alphabets AS b
            WHERE     b.ID = a.ID
            ORDER BY  b.Alphabets
        FOR
            XML PATH('')
        ) + '|' AS Alphabets
FROM    ( SELECT DISTINCT
                    ID
            FROM      dbo.Alphabets
        ) AS a
ORDER BY Alphabets DESC;


HAVING is for filtering on aggregated results. You could use it to solve your problem if you rewrite it slightly so it's filtering on the number of times a DISC_NAME has a result from your joins which has GRADE=1. For this aggregate, you'd be grouping by the DISC_NAME and you can count matches with a case when
select d.disc_name
from students s
    ,disciplines d
    ,grades g
where g.id= s.id
and   g.kod_disc = d.disc_id
group by d.disc_name
having sum(case when g.grade = 2 then 1 else 0 end) = 0

(assuming the grade column comes from grades and disc_name is part of disciplines)
You could also achieve the same with an anti-join query. Here I've removed the join to students because you don't seem to use it (other than making sure the grade row belongs to a student row, which I would assume isn't necessary). I've also switched to using more modern ansi join syntax - you could use either.
select d.disc_name
from disciplines d
where not exists (select null
                  from   grades g
                  where  d.disc_id = g.disc_id
                  and    g.grade = 2
                 )


Display multiple IP address values for one system in a single row

If you're okay with a single column showing the IP addresses as a comma-separated list:

SELECT SYS.Netbios_Name0 AS [Computer Name],
       SYS.User_Name0 AS [Last Login User],
       SYS.User_Domain0 AS [User Domain],
       Sys.Resource_Domain_OR_Workgr0 AS [System Domain],
       v_GS_Computer_System. Manufacturer0 AS [Manufacturer Name],
       v_GS_COMPUTER_SYSTEM.Model0 AS [Model Name],
       v_GS_COMPUTER_SYSTEM.SystemType0 AS [System Type],
       v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [Bios Version],
       v_GS_PC_BIOS.SerialNumber0 AS [Service Tag],
       v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Active Processors],
       [IP Addresses] = STUFF((SELECT ',' + rasi.IP_Addresses0
         FROM dbo.v_RA_System_IPAddresses AS rasi
         WHERE rasi.ResourceID = SYS.ResourceID
         FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
FROM v_R_SYSTEM SYS
LEFT JOIN  v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
LEFT JOIN v_GS_PC_BIOS on  SYS.ResourceID = v_GS_PC_BIOS.ResourceID;

(If you're still getting duplicates here, it's because of the other joins, not the IP addresses.)

If you need each IP address represented under its own column, you'll need to figure out the highest number of IP addresses that exist for any given system, and build a PIVOT statement using dynamic SQL. Not something I can invest in at 5:00 PM and when I don't even know you need it.


How to tackle a large undocumented database

The linked answer tackles the problem bottom-up, database first. As your responsibilities encompass the applications and database, I'd be inclined to attack this top-down starting at the application(s).

Focus your attention on understanding the most frequently used features of the application through consultation with the user base. Trace the database interactions of those features through profiling/logging tools so you can identify the key tables and procedures.

This way your early efforts are constrained to the "stuff that matters", rather than wasting time documenting tables and queries that may be rarely or never used. The focus should also bring the Pareto Principle to bare on your bug fixing efforts (so says Microsoft anyway).


how to update column on same row with trigger without recursion?

Given that you cannot disable recursive triggers, the next best options are:

Have the trigger detect how many levels deep it is using TRIGGER_NESTLEVEL function. Use this at the beginning of the trigger to simply exit if it is not the 1st trigger execution in the stack. Something along the lines of:

IF (TRIGGER_NESTLEVEL() > 1)
BEGIN
  -- Uncomment the following PRINT line for debugging
  -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;

This will require a little bit of testing to see how it is affected by the initial insert being done by another trigger (in case that ever becomes an issue, but it might not). If it doesn't work as expected when called by another trigger, then try setting some of the parameters to this function. Please see the documentation (linked above) for details.
Set a flag in the session-based "context info" using SET CONTEXT_INFO. Context info is a VARBINARY(128) value that exists at the session level and retains its value until overwritten or until the session ends. The value can be retrieved either by using the CONTEXT_INFO function or selecting the context_info column from either of the following DMVs: sys.dm_exec_requests and sys.dm_exec_sessions.

You could place the following at the beginning of the trigger:

IF (CONTEXT_INFO() = 0x01)
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;
ELSE
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  SET CONTEXT_INFO 0x01;
END;

This option doesn't work so well if you are already using Context Info for some other reason. But, anyone using SQL Server 2016 can make use of SESSION_CONTEXT, which is a new session-based set of key-value pairs.

Either of those methods is more reliable than using IF NOT UPDATE(LastUpdated) since the UPDATE(column_name) function can only tell you if the column was in the SET clause or not. It cannot tell you if the value has changed, or if it changed to the "current" GETDATE() value that you are expecting / wanting. Meaning, all of the following statements bypass the desired effect of the trigger (i.e. making sure that the LastUpdated column has the actual date & time of the modification):

UPDATE ct
SET    ct.LastUpdated = ct.LastUpdated
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = '1900-04-01`
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = 1
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = GETDATE() + 90
FROM   Contact ct
WHERE  ...

The safest method is probably using TRIGGER_NESTLEVEL() (option 1) and passing in the parameters for checking just this particular trigger, so that being called due to an INSERT from another trigger does not adversely affect it:

TRIGGER_NESTLEVEL( @@PROCID , 'AFTER' , 'DML' )


Creating domain user login from another domain?

In order for one domain to have access to objects in another domain, a domain trust needs to be established to allow it. (I'm assuming you don't have a parent-child domain relationship right now.)

This is a configuration change in Active Directory; SQL Server is not involved.

Once a trust is established, the server will be able to see the necessary domain objects when you create the login.


MySQL Cluster -- Storing table data on data nodes

everything is in order!

 The data nodes don't have individual files for each table and so you won't see the table names there. What you're seeing with the MySQL Server directories is not the files containing the contents of the tables but a copy of the data dictionary/schema definitions for those tables (e.g. if you add more rows to these tables then the files you see with the data nodes should increase in size but the ones with the MySQL Servers should not).

Regards, Andrew.


sysadmin user can't connect to explicitly specified database

I checked whether I had any SQL Agent jobs on the server that ran every five minutes, and found one that was failing.  When I checked the job steps, one of them had a blank Database drop down.  I selected a database from the drop down, and that corrected the issue.  Now, after finding the root cause, the error makes perfect sense: that user definitely couldn't open an undefined database.


The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package

You cannot use a SQL Account to run an SSIS package in the Integration Services Catalog. There is logic within the CLR methods that are used to run the SSIS packages that reject non-windows authentication (as you have discovered).

As a consultant, I ran into issues where I'd use my corporate laptop plugged into a client's network. In that case, my account sdc\billinkc would not exist in the client domain. Instead, their servers would expect client\bfellows credentials to be presented. The work around is to use RunAs. Alternate reference

From my answer on deploying ispac to foreign domain...

  I created a suite of batch files that launch every process I need. They take the form of

runas /netonly:client\bfellows "C:\windows\system32\cmd.exe"

  I have one for a command prompt (above), Visual Studio, SSMS, PowerShell, PowerShell ISE and some other specialty apps that need to work with their domain.

  Using the runas approach, I've been able to deploy packages using all of the above methods (as well as deploying directly from a Visual Studio instance being run with foreign credentials).

You'd want to use ssms.exe and the correct install path to launch management studio and then SSIS packages would run.

Approach #2

If you don't have an account in the foreign domain and the only thing they are willing to do is create a sql login, then they will also need to

Create a Credential
Create a Proxy
Create a SQL Agent job that uses the proxy to run the SSIS package
Grant the sql login the rights to run the job

This will allow you to run an SSIS package in a manner that is allowed as well as present domain credentials to the required resources.


How to get pg_archivecleanup on Amazon Linux 2014.03?

With the help of this AWS forum thread, I realized that this line in /etc/yum.conf

exclude=postgresql*

excludes not only updates but installs.  Once I commented that out (temporarily), I was able to use

sudo yum install postgresql-contrib

to install postgresql-contrib directly from the Amazon repo--no need for changing the repository or the operating system.


As @jkavalik said in a comment, according to the MySQL docs UNION can only be used to combine the outputs of SELECTs, which EXPLAIN is not.
However, you can line up the raw outputs of each EXPLAIN in a text file using the MySQL commands in a shell. Run this once for each EXPLAIN statement:
mysql -u $USERNAME -p$PASSWORD -Bse "[EXPLAIN STATEMENT GOES HERE]" >> explains.csv
From the MySQL docs:

-B: Print results using tab as the column separator
-s: Silent mode. Produce less output.
-e statement: Execute the statement and quit.

This will give you a tab delimited row and >> will append to the file specified at the end, creating it if it is not already there.


How do I show a running total from the previous records?

I am guessing you have an sql server, but it should alspo work on other rdms that support window functions

SELECT
Seller
, Amount
, SUM(Amount) OVER (PARTITION BY Seller ORDER BY Seller ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Sales
GO

Seller |  Amount | RunningTotal
:----- | ------: | -----------:
Alex   | 10.0000 |      10.0000
Alex   | 10.2500 |      20.2500
Alex   | 20.5500 |      40.8000
Jane   | 10.2500 |      10.2500
Pete   | 10.2500 |      10.2500
Sarah  | 15.0000 |      15.0000
Tracy  | 10.2500 |      10.2500
Tracy  | 11.0000 |      21.2500
Tracy  | 10.2500 |      31.5000

db<>fiddle here


Database system vs database management system

Most are saying they are the same.

They are the same, pretty much. Most people use the two interchangeably.

You could try make a case for "database system" to cover simpler arrangements where there is just a database and not management tools (for instance in memory database libraries and/or like sqlite is often used), but there are so many grey areas here defining the difference is going to be difficult to do in a consistent manner.


Why the mysql MSI Installer is more huge than wamp installer packages size?

MySQL Installer provides an easy to use, wizard-based installation experience for all your MySQL software needs. Included in the product are the latest versions of:

MySQL Server
MySQL Connectors
MySQL Workbench and sample models
Sample Databases
MySQL for Excel
MySQL Notifier
MySQL for Visual Studio
Documentation

While WAMP comes only with MySQL-Server (no client tools etc), not 100% sure but did you see if the wamp-setup is offline installer only?


Lookup transformation not seeing new records

By default, the lookup transformation uses full cache and doesn't refresh it's data. You would need to change the lookup transformation to "No Cache" to make sure it executes a query every time the task has to look up a value:

Please refer to the guidance on the SSIS team blog:

Lookup cache modes

  When to use no cache:

  When your reference table is changing (inserts, updates, deletes)
  ...


Display line number where error occurred in DB2 stored procedure

If you are on a currently supported version of Db2 for LUW (11.1 or 11.5) you can use either DBMS_UTILITY.FORMAT_CALL_STACK()  or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() in your signal handler to log the troubleshooting information.


Is it useful to have the SQL Server instance root directory on a separate drive?

With regards to splitting the instance root there are a couple arguments in favor of doing it.

Some People are in favor of keeping their "C" drive dedicated to just the OS and OS binaries. This can give you some different options for recovery in the event of a crash on the C drive, it can help keep the OS from causing or receiving space related issues from sharing with other apps.
You are isolating SQL Server's binaries from other programs and ensuring availability of some of the critical folders like the Logs folder where error logs go - this folder needs to be accessible for SQL Servers start up. You are protecting yourself from others, basically.

You can put the SQL Server binaries/instance files in the same place you tend to put your other program files. But if you do that - at least make sure you take your system database files and potentially your default backup location and move it someplace else..

Here is what I tend to do when given an unlimited number of drive letters to play with (at a minimum.. Letters aren't important here):

C - OS and system level files. Only
D - Program files for all apps (including SQL Server)
S - Instance level files/SQL Server system databases and log files typically (except for TempDB) (note.. If I have multiple instances, I won't make 4 of these.. I'd put all SQL binaries for all instances on S in most situations, with the folders providing the separation)

(ED- Another note - I often don't have an "S" drive available. At the end
   of the day, having your system database files for Master, Model, MSDB
   and Resource db living on the same drive as some of your user
   database files, but in a separate folder for logical separation to keep things less confusing isn't the end of the world.)

F - Data files for user databases
L - Log file drive for user databases
T - TempDB
X - Backup drive (though in a lot of cases I elect to stream a backup across to a network drive, not paying for a copy after the backup and I'm immediately backing up to storage someplace else.)

I'll often have more data and log drives and sometimes another TempDB drive. Add in multiple instances and you can run out of drive letters quickly. You can certainly get away with putting your instance level files on C:. And I do a lot of health checks for clients that were setup like that - and I never say "oh wow.. we have to fix that now" - Now  if their TempDB file(s) are there, too, I'll typically have them change that. Sometimes move their master and MSDB databases as well..

But the world won't end if you don't split these things up. I think the benefit is really just keeping -your- files separate. As a DBA you should have a healthy paranoia around other roles at your company, other applications, other installations, etc. and the more you can isolate yourself from the potential for conflicts, the better you'll be. And it gives you some more options for reinstallation and recovery. So yes separate your binaries from C.. But my advice wouldn't be to go crazy on a separate drive for each instance..


SSIS Package Failing since Database failed over to Secondary (Basic Availability Group)

I figured what was the issue. Apparently the SSIS job was connecting with 2 databases that site on 2 different clusters, thus it required to connect to 2 different Listeners. When I added the user on secondary nodes of both clusters the issue was resolved. We were able to test this during the patching windows when we had to fail-over.


Most efficient way to aggregate data to new table

This is what SSAS was made for, and there are a whole host of client tools that can connect to it including Excel.

Elaborating

SSAS - SQL Server analysis services is a free component of SQL server, which will build a multidimensional version of your data(cube), that will pre-build all the aggregations you require/define.

The design tool is free to download called SQL Server Data Tools.

Once you set up the data source and data view based on the table you have the easiest way to go is to create a Dimension and Fact based on your table the dimension will have all the Metadata descriptors and the fact will just have the numerics/measures.

You then join the two in the Cube, once published/deployed both SSRS and other tools can connect to it.

Some links

https://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv

https://technet.microsoft.com/en-us/library/ee677579.aspx


(MySQL) Need an "in-place" REPLACE for adjusting historical data

Using the INSERT (SELECT)...ON DUPLICATE KEY UPDATE should permit you to visit these rows and update them with original values. Also you're not correct about REPLACE. This is the mechanism used by pt-table-sync to correct data drift. The documentation details a REPLACE == DELETE AND INSERT but that's not done within the scope of AUTO_INC.

mysql> create table replace_test (id int auto_increment primary key, c1 varchar(5)) engine=innodb;

mysql> insert into replace_test(c1) values ('aaaaa'),('bbbbb'),('ccccc');

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  2 | bbbbb |
|  3 | ccccc |
+----+-------+
3 rows in set (0.00 sec)

mysql> replace into replace_test (id,c1) values (2,'ddddd');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  2 | ddddd |
|  3 | ccccc |
+----+-------+
3 rows in set (0.00 sec)

I think you expected to see something like below but that is not the behaviour.

mysql> select * from replace_test;
+----+-------+
| id | c1    |
+----+-------+
|  1 | aaaaa |
|  3 | ccccc |
|  4 | ddddd |
+----+-------+
3 rows in set (0.00 sec)

But that does not happen as you can see from above.


Why do I have to backup a transaction log twice for it to shrink?

SQL Server's transaction log files are made up of Virtual Log Files (VLFs). When you shrink a log file, it will only release unused VLFs at the end of the file. There is always going to be at least one VLF that is being used for current activity.

Consider this simplified example. You have a Log file that is made up of 6 equally sized VLFs. For simplicity, let's say 1 VLF = 1MB, so the total log file is 6MB. You want to shrink it to 3MB. You perform a transaction log backup, which makes some VLF (we'll pick 4 at random) active, and the rest available for reuse:

1____2____3____4_XX_5____6____

Now you perform a shrink with a target size of 3MB. Shrink can only lop off the unused VLFs at the end, but cannot go all the way down to the target size of 3MB:

1____2____3____4_XX_

You can't shrink any further, because VLF 4 is in use, and that's just how things work. You need VLF 4 to be available for reuse before it can be truncated.  So you take a second transaction log backup--VLF 4 gets backed up, and VLF 1 becomes the active VLF:

1_XX_2____3____4____

Now, you can perform a second shrink, and get the transaction log down to the target size of 3MB:

1_XX_2____3____


Does PostgreSQL ever retry failed transactions internally?

AFAIK PostgreSQL does not retry failed transaction. Even if this would be the case you can never be 100% sure your transaction will succeed: coding your application with any DBMS assuming transaction will never fail in the database would be wrong.

the proof is in the pudding

I know that other database may in some very special cases retry statement (not transaction): for example Oracle see https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:11504247549852#3950826900346316085.


`"C:\xampp\mysql\bin"' is not recognized as an internal or external command,operable program or batch file

You might want to try the following instead:

C:\xampp\mysql\bin\mysql.exe -u root -p

assuming you are using a CMD / DOS Prompt

Depending on the path in which your mysql.exe resides, you might have to incorporate using double quotes as follows:

""C:\Program Files (x86)\mysql\bin\mysql.exe" -u root -p"

assuming you are using a CMD / DOS Prompt

You add double-quotes from "C:\... to the end of the executable ...exe" and then add double-quotes at the beginning of the whole command until the end. You end up with the above command.

Reference: Long Filenames or Paths with Spaces Require Quotation Marks (Microsoft Support)


PostgreSQL, how to extract all the possible couples of a primary key in a table?

Sounds like a simple cross join:

select a.id, b.id
from input_table a
  cross join input_table b
where a.id <> b.id;


Catalog of LinkedServer2 from Server1 shows databases on Server1

Check the definition of the linked server by running:

SELECT s.name
    , s.product
    , s.data_source
    , s.location
    , s.catalog
FROM sys.servers s

Almost certainly, the linked server is connected to server1, which will likely be confirmed in the contents of the data_source column in the query above.


How Can I Determine Object Distribution in a Multiple-File Filegroup?

You should assume, for all intents and purposes, that parts of every object in a given filegroup will be distributed across all of the files in that filegroup. You might be able to work up some script that will prove one or two smaller tables don't have any data on file 5 or file 8, but for what purpose? I say it's easier to just write a script that will rebuild all of the tables in that filegroup.