Unmarshalling Hierarchical JSON in SQL Server 2016+

In this case, I'd use a cross apply.

DECLARE @json NVARCHAR(MAX) = N'{"menu": {
    "header": "SVG Viewer",
    "items": [
        {"id": "Open"},
        {"id": "OpenNew", "label": "Open New"},
        null,
        {"id": "ZoomIn", "label": "Zoom In"},
        {"id": "ZoomOut", "label": "Zoom Out"},
        {"id": "OriginalView", "label": "Original View"},
        null,
        {"id": "Quality"},
        {"id": "Pause"},
        {"id": "Mute"},
        null,
        {"id": "Find", "label": "Find..."},
        {"id": "FindAgain", "label": "Find Again"},
        {"id": "Copy"},
        {"id": "CopyAgain", "label": "Copy Again"},
        {"id": "CopySVG", "label": "Copy SVG"},
        {"id": "ViewSVG", "label": "View SVG"},
        {"id": "ViewSource", "label": "View Source"},
        {"id": "SaveAs", "label": "Save As"},
        null,
        {"id": "Help"},
        {"id": "About", "label": "About Adobe CVG Viewer..."}
    ]
}}
'

  SELECT
     JSON_VALUE(a.value, '$.header') AS MenuItem
    , JSON_VALUE(b.value, '$.id') AS Id
    , JSON_VALUE(b.value, '$.label') AS Label
  FROM OPENJSON(@json) a
    CROSS APPLY OPENJSON(a.value, '$.items') b


Unless I'm missing something, wouldn't it be:

ROUND((SUM(revenue) - SUM(cost)) * 100.0 / SUM(cost), 2) as roi


This is not enough information in your question, to definatively say if you can or can not meet your goal with your available resources.

It is possible to Create a domain-independent availability group that spans physical locations.

Yes it can be done.  How to set up the infrastructure to support it, is going to be out of scope for DBA.se, but you can get help at https://serverfault.com/help/on-topic & https://networkengineering.stackexchange.com/


How do I get rid of the duplicate code in this query?

I would just duplicate the expression. It is possible to structure your query so that you only define the expression once, but it will end up being more verbose, as it requires that you define the field you're WHEREing as a column in a subquery and then do the WHERE on the outer field.
SELECT * FROM (
  SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
  FROM personal."time tabs"
  WHERE "reset automatically" = true
) AS inner_query
WHERE "new last done" < now();

As long as you don't have ORDER BY or LIMIT on the inner query, the query planner should be able to tell that it's logically equivalent to having that WHERE in the inner query, so actual query execution shouldn't be affected.


Granting permissions to SSIS for developer

Permissions to assign her are going to be based on what deployment model she is going to use within your environment. SSIS 2012/2014 added an additional model: Project and Package

For Package Deployment model she has the option of deploying the packages to msdb. Granting her permissions for this type of deployment you will grant her permissions to an SSIS role. If she is the only person that is going to be working with the packages you can simply grant her db_ssisltduser. This will provide her permissions to do anything she needs with the packages she creates and deploys (making her the owner of those packages). You can read through the article linked to get an idea of more.

For Project Deployment model you will need to work with the SSISDB catalog. With this it is just granting permissions at a database-level role. If this is not already created on the instance you will need to do this for her. Once it is created you should see a database that will have a specific role called ssis_admin, this role is special to the catalog database and is not a normal role found in every database.

The database will show up under the Databases node in Object Explorer, just go to Security>Roles>Database Roles and find the ssis_admin role. Within the properties of that role you can add her login.


What do you expect your real life volume of data to be?

For 10 million rows, I wouldn't bother with partitioning. The overhead far outweighs the benefits: partitioning isn't a silver bullet to cure performance issues.

To answer,

Point 1: on the first run, data needs loaded into memory ("buffer pool") and will stay cached until evicted based on memory pressure and usage. Personally, I'd test with the cache filled because you'd expect your app to require that data very often, especially if you think partitioning is the solution to some problem

For point 2, what queries do you expect to run in production? The queries should be representative of this production load. However they should test different realistic filter combinations with and without partition key at least.

Edit, some reading, after comments below:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/
http://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/ (point 7 especially)


Query to find the subtract of each column from the following column

You could use LAG() to subtract the value from the previous value, ordered by DateKey.

SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V;

But this will give you an extra NULL for the first value since we are doing 100 - NULL (Non existing row since the row with val 100 is the first one).

One way around this is by removing the NULL with a CTE

;WITH CTE
AS
(
SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V
)

SELECT * FROM CTE
where val IS NOT NULL;

Result

ProjectCode val
A   20
A   20


Repository for organizing company SQL statements?

We've used SVN for this, seems to be quite popular as several companies I've spent time with used it. You should be able to control who can and cannot write/update files, as well as who can see what. And one of the more popular clients is Tortoise, as vercelli mentions.


Oracle - Why create several service_name in service_names parameter?

Because in the same database you have multiple applications, multiple workloads,... and services help to:
 - identify sessions when troubleshooting performance issue
 - manage resources for those with higher or lower priority
 - balance between several nodes
 - offload some read-only workloads to a standby database
...


Trigger and temp table or materialized view

If flashback does not work for you for some reason, you could add a column to your table to save the original value of the indicator and a trigger of the sort:

create trigger t
before update of ind_col on your_table
for each row
begin
 if :old.ind_save_col = '' then
   :new.ind_save_col := :old.ind_col;
 end if;
end;
/

(code not tested). Your nightly process would then copy rows where ind_col != ind_save_col, then reset ind_save_col.


Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

Your XML stored in a varchar(max) column should look like this.
<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran LÃ³pez</FLD>
    </LIN>
  </TAB>
</PARAM>

The ó should be represented with a double byte value Ã³.
If you don't have a UTF-8 encoded string stored in your column, the right way to go about this is to remove the encoding from the XML before you convert the value to the XML datatype.


Distribute records on different MySQL databases - MySQL Proxy alternative

You can take a look into MySQL Fabric (Official Doc) but it requires more db server

I have tried this tool only in R&D env for testing a basic HA

It supports some sharding scenarios

Here some high level pros and cons

Pros:

setup
sharding
write/read
basic HA

Cons:

fabric node is the SPOF
no multi master
rewrite software in order to use the fabric driver


Replacing Database in High Availability Group Enviroment

With SQL Server 2012, there is only one way to synchronize a database when it is added to an availability group--backup and restore.  So you will need to drop the database from the secondary and restore the primary's backup (or restore with overwrite) when adding the database to the availability group.

With SQL Server 2016 and later, automatic seeding is available for initial synchronization, so the backup/restore process can be skipped if seeding is enabled on the AG.


ADO connection suddenly starts failing Intermittently

Since Access can successfully connect to the SQL Server and execute queries against it, the connection string the Access is using must be correct.  The connection made through ADO, on the other hand is relying on CurrentProject.Connection property, which we don't know what it is, and fails unexpectedly.  Inside AccessDataBaseEngine_X64.exe seems to have no ODBC drivers, and Access works with or without AccessDataBaseEngine_X64.exe installed.  So the DSN the Access is using didn't change, therefore the problem must be the connection string used in ADO.

So if you use the same connection string as that of Access, the problem should go away.

As for why it started behaving erratically all of sudden I can't fully comment on that.  But what Microsoft states about the CurrentProject.Connection property might give you a clue (the relevant section in bold).

  Use the Connection property to refer to the Connection object of the current Microsoft Access project (.adp) or Access database object. You can use the Connection property to call methods on the Connection object such as BeginTrans and CommitTrans.

  Note:
  The Connection property actually returns a reference to a copy of the ActiveX Data Object (ADO) connection for the active database. Thus, applying the Close method or in anyway attempting to alter the connection through the Connection object’s methods or properties will have no affect on the actual connection object used by Microsoft Access to hold a live connection to the current database. Since the Connection property is the main Shape provider connection, the following information is necessary when using this property.

  MSDataShape uses Recordset.CursorLocation = adUseClient. Do not set   CursorLocation prior to assigning a recordset to CurrentProject.Connect.
  MSDataShape uses Recordset.CursorType = adOpenStatic. Do not set CursorType prior to assinging a recordset to CurrentProject.Connection.
  MSDataShape accepts Recordset.LockType = adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly (default). If set to adLockPessimistic, it is changed to adLockOptimistic.
  The shape connection does not support the all ADOX operation, specifically the Columns.Properties collection is not supported.
  In order to ensure that a shape connection will work correctly, the Command.CommandType must be set to adCmdTable.

Bonus:

How to know what files were involved with AccessDatabaseEngine_x64.exe? (I was curious!)

Use Orca


monitoring sql execution perfomance of an application that makes use of SP_prepare

Both missing indexes and long-running queries are available in DMVs, with the caveat that DMVs can get refreshed on some events, such as service restart, certain sp_configure changes, etc.

Here's a missing index query from Bart Duncan's blog post, Are you using SQL's Missing Index DMVs?:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)
    * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_'
    + CONVERT (varchar, mig.index_group_handle) + '_'
    + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
  ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
  ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)
  * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact
  * (migs.user_seeks + migs.user_scans) DESC

I would change about 20 things in that query, but it's a start. This will show you the indexes you should investigate creating - but please don't just create all of the indexes suggested. I've blogged about why you don't want to do that.

And here is a query from the sys.dm_exec_query_stats documentation:

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
  SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
  MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

Also some bad habits here, but shrug. This will collect the information about your longest-running queries in aggregate. If you want to know about individual instances of long-running queries, you'll have to capture those yourself using a server-side trace with a duration filter, or extended events, or a 3rd party monitoring product (I'm biased because I worked there for nearly a decade).


SQL Deadlock graph - Find exact statement holding the lock and the time it started

Question 1: But which statements are the OWNERS ?

Statements don't own locks.  Transactions own locks.

    And when did they start holding the X lock on the Index Key?

There's nothing in the deadlock graph or DMVs that will tell you that.  That information is only available in a trace event or an XEvent, at the moment the lock is acquired.

    Is a Key Lock held only on the rows which satisfy the UPDATE's WHERE clause, or is it held on All values that match the value in the Index

UPDATE uses a U lock to read, then converts to an X lock to update.

    Would it go away by adding (TABLOCK) to the UPDATEs

Probably.  One key strategy to deadlock resolution is to lock earlier and bigger.  Deadlocks occur only when two sessions acquire compatible locks, and then later attempt to acquire incompatible locks.  If the first locks in the transaction are incompatible, then the second session will wait until the first commits to acquire its first lock.

One easy way to get a lock at the right granularity is to use an application lock, with sp_getapplock.  If you decide that transaction A and B can't run concurrently, then just make each acquire the same application lock at the beginning, and they will run sequentially.


Alternative Database Tools for Web Admin

A list of web replacements is available here:

http://www.webdesigneronline.co.uk/10-great-alternatives-to-phpmyadmin

And this question was asked before in Stackoverflow as well:

https://stackoverflow.com/questions/1324972/ajax-phpmyadmin-alternative

In my opinion, a alternative phpmyadmin should not just look good, but must at all cost keep up with security updates. If you are intranet user, it might be a different case, but I know from the early days of phpmyadmin that security is very important for anything involving "http and sql" ;-)


Is this procedure to Failover to Logshipped secondary and Fail back again procedure correct?

For built-in failover / failback functionality, choose DB Mirroring over log shipping:

http://technet.microsoft.com/library/bb934127.aspx

You can follow a process to do this with log shipping, but manual steps are required. Mirroring is the best choice.


Given that the valid characters for unquoted identifier characters are documented here, you should be able to find non-confirming table names in information_schema using a regular expression match, with this query:

SELECT *
  FROM information_schema.tables
 WHERE table_name NOT RLIKE '^[0-9a-zA-Z$_]+$';


PostgreSQL DELETE query hangs (and so does EXPLAIN ANALYZE!)

Assuming that the columns are not nullable, the query can be simplified (without the self join) to using either NOT IN or NOT EXISTS:
DELETE FROM cards
WHERE available = FALSE
  AND _id NOT IN
      ( SELECT card_id
        FROM game_results
      ) ;

DELETE FROM cards AS c
WHERE c.available = FALSE
  AND NOT EXISTS
      ( SELECT *
        FROM game_results AS gr
        WHERE gr.card_id = c._id
      ) ;

Still, deleting 100K rows can not be instantaneous. Performance may be affected by lack (or excess) of indexes, triggers, cascade deletes, etc.
You can use EXPLAIN (only, without ANALYZE) to get the execution plan of a DELETE. See EXPLAIN for details, especially if you want to try ANALYZE or other options of EXPLAIN:

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;


Dynamic select column name on the basis of a flag

If your final query returns 2 columns, it returns 2 columns. You can move CASE to the SELECT sentence but it will return 2 columns.

SELECT CASE WHEN @flag1 = 1 THEN #{COLUMN_VALUE} END AS col1,
       CASE WHEN @flag1 <> 1 THEN #{COLUMN_VALUE} END AS col2
FROM   ACCOUNT_USER table
WHERE  acct_id = #{accountId};

In case you need only one column named as COL1 or COL2 I'd suggest to use a dynamic query.


Can you get a filehandle to an Oracle SecureFile LOB?

No, to SecureFile's exposing a FileHandle

You can not. There is no filehandle to (internal) LOBs. Oracle separates their large binary implementations into ones that are internal, or external. Internal Lobs are tracked with a "Lob Locator" (Oracle terminology) which is of type OCILobLocator in the OCI. You can perform operations on them with the Oracle Call Interface (OCI). The OCI's operations take a Lob Locator as a handle but are not otherwise useful, and do not have an equivalent for all POSIX operations. For example, there is no fseek.

OCI Lob Interface (C)
OCCI Programming with Lobs (C++)

External LOBs

Oracle Provides an "external LOB" called a "BFILE". The BFILE is associated with a path on disk. Using that path, you can manually open the file with open, but there is no transaction token, and the filehandle is not closed at the end of the transaction. Note "BFILE"s do not support deduplication, compression, or encryption and you'll be left to the underlying filesystem of the disk for those faculties.


Mapping between each bit in a bitmap index and tuple-pointers

First of all, there is no bitmap index type. The Bitmap Index Scan is just a fast mechanism to look up and sort values from a bitmap.

The Backend Process scans the Index for all tuples, and stores them in a structure called TIDBitmap. The pointers to the tuples are sorted so they could be accessed sequential on disk.

The source of the Bitmap structure could be found in src/backend/nodes/tidbitmap.c


PostgreSQL: streaming binary data to a bytea field is very slow

Updating a row in a multiversion model like postgres means creating a second copy of that row with the new contents. Physically, there is no in-place update: UPDATE is similar to DELETE + INSERT the new contents.

So in the above loop, the first chunk, instead of being written once, is written N times, the second chunk is written N-1 times, the third N-2 times and so on.

To make it worse, all these writes have to go to the WAL files for journaling too, and all the intermediate versions of the rows will need be picked up by the autovacuum process to be eventually discarded.

Assuming that these chunks cannot be assembled on the client and must be streamed, it might help to do something like this:

CREATE TEMPORARY sequence s;
CREATE TEMPORARY TABLE buffer(seq int default nextval('s'), chunk bytea);

-- buffer in temporary storage
LOOP
   INSERT INTO buffer(chunk) VALUES ($chunk)
END LOOP

-- assemble in final storage
INSERT INTO permanent_table(data)
   SELECT string_agg(chunk,''::bytea order by seq) FROM buffer;

TRUNCATE (or DROP) TABLE buffer;

At least the chunks will be written only twice (once in buffer storage and once in final durable storage), and only once in the journal, as the temporary table won't be WAL-logged.


Azure SQL Server export via sqlpackage produces Error SQL71501 (Unresolved reference to object MS SyncAccount

This occurs when Azure SQL Data Sync was used on the database to sync with another database. The remnants from the DSS schema used by SQL Data Sync do not allow to export the database due to inconsistencies.
In order to successfully export the database, we need to:

Create a database copy. You can check how to create a copy of the
database using the portal at the portal. (there is also
documentation for doing this using T-SQL or PowerShel.

Remove the Data Sync metadata objects from the copy. You can find a
script to do it here ... Please make sure you are connected to
the copy.

Export the database from the copy.

Delete the database copy.

For more information, visit this article.


Minimal grants for readonly single-table access on PostgreSQL

The question is: is this sufficient or there should be more grants
  (database connect, usage maybe)?

Security may be hardened from the default mainly on these points:

The pg_hba.conf file. It filters connections before any database privilege is considered. The default is relatively open for local connections, but it might be restricted to an explicit list of databases, usernames, and network origins.
The permissions for public, the pseudo-role that anyone has. A user can implicitly connect only if the connect privilege is granted to PUBLIC. See Created user can access all databases in PostgreSQL without any grants. A database might have all privileges revoked from public. See REVOKE in the doc for the major PostgreSQL version you're using.
The existence of the public schema. It's created with the database by default for convenience but it's not mandatory. Instead of removing public permissions from the public schema, it may make sense for DBAs to just drop the public schema when new users should have no implicit permission at all.

In the case when these defaults have been removed, to read one table a new user should be granted:

GRANT CONNECT ON DATABASE dbname TO username;

At the database level:

GRANT USAGE ON SCHEMA schemaname TO username;
GRANT SELECT ON schemaname.tablename TO username;


Odd behavior with SSRS 2016 drop down parameter list

After desperately trying everything from restarting and updating SSRS, to clicking my heels three times and throwing salt over my shoulder, the solution is to not use the Microsoft Edge browser. When I switch to using Chrome/Firefox to run my SSRS reports, the behavior is removed.

Microsoft Edge version: 38.14393.0.0


Finding stored procedures with XACT ABORT ON OR OFF

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code:
DECLARE @cmd nvarchar(max) ;
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL
' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS
    , db = ''' + d.name + N''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , [definition] = sm.definition COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.sql_modules sm
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON sm.object_id = o.object_id
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON o.schema_id = s.schema_id
WHERE sm.definition LIKE N''%XACT_ABORT%'' COLLATE SQL_Latin1_General_CP1_CI_AS
'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

One could rewrite that to show code without XACT_ABORT present, by simply adding NOT to the WHERE clause, as in:
DECLARE @cmd nvarchar(max) ;
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL
' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS
    , db = ''' + d.name + N''' COLLATE SQL_Latin1_General_CP1_CI_AS
    , ObjectName = s.name + N''.'' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
    , [definition] = sm.definition COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.sql_modules sm
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON sm.object_id = o.object_id
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas s ON o.schema_id = s.schema_id
WHERE sm.definition NOT LIKE N''%XACT_ABORT%'' COLLATE SQL_Latin1_General_CP1_CI_AS
'
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
ORDER BY d.name;
EXEC sys.sp_executesql @cmd;

However, you'll notice there are several possibilities for false positives:

If you have a column named XACT_ABORT in the code.
You have a comment such as /*  must not use XACT_ABORT in this code */

etc.
To be honest, your question sounds like an XY problem.  Are you sure you want to determine which code uses XACT_ABORT?  A stored procedure won't cause an open transaction to "hang around".  The most likely cause is a code window open in SSMS that has BEGIN TRANSACTION where the corresponding ROLLBACK or COMMIT haven't been executed.  Alternately, there could be some legitimate long-running code keeping transactions open.  One common issue that results in long running transaction is code using the RBAR method.
Regarding your second question, I'd be extremely reluctant to ever say a particular set statement should "always" be used, although I do tend to use SET XACT_ABORT = ON a lot.  Erland Sommarskog is very highly regarded in the SQL Server community, but I don't agree with this:

stored procedures should always include this statement in the beginning:
SET XACT_ABORT, NOCOUNT ON


How long has it been since last card top-up?

I've added time part to the TransactionDate just to get the correct order for the transactions:

create table cards(CardNo varchar(10), TransactionType int, Amount money, TransactionDate datetime2);
create index ix_cards on cards(CardNo, TransactionType, TransactionDate);
insert into cards values
('0001',        1,              1000.00,    '20170809 00:00:00'),
('0001',        0,              200.00,     '20170810 00:00:00'),
('0001',        0,              300.00,     '20170811 00:00:00'),
('0001',        1,              200.00,     '20170811 00:00:01'),
('0001',        0,              200.00,     '20170812 00:00:00');

GO

5 rows affected

Then you can get it by using an APPLY operator with all records where TransactionType = 1. Note you must limit to one record the rows returned by the subquery.

set statistics xml on;

SELECT t1.CardNo, t1.TransactionType, t1.Amount, t1.TransactionDate, t3.TransactionDate,
       DATEDIFF(DAY, t3.TransactionDate, t1.TransactionDate) GapDays
FROM   cards t1
OUTER APPLY (SELECT TOP 1 t2.TransactionDate
             FROM   cards t2
             WHERE  t2.CardNo = t1.CardNo
             AND    t2.TransactionType = 1
             AND    t2.TransactionDate <= t1.TransactionDate
             ORDER BY t2.CardNo, t2.TransactionDate DESC) t3
ORDER BY t1.CardNo, t1.TransactionType, t1.TransactionDate
set statistics xml off;
GO

CardNo | TransactionType | Amount    | TransactionDate     | TransactionDate     | GapDays
:----- | --------------: | :-------- | :------------------ | :------------------ | ------:
0001   |               0 | 200.0000  | 10/08/2017 00:00:00 | 09/08/2017 00:00:00 |       1
0001   |               0 | 300.0000  | 11/08/2017 00:00:00 | 09/08/2017 00:00:00 |       2
0001   |               0 | 200.0000  | 12/08/2017 00:00:00 | 11/08/2017 00:00:01 |       1
0001   |               1 | 1000.0000 | 09/08/2017 00:00:00 | 09/08/2017 00:00:00 |       0
0001   |               1 | 200.0000  | 11/08/2017 00:00:01 | 11/08/2017 00:00:01 |       0

dbfiddle here


Does automatic fail over happens in mirroring if the connection between witness server and principle server is lost for some reason.?

No automatic failover does not happen in this scenario as the current principal will still have a quorum forming a partner-to-partner quorum. Without a witness, automatic failover is not possible - ONLY Manual failover is possible.

This is explained in more detailed at How a Witness Affects Database Availability ?


Does MySQLDump Verify the backup as restorable

No - there is no way to check if the text file that is produced by the mysqldump utility will in fact restore correctly. Of course, the backup process can be automated (e.g. as per here). I'm not much of a Windows man, but search engines appear to return a number of scripts which you could use/adapt.

What you CAN do however is to run the CHECK table command on your tables of interest - you mightn't be so interested in slow/never changing reference tables (i.e. names of US states for example). Depending on your scripting ability, if the CHECK process throws an error, you could a) stop the backup and b) page a dba or invoke some other process. An excellent answer on how to automate this workflow is given here.

If you wish to use a "belt and braces" approach, you could use the mysqlcheck utility which:

  mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE
  TABLE, and OPTIMIZE TABLE in a convenient way for the user.

Also, see here, but be warned,

  If you use the --databases or --all-databases option to process all
  tables in one or more databases, an invocation of mysqlcheck might
  take a long time.

Basically, it can't be put better than as expressed here:

  It is always a good practice to test the whole solution
  backup/restore. So nothing is better than testing a restore, actually
  it should be a periodic procedure.

Like most things in life, dba work is a series of compromises! :-) You have to consider your RPO (Recovery Point Objective) and your RTO (Recovery Time Objective) (see here) and balance those with the time and expense spent in verifying backups. An agreement with the major stakeholders (clients, managers, dbas) on what is an acceptable level of risk vs. expense is what is required.

Remember though, that every extra 9 in your reliability will add an extra 0 to your budget!

"Yae cannae beat the laws o' physics, Jim!" (*)

(*) with apologies to Gene Roddenberry


Extended event for lock count does not show locks

You mentioned that you are testing in SQL 2012 I suggest you change you query for SQL 2012. You are using the version for 2008R2.

Pay attention to these lines.

WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)

Full test code that is working for me.

-- Find objects in a particular database that have the most
-- lock acquired. This sample uses WideWorldImporters .
-- Create the session and add an event and target.
--
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
DROP EVENT session LockCounts ON SERVER
GO
DECLARE @dbid int

SELECT @dbid = db_id('WideWorldImporters')

DECLARE @sql nvarchar(1024)
SET @sql = '
CREATE event session LockCounts ON SERVER
ADD EVENT sqlserver.lock_acquired (WHERE database_id =' + CAST(@dbid AS nvarchar) +')
ADD TARGET package0.histogram(
SET filtering_event_name=''sqlserver.lock_acquired'', source_type=0, source=''resource_0'')'

EXEC (@sql)
GO
ALTER EVENT session LockCounts ON SERVER
STATE=start
GO
--
-- Create a simple workload that takes locks.
--
USE WideWorldImporters
GO
SELECT TOP 1 * FROM sales.Orders
GO
-- The histogram target output is available from the
-- sys.dm_xe_session_targets dynamic management view in
-- XML format.
-- The following query joins the bucketizing target output with
-- sys.objects to obtain the object names.
--
SELECT name, object_id, lock_count FROM
(SELECT objstats.value('.','bigint') AS lobject_id,
objstats.value('@count', 'bigint') AS lock_count
FROM (
SELECT CAST(xest.target_data AS XML)
LockData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)
 ) LockedObjects
INNER JOIN sys.objects o
ON LockedObjects.lobject_id = o.object_id
WHERE o.type != 'S' AND o.type = 'U'
ORDER BY lock_count desc
GO
--
-- Stop the event session.
--
ALTER EVENT SESSION LockCounts ON SERVER
state=stop
GO

I also tested this as target to a file.  You can then save it in a database table and query/aggregate to your need.

CREATE EVENT SESSION [CaptureLocks] ON SERVER
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.database_id,
           sqlserver.database_name,
           sqlserver.server_instance_name,
           sqlserver.session_id)
    WHERE ([package0].[equal_uint64]([database_id],(5))
           AND [object_id]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\CaptureLocks.xel')
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=OFF)
GO

Sample query to look at the data saved in table.

SELECT OBJECT_NAME([object_id]) AS [objectName],
       resource_type,
       mode,
       Count(0) AS [LockCounts]
FROM [WideWorldImporters].[dbo].[CollectLocks]
GROUP BY [object_id], resource_type, mode

Adding this section based on the question asked in comment section.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-acquired-event-class

Mode column will give you lock type. See above link for details.

I captured another trace with paglock and rowlock hint.  resource_type column will capture the resource type (table/page/row).

Exact query I ran was:

SELECT TOP (1) * FROM sales.orders WITH (PAGLOCK)
GO
SELECT TOP (1) * FROM sales.orders WITH (PAGLOCK)
GO

Sample form live data window.


You can try to analyse your slow log with the Percona tool pt-query-digest and use the option :

–filter ‘($event->{user} || “”) =~ m/john/’

Max.


Teradata SQL syntax from date to quarter

Teradata supports Oracle's TO_CHAR syntax since TD14:

TO_CHAR(date, 'q"Q"yy')


Error 13542 enabling system-versioning for existing tables

I had similar issue and splitting alter table into two separate statements solved it. Try

ALTER TABLE dbo.ClientBeacon ADD
  datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT F_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(),
  SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59');

GO

ALTER TABLE dbo.ClientBeacon ADD
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO


How to reference a table called `\` (simply just backslash character) in MySQL in a query?

Somehow this query actually works...

RENAME TABLE `\` TO backslash; `;

Two statements, three backticks in total - neither statement would work by itself but somehow together they do the job. Despite giving an error.

To test this in a vacuum you can do:

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `\` ( `foo` int(10) ) ENGINE=InnoDB CHARSET=utf8; `;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`' at line 1
mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| \             |
+---------------+
1 row in set (0.00 sec)

mysql> DROP TABLE `\`; `;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`' at line 1
mysql> show tables;
Empty set (0.00 sec)

Please note that the syntax for this command is quite peculiar: it is the word "DROP" followed by "TABLE" followed by the string "\; `;" - that is backtick, then backslash, backtick again and then semi-colon. This is further followed by space, then (another) backtick and finally semi-colon.


Import an xlsx file to Oracle SQL Developer and interpret it as a table

No.  SQL Developer is just a client that interacts with an Oracle database.  It is not a database in and of itself so it has no way to store and manipulate data other than via an Oracle database.

You could download and install an Oracle database on your machine, connect SQL Developer to that, and use the SQL Developer wizard to load the Excel file into a table you create in this new Oracle database. But that is a decent amount of effort to go through just to deal with a single spreadsheet.


Security risk of Enabling MS DTC Distributed transaction?

The more features you turn on, the greater the risk.  The Linux community openly says to turn features off if you don't need them.  Microsoft has a Windows Server 2012 core mode:

Install Server Roles and Features on a Server Core Server

I don't see DTC on the list.  There are good practices, but if there is a business need I turn it on.  I always say the right tool for the right job.  But you can change the default port for DTC, if you do this hacker scanning tools won't find it.

How to Configure MSDTC to Use a Specific Port in Windows Server 2012/2012R2

I have worked at banks that change the default port of all the SQL Servers.  In some cases, each server had a port in a range of 100 and all the firewalls let that group through.  It is a support nightmare, but it is VERY secure.


How can I automatically trigger a script when my DB fails over?

For a cluster failover you can use a startup procedure. Assuming you have xp_cmdshell enabled and the service account has adequate permissions to start services:

USE [master];
GO

CREATE PROCEDURE dbo.StartService
AS
BEGIN
  SET NOCOUNT ON;

  EXEC master..xp_cmdshell 'net start servicename';
END
GO

EXEC [master].dbo.sp_procoption
   'dbo.StartService',
   'startup',
   'true';

I blogged about this for different reasons here:

https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

You could also not use the startup option and instead just call the stored procedure from a job, and schedule the job to run when SQL Server Agent starts:

Of course this depends on the SQL Agent service starting successfully, and also assumes that the Agent or SQL Server services do not depend on the service you're trying to start. :-)


MS SQL Server 2005 DB backup with partitioned tables to non-partitioned SQL Server 2012

Community Wiki answer compiled from comments to the question†

Jon Seigel:

  It isn't possible to restore a database that uses Enterprise edition
  features on an instance that doesn't support those features. You'll
  have to either remove partitioning and then use backup/restore, or
  export/import the data.

Kin

  See this answer on another question that shows how to use bcp
  out and BULK INSERT in native mode to transfer the data quickly.

†Comments may be deleted and are not searchable.


SSMS Extremely Slow connecting to local dev db

Your DNS entry in the C:\Windows\System32\drivers\etc\hosts file will be:

ROG-DREAM-MACHI   127.0.0.1

You'll need to use Notepad with administrative rights, to be able to make any changes to that file.

(However, I'm voting to close this question as a duplicate of the one you posted.)


Tool to synchronize PostgreSQL DDL and data databases

Here are some tools that have been around for a while, though I have not used any of them:

https://github.com/jmafc/Pyrseas
http://www.andromeda-project.org/
https://labs.omniti.com/labs/pgtreats/wiki/getddl

I have a feeling there is at least one more floating around, but a quick search didn't trigger any other links for me.


parallelism repartitions, ordering, and hash matches

Hash matches are not necessarily bad @ all. Please see Grant Fritchey's awesome (and free!) book: SQL Server Execution Plans


The query below is a good starting point.  It returns a list of logins that have no matching user in any attached database.  Be aware there are many reasons a login might exist at the server level, but not have an explicitly created user in any user database.  Before removing the logins shown by this code, you should validate that they are not needed.  As an example, this code may list the service accounts used to run SQL Server services; removing them might be a bad idea.

It filters out server roles, since they cannot be added to databases as users.  Similarly for certificate-mapped logins, since they can only be used for code signing.

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'SELECT dp.sid
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
WHERE dp.sid IS NOT NULL
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE';

PRINT @cmd;

DROP TABLE IF EXISTS #database_sids;
CREATE TABLE #database_sids
(
    [sid] int NOT NULL
);
INSERT INTO #database_sids ([sid])
EXEC sys.sp_executesql @cmd;

SELECT sp.name
    , sp.type_desc
FROM sys.server_principals sp
WHERE NOT EXISTS (
    SELECT 1
    FROM #database_sids ds
    WHERE ds.sid = sp.sid
    )
    AND sp.[type_desc] NOT IN (
          N'SERVER_ROLE'
        , N'CERTIFICATE_MAPPED_LOGIN'
        )
ORDER BY sp.name;

The query gathers the list of security identifiers from each database in the SQL Server instance, then returns logins that don't appear in that list of security identifiers.

There are a couple of "gotchas" with this approach.  If any databases are offline when the code runs, users in the offline database(s) will not be excluded from the output, resulting in possible false-positives.

Since the script uses the security identifier, instead of the name of the user, it may return false positives for users and logins with the same name, but different SIDs.  This can easily occur If you restore a database from a different source SQL Server and run this code against it without "fixing" the orphaned users first1.

1 - I am associated with the site SQLServerScience.com and wrote this blog post


Will logRotate cause any unavailability in MongoDB service?

The should not be any break in service. Just remember set systemLog.logRotate or --logRotate to rename or it will open same file again...


What is the best way to divide data for random groups?

One alternative would be to use a sequence to perform round-robin allocation. Define the minimum and maximum values of the sequence to match the requisite number of buckets. Something like
CREATE SEQUENCE group_sequence
    AS tinyint
    START WITH 0
    INCREMENT BY 1
    MAXVALUE 31
    CYCLE;

The persisted column holding the group number can have a default value of ..next value for group_sequence.
The randomness of this will depend on the arrival order of new rows. If, say, you have 32 input streams and they send rows one after another each stream's rows will end up in one group, obviously. If data arrive in a batch, that batch will be evenly spread over all groups, give-or-take a few rows. Whether this is significant to the quality of your calculations I cannot say.
On the matter of casting to tinyint I do not have a mathematical answer, just an observation. Since an MD5 hash returns 16 bytes casting to bigint already produces truncation. Why would casting to tinyint be more problematic? You can perform a modulo calculation directly on the result of HASHBYTES, which will perform an implicit cast to 4-byte int.
Note also that casting the hash could produce a negative integer. Taking the absolute value after calculating the modulo will give about half the number of rows in the zero group compared to other groups. Since tinyint cannot be negative this at least would be avoided.


Delete rows on a table with cascading foreign keys

First, it depends on how your foreign keys are declared. Assuming tables like:
CREATE TABLE parent
( pid ... not null primary key
, ...
);

CREATE TABLE child
( ...
, pid ... not null
    references parent (pid)
        on delete <action>
        on update ...
...
);

action can be any of:

NO ACTION
Produce an error indicating that the deletion or update would create a foreign
key constraint violation. If the constraint is deferred, this error will be
produced at constraint check time if there still exist any referencing rows.
This is the default action.

RESTRICT
Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

CASCADE
Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.

SET NULL
Set the referencing column(s) to null.

SET DEFAULT
Set the referencing column(s) to their default values.

See https://www.postgresql.org/docs/9.2/sql-createtable.html
If your foreign keys are declared as "on delete cascade" it is - in theory - sufficient to delete the root node. In practice, there may be physical limitations that restrict the total number of rows that can be deleted in one transaction.
If you want to experiment with the different actions you can use Fiddle. 9.5 is the oldest one available. If you are still on 9.2, consider upgrading to something more modern.


Does innodb_online_alter_log_max_size took memory from ram or Hard Disk

As I read the manual:

  Specifies an upper limit on the size of the temporary log files used during online DDL operations for InnoDB tables. There is one such log file for each index being created or table being altered. This log file stores data inserted, updated, or deleted in the table during the DDL operation. The temporary log file is extended when needed by the value of innodb_sort_buffer_size, up to the maximum specified by innodb_online_alter_log_max_size. If a temporary log file exceeds the upper size limit, the ALTER TABLE operation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large value for this option allows more DML to happen during an online DDL operation, but also extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log.

That refers to disk space, not RAM.  And it will grow as needed, not start out at the setting.  innodb_online_alter_log_max_size should perhaps be set to something less than the disk space available, but otherwise not too small.

innodb_sort_buffer_size defaults to 1MB; there is probably no reason to change that.  In certain operations, this much space is allocated in RAM.  Probably one of those will be allocated (in RAM) for each index on the table.


MySQL - CSV UPDATE (not INSERT) into existing table?

REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs.

Here is something else you can do.

Suppose your table is called name_city and it looks like this:

CREATE TABLE name_city
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

and you want to do the LOAD DATA INFILE.

Here are your steps to update the city based on id:

Step 01) Create another table to use for the import

CREATE TABLE name_city_import LIKE name_city;

Step 02) Drop the name column from the import table

ALTER TABLE name_city_import DROP COLUMN name;

Step 03) Perform the import into name_city_import

LOAD DATA INFILE 'somefile.csv' INTO TABLE name_city_import ...

Step 04) Perform an UPDATE JOIN

UPDATE name_city_import B
INNER JOIN name_city A USING (id)
SET A.city = B.city;

Step 05) Remove the import table

DROP TABLE name_city_import;

That's it.

Give it a Try !!!


I guess this is what you are looking for

 CREATE TABLE  newResult1
    ( name1 CHAR(3),
      name2 CHAR(3),
      name3 CHAR(3)
     )

    INSERT newResult1 (name1,name2,name3)
    SELECT 'yes','no','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'yes','yes','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'no','no','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'no','no','no'

Declare @countofTableRows INT ;

select @countofTableRows= Count(1) from newResult1;

select [name1yes], @countofTableRows - [name1yes]  [name1No]
       ,[name2yes], @countofTableRows -[name2yes]  [name1No]
       ,[name3yes], @countofTableRows - [name3yes] [name1No]
from
(
select sum(case name1 when 'yes' then 1 else 0 end ) [name1yes]
,sum(case name2 when 'yes' then 1 else 0 end ) [name2yes]
,sum(case name3 when 'yes' then 1 else 0 end ) [name3yes]
from newResult1
)Resetset;


How to use the result of an insert as foreign key in another insert?

I think you can use the LAST_INSERT_ID() function, with something like this:

INSERT INTO tableA
    (col1, col2, col3)
SELECT col1, col2, col3
FROM tmp_table
ORDER BY id ;

@first_id = LAST_INSERT_ID() - 1 ;

INSERT INTO tableB
    (fk_id, col4)
SELECT @first_id = @first_id + 1, col4
FROM tmp_table
ORDER BY id ;

The ORDER BY id is needed in both insert statements because we want the rows from the temp table to be inserted in the same order to both tables.

The first insert (into tableA) will give consecutive id values to tableA.id column. The first of those values is the returned by the following LAST_INSERT_ID() call.

The second insert then uses that value (and increasing it one by one) to provide the same values (as tableA.id) to tableB.fk_id column.


attach database - verify - detach from a script or a batch file

This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.

# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file names somehow and generate attach/check/detach statements
foreach($db in $databases) {
  $dbName = [regex]::Replace($db.Name, "(?i)\.mdf", "")
  $lgName = [regex]::Replace($db.FullName, "(?i)\.mdf", ".ldf")
  $sqlCmds += $("CREATE DATABASE {0} ON (FILENAME = '{1}') LOG ON (FILENAME = '{2}') FOR ATTACH;"  -f $dbName, $db.FullName, $lgName)
  $sqlCmds += $("DBCC CHECKDB ({0});" -f $dbName)
  $sqlCmds += $("EXEC sp_detach_db @dbname='{0}';" -f $dbName)
}

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Server=SERVER\INSTANCE;Database=master;Integrated Security=True"
$s = $sqlCmds[0]

# Try and execute the statements
try {
    foreach($s in $sqlCmds) {
      $ret = $conn.ExecuteNonQuery($s)
      $ret
    }
} catch [system.exception] {
  $_.Exception.ToString()
}


After mysql_upgrade to 5.7, optimizer not using index on large IN clause

You may not be aware of this, but MySQL optimizer has a different setting between versions

For MySQL 5.6, optimizer_switch looks like this:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on

For MySQL 5.7, optimizer_switch looks like this:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on

You could set the default for optimizer_switch for MySQL 5.6 in my.cnf and restart MySQL 5.7. Then, the optimizer's behavior will be the same as before the upgrade. For the new options, set duplicateweedout=off and derived_merge=off.

This is not a complete answer. You'll have to test this.


To select from all tables inside the schema in PostgreSQL

You can't do it directly as one can not write a 'normal' query if the table names are not known (ie. coming from some variable or subquery).  But you can build and execute a dynamic SQL statement for this.  For example, if you need the column 'name' from every table, you can do the following (inside a PL/pgSQL function):

FOR i IN SELECT table_name
           FROM information_schema.tables
          WHERE table_schema = 'your_desired_schema'
LOOP
    sql_string := sql_string || format($$
        -- some whitespace is mandatory here
        UNION
        SELECT name FROM %I
    $$,
    i.table_name);
END LOOP;

EXECUTE sql_string;

In this form it won't work however, since you cannot SELECT in plpqsql unless you do it into a variable.  You can either create a temporary table for this, loop over the results (in an other FOR loop), or - not using UNION - just return in every iteration, depending on your needs.

And, of course, this presumes that you want to select a single column (or more than one, but always with the same name and type) from all the tables.  If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order, with the same names).

Notes:

the format() function was introduced in version 9.1
I've omitted some mandatory elements of any PL/pgSQL block to keep it simple
the earlier version mentioned using a DO block.  The problem with it is that you cannot simply use SELECT there to return rows, as it was pointed out in another answer.


Postgres Server access restricted to only one database from list of databases

If you make changes to pg_hba.conf, you need to reload PostgreSQL.

But I think you'd be better off revoking the CONNECT privilege for that user. (Or possibly revoking ALL privileges.) Skeleton syntax is

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

Something like

REVOKE CONNECT ON DATABASE database-name FROM fishy_user;

In PostgreSQL, each role (user or group) can be granted permission to access different databases if that's what you want to do. See Database Roles


Use same Postgres WITH in multiple queries

You can chain multiple CTEs that perform an INSERT:

with x (id) as (
  insert into ...
  returning id
), t2_insert as (
  insert into t2 (id)
  select id
  from x
)
insert into t3
select id
from x;


SQL Server: what is the reason to favor customized database roles over fixed ones?

Fixed database roles provide permissions to the whole database. Custom roles come into play when you don't want to give users permissions to all of the database, just a portion of it.

For example, db_datareader provides SELECT permission to every table, view, etc in that database. For compliance reasons there may be some tables or views that HR staff should only be able to SELECT from and other staff shouldn't. That is usually where custom roles come into play.


Index for a Postgres query with a sort and an equality

Simplified query to make it readable:

SELECT *
FROM   "Follows" f
JOIN   "Users"   u ON f."followeeId" = u."id"
WHERE  f."followerId" = 169368
ORDER  BY f."createdAt" DESC
LIMIT  1000;

Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if and only if you can get an index-only scan out of this. Maybe not possible in your case.

CREATE INDEX follows_foo_idx ON "Follows"("followerId", "createdAt" DESC, "followeeId");

Generally, this query won't get lighning fast, since you have to fetch up to 1000 random rows from "Users". The key to performance will be:

Optimized table layout of "Users" to keep the table as small as possible, so fewer blocks have to be read and more of it can stay in cache.
Plenty of RAM and cache settings accordingly if your table "Users" is big.

It would also help to cluster the "Follows" table from time to time. Details:

Configuring PostgreSQL for read performance


Execute Mysql queries using the same file more than once

The problem is, you should not be trying to create an ever-growing set of queries in this manner.

Of course the first step in your example fails on the second run, since the imageFormat column has already been dropped.  There is nothing to restore imageFormatand there is no error handling to cope with the missing column.

I cannot fathom why you would want to build up your queries in such a manner.

If your goal is to record every query that you run, then insert the code into a table that records the time and the code that you run.

But do not expect to run all the commands without errors unless you have scripted the code to also deal with each exception that may arise.


How to list Oracle objects that have not been used in the last month?

There is no reliable way to do this. Even with auditing enabled, indirect object access is not recorded in some cases. For example if you have an ON DELETE CASCADE foreign key defined, and you delete rows from the parent table, the deletion of rows from the child table does not appear in the audit trail.

As far as I know, the only object type that has support for this, is the index. You can enable index monitoring by:

alter index owner.index_name monitoring usage;

Once you have enabled monitoring for an index, later you can check if the index was used in the view V$OBJECT_USAGE (when logged in as the owner of the index).

Starting with 12c, you need not to log in as the owner to query this, the new DBA_OBJECT_USAGE view can be used for this.

When you do not want the index to be monitored anymore, disable monitoring by:

alter index owner.index_name nomonitoring usage;


Postgres partition avoiding redundant column

Yes, the partitioning column must be part of the table.
I assume that you are using list partitioning. Now with list partitioning it is possible to have several values for the partitioning column in the same partition (FOR VALUES IN (1, 2, 27)), so one couldn't omit the partitioning column from the table.
Also, one of the key features of partitioning is that you can quickly attach and detach partitions. Now if the partitioning key were not stored, both attaching and detaching partitions would require a table rewrite.


MySQL: Which join is better between left outer join and inner join

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it.
In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that employee, while a left join (whose first table is employees) would show all of them, and nulls on the fields from work_log if there are not matches.

Image by C.L. Moffatt on Code Project
Again, performance is a secondary thing to query correctness. Some people say that you shouldn't use LEFT JOINs. It is true that a LEFT JOIN forces the optimizer to execute the query in one particular order, preventing some optimizations (table reordering) in some cases. Here is one example. But you should not choose one over the other if correctness/meaning is sacrificed, as an INNER JOIN is not inherently worse. The rest of the usual optimizations apply as usual.
In summary, do not use LEFT JOIN if you really mean INNER JOIN.
In MySQL CROSS JOIN, INNER JOIN and JOIN are the same. In the standard, and semantically, a CROSS JOIN is an INNER JOIN without an ON clause, so you get every combination of rows between tables.
You have examples of all semantic types of join on Wikipedia. In practice, in MySQL, we tend to only write JOIN and LEFT JOIN.


Because for many applications, they don't want individual user accounts connecting to the database. The users/passwords/rights/permissions is all handled at the application layer, and a single dedicated service account is used to connect to the database back-end.

As a DBA, I don't want to have to manage, at the database level, the 10,000 active users of some medium-sized public-facing web application, or maybe the 2+ million users of some suddenly popular app.

In a very real sense, this is a difference in philosophy between application developers and database developers/DBAs.

Many/most application developers are not going to want to pass responsibility for major aspects of app functionality and/or business rules down to the database layer. Instead, they view the database as a tool to simply store and retrieve data.

In some cases, this might be short-sighted; many RDBMSes do have awesome features that could make app dev lives much easier (row-level security, columnar indexes, filestream storage, etc.).

But some of these cooler features are only available in newer versions, and organizations aren't always quick to upgrade existing environments (see this chart from 2014).

And in other cases, handling those things at the application layer is preferred (and not just for database platform portability, I frankly think that claim is overblown).


How can the System databases in SQL Server 2016 be moved using Powershell?

The Restore-DbaDatabase in dbatools right now blocks the ability to restore the system databases. It is on the roadmap at some point to work out the process. The block in that command is why Copy-DbaDatabase would fail as well, and you can't move any system database by simple detach/attach method.

Overall it could be worked out to perform the whole task in PowerShell using dbatools commands with a mix of just "manually prepared" code around SMO.


How to do Partition for MySQL 100GB table in zero downtime?

You may use one of the online-alter-table tools: these are specialized scripts which simulate an ALTER TABLE operation, using TRIGGERs and shadow tables, and which allow for non-interruptive process. Thus, they will copy your table in small chunks into a shadow table even while you are modifying (inserting, deleting, updating) your table, synching the two.

They end with a very brief (up to a few seconds, typically sub second) lock, where your original table is thrown away and the "shadow" table kicks in to replace it.

Two such tools are:

oak-online-alter-table, as part of the openark-kit (disclosure: I'm author of this tool). Been around since 2009 and the first of its kind.
pt-online-schema-change, as part of the percona-toolkit; later arrival but more sophisticated and more tested.


Roles are not owned, but granted to users:

select grantee from dba_role_privs where granted_role = 'LANDING_R';

Object privileges granted to LANDING_R:

select * from dba_tab_privs where grantee = 'LANDING_R';

System privileges granted to the LANDING_R:

select * from dba_sys_privs where grantee = 'LANDING_R';


ORA-01422 Error Occurred When I Call Procedure with Number Parameter

Naming your parameter the same way you named your column is quite unfortunate.

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = ID;

In the above statement, typing id in lowercase or ID in uppercase makes no difference, it is case insensitive. Just because you named your PL/SQL parameter ID (in uppercase), does not mean the database will use it when you type ID (in uppercase) in the above. Even if the id in customers has unique values, the above SELECT returns all* rows from the table, because the id column precedes the ID parameter, and id always* equals ID (*except for NULLs).

You could just simply use a different name for your parameter, for example I usually use the p_ prefix for them, so your ID parameter could be changed to P_ID:

DECLARE
PROCEDURE update_salary(P_ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = P_ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   SELECT  salary
   INTO  c_sal
   FROM customers
   WHERE id = P_ID;
   IF (c_sal <= 2000) THEN
      UPDATE customers
      SET salary =  salary + 1000
      WHERE id = P_ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id=P_ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/


PostgreSQL9.6 Beta1：new setting remote_apply for configuration parameter synchronous_commit

When the standby initially receives the log records, the are written to the standby's pg_xlog directory.  So those records are there for safekeeping, but they have not yet been applied to the shared_buffers and the data files (and the pg_clog, etc.)  Until they are applied, anyone connecting to the standby cannot see the effects of those records.

It is kind of like the difference between getting a note from the post office saying "Your envelope has been delivered" (synchronous_commit=on) and getting a note from your bank saying "Your envelope had been received, opened, processed, and your instructions have been carried out and your account has been credited with the proceeds, have cleared, and are now available for withdrawal" (synchronous_commit=remote_apply).


Why would Mysql's Round(N.DD,1) sometimes show no digits after the decimal place when using the Perl DBI?

This was unrelated to MySQL and was due to a change in the Perl DBD::mysql packages from version 4.013 to 4.042. Essentially, DBD::mysql now attempts to coerce the MySQL datatype into a Perl datatype. So, previously, a float of 4.0 would be returned as a string '4.0', but now the same float will be returned as a numeric value and be displayed as '4' when printed (without additional formatting on the part of Perl.


Copying large amounts of data to another target tables

I recommend reading through the data loading performance guide, fully understanding your maintenance window and what kind of locks you can take during it, and testing on a different database with table sizes as close to the ones in the production. The question that you asked is pretty generic, but one big thing that you missed is that you should consider disabling nonclustered indexes before loading a large amount of data. It can be more efficient to rebuild the indexes after the data load is complete.

  Set Recovery Model to bulk-logged recovery model. After bulk importing
  the data, switch the recovery model back to the full recovery model.

Make sure that you understand the consequences of the switch before doing it. A recovery model of bulk-logged can help if it makes some of your queries be eligible for minimal logging. Under the right conditions the insert query can be eligible for minimal logging (target table is empty and you have TABLOCK). Read the data performance guide for full details. The update and delete queries will not benefit from the recovery model switch unless you disable and rebuild indexes as part of that process.

  Add table hit TABLOCK to release any locking during the executions.

This is one of the requirements for minimal logging within SQL Server but otherwise isn't likely to really help you.

  Set MAXDOP to 30 and change it back to normal.

What makes you want to use a MAXDOP of 30? The typical advice is to limit your queries to MAXDOP 8 and to consider going over 8 only for specific operations that can parallelize well such as index rebuilds. All of the queries that you listed in your question aren't likely to parallelize well. On SQL Server 2012 you aren't eligible for parallel insert. The part of the query that finds the data to be inserted is eligible for parallelism, but do you really want 30 parallel workers for that part? The delete and update parts of the queries are also single-thread.


When SQL Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer pool, correct?

No the memory is from inside the buffer pool. Generally memory required for such process would 'not be much' and this could be easily satisfied by buffer pool. Also note SQL Server might not grant all memory requested by the query in one go. If query requests memory which is of considerable amount SQL Server would grant minimum just to make sure query starts running.

  The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Buffer pool is always there starting from when plan is created to when query executes. Buffer pool in SQL Server 2008 R2 is sole one allocating memory for any request <= 8KB of memory page.

  If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type. My thought is set MAX Memory at 72 GB. Your thoughts?

No, reducing max server memory would be of help here. I would suggest you to look at query and the 'statistics optimizer is looking when it is preparing the query plan'. You have enough amount of RAM. Sometimes when you run a query with outdated stats it requests large amount of memory and ofcourse SQL Server is not going to grant all of it. It would provide minimum amount so that query at least starts running.

When SQL server creates a compiled plan, it calculates two memory grant parameters called "required memory" and "additional memory". The required memory is minimum memory SQL Server can give for sorting and hash operation and additional is what can be required by query to store temporary data which comes with huge sorts or spills. If Query start with minimum memory and requests more but SQL Server is not able to provide it all in on go query waits with wait type 'resource semaphore'. This does not means memory is less it means the statistics was outdated which forced SQL Server to create bad plan thus requesting more memory.

I suggest you read Understanding Query Memory Grants


MySql is not optimizing the query properly

The MySQL optimizer cannot optimize expressions in this format:

WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f))

It's not a matter of getting the indexes right -- it appears that it's just not implemented.

The optimizer does not understand that this is equivalent to...

WHERE (col_1,col_2) IN ((a,b))
   OR (col_1,col_2) IN ((c,d))
   OR (col_1,col_2) IN ((e,f))

... or ...

WHERE (col_1 = a AND col_2 = b)
   OR (col_1 = c AND col_2 = d)
   OR (col_1 = e AND col_2 = f)

There's Bug #35819, which I originally found in this article, which was in turn mentioned in the comments on this post.

Unfortunately, I didn't find those until I had already broken out the new Optimizer Trace in MySQL 5.6 and run a few test cases through it.  It seemed like a safe bet that if 5.6 couldn't handle it, then previous versions couldn't handle it.

It turns out that MySQL 5.6 indeed cannot handle it.  The "set in set of sets" construct seems to simply not be something the optimizer catches on to.  So in this case, it's not a matter of the optimizer choosing a full table scan over other plans -- the optimizer actually concludes that there aren't even any other possible plans to consider.

This is only true for multiple "row constructors" on the right hand side of IN.  For a single expression, the optimizer does its thing and realizes this is equivalent to col_1 = a AND col_2 = b:

WHERE (col_1,col_2) IN ((a,b))    # is optimized correctly
WHERE (col_1,col_2) IN (ROW(a,b)) # is an equivalent expression in MySQL

Interestingly, your original EXPLAIN suggests the unique index was not being used in exactly the way that you may have believed it was being used, at any rate.  It was only being used to find rows with the desired sale_id... not both values.

You'll notice in your original EXPLAIN that the key_len is shown as 4, meaning only the leftmost 4 bytes of the index are going to be examined -- sale_id, a 4-byte INT would be the leftmost 4 bytes in that index.  The Using where means the optimizer realizes that additional filtering of the rows returned from the range scan may be needed to eliminate any rows that don't satisfy the rest of the WHERE clause -- all rows with sale_id 502 and 504 are being retrieved via the index regardless of their value for product_id, and then the resulting rows will be subsequently filtered to satisfy the additional constraints imposed by the WHERE.

The optimum path is probably to stick with (expr and expr) or (expr and expr) or (expr and expr) in your where clause.  It's logically equivalent and the optimizer understands it.

Additional note, regarding some of your comments... in light of what I've discussed above, index hints will not help, because the optimizer appears unaware of the equivalence of the expression you've used to other expressions that it could handle... but as a point of reference, the reason it was syntactically invalid is that you have to use the name of the index, not the list of columns in the index.  You've called your unique index 'UNIQUE', so the way to use this as an index hint would be in this format:

USE INDEX(`UNIQUE`)


Cassandra: memtable_offheap_space_in_mb and memtable_cleanup_threshold

Memtable flush happens in 3 ways1. When memtable_offheap_space_in_mb size is exceeded.2. When commitlog_total_space_in_mb size is exceeded.3.When forcefully Nodetool flush commend is executed. In your case I'm guess for second reason that flush happened. For more you can check this. http://academy.datastax.com/courses/understanding-cassandra-write-path/understanding-memtables-and-commitlog


I have an error before I connect to the user Oracle 12c

Possible reasons:

You have SQLNET.AUTHENTICATION_SERVICES=(NONE) set in sqlnet.ora which disables operating system authentication based on group membership ($ORACLE_HOME/network/admin/sqlnet.ora, or $TNS_ADMIN/sqlnet.ora, it $TNS_ADMIN is set).
your user (oracle) is not the member of the group configured to be used for operating system authentication. Groups can be found in $ORACLE_HOME/rdbms/lib/config.c.
You have the environmental variable TWO_TASK set to something which is automatically added to sqlplus connection attemts as a TNS alias. For example if TWO_TASK is set to ORCL, sqlplus / as sysdba will become sqlplus /@ORCL as sysdba in the background, where operating system authentication does not work.


Configure sp_add_schedule to run on the first day of every X months/weeks

I believe @freq_recurrence_factor is the parameter you are looking for.


Duplexing Backup Sets

  ... It is not possible to duplex backup sets to the fast recovery
  area.

A similar effect could be achieved by setting the format to the location of FRA, but it will not be the same, as these files will not be accounted in the space usage of FRA.

configure device type disk parallelism 2;
configure channel device type disk format   '/fra/%U',   '/oracle_backup/%U';
configure datafile backup copies for device type disk to 2;
configure archivelog backup copies for device type disk to 2;
configure default device type to disk;

backup database plus archivelog;


How real-time apply works in logical standby oracle?

Does archived redo logs required for logical standby or not?
  Yes, it does.

https://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ls.htm#g105412

The archived redo logs (archivelogs, commonly) contain SQL statements to apply the changes being made to the database, as well as SQL statements to undo the work if a rollback occurs later on the primary's activity.


What is the difference between SQL Server 2012 SP4 vs SP3 with CU-10?

2012 SP4 contains all the same fixes as 2012 SP3 CU #10, and was basically just a rollup to release a final service pack. The only differences are going to be:

build number will be higher
longer end of life
potentially better chance of servicing for a security issue in the future
easier slipstreaming

Personally I would go with 2012 SP4.


The report server is configure to use SSL, and the report server is configure to use Basic Authentication.

Basic authentication is defined in the HTTP protocol and can only be used to authenticate HTTP requests to the report server.

If you use Basic authentication, use Secure Sockets Layer (SSL) to encrypt user account information before it is sent across the network. SSL provides an encrypted channel for sending a connection request from the client to the report server over an HTTP TCP/IP connection

If we configure the report server to use Forms (custom) Authentication over SSL, we will get the error "The username or password is not valid or you do not have permission to access this server. (Microsoft.SqlServer.Management.UI.RSClient)" while connecting to the report server from SQL Server Manager Studio(SSMS) using server alias.

To fix the issue, please use the full URL and authenticate with my domain\username and password.

For example,

  https://server.domain.com/reportserver


Postgresql SELECT item from JSONB array - array as top-level json object

You need to unnest the array elements to rows in the from clause:

select i.dt -> 'item1' as item1
from test_table t
  cross join jsonb_array_elements(t.data) as i(dt)


How to log the values that cause a unique constraint violation

You can use DML error logging

begin
  dbms_errlog.create_error_log( dml_table_name => 'MYTABLE' );
end;
/

insert into mytable
  <<your select statement>>
  log errors into err$mytable
  reject limit unlimited;

This will log all the rows that violated a constraint to the err$mytable table.


Greenplum : Inserting non-duplicate rows on unique constraint violation

First of all, I think you need to change slightly your query, because the WHERE NOT EXISTS, as it is now written, would be false as soon as your table has some row. You need to specify a WHERE clause:

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table a
      WHERE NOT EXISTS
             (SELECT 1
                FROM schema.my_unique_values m
               WHERE m.id = a.id AND m.value = a.value)

INSERT, either within a transaction, or as a single-statement-transaction, will be atomic. So, if one row fails, everything will fail. In any case, you cannot have phantom reads in SERIALIZABLE transaction isolation levels. If you're under that isolation level, make sure your NOT EXISTS (...) really has the properly written WHERE clause.

As of PostgreSQL version 9.5, there is a new clause that can be part of an INSERT and that looks exactly for your use-case. You could modify your querty to make use of ON CONFLICT DO NOTHING:

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table a
ON CONFLICT DO NOTHING ;

NOTE: The ON CONFLICT clause applies to each row, not to the INSERT as a whole.


admin user can not login to other databases after upgrading to mongodb 2.4

The short answer is, at least for the user you list: this is how it is supposed to work with the new user privilege model in 2.4+

To explain:

The userAdminAnyDatabase role means that the user you have now created will be able to grant access (for itself, or any other users) to any other database, hence the designation in the docs as a "super user".  However, that does not automatically grant that admin user read/write permission on all those databases (though it can bestow them upon themselves).  Hence that user can't authenticate against anything other than the admin DB, but by being able to do so, it can grant the relevant permissions elsewhere.

I think what you might be looking for is the readWriteAnyDatabase role, though I would recommend using that particular role sparingly (if at all) and instead grant access as needed, for greater security.

I can't really speak to the user permissions prior to removal, because they have not been listed, just the role of the new user.


How to grant user rights to create stored procedures in a custom schema

You can't change the ability to create procedures to only one schema by only granting permissions on the schema. (Assuming the user has no other rights.)

Why?

The user still needs the right  to create objects in the database, which are in this case procedures.

What you can do, is grant the user CREATE PROCEDURE rights, and then either change the owner of the schema to that user (more secure, see below for more information) or grant that user permissions on the 'IC' SCHEMA.

Only granting CREATE PROCEDURE does not allow the user to create procedures on  schema's such as dbo.

Create the login, the corresponding user and the IC schema

USE [master]
GO
CREATE LOGIN [TestIC] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [test]
GO
CREATE USER [TestIC] FOR LOGIN [TestIC];
GO
CREATE SCHEMA IC;

Grant Create procedure to the user

GRANT CREATE PROCEDURE TO [TestIC];

Either Change the owner of the schema (Safer Option)

ALTER AUTHORIZATION ON SCHEMA::IC TO [TestIC];

Or grant the user access to the schema

GRANT
     ALTER

ON SCHEMA::IC

      TO [TestIC] ;

Risk involved with granting rights on the schema

  Granting a user the ability to alter another user's schema gives that
  user the ability to SELECT, INSERT, UPDATE and DELETE rows in any
  table owned by the owner of that schema. This is called "Ownership
  Chaining" and it's what makes Views and Stored Procedures really
  simple ways to control security, as a user who has permissions on a
  View or Stored Procedure does not need to be granted permissions on
  the underlying Table, so long as the View/Proc is has the same owner
  as the Table.

Source

Take a look at the answer by @DavidBrowne-Microsoft for the origin of the quote, and to get more information on "ownership chaining" security risks when granting alter schema rights.

Test

EXECUTE AS LOGIN  = 'TestIC';

CREATE PROCEDURE IC.test
as
select * from sys.databases;

Result:

  Commands completed successfully.

This fails

CREATE PROCEDURE dbo.test
as
select * from sys.databases;

  Msg 2760, Level 16, State 1, Procedure test, Line 1 [Batch Start Line
  35] The specified schema name "dbo" either does not exist or you do
  not have permission to use it.

Undo impersonation

REVERT;


Insert into DB2 table a result from temp table

Since TEMP2 only exists in the scope of the statement, you can't reference it from another statement. You can insert directly like:

INSERT INTO T_STORAGE (CUSTOMER_ID, ORDER_ID)
WITH TEMP1 AS (
    SELECT CUSTOMER_ID
    FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 AND 300
), TEMP2 AS (
    SELECT CUSTOMER_ID, ORDER_ID
    FROM TEMP1 T1
    JOIN T_ORDER R1
        ON T1.CUSTOMER_ID = R1.CUSTOMER_ID
)
SELECT CUTOMER_ID, ORDER_ID
FROM TEMP2

If you need to use the content of TEMP2 in several statements, you can declare a global temporary table and insert to that first.

I'm not sure why you need the CTE though, this should be equivalent:

INSERT INTO T_STORAGE (CUSTOMER_ID, ORDER_ID)
SELECT T1.CUSTOMER_ID, R1.ORDER_ID
FROM T_CUSTOMER T1
JOIN T_ORDER R1
    ON T1.CUSTOMER_ID = R1.CUSTOMER_ID
WHERE T1.CUSTOMER_ID BETWEEN 100 AND 300


Looking at your current information, I see you posted the following info from SHOW INDEXES FROM:

Key_name
Seq_in_index
Column_name
Collation
Cardinality

The indexes you have are

course_id
complete_flag
pass_flag
reported_flag
student_id_2
reported_status
course_id_index

Give the read/ratio of 70%/30%, running a DELETE against a Row in this InnoDB table is not as simple as one would think. Why?

InnoDB's Index structure and Locking. All secondary indexes in an InnoDB table have a key back to the Clustered Index (a.k.a. gen_clust_index). Therefore, index contention (intermittent deadlocking) would always be a problem. I have written past posts about this drawback

MySQL deadlock - cannot restart normally?
Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

If you are always having this issue with DELETE on a single row, you may want to think about applying either a tombstone table or adding a deleted flag to your table. I wrote a post about this as well: Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios


What determines the default schema name in SQL Server?

In SSMS go to your database user and click on properties.  You will see a field there for default schema.

T-SQL version:

alter user YourDbUser
with default_schema = YourNewSchema


What is the best design for storing courses subscription data?

Both are OK designs, so now the problem is deciding between the two.  Here are some things that I think you should consider as you decide which one is best.

How similar are the non-essential columns in the Course Subscription and Lesson Subscription tables?  If they are very similar then records which contain only a course subscription in the Subscriptions table would not have a bunch of null columns to skip which apply only to lesson subscriptions and vice versa.  The more similar the columns are, the more you should lean towards using the Subscriptions design.
Can a student subscribe to both a course and a lesson at the same time? If the student does that, will the data for any shared columns between a course and lesson be different?  If the data is different, then you'll definitely need the CourseSubscriptions and LessonSubscriptions tables.  Otherwise, you should be fine with just a Subscriptions table.

Finally, if you go with one table... Think through how your output would handle a combined course and lesson subscription.  Is that two subscriptions or one?  Conversely, if you go with two tables and one action results in two entries, is that one or two?


What does enabling containment on server incur?

Instance-wide settings are typical when the database engine can optimize code paths if the setting is off.  In this instance, having contained database authentication = 1 means the database engine must perform checks on each database that it normally wouldn't need to, in addition to checking login requests against any database that has containment enabled.

The real impact on this is probably quite negligible; if you are forecasting that at some near-term future point you might need to enable containment for a specific database, you may consider enabling the instance-wide setting during some maintenance window.


Logical Replication: Schema does not exist while subscription

Connect to the database where Subscription is required.

\connect indsolv_dev


Need help with this sql query for Postgres DB for a Python flask application

I think there are several problems with your method. The first question would be: do you only want to return book_id for books that have a review? In your code:
book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()

If not mistaken, book_details is a tuple like:
(col1, col2, ...)

Second, why bother looking for the book_id in book_reviews if you already know what it is?
If you want to return the book_id for a particular ISBN, then:
book_id = db.execute("SELECT book_id FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()[0]

is sufficient. If you want to return the book_id only if it has been reviewed:
book_id = db.execute("SELECT book_id FROM books b WHERE isbn=:g and exists (select 1 from book_reviews r WHERE r.book_id=b.book_id)", {"g": isbn_result_isbn}).fetchone()[0]

One of the really nice things about python is multiline strings, so you could write it like:
sql = """SELECT book_id
         FROM books b
         WHERE isbn=:g
           AND EXISTS (
               SELECT 1
               FROM book_reviews r
               WHERE r.book_id=b.book_id
         )"""
book_id = db.execute(sql, {"g": isbn_result_isbn}).fetchone()[0]


How can I order the results from a DISTINCT ON query

Wrapping the query in a derived table is the obvious way to solve this:

SELECT *
FROM
    ( SELECT DISTINCT ON (item_id)
             item_id, data, timestamp, sort_order, ...
        FROM [this table]
       WHERE group_id = [some value]
         AND timestamp < [some value]
    ORDER BY item_id, timestamp DESC
   ) AS t
ORDER BY sort_order ;

If your original query is efficient and doesn't return too many rows, the additional sort will not add much cost to the query.

Besides DISTINCT ON, there are a few other methods that are often more efficient in this type of "greatest-n-pre-group" queries - but efficiency depends on many parameters, like targeted indexes and distribution of values (how many distinct item_id in the table, how many rows per item_id), Postgres version, etc.

Most notable method is using LATERAL subqueries. See these excellent answers by Erwin Brandstetter:

Optimize GROUP BY query to retrieve latest record per user
Optimize groupwise maximum query
Select first row in each GROUP BY group?
Query last N related rows per row
Optimise a LATERAL JOIN query on a big table


How to find the SQL statements that caused tempdb growth?

There are three DMVs you can use to track tempdb usage:

sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_usage

The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.

The following example query will give you allocations per session:

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

If you want to track usage over a period of time, consider collecting data with sp_whoisactive, as demonstrated by Kendra Little.


Can't get this PostgreSQL query to run faster

ST_DWithin

ST_DistanceSphere(x,y)<t cannot use your spatial index. ST_DWithin can, depending on the selectivity.  Instead use,

ST_DWithin(u.geo_point, l.geo_point, 1000);

Also, for testing purposes, you should remove the join to user_push_tokens and add it back once you get a better plan for the simpler query.


Query for View with unique entries from Database with repeated entries

The simplest solution is this:

SELECT DISTINCT city_name, country_name FROM yourtable

That takes the list of result rows and returns each one only once, even if it appears in the table multiple times. See "DISTINCT" in http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm fro details.

If you also want to know mor information about each row group, like how many hotels exist in each city you can use GROUP BY instead:

SELECT city_name, country_name, COUNT(1) AS Cnt
  FROM yourtable
 GROUP BY city_name, country_name

This again returns each city only once but additionally counts how often it appears in the table. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2065777 for more details on that.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Washington','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','New York','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','New York','USA');

Query 1:

SELECT DISTINCT city_name,country_name FROM hotels

Results:

|  CITY_NAME | COUNTRY_NAME |
-----------------------------
|    Seattle |          USA |
| Washington |          USA |
|   New York |          USA |

Query 2:

SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 GROUP BY city_name,country_name

Results:

|  CITY_NAME | COUNTRY_NAME | CNT |
-----------------------------------
|    Seattle |          USA |   3 |
| Washington |          USA |   1 |
|   New York |          USA |   2 |

If you want to create a view for each country you can just add a WHERE clause with the country name like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Hamburg','Germany');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','Paris','France');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','Paris','France');

CREATE VIEW US_Hotels
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'USA'
 GROUP BY city_name,country_name;

CREATE VIEW Ger_Hotels
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'Germany'
 GROUP BY city_name,country_name;

Query 1:

SELECT * FROM US_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Seattle |          USA |   3 |

Query 2:

SELECT * FROM Ger_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Hamburg |      Germany |   1 |


Can a SQL Server backup device hold both data and log backups?

You can write all 3 backup types to a backup device, since a backup device is simply a logical location pointer: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/define-a-logical-backup-device-for-a-disk-file-sql-server.
Full, Differential, and T-Log backups can all be written to the device. Think of it as an "alias" or "shortcut" to the backup path.

Further info based on the comments...

The backup device is really just an alias, so you don't have to type out "\server\drive\folder\subfolder...". Now, the different types of backups,

full = .bak

diff = .bak

t-log = .trn
would likely (NOTE: LIKELY) be in different folders/subfolders. You can store them all in the same folder, but I have found in the past that trying to differentiate between a full and a diff can be problematic, even if you name them differently. I always create the following folder structure for backups:

System

Full

Differential

TransactionLog

This way, I can easily write the backups to the correct location, and even more importantly, I can move/copy/purge these files easily based on this file structure. It's much easier in a restore situation to know where all the pertinent files will be located, rather than have to search through hundreds (or thousands) of files in a single folder.
So, in conclusion, for ease of administration, I would highly recommend creating a backup device for full backups (up to you whether you want to separate these out between system and user databases), one for differential backups and one for transaction log backups.
Also, I know it's common (even Ola's Maintenance Solution does this) to create a top level folder based on the instance name, then next level folder is the database name, and under that is each type of backup. This is a pet peeve of mine (yes, personal bias) because then traversing this structure is unnecessarily complicated. But, make sure to create something that will best fit your needs--and remember that the reason for backups is to recover from a problem...so, don't add to that problem by struggling to find the backups you need.


mysql workbench connection type tcp/ip over ssh with libpam-google-authenticator enabled

You can still use Google Authenticator with mysql workbench, but you will need to change how SSH and Google Authenticator work together.  When you login via SSH, you probably enter your password and then you are prompted for your Two-Factor pin.  You want to change this so that you enter your password and pin, one directly after the other, on one line as the password:

[my-password][two-factor-pin]

This will work with MySQL Workbench TCP/IP over SSH.  I modify /etc/pam.d/sshd to use the forward_pass option for the Google Authenticator module.  You will need to put a line like this at the top of /etc/pam.d/sshd:

auth required pam_google_authenticator.so nullok forward_pass

Google Authenticator will authenticate the pin, strip it from the password, and pass the password along to the other PAM modules.


If you are replacing all the data, then build new tables, test them, then swap.
If you have a Master-Master replication setup, then break the connection, do the ETL, test, then failover and reestablish the link.
If something else, please elaborate.

More details from me after more from you.