Values of created_tmp_disk_tables increases

Created_tmp_disk_tables is an indication of poorly written queries.  Especially when Created_tmp_disk_tables/Created_tmp_tables is a high percentage.

The settings cannot necessarily prevent it.  You already have tmp_table_size and max_heap_table_size as high as the should be for 32GB of RAM.

The issue may not be a real issue -- If the tmp table is only a few rows long, you probably won't notice an issue.

The real issue is whether queries are running "too slow".  That is best judged by turning on the slowlog.

When a query is too complex, it needs to generate a tmp table.  The first choice is to make a MEMORY.  But that cannot be done if any referenced columns are TEXT or BLOB.  The second choice is MyISAM, which implies "disk tmp table", although it may stay cached in RAM and never actually touch the disk.  Another cause for it is when the MEMORY table exceeds those two max... settings, it will be converted to MyISAM.  This is unlikely in your case because of how big they are.

So, once you have discovered which queries are slow, we can discuss how to add composite indexes and/or reformulate the queries to avoid the need for disk tmp tables.  Or, at least try to speed them up.


how to set timestamp in datetime field if the time is not specified?

There's a few ways. Here's one.

declare @startDate datetime = '20180114'

if @startDate = cast(@startDate as date)
begin
    set @startDate = dateadd(second,-1,dateadd(day,1,@startdate))
end

select @startDate


Your backup plan seems to be good. Of course, everything depends on:

Recovery Point Objective (RPO) in other words – how much data can you afford to lose?

And

Recovery Time Objective (RTO) if database disaster occurs, how much time are you able to spend restoring a database to its working condition?

Note, if you will run your database under the simple recovery model you will not able to make  transaction log backups.


Efficiciency of multiple small triggers versus one large trigger on SQL Server

Community wiki answer:

You already sound like you know the right answer. The best approach is the one that has the trigger code run the fastest with the least amount of code. If it were me, I'd have one trigger per action. Will there be duplicate code - absolutely - but it saves you for when anything needs to be changed and can be customized per situation.

You could try both and then simulate load to see which you like/performs the best. Aaron Bertrand found there was observable additional overhead with multiple triggers performing the same operations, but YMMV.

However, a couple dozen transactions per minute doesn't sound like a tremendous amount of activity. Maintainability 'might' be able to justify a small amount of overhead. There are numerous advantages to using separate 'single-function' triggers. As you indicated, they have a single purpose and can be individually tested (by different people), enabled, disabled. Isolating the processing into separate 'single-function' triggers 'might' reduce the risk involved in future maintenance.

Of course, final testing of the individual triggers cannot be by different people, as you need to ensure the triggers work properly in unison. And, while future maintenance of smaller chunks of code is logically easier, you cannot ignore the other triggers when making future changes, as something that causes no issues in trigger A could completely mess up what trigger C is doing.


How to add up a column depending on data from another column

The problem is that you are not joining your two tables based on band_id, as a result you are creating a Cartesian product.  When you create this cartesian product, you are combining each row from your placing table to each row in the band table.

For example, if you have data similar to the following:

create table band
(
  band_id number
);

insert into band values (1);
insert into band values (2);
insert into band values (3);
insert into band values (4);
insert into band values (5);

create table placing
(
  band_id number,
  points number
);

insert into placing values (1, 10);
insert into placing values (1, 2);
insert into placing values (2, 1);
insert into placing values (3, 3);
insert into placing values (3, 89);
insert into placing values (4, 45);
insert into placing values (5, 63);

And you query the data without a join on the band_id column:

SELECT
  placing.points,
  band.band_id
FROM placing, band;

You are generating data similar to:

| POINTS | BAND_ID |
|--------|---------|
|     10 |       1 |
|      2 |       1 |
|      1 |       1 |
|      3 |       1 |
|     89 |       1 |
|     45 |       1 |
|     63 |       1 |

See Demo. As you can see band_id = 1 now has every single point value from the placing table even though your really only have points = 10 and points=2.

In order to get the correct result, you need to JOIN the two tables on the band_id column. Your query will be similar to the following:

SELECT
  SUM(p.points) AS sumpoints,
  b.band_id
FROM placing p
INNER JOIN band b
  on p.band_id = b.band_id
GROUP BY b.band_id
ORDER BY SUM(p.points) DESC;

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

| SUMPOINTS | BAND_ID |
|-----------|---------|
|        92 |       3 |
|        63 |       5 |
|        45 |       4 |
|        12 |       1 |
|         1 |       2 |


Mixing Clustered Instance and Non-Clustered Instance

How is the new instance intended to be used? Do the databases destined to be on it relate to those in the cluster at all? (please add this detail to the question so others will see it easily)

If it is not part of the cluster then isn't it just a stand-alone instance that needs no unusual care? You should just be able to install it as normal.

If it is being installed on the same machine as one of the clustered instances then be careful about memory use: instances are not terribly good at sharing memory between them so if the new instances is going on the passive of the pair it may claim much RAM and then if/when a fail-over occurs the clustered instance will find very little available when it needs to take over from the previously active one. There are settings to limit the amount of memory an instance can use, look into setting those if you are adding a new instance to one of the existing machines partaking in the cluster.


DBCC CHECKDB will also run DBCC CHECKALLOC, DBCC CHECKTRABLE, and DBCC CHECKCATALOG.

Running this regularly is extremely important in an effort to detect data corruption. If availability is important, you should consider running this daily. It is possible for backups to contain corrupted data as well. This can easily go unnoticed if the corrupted page(s) isn't accessed. Corruption can occur due to several factors including: storage configurations, power faults, poor cache, etc.

It is possible for corruption to spread, so be prepared! Your system and user databases should be backed up frequently with CHECKSUM and the databases be configured with the Page Verify setting set to CHECKSUM.

I would take a look at Kendra Little's post on the issue.

Brent Ozar also has a post regarding what to do when DBCC CHECKDB reports corruption.

Steve Stedman also has an excellent Weekly Database Corruption Challenge if you wish to attempt repairs.


MySQL insert into different databases tables based on PK value

Hi you can try the below query.

insert into db1.user_feedback as select * from db2.user_feedback where < your
conditions>;
insert into db2.user_feedback as select * from db1.user_feedback where < your
conditions>;


How to handle the My SQL DB Master to Master replication when there was a disk space outage

There are two things that need to be mentioned here:

1) The word impossible is a dead giveaway. Client requested master to start replication from impossible position. Here is what this essentially means:

It just so happens that the file size of a binary log and the position of the binary logs are one and the same. The slave wants to read from mysql-bin.001067 position 183468345. Since the word 'impossible' comes up in the message, this indicates that the master binary log mysql-bin.001067 is less than 183468345 bytes. To get replication going again, skip to the next binary log:

CHANGE MASTER TO
  MASTER_HOST='XX.XX.XXX.XXX',
  MASTER_USER='replicate',
  MASTER_PASSWORD='slave',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.001068',
  MASTER_LOG_POS=NewPos,
  MASTER_CONNECT_RETRY=10;
START SLAVE;

NewPos is dependent on the version of MySQL.

MySQL 5.6 : NewPos = 120
MySQL 5.5 : NewPos = 107
MySQL 5.1 : NewPos = 106
MySQL 5.0 : NewPos = 98
MySQL 4.x : NewPos = 98

2) You could just look into using data synchronization tools from Percona.

mk-table-checksum
mk-table-sync

I have used these tools for about 2 years and they help you hunt down differences in tables between master and slave, even if the table on the master is InnoDB and the same table on the slave is MyISAM (provided the tables have the same table structure).

Replication must be on while running these tools.

BTW Percona has a new set of tools called the Percona Toolkit. They forked away from their own MAATKIT tools to make better ones. The tools are probably called pt-table-checksum and pt-table-sync.


Multiple Database in One Server Problems

The answer is classic -"It depends !"

Sit down with your stake holders and present then the pros and cons of the 2 options. Being in the business of client hosting, I have faced this situation and below is my view point :

  None of the multiple databases relate or utilize cross database joins. We have ample resourcing and budget.

This means that the dbs are decoupled or independent. I would say, club the dbs depending on the criticality and uptime SLAs and then distribute them between 2-3 servers (VM would be another option as well). This way, you dont put all your eggs in the same basket.

Why ?

Putting all 5 dbs on one server will be a single point of failure. An issue with a single DB puts all your other DBs at risk. Isolate your risk as much as possible but that will cost you additional licensing and maintenance overhead which you have by saying - ample resourcing and budget.
Even if you have Resource Governor enabled, it does not govern buffer pool. It only governs "working memory of a query e.g. sort, hash etc". In 2014 and up, you can limit IOPS using Resource Governor.
You have one solution for a given server hosting all your dbs to do backups - full and log, integrity checks, index maintenance. If there is an issue with your server and that job fails midway, other dbs will be impacted except you have inbuilt mechanism to detect and fix the issue and continue with the rest.
An external issue with OS affects all your estate being in one place.
Even with AGs, you have a fix amount of worker threads and they get shared with all your dbs on a single server. Having dbs spread out depending on the criticality will alleviate that problem.
Tempdb is one per server. So imagine all your dbs being on the same server might be a bottleneck.
Problem isolation will be difficult if you have one server serving all your dbs.

I have written a sort of similar answer Which is better: one database per application, or just one database?


Why are duplicate permissions appearing on the securables GUI in SSMS?

There are multiple entries because you can be granted the same permission multiple times, from different "grantors". If that principal (i.e. the "grantee") were already granted that same permission from two grantors, then you would see three entries for that same permission on this screen.

For the "duplicate" entries that you are seeing, the one with the empty "Grantor" column could be used to assign that same permission from a different grantor than the one listed in the other entry for that same permission (in this case it is: dbo). Of course, I am not sure why there is that extra entry when you are connected as principal that already has a granted permission for what would be the same grantor since you can only have one row per grantor. If you change the "With Grant" option as "dbo", for example, it will simply update the entry for "Grantor" = "dbo". I guess the GUI does not check the connection to filter out the empty rows when there is a non-empty "Grantor" row for the same principal as the connection. Then again, it would make sense to have that empty row if it the "Grantor" cell was a drop-down of principals that would allow for selecting one to be used in the AS clause of the GRANT statement, but it isn't, so having the empty row seems more confusing than practical.

Try the following to see this in action:

USE [tempdb];

CREATE USER [GrantPark] WITHOUT LOGIN;
CREATE USER [GrantWard] WITHOUT LOGIN;

CREATE TABLE dbo.MultiplePermissionsTest (Col1 INT);

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantPark] WITH GRANT OPTION;

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantWard];

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

GRANT SELECT ON dbo.MultiplePermissionsTest TO [GrantWard] AS [GrantPark];

SELECT dp.[permission_name], dp.state_desc, USER_NAME(dp.grantee_principal_id)
       AS [Grantee], USER_NAME(dp.grantor_principal_id) AS [Grantor]
FROM   sys.database_permissions dp
WHERE  OBJECT_NAME(dp.major_id) = N'MultiplePermissionsTest';

Returns:

permission_name    state_desc                 Grantee      Grantor
(no rows)

permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo

permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo
SELECT             GRANT                      GrantWard    dbo

permission_name    state_desc                 Grantee      Grantor
SELECT             GRANT_WITH_GRANT_OPTION    GrantPark    dbo
SELECT             GRANT                      GrantWard    dbo
SELECT             GRANT                      GrantWard    GrantPark

Now, go to SSMS and expand the "System Databases" folder, then go to "tempdb", and then to "Tables".  Right-click on "dbo.MultiplePermissionsTest" and go to "Properties", then to "Permissions", and select "GrantWard". Scroll down a little and you should see 3 rows for "Select", each with a different value for "Grantor": (empty), dbo, and GrantPark.


Mandatory to use "Three Parts Name" in T-SQL statements of SP

Using three-part names in queries that reference objects in the current database is a terrible practice and you should create a rule against it.  The most frequent problem with it is that it prevents you from having two instances of your application database on a single server, and can lead to unintentional cross-database access.
Using two-part names in dynamic SQL has some, slight, performance benefit as the object name resolution doen't have to check the user's default schema and then the dbo schema.  But in a view or stored procedure is not as helpful as object name resolution uses the object's schema.  And in the paradigm case of a proc/view in dbo referencing a table in dbo it doesn't help at all.


How to enforce full integer (no floating point)?

When you define a column as number(m,n) where m is larger than n and n is positive, the values you can store in it can have m-n digits to the left from period and n digits to the right after period. For example when you defined your column as number(5,3), these are valid values: 1,234, 12,345. If you attempt to insert a value with a precision higher than n, it is automatically rounded, for example 1,2345 is rounded to 1,235.

When you define a column as number(m,0) you can have m digits to the left before the decimal point, and the numbers you insert are automatically rounded.

When you define a column as number(m,n) where n is larger than m, the values you can store in it can have zero digits to the left from the period and n digits to the right from the period with n-m zeros right after the decimal point. For example, if you defined your column as number(3,5), you can have values like 0.00123 in it, but not 0.01200. If you insert the value with more than n digits like 0.001235 it's automatically rounded to 0.00124.

When you define a column as number(m,n) where n is negative then it can store m-n digits to the left from decimal point, there should be exactly abs(n) zeros to the left from the decimal point, and the number is rounded to the last abs(n) places. For example, if you defined your column as number(3,-1), you can store numbers with 3 - -1 = 4 digits before decimal point which should have zeros in the last abs(n)=1 places. You can have values like 280, 1230, and if you isert values with digits other than zero in the last abs(n) places, the numbers are automatically rounded, for example 536 will eventually be stored as 540, 1245 will be stored as 1250.


Best way to check if record exists or not in MySQL

You could probably reduce this to 2 queries with something like:

select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
    select * from table order by date_field desc limit 0,20 ;
end if ;


Can not install a database, can I still learn though?

The easiest way to learn about SQL-Server is to install a SQL Server express.

Even without having an actual website attached to it, Microsoft has a few samples.
And if you want live environment to try out, and see what you can put together, Azure has a free trial.
Which you can use to set up a few vm's.

It's very hard to learn anything from a limited environment, every problem or fault might or might not be caused by something you have control over.
If you want to get the most out of it, set up your own environment.
Or contact whoever is in charge of your environment to see what can be arranged.


Can filtered indexes help improve queries that are based on a time entered or should this be avoided?

BitOff,

It depends on a few factors, there is no one answer for every scenario. For example, you may want to partition and archive some old data onto a dedicated set of disks becvause it is accessed infrequently. I doubt you would consider swapping such a partition for a filtered index. And I doubt you want to create a partition for all the values of NULL in a specific column as opposed to using a filtered index.

You should read the partitioning whitepaper by Kimberley Tripp at http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx, and the filtered index design guidelines found at http://technet.microsoft.com/en-us/library/cc280372.aspx.

Those two links should help point you in the right direction as far as all the design considerations you need to take into account.


Considerable size difference between mysql versions

I suggest looking at the 5.6/5.7 release notes to see what changed from 5.5.

ib_logfile file size are because the innodb_log_file_size changed from the outrageously small value of 5M to something that actually performs when people update any modest amount of data.

The ibdata1 increase in size might be a result of utf8_mb4 being the default.

System tables support more features and have some bigger fields.

Overall, its not that much. Are you really running on an embedded processor?


Why is `SELECT @@IDENTITY` returning a decimal?

@@identity returns a numeric(38,0).  You'll need to cast it to get it to an int.
SELECT CAST(@@identity AS INT)

Also, try using scope_identity instead.  If you have any triggers on the Customers table, you may end up getting the last identity from another table.

Finally, since you're using dapper, you'll want to wrap all of that inside a stored procedure so that you are guaranteed to execute the insert and then the select on the identity in the same batch.
Theoretically, it should work most of the time to execute both of those on their own.  But issues could arise if you have to go to the database twice.  (E.g. how does this work with connection pooling?  What about dropped connections? etc.)  If you just throw it all in a stored procedure, you won't have to worry about that extra effort down the road.


SQL query failing because of commented part of T-SQL query

Given that the script works without the header comment block but fails with it (but only for remote / TCP connections), the issue is most likely due to a parsing error. For example, SQLCMD and SSMS do their own pre-parsing of the submitted script (to break it into batches), and sometimes that pre-parsing doesn't work correctly (e.g. "GO" in 2nd half of nested block comments breaks batch parsing in SSMS and SQLCMD). To narrow it down, you can try the following tests:

Highlight the entire /*...*/ comment block at the top and then hit Control-K, Control-C to comment each line out via inline comments (i.e. --):

--/********************************************************************************
--* Summary: Creates source database schema for BCP Import. Only created PK on ...
--...
--*********************************************************************************/

Remove just a portion of the comment block. So maybe start by removing most of it (keep the top 3 lines and the bottom ***/ line). Then, if it works, keep adding back in the lines, starting with line #4, until it stops working. Meaning, the comment block would start out as being just the following:

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on ...
* No other constraints are replicated
*********************************************************************************/

If that works, add in the 4th line so that the comment block is now:

/********************************************************************************
* Summary: Creates source database schema for BCP Import. Only created PK on ...
* No other constraints are replicated
* Parameters:
*********************************************************************************/

O.P. tried method #2 above and was able to get the script to work remotely with only the following line (4th from the bottom) removed:

/*******...
  Computed column definition being hard coded now as xp_cmdshell cannot read from sys.computed_columns
...*******/

O.P. then used inline comment (method #1 above) to narrow down a subset of the offending line and was able to get the following to work:

/*******...
  Computed column definition being hard coded now as --xp_cmdshell cannot read from sys.computed_columns
...*******/

At this point it is still unclear as to the exact root cause of the issue. I was unable to reproduce this error using the exact same comment block, testing in both SSMS 2008 R2 and SSMS 2012, even enabling SQLCMD Mode and forcing a TCP/IP connection (verifying via SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;). @MartinSmith asked in the discussion if there was "any sort of network security between the client and the server that might take exception to xp_cmdshell". That still could be a possibility, hence more testing is needed in order to determine what layer, specifically, is unhappy with xp_cmdshell (or some other part of that line that is to the right of the newly added --).

UPDATE

While the root cause has not been identified, here is more info to hopefully help narrow this down:

The O.P. ran the following two statements via SSMS, SQLCMD, and .NET SqlClient:

Running just xp_cmdshell (by itself) gets a "missing parameter" error, not the timeout
Running xp_cmdshell 'cls' gets the timeout error. The error includes "HRESULT: -2146232060".

SELECT CONVERT(VARBINARY(30), -2146232060 );
-- 0x80131904

Searching on "HRESULT: 0x80131904" finds that it is a not-very-specific SQL Server error (but mostly related to SharePoint, for some reason). Here are some of the variations on it:

https://social.technet.microsoft.com/Forums/office/en-US/24e3d1ea-7827-4e19-b099-fd44e7c0f9ec/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious

  My problem was solved by rebooting all servers in the farm, including SQL Server, which would have truncated TEMPDB, as one article suggests.

https://social.technet.microsoft.com/Forums/sharepoint/en-US/823ce071-72a8-4d6d-9bef-068cbe2b602d/error-exception-from-hresult-0x80131904?forum=sharepointadminprevious

  We saw several errors on the SharePoint application showing up for Application EventIDs that led us to a VMWare forum that suggested disconnecting the CD/DVD drive from the SQL and SharePoint servers.  Since doing that the environment has been stable and there have been no  HRESULT: 0x80131904 errors.  From other forums it appears the issue is related to the location from which the media is used for installation.

https://social.technet.microsoft.com/Forums/office/en-US/21627022-f910-4bc8-a6f6-6e70f9b0ebff/exception-from-hresult-0x80131904?forum=sharepointadminprevious

  the root cause of the problem was that the location where our SQL server log files are being written to ran out of drive space (i.e. this being the SQL Server that's hosting our SharePoint content database).  We were able to resolve the problem by removing many of the older log files from that directory in order to free up some drive space.

https://stackoverflow.com/questions/23216704/sharepoint-2010-web-part-error-exception-from-hresult-0x80131904

  The comparers are case sensitive. should be . In my case, it was , changed to and it works now!

Meaning: this might be related to Locale / default language, Code Page, or Collation.

The O.P. ran the following three queries in SSMS and received an "Incorrect syntax" error:

select 1/0;
go
xp_cmdshell --comment
'cls';

and:

select 1/0;
go
axp_cmdshell --comment
'cls';

and:

select 1/0;
go
p_cmdshell 'cls';

All three of those queries received the following error:

  Msg 102, Level 15, State 1, Line 1
  Incorrect syntax near ' '.

The O.P. ran the following query in SSMS and received a different "Incorrect syntax" error, but not the timeout error:

select 1/0;
xp_cmdshell 'cls';

That query received the following error:

  Msg 102, Level 15, State 1, Line 2
  Incorrect syntax near 'xp_cmdshell'.

@Tom V mentioned that this issue has been submitted on Microsoft Connect, but has also been closed as "Not Reproducible":

Text 'xp_cmdshell' causes transport-level error: semaphore timeout period has expired from remote system.
Here are two very similar reports (both on S.O. but neither with any resolution):

SQL bcp The semaphore timeout period has expired
SQL Strange Timeout Issue


how to create trigger in SQL server using two tables to find stock quantity

Instead of creating a trigger I would suggest you to create a new column in the purchase table which will be a computed column.

Before creating it, create a scalar function which will accept productname or productid and returns int. Inside it, write a query to find the total from sales for that particular row (by passing productname or id) and subtract it from purchase's quantity for that product.

If purchase can contain multiple entries for a particular product then create a separate table Stock and have columns as ID, ProductName\Id, stockQuantity (which will be a computed column as explained above).


If you wish to know under which settings PostgreSQL is currently running, one good way to do it is to have a look at the pg_settings view:

SELECT name, setting, context
FROM pg_settings
WHERE name LIKE '%conf%';

I've added the context column to know how new values will be taken into account (a server restart, a config reload, ...).

You could also use the SHOW command:

SHOW config_file;


Operand data type time is invalid for add or subtract operators

You should use datetime instead of time, nevertheless it is also possible using your current data:

;with temp
AS
(
    SELECT t.ID,
    CAST(t.Arrival AS time(7)) Arrival,
    CAST(t.Departure AS time(7)) Departure,
    CAST(t.[Break] AS time(7)) [Break],
    DepartureNextDate
    FROM (
        VALUES
        (1, '23:00:00', '01:00:00', '00:00:00', 1),
        (2, '08:00:00', '17:00:00', '01:00:00', 0)
    )t(ID, Arrival, Departure, [Break], DepartureNextDate)

)
SELECT ID, CONVERT(time(7),
    CAST(Departure AS Datetime)
    + DATEADD(DAY, DepartureNextDate, '')
    - CAST(Arrival AS Datetime)
    - CAST([Break] AS Datetime)
    )
FROM temp


Trigger SQL Agent job based on text in error log for an AG failover

You need to select <all databases> in the database name selection.  The error you are checking for is not related to a specific database, so having a database specified is effectively filtering it so the alert doesn't care about it.

I just tested this using RAISERROR to log a message that included "'this' or 'that'" and it worked fine.  At first I thought you may need to delimit the single quotes, but in my test I didn't and it worked.


mysql need to show all months data for the selected year

Maybe:

SELECT
   MONTH(`DateAdded`),
   YEAR(`DateAdded`),
   count(pkID) as TRecords
FROM students
WHERE
    fkCategoryID ='56' OR
    fkSecondaryCategoryID ='56' OR
    fkOptionalCategoryID ='56'
group by
    MONTH(`DateAdded`), YEAR(`DateAdded`)

Or maybe some more filters. Your question is not all clear.

Or maybe:

if object_id ('tempdb..#students') is not null drop table #students

create table #students
    (
    pkID int,
    DateAdded date
    )

 insert #students values
      (1, '20100101'),
      (1, '20100201'),
      (1, '20100301'),
      (1, '20100401'),
      (1, '20100501'),
      (1, '20100601'),
      (2, '20100101'),
      (2, '20100201'),
      (2, '20100301')

SELECT
   MONTH(DateAdded),
   YEAR(DateAdded),
   count(pkID) as TRecords,
   sum(count(pkID)) over(partition by YEAR(DateAdded) order by MONTH(DateAdded))
FROM #students
group by
    MONTH(DateAdded), YEAR(DateAdded)
 order by 2,1

Or maybe:

SELECT
   MONTH(s.DateAdded),
   YEAR(s.DateAdded),
   count(s.pkID) as TRecords,
   (select count(pkID)
    from #students
        where
            YEAR(DateAdded) <= YEAR(s.DateAdded)
            and MONTH(DateAdded) <= MONTH(s.DateAdded)
   )
FROM #students s
group by
   MONTH(s.DateAdded), YEAR(s.DateAdded)


Estimated vs. Actual Row counts in a trivial case

It's important to realize that a clustered index scan doesn't always mean that SQL Server expects to read all of the rows from the table. For some queries SQL Server is able to stop early after it finds all of the rows that it needs. The low number of estimated rows that you're seeing in the plan is likely due to a row goal.

Let's walk through a simple example. Suppose that I create two heap tables without any shared values:

CREATE TABLE X_HEAP_1 (ID INT NOT NULL);

INSERT INTO X_HEAP_1 WITH (TABLOCK)
SELECT N FROM dbo.GetNums(1000000)
WHERE N % 10 <> 5;

CREATE TABLE X_HEAP_2 (ID INT NOT NULL);

INSERT INTO X_HEAP_2 WITH (TABLOCK)
SELECT N FROM dbo.GetNums(1000000)
WHERE N % 10 = 5;

I know that the following query will not return any rows:

SELECT TOP (10) H1.ID
FROM X_HEAP_1 h1
INNER JOIN X_HEAP_2 h2 ON h1.ID = h2.ID;

Because there are no indexes on the tables, SQL Server will be forced to read all of the rows from both tables. However, SQL Server does not know that the query will not return any rows. In fact, based on statistics and modeling assumptions it expects the query will return the full 10 rows. It also expects that it will not need to scan every row in both tables to get to 10 rows. Here is an estimated plan:

The query optimizer thinks that after it gets 89 rows from X_HEAP_1 that it will have a total of 10 matching rows from both tables. However, we already know that in the actual plan it will need to scan all 900000 rows from X_HEAP_1:

That's a simple example of how a row goal can lead to an estimated number of rows which is 10000X off.


Postgres 9.4 finally added an extension to preload data from relations into the OS or database buffer cache (at your choice):

pg_prewarm

  This allows reaching full operating performance more quickly.

Run once in your database (detailed instructions here):

CREATE EXTENSION pg_prewarm;

Then it's simple to preload any given relation. Basic example:

SELECT pg_prewarm('my_tbl');

Finds the first table named my_tbl in the search path and loads it to the Postgres buffer cache.

Or:

SELECT pg_prewarm('my_schema.my_tbl', 'prefetch');

  prefetch issues asynchronous prefetch requests to the operating
  system, if this is supported, or throws an error otherwise. read
  reads the requested range of blocks; unlike prefetch, this is
  synchronous and supported on all platforms and builds, but may be
  slower. buffer reads the requested range of blocks into the database
  buffer cache.

The default is buffer, which has the greatest impact (higher cost, best effect).

Read the manual for more details.
Depesz blogged about it, too.


What's the recommended way to handle naming collisions with the PK column?

Given the stated rules, shipment_id_from_vendor would be better than vendor_shipment_id (doesn't end in _id, so not expected to be a PK/FK field).

Generally, for me, a name collision would mean that one or both names require a better definition, to clarify whey they're distinct values. Even near-collisions should be avoided: If you have a requirements date and a request date, using the names req_date and request_date is not particularly helpful; someone may be looking for the request date, see req_date, and assume that they've found what they wanted. requirements_date may be much longer to type, but it's immediately clear what it is, and no one's going to mix it up with request_date.

That said, there's no universal solution to this. I have no issue with long table and column names, if that's what's needed to make them clear; other people would insist brevity is key and make the two columns from the previous paragraph reqr_date and rqst_date.


How to remove Data Quality Services completely?

Follow the instruction given by Microsoft in this article.

Remove Data Quality Server Objects


In addition to the great answer by armitage you probably do not need to use DBCC CLEANTABLE in your scenario.

You state

  Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB..

The best practices in the Microsoft documents says:

  DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space. Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.

It seems like time and space are your biggest goals.  Generally rebuilding an index is quicker (but more resource intensive) than a reorg.

As you are working on a Development server.

Just rebuild your indexes and you will get the benefits of the index reorg and the DBCC CLEANTABLE at the same time, and probably much quicker.

Note Rebuild and Reorganize are not the same thing:

Reorganize and Rebuild Indexes (Microsoft)
Rebuild or Reorganize: SQL Server Index Maintenance (Brent Ozar)
SQLskills SQL101: REBUILD vs. REORGANIZE(Paul Randal)


Why set up static data in views vs. using tables in mysql?

I would simply use the views if they perform.  No need to make copies of data unnecessarily, and many database platforms allow indexed or materialized views and filtered views etc.

HOWEVER, MySQL does not support indexed views.  So unless the underlying indexes on your table support the various ways you are accessing the data, it might be worthwhile materializing a version of the view yourself.


How to use the 'select benchmark' command on a procedure

The BENCKMARK function only works on expressions.
What qualifies as an expression?

Stored Function (has a return value)
ENCODE function on a String
1 + 2 (that expression gives an integer)

ALTERNATIVE
What you need is to simulate the BENCHMARK function yourself.
Here is some sample code for you to try
drop database if exists mybmark;
create database mybmark;
use mybmark
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`testproc` $$
CREATE PROCEDURE `mybmark`.`testproc` ()
BEGIN

    DECLARE answer INT;

    SELECT 1+2 INTO answer;

END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`mybenchmark` $$
CREATE PROCEDURE `mybmark`.`mybenchmark` (loop_count INT,expr varchar(128))
BEGIN
    DECLARE dt1,dt2,dtdiff,ndx INT;

    SET dt1 = UNIX_TIMESTAMP();
    SET ndx = loop_count;
    SET @sql = expr;
    PREPARE stmt FROM @sql;
    WHILE ndx > 0 DO
        EXECUTE stmt;
        SET ndx = ndx - 1;
    END WHILE;
    DEALLOCATE PREPARE stmt;
    SET dt2 = UNIX_TIMESTAMP();
    SET dtdiff = dt2 - dt1;

    SELECT dt1,dt2,dtdiff;

END $$
DELIMITER ;

To benchmark the testproc procedure, just pass the call to the testproc procedure as a parameter. For example to call the testproc procedure 1,000,000 times, do this:
mysql> call mybmark.mybenchmark(1000000,'CALL mybmark.testproc()');
+------------+------------+--------+
| dt1        | dt2        | dtdiff |
+------------+------------+--------+
| 1333474085 | 1333474102 |     17 |
+------------+------------+--------+
1 row in set (16.80 sec)

Query OK, 0 rows affected (16.80 sec)

mysql>

Give it a Try !!!


Have several 128 elements lists, need to obtain distance between them

I can not save them as a list of floats, since i get: "can't adapt type numpyndarray"

Quick google search shows numpyndarray as being SciPy's "N-dimensional array". This can hold what PostgreSQL calls a cube and vise-versa.

You assume that there is a translation layer that takes numpyndarray and converts the types to a cube for you. There likely isn't. You could provide such a layer, or extend your PostgreSQL connector/driver to provide that layer (see this for information on psycopg), but shy of that you'll have to go by way of text.

That means you'll be calling cube() or providing a text string and a cast to cube.

SELECT '(0,1,2,3,4,5)'::cube;
SELECT CAST ( '(0,1,2,3,4,5)' AS cube );

Your job is to convert numpyndarray to a textual representation like the above, and then to convert it back again.

Cubes can also be constructed as float[] which you may find easier if your DB layer supports that,

SELECT cube(ARRAY[0,1,2,3,4,5]::float[]); ## Array-constructor for float[]
SELECT cube('{1,2,3,4,5}'::float[]);      ## Text-constructor for float[]


Update column by comparing date and string values together

If you want to ignore the time part and match where the days are the same then:

UPDATE dim_sesid ds
       SET role =
             (SELECT min (er.role)
                FROM EMPLOYEEROLE er
               WHERE substr(DS.NAME, 1, 8) || 'xxxxxx'
                  || substr(DS.NAME, 15, length(USERNAME))
               like to_char(thedate::timestamp, 'yyyymmdd') || 'xxxxxx' || USERNAME);

At the moment, for the first row, the to_char(thedate::timestamp, 'yyyymmdd') || er.username would return 20111012aaa which doesn't exist. By replacing the time portion with the same set of characters (xxxxxx) then we can ignore that and it becomes:

           WHERE '20111012' || 'xxxxxx'
              || 'aaa'
           like '20111012' || 'xxxxxx' || 'aaa'

The problem is though that you have two roles for 13th January 2012. The min will always return the day guard leaving the night guard superfluous.


Improve MySQL Query Performance - Too many records

I'd recommend to not have three columns phone1, phone2 and phone3, but have a related table for phones, and have your data fully normalized (or, further normalized):

 CREATE TABLE `phone_directory` (
   `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
   `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
   `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
   `first_name` VARCHAR(30) DEFAULT NULL,
   `middle_initial` CHAR(1) DEFAULT NULL,
   `last_name` VARCHAR(30) DEFAULT NULL,
   `address1` VARCHAR(100) DEFAULT NULL,
   `address2` VARCHAR(100) DEFAULT NULL,
   `address3` VARCHAR(100) DEFAULT NULL,
   `city` VARCHAR(50) DEFAULT NULL,
   `state` CHAR(2) DEFAULT NULL,
   `postal_code` VARCHAR(10) DEFAULT NULL,
   `email` VARCHAR(70) DEFAULT NULL,
   `fax_number` VARCHAR(255) DEFAULT NULL,
   `manager_name` VARCHAR(255) DEFAULT NULL,
   `status` VARCHAR(6) DEFAULT NULL,
   PRIMARY KEY (`lead_id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

 CREATE TABLE phones
 (
     lead_id INT(9) NOT NULL REFERENCES phone_directory(lead_id),
     phone VARCHAR(12) NOT NULL,
     priority tinyint DEFAULT 1,  -- If you need to give them priorities (1, 2, 3, ...), or sort them
     CONSTRAINT unique_phones UNIQUE(phone), -- You don't want repetead telephones. This enforces it.
     PRIMARY KEY(lead_id, phone)  -- Covering index + clustering... for the sake of efficiency
 ) ;

Your check now is only:

SELECT
    lead_id, phone
FROM
    phones
WHERE
    phone IN ('315XXXXXXX','0315XXXXXXX');

On average, this will be 3x faster ... and you can have 0, 1, 2, 3 or any number of phones for a given lead_id, and your UNIQUE constraint enforces uniqueness, so, if there's an error at some point in your application, the database helps you avoid a mistake.

dbfiddle here

NOTE  1: It is not strange to have one company have more than 3 telephone numbers: you're covering all bases.

NOTE 2: As phone numbers aren't going to use non-ASCII characters, you could save some space (and increase slightly in speed, as less data is going to move around) by specifying a single-char collation, such as latin1_bin.

dbfiddle here


Repeatedly emptying a table, keeping the table itself

My short answer is:

Use truncate if you do not see any errors (because truncate cannot be used in many scenarios, such as the table is referenced or the table is in replication, but you will get error in such cases)

The advantages of truncate (over delete esp. in your repeated deletion case) are:

fast as it does not involve big overhead of transaction logging.
much smaller footprint on database log.

Hope this helps !


Migrating PostgreSQL 9.1 database to Ubuntu 14.04

The message from the command-not-found package telling me I needed to install postgres-xc turned out to be misleading. In fact pg_upgrade is part of the default install but it is not in the default PATH rather it is located in /usr/lib/postgresql/9.3/bin/pg_upgrade.

Because pg_upgrade was not in PATH a search through available packages was performed and the postgres-xc package happens to have a /usr/bin/pg_upgrade executable. That was however not the pg_upgrade I was looking for.

Alas pg_upgrade would not work unless I was able to bring up a PostgreSQL instance serving the files in /var/lib/postgresql/9.1 which I wasn't able to do with the packages available in Ubuntu 14.04 repositories.

So I concluded that the answer to my question most likely was: No, converting the files in /var/lib/postgresql/9.1 is not possible using software from the Ubuntu 14.04 repository.

What I did in the end was to use pg_dump on the Debian system where the database originated. My concern about changes being made while pg_dump was running were addressed by setting default_transaction_read_only in /etc/postgresql/9.1/main/postgresql.conf. To ensure that no currently running transaction would commit after I started pg_dump, I restarted PostgreSQL between enabling default_transaction_read_only and starting pg_dump.

This approach had the added benefit of at least having the system keep running in read-only mode while the migration was ongoing, thus minimising the user visible downtime.

Before I started this manoeuvre I verified the process using the output of a pg_dump command performed on the running database.

In particular I verified that:

Database triggers were migrated correctly.
Unusual unicode characters in the data were migrated correctly.

I decided to use the textual output from pg_dump to be imported by using psql rather than the binary output format, because that allowed me to easily perform a few manual sanity checks on the file being migrated between the two databases.


Recreate a table that was deleted during an ALTER statement (errno: -1)?

Move all tables to a temporary database
Drop the original database (it's empty by now)
Move the tables from the temporary database to the original one.

That should fix the InnoDB dictionary and you'll be able to create the table again.


Set replica set member host names on Secondary

There is a guide for how to do this (reconfigure a replica set with unavailable members) here:

http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/

You essentially have to do a forced reconfig of the set because there is no primary available.  If you want to keep a set operational while doing this type of move, you can:

Use your hosts file to temporarily point the old names to the new (or similar) and "fool" the primary into thinking they are up (then make the permanent changes one at a time)
Temporarily add arbiters to prevent the primary from stepping down (2 more for example, so the primary is kept with 3/5 votes)
Reconfigure the set to a single node before doing the moves
Do the moves one server at a time, ensuring there are 2/3 nodes up to form a majority at all times (and hence keep a primary up)


You shouldn't delete the log file.  If you are trying to reattach a data file without the log, SQL Server can technically recreate it, but there are a few potential issues, like if there were open transactions when the database was detached.  In which case, you'd have total data loss.

Consume the space, and don't delete your log files.  You're asking for trouble with that.

See this article on Transaction Logs, in particular the "Log Mismanagement" portion.


Restrict users from modifying datasources in SSRS

I'm going to guess that you are running SSRS in Native mode rather than SharePoint integrated, and that you are using the pre-defined roles to grant access to users. If you want to post more information about the version of SQL Server you are using and the security role these users are in, I can update my answer. For now, try this.

There are pre-defined security roles in SSRS:

Content Manager
Publisher
Browser
Report Builder
My Reports
System Administrator
System User

I'm going to guess that these users are currently in the My Reports Role.  This role is allowed to do quite a bit, including manage data sources and manage reports.  This means these users can create delete, view, and modify data sources.

You might want to consider creating a new role that has only the item-level permissions you want.  You can find the list of item-level tasks here. You basically want to include all of the item-level tasks included in the My Reports role, except Manage Data Sources. You could also modify the My Reports role to remove this one item rather than creating a new role.


Using the ALTER TABLE .. ADD INDEX there is no requirement to add a name.

If you look at the MySQL documentation for ALTER TABLE you can see that the name parameter is optional (between square brackets), just like the type and options:

  | ADD {INDEX|KEY} [index_name]
          [index_type] (index_col_name,...) [index_option] ...

When you add an index without specifying a name MySQL will generate a name based on the name of the first field in the index. If multiple indexes start with the same field the index name will be suffixed by a sequential number.

For example running this code:

CREATE TABLE `index_demo` (
  `IdField` int(11) DEFAULT NULL,
  `DateField` date DEFAULT NULL,
  `GroupField` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `index_demo` ADD INDEX (`IdField`, `DateField`);

ALTER TABLE `index_demo` ADD INDEX (`IdField`, `GroupField`);

Will result in a table with 2 indexes, IdField and IdField_2 as evidenced by the resulting create table statements:

CREATE TABLE `index_demo` (
  `IdField` int(11) DEFAULT NULL,
  `DateField` date DEFAULT NULL,
  `GroupField` char(1) DEFAULT NULL,
  KEY `IdField` (`IdField`,`DateField`),
  KEY `IdField_2` (`IdField`,`GroupField`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please note, the name parameter is only optional when using the ALTER TABLE syntax and not the CREATE INDEX syntax as again documented in the documentation (notice the lack of square brackets)

  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

I would however consider it a good practice to still name your indexes instead of relying on the default naming scheme.


Is it expected behavior that MIN() window function in MariaDB/MySQL can return a non-null value from a window with only null values?

Reported this as a bug to MariaDB bug tracker:

https://jira.mariadb.org/browse/MDEV-21874


Why does OS login not exist in the user table, but I can still login to MySQL?

MySQL has a funny authentication style

Actually, you need to use two functions

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

You must be logging as an anonymous user. CURRENT_USER() will tell you what mysqld allowed you to come in as.

My guess is that qaz is the OS user and it was chosen by the mysql client as the user name to login.

Your set up is not secure. Please run this:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE user='root' AND host<>'localhost';
UPDATE mysql.user SET password=PASSWORD('whateverpassword')
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This does three(3) things:

Blocks anonymous users
Keeps only root@localhost
Assigns a password to root@localhost

If you cannot log back into mysql, do the following

service mysql restart --skip-grant-tables --skip-networking
mysql -e"UPDATE mysql.user SET password=PASSWORD('whateverpassword') WHERE user='root' AND host='localhost';"
service mysql restart

I wrote earlier posts about this

Cannot drop anonymous user from mysql.user
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Secure method beside phpMyAdmin for remote mysql
MySQL : Why are there "test" entries in mysql.db?
mysqladmin user account not secure?


I think your question is quite broad and hard to answer in its entirety. But here are some things I noticed about your design:
General Design
Some tables in your design violate the first normal form. A good example is contact_adress which has adress1, adress2 and adress3 as columns. Usually a place has only one street address and then one adress column is enough, but in case you really want to have the ability to add multiple street adresses to one place, you should move those adresses to another table (contact_adress, street_adress). The same is true for journal_bills (detail1, detail2) and the accounts table: Instead of parent_imediate, parent_second and parent_third a single parent attribute should be enough. To get the second or the third parent you can use recursive CTEs instead.
I don't know all your business requirements, but you should check if your design allows you to enter nonsensical data: Can an account be both cash and credit? If you have currencies, what are the exchange rates? Can a zip code start with one or more zeros? What about the phone numbers? For some things there are best practices, e.g. how to deal with recurring events.
Naming
Some column names are difficult to understand (ag, acc, pmt), which can lead to maintainability problems if someone else has to work with your database. Use a style guide if you're not sure how to name things. Generally it is a good idea to stick to a consitent naming scheme, e.g. give every table a plural name.
How to implement reporting?
I would stick to simple SQL queries and try to stay away from sophisticated functions like triggers unless you really need to, which is probably never the case for a simple application.


T-SQL Purpose of MAX in this Group By Query

You say: "My best educated guess is that somehow max is being used to avoid multiple grouping columns"

That is correct.

and then: "... but how can this return the correct results?"

It returns correct results because the Symbol is the primary key in both the Investments and the Price tables. Therefore, any aggregate function over a P.column or an I.column is aggregating identical values. And MAX(c) when c is 2, 2, 2 or 2 is of course 2.

Could the query be written somehow else, possibly without all these aggregations? Yes, see a related question: Why do wildcards in GROUP BY statements not work?

It would have to be a rather long GROUP BY clause or have the aggregations moved into a subquery with only the Holdings_Secure table (where Symbol is not the Primary key) and then joined to the other two:

SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name

,   H.TotalQuantity
,   H.TotalMarketValue
,   H.Price

,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM
    ( SELECT
          SUM(Quantity) TotalQuantity
      ,   SUM(MarketValue) TotalMarketValue
      ,   MAX(PriceLC) Price
      ,   Symbol
      FROM
          HOLDINGS_SECURE
      WHERE
          Quantity > 0
      GROUP BY
          Symbol
    ) H
  JOIN
    INVESTMENTS I
      ON H.Symbol = I.Symbol
  JOIN
    PRICE P
      ON H.Symbol = P.Symbol
WHERE
        I.Product = 'stock'
    AND I.CategoryCode5 NOT IN ('X', '') ;


Postgres Transaction ID Wraparound and Autovacuum

You can either drop or truncate the table.  They will initially block on the autovac job, so you will need to identify that process and then kill it using your system tools, or pg_terminate_backend.  At that point, the waiting drop or truncate should be able to acquire the lock faster than that autovac can relaunch.


How to avoid row locks on both tables joined in an update?

You don't say what engine the tables are using (show create table $db.$table_name; ).

If you are using the MyISAM engine, you can manually lock the tables.  This isn't recommended for InnoDB tables because you will be over-riding the row level locking mechanism.

If you lock one table, you need to acquire a lock on all you use within the same lock command.  The command to lock both t1 and t2 for your purposes is...

lock table t1 write, t2 read;

After making your changes, you will want to immediately release the locks...

unlock tables;


Full text search on multiple joined tables

The blog post you refer to is wrong in using [INNER] JOIN between post and posts_tags. Nothing enforces that at least one tag exists, so posts without tags are excluded from the result.
(Also, why is there no PK for posts_tags? A dubious DB design.)

You copied that error and made it much worse by combining multiple relationships that way. Each many-to-many relationship allows for the possibility of no (zero) related rows. If you join multiple tables that way, only such rows from article remain that have relatives in each and every of the joined tables.

On the other hand, you multiply rows with multiple relatives in multiple tables, resulting in a potentially huge intermediary table with lots and lots of redundant copies in the aggregated result. Detailed explanation:

Two SQL LEFT JOINS produce incorrect result

This query should work with Postgres 9.1 or later:

SELECT a.article_title AS title
FROM   article a
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.keyword, ' ') AS txt
   FROM   article_keywords x
   JOIN   keyword          y ON y.id = x.keyword_id
   GROUP  BY 1
   ) k USING (id)
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.name, ' ') AS txt
   FROM   article_authors x
   JOIN   author          y ON y.id = x.author_id
   GROUP  BY 1
   ) a USING (id)
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.org_name, ' ') AS txt
   FROM   article_organisations x
   JOIN   organisation          y ON y.id = x.organisation_id
   GROUP  BY 1
   ) o USING (id)
WHERE  to_tsvector(concat_ws(' ', a.article_title, k.txt, a.txt, o.txt))
    @@ to_tsquery('David & Feeney');

Using LEFT [OUTER] JOIN we don't exclude rows without relatives in the joined tables.

By aggregating multiple rows before joining we avoid the cross-multiplication of rows.

I concatenate the string with concat_ws() (which ignores potential NULL values from any of the contributing sources) and call to_tsvector() only once, which should be a bit cheaper.

Besides correctly aggregating input from all related tables, this should also be among the fastest possible solutions. Aggregate first, join later.

That is - as long as you want to combine all words to search for David & Feeney. For instance, this way you get a match for an article associated with any organization that has the word 'Feeney' in its name and any author with 'David' in his / her name. That's what your query suggests. It's typically not what you'd want, though.

I would not concatenate everything for this purpose, but rather search in each table separately for the combination of both words ...

Also adding separators between word to avoid false positives from matching illegally combined words, like @Daniel hinted in his comment.

And I remove the subquery that's not needed.


SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics.

Consistency
There is no general guarantee of consistency. Estimates may be calculated on different (but logically equivalent) subtrees at different times, using different statistical methods.
There is nothing wrong with the logic that says joining those two identical subtrees ought to produce a cross product, but there is equally nothing to say that choice of reasoning is more sound than any other.
Initial estimation
In your specific case, the initial cardinality estimation for the join is not performed on two identical subtrees. The tree shape at that time is:

  LogOp_Join
     LogOp_GbAgg
        LogOp_LeftOuterJoin
           LogOp_Get TBL: ar
           LogOp_Select
              LogOp_Get TBL: tcr
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [tcr].rId
                 ScaOp_Const Value=508
           ScaOp_Logical x_lopAnd
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [ar].fId
                 ScaOp_Identifier [tcr].fId
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [ar].bId
                 ScaOp_Identifier [tcr].bId
        AncOp_PrjList
           AncOp_PrjEl Expr1003
              ScaOp_AggFunc stopMax
                 ScaOp_Convert int
                    ScaOp_Identifier [tcr].isS
     LogOp_Select
        LogOp_GbAgg
           LogOp_LeftOuterJoin
              LogOp_Get TBL: ar
              LogOp_Select
                 LogOp_Get TBL: tcr
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [tcr].rId
                    ScaOp_Const Value=508
              ScaOp_Logical x_lopAnd
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [ar].fId
                    ScaOp_Identifier [tcr].fId
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [ar].bId
                    ScaOp_Identifier [tcr].bId
           AncOp_PrjList
              AncOp_PrjEl Expr1006
                 ScaOp_AggFunc stopMin
                    ScaOp_Convert int
                       ScaOp_Identifier [ar].isT
              AncOp_PrjEl Expr1007
                 ScaOp_AggFunc stopMax
                    ScaOp_Convert int
                       ScaOp_Identifier [tcr].isS
        ScaOp_Comp x_cmpEq
           ScaOp_Identifier Expr1006
           ScaOp_Const Value=1
     ScaOp_Comp x_cmpEq
        ScaOp_Identifier QCOL: [ar].fId
        ScaOp_Identifier QCOL: [ar].fId

The first join input has had an unprojected aggregate simplified away, and the second join input has the predicate t.isT = 1 pushed below it, where t.isT is MIN(CONVERT(INT, ar.isT)). Despite this, the selectivity calculation for the isT predicate is able to use CSelCalcColumnInInterval on a histogram:

  CSelCalcColumnInInterval
      Column: COL: Expr1006

Loaded histogram for column QCOL: [ar].isT from stats with id 3

Selectivity: 4.85248e-005

Stats collection generated:
  CStCollFilter(ID=11, CARD=1)
      CStCollGroupBy(ID=10, CARD=20608)
          CStCollOuterJoin(ID=9, CARD=20608 x_jtLeftOuter)
              CStCollBaseTable(ID=3, CARD=20608 TBL: ar)
              CStCollFilter(ID=8, CARD=1)
                  CStCollBaseTable(ID=4, CARD=28 TBL: tcr)

The (correct) expectation is for 20,608 rows to be reduced to 1 row by this predicate.
Join estimation
The question now becomes how the 20,608 rows from the other join input will match up with this one row:

  LogOp_Join
      CStCollGroupBy(ID=7, CARD=20608)
          CStCollOuterJoin(ID=6, CARD=20608 x_jtLeftOuter)
              ...

      CStCollFilter(ID=11, CARD=1)
          CStCollGroupBy(ID=10, CARD=20608)
              ...

      ScaOp_Comp x_cmpEq
          ScaOp_Identifier QCOL: [ar].fId
          ScaOp_Identifier QCOL: [ar].fId

There are several different ways to estimate the join in general. We could, for example:

Derive new histograms at each plan operator in each subtree, align them at the join (interpolating step values as necessary), and see how they match up; or
Perform a simpler 'coarse' alignment of the histograms (using minimum and maximum values, not step-by-step); or
Compute separate selectivities for the join columns alone (from the base table, and without any filtering), then add in the selectivity effect of the non-join predicate(s).
...

Depending on the cardinality estimator in use, and some heuristics, any of those (or a variation) could be used. See the Microsoft White Paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator for more.
Bug?
Now, as noted in the question, in this case the 'simple' single-column join (on fId) uses the CSelCalcExpressionComparedToExpression calculator:

Plan for computation:

  CSelCalcExpressionComparedToExpression [ar].fId x_cmpEq [ar].fId

Loaded histogram for column QCOL: [ar].bId from stats with id 2
Loaded histogram for column QCOL: [ar].fId from stats with id 1

Selectivity: 0

This calculation assesses that joining the 20,608 rows with the 1 filtered row will have a zero selectivity: no rows will match (reported as one row in final plans). Is this wrong? Yes, probably there is a bug in the new CE here. One could argue that 1 row will match all rows or none, so the result might be reasonable, but there is reason to believe otherwise.
The details are actually rather tricky, but the expectation for the estimate to be based on unfiltered fId histograms, modified by the selectivity of the filter, giving 20608 * 20608 * 4.85248e-005  = 20608 rows is very reasonable.
Following this calculation would mean using the calculator CSelCalcSimpleJoinWithDistinctCounts instead of CSelCalcExpressionComparedToExpression. There is no documented way to do this, but if you are curious, you can enable undocumented trace flag 9479:

Note the final join produces 20,608 rows from two single-row inputs, but that should not be a surprise. It is the same plan produced by the original CE under TF 9481.
I mentioned the details are tricky (and time-consuming to investigate), but as far as I can tell, the root cause of the problem is related to the predicate rId = 508, with a zero selectivity. This zero estimate is raised to one row in the normal way, which appears to contribute to the zero selectivity estimate at the join in question when it accounts for lower predicates in the input tree (hence loading statistics for bId).
Allowing the outer join to keep a zero-row inner-side estimate (instead of raising to one row) (so all outer rows qualify) gives a 'bug-free' join estimation with either calculator. If you're interested in exploring this, the undocumented trace flag is 9473 (alone):

The behaviour of the join cardinality estimation with CSelCalcExpressionComparedToExpression can also be modified to not account for bId with another undocumented variation flag (9494). I mention all these because I know you have an interest in such things; not because they offer a solution. Until you report the issue to Microsoft, and they address it (or not), expressing the query differently is probably the best way forward. Regardless of whether the behaviour is intentional or not, they should be interested to hear about the regression.
Finally, to tidy up one other thing mentioned in the reproduction script: the final position of the Filter in the question plan is the result of a cost-based exploration GbAggAfterJoinSel moving the aggregate and filter above the join, since the join output has such a small number of rows. The filter was initially below the join, as you expected.


Create a single-server replica set only for watching on change streams

Is it OK to just add the --replSet [name] flag to the already existing server and then call rs.initiate() in it without rs.add()-ing any secondary members?

Yes, a single node replica set is fine if you don't require data redundancy or fault tolerance.

  What drawbacks will this have (database size, performance, ...)?

A replica set node maintains an operation log (oplog) which has some additional storage overhead. You can change the size of the oplog to suit your use case. Since you only need the oplog for change streams, it can perhaps be smaller than the default to save on disk space.

In MongoDB 3.6+ you can also adjust the size at runtime using the replSetResizeOplog  command. In earlier server versions the resize procedure is manual: Change the size of the oplog (MongoDB 3.4).

  I'm not that much of a friend of replica sets as I have an important Database which holds about 80-90MB of documents (total size when exporting all collections to disc), but each member consumes around 1.5 GB of space (--logpath data excluded).

Clarifying excessive storage usage is really a separate question, but this definitely seems an anomalous ratio.

Some likely causes:

Storage usage in the dbPath includes the replication oplog. The default oplog size is determined based on free disk space and O/S (for example, 5% of free disk space on Linux/Windows with a lower bound of 990MB and upper bound of 50GB).
You were using an older version of MongoDB (likely 3.2 or earlier) with the MMAPv1 storage engine. MMAP does not compress data on disk and some use cases will lead to storage fragmentation over time. Modern versions of MongoDB default to the WiredTiger storage engine which includes data & index compression and significant improvements over MMAPv1.


How to load entire database into memory?

I want to load my entire database into memory, but how can I do this?

Before figuring out "how" to do something, it is often best to be clear on "why" that something should be done. So, why exactly do you want to load your entire database into memory? Memory is a finite resource, so it needs to be used efficiently / wisely.

  I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory

Ok, so you appear to have 56 GB of RAM beyond the size of the database. But here is what is not being considered outside of the data itself:

Unless your company goes out of business, databases grow, even if you archive off older data. 56 GB will soon not be enough, right?
Memory is needed by the OS (Windows plus the related services such as Scheduled Tasks, etc)
Memory is needed by 3rd party software (monitoring, anti-virus maybe, etc)
Memory is needed to run the database engine process (the MSSQLSERVER or InstanceName NT Service)
Memory is needed to run the SQL Server Agent process
Each Connection / Session requires some amount of memory
Memory is needed for queries (look at the "memory grant" of a query plan):

aggregate operations
computations
sorting
etc

When objects call other objects, SQL Server maintains a call stack of each parent context so that control can revert back to that parent context when the current context completes. This call stack is managed in memory.
Query Plan cache
DMV stats / info
Result sets (or at least the pointers to the position within each Result Set that each process is currently at)
etc
etc

Now, regarding that 200 GB of data, you should consider this: Do you use all 200 GB of that data? I highly doubt it. Loading data pages into memory and not referencing them is inefficient and wasteful. You are far better off loading pages on demand because the disk I/O used to get them and the memory taken up by them will be worth both of those resources since you aren't wasting them on pages that nobody wanted anyway.

  I want to load entire database by single command not SELECT * table by table.

No, there is no single command to do this.

Also, the SELECT COUNT(*) method doesn't even accomplish this goal. All it does is load, via a Scan, the smallest object it can find, which might be a NonClustered Index, or it might actually be the base table itself.  But it doesn't load:

any indexes into memory (if scanning the Table) or the Table itself into memory (if scanning an Index), nor any objects that weren't scanned for the COUNT(*) operation. So you would still need to find a way to force an Index Scan for all Non-Clustered Indexes and/or a Table/Clustered Index Scan if a NonClustered Index was used for the COUNT(*) operation. But that would be even more wasteful because not only are they not always being used, but loading them each time would work against your ability to use the index DMVs to determine which indexes are, in fact, not being used!
any off-row LOB data pages (and probably not Overflow pages). You would need to select any LOB columns separately, and any variable-length columns that have Overflow data.


Aggregation by positive and negative values

I use your code and added some checks, regarding the COST_CHANGE = 0

For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2)

--my source table
DECLARE @tPROD_COST TABLE
(ID INT /*IDENTITY(1,1)*/,
 Duration INT,
 Cost FLOAT,
 COST_NEXT FLOAT,
 COST_CHANGE FLOAT)

 INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE)
 VALUES
(  1 , 1 , 10   , 8.5  , -1.5  ),
(  2 , 1 , 8.5  , 12.2 , 3.7   ),
(  3 , 1 , 12.2 , 5.3  , -6.9  ),
(  4 , 1 , 5.3  , 4.2  , 1.2   ),
(  5 , 1 , 4.2  , 6.2  , 2     ),
(  6 , 1 , 6.2  , 9.2  , 3     ),
(  7 , 1 , 9.2  , 7.5  , -2.7  ),
(  8 , 1 , 7.5  , 6.2  , -1.3  ),
(  9 , 1 , 6.2  , 6.3  , 0.1   ),
(  10 , 1 , 6.3  , 7.2  , 0.9  ),
(  11 , 1 , 7.2  , 7.5  , 0.3  ),
(  12 , 1 , 7.5  , 0    , 7.5  )
,(13,1,10,19.262,9.262)
,(14,1,9.262,9.262,0)
,(15,1,9.262,18.870 , 9.608)
,(16,1,18.870,18.870 , 0)
,(17,1,18.870,18.870 , 0)
,(18,1,18.870, 18.000, -0.870)
,(19,1,18.000, 18.000, 0)
,(20,1,18.000, 17.000, -1.00)

--for SQL 2012

SELECT ID,
      Duration,
      Cost,
      COST_NEXT,
      COST_CHANGE,
      SUM(GRP) OVER (ORDER BY ID ASC) +1 AS Groups
FROM (
    SELECT *,
        CASE
            WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE))
                 AND ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE) <> 0
                 AND Cost_change != 0
            THEN 1
            WHEN SIGN(COST_CHANGE)  <> 0
                AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE))
            THEN 1
            ELSE 0 END AS GRP
    FROM @tPROD_COST AS PC
    OUTER APPLY  -- get the previous COST CHANGE <> 0
    (
        SELECT TOP(1)
            PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
         FROM
            @tPROD_COST AS PC3
         WHERE
            PC.ID>PC3.ID
            AND PC3.COST_CHANGE <> 0
        ORDER BY PC3.ID DESC
    )OA2(Prev_Cost_Change_NonZero)
    ) X

--for SQL 2008,2008R2:

;WITH cte
AS( SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          GRP
    FROM
       ( SELECT ID, Duration, Cost, COST_NEXT, COST_CHANGE,
            CASE
                WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(OA.Prev_Cost_Change,COST_CHANGE))
                     AND ISNULL(OA.Prev_Cost_Change,COST_CHANGE) <> 0
                     AND Cost_change != 0
                THEN 1
                WHEN SIGN(COST_CHANGE)  <> 0
                    AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE))
                THEN 1
                ELSE 0 END AS GRP ,
                OA.Prev_Cost_Change,
                OA2.Prev_Cost_Change_NonZero
        FROM
            @tPROD_COST AS PC
        OUTER APPLY  -- get the previous COST CHANGE
        (
            SELECT TOP(1)
                PC2.COST_CHANGE AS Prev_Cost_Change
             FROM
                @tPROD_COST AS PC2
             WHERE
                PC.ID>PC2.ID
            ORDER BY PC2.ID DESC
        )OA(Prev_Cost_Change)
        OUTER APPLY  -- get the previous COST CHANGE <> 0
        (
            SELECT TOP(1)
                PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
             FROM
                @tPROD_COST AS PC3
             WHERE
                PC.ID>PC3.ID
                AND PC3.COST_CHANGE <> 0
            ORDER BY PC3.ID DESC
        )OA2(Prev_Cost_Change_NonZero)
    )A
)

    SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          --GRP,
          ISNULL(OA.Prev_Sum_Grp,0) +1 AS Groups
    FROM
        cte AS C
    OUTER APPLY  -- get the previous SUM
    (
        SELECT
            SUM(C2.GRP) AS Sum_GRP
         FROM
            cte AS C2
         WHERE
            C.ID>=C2.ID
    )OA(Prev_Sum_GRP)

The output:

ID          Duration    Cost                   COST_NEXT              COST_CHANGE            Groups
----------- ----------- ---------------------- ---------------------- ---------------------- -----------
1           1           10                     8.5                    -1.5                   1
2           1           8.5                    12.2                   3.7                    2
3           1           12.2                   5.3                    -6.9                   3
4           1           5.3                    4.2                    1.2                    4
5           1           4.2                    6.2                    2                      4
6           1           6.2                    9.2                    3                      4
7           1           9.2                    7.5                    -2.7                   5
8           1           7.5                    6.2                    -1.3                   5
9           1           6.2                    6.3                    0.1                    6
10          1           6.3                    7.2                    0.9                    6
11          1           7.2                    7.5                    0.3                    6
12          1           7.5                    0                      7.5                    6
13          1           10                     19.262                 9.262                  6
14          1           9.262                  9.262                  0                      6
15          1           9.262                  18.87                  9.608                  6
16          1           18.87                  18.87                  0                      6
17          1           18.87                  18.87                  0                      6
18          1           18.87                  18                     -0.87                  7
19          1           18                     18                     0                      7
20          1           18                     17                     -1                     7


How to set space usage alert for database using SQL Server 2012 with Amazon RDS as managed service?

You could create a SQL Server Agent Job which will be run daily and check the free space by using a query like below:

USE [YourDatabaseName]

/* Size in MB */
DECLARE @Info TABLE (
    [Size]          decimal(12,2),
    [SpaceUsed]     decimal(12,2),
    [SpaceFree]     decimal(12,2)
)

insert @Info ([Size], [SpaceUsed], [SpaceFree])
SELECT
    [Size] = ISNULL(CONVERT(decimal(12,2),round(f.size/128.000,2)),0)
    ,[SpaceUsed] = ISNULL(CONVERT(decimal(12,2),round(fileproperty(f.name,'SpaceUsed')/128.000,2)),0)
    ,[SpaceFree] = ISNULL(CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,'SpaceUsed'))/128.000,2)),0)
    --,GETDATE() as DT
FROM sys.database_files f
    CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) vs
WHERE f.name = 'YourFileLogicalName'

/* Free space less than  5GB */
If (SELECT TOP 1 [SpaceFree] FROM @Info) < 5120 BEGIN
    /*
        Anything is suitable for you:
        - send email
        - raiserror
        - write log
        .......

    */
    EXECUTE dbo.KickDBAToAwakeHim
END

Maybe, it has sense to create some table and write the values there every day to have information about how the space is changed.


It sounds like you've done your homework to a large degree.  Clearly you're aware of the log chain implications for COPY_ONLY backups.  My advice to you would be to ensure you have a clearly though-out and tested restore plan.

Without having tested your disaster recovery plan, you can never be certain it will work, and therefore it's not a plan.

If you can do standard, i.e. not COPY_ONLY backups, then do them.  I might consider doing standard FULL backups on the primary node every so often just because you can never have too many backups.


If the third table is related to employees and not to every user, you should reference employees.userid.

It makes sense for, say, a table of employee identity card numbers to reference "employees"."userid". But it makes sense for a table of email addresses to reference "users"."userid", because every user has an email address.


How to Restrict Schema Access to Specific IP Address & user?

An AFTER LOGON trigger can be used for this purpose.

CREATE OR REPLACE TRIGGER system.check_ip_addresses
AFTER LOGON
ON DATABASE
BEGIN
IF USER IN ('USER1', 'USER2') THEN
IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN
('192.168.0.10','192.168.0.11') THEN
  RAISE_APPLICATION_ERROR(-20000, 'Can not log in from this IP address (' ||
l_ip_address || ')');
END IF;
END IF;
END;
 /

Remember the following points.

The trigger must use ON DATABASE, as making it schema specific would prevent the RAISE_APPLICATION_ERROR from preventing the logon.
The trigger will fire for all connection attempts, so make sure you restrict which users it affects carefully to reduce the overhead.


Restore from copy of SQL Server backup file produces invalid data error

Since the local copy of the backup file is validated successfully via RESTORE VERIFYONLY, the most likely cause of the issue is corruption of the target file while it is being copied over the network.

You can determine if the two copies of the backup file are binary-identical with the command-line utility fc:

fc /b C:\some\local\path\file1.bak \\server\share\path\file2.bak

Alternately, you could generate a cryptographic hash of each file, and see if the hashes are identical.  Microsoft offers a command-line tool for that purpose at https://www.microsoft.com/en-us/download/details.aspx?id=11533

If both files are binary-identical, the restore should complete without error.


Did you get any error messages on dump, or on restore?  If not, then the most likely explanation is that nothing is wrong.  There is no tight coupling between the size of the database and the size of the dump.  For example, if you deleted a bunch of data and did not do a "Vacuum full", then the database itself is unlikely to shrink, but the dump (and the restore of it) will be smaller.


What can I add to a server to make SQL restores faster?

Don't do a backup and restore; use SQL Server Snapshots. It takes a lot of disk space to store a sparse file the same size as the files you've snapshotted, but rolling back is hundreds of times faster.

They are available in SQL Server Enterprise and SQL Server Developer editions.


How to connect an oracle DB with MySQL workbench?

to answer your question:

"Thanks for your answers. I thought not that oracle own this product they made it able to connect to a oracle database. Is there a semilar tool like workbench for oracle dbs?" from comments,

you can use toad for oracle or even sql developer.


\set path '/tmp/'
\set file 'foo'
\set pf :path:file \echo :pf
/tmp/foo

Why does this work? I quote the manual here:

  \set [ name [ value [ ... ] ] ]

  Sets the internal variable name to value or, if more than one value is given, to the concatenation of all of them. [...]

Emphasis mine.


Environment variable for two oracle_home

I suggest you don't set the environment variables in .profile. Instead use the oraenv utility to change the environment to suite the instance you want to connect to.


Why does my SSIS package produce different results when executed from a maintence plan

It's not that it's executed from a maintenance plan. Rather, it's that the user executing the package is different. NT AUTHORITY\SYSTEM is god on the machine. However, outside the machine, the account is meaningless.

In your case, it attempts to talk to a foreign machine and that request is denied as the share is unlikely to allow Everyone read/write access to it.

I know of at least 3 ways to resolve the problem. The first and worst is to grant Everyone full control of the network share. That would probably allow the local account to do what it needed to on the remote machine but I'm embarrassed to even list that as an option.

The real two options are to change the execution account for SQL Agent to a domain account with sufficient privileges in both the SQL Server Instance and has network access to the share.

A more precise and less risky fix would be to create a create a Credential and then under the SQL Server Agent, create a Proxy that uses the Credentials for job steps of type SSIS Package Execution and then update the job to use the Credentials it is now empowered to access.

As @Shawn Melton pointed out in the comments, a fourth option would be to grant the computer account permissions to the other network resource https://serverfault.com/questions/135867/how-to-grant-network-access-to-localsystem-account


Can I just restore the .diff file over full backup to speed up restore?

Yes, it is possible.
You should restore full backup first WITH NORECOVERY  option, and after that, you can restore only DIFF backup.
Just try without FULL backup;
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\diff-final.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5

RESTORE WITH NORECOVERY Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery
path. To recover the database, you will have to perform a restore
operation by using the RESTORE WITH RECOVERY option (see the preceding
option).

Check out the document.


MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time

You'll want to use LAST_INSERT_ID() to get the identity value that was generated after the INSERT for the PrimaryKey of the Parent table then use it for the ForeignKey to INSERT into the children tables.
Per the documentation:

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit)
value representing the first automatically generated value
successfully inserted for an AUTO_INCREMENT column as a result of the
most recently executed INSERT statement. The value of LAST_INSERT_ID()
remains unchanged if no rows are successfully inserted.

You can see more information in this StackOverflow post.


Select rows, where 3 columns have the same values

SELECT
  col0
  ,col1
  ,col2
  ,col3
FROM
  foo
WHERE
  col0 = col1
  and col0 = col2;


An attempt to document this is made here:

CREATE SEQUENCE (Transact-SQL)

  sequence_name
  Specifies the unique name by which the sequence is known in the
  database.

It is not very clear that the name must be unique across all objects in the database. And even that is not true. The name must be unique within the schema in a database.


A slow SELECT that can be sped up by adding a composite index or reformulating the query will mostly avoid getting blocked.

Ditto for inefficient INSERTs and other queries.

What is your setting for tx_isolation?  Changing that to READ UNCOMMITTED for the long SELECT may help.

Is this a Data Warehouse application and the SELECT is a 'report'?  If so, consider building and maintaining a Summary table.  This makes reports much faster.

Consider Master-Slave setup, with the long SELECTs going to the Slave.

You are using InnoDB, not MyISAM, correct?

If you want more specific advice, please provide (at least) SHOW CREATE TABLE and the SELECT.


Install old Cumulative Update SQL Server 2016 Standard

You said you are running a new instance of SQL 2016 SP2 CU13. In order to install CU4, you will need to uninstall later CUs. I just spun up a new EC2 instance with SQL 2016 STD and went to Control Panel > Programs > Programs and Features > Installed Updates and saw Hotfix 5820, 5698, and 5598 after SP2. They should be uninstalled in the order listed from the newest to the oldest. After that you will be on SP2 and you can install SP4.


sp_Blitz, >1000 plans for CREATE PROCEDURE

Question 1: "What's with CREATE PROCEDURE?!?" When you execute a stored procedure, SQL Server stores the entire text of the stored procedure as the thing you called.

You weren't CREATING the stored procedure - you were only executing it - but this can be a little confusing for folks who are just getting started analyzing the plan cache.

So hey, you're now past that hurdle! Yay, you!

Question 2: "How can 1 stored procedure have multiple plans?" Without seeing the full text of it, it's hard to tell, but I'd start with Erland Sommarskog's epic post, Slow in the App, Fast in SSMS. In particular, check the section titled Different Plans for Different Settings.

I actually don't think that's the problem - I'm betting there's something dynamic inside the proc text - but I understand that you don't want to post your exact code here. Without seeing the exact code, it's tough for outsiders to answer that particular question.

Update: mystery solved. You mentioned casually that this stored procedure happens to use table-valued parameters. This is a known issue with how TVPs can be called. This is a great example of why it's so important to include the complete code that you have questions about - sometimes even the tiniest things can have a big impact on the question you're asking.


How can I change the target server type for a trace template?

If you open Profiler, go to File > Templates > Import and choose your .tdf file.

You will have a new user trace template with the name [filename] (without the .tdf). Go to File > Templates > Edit and pick the trace template you imported. You should then be able to edit the server type by pulling the dropdown:

Then you can save the template and that should be the server type when you start a new trace using that template.


Help with Query. Finding records that have the same relationships (MySQL)

If I understand correctly, you want to perform relational-division. Try this question with lots of different ways to accomplish that: How to filter SQL results in a has-many-through relation.

I would go for the (multiple) JOIN solution but you can always test with your data and queries:

SELECT
    li.*

FROM
    listings AS li

  JOIN
    listings_taxons AS lt1
      ON  lt1.listing_id = li.id
  JOIN
    taxons AS t1
      ON  t1.id = lt1.taxon_id
      AND t1.name = 'Ford'

  JOIN
    listings_taxons AS lt2
      ON  lt2.listing_id = li.id
  JOIN
    taxons AS t2
      ON  t2.id = lt2.taxon_id
      AND t2.name = 'Exhaust'


SQL Server 2016 high idle CPU and queries extremly slow

Since your CPU utilization is low (and you have a great CPU set), I would check for memory pressure first. With SQL Express 2016 you are limited to 1410 MB of memory, but your VM only has 2GB. Give your VM 1GB more of memory and see if that helps. You can also check your SQL Server log if it is paging memory to file.

If that does not work, try setting the database setting LEGACY_CARDINALITY_ESTIMATION to ON. Reference: Using DB Compatibility Level 130 with Old CE in SQL Server 2016. We ran into this with some of our upgrades; results will vary with each database.


I fully support your inclination not to delete material records. The association records joining the Type/Course/Class to Materials however, you will need to delete if you no longer need the association.

I would recommend the following schema, which I've written in T-SQL as it's my native SQL flavor. It provides you the basis you create you Type -> Course -> Class structure, and enables to you share a global materials list across all three, whilst creating unique associations at each level.

Here is a sqlfiddle to let you play around and get familiar with the design.

create table CourseType (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table Course (
  Id int identity primary key
  ,CategoryId int not null foreign key references CourseType(Id)
  ,Name nvarchar(100) not null
);

create table Class (
  Id int identity primary key
  ,CourseId int not null foreign key references Course (Id)
  ,Name nvarchar(100) not null
);

create table Material (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table CourseTypeMaterials (
  CourseTypeId int not null foreign key references CourseType(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseTypeId, MaterialId)
);

create table CourseMaterials (
  CourseId int not null foreign key references Course(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseId, MaterialId)
);

create table ClassMaterials (
  ClassId int not null foreign key references Class(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (ClassId, MaterialId)
);


This problem can easily be solved by php...
Get the number of rooms selected from the first query and assign it to a variable and then loop the second query by the number of rooms choosen.


That is definite a security hole. That's because mysql was installed that way.

To remove those entries, please run these lines

DELETE FROM mysql.user WHERE user='' or password='';
FLUSH PRIVILEGES;

Here are three(3) past posts I wrote on how and why to do such cleanup of mysql.user and mysql.db

Secure method beside phpMyAdmin for remote mysql
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
MySQL : Why are there "test" entries in mysql.db?


PostgreSQL database dumps are normally produced by the command line tool pg_dump, and expected to be used via either pg_restore or simply psql command line tools.

However, you can both backup and restore from PgAdminIII. The backup option is available via right-clicking either the server or any database or schema. The Restore option is available via right-clicking a database or schema.

Here's a step-by-step example of how dump or restore a database using PgAdmin.


Caveats of OUTER JOIN on nested JSON value

Use NOT EXISTS:
SELECT *
FROM  (VALUES ('id1'), ('id2'), ('id3')) AS v(id)
WHERE  NOT EXISTS (SELECT FROM content WHERE json_data ->> 'elementId' = v.id);

Or if you prefer a join:
SELECT v.id
FROM  (VALUES ('id1'), ('id2'), ('id3')) AS v(id)
LEFT   JOIN content c ON c.json_data ->> 'elementId' = v.id
WHERE  c.json_data IS NULL -- or use the PK column

Either is an "anti-join", technically; and both will probably result in the same query plan.
See:

Select rows which are not present in other table

Consider upgrading to a current version of Postgres. 9.4 has reached EOL in Feb 2020.
Index
But even Postgres 9.4 already supports jsonb which (unlike json) allows a GIN index to support your query. See:

How to get particular object from jsonb array in PostgreSQL?
What's the proper index for querying structures in arrays in Postgres jsonb?

Or if you are focused on this query exclusively, a plain btree on an expression should be the optimum:
CREATE INDEX ON content ((json_data ->> 'elementId'));

Related:

PostgreSQL operator uses index but underlying function does not


SQL Server and ASP.NET - Backup Log database with Application Pool in Always Running

Can I use a shrink to reduce log size? Even if users are using websites?

No, users will be blocked while the shrink operation is occurring.  Plus, you'll have to do it over and over again unless you address the root cause of the growth.

For a really thorough treatment of why the log file keeps growing, see Why Does the Transaction Log Keep Growing or Run Out of Space?

When your websites are always running, you're producing more log records than the size of the log file in a single day, resulting in the file growth.
You have a few options:

Leave the database in the SIMPLE recovery model

With this approach, you don't take log backups at all.  You take the full backup once per day, as you always do.  If there were a crash or some other need to recover from a backup, you could lose up to one day of data.
ALTER DATABASE [DatiMachinaWeb]
SET RECOVERY SIMPLE;

Take log backups more often

You can take as many log backups as you like between full backups.  Taking a log backup will free up space in the log (referred to as "truncation") and prevent the log file from growing.  This is really the best, and most common, option for dealing with log file growth.

Permanently increase the log file size to accommodate the once-per-day log backups

The default log file size in SQL Server 2019 is 8 MB.  If your system does more than 8 MB worth of transactions in a day, that log file will grow (by default, it will grow by 64 MB).
Assuming the amount of transactions your system does is somewhat consistent from day to day, the growth of the log file will eventually normalize.  After the first growth event, the log will be 72 MB - if you don't do more than 72 MB of transactions in a day, the log file should stay that size.

Lower likelihood: investigate long-running transactions

It's possible that your websites are causing the log file to not be truncated when they are in this "always running" mode.  Maybe they start a transaction on startup, and don't commit or rollback until they shut down.
If the log file continues to grow, even with frequent log backups or a sufficiently large log file, you can run this query to see what is preventing log file truncation:
SELECT
    d.[name],
    d.log_reuse_wait,
    d.log_reuse_wait_desc
FROM sys.databases d
WHERE
    d.[name] = 'DatiMachinaWeb';


Disabling sql services while performing maintenance on SQL Server 2008 R2 cluster

Disabling the service will not impact the cluster configuration. I would disable the node in possible owners as well to make sure that the server will not try to failover.


Yes, Postgres allows settings per user or even per database and user, including statement_timeout:
ALTER ROLE foo SET statement_timeout = 12345;  -- milliseconds

Related:

How does the search_path influence identifier resolution and the “current schema”

To see the currently active setting for the session:
SHOW statement_timeout;

Or get more details from the view pg_settings (including how it was set):
SELECT *
FROM   pg_settings
WHERE  name = 'statement_timeout';

To check current settings for a role:
SELECT rolname, rolconfig
FROM   pg_roles
WHERE  rolname = 'foo';

rolconfig is an array, unnest it to get one setting per row:
SELECT rolname, unnest(rolconfig) AS setting
FROM   pg_roles
WHERE  rolname = 'foo';


Measuring DB time of application using SQL Server Profiler

On server side trace DURATION is the total execution time (in ms) of a query. CPU column will provide you time on CPU (total of one or multiple CPUs). While DURATION - CPU = IO Time

So If RPC-Completed time for single procedure call or total time is according to benchmarks then problem could be on network side.

Where is actual problem. To get this you must keep close eye on Wait Statistics. Which will help you to detect real problem whether its on database side or network.

Note:
1-In case of parallelism (a query resolved by multiple CPUs) DURATION could be less then CPU.
2-Profiler will show you DURATION in milliseconds while inserting same trace data in a table, you will get DURATION  in microseconds.


The equivalent of Common Table Expressions (CTEs) in MySQL

In this particular case the CTE could be replaced with a normal subselect (a derived table):

SELECT
  tbl_notifications.module_name,
  tbl_notifications.message,
  tbl_notifications.staff_username,
  tbl_notifications.date_time
FROM
  (
    SELECT
      tbl_modules.module_code
    FROM
      tbl_student
      INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
    WHERE
      tbl_student.student_number = '1002'
  ) AS CTE
  INNER JOIN tbl_notifications ON cte.module_code = tbl_notifications.module_code
ORDER BY
  tbl_notifications.date_time DESC
;

And that query would work both in SQL Server and MySQL (in any major SQL product, in fact).

Moreover, nesting is unnecessary here at all, and so you could have the same result with the following:

SELECT
  tbl_notifications.module_name,
  tbl_notifications.message,
  tbl_notifications.staff_username,
  tbl_notifications.date_time
FROM
  tbl_student
  INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
  INNER JOIN tbl_notifications ON tbl_modules.module_code = tbl_notifications.module_code
WHERE
  tbl_student.student_number = '1002'
ORDER BY
  tbl_notifications.date_time DESC
;

And by the way, please consider using short table aliases in your queries. Here is what the same query looks like with short aliases:

SELECT
  n.module_name,
  n.message,
  n.staff_username,
  n.date_time
FROM
  tbl_student AS s
  INNER JOIN tbl_modules AS m ON s.registration_year = m.registration_year
  INNER JOIN tbl_notifications AS n ON m.module_code = n.module_code
WHERE
  s.student_number = '1002'
ORDER BY
  n.date_time DESC
;

It may be subjective sometimes, but I do believe that in this case readability improves greatly.


Nothing to do, no schemas or tables selected on export

Uninstall workbench from system and install it back.
Problem mainly occurs with the some where file distribution might be corrupted.