Changing table types from MyISAM to InnoDB in a MySQLdump file

I have created scripts where I flip the storage engine from MyISAM to InnoDB as follows:

mysqldump ... | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > dump.sql

If you want to see if this really works, just dump the schema as follows:

mysqldump --no-data ... | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > schema.sql

With reference to the mysql schema, please don't touch it. Why ???

In MySQL 5.5, all tables in the mysql schema were MyISAM.

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|          22 | MyISAM |
|          22 | Total  |
+-------------+--------+
2 rows in set (0.03 sec)

In MySQL 5.6, that began to change

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|           2 | CSV    |
|           5 | InnoDB |
|          21 | MyISAM |
|          28 | Total  |
+-------------+--------+
4 rows in set (0.00 sec)

Here are the non-MyISAM tables for MySQL 5.6:

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine in ('InnoDB','CSV')
    -> order by engine,table_name;
+----------------------+--------+
| table_name           | engine |
+----------------------+--------+
| general_log          | CSV    |
| slow_log             | CSV    |
| innodb_index_stats   | InnoDB |
| innodb_table_stats   | InnoDB |
| slave_master_info    | InnoDB |
| slave_relay_log_info | InnoDB |
| slave_worker_info    | InnoDB |
+----------------------+--------+
7 rows in set (0.00 sec)

The two CSV tables are the slow log and error log, should your change log_output to FILE. The 5 InnoDB tables were introduced to support crash-safe replication (See Documentation on this).

It does not stop there. MySQL 5.7 introduced more InnoDB tables.

mysql> select count(1) table_count,ifnull(engine,'Total') eng
    -> from information_schema.tables where table_schema='mysql'
    -> group by engine with rollup;
+-------------+--------+
| table_count | eng    |
+-------------+--------+
|           2 | CSV    |
|          19 | InnoDB |
|          10 | MyISAM |
|          31 | Total  |
+-------------+--------+
4 rows in set (0.01 sec)

Here are the non-MyISAM tables for MySQL 5.7:

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine in ('InnoDB','CSV')
    -> order by engine,table_name;
+---------------------------+--------+
| table_name                | engine |
+---------------------------+--------+
| general_log               | CSV    |
| slow_log                  | CSV    |
| engine_cost               | InnoDB |
| gtid_executed             | InnoDB |
| help_category             | InnoDB |
| help_keyword              | InnoDB |
| help_relation             | InnoDB |
| help_topic                | InnoDB |
| innodb_index_stats        | InnoDB |
| innodb_table_stats        | InnoDB |
| plugin                    | InnoDB |
| servers                   | InnoDB |
| server_cost               | InnoDB |
| slave_master_info         | InnoDB |
| slave_relay_log_info      | InnoDB |
| slave_worker_info         | InnoDB |
| time_zone                 | InnoDB |
| time_zone_leap_second     | InnoDB |
| time_zone_name            | InnoDB |
| time_zone_transition      | InnoDB |
| time_zone_transition_type | InnoDB |
+---------------------------+--------+
21 rows in set (0.17 sec)

Please note that there are still 10 MyISAM in MySQL 5.7. Here they are:

mysql> select table_name,engine from information_schema.tables
    -> where table_schema='mysql' and engine='MyISAM';
+------------------+--------+
| table_name       | engine |
+------------------+--------+
| columns_priv     | MyISAM |
| db               | MyISAM |
| event            | MyISAM |
| func             | MyISAM |
| ndb_binlog_index | MyISAM |
| proc             | MyISAM |
| procs_priv       | MyISAM |
| proxies_priv     | MyISAM |
| tables_priv      | MyISAM |
| user             | MyISAM |
+------------------+--------+
10 rows in set (0.00 sec)

Why are there 10 tables still in MyISAM ?

The tables user, db,procs_priv, proxies_priv, tables_priv, and columns_priv are tables that store user grants.
func contains stored functions
proc contains stored procedures
event contains schedules for executing events
ndb_binlog_index contains info needed for the NDB storage engine (MySQL Cluster)

These 10 tables are rarely used. Their contents are loaded into RAM on startup and then referenced from RAM. There is no need for them to be InnoDB because they are system-level tables that should not be changed via transaction.

Look at the potential problems that could occur if these 10 tables were InnoDB:

Running a GRANT command in a transaction and it rolls back
Creating a stored procedure in a transaction and it rolls back
Creating a new user and the user table crashes and gets corrupted

These and other problems can result and you would not even know it until mysqld restarts and nobody can log in, execute a stored procedure, or schedule a cron'd event.

Therefore, with good reason, these 10 tables were left as MyISAM. Again, please don't change the storage engine of anything in the mysql schema.


Dumping mssql db from one server and restoring on another on Mac/Linux

In SQL Server you use BACKUP and RESTORE commands for this.  These both read and write files relative to the server, not the client.  So you'll need to write the backup to a storage location accessible to both SQL Server instances.  Backups can be written to and read from the local filesystem, SMB shares, or Azure Blob Storage.


Identify Queries causing High Average Wait time (SQL Server 2008)

You can probably find simpler queries to give you that information, but if you want something more robust, I'd suggest sp_BlitzCache. Full disclosure, I'm one of the authors. It's totally free, open source, and you can get it here.

How does it help you?

You can use the @SortOrder parameter to look for queries either with the most total duration accumulated in your plan cache:

EXEC sp_BlitzCache @SortOrder = 'duration'

Or you can look by long average duration:

EXEC sp_BlitzCache @SortOrder = 'avg duration'

That will average our duration over executions to find the longest running by average.

You'll get back a ton of information about each query, including the query plan. If you have any questions about the results, you can ask them here or in the GitHub repo.

Hope this helps!


Single year column and multiple weeks columns into single year/week column with PIVOT

This sounds more like an UNPIVOT to me.

SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value
FROM SalesTable
CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1),
                    ('02'+CAST([Year] AS CHAR(40)), Week2),
                    ('03'+CAST([Year] AS CHAR(40)), Week3),
                    .......)
        CrossApplied (WeekYear, Value)

There are other ways to do an UNPIVOT but using CROSS APPLY is my favorite.  I give a more general example here.


Different behaviour between oracle and postgres regarding alter table primary key command

An index is added in PostgreSQL, too, when a PRIMARY KEY or a UNIQUE constraint is created, as it is clearly stated in the docs. See CREATE TABLE:

  PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. ...

Tested:

Version:

x=# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

Create the table and show the structure:

x=# create table abc (one int not null, two int not null, three int not null);
CREATE TABLE

x=# \d abc
      Table "public.abc"
 Column |  Type   | Modifiers
--------+---------+-----------
 one    | integer | not null
 two    | integer | not null
 three  | integer | not null

Add the primary key constraint  and show the structure again:

x=# alter table abc add constraint abc_pk primary key (one, two, three);
ALTER TABLE

x=# \d abc
      Table "public.abc"
 Column |  Type   | Modifiers
--------+---------+-----------
 one    | integer | not null
 two    | integer | not null
 three  | integer | not null
Indexes:
    "abc_pk" PRIMARY KEY, btree (one, two, three)

As for PgAdmin, looks like it doesn't show these automatically created indexes in the Indexes list. That's a decision its developers took. See an old discussion (from 2004!) about the exact same issue:

[pgAdmin-Support] Indexes generated for primary key are not shown by PgAdminIII.


Depends on the Azure offering you want to use.

Azure SQL Database

You can get Geo-replication setup which is very straight forward and will give you a read-only secondary.

Azure Portal : Geo replication

Azure SQL Managed Instance : You can use regular transaction replication (under preview) or geo-replication.

Azure Managed Instance


Query input must contain at least one table or query error

I think you've got your VALUES clause a bit mixed up.

Is this what you're after?

INSERT INTO Grade
       (StudentID, CourseID, Semester, Year, Grade)
SELECT s.StudentID, c.CourseID, 'Fall', '2016','B'
FROM Student s
JOIN Course c
ON s.StudentId = c.StudentId
WHERE s.StudentID = 5812356
AND c.CourseID = 'CS101';


Trying to return empty rows even if data does not exsist in SQL

SELECT u.user_id,
  column_name = COALESCE(MAX(change_date_time), '19000101')
  FROM dbo.users AS u
  LEFT OUTER JOIN dbo.audit_log_tx AS o
  ON u.user_id = o.user_id
  GROUP BY u.user_id;

Though I really don't like the use of token dates for missing data (especially 0).


How to know there is a maintenance job running on a postgresql database

In current versions of PostgreSQL, you can look at the *pg_stat_activity* view to find autovacuum tasks.  They will have *current_query* fields that start with "Mark autovacuum entries in pg_stat_activity that look like this:

autovacuum: VACUUM t

A query to count how many of those you have might look like this:

SELECT count(*) FROM pg_stat_activity WHERE position('autovacuum:' IN current_query) = 1;

And you can have your Python code check if the value returned is >0; if so, autovacuum is running.  You might want to track down and avoid running concurrently with manual VACUUM calls, too.


Turned out this was just lack of memory on the chosen RDS instance. Increased it to the "medium" instance instead of "micro" and it imported fine at around 10x the speed, with all tables.


Do auto created statistics indicate that you should build an index?

No, they don't.  If statistics are automatically created, it is because the optimizer can benefit from the single-column statistics of the cardinality estimates.  It's simply an option.  I'm guessing your real question may stem from statistics being automatically updated (as opposed to automatically create).

BOL definition for Statistics:

  Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result.

There are many parameters for exactly WHEN statistics are marked as invalid (therefore updated when the next using query executes).  Please see this blog post for a list of when this happens.

Take an example like this:  You have a sequential table.  You do 10 million sequential inserts at the end of the table.  There is going to be zero fragmentation in that table.  But the statistics will be forced as invalid (stale) and surely updated.

Therefore, the stats are updated.  But there is no need to rebuild that clustered index (provided it's not a heap).

Often times, there will be inserts that aren't as sequential and this will cause page splits.  So many inserts, much index fragmentation.  Many inserts, stale statistics.  So will stats be updated and the need for an index rebuild?  Yes, absolutely.

But these two do not walk hand-in-hand.


I wrote Windows Console app to run the tests and specify TrustServerCertificate=True in connection. It loops, connects and gets data.

Thank you for the code - it helps! The encrypted connection information shouldn't have any bearing on this, though.

  The latency is always 28 seconds, following failover.

This can happen for a variety of reasons. There are some subtle nuances that make it seem like it isn't actually up. Let's take a look.

string connetionString = "Trusted_Connection=yes;database=MyDataBse;server=fslistener,1433;Encrypt=True;TrustServerCertificate=True";

The connection string is not setting something called Connection Timeout which handles the way a connection is deemed to have timed out or failed. This is in seconds. The default is 15 seconds. Knowing this...

Assume SQL Server failed and we needed to fail over. The fastest this application would connect is the time it takes to failover. This is a combination of a few things:

The cluster detecting an issue
The cluster arbitrating of resources
SQL Server recovering the databases

Let's also assume that the cluster is setup with defaults. This means it'll take a minimum of 5 seconds to notice that something is wrong. It may take longer, depending on how the node fails up to a maximum of the SQL Server healthcheck interval (default 30 seconds).

You can already see that finding the failure can take anywhere from 5-30 seconds, by default.

The next step is initiating the failure and arbitrating for resources. This should take a trivial amount of time, a few seconds at most. This is where the listener is moved and becomes responsive to new login attempts.

Additionally there is the time is takes for the databases to redo and become available - especially if there is a default database specified that is part of the AG (versus one that isn't, say master). This can take anywhere from near instantaneous to many minutes depending on use and hardware subsystems. Let's assume, for this, it only takes 3 seconds.

Adding this all up we have:

5 seconds to find the failure
2 seconds to arbitrate and come online
3 seconds for the database to be available

This totals out to 10 seconds. Now, remember the Time Out I was talking about earlier. If the application attempts to connect and receives a login timeout, that means we waited 15 seconds and didn't get a response. This doesn't mean we constantly tried for 15 seconds, it means we sent a request and patiently sat there, waiting. Adding in a single timeout wait of 15 seconds now brings our total up to 25 seconds. That's assuming a whole bunch of things.

Wrapping up

In the end, the actual failover time will be held in the SQL Server errorlog by finding the start of the failover and the database online messages. Additionally, extended events can be used to find the actual failover times.

The application may (most likely will) see a longer "downtime" due to the way it is coded. In this instance, a default timeout value of 15 seconds is most likely adding and additional overhead. The code also sleeps for 1 second between retries which makes a total of 16 seconds. The connection string has a default database which means it takes us even longer to connect as the database is required to be online for the connection to be established. It takes time to detect and arbitrate the failover, this needs to be taken into account.

More information: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout(v=vs.110).aspx


How to Repair "Orphaned" Database in SQL Server 2016

You cannot attach a table to a database.  You can get the system to make a script for CREATE TABLE which you would then have to run on the other database.  However, you already have that script so you might as well use it.

If you know the table names that were accidentally created and you don't want to delete them one by one then you can make a quick script to delete the "bad" copies from the other database.

IF EXISTS (SELECT * FROM information_schema.tables WHERE name = 'badtable')
 DROP TABLE badtable;

In 2016 you could also:

DROP TABLE IF EXISTS dbo.badtable;


Have foreign key constraint and index key for the same column(s)

Constraints and Indexes serve two different purposes in the database.

Constraints prevent data that violates the constraints from being stored in the database.  This protects future users of the data, but it limits flexibility and adds overhead.  For foreign key constraints such as the ones you have presented, the overhead is usually small, compared with the benefit of avoiding orphan references.

Indexes provide a map between key values and retrieval pointers.  SQL users never deal directly with retrieval pointers in SQL.  (Well, there are some dialects of SQL that let you do that, but it's generally a bad idea anyway.)  When the optimizer is evaluating strategies for retrieval, and estimating the cost, the presence or absence of an index may make the optimizer choose one strategy over another.  Sometimes the speed-up is dramatic.  Indexes cost a little space to store, and a little time to update when the data changes.  Some indexes are worth the cost, others are not.

In your case, I would play around with leaving out the index you've called fk_merchant_appointment.  It may be the case that MySQL will use the apointments_index instead, and get most of the benefits with less cost.  I can't say for sure.


Sybase ASE doing showplan for all requests

SET SHOWPLAN [ON|OFF] is a session-level command in Sybase ASE, it's not a server-level setting (if it was a server-level setting you'd be able to alter it via sp_configure). Can you run wireshark (or something similar) on the packets being sent from the JDBC client to see if it's setting showplan on as part of the session initialization?

That said - showing the plan should not affect database CPU or memory usage, the plan is generated by the query optimiser anyway. However, showing the plan will increase network utilization.


Merge replication - corrupt row continually trying to INSERT

I've managed to hack my way around the issue, so I'll leave this here for posterity and in case anyone else has the problem. I've no doubt this is an extremely bad idea, and will cause my whole server to implode, but it has got replication firing again.

The msmerge_contents table seems to contain the "queued" UPDATEs and INSERTs, and it's one or more of the latter that seems to be causing the problem. So I did

delete from MSmerge_contents
where tablenick=xxx

... where xxx is the integer identity of the table from the other replication tables. This cleared out a couple of thousand rows, I restarted the agent, and after a bit of processing everything was back to normal. Fortunately, the table in question didn't contain anything too vital, so I don't need to worry about integrity too much.

Again, this is undoubtedly Not The Way To Do It, but it's appeared to work for me.


combining multiple rows with arbitrary number of columns

well it looks like I will use stuff xml again and parse out in application. however if any one knows any clever rearrangement of this query using cte's and cross or outer applys I would love to hear it.

        select distinct tv.Item,
       --tv.ItemDescription,
       tv.PackQty,
       TotalUsage = (
                        SELECT sum(tvss.Qty * -1)
                        FROM transactions_VIEW as tvss
                        WHERE tvss.Item = tv.Item
                          AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                          AND tvss.Supplier IN ('###', '###', '###')
                          AND tvss.TranType IN ('I','B')
                    ),
       [jobDesc:use] = STUFF((SELECT ';' + CAST(tvs.JobDescription as nvarchar(max)) +':'+CAST(count(*) as nvarchar(max))
                          from transactions_VIEW as tvs
                                   join Job as js on tvs.JobNumber = js.MyNo
                          WHERE js.Active = 1
                            AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                            AND tvs.Supplier IN ('###', '###','###')
                            AND tvs.TranType IN ('I', 'B')
                            AND tvs.Item = tv.Item
                          Group By tvs.JobDescription FOR XML Path ('')), 1, 1, '')
from transactions_VIEW as tv
         join Job as j on tv.JobNumber = j.MyNo
WHERE j.Active = 1
  AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
  AND tv.Supplier IN ('###', '###','###')
  AND tv.TranType IN ('I', 'B')
group by tv.Item, tv.JobDescription, tv.ItemDescription, Qty, PackQty


What is the best alternative for MySQL Query Cache?

The alternative to query cache is to write code to cache data yourself, in the application. You decide which query results need to be cached, for how long, and in what format.

Many queries are against constantly-changing data, and your application requires viewing the current data.

Other queries are tolerant of viewing data that is not strictly current, so it's fine to use the cached copy for a few minutes, even though the underlying data has been updated.

Caching the data in the form of a result set isn't the only option. I have an application where I format results from several queries into one string which is a fragment of HTML, that store that string in the cache. It avoids having to re-format that part of the HTML every time someone views the page.

You can also use a shared cache like Redis or Memcached so if your app is deployed as multiple appserver instances, they can all share the same cache.
This is more work for you as a developer. There's no automatic solution for a server like MySQL to know which queries deserve to be cached.
Optimizing is often more work than simple, non-optimal solutions.


Best query to pick row based on IP address and DateStamp

I'm not sure if this sample data cover your current issue.

CREATE TABLE Table1(account int, ip_address varchar(15), created_date date);
INSERT INTO Table1 VALUES
(1, '10.0.0.1', '20170101'),
(1, '10.0.0.1', '20170201'),
(1, '10.0.0.1', '20170301'),
(2, '10.0.0.2', '20170201'),
(3, '10.0.0.3', '20170201');

CREATE TABLE Table2(ip_address varchar(15), created_date date);
INSERT INTO Table2 VALUES
('10.0.0.1', '20170201'),
('10.0.0.2', '20170201'),
('10.0.0.3', '20170201');

This solution assumes that, at least, there is a created_date on your Table1 >= of the created_date on Tabel2

SELECT (SELECT TOP (1) account
        FROM     Table1 t1
        WHERE    t1.ip_address = t2.ip_address
        AND      t1.created_date >= t2.created_date
        ORDER BY t1.ip_address, t1.created_date desc) account,
       t2.ip_address,
       t2.created_date
FROM   Table2 t2

account | ip_address | created_date
------: | :--------- | :------------------
      1 | 10.0.0.1   | 01/02/2017 00:00:00
      2 | 10.0.0.2   | 01/02/2017 00:00:00
      3 | 10.0.0.3   | 01/02/2017 00:00:00

dbfiddle here
If there is no an exact date, you can build a list of periods:

WITH ipList as
(
    SELECT account, ip_address, created_date,
           COALESCE(LEAD(created_date) OVER (PARTITION BY account, ip_address ORDER BY created_date), created_date) next_date
    FROM   Table1
)
select * from iplist;
GO

account | ip_address | created_date        | next_date
------: | :--------- | :------------------ | :------------------
      1 | 10.0.0.1   | 01/01/2017 00:00:00 | 01/02/2017 00:00:00
      1 | 10.0.0.1   | 01/02/2017 00:00:00 | 01/03/2017 00:00:00
      1 | 10.0.0.1   | 01/03/2017 00:00:00 | 01/03/2017 00:00:00
      2 | 10.0.0.2   | 01/02/2017 00:00:00 | 01/02/2017 00:00:00
      3 | 10.0.0.3   | 01/02/2017 00:00:00 | 01/02/2017 00:00:00

And then try to find the best match:

WITH ipList as
(
    SELECT account, ip_address, created_date,
           COALESCE(LEAD(created_date) OVER (PARTITION BY account, ip_address ORDER BY created_date), created_date) next_date
    FROM   Table1
)
SELECT    (SELECT TOP(1) ipList.account
           FROM   ipList
           WHERE  t2.ip_address = ipList.ip_address
           AND    t2.created_date >= ipList.created_date
           AND    t2.created_date <=  ipList.next_date) account,
          t2.ip_address,
          t2.created_date
FROM      Table2 t2;
GO

account | ip_address | created_date
------: | :--------- | :------------------
      1 | 10.0.0.1   | 01/02/2017 00:00:00
      2 | 10.0.0.2   | 01/02/2017 00:00:00
      3 | 10.0.0.3   | 01/02/2017 00:00:00

dbfiddle here


Optimizing query for large table with `rand()` ordering

There is no good way to do ORDER BY RAND() LIMIT 1, but this has several suggestions.  Some depend on AUTO_INCREMENT, some based on an extra column.


Sometimes getting quotes in Oracle spool sometimes not

The problem was in on case I was doing

/*csv*/ -- this works

/* csv */ -- this does not work.

Thanks


install FirstResponderKit in Azure SQL DB?

From the documentation on GitHub - (highlighting mine)

The First Responder Kit runs on:

SQL Server 2008, 2008R2, 2012, 2014, 2016, 2017 on Windows - yes,
fully supported
SQL Server 2017 on Linux - yes, fully supported except sp_AllNightLog
and sp_DatabaseRestore, which require xp_cmdshell, which Microsoft
doesn't provide on Linux
SQL Server 2000, 2005 - not supported by Microsoft anymore, so we
don't either
Amazon RDS SQL Server - fully supported
Azure SQL DB - It's a dice roll. Microsoft changes DMV contents in here without warning, so no guarantees


Avg write latency spikes for disk more than usual. Need help finding the culprit

My question is how can I check what in TempDB if it is would have
caused this spike?

To analyze what impact those queries have on tempdb you could use sp_whoisactive since one of the info it outputs is the use of tempdb:

The most confusing of these columns are those related to tempdb. Each    of the columns reports a number of 8 KB pages. The
[tempdb_allocations] column is collected directly from the
tempdb-related DMVs, and indicates how many pages were allocated in
tempdb due to temporary tables, LOB types, spools, or other
consumers. The [tempdb_current] column is computed by subtracting the
deallocated pages information reported by the tempdb DMVs from the
number of allocations. Seeing a high number of allocations with a
small amount of current pages means that your query may be slamming
tempdb, but is not causing it to grow. Seeing a large number of
current pages means that your query may be responsible for all of
those auto-grows you keep noticing.

Another way to show that you have a tempdb related problem is having sp_BlitzCache™ Result: TempDB Spills. As that page says:

To find the queries that are causing spills, you have several options
– you can capture and examine actual execution plans, or you can
collect exchange spill events through extended events. Paul White
covers your options in his reply to What frequency of hash/sort
spills into tempdb is
concerning?

Also can those select queries also contribute to TempDB write latency
. These select queries are run on tables over TBs of data and user can
have part of queries like order by and group by from what I have seen.

Certainly yes. If you check the tempdb database doc, you'll see it stores the following objects:

Internal objects that the database engine creates. They include:
Work tables to store intermediate results for spools, cursors, sorts,    and temporary large object (LOB) storage. Work files for
hash join or    hash aggregate operations. Intermediate sort
results for operations    such as creating or rebuilding indexes (if
SORT_IN_TEMPDB is    specified), or certain GROUP BY, ORDER BY, or
UNION queries.


Why is casting a string to DATETIME not accurate to the ms?

DateTime has a precision of has a  0.00333(1/300th) seconds, so sql server rounds value to nearest 1/300th.

If you are using SQL Server 2008 above, use DateTime2 which can have a precision of millisecond, even down to 100 nanoseconds and can hold the accurate value.

SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME2(3));


Correct way to perform an UPDATE on multiple tables?

Usually updates to two tables are done as two different queries to avoid making a mistake and badly mangling both tables.

A combined update is a form of JOIN, so you need to be careful when establishing update conditions. I don't see how this query you've created is any better than two simple queries that achieve the same effect. If you need to make the operation atomic, frame it inside a transaction.


The locking of documents differs between the WiredTiger and the MMAPv1 storage engine. When using the Wired Tiger storage engine, write operations only hold an exclusive lock at document level. This means that multiple threads can update multiple documents in the same collection at the same time, however, they can NOT update the same document at the same time. In addition to an exclusive lock at document level, WiredTigger also holds intent locks at the global, database and collection levels. These intent locks will not block reading or writing operations,however, when the storage engine detects conflicts between two operations, one will incur a write conflict causing MongoDB to transparently retry that operation.

When using the classical 'MMAPv1` storage engine, a write operation hold an exclusive lock on the entire collection and therefore multiple threads can NOT write to the same collection at the same time, let alone write to the same document at the same time.

The locking does not occur when reading from a document, so two threads can a document at the same time. Regarding $isolated, you need to use this for write operations that affect multiple documents. Setting this flag prevents a write operation from yielding to other reads and writes once the first document is written. For example, if you are running an update query on document 1,2,...100, setting the $isolated flag will prevent document 100 being changed while the update query is updating document 1.


Reorder partitioned results by specific column

I would get the ROW_NUMBER logic happening first, by enclosing it in a sub-query, and then filtering by the top 50 rows per timezone. This also makes the query immune to new timezones being added to your FROM table.

SELECT *
FROM (
    SELECT ID
        ,event_date
        ,timezone
        ,ROW_NUMBER() OVER (
                PARTITION BY timezone --these are the main groups you want
                ORDER BY event_date DESC --DESC picks the LATEST dates first (ASC would pick the earliest)
            ) AS RowNumber
    FROM...
    ) Results
WHERE RowNumber <= 50 --top X rows per timezone
ORDER BY timezone, RowNumber


Postgresql. Generate sets of random integers

Would this be what you want?

SELECT
   n, generate_series (1,16384), (random()*200000)::int AS id
FROM
   generate_series (1,10) AS x(n) ;

or the similar:

SELECT
    n, i, (random()*200000)::int AS id
FROM
    generate_series (1,10) AS x(n)
  CROSS JOIN
    generate_series (1,16384) AS y(i) ;


MySQL update row cell with 0 value with value from closest row that does not have 0 value

SET @var :=0;
UPDATE records
SET time= (@var :=CASE WHEN time = '0' THEN @var ELSE time END)
ORDER BY id;

Fiddle

Add DESC after ORDER BY if you want to update time with the time value of the next id.


The REVERSE is clearing our your string, not entirely sure why.

EDIT: Here's why. In this sequence the string ' to ' is actually ' ot '. (Ken sniped it mid edit!)

Fixed:

SELECT
LEFT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5) AS 'ChangeType'
,REPLACE(SUBSTRING(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5, CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - 5), '"', '') AS 'ChangeFrom'
,REPLACE(RIGHT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' ot ', REVERSE(CONVERT(VARCHAR(MAX), bp.bp_comment)))), '"', '') AS 'ChangeTo'
FROM
bug_posts bp

Here's an alternative approach.

SELECT LEFT( CONVERT( varchar( max ) , bp.bp_comment ) , CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                   + 5 )AS 'ChangeType' ,
   REPLACE( SUBSTRING( CONVERT( varchar( max ) , bp.bp_comment ) , CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                 + 5 , CHARINDEX( ' to ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                     - CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                     - 5 ) , '"' , '' )AS 'ChangeFrom' ,
   REPLACE(REPLACE(RIGHT(bp.bp_comment, LEN(bp.bp_comment) - CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment))), '"', ''), 'to ', '') AS 'ChangeTo'
 FROM bug_posts bp;


I believe that it's in seconds.  I've got a script running a loop that's capturing the value once every minute and it's going up steadily by ~60 every 1 minute interval.


move old data to another table when try to insert data

As a sample:

CREATE TABLE main (id INT, "date" INT);
CREATE TABLE old (id INT, "date" INT);

CREATE TRIGGER old_to_old
ON main
FOR INSERT
AS
BEGIN
    INSERT INTO old (id, "date")
        SELECT id, "date" FROM inserted WHERE "date" < 5;
    DELETE FROM main
        FROM main
        JOIN inserted ON main.id=inserted.id
        WHERE inserted."date" < 5;
END;

or

CREATE TRIGGER old_to_old
ON main
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO old (id, "date")
        SELECT id, "date" FROM inserted WHERE "date" < 5;
    INSERT INTO main (id, "date")
        SELECT id, "date" FROM inserted WHERE "date" >= 5;
END;

INSERT INTO main VALUES (1,1), (4,4), (7,7), (10,10);

SELECT *, 'main' from_where FROM main
UNION ALL
SELECT *, 'old' FROM old;

Output:

id | date | from_where
-: | ---: | :---------
 7 |    7 | main
10 |   10 | main
 4 |    4 | old
 1 |    1 | old

fiddle (FOR)

fiddle (INSTEAD OF)


Doing a left join and having every match include an extra null row

I see 3 ways to do this but all involve a UNION ALL:

your version`:

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION ALL

SELECT
    T1.C1, ..., T1.CN,
    NULL, ... NULL
FROM
    Table1 T1 ;

slightly changing the second part:

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION ALL

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 LEFT JOIN Table2 T2
        ON 0 = 1 ;                   -- FALSE

first a UNION, then join:

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 JOIN
      ( SELECT * FROM T2
        UNION ALL
        SELECT NULL, ..., NULL
       ) AS T2
        ON T1.Key1 = T2.Key1
        OR T2.Key1 IS NULL ;

I don't think there will be much difference in execution plans and efficiency but the first one seems more simple.


SQL Server 2005 to 2012 Replication, cannot access replicated data

This is covered in Frequently Asked Questions for Replication Administrators:

  Are tables locked during snapshot generation?

  The length of time that the locks are taken depends on the type of
  replication used:

For merge publications, the Snapshot Agent does not take any locks.

For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.

For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

  Because locks prevent other users from updating the tables, the
  Snapshot Agent should be scheduled to execute during periods of lower
  activity on the database, especially for snapshot publications.


PostgreSQL:- How to continue execution even INSERT INTO SELECT FROM fails and how to work with refcursor as out param with other output params

Honestly there are so many things wrong with this question, it's pretty hard to know where to start. Your function code is not valid and won't compile due to multiple issues like no variable declaration and almost certainly some record / type mismatches, as well as any return method. Since you don't include the full code, we can't tell if the message your getting back is from where you think it is (I don't think it is, but can't be 100% sure).
It sounds like both things are doable, but you're going to have to clean up the code and provide more info (or smaller test cases) to get real help. The one extra thing I was thinking to share was, if you want to use the refcursor approach, you can; here is an example of one way to do that using a stored procedure (would also work with a function), because these examples are hard to find: https://github.com/xzilla/pagila/commit/52c0ef17609c6d0a3a0e72d6de10ad9101314ffa (there are some other functions in this project as well that might server as good examples. HTH.


Adding a user to MySQL with 'name'@'%' fails with ERROR 1396

According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
mysql> CREATE USER 'name' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

If you delete the user and still get the message, try running FLUSH PRIVILEGES.

Also see this SO question for additional info.


A methodology for tracing a query that sporadically runs for hours instead of seconds

Based on your thorough outline, I believe you may be experiencing a problem with your table statistics.

Tables only automatically update their statistics when certain thresholds of row updates are passed, in the case of any table over 500 rows it requires 500+20% of the rows to change. For example your million row table requires 200,500 row changes before it will update statistics.

An index REBUILD will update the statistics on a table (REORGANIZE does not).

I think your tables are growing over time and eventually invalidating your statistics, but not enough to trigger an automatic update. However, your reindexing job is not triggering an actual REBUILD for the relevant tables until after your query starts performing poorly due to the last couple changes from earlier in the day. If your changes are inconsistent and small enough increments this would make the performance degradation consistent and sudden. Your reindexing job then fixes the problem after the query has ran poorly and everything looks fine the next morning.

Things to check to confirm:

If you can catch a 'bad' query plan check the pipes' Actual versus Estimated number of rows returned. If this is radically different it's red flag for statistics issues.
Check the criteria on your Reindex Job to confirm that it's not updating the indexes on the query's tables regularly.
If you can use the following query regularly you can track when the statistics are updated:

SELECT name AS index_name
   ,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('SchemaName.TableName')

If you are running SQL Server 2008 R2 Service Pack 1 or later, you have the option to enable documented trace flag 2371 to dynamically update statistics more frequently:

  The higher the number of rows in a table, the lower the threshold will become to trigger an update of the statistics. For example, if the trace flag is activated, update statistics will be triggered on a table with 1 billion rows when 1 million changes occur. If the trace flag is not activated, then the same table with 1 billion records would need 200 million changes before an update statistics is triggered.

The new behaviour is enabled by default (i.e. without the trace flag) from SQL Server 2016 onward, for databases at compatibility level >= 130.


How to interpret and modify a dBase (.dbf) file?

Do NOT directly manipulate the .dbf-files. There is an ODBC driver that does all the low-level work for you. Save yourself the headaches.


You can modify the jobs to send email on failure. The problem with that approach is that you're going to know that job failed but why it failed will be a mystery until you get to a computer, VPN into your network, fire up SSMS, grab a screenshot and send it to the responsible party.

A rough framework I've used follows

sp_send_dbmail
SSISDB views
sql agent running periodically

This does stuff

DECLARE
        @profile_name sysname = 'SQLAdmins'
,       @recipients varchar(max) = 'billinkc@friedChicken.com'
,       @copy_recipients varchar(max) = NULL
,       @blind_copy_recipients varchar(max) = NULL
,       @subject nvarchar(255) = 'failed package test'
,       @body nvarchar(max) = 'Stuff has failed, fix please'
,       @body_format varchar(20) = NULL
,       @importance varchar(6) = 'NORMAL'
,       @sensitivity varchar(12) = 'NORMAL'
,       @file_attachments nvarchar(max) = NULL
,       @query nvarchar(max)
   = N'
SELECT
    O.object_name AS FailingPackageName
,   O.object_id
,   O.caller_name
,   O.server_name
,   O.operation_id
,   OM.message_time
,   EM.message_desc
,   D.message_source_desc
,   OM.message
FROM
    SSISDB.catalog.operation_messages AS OM
    INNER JOIN
        SSISDB.catalog.operations AS O
        ON O.operation_id = OM.operation_id
    INNER JOIN
    (
        VALUES
            (-1,''Unknown'')
        ,   (120,''Error'')
        ,   (110,''Warning'')
        ,   (70,''Information'')
        ,   (10,''Pre-validate'')
        ,   (20,''Post-validate'')
        ,   (30,''Pre-execute'')
        ,   (40,''Post-execute'')
        ,   (60,''Progress'')
        ,   (50,''StatusChange'')
        ,   (100,''QueryCancel'')
        ,   (130,''TaskFailed'')
        ,   (90,''Diagnostic'')
        ,   (200,''Custom'')
        ,   (140,''DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.'')
        ,   (400,''NonDiagnostic'')
        ,   (80,''VariableValueChanged'')
    ) EM (message_type, message_desc)
        ON EM.message_type = OM.message_type
    INNER JOIN
    (
        VALUES
            (10,''Entry APIs, such as T-SQL and CLR Stored procedures'')
        ,   (20,''External process used to run package (ISServerExec.exe)'')
        ,   (30,''Package-level objects'')
        ,   (40,''Control Flow tasks'')
        ,   (50,''Control Flow containers'')
        ,   (60,''Data Flow task'')
    ) D (message_source_type, message_source_desc)
        ON D.message_source_type = OM.message_source_type
WHERE
    OM.operation_id =
    (
        SELECT
            MAX(OM.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    )
    AND OM.message_type IN (120, 130);
'
,       @execute_query_database sysname = NULL
,       @attach_query_result_as_file bit = 0
,       @query_attachment_filename nvarchar(260) = NULL
,       @query_result_header bit = 1
,       @query_result_width int = 256
,       @query_result_separator char(1) = char(13)
,       @exclude_query_output bit  = 0
,       @append_query_error bit = 0
,       @query_no_truncate bit = 0
,       @query_result_no_padding bit = 0
,       @mailitem_id int = NULL
,       @from_address varchar(max) = NULL
,       @reply_to varchar(max) = NULL;

EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name
,   @recipients
,   @copy_recipients
,   @blind_copy_recipients
,   @subject
,   @body
,   @body_format
,   @importance
,   @sensitivity
,   @file_attachments
,   @query
,   @execute_query_database
,   @attach_query_result_as_file
,   @query_attachment_filename
,   @query_result_header
,   @query_result_width
,   @query_result_separator
,   @exclude_query_output
,   @append_query_error
,   @query_no_truncate
,   @query_result_no_padding
,   @mailitem_id OUTPUT
,   @from_address
,   @reply_to;

So what'd we do? We found the last SSIS package that ran from the SSISDB that failed (message_type 120 = failed)

        SELECT
            MAX(OM.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120

We then collected relevant bits of information (all error and failure messages) about the run with the rest of the query against the SSISDB objects.

That gives enough information for someone to know the package that failed, what folder it was in, when etc. But notice that SSISDB query is assigned to a variable.

I'm going to use that variable as a parameter to sp_send_dbmail When I invoke that proc with the query parameter, I'm going to get an email telling me something broke (which is the level of info I get out of the job step) but then I have an attachment which will be the actual results of the query. Now that's pretty handy.

Left as an exercise for the user is to create a "already notified" list of operation_ids so you don't keep spamming people. Possibly another table that associates folders and or/packages with a list of recipients. This approach assumes only one package runs at a time. That's a silly assumption but twas valid when I wrote this. It would be better to have two tables: Failed and Reported. Collect the unique operation ids into the Failed table every 5 minutes. Every 30 minutes collect those IDs, send emails and move them into the Reported table. Now you have a history that you've processed them.


How to report on sparse areas of sparse fact table

This is a common design pattern for HR systems, so there's any amount of precedent for this type of approach.

You will find that the format used by SSAS stores this type of data quite efficiently, so data volumes won't be a significant problem.  I've seen this done on a HR cube with a row count around 300 million on fairly old hardware using AS2000.  2008R2 on modern hardware shouldn't have a problem with it.


I've set up this example:

create table tbl (horse_name varchar(100));
insert into tbl values ('Rocket(FRA)'),('Apple Jade(IRE) 2'),('Zac');

If last parenthese is located always at end of the string you could use next syntax:

select case when locate('(', horse_name) = 0
            then horse_name
            else left(horse_name, locate('(', horse_name) - 1)
       end as horse_name
from   tbl;

But if there are additional characters after the last parenthese you could CONCAT left and right substrings in this way:

select case when locate('(', horse_name) = 0
            then horse_name
            else concat(left(horse_name, locate('(', horse_name) - 1),
                        right(horse_name, length(horse_name) - locate(')', horse_name)))
       end as horse_name
from   tbl;

This is the final result (note I've added a 2 after (IRE):

| horse_name   |
| :----------- |
| Rocket       |
| Apple Jade 2 |
| Zac          |

db<>fiddle here


Which datatype is used to store video files

Oracle Multimedia (formerly known as Oracle interMedia) lets you write applications to manage images, audio,video, and other heterogeneous media data in Oracle databases.

I have referenced interMedia as you are using Oracle 10g.

interMedia User's Guide

Here is a nice answer given by Justin Cave, which explains why would a person store a video file in a BLOB in a database and what are the advantages and disadvantages compared to storing pointers to the location of the video file.

BLOB's in SQL that stores a Video file


Is an anomaly possible if a serializable transaction interfers with another isolation level?

PostgreSQL wiki https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation states:

  Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

In your example since transactions A and B are not both SERIALIZABLE, anomalies can happen.


Transactional replication creating a fixup index that's sleeping, but blocking other processes

If replication is automatically creating indexes, then you probably can't avoid it.  The only way to do so might be to create a matching index on the subscriber.

Try running the merge agent from the command line with a more verbose logging level and see what information it returns. It might be bumping against something which isn't being returned as an error.


SQL Server Select Count READ_COMMITTED_SNAPSHOT QUESTION

Be careful with READ_COMMITTED_SNAPSHOT: if you set it on, it can cause lots of subtle bugs.

Also READ_COMMITTED_SNAPSHOT is the default isolation level, which may be overridden by something. Run DBCC USEROPTIONS to determine the actual isolation level your select runs under.

I would explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT right before your select. That way you will be sure your select never embraces in deadlocks, and you do not break any other code, like READ_COMMITTED_SNAPSHOT might.


The development process has falsified the premiss of the question, i.e. each of the 2 derivations of metric1 have become inputs to derivations at several metric levels.

If this had not been the case, I'm now inclined to implement the original scenario via alternate views that duplicate query logic while being based on one or the other derivation of metric1.  This seems from my pov a simpler approach, in that it avoids an additional schema and instead adopts a view-naming convention where view names denote the particular metric1 derivation.


The connection is broken and recovery is not possible - sql server error

I had this issue when I was trying to Deploying a Database to Azure SQL Server. I tried a few times and I got the same error. I double-check the Azure permissions, Firewall, user and pwd.
What I did to fix it was run SQL Management Studio as Administrator. Simple as it.
I believe the problem was some permission to the temporary file to "C:\Users\xxx\AppData\Local\Temp" to create bacpac file.


Table to create Ids for one table to many table relationships

You could utilize a sequence object to facilitate the mechanism of generating the IDs if that is the sole purpose of the CommentParents table.  Take a look at the TechNet documentation for the TechNet SQL Server 2014 Sequence Documentation for more detailed information.  What this sequence object will do that your current table can't do is allow you to grab a value and assign it to a variable ahead of time without worrying about doing an INSERT/SELECT SCOPE_IDENTITY() process.  The advantage here is at great scale the current design you have would break down due to the need to maintain metadata overhead where SQL Server has caching for Sequence objects.


Very large btree index with few rows (openstreetmap gis data)

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

  access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and thus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

Unexpected Seq Scan when doing query against boolean with value NULL
Join table on some conditions


Database design: sharing tables between databases?

Normally when I have something like this, I have a table in one database and a VIEW pointing to that table in the other database.   Can you just use a view on the other database which references the first one?  If not then maybe a "materialized view/mirror table" of sorts which be a "slave" table that would get refreshed once an hour or once a day/week/month.


Could the slow down be due to pulling from the multiple SourceServerX databases as opposed to the one ODS database like before even though these are all on the same instance of SQL Server?

Yes.

    If we combined the tables into one database and then added the SourceServer name to the front of the tables would the views then run faster?

Probably not.

    With replication could we combine the tables from all the SourceServers into one set of tables adding in the SourceServerID column like we did for the daily load?

Technically yes, but you would have to write custom replication procedures complicated to handle re-initialization.  So I wouldn't recommend it.

    Is there a different method that would allow for better performance while maintaining the up to date data that replication provides?

Replicate into separate tables/databases and periodically MERGE into a single ODS database.


how to write pl/sql named programme for this

You could use a global system context to do this.  See here Oracle context

The user logs on, your stored procedure determines the value of MMRun for that user and the context is set for this session.
Then your triggers or insert/update/delete triggers or procedures refer to the context for permission to edit.

Really though this sounds like something that will require a more fine grained permission structure in the future. I've never seen something simple like can modify/cannot modify stay simple. Usually you are asked to provide variations on this in the future. There are  open source PL/SQL  packages available which can be adapted to do this.


sqlplus Setting the exit code of a script

Try it like this:

var retCode number
exec :retCode := 30;
exit :retCode

(See http://www.orafaq.com/forum/mv/msg/80574/233106/0/#msg_233106)

Bottom line is that retCode must be a variable defined in SQL*PLUS's scope. Your DECLARE is inside a code block, and SQL*PLUS can't see into it.


For Delete trigger with condition on a joined table

Have you tried:

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
AFTER DELETE AS
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
SELECT 'Borrower' AS TableName ,
       i.ssn,
       GETDATE()
FROM deleted i
join filedata fd on (i.FileDataID = fd.FileDataID )
INNER JOIN (select OrganizationID from Organization where code not like '26%') org on (fd.OrganizationID = org.OrganizationId)
where fd.filename not like '26%'
and fd.filename not like '%.copy' and fd.filename not like '%+'

You shouldn't need to tie back to the Borrower table - the deleted table should hold all the same columns as Borrower did.


In 2014, using 32bit architecture for any critical app is insane. So, I would suggest:

Upgrade your web-app server to 64bit (soon you will not find 32bit
editions for most software). It is not that hard and you get better memory management, performance with the exact same hardware.
Still, if you do not want to do that, install MySQL DB (64bit edition) on a 64bit Ubuntu server to get at least there the benefits of a 64bit architecture. There is no danger when you interact with SQL queries if the architectures of the web-app server and the DB server are different. When (when and not if because it will happen eventually) you will be forced to change your web app server architecture, you will have one less thing to worry about.


I suggest you stop using sp_who2, there are better ways to look at that information.  Even you do not want to use third party script you are better off joining following dmvs and get what you need.

sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_sql_text
sys.dm_exec_query_plan
sys.dm_os_tasks

I use sp_whoisactive by Adam Machanic and will not try to write anything by myself.

To answer your question session id 428 is running in parallel meaning multiple threads and you can see it using below query.  Login name is showing only for exec_context_id =0 which is the parent thread.

SELECT er.session_id,
       er.status,
       er.command,
       er.blocking_session_id,
       er.wait_type,
       th.os_thread_id,
       os.cpu_id,
       ot.exec_context_id,
       ot.task_state,
       st.text
FROM   sys.dm_os_threads th
inner join sys.dm_os_tasks ot on th.worker_address = ot.worker_address
inner join sys.dm_exec_requests er on ot.session_id = er.session_id
inner join sys.dm_os_schedulers os on th.scheduler_address = os.scheduler_address
       CROSS apply sys.Dm_exec_sql_text(er.sql_handle) st
WHERE  er.session_id IN (SELECT session_id
                         FROM   sys.dm_os_tasks
                         GROUP  BY session_id
                         HAVING Count(exec_context_id) > 1
                         )


SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type

Check out this link - http://www.bobpusateri.com/archive/2012/04/compression-estimates-that-dont-make-sense/

  BOL states that “If the requested compression setting is same as the current compression setting, the stored procedure will return the estimated size with no data fragmentation and using the existing fill factor.”

The above link does point to a Microsoft Connect item that was opened that seemed 'somewhat' similar, but not exactly like your issue.  Microsoft close it indicating no fix would be forthcoming.


SQL Server 2016: Bidirectional Transactional Replication using Backup, Do I use/need Snapshot?

Snapshot are not required if subscribers are setup using backup\restore or another technique to preload the subscriber data.


Postgres Upsert Performance Considerations (millions of rows/hour)

INSERT ... ON CONFLICT is the fastest way to upsert data. If it is fast enough for you can only be seen from a performance test.

It matters a lot if you use ON CONFLICT DO NOTHING or if you use an UPDATE clause:

With DO NOTHING, there won't be any dead tuples that have to be cleaned up. If you perform an UPDATE, there will be a dead tuple, and cleaning up these dead tuples may turn out to be the bottleneck in your processing.

Use a fillfactor of 70 or so and make sure that none of the updated columns are indexed. Then you can get HOT updates which may save your day.


Restrictions after you failover to an upgraded server instance

After failing over from an earlier server instance to a SQL Server 2016 server instance, the database session is suspended.

Once you failover to the 2016 instance, the database is now upgraded fully to 2016. This is a one way trip and will no longer be able to be hosted on anything but SQL Server 2016. Since the partner is not 2016, the data movement will be suspended (stopped). No new data will "flow" back to the older instance.

  It cannot be resumed until the other partner has been upgraded

Until the old principal server (now the mirror server) is upgraded to 2016 you will not be able to resume data movement - no data will flow until you upgrade the older instance to 2016 and manually resume the data movement.


Error using an Execute SQL script to load a date variable in SSIS

Try mapping your result set, not parameters. The setting is similar; the Result Names are 0 and 1 and the ? = are not necessary in your select statement.

SELECT TOP 1 Id, BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id

Also, make sure your Result Set is set to Single row.


MSAccess: creating a new row in a related table and linking it back to the "master" table

Your question is legitimate and there are some reasonable patterns that you can follow to get it working.  However, I also feel like your question demands some comments regarding Access forms and controls. so sorry if it's a bit chatty.

It seems completely reasonable to be able to add a new make when entering a new model for a make not yet in the database.  That can be done, so my next comments are not to discourage or dissuade.  However, the built-in controls and data binding in Access do not facilitate this behavior and there are good reasons for it.  Custom macro and/or VBA event programming can overcome these limitations.

This also requires an understanding of what a ComboBox is really doing and not expecting it to do more.  The value list of a ComboBox is largely read-only.  The primary reason for this is that it accepts almost any complex query--including non-updatable queries--as the RowSource for the list values.  Just because the [Makes] table only has one Text column (beside the ID column) does not mean that the ComboBox has any built-in way to automatically insert a new value into that table.  I'm not familiar with any ComboBox even in other environments that automatically facilitate such behavior without custom event programming.  This is not just a question of elegance, rather the impractical ability of updating any arbitrary source.  From my experience, most of my ComboBoxes have lists from complex data sources that would not accept a single value for adding to the list.

Elegance is a very subjective thing.  Honestly, one could argue that normalized relational database tables are completely inelegant compared to a well-designed object model with modern object databases, but that's a longstanding debate and Access is certainly not state-of-the-art.  Access often demands that you deal with separate inserts, retrieving ID values, explicitly refreshing form and control query sources, etc.  Pessimistically, Access is not elegant.  But it can be very convenient and has its plus sides when you don't/can't worry about installing and/or maintaining other RDMS systems and marrying it to a separate GUI environment.

As for the ComboBox properties, it is possible to hide extra column values using the ColumnWidth property, like ComboBox.ColumnWidth = "0;1.5".  This allows you to bind to the correct numeric value while displaying the corresponding text values ([makes].[make] in this case). How would you even properly bind to the [model].[make] field without the numeric ID?

All that said, your own #1 option is probably the best approach:

  Write a macro to insert the new make if it doesn't exist, catch the new primary key, and feed it back into the model insert

I personally prefer VBA instead of a macros.  Consider handling the "Not In List" event of the ComboBox.

An alternative is to create a separate form for managing the [makes] separately.  Provide a button or link (or some other various way) to open the form for adding new [makes].  In your case this probably seems overkill since you only have one data field, but if you start adding more fields to the [makes] table (e.g. website URL for example), you will definitely need something beyond an automatic ComboBox.


Make 3 separate tables, or 1 combined table for similar data?

The full keys to your data are more complex that you indicate.  Sales tax rates can vary by jurisdiction and goods can be categorised as luxury or exempt. Wage rates can vary by industry and public holiday / normal work day.  Exchange rates have to be keyed on the source and destination currencies.
While your values may all be fractional numbers they have different units, which makes them different things.  Tax is "percentage", wages are "dollars per hour", ForEx rates are dimensionless ratios.
Your data scope my be constrained to eliminate these considerations, of course.  Only you can say.
Joe Celko covers this topic in depth here.


Generally, a transaction is exactly one of

committed
rolled back

Committed transactions are never rolled back.
It's how all RDBMS operate, on ACID principles

Now, there are some different cases where it may look like this rule has been broken. But it hasn't.

Before we look at these cases though, different user sessions do not share a connection. Each user/client has one connection to the SQL and all are isolated from each other. Connection pooling does not affect this.

Savepoints

You can SAVE a transaction and rollback to this savepoin,

That is, you can partially commit/rollback if you use savepoints but I've never seen anyone do this in real life code. I won't expand more.

Nested transactions

You can nest transactions but they don't really mean anything
Simply put, SQL Server does not really have nested transations even if @@TRANCOUNT can be higher than one.

A begin increments @@TRANCOUNT by one
A rollback will set @@TRANCOUNT to zero
A commit decrements @@TRANCOUNT by one

A longer explanation is in this SO answer


PostgresSQL - cumulative sum grouped by values

WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno, SUM(c.peso) as totale, c.tiporifiuto
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3 AND c.dataora >= '2020-06-01' AND c.dataora <= '2020-06-12'
    GROUP BY giorno, c.tiporifiuto
    ORDER BY giorno ASC, c.tiporifiuto ASC
)
SELECT giorno, SUM(totale) OVER (PARTITION BY tiporifiuto ORDER BY giorno ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as totale, tiporifiuto
FROM data


An insert is always within a transaction.

If you don't have an explicit BEGIN TRAN ... COMMIT or SET IMPLICIT_TRANSACTIONS ON then the statement runs as a self contained auto commit transaction.

The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too.

Triggers implicitly have XACT_ABORT on. An error with this setting on will automatically lead to transaction rollback (except for errors raised in the code with the RAISERROR statement).


There seems that some people used to patch the source code of xtrabackup to make it usable in OSX:

http://sourceforge.net/projects/xtrabackup-osx/

But expect some problems, as some of the functionalities depend on a linux system (but you can try to give it a shot). The other alternative is MySQL Enterprise backup, which has full Mac compatibility.

1) You can run extra databases on the slave, within the same instance, without problem. There is no compatibility problem, it is just that there is some operational overhead on exceptional cases. Let me elaborate– You cannot setup the slave as read-only, which can increase the danger of slave-drifting by writing to the wrong database by accident. If the master fails, you have to separate the databases. And as you are writing to the binary log of the slave, it would be more difficult to use it for rolling forward a backup or using gtid replication (you cannot just switch master and slave roles, as it would try to replay all slave transaction). But in general, nothing will break more than usual, I use several setups like this where I use the slave as the backup source and the monitoring database.

2) Yes, you can clone it easily just by transferring the files. You may have some compatibility issues with the different case handling (Mac is not case sensitive, Linux is, which can affect db and table names), but that can be fixed in configuration in most cases. Aside from that, and the usual "moving the files around" issues (permissions, locations, configuration, etc.), you should be ok for moving the files, that should be endianness and OS independent for the most case. Please remember to completely shutdown the server before trying that, or you will end up with a corrupted set of files.


It seems you are monitoring a Lock Timeouts/sec Performance Monitor counter or perhaps querying sys.dm_os_performance_counters directly.  The Lock Timeouts/sec class for Profiler has the following notes in the documentation:

Most likely the vast majority of the events you are seeing are explained that way.  Try monitoring Lock:Timeout (timeout > 0) instead.


WHAT RDBMS ( SQL SERVER/ ORACLE ) close to manage MYSQL(DBA)

You can refer MySQL certification exam program and read MySQL documentation to know better about MySQL.

MySQL is much easy to setup and learn than SQL server and Oracle.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=458&get_params=p_track_id:MDBA


How can I create a readable/writable clone of a log shipped standby/read only database?

Using various sources (see list at the bottom) I have created two stored procedures that allow me to do what I need using something similar to:

EXEC sp_copy_ls_database
  @restore_job='LSRestore_server1_LOG_SHIPPED_DB',
  @database='LOG_SHIPPED_DB',
  @dest_database='LOG_SHIPPED_DB_COPY'

Where:

@restore_job - the name of the SQL agent job that is responsible for applying shipped logs on the log shipped database (the stored procedures disables this job while the clone is made and then enables it again).

@database - the name of the log shipped database to clone.

@dest_database - the name to give the cloned database.

Note: I don't think this is 100% fool proof as I believe SQL Server starts processing an agent job before updating it's internal tables and therefore sp_wait_for_running_job may miss the start of the job. Depending on the amount of data change and log ship frequency, this should still be fine for non-production databases.

Here's the SQL to create the stored procedures:

USE master
--SP to determine if a named SQL Agent job is running and wait for it to complete if it is
CREATE PROCEDURE sp_wait_for_running_job
   @JobName as varchar(100) = Null
AS
CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
                    last_run_date         INT              NOT NULL,
                    last_run_time         INT              NOT NULL,
                    next_run_date         INT              NOT NULL,
                    next_run_time         INT              NOT NULL,
                    next_run_schedule_id  INT              NOT NULL,
                    requested_to_run      INT              NOT NULL, -- BOOL
                    request_source        INT              NOT NULL,
                    request_source_id     sysname          COLLATE database_default NULL,
                    running               INT     NOT NULL, -- BOOL
                    current_step          INT              NOT NULL,
                    current_retry_attempt INT              NOT NULL,
                    job_state             INT              NOT NULL)

-- Am I sysadm and who am I
DECLARE @is_sysadmin AS INT
DECLARE @job_owner AS NVARCHAR
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

-- Loop until the <job> stops
WHILE 1=1
BEGIN
 -- Clear out temporary table on each pass
 TRUNCATE TABLE #xp_results

 -- Populate the temporary table with job details from Extended SP xp_sqlagent_enum_jobs
 INSERT INTO #xp_results
     EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

 -- If the <job> is running then break out of the loop, otherwise wait for 10 secs
 IF (SELECT COUNT(*)
     FROM #xp_results xpr
     LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
     msdb.dbo.sysjobs_view sjv
     WHERE (sjv.job_id = xpr.job_id)
     AND step_name = @JobName and job_state = 1) = 0
  BREAK
 ELSE
  WAITFOR DELAY '00:00:10'
END

--SP to clone a log shipped database to a new read/write database without impacting log shipping
CREATE PROCEDURE sp_copy_ls_database
    @restore_job AS NVARCHAR(128),
    @database AS NVARCHAR(128),
    @dest_database AS NVARCHAR(128)
AS
DECLARE @mdf_location AS NVARCHAR(max)
DECLARE @log_location AS NVARCHAR(max)
DECLARE @dest_mdf_location AS NVARCHAR(max)
DECLARE @dest_log_location AS NVARCHAR(max)

--Get log shipped database MDF file location
SELECT @mdf_location=physical_name FROM sys.master_files
WHERE database_id=(SELECT DB_ID(@database)) AND type=0

--Get log shipped database LOG file location
SELECT @log_location=physical_name FROM sys.master_files
WHERE database_id=(SELECT DB_ID(@database)) AND type=1

--Determine destination database MDF file
SET @dest_mdf_location=LEFT(@mdf_location,LEN(@mdf_location) - charindex('\',reverse(@mdf_location),1) + 1) + @dest_database + '.mdf'

--Determine destination database LOG file
SET @dest_log_location=LEFT(@log_location,LEN(@log_location) - charindex('\',reverse(@log_location),1) + 1) + @dest_database + '_log.ldf'

DECLARE @q AS NVARCHAR(max)
DECLARE @cmd AS VARCHAR(255)

--Disable the LS restore job
RAISERROR('Disabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=0
--Wait for the job to finish running if is running
RAISERROR('Waiting for log shipping restore job to complete: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC sp_wait_for_running_job @restore_job
--Set log shipped DB offline
RAISERROR('Setting log shipped database %s to offline mode', 0, 1, @database ) WITH NOWAIT
SET @q = 'ALTER DATABASE [' + @database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
EXEC(@q)

--Drop placeholder DB if it already exists
IF EXISTS(SELECT name FROM sys.databases WHERE name = @dest_database)
BEGIN
    RAISERROR('Dropping existing database: %s', 0, 1, @dest_database) WITH NOWAIT
    SET @q = 'DROP DATABASE ' + @dest_database
    EXEC(@q)
END

--Create new placeholder DB
RAISERROR('Creating placeholder database: %s' , 0, 1, @dest_database) WITH NOWAIT
SET @q = 'CREATE DATABASE ' + @dest_database + ' ON (NAME = ' + @dest_database + ', FILENAME = ''' + @dest_mdf_location + ''') LOG ON (NAME = ' + @dest_database + '_log, FILENAME = ''' + @dest_log_location + ''')'
EXEC(@q)

--Set placeholder DB offline
RAISERROR('Setting placeholder database %s to offline mode', 0, 1, @database) WITH NOWAIT
SET @q = 'ALTER DATABASE [' + @dest_database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
EXEC(@q)

--Delete placeholder database files
SET @cmd = 'DEL "' + @dest_mdf_location + '"'
RAISERROR('Deleting placeholder database MDF file: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;
SET @cmd = 'DEL "' + @dest_log_location + '"';
RAISERROR('Deleting placeholder database LOG file: %s', 0, 1, @dest_log_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;

--Copy the log shipped DB's MDF file
SET @cmd = 'COPY "' + @mdf_location + '" "' + @dest_mdf_location + '"';
RAISERROR('Copying the log shipped database MDF file to: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;

--Copy the log shipped DB's LOG file
SET @cmd = 'COPY "' + @log_location + '" "' + @dest_log_location + '"';
RAISERROR('Copying the log shipped database LOG file to: %s', 0, 1, @dest_log_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;

--Put the copied database online
SET @q = 'ALTER DATABASE [' + @dest_database + '] SET ONLINE'
RAISERROR('Setting copied database %s to online mode', 0, 1, @dest_database) WITH NOWAIT
EXEC(@q)

--Set log shipped DB online
SET @q = 'ALTER DATABASE [' + @database + '] SET ONLINE'
RAISERROR('Setting log shipped database %s to online mode', 0, 1, @database) WITH NOWAIT
EXEC(@q)

--Enable the LS restore job
RAISERROR('Enabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=1

Sources:

General overview: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/backup-and-copy-warm-standby-log-shipped/
Path from file name:
https://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path
Wait for a job to complete:
http://www.sqlservercentral.com/Forums/Topic99401-8-1.aspx#top
Set DB offline immediately
https://stackoverflow.com/questions/808232/extreme-wait-time-when-taking-a-sql-server-database-offline


DB2 ROLLFORWARD ERROR when doing "TO A POINT IN TIME" option

If you archive logs to a location different from the active log directory (logpath database configuration parameter), you need to specify the clause overflow log path /path/to/archived/logs. The ROLLFORWARD command does not do it automatically.

PS. You really should start reading manuals -- they are full of useful stuff, including multiple examples...


What does "ISO standard equivalent" actually mean?

It just means that operator SOME is not some MS proprietary extension. It is part of the (ISO/ANSI) SQL standard.


Active Directory authentication with SQL Server on Linux containers

@Martin Cairry, Yes, client is windows 10 and its joined the domain, ssms verion is 18.5. all users have domain admin privilege. Also when I login to ssms with sa user, I can add my domain user in login section but it can't login using windows authentication.


Many-to-many relationship with a one column ‘junction’ table?

I would include the table you're calling @stock_stockForShop (though I might change the name to something like @stockpile or even @warehouse if that actually makes sense in context.

A stockCode represents a unique entity in your schema; it defines a set of inventory that is available to a set of stores. If you do not store it and enforce it as a foreign key, you have to allow new codes to be created via @stock and @stockForShop, meaning that a typo could create a stockCode that is not used anywhere else, leaving the associated stock to potentially go to waste. This is, perhaps, the most important point - ensuring that all stockCodes are well established and usable, and that new ones are only created deliberately.

In addition, a stockCode might also have important attributes that you might want to be able to search on from an application: it could be tied to a specific geographic location (such that it would make sense to connect to shops within 100 miles of it, but no farther), or contact info (phone/fax/email).

So, I would have the @stock_stockForShop table, and would make IDs referring to other tables as foreign keys (again, making sure that typos don't create virtual new stockCodes).

Terminology note: I would consider @stockForShop to be your linking/mapping table (which is how I read "junction"), as it ties a shop to one or more warehouses, and a warehouse to one or more shops.


Float datatype with 2 digits after decimal point

This can be achieved with the decimal datatype.

See below for an example:

declare @num as float;
set @num=5.20;

select convert(decimal(10, 2), @num);

The output here will be 5.20.

After re-reading your question:

  But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?

Is there a reason why you are specifically using float?  Oftentimes people tend to default to that datatype when decimal is more than sufficient.


SELECT LEFT JOIN but only the record that every matched value is null

If you want just rows which has NULL values, you can use this;

SELECT fname,location_id
FROM name WHERE fname NOT IN
(SELECT fname FROM name WHERE location_id IS NOT NULL)

The last row in code eliminate rows which have a value different than NULL


Concurrent UPDATEs on INNODB tables in MySQL

Is there a (unsafe) way to do it in parallel?

If there is some mark that a particular record was processed by a particular update (or you can insert a field into table structure for this purposes) you may use parallel chunk updates like

SELECT 1
FROM table
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count
FOR UPDATE;

UPDATE table
SET field = function(parameters)
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count;

Moreover, you can start a lot of parallel updates for the same field.


SQL Server Distributed Transactions: xp_sqljdbc_xa_commit vs xp_sqljdbc_xa_end

The SQL Server JDBC Driver's source code on GitHub is a great place to track this down.
@Override
public void end(Xid xid, int flags) throws XAException {
    // Called by the transaction mgr after the app closes the connection it was given from this physical
    // connection
    /*
     * Ends the work performed on behalf of a transaction branch. The resource manager disassociates the XA resource
     * from the transaction branch specified and let the transaction be completed. If TMSUSPEND is specified in
     * flags, the transaction branch is temporarily suspended in incomplete state. The transaction context is in
     * suspened state and must be resumed via start with TMRESUME specified. If TMFAIL is specified, the portion of
     * work has failed. The resource manager may mark the transaction as rollback-only. If TMSUCCESS is specified,
     * the portion of work has completed successfully.
     */
    DTC_XA_Interface(XA_END, xid, flags | tightlyCoupled);
}

src/main/java/com/microsoft/sqlserver/jdbc/SQLServerXAResource.java
So it's used by SQL Server's implementation of the XAResource interface.
For background see How It Works: SQL Server DTC (MSDTC and XA Transactions)


Amazon RDS CPU Usage keep increasing without any long running process in Show Processlist

You should keep an eye on things like your buffer sizes, table cache, query cache and innodb_buffer_pool_size, key_buffer_size as all of these memory allocations can have an affect on query performance which can cause MySQL to eat up CPU.
Enable slow query log and see if there are queries getting logged there.
Observe "mysqladmin status" output for sometime certain values like "Questions, Slow queries, Open tables, Queries per second avg" should help.


Database Unallocated Space - Correct way to measure

You'd have to provide the scripts you're referring to for us to be able to advise you on their accuracy, but if they're based off sys.database_files and the DMVs then good chance you're looking at something that's correct for the purpose they're intended for.
sp_spaceused was specifically designed to tell you the size of the data inside the database, either the whole database itself or for an individual Table or Indexed View:

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

The Transaction Log is stored in a different file and is a different type of object and concept than the database and it's files. The use cases to measure the space consumed by the database either as a whole or for an individual Table or Indexed View is usually more useful to do so without including the Transaction Log files which have a different purpose.
The Transaction Log is a running log of transactions (hence the name) since the last time the log file was backed up, to assist in the recovery of the database to ensure the ACID principles are guaranteed. It generally fluctuates in size (internally) as more transactions are written to the log file, until a transaction log backup occurs. It has no specific concept of indexes, or the data as a whole for the current point in time, rather it is the history to how the data became what it currently is in the database.
sp_spaceused is beneficial in providing you information at the database level on the total space reserved from the disk, how much of that space is used by the data in the database vs how big is the total index_size, and the remaining unused space that will become used by the data and indexes as they grow. Additionally, at the Table or Indexed View level, it even tells you the number of rows in the table (efficiently using meta-data) besides the previously mentioned data points.
To understand the general growth trend of the database, one could routinely view or log the results of sp_spaceused since the change in the data size (and index size) is what will be indicative of that metric, not the Transaction Log size at any given time, per se.


How to check the health of a SSAS database? And of its backups?

I don't think you can verify an SSAS database isn't corrupt apart from querying it. If you query it and you get errors it's corrupt.

If your database ends up corrupt the only sure way to recover is to restore the last known good backup, even though sometimes you can get away with messing in the .xml files or the data folder structure there is no guarantee and it's certainly not automated.

You can test an OLAP backup using msmdsrv.exe on the server (located in <driveletter>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin).

For example executing

C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin>msmdsrv -j -b "f:\backup\olap\olap.abf"

on the server I'm currently on yields this:

  Backup file f:\backup\olap\olap.abf contains database with the Name
  of OLAP and  Id of OLAP consisting of 179 files with the total size of
  100632864 bytes All files were successfully tested

If you are using encryption you need to use the -k switch to provide the password

If the backup is invalid I don't know of any automated method to fix it apart from creating a new backup.


Easier way to change every user in every database to default schema = dbo?

So what you need to end up with is something like this, for all users in any database that don't already have a default schema of dbo:

USE db1;
ALTER USER user1 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user2 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user3 WITH DEFAULT_SCHEMA = dbo;

USE db2;
ALTER USER user4 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user5 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user6 WITH DEFAULT_SCHEMA = dbo;

Here is one way to do this, using aggregate concatenation and nested dynamic SQL, that gets something very close (the USE command is repeated for each user in any database, something you can probably resolve through more tweaking, but not sure it's necessary):

DECLARE @sql NVARCHAR(MAX) = N'', @innersql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT @innersql += ''USE ' + QUOTENAME(name) + ';
  ALTER USER '' + QUOTENAME(name) + '' WITH DEFAULT_SCHEMA = dbo;
  '' FROM ' + QUOTENAME(name) + '.sys.database_principals
  WHERE type_desc IN (N''SQL_USER'',N''WINDOWS_USER'')
  AND default_schema_name <> ''dbo''
  AND name NOT IN (N''dbo'',N''guest'');'
FROM sys.databases WHERE state = 0;

EXEC sp_executesql @sql, N'@innersql NVARCHAR(MAX) OUTPUT', @innersql OUTPUT;

-- you can just print the command to preview it
-- (though it may be too long to review the whole thing):

PRINT @innersql;

-- once you're happy with it, uncomment this and run it all again:
-- EXEC sp_executesql @innersql;


postgresql pgadmin3 queryconnect attribute does not work

I found the answer to my problem.  The pgadmin3 tool won't let you specify both a server and a database.  The server itself HAS a default database and when you select it with the --server option, it won't let you pick a database.  An error message would have been nice!

The 'defiant' server I made has in itself a default connection to a database, you have to set that default database, and you can't use commandline options to change it.

First, you must be disconnected from the default connection to the server you want to edit.  Either make a new connection to a server or edit your existing one by right clicking your server, mine is: defiant(localhost:5432).

Then right click it, go to properties, you'll see a field called `maintenance DB', make that the connection to the database you want to be the default for this server connection.  I set mine to kurz_prod.  Click OK and restart.

Then the following command works.  It opens pgadmin3, it gets a connection to the server and database that I specify, it opens a query window, it loads a previous file I specify into it.  And immediately I can run statements against it.

This command is correct in opening an sql file with a connection to a server and database:

pgadmin3 --file=/home/el/Desktop/sqlfile_for_postgresql.sql --query --server=defiant


MySQL CURDATE() and CURRENT_TIMESTAMP return different value?

Why CURRENT_DATE and CURRENT_TIMESTAMP have different value?

They returns values which have different datatypes.

MySQL 8.0 Reference Manual  /  Functions and Operators  /  Date and Time Functions

  CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

  CURDATE()

  Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context.

  CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

  NOW([fsp])

  Returns the current date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. The value is expressed in the session time zone.

  If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

  NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.


Looks like a postgres backup/dump structure (A sort of a forensic/data archeology question)

Use pg_restore to list its contents, dump it to SQL, or restore it to a database.


6 GB a month is only 72 GB a year. so to get to 1 TB of data it is around 15 years. clearl, the amount of data should not be your concern unless you are really doing something fancy.

what kind of queries do you run to require such a small database?


Is It normal to keep log file larger than data file?

If you have a huge modification going on, then yes, it can be normal to have a log file larger than your data file. After the huge modification is over, and the log backup is done, the file will not shrink back to it's original size however. But the file will be empty.
You can see this in Management Studio, if you right-click on the database and select Reports->Disk Usage.

I don't recommend shrinking your log file as it will only grow back the next time it needs space. But, if you are running out of disk space, know that it is possible to shrink your log file. I just don't do it, as the space gets used every time it needs to and as it's a better practice to leave it as it is.


Autogrowth Analysis in PROD DB based on the trace file data

You are most likely looking into somethng known as default trace, a light weight trace that runs in the background when SQL Server starts. To see if default trace is enabled:

SELECT value_in_use
    FROM sys.configurations
    WHERE configuration_id = 1568 /* 1 indicates the trace is enabled */

To view the location of trace files:

SELECT  REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) AS DefaultTraceLocation
FROM    sys.traces
WHERE   is_default = 1

You will find four trace files here, and each files are about 5 mb in size. When all four files are full, it rolls over (removes the oldest file from the disk) and a new trace file is created. Therefore, you might not be able to keep track of older data unless you siphon it off to a customized table somewhere.

You can view auto growth setting for your current trace file using:

/*Author - Feodor Georgiev*/
SELECT  TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime

You can also pull the data from previous trace files by slightly modifying the previous query.

SELECT  TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'
                                                        FROM sys.traces
                                                            WHERE path LIKE '%\MSSQL\Log\log%.trc'
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime

An example on how to load this data into a table

CREATE TABLE [dbo].[DefaultTrace_History](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [instancename] [nvarchar](256) NOT NULL,
    [EventName] [nvarchar](128) NOT NULL,
    [DatabaseName] [nvarchar](256) NOT NULL,
    [DatabaseID] [int] NOT NULL,
    [NTDomainName] [nvarchar](256) NULL,
    [ApplicationName] [nvarchar](256) NOT NULL,
    [LoginName] [nvarchar](256) NOT NULL,
    [SPID] [int] NOT NULL,
    [Duration] [int] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [GrowthInMB] [decimal](5,2) NOT NULL,
    [FileName] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO

Procedure to load the data incrementally:

CREATE OR ALTER PROCEDURE [dbo].[SP_Load_DefaultTrace_History]
AS

    DECLARE @maxdt DATETIME
    DECLARE @sql NVARCHAR(max)

    SELECT @maxdt = max(starttime)
    FROM   [DefaultTrace_History]

    SET @sql=
    N'INSERT INTO [DefaultTrace_History] (InstanceName,EventName,DatabaseName,DatabaseID,NTDomainName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,GrowthInMB,FileName)
    SELECT  @@servername,
        TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime,
        convert(decimal(19,2),t.IntegerData*8/1024) AS GrowthInMB,
        smf.physical_name AS [FileName]
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX(''\'',REVERSE(path)),LEN(path)))+''log.trc''
                                                        FROM sys.traces
                                                            WHERE path LIKE ''%\MSSQL\Log\log%.trc''
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.master_files smf ON smf.database_id = t.DatabaseID
WHERE   te.name = ''Data File Auto Grow'''
    IF (SELECT count(1)
        FROM  [DefaultTrace_History]
        WHERE  instancename = @@servername) = 0
      BEGIN
          EXEC sp_executesql @sql
      END
    ELSE
      BEGIN
          SET @sql= @sql + ' AND @@servername = @@servername AND t.StartTime >' + quotename(convert(VARCHAR(25), @maxdt, 121),'''') + ' ORDER BY t.StartTime DESC'
          EXEC sp_executesql @sql
      END

--Purge records > 365 days
DELETE FROM [DefaultTrace_History] WHERE [instancename] = @@servername AND [StartTime] < dateadd(dd,-365,getdate())
GO

You can then create a job to run on a schedule to load the data.


Slow updates of PostgreSql ByteA fields with just 1KB of data

To my shame, the problem was not related to bytea data type at all.

It was just absence of the primary key index in my table, and as table grow to several hundreds of thousands of records, finding record by id became too slow.

Now, when I've created primary key index, inserting of the data takes about 0.0001 seconds.


SQL Server Backup Sizes Differential, Compressed Backup Sizes

Why would a differential backup compressed be almost the size of the
  mdf?

A differential backup contains all extents that have changed since the last FULL backup. Could be that a large percentage of the database has changed since the previous FULL, could be that an index maintenance task has rebuilt/re-organised tables.

Try Paul Randal's How much of the database has changed since the last full backup? script to verify what percentage of the database extents have changed.

If zip compressing a backup has as little effect as using SQL backup compression, good odds a large percentage of the space is taken up with LOB data that can't be compressed further e.g. PDF or image types.


Analyzing a query plan for a query with TWO predicates, but index only has ONE

The optimal index for that query is INDEX(faculty, wam), in that order.

MySQL does not (in most situations) use two different indexes.  However, it will use a 'composite' index to good use in some situations, such as this.

Without knowing how many rows have Arts and home many have > 75, you cannot accurately conclude which of INDEX(wam) or INDEX(faculty) would be better.  Nor can the Optimizer, since it's statistics gathering is not very good.  SHOW INDEXES and EXPLAIN SELECT provide numbers that come from the same statistics, so you won't necessarily come to the optimal conclusion.

A "Hash" index (which InnoDB does not have) is essentially no better than a BTree index.  A Hash index is useless for a range (eg, > 75).  (Hence, MySQL uses BTrees, and does not provide Hash indexes.)

Another point:  If more than a small percentage of the table has wam > 75, INDEX(wam) will be shunned; scanning the entire table is likely to be faster than bouncing between the index's BTree and the data BTree.  Again > 75 and 60 pages and 10 and 100 give no good clues of which path the Optimizer should take.  The answer to the quoted question is "it depends" or "not enough info".

You could tackle the question by presenting two answers:  One where very few rows have wam > 75, and one where most meet that predicate.

Has your instructor had any experience using databases, or only textbooks?

More discussion on devising optimal indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql