Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)

The setting is a "soft" max.  If a row exceeds the given value, the entry will be bigger than the max.  That is, "no problem".
If your desire is to avoid them ever being batched, you probably need the value to be less than 8K.
From the 8.0.14 Changelog:

The startup option --binlog-row-event-max-size now has a corresponding system variable binlog_row_event_max_size. The startup option and system variable set a soft limit on the maximum size of a row-based binary log event, with a default setting of 8192 bytes. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded.
The binlog_row_event_max_size global system variable is read-only and can be set only at server startup.

As for whether, or when, Amazon will provide a way to set it, I don't know.  File a bug report with them.
I would suggest that the value of the variable is only a minor optimization.
----- 2017-04-10 8.0.1 Development Milestone & 2017-04-10 5.7.18 General Availability -- Bugs Fixed -- Replication -----
Binlog_sender, which writes events from the binary log to a packet buffer and then sends the packet to the slave, did not reduce the size of the send buffer as expected. (Bug #24643036)


Active database files are constantly changing. Therefore, trying to simply make a copy of an active database file will not result in a valid backup file, but will generally result in a corrupted file; the data written at the start of the file will not necessarily be consistent with the data written later in the file.

So, most database systems have their own internal backup methods, to ensure that a backup file accurately represents the data as of a given moment in time. This backup would be atomic in the database sense - even if it takes hours to write out the file, the contents will be consistent with when the backup started, as though no changes were made to the file for all that time. However, changes are still happening to the file (hence, the online part of the description).

The only other way to get a consistent database backup file is to shut down the database. If the database is not active (not online, in other words), then no changes are happening, and the OS can copy the file without risk of corruption. This would be an offline backup.

Note that there are other differences between these two backup methods. An offline backup is normally going to be a simple copy of the database files; in theory, restoring the database would be as simple as replacing the current files with the backup files. An online atomic backup is usually stored in a different format, and has to be restored by running commands in the DBMS.


TM REQUEST Rollback SQL Server 2012 Always on Availability Group

According to this SO thread Session hanging with TM REQUEST and plan handle 0x0000 it indicates transaction manager servicing a DTC request which isn't supported on AGs (because if a failover occurs at that time it can kill your database - as documented by Microsoft but without instructions to reproduce it so we can go into more detail).

You can confirm this by opening the DTC manager program and seeing what the numbers look like.


Can you please post an example of your query? Without knowing what your query structure look likes, this will be difficult to fix. However here is an example that may point you in the right direction based off of your post title, in regards to ORDER and GROUP:

SELECT pkey,
       COUNT(int),
  FROM my_table
  LEFT JOIN this_table USING (key)
 GROUP BY 1
 ORDER BY 2;


Optimizer logical trees: Order of the different tree types?


You could try GRASS or QGIS., open-source GIS applications.  You might be able to get what you want from one of them.

EDIT: I see you're after a data modelling tool with support for spatial data types.  I can't see any specific reference with basic google-fu.  You may be stuck with building your database model using CREATE TABLE statements and then reverse engineering them into a modelling tool for documentation.

If you have Visio Professional, the database modelling feature of that has a passably good database reverse engineering feature that will work OK with anything that supports an ODBC driver.

Another option might be to get an extensible tool such as Sparx Enterprise Architect and frig the metamodel.  However, this might be more work than building your database by hand.


Postgres - syntax error near IF when using inside Execute

This is the solution thanks to Abelisto's comment:

CREATE OR REPLACE FUNCTION fn_tasks_after_update()
RETURNS trigger
AS $BODY$
DECLARE
    ri RECORD;
    is_changed BOOLEAN;
BEGIN
    FOR ri IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'tasks'
    LOOP
        EXECUTE 'SELECT $1.' || ri.column_name || ' <> $2.' || ri.column_name
        USING NEW, OLD
        INTO is_changed;

        IF is_changed THEN
            EXECUTE
                'INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
                VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');'
            USING NEW, OLD, ri.column_name;
        END IF;
    END LOOP;
    RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_tasks_after_update()
OWNER TO postgres;


How to group and count by two different dates?

The domain for "Date" (Date is a reserved word, let's call it event_date) is a union between created_at and updated_at.

select created_at as event_time, 'C' as event_type
from T
union all -- the legs are disjoint
select updated_at as event_time, 'U' as event_type
from T

From there we can extract the date and count each type:

select date(event_time) as event_date
     , count(case when event_type = 'C' then 1 end) as created_at
     , count(case when event_type = 'U' then 1 end) as updated_at
from (
    select created_at as event_time, 'C' as event_type
    from T
    union all -- the legs are disjoint
    select updated_at as event_time, 'U' as event_type
    from T
) as TT
group by date(event_time)
order by date(event_time);

Untested, but the principle should be valid.


Can see other user's databases in shared hosting

I'd talk to your hosting provider's technical support people about this and get them to explain to you the security configuration they have on the msdb database, which is where those details are stored.

By itself, the list of database names likely does not indicate a security risk.  I'd ensure I wasn't giving away any details in my database names.

If I had SQL Server Agent Jobs running where the output was logged, I'd probably be somewhat concerned about others being able to see that output, so I'd confirm that (a) I don't have SQL Server Agent jobs with output, and (b) no one else can configure SQL Server Agent jobs to run against my databases.


Multiple Selects With One Column Encrypted

I don't believe the issue is concerning the encryption. You are doing two sub-queries either of which could contain multiple rows, hence the error. If both source tables are the same, you can re-write as: -

SELECT COLUMNA AS 'NumberA',
CAST(aes_decrypt(COLUMNB,'key','IV') AS CHAR) AS 'Number2'
FROM TABLE


Postgres Refresh Materialized View Locks

REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock, so SELECTs may still run. Per the manual.

There seems to be an oversight with REFRESH MATERIALIZED VIEW (without CONCURRENTLY) as it's not listed there. A search of the source code shows that ExecRefreshMatView take an ACCESS EXCLUSIVE lock, as you might expect, so no other queries may run on the view.

I've submitted a docs patch to list it.


show statement_timeout as millis in postgres

You can query pg_settings directly:
select setting
from pg_settings
where name = 'statement_timeout'


A nonrecoverable I/O error occurred on file after DB Shrink

After I did the following steps below, I was able to produce a database backup again.

Step 1:

use Test_DB

GO

Step 2:

DBCC CheckDB

GO

Step 3:

Right click the Test_DB database then click properties.
Choose Option page
From Restrict Access option, select Single
Click OK and Open Connections dialog will appear. Click Yes if you want to close all connection connected to the database and change the database on single user mode.

-OR-

ALTER DATABASE Test_DB SET SINGLE_USER WITH NO_WAIT

GO

Step 4:

dbcc checkdb ('Test_DB',repair_rebuild)

GO

Step 5:

dbcc checkdb ('Test_DB',repair)

GO

Step 6:

ALTER DATABASE Test_DB SET MULTI_USER WITH NO_WAIT

GO


Is it possible to upgrade without downtime? As I know maintenance plan
  need to execute in one node. In my case can I execute it on the
  passive node without downtime?

The services will need to be restarted to pick up the new settings. You will also likely be instructed to restart your server. However, upgrading the secondary node and failing over is an option.

  What is the best backup method for 1 TB database. Backup/ Restore is
  enough for this size?

1TB is large, but large is relative. Following normal backup and restore procedures is the way to go. You want to make sure you are using page verification = CHECKSUM, backup verification & checksum, and if you are worried about the size of the backup then compression.

  Does it necessary to take backup and restore after up-gradation? I
  mean the possibility of data corrupt during the update of edition.

Dozens of things could go wrong. I could caution against an in-place upgrade which is what you are proposing. If something goes wrong, there is no "back button". It would be much wiser to set up new servers, cluster, etc and restore your backup to the new instance after testing is complete.

  Our server has 16 core processor and we have 4 licenses of 4 core
  server (4 x 4 = 16). Should we put only one license copy?

This question depends on what HA/DR you are going to, and what you plan to use the secondary for if you are using AGs. I say this because I assume there is a Enterprise level feature you want to use, or a few of them. However, licensing questions should be discussed with the vendor.


What is the JDBC URL for Oracle's Data Guard Fast-Start Failover?

You don't setup Fast-Start Failover in the URL.  It's something that you setup on the data source using the setFastConnectionFailoverEnabled(true) function call.

Per here

  An application enables Fast Connection Failover by calling
  setFastConnectionFailoverEnabled(true) on a DataSource instance, before
  retrieving any connections from that instance.

  You cannot enable Fast Connection Failover when reinitializing a connection
  cache. You must enable it before using the OracleDataSource instance.

For example

    ods = new OracleDataSource();
    ...
    ods.setFastConnectionFailoverEnabled(true);

Also, it mentions:

  FAST CONNECTION FAILOVER PREREQUISITES

  1.) The implicit connection cache is enabled. Fast Connection Failover
  works in conjunction with the JDBC connection caching mechanism.
  This helps applications manage connections to ensure high
  availability.

  2.) The application uses service names to connect to the database; the
  application cannot use service IDs (ORACLE_SID). Use a service name
  rather than an SID when setting the OracleDataSource url property.

  3.) The underlying database has Release 10 (10.1) or Release 10 (10.2)
  Real Application Clusters (RAC) capability. If failover events are
  not propagated, connection failover cannot occur.

  4.) Oracle Notification Service (ONS) is configured and available on the
  node where JDBC is running and on the Oracle RAC. JDBC depends on
  ONS to propagate database events from RAC and notify JDBC of them.
  If ONS is not correctly set up, then implicit connection cache
  creation fails and an ONSException is thrown at the first
  getConnection request.

  5.) The JVM in which your JDBC instance is running must have
  oracle.ons.oraclehome set to point to your ORACLE_HOME. For example:

  -Doracle.ons.oraclehome=C:\oracle\product\10.2.0\db_1

  6.) Set the FastConnectionFailoverEnabled property before making the
  first getConnection request to an OracleDataSource. When Fast
  Connection Failover is enabled, the failover applies to all
  connections in the connection cache. If your application explicitly
  creates a connection cache using the Connection Cache Manager, then
  you must first set FastConnectionFailoverEnabled before retrieving
     any connections.

  7.) The ons.jar must be part of the CLASSPATH for the application.
  The ons.jar can be found in the Oracle Client installation.

Edit:
One last thought, if there ever is a failover, you will need to handle the SQLException:

  ORA-17008: Closed Connection


Checking for Alwayson manual sync process for redo queue data

I have seen frequently few databases are having synchronization issue with secondaries and monitored the same and found redo data queue size is around in 2MB in secondary replicas.

This is indicative of two possible items (or both).

The system (storage, memory, cpu) isn't fast enough or have enough bandwidth to redo the log blocks.
REDO may be blocked by a user thread if it's a readable secondary.

  I have tried in GUI for resume data and do syncup but it's failed giving error.

Was it not synchronized? If not, that's a different problem. Pausing and resuming will potentially only make it worse, depending on the root cause.

  Do we have any code for apply Redo pending data on secondary replica databases manually.

No, there isn't a button to press to tell the REDO to run. It runs. Constantly. All the time.

It doesn't run when it's blocked and it runs very slowly when it doesn't have the hardware to run properly (primary may be more powerful than secondary, etc.).

If you look at the secondary, there should be an AlwaysOn Health extended events session running. Opening the XEL file in management studio (SSMS) will give you what has been captured. Look for any REDO BLOCKED messages. If there aren't any, run a perfmon on the system to check on IO latency and throughput, memory, and cpu utilization.


Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server

I ended up using F5 BIG-IP LTM Virtual Edition, because I didn't find any other free solutions, that fits my business requirements.


pg_ctl status responds with "available" during failed startup

The pg_ctl status command tests that the postmaster process (corresponding to $PGDATA) does exist from the point of view of the operating system. In the positive case, it reports that it's running (note that the word available is not used).

Essentially that's implemented by reading postmaster.pid and checking the result of kill(that_pid, 0).

It does not mean that this PostgreSQL instance does accept connections or is able to execute queries. To that purpose, better use psql or a script like Nagios check_postgres plugin.


Are resource_semaphore_query_compile waits causing 100% CPU on server?

Have you checked active tasks too, in addition to waiting ones? Check sys.dm_exec_requests for offenders with high cpu_time values. Look in sys.dm_exec_query_stats for queries with high total_worker_time.

An explanation for what is happening could be that a plan changed to result in a very CPU intensive execution plan with a large memory requirement (eg. hash joins, sorts). Parallel table scans and joins on non-aligned partitions come to mind... Few queries execute but they consume high CPU (the query exec and query stats views will reveal this). The queries require a large memory grant and cause other instances of the same query to block on the resource grant. A look in sys.dm_exec_query_resource_semaphores will reveal if this is true. Solution would be to address the plan stability, perhaps deploy plan guides.


How to denote separate records as "one and the same"?

Here is a suggestion   users_equal_user

create table  user_equal_user (
ueu_user_id int primary key ,
ueu_link_user_id int);

Select * from users where user_id in
(select ueu_link_user_id from user_equal_user where ueu_user_id = pass_in_user_id);

This will provide a many to many relation your looking for and be unlimited

EDITED OP asked how to deal with A=B=C=D to get all the other cross linked ids
so here is some untested code that should get you close

create or replace function get_associated_ids(puser_id int,
                                       puser_id_matched int[] )
 RETURNS table(user_id int, user_id_linked int)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$
declare
    _r record;
    _found int;
    _matched int[]; --use to block already processed ids,
                    --this is here encase  of a
                    --bad entry that creates a circular
                    --reference that would create an endless
                    --recursive loop

begin
        _matched = array_append(puser_id_matched, puser_id);
        for _r in Select ueu_user_id, ueu_link_user_id
                    from user_equal_user
                    where ueu_user_id = puser_id
                        and ueu_user_id <> any(puser_id_matched)
                        and ueu_link_user_id <> any(puser_id_matched) ;

            select ueu_user_id into _found  from user_equal_user
                     where ueu_user_id = _r.ueu_user_id;
            if found then
                    _matched = array_append(_matched, _found) ;
                    return query ( select * from
                            get_associated_ids(ueu_user_id, _matched ) );
            end if;
            user_id = _r.ueu_user_id;
            user_id_linked = _r.ueu_link_user_id;
            return next;
        end loop;
end;
$BODY$


Oracle redo log buffer cache vs redo log file

Redo Log Buffers: A log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in the redo entries. Redo entries contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations. Redo entries are primarily used for database recovery as necessary.

Redo Log: The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

for more information about database structure please refer to https://docs.oracle.com/en/


Inserting fetched records into new table

Instead of a SELECT you just follow the CTE with a SELECT INTO:

;WITH cte AS
(
   SELECT x = CONVERT(int, 1)
)
SELECT x INTO dbo.TrashMe FROM cte;

With your new query:

; with dub as
(
  select docnumber, sum(amount) summedamount
  from stg_invoice_cust_vw where year(crmcreateddatetime)=2017
  group by docnumber
  having count(docnumber)>1
)
select
  dub.docnumber,
  dub.summedamount,
  sup.slamount SLAmount,
  (dub.summedamount - sup.slamount) diff
-----------
INTO dbo.NEW_TABLE_NAME --<--------------------------- *** HERE ***
-----------
from STG_INVOICE_SUP_VW sup
inner join dub on dub.docnumber = sup.docnumber
where (dub.summedamount - sup.slamount) <> 0

Note you can only do this once... the next time you run this code, you will get an error that NEW_TABLE_NAME already exists.


Can declaring function volatility IMMUTABLE harm performance?

Yes, it can harm performance.

Simple SQL functions can be "inlined" in the calling query. Quoting the Postgres Wiki:

  SQL functions (i.e. LANGUAGE SQL) will, under certain conditions, have
  their function bodies inlined into the calling query rather than being
  invoked directly. This can have substantial performance advantages
  since the function body becomes exposed to the planner of the calling
  query, which can apply optimizations such as constant-folding, qual
  pushdown, and so on.

Bold emphasis mine.

To enforce correctness, there are a number of preconditions. One of them:

  if the function is declared IMMUTABLE, then the expression must not invoke any non-immutable function or operator

Meaning, SQL functions using any non-immutable functions but still being declared IMMTUTABLE are excluded from this optimization. Triggered by these related answers on SO, I have been running extensive tests:

How to aggregate data from multiple years on MM-DD, ignoring year
How do you do date math that ignores the year?

Basically comparing these two variants of a simple SQL function (mapping dates to an integer, ignoring the year which doesn't matter for the purpose):

CREATE FUNCTION f_mmdd_tc_s(date) RETURNS int LANGUAGE sql STABLE    AS
$$SELECT to_char($1, 'MMDD')::int$$;

CREATE FUNCTION f_mmdd_tc_i(date) RETURNS int LANGUAGE sql IMMUTABLE AS
$$SELECT to_char($1, 'MMDD')::int$$;  -- cannot be inlined!

The Postgres function to_char() is only STABLE, not IMMUTABLE (all overloaded instances of it - for reasons beyond the scope of this answer). So the second one is fake IMMUTABLE and turns out to be 5x slower in a simple test:

db<>fiddle here

This specific example can be replaced with the equivalent:

CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
$$SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int$$;

Would seem more expensive with two function calls and more computations. But the IMMUTABLE label is true (plus, the used function is faster and coercing text to integer is more expensive, too).

2x as fast as the faster variant above (10x as fast as the slower). The point being: Use IMMUTABLE functions where possible, then you don't have to "cheat" to begin with.


First of all, use one alter statement to do all of adding two columns and the compound index. ALTER TABLE table1 ADD col1 INT, ADD col2 CHAR(2), ADD INDEX (col1, col5);. More about ALTER
Archiving old records is a good idea. If it is very rarely needed, then some slowness is affordable (I think).
Depending on your engine that you use, dumping the data and importing it to a new table maybe slow if you can't disable the keys. If you drop all indexes and recreate them, that is slow again. If you can afford some down time, any method is ok.
If the tables keeps growing, and you don't want to archive, you may consider "Sharding" the table, but that would require some extra efforts at development level
Last idea, which I don't strongly recommend unless you can't afford downtime, is to create a new table with a primary key and the new column that you want to add. Use this PK to join the original table.


Postgres, how insert "0" value into serial?

Sure, just do it. The counter only gets incremented when you call a Sequence Manipulation Function. In the case when you don't explicitly specify a value, the DEFAULT triggers a call to nextval(sequence_name)

CREATE TABLE foo ( id serial );
INSERT INTO foo (id) VALUES (0);

PostgreSQL 10+

It should be noted, that serial is the old and antiquated method of doing this. You should be using the standardized IDENTITY COLUMNS

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
INSERT INTO foo (id) VALUES (0);

See also

What are Identity Columns?
CREATE TABLE (syntax for IDENTITY COLUMNS)
CREATE SEQUENCE (which has the options for the columns)


How do I prove this relation is in the `3FN`?

A relation is in third normal form when for all the FDs X -> Y of a cover F of the dependencies of the relation we have that either X is a superkey, or Y is prime. So we can prove that R is in 3NF if we can show that this condition is true for all the dependencies of F.
The relation has three candidate keys (you can easily see that the closure of each of them contains all the attributes of the relation):
AB
BC
BD

So the only non-prime attribute is E. So, BC->AE has a determinant which is a superkey, while A->D and D->C have a determinate which is a prime attribute.
So all the dependencies satisfy the condition and the relation is in 3NF.


Import daily csv with varying available headers

I would do it like this:

There exists some working table where the data must be placed into. It contains columns for all data which may present in CSVs, with proper names and datatypes.

Create names table with 2 columns: column name used in CSV; according column name in working table. Like

CSV_name    | table_name
------------|-----------
ID          | id
user_id     | id
Employee_id | id
name        | first_name
FirstName   | first_name
...         | ...

Each column is defined as unique.

Create a procedure which:

Loads first row from CSV file (the file is renamed to some pre-defined name or its is got as a parameter), parses it to separate names, then adds them into CSV_name ignoring duplicate errors, then counts the amount of names in names table with NULL in table_name.

If there are CSV_name rows with table_name IS NULL then procedure types a message and breaks. Operator looks names table and fills table_name column with according values, then calls the procedure again.

If all CSV_names have according table_name then procedure creates temporary table and loads the whole data into it. After data loading the procedure builds INSERT INTO query text and copies the data into working table. Alternatively correct importing query text may be created/executed in one step.


How do I get the remote hostname of the server?

I don't think it's possible. I think what you're looking for is gethostname(). That's not called anywhere in the PostgreSQL source code. Nor is utsname or any kernel interface into this.

What is called is getaddrinfo that provides a CANONNAME but that's essentially just your own method of addressing the server not the "symbolic hostname". If you need the hostname, you'll have to build a C-extension that does just that. Or, use a third party procedural language, like perl plperlu, that has that support (through Sys::Hostname or Net::Domain, see perldoc -q hostname); or, alternatively through javascript with plv8 that has os.hostname().

Another method would be simply reading the file /proc/sys/kernel/hostname.

It's a database, not a remote administration utility. You can't really fault it for not doing this.


Query with ROWNUM returns differents rows on every execution

ROWNUM is evaluated before ORDER BY, that is not how ROWNUM should be used. To use ROWNUM properly, use a query like this:

SELECT * FROM
(
  SELECT DISTINCT
      t1.ZRESUBDIV,
      t1.SUBDIV_NAME
  FROM M_STAGE_WP_PFUNCT_LOC t1
  ORDER BY t1.SUBDIV_NAME
)
WHERE ROWNUM <= 200;

But this is old. Since you are on 12c, use this:

SELECT DISTINCT
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
ORDER BY t1.SUBDIV_NAME
FETCH FIRST 200 ROWS ONLY;


In mysql how to identify what caused a table lock

The best way to do this is to enable the slow query log. It will log in a file queries that took more than X number of seconds to perform, as well as, the lock time it took.

You need to edit your /etc/my.cnf and add the following lines (you'll need to restart your mysql service for the changes to take effect):

slow_query_log = 1
slow_query_log_file = /var/log/slowmysql.log
long_query_time = 2
log-queries-not-using-indexes

A small explanation of each:

The first line enables the log.
The second tells the server where to store it.
The third one tells it how many seconds to wait in order to log the
query. I recommend you start with a high number (say 10 seconds),
and once your queries get optimized, reduce that number
The fourth line will enable you to log every query not properly
using an index. Although this might log quick running queries, it
will also help you spot which queries are not truly optimized using
the correct indexes.


Situations that come to mind are:

Having to Restore the Primary due to
a bad code release  / user error
Loss of Network Connectivity for extended periods of time or due to maintenance activities
DNS Issues / Misconfiguration of Aliases in SQL Server Configuration Manager

Microsoft also has quite a bit of information in an article appropriately named "Possible Failures During Database Mirroring"


Answering my own question, I could say the MySQL in the SQL node was running so when the MySQL Cluster auto installer was trying to start the the SQL node it failed so by stopping the MySQL in the SQL node and starting the cluster the problem solved.


Part of the transaction log, per database?

Yes that is correct. Undo happens when database goes through crash recovery or recovery. There are 3 phases of recovery

Analysis
Redo
Undo

The undo phase is where transactions that are not committed are rolled back so that they don't end up being in database when it comes online. Yes this information about transaction which is not committed is present in transaction log. To understand more about this please read Logging and recovery in SQL Server

Tempdb is always in simple recovery model but "Minimal Logging" is followed in tempdb. This means ONLY information to rollback transaction is there no information to rollforward the transaction is present. Crash recovery does not happens in Tempdb.

Further Reading.

Understanding How Restore and Recovery of Backups Work in SQL Server


Data Warehouse attribute that describes its timeframe?

I would go with "the date range encompassed by the data warehouse is x."

This eliminates all possible confusion about the intent by deliberately using words that are understandable by non-technical people.


One way to do this will be to monitor the writes/sec for weeks or a month and then come up with a number based on what the max values that you are seeing.  Obviously this is a rough guide, and as your workload changes you would have to adjust the threshold as well.

You could also use disk benchmarking tools to test the throughput of your disk subsystem.  diskspd is something which we use.  Please also note that writes/sec is dependant on the block size that is used.  Based on what the drive is used for(data or transaction log)  and also based on the type of workload, SQL Server could use different.  You can read about SQL IO sizes here.  Obviously if you run this on a production system, you might have a performance impact.

A better performance counter to alert on would be sec/Write or sec/Read.  You can read about it here and here


Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)

The parameters in the array may be transformed into a table
to be be joined against the rest. This transformation is
done with the unnest function and the WHERE conditions can then be expressed as
JOIN clauses.
This could also be done by joining with generate_subscripts($1, 1) but that leads to more complicated syntax for no apparent benefit.
select ...<same as your query>...
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
join unnest($1) params ON (p.sku=params.sku AND p.manufacturer=params.manufacturer)
WHERE location_id = $2
group by se.created_at
order by se.created_at

Note: this query leaves the left outer join as they are in the original query because the question is not about them, but it seems they should be inner joins instead.
The reason is that WHERE clauses like  p.sku = '12345' and p.manufacturer = 'CompanyA'
imply that these columns in p cannot be NULL, but that negates the reason why you'd use an outer join versus an inner join, which is having NULL columns added for non-matching rows from each table at the right side of each left join.
In short, if you're not sure why this query uses left outer join, consider replacing them  with join.


Your query is running an infinite loop because it always finds more records that can be added. That's because the rows you're adding in the second half of the query (after the UNION ALL) are the exact rows that are in the first half of the query. You won't get an infinite loop if you use the columns from the @MappingPartOne table, not CTE, in the SELECT list. You won't get what you want but you won't get an infinite loop.

If I'm understanding you correctly, when a row's Property_Key is NULL, you want to use the same Property_Key value as used by the row's parent - in other words, the nearest ancestor's Property_Key where that value is not NULL.  For example: if the row with Functional_Location_Key = 4505 had Property_Key = 9978 instead of NULL, its descendants (Functional_Location_Key 5066, 6121, and 5068) would display Property_Key = 9978.

We need to make several changes to your query to accomplish this:

First, as previously noted, the second part of the CTE should output the columns from the @MappingPartOne table, not from CTE. This, however, gives you multiple copies of your rows.
To get a single copy of each of your rows, you need to start the CTE differently. You're initially explicitly providing a list of the rows you want to see in the first part of the CTE. However, then, in the second part, you're bringing in the descendants of each row from the previous iteration. Since you've included rows in the first part that are the descendants of other rows you've included, you get duplicates as you iterate through the rows.

Instead, you need to specify just the ultimate parent rows - rows that aren't descendants of any other rows. That means, the rows (in the present case, row) where Parent_Key is NULL.
Now, the next step is to check if a row's Property_Key is NULL, and (if so) to use the same value as its parent did. It doesn't matter if the parent's Property_Key was also NULL, because then it would be using its parent's Property_Key. During each iteration through our data, we have the current row (from @MappingPartOne) and its parent (from CTE). So, instead of simply using the @MappingPartOne.Property_Key, we check if that's NULL. If it's not, we use it - if it is, we use CTE.Property_Key.

So, this gives us the following query:

WITH CTE AS (
    SELECT
        m.Functional_Location_Key
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
    FROM #MappingPartOne m
    WHERE m.Parent_Key IS NULL

    UNION ALL

    SELECT
        b.Functional_Location_Key
        ,b.Parent_Key
        ,b.Level
        ,COALESCE(b.Property_Key, t.Property_Key) as Property_Key
    FROM CTE t
    JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
    Functional_Location_Key
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
;

You can see this in action at this SQLFiddle link.

(If you haven't used it before, COALESCE() works like the ISNULL() function. It has two potential advantages: you can provide more than two arguments, in which case it will return the first non-NULL argument, or NULL if all of them are NULL; and, it's part of the ANSI standard, and thus more likely to be portable to other SQL dialects.)

If you change the WHERE clause criteria in the first part of the CTE to pick a specific row by Functional_Location_Key, then you can pull up any given row and its descendants, ignoring the rest of the hierarchy, in case that's needed.

Per your comments, you seem to want to show the Property_Key from the highest level ancestor with a parent (not from roe 1393, but from row 1399) as the default for all its descendants with a NULL Property_Key.

To do this, we need only need to introduce an additional column to our CTE:

WITH CTE AS (
    SELECT
        m.Functional_Location_Key
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
        ,NULL as Top_Property_Key
    FROM MappingPartOne m
    WHERE m.Parent_Key IS NULL

    UNION ALL

    SELECT
        b.Functional_Location_Key
        ,b.Parent_Key
        ,b.Level
        ,COALESCE(b.Property_Key, t.Top_Property_Key) as Property_Key
        ,COALESCE(t.Top_Property_Key, b.Property_Key) as Top_Property_Key
    FROM CTE t
    JOIN MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
    Functional_Location_Key
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
;

The new column is Top_Property_Key. Initially, we're looking at the top level parent. You've said we don't want to use that as our default, so we set the value for this one row to NULL. In the second part of the CTE, we want to use the established Top_Property_Key as our default if it's not currently NULL, otherwise we want to use the current parent row's Property_Key. Once we have a row where Top_Property_Key is assigned a non-NULL value, it will retain that value for all its children.

Finally, we change the COALESCE for each row's Property_Key to use our new column, t.Top_Property_Key, instead of t.Property_Key (the immediate parent's property key value).

Here's an updated SQLFiddle link with the new functionality.

NOTE: If you decide you want to use the very top parent's Property_Key as the default for all its children, then set Top_Property_Key to Property_Key instead of NULL in the first part of the CTE:

        ,m.Property_Key as Top_Property_Key


How to migrate SSAS and SSRS from SQL Server 2008R2 to SQL Server 2012?

Unless you have data in your SSAS cubes that no longer exists in your source (if this is the case correct that going forward!) then you can simply install a new instance of SSAS then deploy and process your SSAS solutions from SSDT (the 2012+ replacement for BIDS).

For SSRS if you have a basic install you can do the same thing - simply install the new server and deploy your reports. If you have a lot of reports, folders, security setup, schedules etc. then you should migrate.

Here are the MSDN Documents outlining the steps to migrate and the considerations prior to doing so.


The main difference compared to the architecture of Data Pump utility exp / imp and expdp / impdp are always run on the DB server and not on a client. Then the files will always on the server where and active an Oracle database.
The advantages of the data pump is performance (eg. Parallel) and the presence of new features' (eg. Migration direct SQL * Net, native compression, remap).
See this  Oracle white paper(Upgrading to Oracle Database 12c)  on page 14.
You can also see the Database Utilities manual.


There may be a problem with that design.  As you search for the next batch to delete, you may be skipping over previously checked rows.  So, each subsequent delete will be slower.  The time to finish is (sort of) quadratic.

Two better techniques:

If there are only a "small" number of rows to keep, then build a new table with just them; then swap tables.  Time: linear.  Advantage:  The result is already defragmented.
Walk through the table 1K rows at a time.  Use the PRIMARY KEY for tracking where you are.  And remember where you left off.  Time: linear.

Both of those techniques (and others) are discussed here.


The SQL Agent uses whatever is assigned as the login on the Windows Service.
To access Windows services and see, click the start menu and type 'Services'.

Locate the SQL Agent and view the properties.
If the account is the Local System, then it is actually using the credentials of the machine.  You confirmed that already when you saw that the DOMAIN\SERVERNAME$ was returned.

(That is the account for the machine that it's hosted on.  All PC's have a local system account and it gets added when SQL is installed.  I believe the system account inherits sysadmin privileges which is why you probably don't want to leave it as the default. NT AUTHORITY\SYSTEM I believe is how it gets added to the logins in SSMS.)

If you want to assign different permissions which is a good idea, you can use a different login, usually a service account or master service account.

Alternatively create a credential in SQL server assigned to an account that has permissions.  Then create a proxy in the SQL Agent and you can use that to run the job.

According to MS best practices is to always assign the login credentials for the service accounts using SQL Configuration Manager.  While I've never run into a problem doing it other ways, if MS says so, it's probably correct.


How to replace (SIMILAR TO + regular expression) with LIKE or ~ in PostgreSQL?

LIKE is much less flexible than SIMILAR TO.  In general there is going to be no formulaic way to downgrade from SIMILAR TO to LIKE.  Based on your example, it might take like 34 (4*4 + 3*3 + 3*3) different ORed together LIKE to accomplish this.  For example, your first expression in the OR branch gets to pair 3 alternatives plus the empty possibility (so 4 total) at the beginning with another 4 at the end, so that would expand to 16.

  I read many advises to avoid using SIMILAR TO and to replace it with simple LIKE for performance issues

This advice would only apply if there is a simple like which does the same job.  But in general, tuning should be based on evidence, not on rumors.  So to start with, do you actually have a performance problem?


Drop tables but space not claimed in postgres 12

You can use TRUNCATE first before dropping and that'll automatically reclaim the space for you. See more information in this StackOverflow: PostgreSQL drop table command is not reducing DB size
Though this shouldn't have to be your process normally with dropping tables, it might help clue you in on why the space isn't being reclaimed.


Not Able to Increase InnoDB Buffer Pool Size in Mysql

Since the OS you are using is 64 bit, you need to make sure MySQL version you are using is 64 bit not 32 bit, how to get those information:

show variables like 'version_compile_machine';

If MySQL version is 32 bit then you will have results like

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| version_compile_machine | i386  |
+-------------------------+-------+
1 row in set (0.00 sec)

Note MySQL 32 bit has limitation, you can not increase the memory to more than 1.8 to 2G.

If MySQl version is 64 bit then you will have the following results

    +-------------------------+---------+
    | Variable_name           | Value   |
    +-------------------------+---------+
    | version_compile_machine | x86_64  |
    +-------------------------+---------+
    1 row in set (0.00 sec)


Is it possible to drop data from OLAP database (and to keep definitions)?

Yes, it is possible.

The easiest way would be to go with Management Studio to the proper SSAS server, right click on the database -> Script Database as -> Create to -> New query window.

That will generate a .XMLA file that creates the complete definition of your SSAS database. That's without the prepared data inside. When you need your database and its data, you just run the XMLA file, it will create the OLAP db, and then Process that database. This will add the data.

But, in theory at least, you should have a Visual Studio project where that SSAS database was created, so if you have it and it's the latest version, than you don't need the OLAP database at all, you just redeploy and process the data.


If you mean getting MySQL to engage multiple cores, you have come to the right place.
In order to get MySQL use multiple core for InnoDB, you must use the new settings that came with the InnoDB Plugin starting with MySQL 5.1.38.
Rather than reinventing the wheel, here are my earlier posts on this subject for InnoDB

Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

Give it a Try !!!


Data replication between SQL Server 2016 and SQL Server 2008 R2 is valid?

I'm afraid Replication between 2016 and 2008 is deprecated per Microsoft.
MSDN Link

  Replication is supported if each SQL Server endpoint is within two
  major versions of the current version of SQL Server. Consequently, SQL
  Server 2016 does not support replication to or from SQL Server 2008 or
  SQL Server 2008 R2.


Unable to wrap a simple CTE in plpgsql loop

First: user is a reserved keyword, you need to use double quotes if you want to use it as a table name: "user" But I strongly suggest you find a different name.

To answer your immediate question: just remove the final select from the CTE - that's the one cause the error (as you need to store the result of that select somewhere).

CREATE OR REPLACE FUNCTION ins()
returns void as
$BODY$
declare
   loopid integer;
BEGIN
  FOR Loopid  IN 0..10 LOOP
    with new_user as (
      insert into "user"
      values(default)
      returning id
    )
    insert into user_property (property_of)
    select id
    from new_user;
  END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Note that the language name is an identifier, you should not put it in single quotes.

However, you don't really need a function for that:

with new_users as as (
  insert into "user" (id)
  select i
  from generate_series(1,10) --<< this is the number of rows.
  returning id
)
insert into user_property (property_of)
select id
from new_users;


How do I use the POSIX character classes upper and lower to search text in MySQL?

From the article on mysql REGEXP:

  REGEXP is not case sensitive, except when used with binary strings.

A simple solution would be to use the BINARY operator:

SELECT * FROM article_body WHERE body_text REGEXP BINARY "FREE";

As to why your example doesn't work, the way I interpret it is "Any string with one or more uppercase letter followed by FREE", but of course FREE is case-insensitive there.


SQLCLR assembly throws error when multiple queries run simultaneously

The type of error that you are getting (i.e. Collection was modified;) is most likely caused by sharing the collection across multiple threads. If the variable holding the collection is defined as static, then that variable is not thread-safe since all sessions share the same App Domain for all Assemblies in a particular Database that are owned by the same User.

And, judging by the class name of StringMetrics, the namespace of SimMetricsMetricUtilities, and the input parameter names, I am guessing that you are using the SQLCLR port recommended in the "News" section of the SimMetrics repository (please note: I am being intentionally vague in not linking to this "News" page or mentioning the title of the blog post containing this particular SQLCLR port since, in its current form, it should not be used due to incorrect usage of the SQLCLR API in a few areas). The main problem with that code (given just a quick review) is that it instantiates static classes for each algorithm. Hence you are sharing those collections across sessions, and even with the algorithm being quick, you are now calling the methods frequently enough to have those sessions (i.e. threads) stepping on each other. Lucky for you that Generic Lists throw these errors.

You might be able to fix this by:

getting rid of the static class constructor
getting rid of the static readonly variables
adding the following line just above the return in the QGramsDistance method in the StringMetrics class:

QGramsDistance _QGramsDistance = new QGramsDistance();

Yes, it needs to be instantiated with each execution of the SQLCLR UDF.
In the SqlFunction attribute, change IsPrecise to be false. Using the FLOAT datatype is specifically imprecise ;-).

Changes #1 and #2 only need to be done once to cover the functions/methods for all of the algorithms. Change #3 should be done across the rest of them using the class name that is appropriate for each particular method. And Change #4 should be done across all of those SqlFunction methods, and is the exact same change across all of them.


Using timestamp plus integer as primary key

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak.

The reasons to have auto incrementing surrogate int PK's are many:

You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works well with little overhead. If you do it the way you suggest, it causes much more overhead with the need to have a UDF to create the key which involves a lot more CPU cycles to create than an auto increment int.
They are the most efficient storage solution. The way you suggest would require a varchar(20)/char(20) or so meaning 20 bytes per row just for the key. A normal int is only 4 bytes. It might not seem like much, but put 100 million rows in there and you increase the size by around 1.5GB. Not cool. Then you have to put it as the FK in another table, another 1.5GB. Or in 3 other tables and your DB is needlessly almost 5GB larger. You get the idea...
Tables should really be linked together with something the users don't care about or understand. This is so that you have a low granularity of control as a DBA, a value that you can refer to which the users know nothing of which becomes important when doing highly granular changes, fine tuning the table or managing the data structure. I can just tell you this from experience of working with DBs that have composite keys like you suggest. They become frustratingly annoying and you are always tempted to just put in an auto incrementing int and be done with the complexity and frustration. Imagine for example, you wish to quickly insert a whole bunch of records that have come from a different source. With an auto increment, easy with minimal hit on server resources. With a composite key... you have to run a UDF which creates the key for each insert... and in your example the randomiser for 100 keys per ms might not be enough, SQL Server may work too fast for you and give you the same ID for 2 records. Damn, you need a different approach which doesn't violate the unique constraint. Or imagine the user wishes to start "reusing" a key for some reason (it happens, sometimes on some mistaken idea of "recycling making the database faster"). Users end up trying to tune the DB themselves, a recipe for disaster. Telling them (and having) behind the scenes things occurring that they don't understand gives a level of separation that often avoids the situation. They don't try to optimise anything, taking their sticky little fingers out of your side of the business, meaning you can optimise the way it should be done. Much like a teenager with no idea attempting to change the electronic timing of a modern car with their smart phone and a hammer... a job much better done by a professional with the right knowledge and tools.
SQL Server expects int PK-FK auto increments and is optimised for it. It will mean faster processing overall due to FK checks when inserting records.
Lastly, I find natural keys to be absolutely fine with small databases with few users that need little optimisation and management. They can also make for cleaner looking queries (by using the composite key in WHERE statements) when the queries are simple, meaning you have less joined tables etc. But as soon as the database starts getting large and complex... managing auto incrementing ints surrogate keys becomes a lot easier.

My final thought: Composite keys are FINE for lookup tables which have a low number of members (meaning a shorter composite key and therefore low space requirements) that are not subject to a lot of change (meaning low management overhead). An example is two letter state codes in the US. They will mean potentially one less join and over many stored procs, this could make a small but measurable performance improvement.


If all clocks are sync'd, I would suspect high write load on the Master. Why?

The Master is responsible for doing two things:

Executing the SQL
Recording the SQL and its timestamp

If there is any intermittency between steps 1 and 2, this could account for what seems to be replication lag.

Since there are stable servers, I suggest resetting the relay logs on the unstable servers by doing the following:

STOP SLAVE;
SHOW SLAVE STATUS\G
Get LOGFIL from Relay_Master_Log_File
Get LOGPOS from Exec_Master_Log_Pos
CHANGE MASTER TO master_log_file='LOGFIL',master_log_pos=LOGPOS;
START SLAVE;

The CHANGE MASTER TO command erases the relay logs it currently has and starts fresh.

Give it a Try, and see if this clears up the replication lag !!!


Is it ever useful to SELECT DISTINCT on the primary key column(s)?

If you join with another m:n table that duplicates the PK value in your result set, then it would be useful.


INDEX on a table where I'm only filtering on a row_number()

If we can declare location, yearnum, and weeknum as NOT NULL, you can create a composite index on location, yearnum, weeknum that can be used instead of doing the full table scan.  Here is a sqlfiddle showing that approach.  Note that I applied a hint to force the index to be used since there isn't enough data in the example for a full scan of the index to be less expensive than a full scan of the table.  The index full scan path would probably be more efficient in the scaled up example assuming each location has data for many more than the past 26 weeks.  That's probably not going to be an order of magnitude improvement, though, unless the table is substantially larger than the index.


The terms are being confused sadly which is causing an issue.  There's WCFS (Windows Clustering Foundation Services) which FCI (Failover Cluster Instance) runs on and requires shared disks.  An AlwaysOn AG can also run on WCFS but it uses non shared storage and provides live read only replicas.

In other words A FCI is a shared storage architecture and you fail over from node to node.  FCI doesn't in any way provide a 'read only' solution out of the box.  Usually you will do mirroring+snapshot/log shipping or something else if you only have FCI.  Right now what you are doing is simply adding another failover node, not an AlwaysOn AG.

This is what you are looking for (from the link).  Note the multi node cluster with shared storage and the non shared storage AG:

Right now this is what you're doing. You're simply adding a 3rd node to the shared storage cluster:

This guide works well if you already have a FCI and want to add AG to it.  Just look for how to install an AG.  I would go through the steps for you but there's a lot of steps and you're better off going with an actual guide with screenshots and such.  Do you have somewhere to test this in? Have you tried an Azure instance to test with if not?  I would not recommend doing this live to prod.  Here is some good documentation from Microsoft.  Are you also sure that your code is AG compliant?

If you use MSDTC or cross db transactions you could create a serious problem.

Now the bigger question is are you the person who will be supporting this and is this prod?  It seems like it's important because you're utilizing both FCI and AGs.  I would highly suggest you research FCI and AGs in depth before deploying to prod.  There are tons of resources out there.  I would highly recommend not attempting to push this through on prod with the current understanding.  You might set yourself up for major failure and your HA solution can bring you down.


How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))

Maybe, and this might be dependant on the command line environment you're using, you need to quote the string, something like

 sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))"

or

 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'


Allow access to RackSpace database from Vagrant box

First, you want to find out what external IP address the Vagrant boxes NAT to:

What is my IP - Google

Then add an iptables rule for that IP address.

Then you will want to grant access from your MySQL database to the host (The Vagrant box)

GRANT ALL PRIVILEGES
ON database.*
TO 'user'@'vagrantboxremotehost'
IDENTIFIED BY 'password';

You can also use

TO 'user'@'%'

To allow from any IP, but that is not recommended.


ERDs and modelling ownership of roles within Job table

I think you'll want an EmployeeCanAuthorizeJob table and an EmployeeCanAdministrateJob table for a straight many-to-many representation ( right now, I think you'll find you'd be duplicating your job records with the current structure ).  This way, you could tie many EmployeeIDs to JobIDs and vice versa for either of the management tasks.

That said, I would likely only end up creating an EmployeeCanManageJob table, along with possibly a secondary look-up table ManageJobType.  The purpose of the second table would be to include the functions an employee can perform on a particular job.  Today you've got two, but who knows if CanDelete of whatever needs to be broken out later down the line?

EmployeeCanManageJob
------------------------------------------------
 PK | Employee_FK        FOREIGN KEY Employee.EmployeeID
 PK | Job_FK             FOREIGN KEY Job.JobID
(PK | ManageJobType_FK   FOREIGN KEY ManageJobType.ManageJobType_PK)

This way, if ManageJobType_PK 1 was "Administrate" and 2 was "Authorize", employee 1 could administrate job 1 with a ( 1, 1, 1 ) record and authorize the same job with a ( 1, 1, 2 ) record.

As per the edit:

There at least two popular methods of change tracking: the first being an audit table, the second being some form of slowly changing dimension.  To save you a little grief down the line, you are not dealing so much with a "slowly changing dimension" here as you are a "rapidly changing fact," if you will, so I expect you will find yourself looking at one of the numerous ways to implement the audit table scenario.  Which method you choose will largely be subject to you and your team's comfort level with the technologies involved ( or more specifically, each options advantages and disadvantages ).  I've personally always been a fan of the log trigger approach and think it would be more than suitable for your needs here, but if you have out-of-the-box capabilities available to you, like in Sql Server, perhaps you and your team would be more comfortable with that method.


build users availability report/Table (using sql)

I think in think the use of a tally table would work in this case. There are 1440 minutes in a day, so a tally table would need a minimum of 1440 records in order to work. Using the tally table, you can utilize the DATEADD function to add one minute between your start times and your end times. This would pull back one record per minute for the interval.

/* create the tables variables */
DECLARE @Tally TABLE
    (MinuteOfDay INT)

DECLARE @Users TABLE
    (UserName  VARCHAR(100)
    ,StartDatetime DATETIME
    ,EndDateTime DATETIME
    )

/* Populate the user table */
INSERT INTO @Users
        ( UserName ,
          StartDatetime ,
          EndDateTime
        )
VALUES  ( 'User_A' , -- UserName - varchar(100)
          '11-10-2015 12:00' , -- StartDatetime - datetime
          '11-10-2015 12:03'  -- EndDateTime - datetime
        )

/* Populate the tally table with 1 minute for 24 hours */
INSERT INTO @Tally
        ( MinuteOfDay )
SELECT TOP 1440
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) -1 AS Row_No
FROM sys.all_objects AS ao

/* Query to pull the result for each minute */
SELECT * FROM @Users AS u
INNER JOIN @Tally AS t ON DATEADD(MINUTE, t.MinuteOfDay,u.StartDatetime) BETWEEN u.StartDatetime AND u.EndDateTime


Another approach is to use the Oracle Gateway for DB2 which can be used to create an Oracle database link to the DB2 database. Then use a SQL script to "pull" all the data from each table in turn by

insert into oracle_table select * from db2_table@dblink;


SQL Server 2014 - Give user permission to run sp_refreshview

Per the sp_refreshview msdn library page (https://msdn.microsoft.com/en-us/library/ms187821.aspx):

  Permissions
  Requires ALTER permission on the view and REFERENCES permission on common language runtime (CLR) user-defined types and XML schema collections that are referenced by the view columns.


Has the ID column configured as PK, NN and AI, be the first column on my table definition?

No.  The order of columns in a table definition does not matter, with one old exception.  In older versions the "first" TIMESTAMP column had a special default DEFAULT value.

It is not required to have an AUTO_INCREMENT.  However it is (essentially) required that you have a PRIMARY KEY.  It smells like AgreementNumber would be such?  Keep in mind that a PK must be UNIQUE (a MySQL requirement).

As a side note, an A_I does not have to be the PK.  The only requirement is that it be the first column in some index.  And remember that a PK is a unique key is a key.

But, that is not your 'real' problem.  That is, you need to do LOAD DATA without the id being provided.

Plan A:  Load into another table, then copy it over.  When copying the data over, specify all the columns except the id; A_I will take care of the rest.

Plan B:  Use LOAD DATA, but specify all the columns except id.  Again A_I will take care of defaulting it correctly.

Other tips, while I have you on the line:

int(1) is a 4-byte number; the (1) means nothing.  Perhaps you want a 1-byte TINYINT?
Use INT UNSIGNED where appropriate
Use datatypes smaller than INT where appropriate; look up MEDIUMINT, and again make it MEDIUMINT UNSIGNED if appropriate.
Use NOT NULL when appropriate.
Don't index flags; they won't be used.
Learn about "composite" indexes.

More

If you choose to have a 6-column UNIQUE key that includes some VARCHARs, having an A_I is probably better than making that the PK.

A UNIQUE KEY is two things:  An index, and a uniqueness constraint.  You can use it for either or both.

A PK is a UNIQUE KEY that identifies the row.  It also is "clustered" with the data, making lookups more efficient than with a "secondary key" (any non-PK).

When the 'cardinality' of an indexed column is very low, such as for a flag, the Optimizer will dynamically look at the value you provide and see if less than 20% of the table has that value.  In this case, it will use the index; else it will not.  The "20%" varies with the phase of the moon.  The reason for eschewing the index is that using the index involves bouncing back and forth between the index's BTree and the data's BTree via the PK.  This can be slower than simply scanning the data, tossing 80% of the rows.  A secondary key is a BTree containing the indexed column(s) plus a copy of the PK.


Create a filtered index on requestId with the predicate where requestStatus = 'NotProcessed'. The optimiser is likely to favour this as long as the query conditions exactly match the index definition.

It may help to use a small tinyint for the status instead of a longer character string. There will be more rows per data page and less IO.


how to find the name of the distributor server and publisher server from the subscriber server/subscriber database

Is there a way I can find the name of the publisher and distributor
  (server and database), from the subscriber?

For push subscriptions from the subscriber you can query MSsubscription_agents to get the publisher and publisher database.

For pull subscriptions from the subscriber you can query MSsubscription_properties to get the publisher and distributor.  Have a look at the columns publisher, publisher_db, and distributor.  The distribution database name will likely be distribution.


View Default value for User Defined Table Type columns in SQL Server

Find your user-defined table type:

-- UDTT will have a 'TT prefix'
select * from sys.objects where type = 'TT' and name like '%LogElements%';

In my output, the object was called TT_LogElements_3A4CA8FD and had an object_id of 978102525.

Now we can use a script similar to Ryan Cooper's answer, by plugging that object_id into the WHERE clause, as the parent_object_id:

SELECT o.name AS [Constraint Name],
    cl.name AS [Column Name],
    s.name AS [Data Type],
    cl.max_length AS [Length],
    c.text AS [Default Value]
FROM sys.syscomments c
INNER JOIN sys.objects o
    ON c.id = o.object_id
INNER JOIN sys.columns cl
    ON o.parent_object_id = cl.object_id
        AND cl.default_object_id = o.object_id
INNER JOIN sys.systypes s
    ON cl.system_type_id = s.xtype
WHERE o.type = 'D'
    AND parent_object_id = 978102525;

This is my example output, which is fairly trivial to convert into a T-SQL script for recreating them.

[Constraint Name]               [Column Name]   [Data Type] [Length] [Default Value]
------------------------------------------------------------------------------------
DF__TT_LogEle__LogSt__3B40CD36  LogStatusId     int         4        ((0))
DF__TT_LogEle__LogUs__3C34F16F  LogUser         varchar     50       ('Test')


Oracle - Using index on unique constraint, what the deal?

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error.

http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225

  If you specify schema.index, then Oracle attempts to enforce the    constraint using the specified index. If Oracle cannot find the index
  or cannot use the index to enforce the constraint, then Oracle
  returns an error.
  If you specify the create_index_statement, then Oracle attempts to    create the index and use it to enforce the constraint. If Oracle
  cannot create the index or cannot use the index to enforce the
  constraint, then Oracle returns an error.
  If you neither specify an existing index nor create a new index, then    Oracle creates the index. In this case:

  The index receives the same name as the constraint.
  If table is partitioned, then you can specify a locally or globally    partitioned index for the unique or primary key
  constraint.


SQL Server Polybase Data Movement Service stopping after few seconds

It turned out to be a permissions issue with the DWConfiguration/ DWDiagnostics and DWQueue databases that I just reattached after the reinstall of the OS, for some reason the [NT AUTHORITY\NETWORK SERVICE] user did not have the correct permissions on these databases and thus the service kept on failing.
I managed to debug this with an extended events session trying to capture all queries that would touch these databases.
Thank you for all the help and support


How can i merge 3 sql select queries in single query?

If you move part of the WHERE statements into the join you can accomplish the same thing:

SELECT tr.object_id,
    p.post_title, p.comment_count,
    p2.guid, p2.post_type,
    pm.meta_value, pm.meta_key
FROM wpug_term_relationships AS tr
    LEFT JOIN wpug_posts AS p ON p.post_parent = tr.object_id
    LEFT JOIN wpug_posts AS p2 ON p2.post_parent = tr.object_id
        AND pm.post_type='attachment'
    LEFT JOIN wpug_postmeta AS pm ON pm.post_id = tr.object_id
        AND pm.meta_key IN ('rehub_views', 'rehub_offer_product_price')
WHERE tr.term_taxonomy_id=122
ORDER BY tr.object_id DESC

I noticed that on the first join to wpug_posts you don't filter post_type but on the second one you do.  I assume this means it will return multiple rows.  Do you want the first one to return post_type='attachment' as well?  If not you'll need to add AND p.post_type<>'attachment' to the first join


1. Is it mandatory to have SQL Server agent account defined in SQL
server logins to run the job?
See this and this.
The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.
As for the account that you used to run the services, this is complicated and has changed in each version of SQL Server.The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER.
There are permission in the operating system that an agent account gets by default. Details here.
As per books online:

The account that the SQL Server Agent service runs as must be a member
of the following SQL Server roles:
The account must be a member of the
sysadmin fixed server role.
To use multiserver job processing, the
account must be a member of the msdb database role TargetServersRole
on the master server.
In all Windows versions, permission to log on as a service (SeServiceLogonRight)

But it should not be a member of local administration group.

We recommend choosing a Windows user account that is not a member of
the Windows Administrators group. However, there are limitations for
using multiserver administration when the SQL Server Agent service
account is not a member of the local Administrators group

2. What user/login my update job is using in order to update the table?
Reference: https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/
Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account.
The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.


MySQL, when executing an update against a single table, does -- wrongly -- treat updates as "ordered," and update the columns in the order they're written in the query.

  The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

  UPDATE t1 SET col1 = col1 + 1, col2 = col1;

  Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

  https://dev.mysql.com/doc/refman/5.6/en/update.html

Your query appears to be written in such a way that this behavior won't cause problems. As long as you don't reference the value of a column you've "already" changed (assuming left to right operation instead of atomic operation) it will do what you expect.


I solved the problem by adding the apt repository mysql-tools :
deb http://repo.mysql.com/apt/debian/ stretch mysql-tools

It works for Debian 9 (stretch). Not tested for Debian 8 (jessie).


The correct procedure when you cannot start the SQL Server services on a computer (for whatever reason), is to simply copy all the database files to a working server and attach them there.


SQL Server: Rows over 8KB: Where are the docs?

Table and Index Organization

  Variable length data stored in varchar, nvarchar, varbinary, or
  sql_variant columns that exceed the 8,060 byte row size limit.

  For every partition used by a table (heap or clustered table), index,
  or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This
  allocation unit contains zero (0) pages until a data row with variable
  length columns (varchar, nvarchar, varbinary, or sql_variant) in the
  IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the
  size limitation is reached, SQL Server moves the column with the
  largest width from that row to a page in the ROW_OVERFLOW_DATA
  allocation unit. A 24-byte pointer to this off-row data is maintained
  on the original page.

Row-Overflow Data Exceeding 8 KB

  The length of individual columns must still fall within the limit of
  8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR
  user-defined type columns. Only their combined lengths can exceed the
  8,060-byte row limit of a table.

Another good answer here:
Work around SQL Server maximum columns limit 1024 and 8kb record size


Undo Database Restore in SQL Server 2008

Unfortunately no. The only solution is to do another restore with the proper backup file(s).


3NF Normalization Problem Employing an Algorithm

You have not specified the algorithm used for the decomposition, and actually both the decompositions (yours and that of the book) have relations that include others, which is something that normally is not done, since is a form of redundancy.

For instance, having R6(A C D), and R7(A C D) (remember that the order of attributes in a relation schema is not relevant) means that you have two relations with exactly the same attributes (and exactly the same functional dependencies projected from the original ones, AD -> C and AC -> D), so that they have both the same candidate keys (AD and AC), and chosing one of them as primary key in a table and the other one in the other is meaningless.

A similar problem is in the schema R2(E G B), where the projected functional dependencies are EG -> B and B -> G, with candidate keys EB and EG, and it is not clear the advantage of having a separate table R1 with only the attributes B and G, and with the key B.

In fact a frequently used algorithm, presented in almost all books on databases, is the so-called “synthesis” algorithm, that avoids this kind of redundancy, while maintaining both the properties of dependency preservation and lossless-join. This algorithm, applied in this case, produces the following decomposition:

R1(A B E F), with candidate key A

R2(A D H I), with candidate key A D I

R3(A C D), with candidate keys AC and AD

R4(C D J), with candidate key CD

R5(A C F K), with candidate key CF

R6(B E G), with candidate keys EG and EB

Basically this algorithm groups all the dependencies with the same left part, produces a relation for each group with all the attributes of the functional dependencies of the group (and the left part is a candidate key). Then all relation schemas included in others are eliminated (to avoid the kind of redundancy discussed previously), and finally, if no relation schema in the decomposition contains one of the original candidate key, adds a new scheme with one (any one) of the original candidate keys. As said above, this algorithms is guaranted to produce a dependency preserving and lossless-join decomposition.


How to filter on multiple values for a given set of keys using JSONB, and an index

I see 2 rather straight forward approaches depending on how many values we're looking for.

The first option is to look for a document containing the single valued keys (in this example: where info @> '{"k1":"bar"}'; and wrap that with additional filtering for all the other values: info->>k0 in <the values>

The 2nd option would be to create full documents for the various options, in your example it means creating 2 documents to search by. If the number of values is small them it's not too much of a problem and the index will be used.


To get the result that you want, it appears that you'll need to use a FULL JOIN. A FULL JOIN will join your 2 tables on the bln column but it will return data from both tables, even if the value of the column doesn't appear in both tables.

The basic syntax of the query will be:

select
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from table1 t1
full join table2 t2
  on t1.bln = t2.bln

See SQL Fiddle with Demo.  This will give a result of:

| BLN |  THN | QTY1 | QTY2 |
|-----|------|------|------|
|   1 | 2014 |   10 |    0 |
|   1 | 2014 |   20 |    0 |
|   2 | 2014 |   30 |  100 |
|   ....
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |

You'll notice that I used a COALESCE on the columns bln and thn - this will return the first non-null value in those columns. I then used IsNull on the qty1, and qty2 columns to replace the null values returned for these columns with a zero.  You could use either COALESCE or ISNULL for these operations - I'm using both for demo purposes.

Now, since you want to get the sum of the qty1 and qty2 columns, you'll add the aggregate function with a group by.  I'd actually do this in a subquery for each table, otherwise you could get an elevated total for each quantity.

select
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from
(
  select bln, thn, qty1 = sum(qty1)
  from table1
  group by bln, thn
) t1
full join
(
  select bln, thn, qty2 = sum(qty2)
  from table2
  group by bln, thn
) t2
  on t1.bln = t2.bln

See SQL Fiddle with Demo.  This gives the result:

| BLN |  THN | QTY1 | QTY2 |
|-----|------|------|------|
|   1 | 2014 |   30 |    0 |
|   2 | 2014 |   80 |  600 |
|   3 | 2014 |   40 |  200 |
|   4 | 2014 |   60 |  300 |
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |

Finally, you just use the query to create a view:

create view dbo.your_view as
select
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from
(
  select bln, thn, qty1 = sum(qty1)
  from table1
  group by bln, thn
) t1
full join
(
  select bln, thn, qty2 = sum(qty2)
  from table2
  group by bln, thn
) t2
  on t1.bln = t2.bln

See Demo


Do I need to redesign my Database and get rid of foreign keys for successful replication?

This looks like a non-issue, because, obviously, you will be using the same table definitions, including engine, on both servers. The example in the MariaDB Knowledge Base article you linked to has different storage engines: the slave server doesn't have InnoDB, so it defaults to MyISAM, which doesn't support foreign keys. MyISAM does tolerate foreign key constraints in the CREATE TABLE statements, but it silently ignores them, so any defined cascading deletes will not happen.


There are several ways to do that.

The first one that Rick already mentioned will work on Postgres as well:

update genres
   set image = replace(image, '.png', '.jpg');

But that will not work if the file names contain .png before the end. To be sure to only replace that value at the end you can use a regular expression:

update genres
   set image = regexp_replace(image, '(.*)\.png$', '\1.jpg');

or you can simply use the first "length - 3" characters and append the new extension to that.

update genres
   set image = left(image, -3)||'png';

Alternatively as you seem to not like the || operator that has been defined for SQL 30 years ago:

update genres
   set image = concat(left(image, -3), 'png');


How to perform a multiplication by a different select query

Probably simplest would be:

DECLARE @var VARCHAR(7), @c INT;

SET @var = 'xtpo';

SELECT @c = COUNT(DISTINCT PcbID) FROM dbo.tbl_C WHERE dbo.tbl_C.field_5 LIKE @var;

SELECT
    row_number() over(order by dbo.tbl_A.field_1) AS [1st column],
    dbo.tbl_A.field_2 AS [2nd column],
    dbo.tbl_B.field_1 AS [3rd column],
    dbo.tbl_A.field_3 AS [4th column],
    COUNT(temp0.reference) AS Qty,
    COUNT(temp0.reference) * @c AS [6th column]
FROM dbo.tbl_C tbl_c
...

Note that I changed your 'single quote' aliases to use [square brackets] instead. Single quote aliases are deprecated in some forms, and they are not well-received because they make your aliases look like string literals. In most cases you are better off not using delimiters at all if you don't need them (which means not using column names that require delimiters in the first place).


How can Excel communicate with a remote SQL Server that is disconnected from the Internet?

We have created an Excel Add-In for updating tables from Excel to SQL Server. The Add-In supports working in offline mode, and I'm pretty sure the off-line mode will handle this case.

You will need to install Excel and the SQL Spreads Add-In on both the non-internet connected server and on the laptop. The process will then be like this:

On your "off-line" server containing your SQL Server databases, create a new Excel file and select the SQL Server table(s) you like to update.
The data from the tables will be loaded into the Excel file. When you save the file, all the SQL Server data will be stored into the Excel file.
Move the Excel file to your laptop and edit the data there. When you save the
file, all your changes will be stored into the file.
Move the Excel file back to the server, open the file and click Save in the SQL
Spreads tab in Excel to push the updates to the SQL Server tables.

The Add-In can also check for conflicts if the data has been changed in the database while you were editing it in your off-line doc.

If that's of interest, you can read more here and download a free trial.

Disclaimer: I'm the founder of the company.


SQL Server Express 2005 DB over size but data < 1GB

First, check your database for indexed views, they would be missed by your original script (courtesy of Michael J Swart):

select OBJECT_SCHEMA_NAME(object_id) as [SchemaName],
       OBJECT_NAME(object_id) as [ViewName],
       Name as IndexName
from sys.indexes
where object_id in
  (select object_id from sys.views)

If that list contains any rows, run the following to see how much space each uses:

EXEC sys.sp_spaceused @objname = N'dbo.YourView'

Second, try this script to distinguish between data and index space in each table (a variation of alpav's script here):

create table #tbl(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'

select name, rows,
    convert(int, substring(reserved, 1, len(reserved)-3)) as reserved_kb,
    convert(int, substring(data, 1, len(data)-3)) as data_kb,
    convert(int, substring(index_size, 1, len(index_size)-3)) as index_kb,
    convert(int, substring(unused, 1, len(unused)-3)) as free_kb
 from #tbl
    order by 5 desc

drop table #tbl

That should at least help you determine what table is responsible.

Once you've done that, you'll need to take further action:

Drop unneeded tables, indexed views, and indexes
Truncate or delete unneeded data
Drop and re-create indexes with a higher fill factor
Drop and re-create indexes with fewer indexed fields, or fewer included fields.

Beyond that, you'll have to look at the data structure (data types used, redundant data, etc), but that's not an easy fix.


SQL Agent using domain user for Jobs with TSQL

uxcolebsrep$ is probably the SQL Server name itself.

In this case, as you are using a linked server, that makes sense. Your actual Agent service account is a local account to your sql server. It does not exist on the remote Linked Server so SQL uses the Machine account to try to connect to the remote SQL Server (the one in the linked server).

To get this fix, you have a couple of option:
1 - Change your SQL Agent service acccount for a domain account and make sure it has permission on the remote SQL server (the one in the linked server)  - (Recommanded option)
2 - Give access to the SQL Machine (uxcolebsrep$) on your remote SQL Server
3 - Create a proxy to use in your SQL job and make sure that this proxy will use an account with permission on the remote SQL Server.


Configure listen_addresses automatically in custom installation

The easiest solution is to append:
listen_addresses = '*'

to postgresql.conf after running initdb. As the last occurrence overrides earlier ones, this would even apply if the default entry were commented out.
Db cluster has to be restarted/reloaded after  that.


How can I check if my user has CREATE SCHEMA permission?

It looks to me like I will not be able to create this schema, is that
  right?

No, it's wrong.
You can create all you want in your database because you are db_owner. You can see it looking at your first picture with membership.

The error in the answer above is that it consider only explicitly granted permissions for given user. But the user may be a member of 1000 roles, and the permissions can be given to these roles.

In your case you are a member of fixed database role db_ownerthat has control on database, so you can do anything within your database.

To see your effective permissions in your database execute this:

select * from sys.fn_my_permissions(null, 'database');


Is there any reason to use index for very small tables (up to 1000 rows)?

Generally yes. In lack of an index the access pattern has to inspect every row, just to see if is the one you need or not. The issue is not the table size, but concurrency. Depending on your isolation level, your scans may block behind uncommitted transaction just to wait for rows that ultimately are 'uninteresting' to be unlocked. Because your scan is guaranteed to 'look' at every row, every scan will block behind any write (insert, delete or update). Oracle defaults to snapshot isolation which is OK in this situation (no blocking) but other vendors default to something else, eg. SQL Server will default to Read committed which does block.

With an index present your access pattern will only look at the relevant rows (within the desired key range) so the pure statistical chance of hitting a lock conflict is greatly reduced.


MySQL query returns unexpected results inside a stored procedure

more correct change variable names:

CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `p_facebookId` VARCHAR(20), IN `p_userAccessToken` TEXT)
    NO SQL
    COMMENT 'check if user is valid. return true or false'
Begin
    /* declare the select resluts variables*/
    Declare v_accessToken TEXT;
    Declare v_expires datetime;

    /* fill the variables with saved user's access token an expires time*/
    Select U.Access_Token, U.Expires
    Into v_accessToken, v_expires
    From Users U Where U.Facebook_Id = p_facebookId;

    select v_expires, v_accessToken;
    /* If the saved access token and expires are valid return true */
    If (BINARY v_accessToken = p_userAccessToken And v_expires > now()) Then
        select true As isUserValid;
    Else
        select false As isUserValid;
    End If;
End

it help read code more easy for You and for MySQL parser as well


Azure SQL Database SQL71562 error exporting to blob storage

This is an error I’ve seen after migrating to Azure; usually in stored procedures. The error is seen when something has a four-part table reference. For example: [server].[database].[schema].[table]. These are not supported and result in a SQL71562 error.
You may be able to identify the issue by querying your [sys] tables for procedures and views that specifically reference a server or database.
For stored procedures:
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
  FROM sys.procedures
 WHERE OBJECT_DEFINITION(object_id) LIKE '%[DB_NAME]%'

For views:
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
  FROM sys.views
 WHERE OBJECT_DEFINITION(object_id) LIKE '%[DB_NAME]%'

Be sure to replace DB_NAME with the name of one of your databases.


Sub query to return same number of records and join

Simplify the problem. Find a register / room combo that has multiple barcodes:

SELECT a.AssetID, g.RoomNo, MIN(au.Room_barcode), MAX(au.Room_barcode)
FROM dbo.AssetRegister AS a
  LEFT OUTER JOIN dbo.LocationRoom AS g
  ON g.LocationRoomID = a.LocationRoomID
  LEFT OUTER JOIN dbo.Audit AS au
  ON au.Asset_barcode = a.AssetID
GROUP BY a.AssetID, g.RoomNo
HAVING COUNT(*) > 1;

Then pick one of those AssetID / RoomNo combinations and look closer at the data:

SELECT a.AssetID, g.RoomNo, au.Room_barcode
FROM dbo.AssetRegister AS a
  LEFT OUTER JOIN dbo.LocationRoom AS g
  ON g.LocationRoomID = a.LocationRoomID
  AND g.RoomNo = <room number from above>
  LEFT OUTER JOIN dbo.Audit AS au
  ON au.Asset_barcode = a.AssetID
WHERE a.AssetID = <asset ID from above>;

Now, if you are getting multiple room barcodes back, you have to determine if that is valid or not (we can't tell you that, this is your data after all). If it is not valid, you need to fix the data. If it is valid, then you need to decide whether you want all the barcodes in the result or, if you just want one, which one? Build actual sample data from this single Asset/Room combo, add that data to your question, and explain what results you want from that sample data.

Don't try to reduce row counts by slapping DISTINCT in front of the column list, or increase row counts by changing inner joins to outer joins. Start simple and make sure you are collecting the right data rather than whether n row(s) affected looks right.


SQL Server Execution Plan on server level

There is some confusing info given here. For example, this does not make sense:

  I turn on execution plan it goes to more than 40 seconds.

What do you mean by "I turn on execution plan"? If you are talking about the "Include Actual Execution Plan" option in SSMS, then yes, it does take a moment longer for SSMS to prepare and draw the graphical execution plan. And it certainly takes even longer the first time this option is enabled. Did you run the query several times?

Also, you say:

  When I run query in SSMS, it takes 2-3 seconds

but then say:

  I found out that when language is English (default collation) application works "as quick as possible" (6-7 seconds)

wouldn't 2 - 3 seconds be "as quick as possible"? Or is the application adding 3 - 5 seconds for its processing?

Either way:

  when it language is changed, obviously, SQL server doing recompile and everytime response is more than 40 seconds.

No, not "obviously". A recompile would not explain a 30+ second difference per each execution. But, how are they changing the Collation? Is this dynamic SQL and they are using either nothing or COLLATE Latin1_General..., and then using COLLATE other_lang... for the other language(s)? Not sure how else one could change the Collation for a query. Still, one thing that could explain this difference), if in fact the "collation" is being changed in the way that I just described), is if the column is indexed, in which case I would guess that the column's Collation is [SQL_]Latin1_General_... and so the index is also ordered based on English. If a query is submitted that forces a different Collation by using COLLATE French_..., for example, then the query can't use the index as the rows might be in a different order.

If I am correct about what is going on (O.P. has since confirmed that the situation is as I have described here), then the only way to index the column with multiple Collations is to create non-persisted computed columns to force the Collation for each language being offered. So you can have one column be:

ALTER TABLE [dbo].[SomeTable]
  ADD [ColumnNameFrench] AS ([ColumnName] COLLATE French_100_...);

And then:

create a non-clustered index on [ColumnNameFrench]
update the app code / query so that if someone selects "English", then it selects from [ColumnName], but if the user selects "French", then it selects from [ColumnNameFrench] instead. Then it will be able to use the index on the [ColumnNameFrench].

Hopefully you don't offer too many language options, because I think 10 copies of the index might be a lot.

EXAMPLE

The table:

-- DROP TABLE #Test;
CREATE TABLE #Test
(
  [TestID] INT IDENTITY(1, 1) PRIMARY KEY,
  [SomethingEnglish] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC,
  [SomethingFrench] AS ([SomethingEnglish] COLLATE French_100_CI_AS_SC),
  [SomethingHebrew] AS ([SomethingEnglish] COLLATE Hebrew_100_CI_AS_SC)
);

CREATE INDEX [IX_#Test_SomethingEnglish] ON #Test ([SomethingEnglish]);
CREATE INDEX [IX_#Test_SomethingFrench] ON #Test ([SomethingFrench]);
CREATE INDEX [IX_#Test_SomethingHebrew] ON #Test ([SomethingHebrew]);

Simple stored procedure:

GO
CREATE PROC dbo.Search
(
  @Search  NVARCHAR(500),
  @Language INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'
SELECT tmp.[TestID]
FROM   #Test tmp
WHERE  tmp.[Something' +
CASE @Language
  WHEN 1 THEN N'Hebrew'
  WHEN 2 THEN N'French'
  ELSE N'English'
END + N'] = @SearchTerm;';

EXEC sp_executesql
  @SQL,
  N'SearchTerm NVARCHAR(500)',
  @SearchTerm = @Search;
GO


Workaround for using functions in restricted system

If the functions are just doing SELECT statements then you can simulate them with an inline view ...

  select t.*
         , notaf.descr
  from  t
        , ( select code, descr from lookup_tbl ) notaf
  where t.col23 = notaf.code

... or a sub-query factoring clause ...

with notaf as ( select code, descr from lookup_tbl )
select t.*
         , notaf.descr
from  t
       , notaf
where t.col23 = notaf.code

--- or even a simple join (using ANSI syntax for variety!) ...

select t.*
         , lookup_tbl.descr
from  t
      join lookup_tbl
           on  t.col23 = lookup_tbl.code

You can use DUAL if the function does something computational (rather than a data lookup) but you may need to pfaff around with nested views to simulate passing parameters.


Migrate a big database from Oracle 11g to 19c

please look into this Oracle documentation about the upgrades, if you are using 11.2.0.4 then you  should be fine.
https://www.oracle.com/a/tech/docs/twp-upgrade-oracle-database-19c.pdf


How to get SQL insert and/or update to not lock entire table on MS SQL Server

Chances are it isn't locking the "whole table".

It is locking a row in the table but your SELECT * FROM LAYOUTSv2 tries to read the whole table so necessarily is blocked by that lock.

For the insert case you can merely specify the READPAST hint to skip past the locked row- however that will not give your desired result for the UPDATE case (it will skip the row again not read the starting version of the row).

If you configure the database for read committed snapshot isolation this will give your desired effect for both cases (at the expense of greater use of tempdb)


How to Backup and Restore Filestream Data

You have to restore the database first with the norecovery switch, then restore the other file group. Then roll the transaction log forward then bring the database online.

This requires a minimum of three RESTORE DATABASE commands to do.


What is the use of the transaction log buffer in SQL Server?

Imagine a modification, for instance INSERT of a row.

SQL Server finds the page(s) that needed to be modified and brings them into memory (if not there already).

Then it constructs log records to reflect the modifications that is about to be performed. These are not written directly to the physical ldf file, for performance reasons. They are cached, in "log buffers".

Then the page(s) are modified, also in memory.

When the transaction is about to commit, SQL Server need to first "harden" the log recods. I.e., physically write the log buffers to the ldf file, before the transation is committed. I.e., a synchonous write, seen from the user's perspective.

The actual data pages will be written at a later time. For instance when checkpoint occurs.


Error while deploying package to catalog

The issue may occur if the user has Deny privilege on the target objects as well. Please check the permissions settings on the target database.

You should remove this roles from your account.

db_denydatareader
db_denydatawriter


Creating a test database from Production database (different machines & servers)

Further to my comment this is, putting it as politely as possible, a moderately crazy approach to getting the job done.

Regardless of how mighty the ORM is, you are inherently changing the behaviour of the system by changing the RDMS.
That your ORM can talk to two different database systems is a different proposition to the same database (i.e. schema) on two platforms. You can certainly create a RDBMS agnostic application but you'd never release it having tested against only one, would you?
To accommodate the change of RDBMS, you create a stack of unnecessary ETL work to re-create the database objects, manage the type changes and then finally shift the data around.

  Are there potential problems with hosting the testing database on the
  same server as the production server, assuming that the load on the
  testing database will be relatively minor?

You could mitigate the inherent risk in mixing development and production with appropriate segregation of security rights but accidents happen, people make mistakes, the wrong button get's clicked. If the server is comfortably spec'd and you are 100% convinced your testing won't place an undue load on the server, you could consider installing a second SQL instance side-by-side with the production instance but make sure all interested parties accept the risks first.

Alternatives:

VirtualBox or similar. Better isolation if you have to run this on your production server and you keep the database in SQL Server.
If you can't buy, rent. Rackspace Cloud, Amazon EC2, Microsoft Azure.


Can plpython3u open files on the file system?

It is quite possible.  But the OS user running the PostgreSQL server (usually 'postgres') might not have permission to traverse the directory tree containing the file even if it has permissions to the file itself.  Or might be in a chroot jail and so can't even see the directory.  Or it could be blocked by SELinux policy.  This is system's level stuff, nothing specific to PostgreSQL or even databases generally.


Test:

SELECT metaval, COUNT(DISTINCT tour_id)
FROM metas, ( SELECT tour_id
              FROM metas
              WHERE (metakey, metaval) IN ((3, 5), (4, 6)) -- or (2, 6) ?
              GROUP BY tour_id
              HAVING COUNT(tour_id) = 2 ) tours
WHERE metas.tour_id = tours.tour_id
GROUP BY metaval


How to format a phone number in a SELECT statement?

Different areas have differing number of digits in their prefix (wikipedia's list):

COALESCE(CONCAT('+',
  CASE
    WHEN LEFT(phone_number, 1) IN ('1', '7')
      THEN INSERT(INSERT(INSERT(phone_number, 2, 0, '-'), 6, 0, '-'), 10, 0, '-')
    WHEN CONVERT(LEFT(phone_number, 2), SIGNED) IN (21, 22, 23, 24, 25, 26, 29, 35, 37, 38, 42, 50, 59,
                                                    67, 68, 69, 80, 85, 87, 88, 96, 97, 99)
      THEN INSERT(INSERT(INSERT(phone_number, 4, 0, '-'), 8, 0, '-'), 12, 0, '-')
    ELSE   INSERT(INSERT(INSERT(phone_number, 3, 0, '-'), 7, 0, '-'), 11, 0, '-') END), 'N/A')

With the query above, all phone numbers will show as +Country_code-XXX-XXX-XXXX although each area has specific formatting rules, which vary greatly, so that formatting will look funny for certain regions, such as Australia where formatting is +54-X-XXXX-XXXX.