Assuming you can find a way for your monitoring software to use an actual account, and further assuming your list of trusted/non-audited users is more static, then using the "exclude user" options should mean less maintenance.

To exclude a list of trusted users from being logged, the MariaDB audit plugin has a server_audit_excl_users option, and similarly the Percona audit log plugin has a audit_log_exclude_accounts.

BTW, regarding rotating users/passwords, you might be interested to know that MariaDB 10.4+ has an expiring password feature.


MySQL cluster error: "Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock"

If you have had a previous version of MySQL server installed, can you run where mysqld in a command prompt to make sure that you're running the MySQL Cluster version of the mysqld binary and not the 'normal' mysqld binary?

Since your management node and SQL nodes both seem to reside on the same machine, you could try changing the config.ini to:

[mysqld]
HostName = 127.0.0.1

instead of

[mysqld]
hostname = host_management

And the SQL Node my.cnf files to:

[mysqld]
ndb-connectstring=127.0.0.1:1186

instead of

[mysqld]
ndb-connectstring=host_management:1186

This might rule out some networking issues.

EDIT: this only works if all hostnames in the config resolve to localhost, meaning you can only use localhost addresses/hostnames in a single server setup.

Also, your management node is showing slots for NodeId's 4 and 5 for the SQL nodes, which doesn't match the definitions in your current config. You could try removing the NodeId configuration from either the SQL Node my.cnf files or the config.ini

You need to perform a rolling restart for the config changes to take effect. As noted by RolandoMySQLDBA, be careful about the --initial

Note: Placing all SQL nodes on the management node machine is not a recommended setup because it creates a single point of failure in your cluster. You could consider placing the SQL nodes on the data node machines.


PostgreSQL is using more memory than I'd expect

If table being updated by your huge UPDATE statement has foreign-key constraints on it, then it will queue up every row updated so that it can validate the constraint at the end of the UPDATE statement.  To maintain this queue, it will use as much memory as it needs to, regardless of the settings of work_mem or anything else.  That is probably where your 43GB is going.  If you don't have enough memory to deal with this, you may need to break up your update into smaller transactions, or drop the constraint and re-add it later.

You are probably over-counting the amount of memory actually used.  The "RES" column reported by top will report the amount shared memory (roughly, shared_buffers, with a little more for locks and other overhead) once under each process which has touched that shared_memory.


MySQL "CREATE TABLE LIKE" include triggers

The long and the short answer is no! This is yet another "non-feature" of MySQL. This construct is known more commonly as CTAS (CREATE TABLE AS.... SELECT) - the CREATE TABLE LIKE syntax is non-standard - like so much of MySQL!

MySQL's documentation argues here - that this lacuna is to ensure "flexibility" - you'd laugh if it wasn't so sad!

  CREATE TABLE ... SELECT does not automatically create any indexes for
  you. This is done intentionally to make the statement as flexible as
  possible.

You can create them (indexes) by declaring them before the SELECT as follows

  If you want to have indexes in the created table, you should specify
  these before the SELECT statement:

> mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

But you're out of luck for TRIGGERs, FUNCTIONs and STORED PROCEDUREs.

If this is important to you, you could try your luck with PostgreSQL - which has a table inheritance mechanism, whereby schema changes are propaged, but I can't see why a WITH INDEXES, WITH FUNCTIONS, WITH STORED PROCEDURES syntax couldn't be introduced.

If you put in a feature request (good luck with that! MySQL doesn't even have CHECK CONSTRAINTs yet), I'd be happy to file a "me-too" if you post the link back here for my attention.

The CREATE TABLE LIKE is a similarly crippled option - from here:

  CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX
  DIRECTORY table options that were specified for the original table, or
  any foreign key definitions.


You are generating logically unreasonable cartesian product and then technically "fixing" it with DISTINCT (If you survive the generated set volume).
Yes, sub-queries is definitly the way to go here.


Can't deploy SSIS package - The certificate chain was issued by an authority that is not trusted

Try this:

Open Windows CMD.EXE and type and enter the following and restart your computer:

netsh winsock reset

Reference:
https://stackoverflow.com/questions/21681762/how-do-i-fix-an-error-connecting-to-sql-server-ssl-provider-error-0-the-mes


The MS documentation on FILESTREAM makes it sound like it should only be used for archiving unstructured data. Am I misunderstanding this?

Your understanding of the feature is correct.

  It just sounds like the FILESTREAM option is more for archiving files (or unstructured data) that once it's saved, should really only be referenced through read operations.

Yes. However, you're welcome to perform updates and deletes, keeping in mind that it can have a negative effect on performance.

For your use case, with small files of around 1 MB in size, you probably won't run into the issue as quickly.

You can also check out FileTable, which exposes an SMB share via FILESTREAM, if you're going to be doing modifications to the images.


In general, there are different canonical covers of a set of functional dependencies, and a canonical cover is called minimal if it has less dependencies of any equivalent cover.

So, for instance, in your example the cover:

A → B
B → C
C → A

is minimal, because it has 3 dependencies and it is not possible to find a cover with less dependencies.

There are several definitions and algorithms of cover. For instance, the Chapter 5 of "The Theory of Relational Databases" of Maier, D. Computer Science Press, 1983, describes several kinds of cover (nonredundant, canonical, minimal, optimal, annular), and different algorithms to find them, but for what I know nobody has given a formal algorithm to find all the possible minimal cover.


Works with single quotes.

[oracle@o71 ~]$ sqlplus u1/u1!@(description=(address=(protocol=tcp)(port=1521)(host=o71.balazs.vm))(connect_data=(service_name=min18_o71)))
-bash: !@: event not found
[oracle@o71 ~]$ sqlplus 'u1/u1!@(description=(address=(protocol=tcp)(port=1521)(host=o71.balazs.vm))(connect_data=(service_name=min18_o71)))'

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 2 09:07:54 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 02 2019 09:07:15 +01:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL>


enforcing function/constant names length in Oracle 18 to be 30 characters

In general it appears that the answer is "no". Allowable object name length is a function of the COMPATIBLE initialization parameter. See here:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-05F1B577-C08C-4DB9-925A-8799C76ADFF4

This parameter is usually intended to ensure your ability to downgrade an Oracle version to a previous release following an unsuccessful upgrade. Once set to a higher value, it cannot be lowered again, as it will have an impact on the data dictionary and other Oracle internal features. See here:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/COMPATIBLE.html#GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/tasks-complete-only-manually-upgrading-oracle-database.html#GUID-C2DE66A9-61F0-41AE-A028-FEAC57ECC0BC


Why do database users with no associated login have access to a database?

It seems obvious I need to remove all of the unnecessary users after
  the databases are copied, but why?

Yes you should remove all the unnecessary users, at least Windows users. And that is why:

For every SQL Server login when creating it, server assings a certain sid(security identifier) that is stored on the server. Every time you map this login to some database, a user with the same sid is created in a database. When you restore this database on another server, unless someone recreates the login with the same sid, it will never reach your database.
Even if the login with the same name already exists on another server, the login's sid is different from user's sid that was restored, so you need to fix this orphan user altering it with corresponding login, to make user's sid equal to corresponding login sid.

The important thing here is that every SQL Server login has only one sid associated with it, and it has access to database only if this sid is also mapped to a database.

Windows logins are different. Server does not create sids for them, it uses Windows access tokens.

When Windows account tries to log on, its sid does not need to be found in sys.server_principals. Instead, any of sids from access token will work as a pass.
Access token contains not only the security identifier (SID) for the user's account but laso SIDs for the groups of which the user is a member.

This means that a situation like yours can be created:

Imagine Windows account acc is a member of 2 Win groups, G1 and G2.

You have 2 servers, S1 and S2 and every group is mapped to only one server:
G1 is a login on S1, G2 is a login only on S2.

You have also a database db1 on S1 where G1 is mapped and has all the permissions on this db1 (let's say G1 is db_owner of db1).

Now you restore db1 on S2.
G1 is still db_owner of db1, but you know that you have no corresponding login on S2, Win group G1 is not mapped to S2, so you think for now you have no db_owners of db1 as the only db_owner was G1 but it cannot reach the server S1.

And now our account acc is logging on server S2.
It can log on to server S2 because it's a member of G2 group. You think that it cannot reach db1 because login G2 is not mapped to db1, but surprise, acc can reach db1 and it is db_owner of db1.

That is because acc is a member of G1. Its token has not only its own SID, it has G1 sid and G2 sid, so G2 sid was used to access a server, and G1 sid was used to access a database.

You can find all the tokens in sys.login_token and sys.database_token, so just impersonate that login (acc):

execute as login = 'dom\acc';
select *
from sys.login_token
where principal_id > 0 -- that is to see only those mapped to server

use db1;
select *
from sys.user_token;


Database design advice on a marksheet schema

There should be questions table that contain all questions with unique ID to each question. A new table reference AnswerSheet table Id and questions id  should be there.


First, sp_BlitzLock only helps you analyze deadlocks, not blocking.

Second, it won't run on 2008R2 -- it's 2012+ only. The deadlock XML definitions are sufficiently different to make supporting both unwieldy.

Third, it already includes the timestamp of the deadlock event, along with other identifying information. I don't think spid would be helpful.


Considerations for Adding Indexes to a SQL Server Database

If you can review the suggested indexes and maybe try adding a few here and there. However, be cautious of over-indexing. Over-indexing can cause SQL Server to get confused on which index to use and that can lead to "DUI’s", that is, it can hamper the performance of Deletes, Updates or Inserts (DUI’s). Also look up the concept of a covering index. A covering index is a single index that "covers" the query and that index can also be reused by other queries, effectively reducing the number of indexes needed.

Watch this excellent free 30 minute video by Kendra Little that covers Indexing in SQL Server:

https://www.youtube.com/watch?v=wAhr91FBlx4


Adding time to an event that runs on a weekly basis

There are three things you should do with this

Change DROP DATABASE to DROP DATABASE IF EXISTS
Add INTERVAL 11 HOUR to the START (as mentioned by @ypercube)
Add ON COMPLETION PRESERVE to make it a repeatable event so you don't have to create it again.

Here are the changes

DELIMITER $$
CREATE EVENT le_drop_database
ON SCHEDULE
  EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY + INTERVAL 11 HOUR DO
ON COMPLETION PRESERVE
BEGIN
  DROP DATABASE IF EXISTS carbon;
END $$
DELIMITER ;

I hope this event is not in the carbon database

If you ever need to disable the event, just run

ALTER EVENT le_drop_database DISABLE;

to reenable

ALTER EVENT le_drop_database ENABLE;

CAVEAT

Please make sure you have this in my.cnf

[mysqld]
event_scheduler=1

You can enable this without restarting mysql by running

mysql> SET GLOBAL event_scheduler = 1;

Give it a Try !!!


Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

One way might be to:

Add a NULLable NVARCHAR column
Using batches, update a number of rows at a time (e.g. 1000 or 10000 rows)
Backup the log, checkpoint, what have you in between batches
When all the rows have been updated, drop the old column and rename the new one
Rebuild indexes

This won't be faster in the long run, and still requires a maintenance window (since you don't want users updating rows you've already updated unless you put a temporary trigger in place to counter that), but it will prevent a huge transaction and after a few updates will give you more predictability about how long it will take.

You could do the same thing by creating a new table, and renaming that once it's done... while this avoids the need for step 5, it would cause even more data churn and can be more problematic due to constraints, foreign keys, triggers etc. that might be involved with the table.


What factors prevent and delay TRUNCATE command?

Other queries. A TRUNCATE statement has to wait until all SELECT statements from that table to complete and as well it must wait until all other transactions that issued INSERT, UPDATE, DELETE, MERGE statements on the table being truncated to commit or rollback. Also other DDL transactions (ALTER TABLE) have to commit or rollback before TRUNCATE can proceed.


There is no "standard" load balancing set up for MS SQL Server that you can run via a wizard.

This would be a database architecture decision and implemented at the database level not the server level. Techniques would be:

Scaling out/federating the database servers
Partitioning
Offload your reporting requirements
Perhaps replication

If anyone disagrees, then I'd like to see an article by a respected known MS SQL figure saying clustering is load balancing. The articles quoted above do not mention load balancing. For example, A Microsoftie (Chas Boyd) says it is not here.

My question to the OP would be what kind of load do you expect?

Database servers are usually IO and memory bound, so proper disk configuration (with appropriate filegroups) and as much RAM as possible will go a lot further than any solution above.

Don't forget: SQL Server 2005/Windows 2003 Enterprise 32-bit goes to 32GB RAM (of which you'd have 26-28GB data cache) and you are not limited by drive letters because of NTFS mount points. As for x64...


Report Server ( [162] Internal request to deactivate schedule ) error ( SQL Server 2008 R2 )

Yes, that's correct, there was bug in earlier version saying

  "If you set up a SQLAgent job to have a recurring weekly schedule, the
  job executes successfully the first day, but may later become disabled
  and the job may not run the next day and thereafter."

But later also, quite a few have reported and it worked for them once they upgraded to SP2+( Sql Server 2008R2), so depends what SP are you running as of now:

Also,

This error may occur if you have a duplicate schedule. Say for the job in question there are various steps and some steps may have the schedule with similar name , even though different timings, but still same name on schedule step also has witnessed the above error. See for any such miss.

Moreover, see here if this helps.


The right URL format when MongoDB authentication is enabled is:

mongodb://[username:password@]host1[:port1][,host2[:port2],...[,hostN[:portN]]][/[database][?options]]

For your case: mongodb://user:pass@localhost:27017/meanhotel?authSource=admin

Reference: MongoClient or how to connect in a new and better way


What is best practice for confirming success of Update queries

You could query before and after looking for the particular conditions you are wanting to update.

For adhoc queries it's a good idea to run the query as a select statement using the same where clause before running as an update statement.

The update should update exactly the same rows select returned unless the data has changed.

create table #test ([id] int identity (1,1), [status] varchar(50) )

insert into #test VALUES ('COMPLETED')
insert into #test VALUES ('FAULTED')
insert into #test VALUES ('COMPLETED')
insert into #test VALUES ('FAULTED')
insert into #test VALUES ('FAULTED')

select status
from #test
where status = 'FAULTED'

select count(*)
from #test
where status = 'FAULTED'

update #test
set status = 'FAILED'
where status = 'FAULTED'

select count(*)
from #test
where status = 'FAULTED'

select status, count(*)
from #test
group by status


In addition to your Full and Incremental/Differential backups you should be doing a backup on the transaction log in a reasonable frequency to mark all locations in the log file with committed entries as available for reuse instead of doing a truncation on the log file.  With a correctly sized log file the transaction log backups will help to keep your log file from growing continuously and might only grow if it a larger volume of changes come in between backups windows then you predicted for.

Now on to log file shrinking.  Shrinking your log file is not necessarily bad it will just take a bit of planning to do it correctly.  (Make sure you are doing this in a maintenance window with a full backup before you begin and if at all possible without any other users connected.) You can use the DBCC shrink command on it to get it down to 1MB but right after you should regrow the log file back to a size that is appropriate for the rate of change that is happening in your database plus a free space buffer to handle larger than normal amounts of data changes.  Also growing the log file in specific consistent increments will create evenly sized VLFs that can help with log performance.  For example after you shrink it down to 1MB you regrow the log file in 1GB increments until you hit 5GB in size.

Kimberly Tripp has a couple of good articles explaining VLFs and transaction log throughput that might be helpful to read over.  Do take note of her warning about a bug in growing the log file by 4GB.

Here is another VLF reference from Brent Ozar that has some more details and also references Kimberly's posts.

It's worth noting, Thanks @BradC, that shrinking and regrowing your log file should only have to be done once or only if your log file grows causing unevenly sized VLFs.  But if you set your auto growth size on the log file to the specific size increments that use used to originally grow it back from 1MB you shouldn't see uneven VLFs even after it auto grows.


"I know its different for every application"

15G is not a large data-set unless you tell me it's a single table! Your data can be contained in innodb-buffer-pool and performance should be better.

Importantly, you should make sure your queries are well written and tables are indexed correctly. (log-queries-not-using-indexes)

I'd start with 1 Sec and review the amount of queries getting logged and work on improvements. long-queyr0time is dynamic and you should be  able to play with it depending on load it generates.


The best thing to do is to export the databases from the MAMP server and import them to your new database server.

Here's a guide with MySQL dump and restore: http://www.patrickpatoray.com/?Page=30


Most likely bloat is taking your disk space. VACUUM FULL requires temporary space to work. It makes a copy of the table without bloat, so for big table it requires a lot of space.
If adding storage is not an option, then perhaps you can try using pgcompact from https://github.com/grayhemp/pgtoolkit - this page has examples of uses.


Handling 'Variable not found' in a tokenised job step

Would using a PowerShell here-string in your script prevent the need for ESCAPE_SQUOTE?

$alert_wmi_state = @'
$(WMI(State))
'@

That should at least let you determine if the problem is with ESCAPE_SQUOTE throwing an error or if it's SQL Agent's token parser itself that's complaining.

As long a the SQL Agent doesn't literally begin with '@ you should be fine.  If it does begin with '@, you can fix that easily:

$alert_wmi_state = @'
_$(WMI(State))
'@.Substring(1)

Note that you typically want to use @'...'@ and not @"..."@ since the latter is still evaluated by PowerShell like a double quoted string.


How to GRANT SELECT by default to B, on tables created by A?

PostgreSQL has default privileges, the perfect tool for your requirement:

ALTER DEFAULT PRIVILEGES FOR ROLE user_a
   GRANT SELECT ON TABLES TO user_b;

Now user_b will automatically get SELECT privileges on all tables created by user_a.


The simplest approach I can think of is to have one fact table for order items, and have two price columns in that fact table: original price, and discounted price. Original price would be the price as listed on the line item. Discounted price would be calculated by totaling the price of all line items on the order, calculating the percent difference between that and the total stated on the order header, then scaling down the prices of the line items using that percentage.


I think this gives you what you want.

My solution uses several common table expression and a variety of Window functions.

There are commented out selects at the bottom of each common table expression that you can use to check individual results along the way.

My first step was to identify the start and end idle times.  I used LAG to check for the previous value of fSpeedKPH and eIgnition ordered by dtUtcDateTime.  If the current values equal the previous values, no change occurred, else a change occurred.  A new column called IsChanged is added to the results.

 ;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
--select * from _BaseData
    ,_BaseDataAndIsChanged  --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
    select * from _BaseDataAndIsChanged

My next step was to identify a grouping that I could use later to sum the IdleSeconds for each group.

;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
--select * from _BaseData
    ,_BaseDataAndIsChanged  --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
--  select * from _BaseDataAndIsChanged
    ,_BaseDataAndGroups     --cte to add groupno column to results
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM _BaseDataAndIsChanged
            WHERE dtUTCDateTime <= t.dtUTCDateTime
            ) groupno
    FROM _BaseDataAndIsChanged t
    )
select * from _BaseDataAndGroups

The last step is to sum IdleSeconds by groups and find the IdleStartTime and IdleEndTime This is also the final query.

;WITH _BaseData
AS (
    SELECT iAssetId
        ,fSpeedKPH
        ,dtUtcDateTime
        ,eIgnition
        ,IdleSeconds = (
            CASE
                WHEN eIgnition = 1
                    AND fSpeedKPH = 0
                    THEN DateDiffSeconds
                ELSE 0
                END
            )
    FROM VehicleMonitoringLog
    )
    --select * from _BaseData
    ,_BaseDataAndIsChanged --cte to add IsChanged column to results
AS (
    SELECT *
        ,CASE
            WHEN (
                    lag(fSpeedKPH, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = fSpeedKPH
                AND (
                    lag(eIgnition, 1, '') OVER (
                        ORDER BY dtUtcDateTime
                        )
                    ) = eIgnition
                THEN 0
            ELSE 1
            END ischange
    FROM _BaseData
    )
    --  select * from _BaseDataAndIsChanged
    ,_BaseDataAndGroups --cte to add groupno column to results
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM _BaseDataAndIsChanged
            WHERE dtUTCDateTime <= t.dtUTCDateTime
            ) groupno
    FROM _BaseDataAndIsChanged t
    )
    --select * from _BaseDataAndGroups
    ,BaseDataAndSums --cte to add SumIdleSeconds to results
AS (
    SELECT *
        ,sum(IdleSeconds) OVER (PARTITION BY groupno) AS IdleDurationSeconds
        ,FIRST_VALUE(dtUtcDateTime) OVER (
            PARTITION BY groupno ORDER BY dtUtcDateTime
            ) AS StartIdleTime
        ,LAST_VALUE(dtUtcDateTime) OVER (
            PARTITION BY groupno ORDER BY dtUtcDateTime RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
            ) AS EndIdleTime
    FROM _BaseDataAndGroups
    )
SELECT iAssetId
    ,StartIdleTime
    ,EndIdleTime
    ,IdleDurationSeconds
FROM BaseDataAndSums
WHERE ischange = 1
    AND IdleDurationSeconds > 0

| iAssetId | StartIdleTime           | EndIdleTime             | IdleDurationSeconds |
|----------|-------------------------|-------------------------|---------------------|
| 2        | 2018-09-08 00:04:51.000 | 2018-09-08 00:36:51.000 | 1986                |
| 2        | 2018-09-08 00:58:28.000 | 2018-09-08 03:50:29.000 | 10432               |


How to decompose this relation into 3NF relations?

First of all, note that the original relation is already in Third Normal Form, since each attribute is prime (each attribute is a key, actually), so that the definition of 3NF is respected.

Then, note that the algorithm is incomplete. The steps are:

  Find a minimal basis of F, say G
  For each groups of FD with the same left part, X → A1, X → A2, ..., X → An in G, use {X, A1, A2, ..., An} as the schema of one of the relations in the decomposition
  Delete all the relations whose attributes are contained in another relation.
  If none of the sets of relations from Step2 is a superkey for R, add another relation whose schema is a key for R.

So in the first case, you obtain three groups of dependencies:

A → B
B → A
B → C
C → B

that produce three relations, R1(A, B), R2(A, B, C), R3(B, C), and, following the algorithm, you obtain as result only R2, since the other two have attributes contained in them.

So you have two different outputs from the algorithm, depending on the minimal base used (which in turn depends on the order in which you consider the dependencies when calculating the minimal cover).

So, the answer to your question:

  which one is correct?

is: both of them are correct, since both of them satisfies the definition of the 3NF. You have simply discovered that the synthesis algorithm for decomposing a relation in 3NF can produce different solutions.

A different question is: which is “better”, and of course the solution with a single relation is “better”, since you do not need to join tables when making queries.

Of course, if one could check at the beginning if the relation is already in 3NF, than he can avoid to apply the algorithm. But this in general cannot be done, since the check requires the exponential computation of all the keys, to find the prime attributes of the relation.


Availability Groups Failover Readinness "No data loss" and rollback of nonqualified transactions

This doesn't count as data loss in this context.

From the documentation (emphasis mine):

  A manual failover causes a synchronized secondary replica to
  transition to the primary role after a database administrator issues a
  manual-failover command on the server instance that hosts the target
  secondary replica. To support manual failover, the secondary replica
  and the current primary replica must both be configured for
  synchronous-commit mode, if any. Every secondary database on the
  availability replica must be joined to the availability group and
  synchronized with its corresponding primary database (that is, the
  secondary replica must be synchronized). This guarantees that every
  transaction that was committed on a former primary database has also
  been committed on the new primary database. Therefore, the new primary
  databases are identical to the old primary databases.

Any transaction in progress while the failover occurs is going to be rolled back and needs to be handled with re-try logic on the application side. You can't have a transaction start on one node, trigger a failover, and then have it finish running on a secondary node - though that would be pretty cool, admittedly.

The synchronous replication just guarantees both nodes are in the same state before/after the failover, otherwise the former primary node could have writes that the now-primary node does not, causing inconsistent data betwixt the two.

Check out the very good documentation for more in-depth information about what really goes on, and what you're actually getting out of the AG feature set.


Partition Function with only one value, what does this effect?

You are correct in your thinking of the boundary values.  Basically what your partition function dictates, is that anything greater than or equal to (range right) 0001-01-01T00:00:00.000 will be on the second partition, and anything less than that will be on the first partition.

The peculiar thing about the partition function is that there will never be any value less than 0001-01-01T00:00:00.000, due to the datetime2 date range.  As far as I can see, this would mean that the first partition will never have data, and the second partition will have all of the data (provided there is no split going forward).

One thing this strategy could be used for is partition switching and sliding window operations.  For more information on exactly what that is you can see it on this blog article.  But as for this isolated partitioning structure as a standalone solution, I'm not convinced that there's anything gained here.


Pentaho Data Integration: 'Execute SQL statement' to create trigger

You can omit the DELIMITER commands and enable the option "Send SQL as single statement":

CREATE FUNCTION Tes()
BEGIN
  SELECT * FROM actors;
END


How to speed up execution time in a SQL Server 2016 database which has restored from 2008 R2?

have restored the backup of the database from SQL V-2008R2 to SQL version 2016 and now facing the performance issue.

Tossing away considerations like hardware,

Are your configuration settings set?

Is your max memory setting high enough? Is your Power plan set to high performance?

The database should be in the same state as it was on the SQL 2008 R2 Instance when the backup occured.
You could always update your statistics to get some better query plans.

  How can the issue be solved without changing database Compatibility

If you can set your compatibility level to 110 (2012) then you could enable traceflag 2312 to get the sql 2014 Cardinality estimator
(this can improve but also degrade the performance of queries).
Traceflag 4199 might also help.

It is hard to help without more information.


SQL Server 2012 Transparent Data Encryption and indexes

Yes. Transparent Data Encryption doesn't change any of the internal processing of queries.  Database pages are transparently encrypted/decrypted during I/O (i.e. when they are read from or written to disk). In-memory query processing is therefore unaffected, so indexes continue to function exactly as they did without TDE.

TDE is designed to negate the need for application developers to make any changes to their applications. http://msdn.microsoft.com/en-us/library/bb934049.aspx contains a salient piece of information:

  This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

In short, queries operate no differently with TDE enabled or disabled, aside from the performance impact of the encryption and decryption activities.


What storage engine should I use for this MySQL table?

When it comes to InnoDB, you should never use big primary keys. Why?

InnoDB has an internal Clustered Index called gen_clust_index. When you are doing INSERTs into an InnoDB table with just a Primary Key, you are building up the gen_clust_index. I would expect this table to bloat very quickly. Your goal should be to record the URL and index it in such a way that the primary is not large and causing potentially make for slow INSERTs. Given the size of the field, I wouldn't even create a secondary index

This table could easily be a MyISAM table. However, in a high-traffic environment, each INSERT into a MyISAM table would produce a full table lock. That's an automatic bottleneck just to record a URL. So, MyISAM is out. Making the table InnoDB allows a high-traffic environment the freedom to INSERT without locking.

I would like to suggest the following approach:

CREATE TABLE url_trail
(
    id int not null auto_increment,
    url_md5 VARCHAR(32),
    url VARCHAR(255),
    PRIMARY KEY (id),
    KEY url_md5 (url_md5),
    KEY url (url (50))
) ENGINE=InnoDB;

Using this type of layout will allow you to index the MD5 o the URL (32 btyes) rather than the 200+ character URL. There is also an optional index on the first 50 characters of the URL. You could use either index or both.

You will have to do INSERTs like this:

INSERT INTO url_trail (url,url_md5) VALUES ('www.yahoo.com',MD5('www.yahoo.com'));

You will have to do searches like this:

SELECT * FROM url_trail WHERE url_md5 = MD5('www.yahoo.com');
SELECT * FROM url_trail WHERE url = 'www.yahoo.com';

The essential thing overall is to not to have bloated primary keys.


How to use COLUMNS_UPDATED to check if any of certain columns are updated?

You can use CHECKSUM() as a fairly simple methodology for comparing actual values to see if they were changed.  CHECKSUM() will generate a checksum across a list of passed-in values, of which the number and type are indeterminate.  Beware, there is a small chance comparing checksums like this will result in false negatives.  If you cannot deal with that, you can use HASHBYTES instead1.

The example below uses an AFTER UPDATE trigger to retain a history of modifications made to the TriggerTest table only if either of the values in the Data1 or Data2 columns change.  If Data3 changes, no action is taken.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    INSERT INTO TriggerResult
    (
        TriggerTestID
        , Data1OldVal
        , Data1NewVal
        , Data2OldVal
        , Data2NewVal
    )
    SELECT d.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i
        LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
    WHERE CHECKSUM(i.Data1, i.Data2) <> CHECKSUM(d.Data1, d.Data2);
END
GO

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

If you're insistent on using the COLUMNS_UPDATED() function, you should not hard-code the ordinal value of the columns in question, since the table definition may change, which may invalidate hard-coded value(s).  You can calculate what the value should be at runtime using the system tables.  Be aware that the COLUMNS_UPDATED() function returns true for the given column bit if the column is modified in ANY row affected by the UPDATE TABLE statement.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    DECLARE @ColumnOrdinalTotal INT = 0;

    SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal
        + POWER (
                2
                , COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
            )
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE s.name = 'dbo'
        AND t.name = 'TriggerTest'
        AND c.name IN (
            'Data1'
            , 'Data2'
        );

    IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
    BEGIN
        INSERT INTO TriggerResult
        (
            TriggerTestID
            , Data1OldVal
            , Data1NewVal
            , Data2OldVal
            , Data2NewVal
        )
        SELECT d.TriggerTestID
            , d.Data1
            , i.Data1
            , d.Data2
            , i.Data2
        FROM inserted i
            LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID;
    END
END
GO

--this won't result in rows being inserted into the history table
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

SELECT *
FROM dbo.TriggerResult;

--this will insert rows into the history table
UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

--this WON'T insert rows into the history table
UPDATE dbo.TriggerTest
SET Data3 = GETDATE()
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

--this will insert rows into the history table, even though only
--one of the columns was updated
UPDATE dbo.TriggerTest
SET Data1 = 'blum'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

This demo inserts rows into the history table that perhaps should not be inserted.  The rows have had their Data1 column updated for some rows, and have had the Data3 column updated for some rows.  Since this is a single statement, all rows are processed by a single pass through the trigger.  Since some rows have Data1 updated, which is part of the COLUMNS_UPDATED() comparison, all rows seen by the trigger are inserted into the TriggerHistory table.  If this is "incorrect" for your scenario, you may need to handle each row separately, using a cursor.

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
SELECT TOP(10) LEFT(o.name, 10)
    , LEFT(o1.name, 10)
    , GETDATE()
FROM sys.objects o
    , sys.objects o1;

UPDATE dbo.TriggerTest
SET Data1 = CASE WHEN TriggerTestID % 6 = 1 THEN Data2 ELSE Data1 END
    , Data3 = CASE WHEN TriggerTestID % 6 = 2 THEN GETDATE() ELSE Data3 END;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

The TriggerResult table now has some potentially misleading rows that look like they don't belong since they show absolutely no changes (to the two columns in that table).  In the 2nd set of rows in the image below, TriggerTestID 7 is the only one that looks like it was modified.  The other rows only had the Data3 column updated; however since the one row in the batch had Data1 updated, all rows are inserted in the TriggerResult table.

Alternately, as @AaronBertrand and @srutzky pointed out, you can perform an comparison of the actual data in the inserted and deleted virtual tables.  Since the structure of both tables is identical, you can use an EXCEPT clause in the trigger to capture rows where the precise columns you are interested in have changed:

IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    ;WITH src AS
    (
        SELECT d.TriggerTestID
            , d.Data1
            , d.Data2
        FROM deleted d
        EXCEPT
        SELECT i.TriggerTestID
            , i.Data1
            , i.Data2
        FROM inserted i
    )
    INSERT INTO dbo.TriggerResult
    (
        TriggerTestID,
        Data1OldVal,
        Data1NewVal,
        Data2OldVal,
        Data2NewVal
    )
    SELECT i.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i
        INNER JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
END
GO

1 - see https://stackoverflow.com/questions/297960/hash-collision-what-are-the-chances for a discsussion of the vanishingly small chance that the HASHBYTES calculation may also result in collisions.  Preshing has a decent analysis of this problem as well.


Not able to produce table based on conditional select

You're probably looking at needing an OUTER JOIN instead of the implied INNER JOINs that you're using. e.g. suppose

   Table 1 Employee (Empl_number, Empl_Name)
   001, Paul
   002, John
   003, Fred

   Table 2 EmployeeRoles (Role_ID, Role_Desc)
   100, Manager
   200, Clerk
   300, KeyHolder

   Table 3 RoleLink (ID, Empl_number, Role_ID
   1, 001, 100
   2, 001, 300
   3, 002, 200

Then to get a list of all employees and their roles you would use

   SELECT e.empl_number, e.empl_name, ISNULL(r.role_desc, 'No role')
   FROM EMPLOYEE e
   LEFT OUTER JOIN ROLELINK l on e.empl_number = l.empl_number
   LEFT OUTER JOIN EMPLOYEEROLES r on l.role_id = r.role_id

Would give

   001, Paul, Manager
   001, Paul, Keyholder
   002, John, No role
   003, Fred, Clerk


How, if possible, can an .sql file be executed against an oracle database from a bash script

In a shell script:

#!/bin/bash

sqlplus user/pass@server/DATABASE<<THEEND

-- Change "1" to the desired fatal return code

whenever sqlerror exit 1;

@yoursqlscript.sql

quit;

THEEND

Or you can just run:

sqlplus user/pass@server/DATABASE @yoursqlscript

... and put the whenever sqlerror exit 1; at the top of your .sql script(s).


Selecting next month first Friday when current month last day not equal to Friday

Oracle fiddle is not available now... :(

In MySQL it can be:

WITH
cte1 AS (      SELECT 0 num
         UNION SELECT 1
         UNION SELECT 2
         UNION SELECT 3
         UNION SELECT 4
         UNION SELECT 5
         UNION SELECT 6),
cte2 AS (SELECT (@firstday:=CAST(CONCAT(@year:=2019, '-', @period:=5, '-1') AS DATE)) + INTERVAL cte1.num * 7 DAY somedate
         FROM cte1)
SELECT somedate - INTERVAL 3 + WEEKDAY(somedate) DAY friday
FROM cte2
HAVING friday BETWEEN @firstday
                  AND @firstday + INTERVAL 1 MONTH + INTERVAL 5 DAY;

@year and @period emulates the values from source table.

@firstday is used to decrease the amount of calculations.

PS. You may face a problem Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'between' in MySQL... if so, replace BETWEEN @firstday with BETWEEN @firstday + INTERVAL 1 DAY - INTERVAL 1 DAY in outer query. fiddle. Or calculate first day of month in outer query instead of @firstday variable usage.


I know that Mongo can autogenerate a random ID for each element in a collection and I also know that you can create a hashed index then to increase the speed search.

Hashed indexes in MongoDB are typically used to create a suitable shard key for data distribution where the original field value is monotonically increasing (for example, default ObjectIDs). The underlying index format is still a B-Tree, so a hashed index does not change the speed or complexity to look up an index value.

  I'll use the document SHA1 hash (or maybe other) as identifier in the web to each document, and I was wondering if it could be a good idea to use it as custom index

ObjectIDs are pseudorandom identifiers designed to be independently generated and reasonably unique with roughly ascending order (based on a leading timestamp component); SHA1 hashes require unique input values and are unordered. ObjectIDs are generated by clients/drivers by default.

Using a custom _id format is fine, but you should consider desired attributes like uniqueness (required for a primary key) as well as ordering (depends on your use case) and independent generation (required for effective scaling of a distributed deployment).

  It's a faster way to find documents, because you can search by the ; hash and not by whole document.

As mentioned above, this is not a faster search solution. If you already know the primary key (_id value), looking it up as a hash value or as an ObjectID is equivalent.

  The ID it's already an index so you will save one index.

True.

  You may save some storage because you don't need to save the hash separately.

_id is already a unique reference, so you wouldn't necessarily need another unique value.

  The SHA1 size is smaller than default Mongo id, so you will be able to have more index in memory at same time.

A normal SHA-1 hash is 20 bytes, which is 8 bytes more than an ObjectID. Perhaps you are thinking of the truncated SHA-1 hashes used by git (typically around 7 bytes). I'm not sure what the relative probability of collisions would be, but smaller key spaces increase the possibility.

There are also factors to consider other than the size of the _id value. Indexes in MongoDB use prefix compression by default, which is helpful when you have a values like ObjectIDs which start with 4 byte timestamps. Indexes with random values may end up creating larger data structures.

For an example comparison of index prefix compression, this older blog post from John Page might be useful: How to reduce your MongoDB hosting costs.

The best way to test the impact of data model changes would be to generate some representative test data for your planned approach. There are mock data tools that can help with creation of plausible data (for example, mgeneratejs).


MySQL DB dynamic ranking or "static" separately saved

The dynamic rank would seem more fluid.

As long as you index the rank field, updating the points gives the rank index a workout and gets the rank dynamically sorted in a logical manner at the same time.

The execution of the query  would be

query parsing
query optimization
rank index scan (if rank is indexed)
limit on result set determined by value of dynamic variable

Updating points and then running a single select would execute the query like this

query parsing
query optimization
points index scan (if points is indexed)
limit on result set determined by the compiled query

Sorting ranks vs sorting points could be just a matter of preference.

However, there is something to consider. If you rank by top 10,000 or 20,000, rank should be a SMALLINT. If the points is also SMALLINT (< 65536 or 64K), then either index/query scenario is fine. However, if points can go beyond 64K and need INT, then go with indexing the rank and dynamically assign the rank.

I would also store rank and point together in the same table to avoid the need to do JOINs. It already has a full plate doing sorts via the index on the points/rank.


Permanently delete SQL server database - anti recovery

There are several ways the database could be recovered:

Database Backups. You need to make sure you're removing all database backups as well by shifting them onto on-premises storage and securely deleting on the rented server.
Server Backups. Your provider is possibly providing server-level backups. These would have older copies of the databases on them, so would need to be securely deleted.
Data Recovery on the underlying storage. The deleted files on physical storage may still be recoverable, so you need to ensure that the storage is being securely deleted. This usually involves zeroing the disks multiple times to securely delete the data.

You should check with your provider regarding guarantees for secure data deletion when you exit the service to see if that meets your requirements.


How to add ansi null and ansi warning in connection string

You can't. They are set per connection, but not in the connection string. So run some SQL after creating the connections

SET ANSI_NULLS, ANSI_WARNINGS ON;

Note though, the error you have is caused by a linked server call from your SQL Server. Depending on how you set this up (say via a stored procedure) you may need to change the stored proc or view, or set the values in the code. Hard to say as it stands.


Secondary data file is in 'Recovery Pending' state, but database is online

Since you mentioned earlier that your DB gets restored daily and it has different filegroups, it seems to me the case of piecemeal restore. You need to check the script used for restore and make sure it is recovering all the filegroups present in the DB backup.

It looks like currently the script is only recovering primary filegroup and skipping the others which is causing the DB to recover but without the skipped filegroups. So you can query all your data which is present in PRIMARY filegroup but not the skipped ones. Just recover all the filegroups using the example given in below link and you should be fine.

http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/sql-server-filegroup-piecemeal-restores-1/


(Note: this isn't a 100% certain answer, and I don't have references, so let me know if you can prove otherwise.)

The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. That simply wouldn't work if "old" versions within one database were cleared out in the middle of another database's snapshot transaction.

So, if there's a very old open transaction, the version store will not be able to clear until that transaction either commits or rolls back.


Is a join optimized to a where clause at runtime?

Those will usually collapse to the same thing internally. The former is the one you should always write, IMHO. More importantly, why does it matter? They're identical in terms of execution plan and performance (assuming you don't mess it up, which is easier to do with the lazy, old-style syntax).

Here is proof using AdventureWorks that there is no CROSS JOIN and filter going on.

The explicit join:

The implicit join:

Look, ma! Identical plans, identical results, no cross joins or filters seen anywhere.

(For clarity, the warning on the SELECT operator in both cases is a cardinality-affecting implicit convert, nothing to do with the join in either case.)


Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?

Actually, my answer is in the Documentation:
https://mariadb.com/kb/en/unicode/

UTF-8 encoding using one to three bytes per character. Basic Latin
letters, numbers and punctuation use one byte. European and Middle
East letters mostly fit into 2 bytes. Korean, Chinese, and Japanese
ideographs use 3-bytes. No supplementary characters are stored.
utf8mb4   -- Same as utf8, but stores supplementary characters in four
bytes.

Also, as per https://mariadb.com/kb/en/data-type-storage-requirements/

String Data Types
In the descriptions below, M is the declared column length (in characters or in bytes), while len is the actual length in bytes of the value.
VARCHAR(M) -- len + 1 bytes if column is 0 – 255 bytes, len + 2 bytes if column may require more than 255 bytes

So, for VARCHAR(80) utf8mb4,
80x4 > 255, so a field with "aaaa" will take 6 bytes (len + 2)


It's best not to use the name of a "type" as an id - it is better to use generated id's and have a key called "type"  in each document where you have a string value of "Home" etc


how to avoid the "An INSERT EXEC statement cannot be nested" exception on this situation?

The source code for Aaron's sp_foreachdb contains the following line:

INSERT #x EXEC sp_executesql @sql;

According to your error message:

  An INSERT EXEC statement cannot be nested.

So code like that below will not be valid as it is nesting INSERT xxx EXEC xxx code.

INSERT @temp
exec sp_msforeachdb  @command1='use ?; Exec sp_helpfile;'


Should I use an int column to represent date?

Shorter answer to the title of the question: No. Why would you want to lose the ability to treat a date like a date. So important for sorting, date functions, etc.

A few thoughts to get you started at least, not sure which DBMS while answering, answering based on my experience with SQL Server:

1.) GUID as a Primary Key is generally not a great idea. Not in SQL Server especially. What's wrong with an Integer primary key? Yes whatever your Primary Key is becomes your Foreign Key in the child table(s) so that is large and probably not necessary.

2.) I would use a regular datetime column. Performance wise, if you are well indexed, you shouldn't notice a difference here. And a datetime column is more versatile as, well, a date. You can ask questions of it that you can't ask as easily of an INT column with built in datetime functions. If you don't need time and you are on a DBMS or version that has just the DATE you can use that data type.

3.) Yes. Especially if CustomerID is a foreign key to a Customers table. Good to index. Whether or not you need to make an index on CustomerID AND the Date column depends on how the queries will typically look. If you are often querying joining to customer and specifying a date range you may find it beneficial to have the date. You may find it beneficial to include some other columns to cover other queries as parts of the key or included columns. It really depends on your queries and data, though.

As far as clustering on the date column. That's a hard one. If this were a fact table in a warehouse and every single query was always on a date range, then there are some benefits there. If this is an operational invoice table, I imagine your app also joining into invoices in other approaches also. I also imagine invoices being queried by invoice IDs stored in other tables, etc. So I don't believe there is enough to determine clustered key. I'm of the school that prefers a simple surrogate key for OLTP tables. An InvoiceID INT (or BIGINT if you really would blow out an INT) setup as an identity column so it is always increasing and avoiding page splits. But I don't know if there is a definite wrong answer here (well there are many, but you haven't proposed any of those)


Model with variable number of "properties" of different types.

hstore is ideal for this job, and isn't in any way experimental; it's been a part of PostgreSQL since 8.3 and is way easier to use in 9.1 and above thanks to CREATE EXTENSION.

The only real issue with hstore is that not all client drivers support converting hstore values to native maps or dictionaries, so you might need to get the properties for an entry as a table using the hstore sql function each, effectively converting the hstore to subtable result.

The only real alternatives I see are:

A simple master-detail table in EAV style. That'll work fine, it's just a bit annoying to query, you'll be using a lot of string_agg or array_agg calls and it's lots slower than hstore. This approach is completely portable across database systems.
Storing the properties as xml, which can be easier to parse from some applications and is sort-of indexable using functional indexes on xpath expressions. This approach is somewhat portable across database systems, in that it should work with any SQL/XML database.
Storing the properties as json, which is easy to consume from most applications but is currently non-indexable and pretty opaque to SQL at the moment (expect improvements in future Pg versions). This approach is somewhat PostgreSQL-specific, but you can use a text/memo/clob field to store json in any database.

As for re-using properties that're already defined, that'a a simple matter of maintaining a table of known property names. If you want you can generate it on the fly using SELECT DISTINCT skeys(properties) FROM thetable; queries on the properties hstores, but that'll be quite slow so I'd recommend maintaining a separate table.


Postgres: Explicit lock with transactions

In postgres, do transaction also ensures locking of rows(preferable)/tables until it is completed?

No, transactions don't ensure locking. Transactions ensure only that whatever is transacting is done atomically. That is to say, that it either all succeeds, or it all fails. How it does what it does, and what it is doing it to is totally controlled by you.

For instance, PostgreSQL currently supports three isolation levels,

Read committed
Repeatable read
Serializable

These three isolation levels either see and operate can see the data directly, or a snapshot of the data at the time transaction starts.

Only in the last mode serializable is any attempt to be made that the transaction commits in a serialized fashion. Whether or not enough of an attempt is made is another question.

All locking in PostgreSQL can be done explicitly or implicitly (the DDL command know how to lock efficiently to get the job done). Usually, explicit locks are shunned. But in same cases, including under serializable, it's the only way to be sure data has not been added since the transaction started.


-skipErrors in Transactional Replication not happening

You don't see the "skipped # rows" in the message window?
Can you maybe post the window screenshot?

Like this blog here (it did what you did, skipped 20598:547), restarted agent
https://www.percyreyes.com/2016/07/the-skiperrors-parameter-for.html


Determine the PackageFormatVersion for multiple .DTSX packages files in a folder

Retrieve dtsx info programmatically

You can read my detailed answer on StackoverFlow:

Automate Version number Retrieval from Dtsx files

Demo App

I Created A Demo Application to achieve this procedure you can download it from the following link:

Dropbox download link

Also i created a new Git-repository for this demo app

App screenshot

Retrieve dtsx PackageFormatVersion using TSQL

I wrote an SQL query that get files from a specific directory, filter on *.dtsx file then read PackageFormatVersion property from them.

--Result Table
CREATE TABLE #TblResult (filepath varchar(MAX) , packageformatversion INT)

--Get Files From Directory

declare @files table (FileName nvarchar(4000))

--Get files fullpath
declare @myPath nvarchar(4000) =  'C:\Users\Admin\Desktop\Stack Overflow';

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
   ,subdirectory nvarchar(512)
   ,depth int
   ,isfile bit
   , ParentDirectory int
   ,flag tinyint default(0));

-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
   VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
   EXEC master.sys.xp_dirtree @myPath,0,1;

UPDATE #DirectoryTree
   SET ParentDirectory = (
      SELECT MAX(Id) FROM #DirectoryTree
      WHERE Depth = d.Depth - 1 AND Id < d.Id   )
FROM #DirectoryTree d;

-- SEE all with full paths
WITH dirs AS (
    SELECT
       Id,subdirectory,depth,isfile,ParentDirectory,flag
       , CAST (null AS NVARCHAR(MAX)) AS container
       , CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
       FROM #DirectoryTree
       WHERE ParentDirectory IS NULL
    UNION ALL
    SELECT
       d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
       , dpath as container
       , dpath +'\'+d.[subdirectory]
    FROM #DirectoryTree AS d
    INNER JOIN dirs ON  d.ParentDirectory = dirs.id
)
insert into @files (filename)
SELECT dpath FROM dirs
WHERE subdirectory like '%.dtsx'

DECLARE @file NVARCHAR(4000)

--Loop over dtsx files

DECLARE csr CURSOR FOR SELECT [FileName] FROM @files

OPEN csr

FETCH NEXT FROM csr INTO @file

WHILE @@fetch_status <> - 1
BEGIN

--Read xml from dtsx file

CREATE TABLE #TblTemp (data varchar(MAX));

DECLARE @strQuery NVARCHAR(4000)

SET @strQuery = 'BULK INSERT #TblTemp
   FROM ''' + @file + '''
   WITH
      (
         ROWTERMINATOR = ''''
      )'

EXEC(@strQuery)

--Get PackageFormatVersion

INSERT INTO #TblResult (filepath, packageformatversion)
SELECT @file, SUBSTRING(data
                 ,CHARINDEX('DTS:Name="PackageFormatVersion">',data,1) + LEN('DTS:Name="PackageFormatVersion">')
                 ,CHARINDEX('<',SUBSTRING(data,CHARINDEX('DTS:Name="PackageFormatVersion">',data,1) + LEN('DTS:Name="PackageFormatVersion">'), 3) ,1) - 1)

FROM #TblTemp

DROP TABLE #TblTemp

FETCH NEXT FROM csr INTO @file

END

CLOSE csr
DEALLOCATE csr

--Read Result

SELECT DISTINCT * FROM #TblResult

--Drop temp Table

DROP TABLE #TblResult

Result looks like

References

Retrieve files with full path in sql server


Why is it omitted?

Because the replication NOT considered to be HA and DR solution, it is considered to be Migrate & Load Data feature within SQL Server, which mainly cover the requirement of distributing subset of data into different locations wherein HA and DR solution mainly focuses on whole database/SQL-Instance level Failover capabilities.

Will Replication be deprecated soon?

I don't think whole replication feature will be depreciated, perhaps some sub-features of replication. for your reference..


Seeking help with Extended events behaviour

sp_statement_completed is doing exactly what you are asking it to : fire for every statement in a procedure.

You likely want to change that to sqlserver.module_end - but some other things in your session don't seem to match what you're after anyway, like transaction_id, when would instance name ever be different, and capturing both database_id and database_name is kind of redundant (unless you are dropping and re-creating databases constantly, you can derive one from the other, but I would argue that database name is more useful). Perhaps:

CREATE EVENT SESSION [EXEC_SP] ON SERVER
ADD EVENT sqlserver.module_end
(
  SET collect_statement=(0)
  ACTION
  (
    sqlserver.client_hostname,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.server_principal_name,
    sqlserver.session_id,
    sqlserver.sql_text,
    sqlserver.username
  )
  WHERE ( [sqlserver].[is_system]=(0))
)
 ADD TARGET package0.asynchronous_file_target
 (
   SET FILENAME = N'C:\EXEC_SP.xel',
   METADATAFILE = N'C:\EXEC_SP.xem'
 )
 WITH
 (
   MAX_MEMORY = 4096KB,
   EVENT_RETENTION_MODE = NO_EVENT_LOSS,
   MAX_DISPATCH_LATENCY = 15 SECONDS,
   TRACK_CAUSALITY = OFF,
   STARTUP_STATE = ON
 );


Guessing at column names:

INSERT #temp(dateColumn)
  SELECT dateColumn
  FROM dbo.WorkCalendar
  WHERE IsWorkDay = 1
  AND dateColumn >= @begindate
  AND dateColumn < DATEADD(DAY, 1, @enddate);

Also please try to avoid regional formats like m/d/y. These will bite you in the end, because someone will run your code on a server with different language or regional settings, and it will be interpreted as d/m/y. See Bad habits to kick : mis-handling date / range queries. Also, for information on why I didn't use BETWEEN, see What do BETWEEN and the devil have in common? It may work for the date data type, but I'd rather be consistent than surprised.


ORACLE RAC Installation : Why there is a need to create a symbolic links for asm disk

It is not mandatory, do not use it if you do not want to.

Creating such symlinks just makes it easier to distinguish the ASM disks. You can even set asm_diskstring to /dev/iscsi/* with the above.


Two general principles I would commend to you.  One - name your relationships so you understand why one entity type connects to another.  You may find there are more than one relationships between any two, or that some you have imagined has do not represent anything concrete in the user's world.  Two - imagine some of the uses cases for this system.  Think about the queries you would need to write to fulfill each.  Walk through your data model to ensure it supports your queries and constraints.

The data model posted encodes many, many more rules than those you have listed.  It would be impossible for a third party to validate the model in its entirety without all the rules.  This is a large task which would typically take some days in a room with user representation, in my experience.


mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

  InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

  InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.


how I use multiple SUM with multiple LEFT JOINS

Try

SELECT members.id AS aid,
       ad.deduction_amount,
       ac.total_portion
FROM members
LEFT JOIN ( SELECT agents_deductions.agent_id, SUM(agents_deductions.amount) deduction_amount
            FROM agents_deductions
            WHERE agents_deductions.loan_status = 0
            GROUP BY agents_deductions.agent_id
          ) ad ON members.id = ad.agent_id
LEFT JOIN ( SELECT agents_commission.agent_id, SUM(agents_commission.portion) total_portion
            FROM agents_commission
            WHERE agents_commission.loan_status = 0
            GROUP BY agents_commission.agent_id
          ) ac ON members.id = ac.agent_id
ORDER BY members.id

PS. I do not understand why the agents_data table is included in the query data source. None of its field is used in a query (except join condition) and it cannot affect to the query result because of left join (except unwanted multiplying).


Yes, those are two different queries. They'll both be parsed and saved in the SQL area independently.

SQL> create table share_test (foobar number);
Table created.

SQL> select foobar from share_test;
no rows selected

SQL> SELECT foobar FROM SHARE_TEST;
no rows selected

SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%foobar%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
g9cdv3mpuhd8d
select sql_id, sql_text from v$sqlarea where sql_text like '%foobar%'

d2v4u39j41y6k
select foobar from share_test

fdsqytc1y3f4u
SELECT foobar FROM SHARE_TEST

The criteria for SQL sharing are in the Configuring and Using Memory - SQL Sharing Criteria docs. Among others:

  The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments.


Does the Inserted table contain Identity values?

The error is caused by attempting to insert AuditTerms when there is no corresponding primary key in TermKeys. After creating the table schema and trigger in tempdb and inserting a few records in TermKeys, I was able to reproduce it by issuing a DELETE twice on the same TermID:

DELETE FROM TermKeys
WHERE TermID = 1

DELETE FROM TermKeys
WHERE TermID = 1

gives me:

  Msg 515, Level 16, State 2, Procedure TRG_TermKeys_AuditTerms_IUD,
  Line 15 Cannot insert the value NULL into column 'TermID', table
  'tempdb.dbo.AuditTerms'; column does not allow nulls. INSERT fails.
  The statement has been terminated.

Something in the application code may be running the query twice; I can't see any other way to get a null value. The best way to avoid the error is to add a precautionary check of @@ROWCOUNT at the beginning of the trigger, since it will return the total number of rows affected by the DML statement that fired it.

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys]
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  -- If the trigger was fired but no rows were affected, do nothing
  IF @@ROWCOUNT = 0
    RETURN;
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;


Is there an execution difference between a JOIN condition and a WHERE condition?

According to Chapter 9 (Parser and Optimizer), Page 172 of the Book Understanding MySQL Internals by Sasha Pachev

here is the breakdown the evaluation of a query as the following tasks:

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

On that same page, it says the following:

In MySQL optimizer terminology, every query is a set of joins. The term join is used here more broadly than in SQL commands. A query on only one table is a degenerate join. While we normally do not think of reading records from one table as a join, the same structures and algorithms used with conventional joins work perfectly to resolve the query with only one table.

EPILOGUE
Because of the keys present, the amount of data, and the expression of the query, MySQL Joins may sometimes do things for our own good (or to get back at us) and come up with results we did not expect and cannot quickly explain.
I wrote about this quirkiness before

Jan 23, 2013 : Problem with nested UPDATE queries
Feb 22, 2011 : Problem with MySQL subquery

because the MySQL Query Optimizer could make dismiss certain keys during the query's evaluation.
@Phil's comment help me see how to post this answer (+1 for @Phil's comment)
@ypercube's comment (+1 for this one too) is a compact version of my post because MySQL's Query Optimizer is primitive. Unfortunately, it has to be since it deals with outside storage engines.
CONCLUSION
As for your actual question, the MySQL Query Optimizer would determine the performance metrics of each query when it is done

counting rows
selecting keys
massaging intermittent results sets
Oh yeah, doing the actual JOIN

You would probably have to coerce the order of execution by rewriting (refactoring) the query
Here is the first Query you gave
select count(*)
from   table1 a
join   table2 b
on     b.key_col=a.key_col
where  b.tag = 'Y';

Try rewriting it to evaluate the WHERE first
select count(*)
from   table1 a
join   (select key_col from table2 where tag='Y') b
on     b.key_col=a.key_col;

That would definitely alter the EXPLAIN plan. It could produce better or worse results.
I once answered a question in StackOverflow where I applied this technique. The EXPLAIN was horrendous but the performance was dynamite. It only worked because of having the correct indexes present and the use of LIMIT in a subquery.
As with stock prices, when it comes to Queries and trying to express them, restrictions apply, results may vary, and past performance is not indicative of future results.


https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0021804.html

  User authentication is completed by a security facility outside the Db2 database system, through an authentication security plug-in module. A default authentication security plug-in module that relies on operating-system-based authentication is included when you install the Db2 database system

If the reset password feature of the CLP is not working for you, you should reset the password directly on the operating-system at the server (or on the security system (e.g. LDAP) that Db2 has been configured to use)


When I implement database mirroring in SQL server it shows an error

I would suggest you to verify the service accounts you are using:

SQL Server service account requires CONNECT permissions to partners’
endpoints
Best option is to use domain accounts for all partners
If using Local Service or Local System, must use certificate
authentication.

  Ensure that the start date of the certificate is the current date or a
  day in the past relative to all servers. Due to time zone differences,
  if the current date on the server is earlier than the start date of
  the certificate, endpoint authentication will fail. Set the START_DATE
  option of the certificate to the day prior to when you are creating it
  to ensure it will work in all time zones.

If using Network Service, must use the computer account
(domain\Computer$) Windows permissions are irrelevant. Do NOT add to
local admins group
Check the SQL log for errors

Also check for  SQL Server service account in case it  do not exist in logins on both Principal & Mirror

You can fix for above case by adding the service account in logins on both the servers and grant the connect permission as below;

GRANT CONNECT ON ENDPOINT::Mirroring TO [domain\loginname]

For further troubleshooting in case above does not help read here


Putting an order in the way how locks are aquired in MySQL

Short answer: No.

Thing you can do:

Tailor the indexes to the queries -- this may decrease the frequency of deadlocks.  Would you like to provide some examples of what is giving you trouble?
Sort the items in IN(...) -- this may prevent certain deadlocks.
Check for errors and replay transactions that are rolled back due to a deadlock.  Then live with the deadlocks.


Possible to keep max number of records in postgresql?

You can define a trigger to maintain your desired row number:

CREATE OR REPLACE FUNCTION trf_keep_row_number_steady()
RETURNS TRIGGER AS
$body$
BEGIN
    -- delete only where are too many rows
    IF (SELECT count(id) FROM log_table) > rownum_limit
    THEN
        -- I assume here that id is an auto-incremented value in log_table
        DELETE FROM log_table
        WHERE id = (SELECT min(id) FROM log_table);
    END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_keep_row_number_steady
AFTER INSERT ON log_table
FOR EACH ROW EXECUTE PROCEDURE trf_keep_row_number_steady();

This is probably not the best performing option, but once you reach the limit, it will never be exceeded.  If there is space for fluctuation, then you can check the row number periodically and delete excess rows from the beginning.

EDIT:
If you have really large logs (say a million per month) than partitioning can be the easiest solution.  You can then simply drop the unnecessary tables (say where max(timestamp) < CURRENT_DATE - 1 year).  You can use your timestamp (or a derived date) as condition for range partitioning.

But be careful before discarding old logs.  Are you sure you will never need those?


Referencing a SELECT subquery value in the WHERE clause

The problem arises because the aliases defined in the SELECT list are not visible by the same level WHERE clause - and the reason behind this is the order of logical execution of queries (which is roughly FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY, omitting some other clauses).

You can bypass this problem with several methods:

duplicating the expression code for the alias (the subqueries code here), so the code is both in the SELECT and in the WHERE clause (or in the SELECT and in the HAVING clause, if there is a GROUP BY and the expression uses aggregated values). This might result in a query which looks more complicated that it needs to be - and it may result in errors/inconsistencies later (if a developer changes the subquery/expression only in 1 of the 2 places).
move the expression code (subquery in our case) to a CTE. This may not be possible or easy to do in all cases - eg. if the expression is a correlated subquery, like in the specific issue.
wrap the whole query into a derived table (or CTE) and move the conditions for those (calculated, aliased) columns to the external WHERE clause. This should work in all cases. It may not be the most efficient though in every case. If the optimizer can "push down" the condition, the plan should be identical to solution 1 (having the code duplicated) as the optimizer can then apply further transformations and choose between different execution paths (in regard with checking the various WHERE conditions and the order of joining tables). If the condition is/can not pushed down, it will have to be evaluated after the derived table which may not be the most efficient in some cases.
use OUTER APPLY instead of duplicating code. This is possible only if the expression does not use aggregated values. (If it uses aggregation, it will need more complex rewriting, using a derived table and OUTER APPLY.)
This in my opinion the best option, as the condition is already "pushed down" to the same level as the rest joins and conditions and we are sure the optimizer has more options to plan the execution. Plus the code is only in one place.

For your case, solutions 1 (duplicate code), 3 (wrap the whole query in a derived table) and 4 (the OUTER APPLY) seem to be doable. The APPLY would look like:

-- the SELECT, with the subqueries removed
SET @vSelect = '
        SELECT
             T1.BranchShortName AS BranchShortName,
             LA.LastApproval,
             NA.NextApproval,
             T6.StatusDescription AS Status
'
-- the FROM as it was (whatever you have there, doesn't change)
SET @vFrom = '
        FROM dbo.table_1 AS T1
          JOIN dbo.table_2 AS T2
          ON  T1.x = T2.y
'

Then we add the APPLY correlated subqueries to FROM clause:

-- OUTER APPLY subqueries added
SET @vFrom = @vFrom + '
          OUTER APPLY
            ( SELECT TT1.EmployeeName AS LastApproval
              FROM [dbo].[Tb_OPL_Employee] AS TT1
                JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
              WHERE TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                    ( SELECT MAX(TTT3.IdTb_OPL_JobTitles)
                      FROM [dbo].[Tb_SYS_Approval] AS TTT3
                      WHERE TTT3.IsPassed = 1
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId = TT2.TaskId)
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
            ) AS LA (LastApproval)
          OUTER APPLY
            ( SELECT TT1.EmployeeName AS NextApproval
              FROM [dbo].[Tb_OPL_Employee] AS TT1
                JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
              WHERE TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                    ( SELECT MIN(TTT3.IdTb_OPL_JobTitles)
                      FROM [dbo].[Tb_SYS_Approval] AS TTT3
                      WHERE TTT3.IsPassed = 0
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId = TT2.TaskId)
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
            ) AS NA (NextApproval)
'

Now, we can use the subqueries results in the WHERE clause:

SET @vWhere = ' WHERE 1=1 '

IF (@ddlSearchCriteria = '1')
BEGIN
    -- The condition of search textbox is not empty
    IF (@txtSearch <> '')
    BEGIN
        -- The Condition of StartDate is not empty and EndDate is empty
        IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'

        -- The Condition of StartDate is empty and EndDate is not empty
        IF (@txtStartDate = '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- The Condition of StartDate is not empty and EndDate is not empty
        IF (@txtStartDate <> '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- we can use LA.LastApproval as any other column
        -- and the OUTER APPLY becomes CROSS APPLY
        IF (@txtStartDate = '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND LA.LastApproval LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''

        PRINT @vSelect + @vFrom + @vWhere + ' ;'
    END


On Duplicate Key Update - million times per day optimization?

The table is InnoDB, correct?  (If not, that change would be important.)

If there is an AUTO_INCREMENT column, you will burning most of a million values each day.  Please provideSHOW CREATE TABLE.

That's about 12/second -- not a deadly rate of changes.

Please time the IODKU and time the following, then report back:

UPDATE ... country=country+1;
if rows_affected = 0 then
INSERT ...,

I suspect they will be about the same speed, but I have not tested it.

Oops, I have a subtle bug in that code -- If another thread sneaks in and does the Insert while I am fumbling around with rows_affected, then the Insert could get "dup key".  The fix:  Change the INSERT to IODKU.

If you end up with more than 100/second, consider collecting the values, then patch applying them.  I would probably do a variant on http://mysql.rjweb.org/doc.php/staging_table .


ORA-1110, ORA-1114, OSD-4002, O/S-Error: (OS 33) The Process Cannot Access The File

The error is specific to Windows platform, where the file is being accessed by another process, probably non-Oracle processes such as anti-virus, OS backup or something else.

Make sure there is no other non-Oracle process accessing the data file #201 when you are running that procedure.

Resolving ORA-27072 , OSD-04008 and O/S-Error: (OS 33) On Windows Platforms (Doc ID 2016620.1)

Update:

In order to find which application is using a specific file-

Download Handle from here, if you don't have already.
Extract the downloaded .zip file in your drive.
Run handle.exe <path to your datafiles> on command prompt.


Determine Index Compression Candidates Online

The optimal number of columns to compress depends on:

The number of entries that will fit in each block (this depends on the number of compressed columns as they are only stored once per block)
The average number of entries with same prefix

These factors can be estimated for the table

The aim is to maximize the size of the compressed prefix whilst minimizing the number of blocks needed to hold all rows with the same prefix.

Assuming that the data is uniform at least to a degree, and ignoring the small amount of overhead compression introduces, you could attempt to implement this approach like this:

helper functions:

create or replace function f_size( p_table_name in varchar,
                                   p_column_name in varchar)
                  return number as
  n number;
begin
  execute immediate
    'select avg(vsize('||p_column_name||'))+1 from '||p_table_name into n;
  return n;
end;
/

create or replace function f_count( p_table_name in varchar,
                                    p_column_names in varchar )
                  return integer as
  n integer;
begin
  execute immediate 'select count(*) '||
                    'from ( select '|| p_column_names ||
                           ' from '||p_table_name||' '||
                           'group by '||p_column_names||' )'
          into n;
  return n;
end;
/

test IOT:

create table t ( k1, k2, k3, k4, k5, val,
                 constraint pk_t primary key(k1, k2, k3, k4, k5))
       organization index as
select mod(k,10)||'_____',
       mod(k,20)||'_____',
       mod(k,30)||'_____',
       mod(k,50)||'_____',
       k||'_____',
       lpad(' ',100)
from (select level as k from dual connect by level<=1000);

query:

with utc as (select table_name, column_name, f_size(table_name, column_name) as column_size from user_tab_columns where table_name='T'),
     uic as (select table_name, column_name, column_position, column_size from user_ind_columns join utc using(table_name, column_name) where index_name='PK_T')
select z.*, (8192-prefix_size*prefixes_per_block)/remaining_size as rows_per_block
from( select z.*, greatest(1,8192/(prefix_size+rows_per_prefix*remaining_size)) as prefixes_per_block
      from( select z.*, total_count/distinct_count as rows_per_prefix
            from( select prefix_length, sum(column_size) as prefix_size, (select sum(column_size) from utc)-sum(column_size) as remaining_size, f_count(table_name, max(prefix_columns)) as distinct_count,
                         (select count(*) from t) as total_count
                  from( select table_name, connect_by_root column_position as prefix_length, column_size, substr(sys_connect_by_path(column_name, ','),2) as prefix_columns
                        from uic
                        connect by column_position=(prior column_position-1) )
                  group by table_name, prefix_length ) z ) z ) z
order by 1;

result:

PREFIX_LENGTH          PREFIX_SIZE            REMAINING_SIZE         DISTINCT_COUNT         TOTAL_COUNT            ROWS_PER_PREFIX        PREFIXES_PER_BLOCK     ROWS_PER_BLOCK
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1                      7                      132.854                10                     1000                   100                    1                      61.608
2                      14.5                   125.354                20                     1000                   50                     1.304                  65.200
3                      22.161                 117.693                60                     1000                   16.666                 4.129                  68.827
4                      29.961                 109.893                300                    1000                   3.333                  20.672                 68.909
5                      38.854                 101                    1000                   1000                   1                      58.575                 58.575

check:

analyze index pk_t validate structure;
select opt_cmpr_pctsave, opt_cmpr_count from index_stats;

OPT_CMPR_PCTSAVE       OPT_CMPR_COUNT
---------------------- ----------------------
13                     3

The check above roughly corresponds with the prefix length with the maximum rows_per_block in the calculation - but I suggest you check my working carefully for yourself before trusting it :)

I am assuming the table is so large that you can't just take a copy and try out different prefix lengths. Another approach would be to do just that on a sample of the data - the sample should be chosen as a random selection of prefixes for a given compression candidate (rather than just a random selection of rows)


Error while installing MySQL on RedHat Linux

I will recommend to move from php-mysql to php-mysqlnd (which provides the same set of extensions: mysql, mysqli and pdo_mysql), and thus, get rid of dependency on libmysqlclient:

# yum shell --enablerepo=remi
> remove php-mysql
> install php-mysqlnd
> run

Notice: PHP 5.4 is now EOL, I also recommend to upgrade to 5.5 or 5.6. And 5.4 is also the last version to provides php-mysql, newer RPM versions only have php-mysqlnd.


How can I configure PostgreSQL 9.0 to start automatically under Windows XP?

Here's a couple things to check:

I believe the Windows PostgreSQL installer has an option you have to check saying you want to run PostgreSQL as a service.  Make sure you used this option when you did your install.
Make sure the service is set to start automatically.

Start->Control Panel->Administrative Tools->Services
Find the service (should be named something like postgresql-9.0)
Make sure the Startup Type is set to Automatic


PostgreSQL IN operator with lots of values in an external file

Sure, either,

Use a CTE with a VALUES statement.
Use a TEMP table with an index.

Here is an example with the CTE.

WITH t AS (
  SELECT * FROM ( VALUES
    (1),
    (2),
    (3)
  ) AS t(table_id)
)
SELECT * FROM t
JOIN myTable
  USING (table_id)
;

Only slightly more complex is getting them into a temp table which permits you to index it. You can also use the Foreign Data Wrapper create a FOREIGN TABLE if you have an external file depending on the format. Try it without an index, and copy it into a table and see if an index speeds it up.


I want to change oracle "homedir" how to do?

Solution found,following this link

I wrote so a dirty and quick solution
In my case olddir was /oracle and new /var/oracle on aix 71
oracle was working,user created,etc..

#for safe shutdown oracle,is not necessary,but better

#mkdir
mkdir -p /var/oracle/app/oracle/product/12.2.0/dbhome_1;chown -R oracle:oradba  /var/oracle

#clone with tar
tar -zcvf  /var/oracle/Oracle_Home11g_clone.tar.gz . -X ~/exclude.txt
or better  use  rsync
rsync -avP --delete $HOMEOLD/ $HOMENEW

#edit /etc/ora* and set new home

#clone with runinstaller
/oracle/app/oracle/product/12.2.0/dbhome_1/oui/bin/runInstaller -clone -silent -defaultHomeName ORACLE_HOME=/var/oracle/app/oracle/product/12.2.0/dbhome_1 ORACLE_BASE=/var/oracle/app -waitForCompletion

# script from root
su root -
/var/oracle/app/oracle/product/12.2.0/dbhome_1/root.sh

#rsync remaining dir
rsync -avP --exclude app /oracle/* .

#edit all variables on oracle homedir(.profile,.bashrc,etc)

#edit  /etc/filesystems or fstab

#edit orafiles
find . -iname *.ora|xargs grep -lw "/oracle"
vim file.ora..

#test running db with olddir unmounted


how to find similar word with more similarities

I've never thought of SOUNDEX or DIFFERENCE as that useful, and they are certainly a bit old-fashioned these days.  However we can combine it with the full-text parser, and it does in fact answer this query.  Note the extra quotes in the search statement:

DECLARE @inp NVARCHAR(MAX), @data NVARCHAR(MAX)
SET @inp= '"You can dance, you can jive, having .... jove... jve, ..."'
SET @data = 'jeve'

SELECT display_term, SOUNDEX(display_term) sdx
FROM sys.dm_fts_parser(@inp,0,0,0)
WHERE SOUNDEX(display_term) = SOUNDEX(@data)
 AND LEN(display_term) <= LEN(@data)

SELECT display_term, SOUNDEX(display_term) sdx, DIFFERENCE(display_term,@data) diff
FROM sys.dm_fts_parser(@inp,0,0,0)

There is a fairly limited use case for these functions so if you have more complex examples then they probably won't extend.  Worth a mention though.


Database design, 1-to-many or many-to-many?

Don't try to over-design your solution. Relational theory, like many other academic subjects can often be taken to extremes, to the point where they are difficult to grasp for a third party that will (eventually) inherit your work.

I think the most common data model you'll find, with regards to time reporting, looks something like this:

CREATE TABLE rep.Users (
    UserID     int PRIMARY KEY,
    ManagerID  int NULL,
    Name       varchar(100),
    FOREIGN KEY (ManagerID) REFERENCES rep.Users (UserID)
);

CREATE TABLE rep.Projects (
    ProjectID  int PRIMARY KEY,
    Name       varchar(100),
);

CREATE TABLE rep.TimeReport (
    UserID     int,
    ProjectID  int,
    StartTime  datetime,
    EndTime    datetime,
    Approved   bit DEFAULT (0),
    PRIMARY KEY (UserID, StartTime), /* possibly also ProjectID */
    FOREIGN KEY (UserID) REFERENCES rep.Users (UserID),
    FOREIGN KEY (ProjectID) REFERENCES rep.Projects (ProjectID)
);

In this design, the notion of a "report" is implicitly what you decide it to be: a day, a week, a month, etc. You can add various elements depending on your needs, for instance:

Is there a start or end validity date to each project and/or user?
Do you want EndTime or NumberOfHours? You can always calculate one from the other, so you never need both.
Do you need approval history or is it enough to know if the report is approved (i.e. closed) or not?


Should I create an enumeration or individual flags?

You should normalize this and create a reference table, that way you can use a simple data type for the type indicator and also be future proof if you need to add types. It also makes for easier queries, plus this structure will maintain the constraint of each product only being of one type at a time.

so (assuming this is an Oracle db):

   Create table Product_types
   (id int,
    name varchar2(100),
    constraint pk_product_types primary key  (id)
    );

create table products
    (id int,
     product_type int,
     somecolumn varchar2(100),
     anothercolumn varchar2(100),
     somedatecolumn date,
     constraint fk_products_product_type foreign key (product_type) references product_types(id)
     );


Upgrade Database from SQL Server 2008 to SQL Server 2008 R2

In this case, the error message is quite clear. You have backed up a database from a SQL Server 2008R2 instance and tried to restore it on a SQL Server 2008 instance.

It is not possible to restore databases to a previous version of SQL Server. This has to do with internal database structure differences between the versions. The only way to get your data from a SQL Server 2008R2 instance onto a SQL Server 2008 instance would be to export the data and import it. You could achieve this through SSIS, bcp, Replication or Linked Servers.


How to “select * from table from database” with -e?

Got it!

Just had to add the database name before the execute flag (-e) and the sql statement.,

mysql -u root db01 -e “select * from table1 limit 1”

Where,

db01 is the database name
table1 is the table name
limit 1 for showing only 1 record


Simple query hangs on a specific column condition

I might be guessing on this one, but I think the key distribution must be insane.

POSSIBILITY #1

Please run this

SELECT * FROM
(
    SELECT IFNULL(user_id,-1) user_id,COUNT(1) prod_count
    FROM products GROUP BY IFNULL(user_id,-1)
) A ORDER BY prod_count DESC;

If you can send the output to a text file, look for user_id 12 and compare its prod_count to the total number of rows in the products table. My guess is that user_id 12 is probably more than 5% of the table. The Query Optimizer would give up using any index on user_id and just do a full table scan. To make sure that is the case, please run

EXPLAIN SELECT * FROM products WHERE user_id = 12;

POSSIBILITY #2

There may be stale index stats and you need to run

ANALYZE TABLE products; if there are a lot of INSERTs, UPDATEs.
OPTIMIZE TABLE products; if there are a lot of INSERTs, UPDATEs, and DELETEs.

GIVE IT A TRY !!!


Select all from table using procedure oracle

You open two cursors  but close the outer one before the inner one

open gen_row; -- open the outer cursor...this semicolon seems odd
for data in(SELECT * from testnirmal)
loop
fetch gen_row into a1;
open gen1_row(a1);
fetch gen1_row into a2;
dbms_output.put_line(a2);
close gen1_row;
end loop;
close gen_row;--here you close the outer cursor
close gen1_row; --here you close the inner cursor when it is out of scope