The import worked for me with your sample data.  But if the real data has commas in that "jsonT" column, it's going to get the data in the wrong columns.  BULK INSERT interprets that comma as part of the CSV format, and then starts putting everything after it in the last column (ClientIPAddress):

If you have control over the creation of the file, you could use a different FIELDTERMINATOR, like pipe (|).  You'll need to pick something that you're sure won't be in the source data, per the docs:

To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

Data fields never contain the field terminator.
Either none or all of the values in a data field are enclosed in quotation marks ("").

UID|customerID|DateT|TypeID|AssociatedID|AttributeID|LandingPage|jsonT|ClientIPAddress
9150|2345|2020-09-01T00:00:36Z|1||859|campaign01.html|{'product': 'string01', 'type': '', 'string011'}|192.168.200.2
9151|2356|2020-09-01T00:02:30Z|1||640|campaign02.html|{'product': 'string02', 'type': '', 'string022'}|192.168.200.1

Then your BULK INSERT statement looks like this:
BULK INSERT dbo.activity
FROM 'C:\temp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
);

If you don't have control over the creation of the export file, you'll unfortunately need to:

do some custom pre-processing on it to be able to use it with BULK INSERT
use a different import technology


Postgres stops using a (btree_gin) index when select clause includes a column not part of the index

As jjanes points out, I may not be right here if you've only got a GIN index. I'm waiting for the plan before I revise

I don't believe the index "is no longer used" is correct. In the first query, you're selecting ONLY the columns indexed. That means you can potentially use an "index-only scan". In the second query, you're selecting columns not in the index. That means you CAN NOT use an "index-only scan". That means you have to visit the table on disk.

On the basis of that, in the second case, whether or not the index is used is dependent on the selectivity estimates. We would need the EXPLAIN ANALYZE of the two queries to further assist in that. We have two options:

I imagine the index is used, however the rows need to be retrieved from disk and thus it is slower than the first query. This is what is likely happening.
PostgreSQL assess a higher cost to visiting the table AND the index, so it just visits the table and does a seq scan. Usually this only happens on small tables.


MongoDB with Sharding has slow performance

First of all I heavily doubt that you config servers build a replica set. By default, config servers are three single instances, which is important to find metadata deviations. If you set up a replica set manually, this would be a single config server. And tbh, I am not even sure wether a mongos would actually switch to a secondary in a failover situation if given a replica set.

Now for the performance gain. All you mongods (at least your shard) run on localhost. And you only have a single shard. Sharding as a load distribution tool only works when the load is distributed across multiple machines. In your setup, your various mongods (6, if I am not mistaken) will battle for resources. Depending on your write concern, for example, the two secondaries may battle for disk IO when syncing data from the primary. If you have WT as your storage engine, the situation gets even more severe, as WT alone will eat up to 50% of your RAM...

I am not sure what you are trying to achieve, but if it is for testing a sharded setup, your test plan is very improvable.


How can I get the actual data size per row in a SQL Server table?

Try this script:

declare @table nvarchar(128)
declare @idcol nvarchar(128)
declare @sql nvarchar(max)

--initialize those two values
set @table = 'YourTable'
set @idcol = 'some id to recognize the row'

set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength(' + name + '), 1)'
        from  sys.columns
        where object_id = object_id(@table)
        and   is_computed = 0
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'

PRINT @sql

exec (@sql)

The rows will be ordered by size, so you can check from top to down.


Make a non-ordinal numeric ID in PostgreSQL

but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind

Aside from the fact that I think you are giving yourself a false sense of security, you can do what you want by reserving the upper half of the identifier for the 'real' sequence and filling the lower half with random bits:

create sequence seq;

create table tab(
  foo bigint
      default (nextval('seq')<<32)+(random()*(1<<32))::integer
      primary key
);

insert into tab select from generate_series(1,9);

select foo, foo>>32 seq from tab;

        foo | seq
----------: | --:
 4294967296 |   1
 8589934592 |   2
12884901889 |   3
17179869185 |   4
21474836481 |   5
25769803777 |   6
30064771073 |   7
34359738369 |   8
38654705664 |   9

dbfiddle here


Combining retail sales and payment OLAP cubes

You need to start off with a cross join of detail and payment, because you say "products in any transactions that includes a certain type of payment are considered to be paid with that payment type", so you are effectively asking for parts of a transaction to be counted multiple times if there are multiple payment types used.


Possible to CROSS JOIN a subquery without duplicating code?

You can use a CTE:

WITH WF AS (
    select T.id, T.parentId
    from Task T
    where
        (T.id = @tgt and T.typeId = 3)
        or
        (T.parentId = @tgt and T.typeId = 1)
)
SELECT *
FROM WF AS WF1
CROSS JOIN WF AS WF2


Understanding the load running on my SQL server

usually query leave to the sql server enegine to manage how a query will be executed and this bring to focus on the parallelism settings of the instances (or databases in the newer versions of sql server).
It's a matter of concurrency. Here you can find the sql server ingine parameters that govern the parallelism in query execution:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15
MAXDOP instance parameter determines the max number of cpu that a single query can use to run.
There is another parameter that govern the threshold for using a parallel plan or not.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15
If sql server expects a cost higher than this parameter then it will use a parallel plan (i.e. more than one logical cpu)
with this query you can count the number of parallel plans in cache against single thread plans:
SELECT
SUM(
    case when p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') > 0 then 1 else 0 end
) as #num_parallel_plans,
SUM(
    case when p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') = 0 then 1 else 0 end
) as #num_nonparallel_plans
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
WHERE
cp.cacheobjtype = 'Compiled Plan'
OPTION (MAXDOP 1)


How can I compare each single day of last two weeks in MySQL

PRELIMINARY DATE MATH

Here are two expressions you need to understand

mysql> SET @this_past_monday = CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @prev_two_mondays = @this_monday - INTERVAL 2 WEEK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURDATE(),@this_past_monday,@prev_two_mondays;
+------------+---------------------+---------------------+
| CURDATE()  | @this_past_monday   | @prev_two_mondays   |
+------------+---------------------+---------------------+
| 2016-10-11 | 2016-10-10 00:00:00 | 2016-09-26 00:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

GOAL OF THIS ANSWER

You need to combine two queries

generate a temp table with the dates based on these two Mondays
retrieve counts from views table based on these two Mondays

QUERY #1

Generate all dates from Jan 1st last year to 1000 days later, extracting all dates spanning 2 weeks before this past Monday

SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;

QUERY #1 EXECUTED

mysql> SELECT dt FROM
    -> (SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
    -> (SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
    -> (SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
    -> (SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
    -> ORDER BY dt) A
    -> WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
    -> AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
+------------+
| dt         |
+------------+
| 2016-09-26 |
| 2016-09-27 |
| 2016-09-28 |
| 2016-09-29 |
| 2016-09-30 |
| 2016-10-01 |
| 2016-10-02 |
| 2016-10-03 |
| 2016-10-04 |
| 2016-10-05 |
| 2016-10-06 |
| 2016-10-07 |
| 2016-10-08 |
| 2016-10-09 |
+------------+
14 rows in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-10-11 |
+------------+
1 row in set (0.00 sec)

mysql>

QUERY #2

SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`);

COMBINATION

LEFT JOIN Query #1 and Query #2

SELECT A.dt,IFNULL(B.RowCount,0) RowsCounted FROM
(SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND) A
LEFT JOIN
(SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`)) B
USING (dt);

REASON FOR COMBINATION / LEFT JOIN

In the event there are no rows for a particular date, you want Query #1 to still show a date but present 0 as the number of rows

GIVE IT A TRY !!!

NOTE : I will leave the gory details of presenting the data in your capable hands


The problem is not with your database, it is the problem of the docker.

Docker has notorious problems with the time synchronization between the host machine and the virtual machines ("containers"...).

The simplest solution for the problem if you start the docker container with admin privileges (--privileged flag to docker run), and then use a time synchron tool inside the container to fix its time (ntpdate -s your.country.clock).


Can I grant access to a MySQL user for a single partition of a table?

I'm not entirely sure you can do what you are after.

I would have thought a better option would be to create a view that only shows the data you want:

CREATE VIEW view_testing AS SELECT id FROM testing WHERE id <= 1000;

Then give your user only permission to access that view:

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT SELECT ON my_test.view_testing TO 'foo'@'localhost';

(n.b. the user creating the view needs access to the underlying table)


Standards for storing a rating as a range (1-5, 1 is best, etc.)

Rule of thumb:  The higher, the better.

All that said, I'd suggest to use a lookup table to store rating definitions. Maybe for an specific question you want to use a rating of 0 to 5, but may be tomorrow you need a bigger or a lower ratio.

Or simply you want to change the name of each option.

Elated
Happy
Indifferent
Unhappy
Miserable

Using a lookup table you can choose different options for different purposes. Sometimes you need 5 options, sometimes 10 and sometimes 2.

Do not restrict yourself with a 5 options.

  Life has a lot of flavors:

  o  True
     o  False
     o  Do not know/ Do not answer


Questions to Continuous Archiving and Point-in-Time Recovery with PostgreSQL

you can use the pg_receivewal utility on the server that will receive the logs, it will help you with this problem.

see more at:

pg_receivewal - Doc official


What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement?

The mysql_load() function calls the open_and_lock_tables() function to lock the table mentioned in the LOAD DATA statement.

MySQL obtains an exclusive lock on the table so that it can very quickly load data into the table.  There is very little overhead in the LOAD DATA process, just the bare minimum parsing is done to make it work.

The CONCURRENT option only affects MyISAM tables, if you are loading into an InnoDB table it does not permit concurrent access.

The reason why System lock took so long is that the actual data load was lumped into the timing for that step.  The profiler works by measuring time between fenceposts, that is the entry time is logged for each instrumented user function.  The mysql_lock_tables() function is called from inside mysql_load(), but mysql_load() is not instrumented, so the elapsed time starts at the system lock and ends at the next fencepost, which was the query cache lock.

Your data load of 1.96mn rows took about 447 seconds, there isn't any way to separate the actual lock time overhead from the loading time because the loading isn't instrumented.


Stored procedure big where clause or multiple procedures

I would create One stored procedure with optional parameters. Also instead of using WHERE @Var is NULL or Column = @Var use dynamic sql to build your query.

This kind of query where clause results in a very unefficient execution plan.

Also Sql server doesn't do any shortcircuting when evaluating OR conditions in where clause, for example even if @Var is null evaluates true sql server may still go ahead with the other Or condition and starts evaluating the whole column against a null variable.

Also with these optional parameters, if you pass a specific set of parameters and there is no execution plan for this procedure in the proc cache sql server will create an execution plan best for these specific set of parameters and uses it, but what if you pass a very different set of parameters on the next execution of this procedure, sql server looks into the proc cache and if it find an execution plan there it just goes ahead and uses that execution plan, even though it was a good execution plan for the first set of params passed but a very bad execution plan for these very different set of params (known as Parameter sniffing).

Using dynamic sql also eliminates this parameter sniffing since it stores parameteised execution plans.

I would use one procedure with dynamic sql something like this...

CREATE PROCEDURE dbo.usp_ProcName
 @Name          VARCHAR(100)
,@FirstName     VARCHAR(100)
,@City          VARCHAR(100)
,@Birthday      VARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(MAX);

SET @sql = N'  select  id
                       ,name
                       ,firstname
                       ,city
                       ,birthday
                from dbo.Customers  WHERE 1 = 1'
       + CASE WHEN @Name IS NOT NULL
              THEN N' AND Name = @Name' ELSE N' ' END
       + CASE WHEN @FirstName IS NOT NULL
              THEN N' AND FirstName = @FirstName' ELSE N' ' END
       + CASE WHEN @City IS NOT NULL
              THEN N' AND City = @City' ELSE N' ' END
       + CASE WHEN @Birthday IS NOT NULL
              THEN N' AND BirthDay LIKE ''%'' + @Birthday + ''%''' ELSE N' ' END

EXECUTE sp_executesql @Sql
                    ,N'@Name VARCHAR(100) , @FirstName VARCHAR(100)
                       @City VARCHAR(100), @BirthDay VARCHAR(100)'
                    ,@Name
                    ,@FirstName
                    ,@City
                    ,@Birthday
END


Why B-tree indexing is used instead of hash based indexing?

Oracle will not read all the index; only the index blocks needed to satisfy the particular query. The number of blocks will depend on the depth of the index; rarely more than three have I seen in practice, for a single lookup.

Here is an example, using a big version of the EMP table. BIGEMP contains just under 2M rows:

select count(*) from bigemp
;

  COUNT(*)
----------
   1835008

I have created a unique index, called IDX1 on empno. Now I can examine the BLEVEL (which is what I was referring to as depth above) and the number of leaf blocks:

 select index_name, blevel, leaf_blocks
 from user_indexes
 where index_name = 'IDX1'
 ;

INDEX_NAME               BLEVEL LEAF_BLOCKS
-------------------- ---------- -----------
IDX1                          2        3947

So this tells me that this index has a root node, and then two level below that. So this mean that to find a unique row from the table, it should only take 4 IO's. That is the index root block, the two leaf blocks, and then another IO to the table to get the actual rows. This can be verified using autotrace

SQL> set autotrace on
SQL> select empno, ename, sal
  2  from bigemp
  3  where empno = 12345
  4  /

     EMPNO ENAME             SAL
---------- ---------- ----------
     12345 Scott            3000

Execution Plan
----------------------------------------------------------
Plan hash value: 3460625844

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIGEMP |     1 |    15 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX1   |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=12345)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
        682  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Sure enough, 4 consistent gets.

Oracle does also support "hash indexing" - called hash clusters. However, for transactional systems, there is a big maintenance penalty on them, so they are rarely used in these systems.


What are the type of drivers for Communication with Java?

In order to connect the Java application with the database you need the connector. You need to the mysql-connector.jar to connect the application with mysql database. It need to add both for standalone as well as for web application.
You can download the .jar file from here.
https://dev.mysql.com/downloads/connector/j/3.1.html


No, you won't get an error until both datafile are full.. The error is thrown when there is no more space in the filegroup (not for every file in it).

You can test it pretty easily by creating a database with 2 data file (one with the auto-growth enable) and inserting data in it until it fills the first data file.

You will see the the first file fills up to 100% and you won't get any error.
If you disable the auto-growth on the second file and continue inserting, we will get an error when both file will be filled.

Thanks.


How to take single database backup using mysqlbackup command in Mysql 8.0.18 version DB

If you create the backup using the --tts option, when restoring your instance you can include/exclude selected tables. A whole database is included/excluded by selecting all of its tables with --include/exclude="^databasename\."
The MySQL official documentation explains it very well:

TLR: https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore.partial.html
Restoring a backup created with "tts" option: https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore-use-tts.html

Hope it helps.


My procedures seem valid... I was able to test the logical DB, then bring it back into an exact copy Logical Standby and re-enable SQL Apply without issues.

However, there was a minor change to my procedures: flashback database requires that the DB is in mount state and that alter database open requires resetlogs or noresetlogs.

Therefore, the amended procedures are as follows:

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
shutdown immediate;
startup mount;
flashback database to restore point 'before_testing';
alter database open resetlogs;
alter database guard all;
alter database start logical standby apply immediate;
drop restore point before_testing;
alter database flashback off;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;


How to list all forced query plans and cancel one

You should be able to get information about 'forced' plans by querying this catalog view (sys.query_store_plan) filtering on is_forced_plan = 1.  At the bottom of the page are links to other useful catalog views for Query Store.

This stored procedure (sp_query_store_unforce_plan) allows you to 'unforce' a plan.


Transactional Replication - Publications with same Name on the same Domain

Yes, it is fine to have same publication names on as many different publishers as you want.  You most likely can't have dup publication names on a single publisher though, but that is not what you are asking.  Further, you can easily test this out in your test/dev environment, on two servers.  Create a single pub/dist/sub server, and on second server, create another pub/dist/sub, and on each, create a publication with same name.

About your comment "but in the past I've found replication to be "problematic" - please explain?


Actually your query doesn't return anything in my test :(. The reason is the attendance_date doesn't fall in between the condition that you have specified(To_date('05','MM') and
 To_date('05','MM') is returning the same value).

You can change your code as
  ...where attendance_date between To_date('05','MM') and To_date('06','MM'); to select all the dates of the May month.

SQL> desc atten;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ATTENDANCE_DATE                    DATE

SQL> select * from atten;

ATTENDANC
---------
05-MAY-16
03-JUN-16
03-JUL-16
03-MAY-16

SQL> select distinct To_Char (attendance_date,'dd/MM/yyyy') from atten where attendance_Date between To_Date ('05', 'MM') and To_Date ('05', 'MM');

no rows selected

You can try the following to get the result.

SQL> select to_char(attendance_date, 'dd/mm/yyyy') from atten
where extract(month from attendance_date) = 5;  2

TO_CHAR(AT
----------
05/05/2016
03/05/2016

OR If you wish to display attendance date of months between 5 and 6(As you question's title suggests).

select to_char(attendance_date, 'dd/mm/yyyy') from atten
where extract(month from attendance_date) = 5 OR extract(month from attendance_date)=6;
TO_CHAR(AT
----------
05/05/2016
03/06/2016
03/05/2016


Standard Edition High Availability without Shared Storage

As per this BOL reference, here are the only options you would have with Standard edition:

Log Shipping
Database Mirroring (sync/safety only)
2-node Failover Cluster Instance

Because you don't want to use shared storage between different participants, that would automatically rule out the failover cluster instance, leaving you to choose between log shipping and database mirroring.

Those are two very different solutions, with two very different needs driving them.  What are your requirements for failover?  Reporting?  How many copies of the data do you want?  Until those questions are answered, you wouldn't be able to choose between these two rock-solid solutions.

EDIT

I must have completely missed your requirements in your question, but with Paul's excellent edit and graphic there, it seems as though a lot of the requirements are already there (although you don't seem to point out reporting or warm data, so I'll assume that's not necessary).

Physical Separation
You could get yourself into trouble here with database mirroring if you're talking about cross-datacenter or geographic separation type of distance, as the restriction for only sync/safety mode could introduce performance implications.

Failover Tolerant Application
Unless you have the logic in your application, log shipping wouldn't be able to help you here.  With database mirroring, depending on your provider and capabilities you could specify the failover partner though.

Failover (preferably automatic)
This an easy one.  Log shipping doesn't have this capability, you'd have to manually intervene.  Database mirroring would be able to fulfill this.

No Shared Storage
Both of these solutions do not require shared storage.

Must work with EF
I don't see why EF would have a problem with database mirroring, but I couldn't say for sure on this without doing more EF research.

SQL Server 2014 Standard
If you mean Standard edition, that is answered above.  If you mean "standard" as in "current", then database mirroring is currently deprecated for what it's worth.


How to get a boolean value into a SELECT operation based on another table?

I'd do it with a CASE statement:

select
    m.*,
    hasAttachments = CASE WHEN EXISTS(select * from Attachment where messageId = M.messageId) then 1 else 0 end
from Message M

or

select distinct
    m.*,
    hasAttachments = CASE WHEN a.AttachmentId is not null then 1 else 0 end
from Message m
left join Attachment a on a.MessageId = m.MessageId


I found what the problem was. I had not deleted the database from its original location, after I detached it.

I copied the database to the new drive, which on my case is T:.

After I deleted the database from its original location, in my case F:\ssas\data, then I could attach the database from T:.

After attached, I checked on the  F:\ssas\data folder and SSAS had created the following structure there, see below.


Design database for real estate property management?

Properties can be resold multiple times, refinanced multiple times, have multiple loans/cash financing options per, be sublet/subdivided, have to follow government reporting requirements for all of that, etc. You need a thorough understanding of the business and proper specifications; the fact that you asked the question you have asked suggests to me that either you don't, and/or that you lack experience in the biz. Also, "property" is the singular of "properties", so, "propertyID".


At what point does one database per client become unfeasible?

Managing 100 or 500 databases is really not all that different from managing 5 or 10 - you just have to embrace automation and have a scalability plan in place (and don't plan to use high-cost-per-database features like mirroring across all clients).

At my previous job we used this architecture and I would never once have ever thought of merging two clients into a single database, even though some of the challenges can be "hard."

The big benefits are independent recovery models (a can be simple, b can be full, etc.), the ability to restore to a point in time (or remove entirely) a client without disrupting others, the ability to seamlessly move a resource-heavy client to its own storage or to a completely different server with very little in the way of transparency (you update a config file or table that tells the app where to find that client).

I address several of the objections, and/or how to approach the problems, in these posts:

Handling growing number of Tenants in Multi-tenant Database Architecture
Multi-tenant database using SQL Server 2008?
Automation of backups of Large Number of Database

That all said, I don't think any of us can tell you the point at which management becomes impractical for you - just know that whatever specific challenges you come across, you can ask about those problems individually.


How to resolve "Error log file size is increasing in mysql 5.5" issue w/o restarting mysql

Put the following parameters in your my.cnf and then restart the service:-

skip-name-resolve
log-warnings=1

The above activity will solve your  issue.

Try to check this link also:-

https://serverfault.com/questions/341290/mysql-warning-ip-address-could-not-be-resolved


UPDATED: Relational (or NoSQL) Database Design

Which of the two options is the best?

The relational design is clearly superior to the hierarchical design used in the NoSQL example.  A database schema designed using relational principles does not favor one access path over another.  Each table represents a real world entity type and through the use of relational algebra queries of arbitrary complexity can be constructed to answer not only current questions of the data but any future questions you think of that haven't yet been conceived.  A hierarchical schema imposes a single structure upon the data - in this case work packages contain other work packages, the worker, and the parts, and the files.  If you want to ask questions of the data not aligned with this single view it will be much more difficult if not impossible.  Furthermore, the relational model includes a data integrity component to ensure data is consistent with respect to rules declared to it approximating the real world, whereas systems, like a NoSQL system, using a hierarchical or network model do not provide these.

  Should a Work Package reference the above Work Packages, or the contrary is better?

The best way to implement this is to create a new table that represents the links between the parent and dependent work packages while leaving the work package table to represent the work packages themselves regardless of dependency.  This design does not mix work packages dependent on other work packages with work packages that are not dependent on others.  A key advantage of the relational approach is that a single structure with a single set of operators - relational tables and relational algebra - can easily represent a hierarchy or a network.  Network DBMS's require two types - a node and a link - with two distinct sets of operators, and thus twice the complexity.  A hierarchical DBMS can't represent a network correctly at all.  This is a key reason why the NoSQL systems available today can only address a narrow set of use cases.  Fabian Pascal's book Practical Issues in Database Management, Chapter 7, gives an excellent overview of data hierarchies using SQL and I highly recommend it.

  There will be a substantial of reads, however the writes will be considerable less.

Another benefit of the relational approach is that the performance of the system can be managed to a great extent at the physical level without impacting the logical database schema used by applications to work with the data.  With the relational approach you can focus on the correct logical schema first and then in most cases let the DBA implement a correct physical design and infrastructure to best support the read heavy workload.  This is not the case in navigational systems where the programmer must specify the access paths directly in programs.


What are the advantages of having a centralised SQL database server for development?

I don't know of a single good comprehensive solution to this.

Local development means that developers don't break other people working on their own code against a shared database.  However, when you get latest code, you also need to get the database into the right state to match the code changes you've merged in.  If two people are making changes at the same time, merging can be difficult since database upgrade scripts can be incompatible.  Column order doesn't normally matter in a database, but it can be a bit annoying for databases to be different.

There are good tools to compare schema and data and apply changes.  I would say schemas should aim to be identical.  However, typically you want lookup-type data updated between developers but not regular application data (new customer types, but not new customers).  Configuration data you might want updated, BUT sometimes only a subset (new printer options but not file path settings).

You would think that ideally, you could JUST rebuild your local database completely once you've each merged changes.  If you've set up a bunch of test scenarios through the application for testing (instead of in the build scripts), you now don't have a script to get those changes back into the database.  And this is more difficult as the scope of the database schema increases - with surrogate keys and parent-child relationships which all might have complex dependencies.

In the ideal scenario for a central database, you would have a developer DBA managed the database interfaces for the app and control that so that the exposed interfaces would consistently evolve over time and all developers about that level would see the same interface at the same time.  But then you have two separate groups coordinating their different feature timelines.

Which I think goes a long way to showing why people are still attracted to a variety of other approaches which put more emphasis on code and less on the database.  Ultimately, I think that just shifts the problem around.


What I am about to propose would be considered by many to be a hack, but will get you what you want.
STEP 01 : Create a special database called specialdata
CREATE DATABASE specialdata;

STEP 02 : As root@localhost, place the table you want into the specialdata database
CREATE TABLE specialdata.specialtable LIKE mydb.mytb;
INSERT INTO specialdata.specialtable SELECT * FROM mydb.mytb;

STEP 03 : Create anonymous user access to the specialdata database with the needed grants
INSERT INTO mysql.db
SET host='%',user='',db='specialdata',
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y';
FLUSH PRIVILEGES;

STEP 04 : There is no STEP 04
Every user should have access to any table in specialdata
Give it a Try !!!


Suggestion for Tooth Chart Data Structure

I would so something like this.  I presume that your patients will visit more than once and you will need a data set for each visit.  Of course, you will need to add more columns, but this structure should allow for flexibility for reporting as well as data entry.

CREATE TABLE Teeth
(
ToothID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, ToothPosition INT NOT NULL
, ToothType VARCHAR(10) NOT NULL
)

CREATE TABLE Patient
(
PatientID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, PatientName VARCHAR(100)
)

CREATE TABLE PatientVisit
(
PatientVisitID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, PatientID INT NOT NULL REFERENCES Patient(PatientID)
, DateVisit DATETIME2 NOT NULL
)

CREATE TABLE PatientTeeth
(
PatientTeethID INT PRIMARY KEY NOT NULL IDENTITY(1,1)
, PatientVisitID INT NOT NULL REFERENCES PatientVisit(PatientVisitID)
, ToothID INT NOT NULL REFERENCES Teeth(ToothID)
, ToothCondition VARCHAR(10) NULL
, ToothRecommendation VARCHAR(10) NULL
)


SQL 2012 Replication Between Subscribers Across VPN

This has been resolved as the replication will be scripted using stored procedures from the publisher (remote instance) to the subscriber (local instance) using a sql account setup for the subscription's linked connection to the remote instance.

The VPN is dedicated so there no major concerns with the VPN.

The publisher will use push method for the local subscription. Then from the local subscription a replication using the subscription (sub 1) will be pulled to the second local subscription(sub 2) that is read only.


How to left join intermediate result table after filter ta(subquery)?

I'm not sure I understand your problem, but judging from your sample data you can use a conditional count:

SELECT COUNT(CASE WHEN stu_name like 'A%' then 1 end) AS A
     , COUNT(CASE WHEN stu_name like 'B%' then 1 end) AS B
FROM stuInfo
WHERE stu_no = 1;

CASE WHEN stu_name like 'A%' then 1 end evaluates to 1 if stu_name starts with A and null otherwise. COUNT counts all non-null expressions.


Can atomic counter in trigger lead to "could not serialize access due to concurrent update"?

The actions taken in the trigger are part of the transaction that fires it.  This means that the counter increment will happen only if the whole transaction (or the subtransaction defined by a SAVEPOINT) succeeds.  This way we can say that the above trigger will increment the counter atomically, from the point of view of the INSERT INTO comments statement.

If this is all you have inside the transaction (INSERT and the increment), you are safe from concurrency issues, and the default isolation level (READ COMMITTED) is enough.

Furthermore, depending on how the INSERT is issued, you might even spare the trigger, which would save you some overhead.  In order to do this, the following conditions must be met:

you can wrap the INSERT INTO comments and UPDATE users statements into a transaction
there is no other way adding a comment than the transaction in the previous point (i. e. no manual editing of the data by an administrator or similar)


Postgres functions: how to pass a type as an argument

It is possible:

create type mood as enum ('sad','ok', 'happy');
CREATE TYPE

create function f(p_typname text) returns setof text
as $$
begin
   return query execute
      format(
         'SELECT unnest(enum_range(null:: %s))::text AS enum_values',
         p_typname
      );
end;
$$
language plpgsql;
CREATE FUNCTION

select * from f('mood');
   f
-------
 sad
 ok
 happy
(3 rows)


Simple recursive CTE stuck in infinite loop

I think you are missing the anchor WHERE...

select parent, child as descendant, Date, 1 as level
    from #source where <<SOMETHING TO SELECT JUST FEW RECORDS>>

Otherwise, every time you will be selecting all the records.

Moreover, check the first 2 records...

('05529744', '05395782', '9999-12-31'),
('05395782', '05529744', '2017-12-31'),

They are cyclical... This will never finish...
Fix the data, give it another try and let me know

Other thing that will throw error is the date convertion...
Your date fields should look like this:

convert(date,'9999-12-31',120)

Fixing some of the data. The following CTE will work:

CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)

INSERT INTO #source
(Parent, Child, Date)
VALUES
('05529744', '05529744', convert(date,'9999-12-31',120)),
('05395782', '05395782', convert(date,'2017-12-31',120)),
('05395782', '05147762', convert(date,'2016-12-31',120)),
('05147762', '04564728', convert(date,'2016-09-30',120)),
('04564728', '04299824', convert(date,'2013-07-31',120)),
('05224920', '05224920', convert(date,'2017-03-31',120)),
('04999841', '04999841', convert(date,'2016-10-31',120)),
('04999841', '04824720', convert(date,'2015-12-31',120)),
('04824720', '02917870', convert(date,'2015-06-30',120))

select * from #source order by 1

;with
descendants as
  ( select parent, child as descendant, Date, 1 as level
    from #source where Parent = Child
  union all
   select d.parent, s.child, d.Date, d.level + 1
     from descendants as d
  join #source s
    on d.descendant = s.Parent
    where s.Child <> s.Parent
)
select *
from descendants

order by parent, level, descendant
option (maxrecursion 0) ;

DROP TABLE #source;


PostgreSQL use NEW in query for INSTEAD OF trigger

NEW is a record, not a table. Basics:

Use NEW in FROM clause in Postgres trigger?

Slightly modified setup

CREATE TABLE product (
  product_id serial PRIMARY KEY,
  product_name text UNIQUE NOT NULL  -- must be UNIQUE
);

CREATE TABLE purchase (
  purchase_id serial PRIMARY KEY,
  product_id  int REFERENCES product,
  when_bought date
);

CREATE VIEW purchaseview AS
SELECT pu.purchase_id, pr.product_name, pu.when_bought
FROM   purchase     pu
LEFT   JOIN product pr USING (product_id);

INSERT INTO product(product_name) VALUES ('foo');

product_name has to be UNIQUE, or the lookup on this column could find multiple rows, which would lead to all kinds of confusion.

1. Simple solution

For your simple example, only looking up the single column product_id, a lowly correlated subquery is simplest and fastest:

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase(product_id, when_bought)
   SELECT (SELECT product_id FROM product WHERE product_name = NEW.product_name), NEW.when_bought
   RETURNING purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT ON purchaseview
FOR EACH ROW EXECUTE PROCEDURE insert_purchaseview_func();

No additional variables. No CTE (would only add cost and noise). Columns from NEW are spelled out once only (your point 1).

The appended RETURNING purchase_id INTO NEW.purchase_id takes care of your point 2: Now, the returned row includes the newly generated purchase_id.

If the product is not found (NEW.product_name does not exist in table product), the purchase is still inserted and product_id is NULL. This may or may not be desirable.

2.

To skip the row instead (and possibly raise a WARNING / EXCEPTION):

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,     when_bought)
   SELECT pr.product_id, NEW.when_bought
   FROM   product pr
   WHERE  pr.product_name = NEW.product_name
   RETURNING pu.purchase_id
   INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

   IF NOT FOUND THEN  -- insert was canceled for missing product
      RAISE WARNING 'product_name % not found! Skipping INSERT.', quote_literal(NEW.product_name);
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

This piggybacks NEW columns to SELECT .. FROM product. If the product is found, everything proceeds normally. If not, no row is returned from the SELECT and no INSERT happens. The special PL/pgSQL variable FOUND is only true if the last SQL query processed at least one row.

Could be EXCEPTION instead of WARNING to raise an error and roll back the transaction. But I'd rather declare purchase.product_id NOT NULL and insert unconditionally (query 1 or similar), to the same effect: raises an exception if product_id is NULL. Simpler, cheaper.

3. For multiple lookups

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO purchase AS pu
            (product_id,   when_bought)     -- more columns?
   SELECT pr.product_id, i.when_bought      -- more columns?
   FROM  (SELECT NEW.*) i                   -- see below
   LEFT   JOIN product  pr USING (product_name)
-- LEFT   JOIN tbl2     t2 USING (t2_name)  -- more lookups?
   RETURNING pu.purchase_id                 -- more columns?
   INTO   NEW.purchase_id;                  -- more columns?

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

The LEFT JOINs make the INSERT unconditional again. Use JOIN instead to skip if one is not found.

FROM  (SELECT NEW.*) i transforms the record NEW into a derived table with a single row, which can be used like any table in the FROM clause - what you were looking for, initially.

db<>fiddle here


Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

The problem is that you are converting XML data, which is stored as Unicode (UTF-16 specifically), into 8-bit data. 8-bit data requires a code page to know which set of up to 256 characters to use (or up to approximately 24k characters if using a Double-Byte Character Set, but the vast majority of the time it's 256 characters max). The issue here is that there is a character (or possibly several) in the XML data that either a) does not exist in the code page specified by the default collation of the current database, or b) does not exist in any code page.
For example, the following queries show a character that does not exist in most code pages (or maybe none) and so gets the same error when converting to VARCHAR, but converting to NVARCHAR works:
SELECT NCHAR(0x1234);
-- ሴ

DECLARE @Test XML;
SET @Test = CONVERT(XML, N'' + NCHAR(0x1234) + N'')
SELECT @Test;
-- ሴ

SELECT CONVERT(NVARCHAR(MAX), @Test);
-- ሴ

SELECT CONVERT(VARCHAR(MAX), @Test);
/*
Msg 6355, Level 16, State 1, Line XXXXX
Conversion of one or more characters from XML to target collation impossible
*/

Please convert to NVARCHAR(MAX) instead of VARCHAR(MAX). Your WHERE predicate should be:
CONVERT(NVARCHAR(MAX), qp.query_plan) LIKE N'%<missing%'

(please note the addition of the "N" prefixing the string literal — not exactly necessary, but a good habit to get into).
Converting XML data to VARCHAR is only truly safe if the query is executing in a database that has a UTF-8 collation as its default collation, and that is only available starting in SQL Server 2019.
Finally, to answer your question of "would forcing a collation via COLLATE help?": No, not when converting from XML. You can't add the COLLATE clause to the XML data as collation does not apply to XML (you would get "Expression type xml is invalid for COLLATE clause."). And, placing the COLLATE outside of the CONVERT() doesn't help as that is applied after the conversion, which here fails. You also cannot place the COLLATE just after the VARCHAR(MAX) as that is a datatype and not data (you would get "Incorrect syntax near the keyword 'COLLATE'.").

For more info on working with collations / encodings / Unicode, please visit my site: Collations Info


Saving your data differently would make that query trivial. Namely: one number per row. Something like:

results(Date, Time, Rank, Number)

where Rank would be 1 (or zero) for the first number, 2 for the second, etc. (Only if the order has importance, drop the rank if it doesn't.)

Then your query boils down to (ignoring reserved identifiers):

select number, count(*)
from results
-- where date = ...
group by number
order by number;

With your current schema, you can still get it fairly easily with a union:

select number, count(*)
from (
  select NumberOne as number from Results
  union all
  select NumberTwo as number from Results
  union all
  select NumberThree as number from Results
) AllNumbers
group by number
order by number;

(Or use a with AllNumbers as (...) CTE.)

When it comes to extending your application to more than three numbers, it's pretty clear which solution wins - you really don't want to add more columns for every type of draw, or produce a huge list of unions.

There's one issue I see though: a lot of data duplication for each row. More so if you need to add a column with the "type" of draw (to distinguish between "pick 3", pick 4" or whatever types of game you want).

You could mitigate that by only storing a "draw id", (optional) rank and number, and have another table where you store the "metadata" for each draw id. There probably are intermediate schemes that would fit your application better - depends on what trade-offs you can/want to make.


Grouping data by MONTH on DATETIME column in SQL Server

If you're just looking for October regardless of the year, then the easy way would be to just COUNT the records from that month:

SELECT COUNT(*) AS NumberOfJoiners FROM Employee WHERE MONTH(DateOfJoining) = 10;

or

SELECT COUNT(*) AS NumberOfJoiners FROM Employee WHERE DATEPART(MONTH, DateOfJoining) = 10;

If you want to group by year, then you'll need a group by clause, otherwise October 2013, 2014, 2015 etc would just get grouped into one row:

SELECT
  DATEPART(YEAR, DateOfJoining) AS YearOf Joining,
  COUNT(*) AS NumberOfJoiners
FROM Employee
WHERE DATEPART(MONTH, DateOfJoining) = 10
GROUP BY DATEPART(YEAR, DateOfJoining);


Recursive CTE throws temp tablespace is empty error

That error does not mean the temp tablespace got full, it means it is empty. Empty as no tempfile in it.

CREATE TABLE EMP (
    EMPID NUMBER,
    NAME VARCHAR2(20),
    MGRID NUMBER,
    DEPT VARCHAR2(5)
);

INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');

Query:

SQL> WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

      RNUM DEPT  ID
---------- ----- --------------------
         5 IT    1/2/3/4/5

Now if I drop the tempfile:

SQL> select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL> alter database tempfile 1 drop;

Database altered.

SQL> select file_id from dba_temp_files;

no rows selected

SQL> WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
WITH tmp (rnum,dept,id) AS
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

Check DBA_TEMP_FILES and fix your temporary tablespace.


You don’t need to delete the already missing database.The user is configured to use a none existing or deleted database as a default database for the application. First, use trace and check for which application the user is failed to login, then on the application, change the default database to one of the existing databases. Sadequl’s post  might help you for step by step guide to fix the issue, https://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/


Postgres upgrade and converting latin1 to utf8

Here are the steps you should perform:

Dump roles and tablespaces with pg_dumpall and the --globals-only option
Dump data and schemas with pg_dump and the --encoding option to have data in utf8 for each database
Create each new database on new server with correct encoding
Restore roles and tablespaces on new server
Restore schemas and data for each new database

Here you will find documentation for pg_dump and pg_dumpall in 8.1:
https://www.postgresql.org/docs/8.1/static/app-pgdump.html
https://www.postgresql.org/docs/8.1/static/app-pg-dumpall.html

And here you will find documentation for pg_restore and psql (your restore tool depends on your dump options) in 9.4:
https://www.postgresql.org/docs/9.4/static/app-pgrestore.html
https://www.postgresql.org/docs/9.4/static/app-psql.html

And here is generic documentation about databases encoding in PostgreSQL:
https://www.postgresql.org/docs/9.4/static/multibyte.html

Have a nice day!

Arkhena


You have to add the index in a separate step.

From MSDN:

  Indexes, constraints, and triggers defined in the source table are not
  transferred to the new table, nor can they be specified in the
  SELECT...INTO statement. If these objects are required, you must
  create them after executing the SELECT...INTO statement.


DELETE rows which are not referenced in other table

Quoting the manual:

  There are two ways to delete rows in a table using information
  contained in other tables in the database: using sub-selects, or
  specifying additional tables in the USING clause. Which technique is
  more appropriate depends on the specific circumstances.

Bold emphasis mine. Using information that is not contained in another table is a tad bit tricky, but there are easy solutions. From the arsenal of standard techniques to ...

Select rows which are not present in other table

... a NOT EXISTS anti-semi-join is probably simplest and most efficient for DELETE:

DELETE FROM link_group lg
WHERE  NOT EXISTS (
   SELECT FROM link_reply lr
   WHERE  lr.which_group = lg.link_group_id
   );

Assuming (since table definitions are not provided) link_group_id as column name for the primary key of link_group.

The technique @Mihai commented works as well (applied correctly):

DELETE FROM link_group lg
USING  link_group      lg1
LEFT   JOIN link_reply lr ON lr.which_group = lg1.link_group_id
WHERE  lg1.link_group_id = lg.link_group_id
AND    lr.which_group IS NULL;

But since the table expression in the USING clause is joined to the target table (lg in the example) with a CROSS JOIN, you need another instance of the same table as stepping stone (lg1 in the example) for the LEFT JOIN, which is less elegant and typically slower.


EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well.

In some you can even write (NOT) EXISTS (SELECT 1/0 ...) and the result is the same - without any (division by zero) error, which proves that the expression there is not even evaluated.

About the LEFT JOIN / IS NULL antijoin method, a correction: this is equivalent to NOT EXISTS (SELECT ...).

In this case, NOT EXISTS vs LEFT JOIN / IS NULL, you may get different execution plans. In MySQL for example and mostly in older versions (before 5.7) the plans would be fairly similar but not identical. The optimizers of other DBMS (SQL Server, Oracle, Postgres, DB2) are - as far as I know - more or less capable of rewriting these 2 methods and considering the same plans for both. Still, there is no such guarantee and when doing optimization, it is good to check the plans from different equivalent rewrites as there could be cases that each optimizer doesn't rewrite (eg. complex queries, with many joins and/or derived tables / subqueries inside the subquery, where conditions from multiple tables, composite columns used in the joining conditions) or the optimizer choices and plans are affected differently by the available indexes, settings, etc.

Also note that USING cannot be used in all DBMS (SQL Server for example). The more common JOIN ... ON works everywhere.
And the columns needs to be prefixed with the table name/alias in the SELECT to avoid errors/ambiguities when we have joins.
I also usually prefer to put the joined column in the IS NULL check (although the PK or any non-nullable column would be OK, it might be useful for efficiency when the plan for LEFT JOIN uses a non-clustered index):

SELECT a_table.a, a_table.b, a_table.c
  FROM a_table
       LEFT JOIN another_table
           ON another_table.b = a_table.b
 WHERE another_table.b IS NULL ;

There is also a third method for antijoins, using NOT IN but this has different semantics (and results!) if the column of the inside table is nullable. It can be used though by excluding the rows with NULL, making the query equivalent to the previous 2 versions:

SELECT a, b, c
  FROM a_table
 WHERE a_table.b NOT IN
       (SELECT another_table.b
          FROM another_table
         WHERE another_table.b IS NOT NULL
       ) ;

This also usually yields similar plans in most DBMS.


I want to remove Json object from Json Array in Mysql 8. Column data type is Json

I'm thankful to @wchiquito for solving my problem

[{"section": {"name": "Basic Info", "order": 0, "fields": [{"name": "field_1568988989723", "order": 0}, {"name": "field_1568989125942", "order": 1}]}}]

This is the query which solved my problem

UPDATE apps_object SET detail_view = IFNULL( JSON_REMOVE( detail_view, JSON_UNQUOTE( REPLACE( JSON_SEARCH(detail_view, 'all', 'field_1568988989723', NULL, '$**.fields'), '.name', '' ) ) ), detail_view ) where id = 34;

With the help of dbfiddle


MySQL - Automatically Replicate structure-only from DB1 to DB2 on the same host?

This can be achieved by using either black-hole storage engines or by invoking some schedule script to truncate the tables periodically.

Both approaches have prons and cons so use which suits you best. You can use following method when you want to use black-hole storage engine

1. Take back-up using mysqldump using following command

mysqldump --login-path=cred  --port=$i  --master-data=2 --databases user-databases > mysql_backup.sql

2. Replace storage engines from innodb to blackhole using following command

sed -i.bak 's#InnoDB#blackhole#g'  mysql_backup.sql

3. Now proceed to restore this backup on to your target server using following command

mysql --login-path=<cred> -h <emote_host> --show-warnings <mysql_backup.sql

4. Issue change master on slave server getting co-ordinates from the backup file.

5. Issue show slave status\G to verify slave is working.

6. Confirm from some table to verify that no data is being replicated.

You need to change default storage engine to Blackhole on slave server so structure of tables created on master is replicated and engine needs to be overwritten.

The second approach involves truncating tables periodically, this way you can recover or switch-over in case of some disaster.
Use following commands to print a truncate table for all user databases and then execute it in some script.

select concat('truncate ',table_schema,'.',table_name,';') from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys','test');

Hope it helps


Error on Check Contraint that is on a Column with a Default Constraint

To be honest, I think you are in for a rough ride here. I see several issues with how this will pan out.

The SELECT (the subselect for the INSERT) will resolve GETUTCDATE to something which later will be used in the INSERT part. The CHECK constraint is evaluated row by row, and since the value from GETUTCDATE will be "old", then you get the error.
I can't say why this fails for sure, but I'm speculating that the default value it captured momentarily a very short time before the check constraint is evaluated. Again, not the same value and hence the error.
Here it is pretty obvious that you first capture the date into a variable, which at time of INSERT will be old.

I changed the data type to datetime2(0) and the check constraint to  CHECK ( DateCreated = CAST(GETUTCDATE() AS datetime2(0)) and I didn't get any errors. But who knows if you happen to pass a one-second boundary and it then will fail. Also, that resolution might not cut it for you.

Another issue that I see that that a subsequent UPDATE of a row might re-trigger that check constraint to be evaluated (even if you don't modify that column) and that UPDATE will fail even though the data is "correct".

I would investigate either protect from UPDATE using permissions or having a trigger.


Ubuntu cannot open shared library mysqlx.so

Three comments:

One: Maybe you are using  'mysqlx.so; and you forgot the closing " ' "

I mean please use 'mysqlx.so' in stead of 'mysqlx.so

Two: We expect to find the file mysqlx.so in the that /usr/lib/mysql/plugin/.

Is the file mysqlx.so in than path /usr/lib/mysql/plugin/

Three: If so, maybe you have to check if the file mysqlx.so has full permissions for the user that controls mysql.


Sybase server build error, missing library files

It should be enough to install missing libraries on you system.

libXm is part of openmotif package:

libXm.so.4 (32bit)

libXm.so.4 (64bit)

libXp is part of X.Org X11 runtime library:

libXp.so.6 (32bit)

libXp.so.6 (64bit)

EDIT: I created a support case on SAP site and they responded me with a list of necessary packages. First, there is a site System requirements for Linux (this one is for ASE 16.0 SP03 PL05, but you can change it in the upper right corner) where you can find that you need to setup $LANG environment variable first. Then there is referral to SAP KBA Note 2489781, which is accessible only with valid credentials, so I picked important information below (again valid for ASE 16.0 SP03 PL05):

System: Red Hat

Hardware: x86_64 processor (Intel Xeon with EM64T)

Libraries:

kernel-2.6.32-71.el6.x86_64
glibc-2.12-1.7.el6.x86_64
compat-glibc-2.5-46.2.x86_64
glibc.i686 (Redhat 7 requires 32-bit libraries to execute the isql and dscp utilities)

Additional Linux Libraries:

openmotif-2.3.1-2.el5
libXp-1.0.0-8.1.el5
libXt-1.0.2-3.1.fc6
libXtst-1.0.1-3.1
libXi-1.6.1-1
libXmu-1.0.2-5
libXext-1.0.1-2.1
libSM-1.0.1-3.1
libICE-1.0.1-2.1
libX11-1.0.3-9.el5

With all of above installed, you should be able to build and run this version of ASE.


Can I use unique index in GROUP BY clause instead of column in mysql

SELECT toll, date, SUM(car), SUM(truck), SUM(bus)
    FROM shift_wise_traffic
    GROUP BY toll, date;

The GROUP BY must have a list of columns, not an index name.
An INDEX is an optimization, not a requirement.
An INDEX may be useful for queries like this.  (No index is likely to be useful for this particular query.)
Probably this query will simply scan the entire table, sort by toll+date, sum up the values, then deliver the output.  (Or it may build an in-memory hash based on toll+date for summing.)
UNIQUE versus INDEX -- makes no difference in this context.  UNIQUE = INDEX + a constraint.  For INSERT, UNIQUE prevents duplicates.

It feels like your PRIMARY KEY is (date, shift, toll) in some order.  Is it?  If so, then changing the order to PRIMARY KEY(toll, date, shift) might make your GROUP BY run a little faster.  (But it might slow down other operations.)


You can't use columns aliased in the SELECT list directly in the WHERE clause. See blog post here. Full disclosure; I wrote it.

You'd have to do this. Note that leading zeroes are discarded for integers.

SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS
WHERE MONTH(CHECK_DATE) = 1 OR MONTH(CHECK_DATE) = 2 OR MONTH(CHECK_DATE) = 3
GROUP BY YEAR(CHECK_DATE);

Or use a CTE

WITH really_bad_idea AS (
SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS
GROUP BY YEAR(CHECK_DATE)
)
SELECT *
FROM really_bad_dea
WHERE (PayrollMonth = 1 OR PayrollMonth = 2 OR PayrollMonth = 3);

Though I would ask very nicely that you not write non-SARGable WHERE clauses like that, because they are performance nightmares.

Also, it's doubltful the query will compile with just that column in the GROUP BY.


Error while running stored Proc sp_BlitzIndex (TM) v4.2 - September 03, 2016

You also need VIEW SERVER STATE permission in order to run sp_BlitzIndex. This is because it is querying DMVs.


How to import data from a pair of .mdf/.ldf files into a MySQL database

The first thing to note is that an .sql file is not a MySQL database. It is a script file containing SQL statements. You would run such a script against the database platform for which it was written (using the SQL dialect for a specific SQL database engine) to create the actual database.

Assuming that you are planning to do this all on a desktop machine with no software already installed, here is an overview of the steps to accomplish what you stated in your question. To describe every step in detail would make for a very long answer, so take this as a "roadmap" and start working through the steps. If you run into any particular difficulties that create a new question for that specific issue.

(Also, see the "Notes" at the bottom for a couple of possible alternatives.)

Install Microsoft SQL Server

If you are reasonably certain that the database in question is no larger that 10 GB and uses no Enterprise-level features (thanks, Jon!) then download SQL Server 2008 Express from here (thanks, Ashian!) and install it. Otherwise, download and install the (180-day trial) SQL Server Enterprise Evaluation Edition.

Attach the SQL Server database

Once SQL Server is installed, you need to "Attach" the existing database (.mdf/.ldf files) to the instance of SQL Server that you just created.

Install MySQL Server

To create and manipulate a MySQL database you need to have the MySQL Server software installed.

Install Internet Information Services (IIS)

phpMyAdmin is a web-based administration tool for MySQL, so it needs a web server environment in which to operate.

Install PHP

phpMyAdmin runs under PHP, so your IIS install must have that available, too.

Install phpMyAdmin

You need to install it if you're going to use it.

Install MySQL Workbench

MySQL Workbench will do the actual conversion from Microsoft SQL Server to MySQL.

Notes:

Of course, once you have SQL Server installed and the database attached, you could always "make multiple quer[ies]" against the database in SQL Server itself.
If the database really needs to be in MySQL (e.g., if you already have MySQL queries written or provided to you) but you only need to query the database locally, then a tool like HeidiSQL might allow you to avoid installing IIS, PHP, and phpMyAdmin. In fact, HeidiSQL might be able to do the conversion for you (e.g., by doing an export from SQL Server and an import into MySQL), in which case you could use it instead of MySQL Workbench.


Determine the name of the column with a null value nearest the last column

Try this

select
    case when field_4 is null then 'field_4'
         when field_3 is null then 'field_3'
         when field_2 is null then 'field_2'
         when field_1 is null then 'field_1'
                              else null
    end
from transactions;


PostgreSQL results of conditional insert

You can try insert ... select:

    INSERT INTO mytable (id, hash, status, datecreated)
    SELECT 123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z'
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE hash = 'somelonghash')
    RETURNING *;

But: is there a reason preventing you from using on conflict instead?


Can I use Server3 to host the AG groups of Server1 and Server2.

Your scenario is possible. AG1 and AG2 should be different availability groups. Also, all the 3 servers should be part of same Windows cluster.

The only problem that I can see is if server3 goes down (for whatever reason, blue screen, disk failure, or any disaster) then you will loose DR for server1 and server2 both at the same time. Also, server3 when a failover occurs from both server1 and server2 should be powerful to support the load interms of CPU, DiskIO and Memory.


Automatic replication on slave node after service restart

Move server-id=3 from under [mysqld_safe] and put under [mysqld]

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
server-id=3
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections
max_connections=400
symbolic-links=0
#skip-slave-start
log-bin=/data/mysql/mysql-bin
#binlog_format=ROW
max_user_connections = 400
key_buffer = 64M
max_allowed_packet = 32M
sort_buffer_size = 64M
net_buffer_length = 64K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 64M
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 100M
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16

Then restart mysql

Perhaps [mysqld_safe] does not understand server-id. Normally, options not understood by [mysqld_safe] are supposed to passed to [mysqld]. That's probably broken in MySQL 5.1.


Why does MySQL + Windows Server = crashed tables periodically?

From the error message I can quickly tell the following:

The database is elite_prod
ffcount,bldr,frtfwd, and histry are all MyISAM tables

Because you are using the MyISAM Storage Engine, you have two major handicaps:

HANDICAP #1

Changes to MyISAM are cached differently (regardless of the OS)

The index pages for MyISAM tables are cached in the MyISAM Key Buffer (sized by key_buffer_size)
Index changes are flushed to the .MYI by the MyISAM Storage Engine
Data are never cached as there is no engine-centric buffering for data
Data changes are flushed to the .MYD by the OS (OUCH !!!!)
I discussed this before

What are the main differences between InnoDB and MyISAM?
Few Insert queries rollbacked due to system crash
My database keeps crashing on startup how can i repair it without it running?

HANDICAP #2

Microsoft Windows is terrible at caching disk changes. Even running FLUSH TABLES; in MySQL in a Windows environment is not a panacea. IMHO anyone using PostgreSQL or Oracle should be able to voice this same complaint about Windows. I will leave it to the SQL Server gurus to answer how they find Windows caching in terms of SQL Server.

ANALYSIS

MyISAM maintains a count of open file handles against the tables.

If the mysqld process or the Windows Server crashes, every MyISAM that had open file handles will retain the open file handle count internally.

When you access a MyISAM table for the first time since mysqld was started, it should have a zero file handle. Otherwise, you get that error message marked as crashed and should be repaired. This explains the tables periodically coming up as crashed crashed.

See my post MyISAM table keeps crashing. What are my options?

SUGGESTIONS

You could switch those tables to InnoDB and let the InnoDB Buffer cache everything, or at least better caching. I would still worry about Windows in this respect because the option innodb_flush_method is disabled in the Windows version of MySQL. I say disabled because the MySQL Documentation says:

  Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you want leave the tables as MyISAM, please go back to Ubuntu because it is a bit more diligent when it comes to flushing disk changes, especially when it has enough RAM. Even a terabyte of RAM will not do a thing for MyISAM tables in Windows.


What is the proper type for columns holding GeoJSON?

In PostgreSQL, as JSONB (ideally), or JSON.

  Which is the proper way of migrating data held in Oracle spatial in production to PostGIS?

As "Well-Known Binary", (WKB) using Get_WKB. Or simply using oracle_fdw which solves most of these problems and uses WKB behind the scenes.

  The only supported geometry types are POINT, LINE, POLYGON, MULTIPOINT, MULTILINE and MULTIPOLYGON in two and three dimensions. Empty PostGIS geometries are not supported because they have no equivalent in Oracle Spatial.

Moreover on the subject of GeoJSON, PostGIS provides ST_GeomFromGeoJSON, and ST_AsGeoJSON if you want to use GeoJSON.


This specific information is not fully stored in SQL Server.

You do however have a few options to make a small analysis.

You can check the index_usage stats ( SYS.DM_DB_INDEX_OPERATIONAL_STATS) and cross reference this to the columns included in the index. This will, however, not give you exactly which columns are used or selected or used in a where clause. But you can derive a lot of information here to check for more optimal indexes.
You can use the following query from the exec_query_stats

 SELECT
     OBJECT_NAME(qt.objectid)
   , qs.execution_count AS [Execution Count]
   , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
   , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
   , qs.total_worker_time AS [TotalWorkerTime]
   , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
   , qs.max_logical_reads
   , qs.max_logical_writes
   , qs.total_physical_reads
   , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
   , qt.text
 FROM
     sys.dm_exec_query_stats AS qs
 CROSS APPLY
     sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

This will give you the execution count of queries on you SQL server (not all, only cached). Here you can go through the query text field and cross reference to your tables but this will be a lengthy process. You can also check the execution stats for stored procedures & then cross reference this to the columns in the stored procedures, but this will again be a tough process.

Use a third party tool which captures most of your queries and query through your queries (this will be slow and a lot of data).


If your existing archive location is no longer available, change it to one that is:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = ('LOCATION=<new location>');

You'll want to take a Level 0 backup immediately afterwards. With missing archivelogs, your database is no longer recoverable.


Order by of records of a table in SQL Server

Test

WITH
cte1 AS ( SELECT 'US' country, 10 cnt
          UNION ALL
          SELECT 'UK', 2
          -- UNION ALL
          -- ...
        ),
cte2 AS ( SELECT customer.*,
                 country.name country,
                 ROW_NUMBER() OVER (PARTITION BY country.name
                                    ORDER BY customer.id) rn
          FROM customer
          JOIN country on customer.country_id = country.id
        )
SELECT cte2.id CustomerID, cte2.name CustomerName, cte2.Country
FROM cte2
JOIN cte1 ON cte1.country = cte2.country
WHERE cte2.rn <= cte1.cnt


SELECT JOIN same table prevent duplicates MySQL 8.x

You can use GROUP BY
select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2 ON (p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePlatform)
GROUP BY p1.idRequest,p1.idPair, (p1.idExchangePlatform + p2.idExchangePlatform )

The Result:


Mysql FLUSH LOGS in live server with replication

FLUSH LOGS; is safe to do anytime.  It will rotate the replication logs; this is harmless; replication knows how to move on to the next log.  The binlog is automatically rotated when it exceeds max_binlog_size.

Before 5.6, FLUSH LOGS did not rotate the slowlog.  See http://dev.mysql.com/doc/refman/5.6/en/flush.html or the page for your version.

I have never done reset master; I would not include it.  I use expire_logs_days for automatically purging binlogs.  (If a slave has been offline for that many days and I don't notice it, I deserve the hassle.)

I prefer log_queries_not_using_indexes = OFF; otherwise it clutters the slowlog.  (A tiny table without an index is often not-a-real-problem.)


Is there an easy way in PostgreSQL to escape newlines, carriage returns, and tabs?

You can use string escape syntax and replace(string,substring,replacement) function like below.

Please note it's heavily dependent on standard_conforming_strings setting. Should be on. You can find details here, here and here.

postgres=# \d t
     Table "public.t"
 Column | Type | Modifiers
--------+------+-----------
 s      | text |

postgres=# SELECT s, md5( s ), replace( replace( replace( s, E'\n', '\n' ), E'\t', '\t' ), E'\r', '\r' ) FROM t;
    s     |               md5                |  replace
----------+----------------------------------+------------
 newline:+| 75c093ed14f1239a3510006326a1a260 | newline:\n
          |                                  |
 tab:     | 355393c5cab9aa324c6ec90682b13d7e | tab:\t
 cr:\r    | 094911687582e40cfeb6217f8c760543 | cr:\r
(3 rows)


It looks like you have very few indexes in there.  If this isn't a third-party app then here are some indexes I'd test out.  I'm making some assumptions, such as Row_ID is a unique column.

CREATE UNIQUE CLUSTERED INDEX CIX_OdsSCase_Sc2Sub_RowId ON ODS_S_Case.Sc2Sub
(
    RowId
)

CREATE INDEX IX_OdsSContact_Mcon2Sub_SocSecurityNum ON ODS_S_Contact.MCON2SUB
(
    Soc_Security_Num
)
INCLUDE
(
    Row_Id
)

CREATE INDEX IX_OdsSCaseBnftplan_Cbpsub_CaseId ON ODS_S_CASE_BNFTPLAN
(
    CASE_ID
)
INCLUDE
(
    STATUS_CD
)

CREATE INDEX IX_OdsSContact_Mconsub_SocSecurityNum ON ODS_S_CONTACT.MCONSUB
(
    Soc_Security_Num
)
INCLUDE
(
    Row_Id
)

CREATE INDEX IX_OdsSCase_Scsub_StatusCd_MstrCaseId ON ODS_S_CASE.SCSUB
(
    Status_Cd
    , Mstr_Case_Id
)
INCLUDE
(
    Row_Id
    , Created
    , Type_Cd
)

Also, I can't see this without saying something.  You appear to have a social security number unencrypted, used as a foreign key, assuming it's unique, etc..  This is a risk on many levels.  If what I'm seeing is correct and you're not convinced it's a bad idea  you should read https://www.computerworld.com/article/2552992/not-so-unique.html and https://helifromfinland.blog/2014/04/18/is-social-security-number-a-good-primary-key/

I've spent too much time on this for now.  Try out these indexes in your test environment and see how much of a difference it makes.  If you're able to make these types of changes and didn't know you should then start looking for blog posts introducing people to indexing.


SQL Injection would be straightforward if scripting code was not properly sanitizing its inputs.

Here is a good post on SQL Injection Prevention via Stored Procedures : Do stored procedures prevent SQL injection?

Interestingly, even in the book MySQL 5.0 Certification Study Guide, Chapter 33 is all about using Stored Procedures and Triggers for Administration. In it are three bulletpoints on Page 470:

  For the tables in question, disallow direct access to by ordinary
  users for INSERT, UPDATE, and DELETE statements. (You can do this be
  granting the appropriate privileges to administrative users only.)

  Implement a procedural interface for modifying each table. That is,
  using an administrative account that has access to the table, write
  stored procedure that have DEFINER security and that perform the
  required modifications to the table, giving appropriate data values as
  parameters. Grant the EXECUTE privilege for these routines to the
  appropriate users.

  Require users to perform table modifications by calling the stored
  procedures and passing column values for the rows to be modified as
  parameters. Each procedure examines its arguments and verifies that
  they satisfy whatever constraints are deemed necessary. If the
  arguments are suitable, the procedure performs the requested
  modification. If they are not, the procedure aborts the operation.

In another mysql aspect, you have to secure user authentication. The most overlooked aspect is looking for the mysql schema tables. I wrote about this before in terms of how user authentication works and how to clean it up at install time and later afterwards:

What is the mysql.db table used for?
MySQL : Why are there "test" entries in mysql.db?
Is this a normal set of MySQL privileges?
Secure method beside phpMyAdmin for remote mysql


Optimize query with a subquery (index is not used)

In order for the in clause to be evaluated, the subquery is likely being evaluated for every row. My first performance suggestion would be to use a left join and then filter:

select t.status, t.is_client_coordinator, t.id, first_name, t.last_name
from users t
left join projects p on t.client_coordinator_id = pl.id
where t.status != "CLOSED"
and t.is_client_coordinator
or (
exists(p.client_coordinator_id)
and p.status != "DELETED"
and p.status != "ARCHIVED"
)
ORDER BY concat(t.first_name, t.last_name)


Two corrupted indexes found by DBCC CheckTable

Non-clustered indexes can always be recreated from the base table. No need to drop them just rebuild them:

alter index IX_Advertise_BrandCopy_Price_Stock on BrandCopy rebuild;

Of course, you must figure out how did they end up corrupted. Is Windows reporting IO errors? Does your drive firmware report errors? You need to investigate to figure out what is causing the corruptions and eliminate the problem (likely faulty hardware, disk or memory).


Ok, so it turns out I didn't know that I couldn't roll back to an earlier version of my newly created database until a full backup was made.  This is why I couldn't see any options to roll back to before midnight in the restore dialogue.


Recovering logical space from a tablespace

When you delete records there is nothing that automatically compacts the segment, therefore you will need to do a segment shrink to reclaim the space.  Here is excerpt from the 11.2 Administrator's Guide on Reclaiming Wasted Space:

  Over time, updates and deletes on objects within a tablespace can
  create pockets of empty space that individually are not large enough
  to be reused for new data. This type of empty space is referred to as
  fragmented free space.

  Objects with fragmented free space can result in much wasted space,
  and can impact database performance. The preferred way to defragment
  and reclaim this space is to perform an online segment shrink. This
  process consolidates fragmented free space below the high water mark
  and compacts the segment. After compaction, the high water mark is
  moved, resulting in new free space above the high water mark. That
  space above the high water mark is then deallocated. The segment
  remains available for queries and DML during most of the operation,
  and no extra disk space need be allocated.

Further down on the same page you can read this:

  Segment shrink is an online, in-place operation. DML operations and
  queries can be issued during the data movement phase of segment
  shrink. Concurrent DML operations are blocked for a short time at the
  end of the shrink operation, when the space is deallocated. Indexes
  are maintained during the shrink operation and remain usable after the
  operation is complete. Segment shrink does not require extra disk
  space to be allocated.

  Segment shrink reclaims unused space both above and below the high
  water mark. In contrast, space deallocation reclaims unused space only
  above the high water mark. In shrink operations, by default, the
  database compacts the segment, adjusts the high water mark, and
  releases the reclaimed space.

The page includes much more information on the issue including examples.

The "Segment Space and the High Water Mark" section from the Concepts Guide might also be useful.


Look in your Wait Stats (preferably Query Store Wait Stats, Session Wait Stats, and otherwise System Wait Stats) for preemptive waits.
Sessions enter preemptive waits when calling into code that doesn't participate with SQL Server's cooperative scheduler, which is a common cause of a session refusing to yield a scheduler (CPU Core).


How to get the equivalent of array or string GROUP BY aggregate functions in Access?

Access does come with a set of domain aggregation functions. An overview of these can be found here or in the MSN documentation here. There is, unfortunately, no native domain concatenation function.

Fortunately, Patrick Matthews has published a domain concatenation function here (and reproduced below to guard against link rot) that will accomplish what you're looking for.

From the documentation of DConcat():

SELECT DConcat("Account","Sample") AS Accounts
FROM Sample
GROUP BY DConcat("Account","Sample");

Returns:
Accounts
-----------------------------------------------------------------------------
Acct1, Acct10, Acct2, Acct3, Acct4, Acct5, Acct6, Acct7, Acct8, Acct9

For the specific example in question:

SELECT ID, DConcat("col","YourTable", "ID=1") AS cols
FROM YourTable
WHERE ID=1
GROUP BY ID, DConcat("col","YourTable", "ID=1")

Should return these values:

ID cols
----------
1  A, B, C

Then repeat for each different required ID value.

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)

    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years

    ' Requires reference to Microsoft DAO library

    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.

    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause

    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long

    On Error GoTo ErrHandler

    ' Initialize to Null

    DConcat = Null

    ' Build up a query to grab the information needed for the concatenation

    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")

    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value

    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF

            ' Initialize variable for this row

            ThisItem = ""

            ' Concatenate columns on this row

            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next

            ' Trim leading delimiter

            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

            ' Concatenate row result to function return value

            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With

    ' Trim leading delimiter

    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)

    GoTo Cleanup

ErrHandler:

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria

    DConcat = CVErr(Err.Number)

Cleanup:
    Set rs = Nothing

End Function


What is the difference between these invocations of COUNT()?

COUNT

  If you specify expr, then COUNT returns the number of rows where expr
  is not null. You can count either all rows, or only distinct values of
  expr.

  If you specify the asterisk (*), then this function returns all rows,
  including duplicates and nulls. COUNT never returns null.

0, 1, 'FAST' are all constant expressions, they are never null, so the query returns all rows.

* also returns all rows by definition.

So they all do the same.


Sql Server Stored procedure exicution taken long time

In addition to Kapil's answer, I'd like to add the following...

SQL Server is optimized to perform set-based operations.  Your stored procedure reads very much like an iterative process.  (Do this step, increment counter, do another step, etc.)  I believe that if you restructure your stored procedure to perform one operation on 1,000 rows as opposed to 1,000 operations on a single row, that you will get better performance.  One thing that might help is to understand what a Tally table is.  Use of a Tally table should be able to help you with your incrementing ID values.  You should be able to find many blogs that tell you how to set up and use a Tally table.  (Here is one example.)

Each single-record insert or update is a command that needs to be analyzed, executed and whose results need to be handled.  By reducing the number of executions by a factor of roughly 1,000, you should see improved results.  However we do not have your server, nor your data.  So you'll need to do that footwork.  From reading the two comments that you posted so far, I get the feeling that you're looking for one of us to wave a magic wand and have everything fixed, nice and easy.  Performance tuning is rarely nice and easy.

Good luck.


How can I make a host show up by hostname in 'show processlist' rather than by IP address?

By default, MySQL makes a DNS lookup to resolve the client hostnames. You can disable this lookup with the option skip_name_resolve, check if your server wasn't started with this option:

[22:54:53] [DEV\(none)] mysql> show variables like "skip_name_resolve";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

Best Regards,

Max.


Space consumed by a column in non clustered index

Is that any way for me to know how much the extra space consumed by the index by adding a new key column

Create a new index and check its size:
select schema_name(t.schema_id) schema_name,
       t.name table_name,
       i.name index_name,
       ps.used_page_count / 8. / 1024 size_mb,
       row_count,
       row_count / nullif(ps.used_page_count,0) rows_per_page
from sys.tables t
join sys.indexes i
  on t.object_id = i.object_id
join sys.dm_db_partition_stats ps
  on i.object_id = ps.object_id
 and i.index_id = ps.index_id
 order by table_name, index_name

is that there is any data/index ratio best practices, as I know its better not to have more than 5 index per table

Start with indexes for your candidate keys and foreign keys, then add additional indexes as necessary for query performance, monitoring both missing indexes and unused indexes.


InnoDB uses B+Tree indexes, not B-Tree. All details about InnoDB data structures can be found here. You may also want to look at these diagrams. The author of both the resources, Jeremy Cole, was head of MySQL team at Google.

Why is the syntax BTREE instead of B+TREE? This question should be posed to some MySQL or MariaDB engineer, but I see at least two possible reasons:

B+TREE would be a very bad keyword, because it contains +, which is usually an operator.
That syntax is older than InnoDB. It is probably as old as the ISAM storage engine, which exists no more. It is very possible that B-TREE was used at that time.

Why does the documentation state that InnoDB uses B-Tree? Well, not all MySQL users are supposed to know what B+Tree is. This may be an oversimplification, but in that context it seems to me acceptable.

You wrote that you know the difference between B-Tree and B+Tree. Than the different performance characteristics should be clear:

B+Tree is faster for sorting;
B-Tree is faster when you insert values in the middle.

But in general, B+Tree is considered superior. How much? I don't know, but surely not orders of magnitude.


You need to ensure the SQL Server Service Account has the required Access Control for the paths used for storing your backups.

Check the SQL Server Accounts and Permissions page for details about what ACLs are required.  See Configure File System Permissions for database engine access for details about how to change permissions.


Apparently we were able to solve the problem. There is one particular UDF that is being called thousands of times per second, and it declares 3 table variables. For most of the calls, the UDF returns before declaring the tables, but I did not realize that SQLServer creates them anyway. We separated the second part of the query to a new UDF that is called only when necessary. This way, most of the times, the table variables are not created (obtained from cache to be more precise). By doing this, we were able to significantly reduce the perf index Active Temp Tables, and SQL Server returned to normal behavior.

For those that did not know this: if an ad-hoc query, PROC or UDF declare a table variable (or a temporary table), SQL Server creates the tables, before starting execution. So, an early return does not prevent temp tables from being created. This completely fooled this accidental DBA. I was trying to find the culprit by changing queries one by one, putting a return in the beginning. Because of this behavior (which is understandable), I was failing to find the root cause of the contention on the DB. Live and learn every day!

For those who are now saying that it does not make sense to have a query called so many times, you are right! We are already changing the application to cache the results outside SQL Server.

Thank you Aaron and RTL for your help.

Renato Ferreira