How to view the user limits like max_queries or max_updates in MySQL?

MySQL 8.0 Reference Manual  /  ...  /  Setting Account Resource Limits (valid for 5.5+ at least).

  The server stores resource limits for an account in the user table row corresponding to the account. The max_questions, max_updates, and max_connections columns store the per-hour limits, and the max_user_connections column stores the MAX_USER_CONNECTIONS limit.

SELECT CONCAT('\'', user, '\'', '@', '\'', host, '\'') account,
       max_questions,
       max_updates,
       max_connections,
       max_user_connections
FROM mysql.user;


Surrogate key resolution, or what am I missing in the Surrogate v Natural argument?

A proper Postgres schema for your example could look like this:

CREATE TABLE selected_task(
  selected_task_id serial PRIMARY KEY  -- surrogate PK
, user_id          int REFERENCES users(user_id)
, task_number      int
, name             text
, jobsite_id       int REFERENCES site(jobsite_id)
);

CREATE TABLE sub_task(
  sub_task_id      serial PRIMARY KEY
, selected_task_id int REFERENCES selected_task  -- this is how
, assignee         int REFERENCES users(assignee)
, time_allowed     int
, report_to        int REFERENCES groups(report_to)
);

Use a serial column as surrogate PK.

PostgreSQL SELECT primary key as "serial" or "bigserial"
Auto increment SQL function
Use serial ID for multiple columns and return it from INSERT

You can't use reserved words like user or group as identifier.
Some other improvements. Detailed instructions here:

How to implement a many-to-many relationship in PostgreSQL?

INSERT in both tables at the same time?

Use a data-modifying CTE with the RETURNING clause, or two INSERT statements in a transaction with lastval() (which is the Postgres equivalent for SCOPE_IDENTITY() in SQL server @Aaron mentioned in his comment):

Related answer with code example for either method.

Inserting into related tables


Lock priority for scripts run in maintenance windows

Microsoft introduced the new Low Priority Lock Wait option for Index rebuilds in SQL Server 2014. Not sure what SQL Server version you are running.

Index rebuild using 'low priority lock wait':

ALTER INDEX Index_name
ON Schema.Table_name
REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY
             (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )))
;

Check out Paul Randal's excellent article here Low Priority Wait and Testing Index rebuilds with Low Priority Wait by Kendra Little for clear explanation.

That's specific for Index maintenance, I am assuming your maintenance plans does include Integrity checks and other stuff too.

An option would be writing some TSQL code to monitor blocking and and create and schedule a SQL agent job to automatically kill the 'low priority session' based on the SQL text its running.

If you are experiencing issues caused by deadlocks, you could probably lower the Deadlock priority value to see if that helps.

Depending on the type of resource contention, you can also try limiting the resources for specific logins using Resource Governor.

If you are using custom maintenance plans, you probably need to look into and start using Ola Hallengren's Maintenance solution.


Phrases like bad for performance are essentially meaningless. Measurements are  meaningful; opinions are, umm, not measurements.

But int(7) and int(11) are both 32-bit integers. The number in parens refers to display width; it has nothing to do with storage size or index size.

For varchar() columns, you can create an index that's based on the first 'n' characters. That decision has some performance implications, for example when you're searching for 'wibble' in a column indexed on the first three characters. And there are several subtle points with columns of type "text". Read CREATE TABLE carefully. (Search for "length".)

But I'd be a little surprised if you could measure any difference in joining columns of varchar(200)/varchar(200) and in joining columns of varchar(220)/varchar(200). That is, other than the obvious--storing more than 200 characters in the varchar(220) column.

There's an easy way to tell: write some code in your favorite scripting language, generate a few million rows of sample data, load it, and test it. And post your results here, too.


Unable to use cyrillic characters in mysql docker console

As it turns out this was indeed a Docker problem rather than MySQL.
Vanilla MySQL image is built on Debian 9 and by default it lacks UTF-8 support. So I had to follow these steps:

Login into Docker container with docker exec -it mysql1 bash and run apt-get update
Then run apt-get install vim apt-get install locales
Run dpkg-reconfigure locales and select 376 for ru_RU.UTF-8
Open ~/.bashrc and add

export LANG=ru_RU.utf8
export LANGUAGE=ru_RU.utf8
export LC_ALL=ru_RU.utf8

Exit the shell and restart the container

Now I can use Russian characters inside the MySQL docker console.
PS: I guess a cleaner and more "Dockeresque" solution would be to run these commands from a Dockerfile. If anybody could provide an answer with a dockerfile example, I'd be happy to accept it.


What's better for large changes to a table:  DELETE and INSERT every time or UPDATE existing?

It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column.

Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are changing and lets say these are not part of any non-clustered indexes, then you may be better off "Updating" the records because in this case only the clustered index will be updated (and indexes will not have to be updated).

On further research, I did find that the above comment by me is sort of redundant as SQL Server internally has 2 separate mechanism for performing an UPDATE. - An "in-place update" (ie by changing a columns value to a new in the original row) or as a "not-in-place UPDATE" (DELETE followed by an INSERT).

In place updates are the rule and are performed if possible. Here the rows stay exactly at the same location on the same page in the same extent. Only the bytes affected are chnaged. The tlog only has one record (provided there are no update triggers). Updates happen in place if a heap is being updated (and there is enough space on the page). Updates also happen in place if the clustering key changes but the row does not need to move at all.

For eg: if you have a clustered index on last name and you have the names:
Able, Baker, Charlie
Now you want to update Baker to Becker. No rows have to be moved. So this can take in-place. Whereas, if you have to update Able to Kumar, the rows will have to be shifted (even though they will be on the same page). In this case, SQL Server will do a DELETE followed by an INSERT.

Considering the above, I would suggest that you do a normal UPDATE and let SQL Server figure out the best way to how to do it internally.

For more details on "UPDATE" internals or for that matter any SQL Server related internals, check out Kalen Delaney, Paul Randal's, et al.'s book -  SQL Server 2008 Internals.


Type of Foreign Keys for a Dimensional Model Fact Table?


Availability Group with log backups to NUL

No, you aren't missing anything except the opportunity to do a point in time recovery of the database.  AG's protect you from MOST hardware failures but they don't protect from user errors that drop a table or truncate data. Even if the business didn't have a SLA for RPO I would have log backups running as a DBA just to cover things, but you can backup to DISK='NUL' and the log will clear just like you backed up to a regular disk file.


How to determine whether a PostgreSQL Hot Standby is fully mirrored?

I think this is normal and expected if your restore_command is set to something like this example:

restore_command = 'cp /mnt/server/archivedir/%f "%p"'

The manual says that:

  At startup, the standby begins by restoring all WAL available in the
  archive location, calling restore_command. Once it reaches the end of
  WAL available there and restore_command fails, it tries to restore any
  WAL available in the pg_xlog directory. If that fails, and streaming
  replication has been configured, the standby tries to connect to the
  primary server and start streaming WAL from the last valid record
  found in archive or pg_xlog. If that fails or streaming replication is
  not configured, or if the connection is later disconnected, the
  standby goes back to step 1 and tries to restore the file from the
  archive again. This loop of retries from the archive, pg_xlog, and via
  streaming replication goes on until the server is stopped or failover
  is triggered by a trigger file.

So you can expect to see exactly one restore_command failure when you start your standby, because PostgreSQL will keep calling it (with incrementing log file names/numbers) until it fails once.

Then it will connect to the primary and start streaming as described above, and as you saw in your logs:

LOG:  streaming replication successfully connected to primary

The slave is not guaranteed to be exactly up-to-date with the master, because it could be disconnected from the master for example. In particular, this line:

LOG:  consistent recovery state reached at 31/B73624A0

does not mean that "the hot standby contains all the data of the master". However, if you see it followed by this line, as you did:

LOG:  database system is ready to accept read only connections

then the database is "ready enough" to start functioning as a read-only standby, as the manual says:

  It may take some time for Hot Standby connections to be allowed,
  because the server will not accept connections until it has completed
  sufficient recovery to provide a consistent state against which
  queries can run. During this period, clients that attempt to connect
  will be refused with an error message.

In my case, I saw consistent recovery state reached not followed by database system is ready to accept read only connections. This turned out to be a problem with an embedded scripting language plugin (plpython2) having a system-wide startup script (sitecustomize.py) which did bad things to the PostgreSQL process (enabling faulthandler and installing a signal handler for SIGUSR2) which caused it to never enter hot standby mode.


What flavor of SQL is used inside Microsoft Access?

For full details read the docs: Microsoft Access SQL reference

MS Access definitely is not the same as SQL Server. The overall syntax is similar as any flavor of SQL, but it doesn't have a lot of features that were added since SQL Server 2005.

It doesn't even have some pretty basic features, like:

  DISTINCT aggregate function references. For example, Microsoft Access SQL does not allow SUM(DISTINCT columnname).
  The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. You can use only the WHERE clause to limit the
  scope of a query.

On the other hand, it has enhanced features, like:

  The TRANSFORM statement, which provides support for crosstab queries.
  Additional aggregate functions, such as StDev and VarP.
  The PARAMETERS declaration for defining parameter queries.

Window functions that were added since SQL Server 2005+, such as ROW_NUMBER, OVER clause don't exist as well.

Set of built-in functions for string or date/time manipulation are similar, but they are not the same.


You were close with your approach. The two issues with it that I can see are:

The subquery is not correlated with the outer query. Your subquery needs to have a WHERE clause to limit the rows only to those where EmpName is the same as the outer query's EmpName.

In order to be able to reference the outer query's columns, you need to assign a different alias to either instance of dbo.TableName. It would probably be best to assign one to each – something like this:

SELECT
  ...
  (SELECT ... FROM dbo.TableName AS b WHERE b.EmpName = a.EmpName)
FROM
  dbo.TableName AS a
...

The main query does not need to group by Period_Time, because it is aggregating that value.

Just remove the Period_Time column from both the GROUP BY and SELECT.

So, this is how your query could look like after addressing both issues:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0
              / (
                  SELECT
                    SUM(b.Period_Time)
                  FROM
                    dbo.TableName AS b
                  WHERE
                    b.EmpName = a.EmpName
                )
FROM
  dbo.TableName AS a
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;

However, you could do better than using a correlated subquery here. Since SQL Server 2005, Transact-SQL has started supporting window aggregate functions. They allow you to obtain aggregate results alongside detail results and use both in various calculations – exactly what you need for your problem.

Basically, the syntax would go like this:

SELECT
  SomeKey,
  DetailData,
  TotalData = SUM(DetailData) OVER (PARTITION BY SomeKey),
  -- or you can get percentage directly, like this:
  -- Percentage = DetailData * 100.0 / SUM(DetailData) OVER (PARTITION BY SomeKey)
FROM
  dbo.SomeTable
;

There is only one issue: you cannot calculate the total Period_Time like this:

SUM(Period_Time) OVER (PARTITION BY EmpName)

because in your case Period_Time would be an invalid reference: that column is not in GROUP BY and may not be referenced without being enclosed in a (non-window) aggregate function. Your main query already aggregates it using SUM – that is how it can be used in SUM ... OVER as well:

SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)

For reference, here is an example of a complete query that calculates percentage using window aggregation instead of a correlated subquery:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0 / SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)
FROM
  dbo.TableName
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;


Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)?

Just change the configuration.  It will take effect next time a backup is started.  If there is a currently executing backup it will not switch midstream.  You can let it finish, or kill it and start a new one with the new configuration.


In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all?

that claim seems spurious.

I do integer IDs with multi-master replication, it's not hard.

I set the step of the sequences to such that two servers cannot use the same id number.

alter sequence foo_id_seq set increment_by=10;
select setval('foo_id_seq',2);  -- or 3 or 4 etc...

but if you can do it natural keys are even easier to replicate.


can't update user password on Mongodb 4.0.5 (Use of SCRAM-SHA-256 requires undigested passwords)

Basically, I made it with db.updateUser.

On the doc, it says the following:

passwordDigestor : string

  Optional. Indicates whether the server or the client digests the password.

  Available values are:

  "server" (Default)
          The server receives undigested password from the client and digests the password.

  "client" (Not compatible with SCRAM-SHA-256)
          The client digests the password and passes the digested password to the server.

  Changed in version 4.0: The default value is "server". In earlier versions, the default value is "client".

It seems that, the default was "client", by unknown reasons. By the way, my Mongo Shell Client is v3.6.3.

Therefore, I just defined passwordDigestor as server, and ok. Logged out and logged in with the new password.

> db.updateUser("mongo", {pwd:"mongo_n3w_p4ss", passwordDigestor:"server"});


Migrating SQL Server 2000 to SQL Server 2008

If you're asking how to get this information from a SQL Server 2008 installation, then use sys.sql_modules to find the text of a procedure/view

select m.definition
    from sys.views v
    join sys.sql_modules m
        on v.object_id = m.object_id
    where definition like '%ORDER[ ]BY%'

or

   select object_definition(object_id)
    from sys.views v
   where object_definition(object_id) like '%ORDER[ ]BY%'

If you're asking how to get the information from the SQL Server 2000 database, why not backup and restore? Or you can connect to your SQL Server 2000 instance using SSMS, right click on the database, choose "Generate Scripts" and select all objects (or views, or procedures, etc.).


Innodb because its transactional does do recovery/rollback during startup. If it was an unclean shutdown this can take some time depending on tuning / data volume.

As other tables don't have extensive checks they are quick to start up.

Debian does script in myisam checks in the service start meaning the availability of MyISAM tables could be delayed.


No need to run a query - just input

SHOW GRANTS;

If you have sufficient privileges, you should get all their privileges.

A bit more fine-grained is the following query

mysql> SELECT grantee, table_catalog, table_schema, privilege_type, is_grantable
 FROM schema_privileges;

This way, you can do it by user, &c. BTW, you have to run this from the information_schema. This information

This page should also be of use.

Using the mysql schema, you can issue the query

mysql> SELECT Host, Db, User, Table_name, Column_name, Column_priv FROM columns_
priv;

From this page.


SQLite3 not using covering index with json_extract expression

The documentation says:

  The SQLite query planner will consider using an index on an expression when the expression that is indexed appears in the WHERE clause or in the ORDER BY clause of a query.

So expressions in the SELECT clause will not use the expression index.

Using a covering index is not as much an improvement over using a normal index for searching/sorting as using a normal index would be over using no index at all, so this optimization has not (yet?) been implemented for expression indexes.


Enabling TLS and TLS-MA simultaneously for different clients of a Oracle DB

SSL_CLIENT_AUTHENTICATION is a sqlnet.ora parameter - effectively a property of the database itself - not a listener parameter. You can only have one value per database instance (stand-alone or container), so there is no way to have the parameter set differently for separate clients. What you are attempting cannot be done as far as I know.


If calculation is negative it shows NULL, how to fix this?

The problem is here:

(select total_count from cte where [object] = 'target')

There is not object = 'target' in the "cte" cte.
As the select returned null, the calculation also have to return null.
Try this: (changing cte for cte1)
with cte as (
            select 'source' [object],count(contract_id) as total_count
            FROM [account].[dbo].[account] act
            left join [account].[dbo].[contract] cont
            on act.account_id = cont.account_id)
,cte1 as(
          select 'target' [object], count(contract_id) as total_count
          from [account].[dbo].[action]
          union
          select * from cte)
          select * from cte1
          union
          select 'diff' [object],
                 (select total_count from cte where [object] = 'source')
                -
                 (select total_count from cte1 where [object] = 'target')


Are PostgreSQL dynamic arrays more wasteful than plain text?

His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources.

Evidence?

If he's arguing performance against what'd otherwise be a clarity and correctness improvement, the onus is on him to provide evidence.

Delimited text is usually the wrong way to do it - see e.g. this related question.

  I don't know enough about PostgreSQL to know if the use of arrays in this case would be concern.

It depends on how you're constructing the arrays. Try to create them in a single run using array_agg or an ARRAY[] constructor or similar. Iteratively building them with concatenation in a loop tends to be very slow because at the SQL level arrays are immutable, so they have to be copied to append to them.

Of course, the same is true of text at the SQL level, and the same principles apply.

  My questions are: do PostgreSQL dynamic arrays use up more memory than text?

It depends on the underlying type. In general, if t1 uses more memory than t2, then an array of t1 uses more memory than an array of t2.

The internal storage of arrays depends a little on how they're created, but in general they add little or no memory overhead over the base type.

  And if so, is it a significant concern to drop the use of arrays on large queries?

Yes, but no more of a concern than using delimited text.

Try to return result sets, not huge single results.


SSMS generated script failing to execute

Try running SQLCMD with the -x argument to disable variable substation. This will avoid issues when the script includes SQLCMD variable tokens (e.g. $(stuff-here)) but are not actually SQLCMD variables.


Migrating MySQL database structure with foreign keys fails

Doh, I noticed that there was some remains from an earlier, botched migration. Dropped all databases, and then import worked.


Is this a sane model for a network provisioning system?

Understanding of the specifications and proposed data model
We have mantained very productive interactions via comments, I have read your question carefully and examinined your diagram closely, so I have prepared the following assertions that I consider relevant in order to describe my current understanding of your scenario and, therefore, share my personal approach to such:

A Node lodges one-to-many Switches.
A Switch forwards data to one-to-many Services (employing one particular VLAN in each data forward).
A Switch provides one-to-many Services to one-to-many CPEs (employing one specific VLAN in each provisioning). Or, put another way, a CPE is provided with one-to-many Services by one-to-many Switches (using one specific VLAN in each provisioning).
A CPE is classified by one SpeedProfile.
A CPE is set with one Port in oder to receive one specific  Service.
A CPE is set with one Port in oder to receive data from one specific Switch.
A Customer (either one Organization or one Person) receives one-to-many Services via one CPE.

From such collection of assertions, I have derived (and uploaded as a .PDF document to Dropbox) the following…

Network Provisioning Preliminary Data Model.

Note: It is necessary to point out that some of these assertions (or parts of specific ones) have been identified as inaccurate or erroneous, as mentioned below in the section entitled Subsequent conclusions.
As you can see in said IDEF1X data model, I consider that CPEService is the “core” entity, so to speak, since this is where I think that the relationships between CPEs and Services, Switches or VLANs take effect.
It is very important to mention that, if I understand your specifications well, there is a “three way” relationship in which the entities Service, Switch and VLAN are involved. That is why I have modelled (a) the association between Switch and VLAN in a specific many-to-many relationship, (b) the association between Service and VLAN in another particular many-to-many relationship, and (c) another many-to-many relationship called SwitchVLANService, where the “connection” between these three entities actually takes place.
I have used natural PRIMARY KEYS as much as has been possible, in order to capture the meaning of every prime attribute and make it more evident not only in its original entities, but also when it migrates to related ones. If you feel comfortable with my proposed model, it will be more easy for you to determine the stability of each one of such KEYS, and decide if you keep them or replace them with different ones, since you are, of course, involved in the actual context where the database is going to be used.
Subsequent conclusions
By virtue of comparing our models, discussing the important subjects and clarifiying the pending points via comments, we have have been able to confirm most of the things that you have already defined in your model:

The “three way” relationship between Service, Switch and VLAN that I proposed in my preliminary is totally unnecessary, since what it is actually relevant in your context is stroring only what Service is broadcast by a particular VLAN. In this manner, a Service is related to a Switch by means of its association with a VLAN.
The Service.RateLimit, the SpeedProfiles defined for a specific VLAN and the CPEPort.RateLimit are all necessary, and you will manually decide which speed is set for a particular set of entities, since you have determined an overriding order for this aspectt.
CPEPorts are not directly related to Switches, but CPEPorts have a direct relationship with VLANs, since you have to set a specific CPEPort to receive a given Service that is supplied by a particular VLAN. In this way, as you have pointed out via comments, it is necessary to ponder the possiblity of dropping the Service - CPEport relationship that you have presented in your diagram.
The stability of the used PRIMARY KEYS has been identified.

This Network Provisioning Data model (also uploaded as a .PDF document to Dropbox), which is based on yours and also on the series of deliberations that we have kept over the last weeks, depicts these conclusions.
The Party entity
I have included the Party[1] entity in the data model since I think that it can be very useful to provide a means to represent both Organizations and Persons as a single entity, while also keeping their particular attributes in two corresponding separate entities. This scenario is an occurrence of what is commonly referred as supertype-subtype cluster (an exclusive one, in this case) in which Party has been defined as the subertype, and Organization and Person as its subtypes. You may find this answer of help as an introductory resource about this kind of structures.
Thus, your following comment provides a good oportunity for describing its usage:

I quite like the Party table, though I don't think MySQL supports a compound FK in a single column? I'll, most likely, use a single table for both types of customers. Might also use two child tables, to contain the different info for each type of customer. With a 1-0 relation to the customer table.

These are very relevant points that you have brought to the discussion since, in accordance to the structure depicted in the new data model, it is not necessary to use a compound FOREIGN KEY in a single column, because Organization.OrganizationId and Person.PersonId are PRIMARY KEYS that, at the same time, serve as FOREIGN KEYS that draw their values from a corresponding row contained in Party.PartyId. This is an example of what Dr. E. F. Codd defined as Primary Keys on a Common Domain[2].
This way, as you have already skillfully determined, you can use a single table for storing the data pertaining to the role played by a particular Party (Organization or Person) as a Customer, so I have included the latter entity in the model. For instance, suppose that the MaximumOverdue for customers will be arrranged on an individual basis, so this datum should be stored in the Customer table. It is also worth mentioning that the Customer.CustomerId PRIMARY KEY column expresses the role name assingned to Party.PartyId in this specific entity and, as such, CustomerId always takes its values from the last-mentioned column.
Implementation
The follwing are some important aspects that must be taken into account when implementing exclusive supertype-subtypes relationships:

Each supertype row must always have one corresponding subtype row, that is, there should not be a single supertype row without one subtype counterpart stored in the database. This means that, in this particular scenario, you should make sure that there is not a single Party row without an Organization (or Person) row.
Every supertype occurrence must only be complemented by the correct subtype
instance, i.e., the value contained in the subtype discriminator attribute (which is Party.PartyTypeCode[3] in this case) has to be correct for the respective subtype row, and there should be no chance for a supertype to be supplemented by one row of the wrong subtype. In other words, you should create a method in order to assure that, when you have a Party that contains a PartyTypeCode value that indicates that such Party is an Organization, it is only complemented by a row contained in the Organization table, and never by a row contained in the Person table.

In order to cover these aspects in your database, I would highly recommend you inserting every supertype-subtype relationship instance inside a transaction.
Notes
1. A party is a person or group of persons (or people) that compose a single entity in a legal sense, so this term is useful to represent either an Organization or a Person in this particular business domain.
2. Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 25-26). Boston, MA, USA: Addison-Wesley
3. Since a party has only two possible subtypes, you can remove the PartyType entity and the subsequent Party.PartyTypeCode column, and then handle the Party subtype discriminator by means of a BIT column, which you could possibly denote as IsOrganization or IsPerson.


Is there a way to limit the memory buffer pool for a database?

Is there a way I can limit AltDB to only use a maximum of 10GB.

No, not even with Resource Governor.

From BOL

  The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query.

A good article to read : The clock hands of the buffer cache

  My goal is that more memory is used by MainDB.

Another alternative is to have MainDB on a different instance capped by Max Memory. This way you can reduce the max memory for AltDB.


SQL ServerRecovery Model Change Tracking

It is recorded in the SQL Server Error Log.

exec xp_readerrorlog 0, 1, N'setting database option recovery'

The result of that should have Text of:

  Setting database option RECOVERY to FULL for database 'YourDatabaseName'.


Restore PROD database to QA with AlwaysOn

what are my options to get rid of the old secondary target database?

You can create linked server and use Execute At LinkedServer. This is what I am doing in my current scripts when I have to refresh AG env in Dev / QA from PROD.

You can use powershell, etc .. but Execute At LinkedServer just works fine for my needs.

  anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Refresh scripts to downstream environments are specific to company environment esp. the post restore process like desensitizing or masking data, etc. So, I would say, invest some engineering time and make the process repeatable that will save you a lot of time in future.


How to Sync a MySQL DB with a Oracle DB?

Oracle GoldenGate might not be useful for you if Oracle source database is outside of your organization with no possibility for you to set up extract process.

The best for you is to have a look at this old post "Migrate from Oracle to MySQL" that despite its title points out good ways to do what you're looking for.

I found especially useful the below points from David Mann answer to the above mentioned topic:

There are 3rd party utilities available. I have used a few of these
but best for you to check them out yourself for your intended purpose.
A few 3rd party products are listed here: OraFaq . Unfortunately a lot
of them run on Windows which would slow down the data unload process
unless your DB server was on windows and you could run the load
utility directly on the server.
If you don't have any complex datatypes like LOBs then you can roll
your own with SQLPLUS. If you did a table at a time then you can
easily parallelize it. Topic has been visited on this site probably
more than once, here is an example: Linky


According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO.

Edit:

If you were to run the query to get the list of servers in SQL Server 2000, you'd receive a message like this:

Msg 6825, Level 16, State 1, Line 1
ELEMENTS mode requires FOR XML AUTO.

That would come after you found that sys.servers doesn't exist in SQL Server 2000.

This is the query you'd need to run to get the data you want. I've updated the column names because SQL Server 2000 doesn't like duplicated column names:

SELECT
    srvname AS [TD1]
    ,srvproduct AS [TD2]
    ,providername AS [TD3]
    ,datasource AS [TD4]
    ,isremote AS [TD5]
FROM dbo.sysservers
ORDER BY
    isremote
    ,srvname
FOR XML AUTO, elements

That query works in 2000. From there, I think it's a problem with the nested query not working in 2000 as it's expected to in 2005+. If I have some extra time, I'll play with it some more.

Edit edit:

As I had suspected, but couldn't confirm, FOR XML doesn't work in a subquery in SQL Server 2000. This page has a post from Erland Sommarskog stating as much, and he directs the person who asked a similar question to this Books Online page for SQL Server 2000 where you can see the very first point states

  FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other
  statements (SELECT INTO, assignment).

So, what you're trying to do in SQL Server 2000 can't be done.


When you use a column from a table on the "wrong"* side of an OUTER join, the join usually  becomes equivalent to an INNER join.

* "wrong": right side of a LEFT join and left side of a RIGHT join.

So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this:

SELECT cre.id,
       rer.idChambre                 -- to actually see NULL
                                     -- where there is no match
FROM   reserver AS rer
       INNER JOIN reservation AS ron
         ON rer.idReservation = ron.id
       RIGHT JOIN chambre AS cre
         ON rer.idChambre = cre.id
         AND (ron.dateFin < ?
             OR ron.dateDebut > ?
             OR (ron.dateFin IS NULL AND ron.dateDebut IS NULL)) ;

Another issue is the = NULL that have to be converted to IS NULL. Nulls are tricky and are not equal to anything, not even to themselves.

It seems it wass not very clear what exactly you wanted. Your issue - as far as I can understand from the question and the following comments - is that you want:

Find all rooms (chambres) that are not reserved for the specific period (4 April 2015 to 4 May 2015)?

Then your query could be rewritten like this:

SELECT cre.id
FROM   chambre AS cre
       LEFT JOIN
                 reserver AS rer
                 INNER JOIN reservation AS ron
                 ON rer.idReservation = ron.id
                 AND (ron.dateFin > '2015-04-04 00:00:00'
                    AND ron.dateDebut < '2015-05-04 00:00:00')
       ON rer.idChambre = cre.id
WHERE
    rer.idReservation IS NULL ;

See the SQLfiddle-2 for various other ways to write it (Please avoid, RIGHT joins as much as you can. They confuse many developers who are used to work with LEFT outer joins only.


MySQL Best approach for recording last update timestamp for every column and every row

COMMENTARY
I love this question because I answered another question like it 7 years ago

June 30, 2013 : Get the Time of Last update of a column

You actually wrote the SQL I had suggested. I am impressed.
YOUR ACTUAL QUESTION
IMHO I would go with Suggestion #3 because its implementation is as normalized as you are going to get. The retrieval of the deltas for any column would consume the least amount of memory in the InnoDB Buffer Pool, memory within the DB Connection, and the smallest footprint on disk.
The other suggestions would have so much redundant data that retrieving the delta info would most likely push your working dataset out of the InnoDB Buffer Pool frequently.
CAPTAIN'S LOG : SUPPLEMENTAL
Surprisingly, there is an alternative.
MariaDB has implemented all the above suggestions into a table metadata framework called a Temporal Table. What you are going to implement falls under the category "System-Versioned", which tracks the Change History of all table rows. The best part about "System-Versioned Temporal Tables" is that you do not need to explicitly create and track timestamps. That's done for you.
The only drawbacks I can think of are the following:

Small Learning Curve in Understanding MariaDB's SQL Dialect for Temporal Tables.
The timestamps are for the row not the column. This means a column may change or not from one iteration of a row's point-in-time to the next. You may need write some fancy SQL GROUP BY or DISTINCT aggregation to go through each row and stare at the column you wish to track or audit.


Oracle nologging impact for REDO Generation

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO.  Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL.

But it's usually not too difficult to track down the worst SQL statement.  Since you've already got AWR, look at the SQL Statistics section, ordered by Elapsed Time.  If there's a lot of REDO then one of those statements may stick out.  For example, if the top statement is an UPDATE, DELETE, MERGE (UPDATE), or an INSERT without a hint (or with a bad hint).

Possibly the top statements are all CTAS or INSERT /*+ APPEND */.  In that case, look at the explain plan for each of those top statements, for example by using select * from table(dbms_xplan.display_awr(sql_id => 'sql_id from AWR'));  If the execution plan says LOAD AS SELECT then it's using direct path inserts and is OK.  If it says LOAD TABLE CONVENTIONAL, that's the problem statement.

Possibly they are all ok, but the REDO is generated by the indexes.  The only way an index will not generate REDO is if the index is set to NOLOGGING and if the index is built after the INSERT.  Index NOLOGGING only applies to index DDL.  You may need to drop/disable and recreate/rebuild indexes to reduce REDO.

In my experience, the most likely causes of an INSERT statement not running in direct-path write are:

Foreign key indexes enabled on the target table, except when reference partitioning is used.
Bad hint syntax.  For example, if there's a space before the plus sign, like this: /* +.  Or if the hint is placed in a slightly wrong position, like this
INSERT INTO /*+ append */ ....
Weird data types that aren't supported.  I can't remember exactly, but I think with LOBs there are some extra limitations, like you may need to have partitioning.
Triggers on the target table.
There are likely some other reasons I'm forgetting.

Post the entire SQL statement and execution plan and we may be able to help investigate.

If none of that helps, than maybe it is an AWR bug.  15GB per second is very high, but not impossible.  I wouldn't be too surprised if an Exadata system could achieve that.


MySQL - how to use having in group by (SQL query)

The query has GROUP BY w.word so the COUNT(DISTINCT w.word) is - has to be - 1 for every group, so for every row in the results.

To get the results you want, you need to first GROUP BY product and then GROUP BY words:

SELECT g.words,
       g.wordcount,
       COUNT(*) AS productcount
FROM
    ( SELECT GROUP_CONCAT(DISTINCT w.word) AS words,
             COUNT(DISTINCT w.word) AS wordcount
      FROM productwords pw JOIN words w ON pw.wordid = w.id
      WHERE w.word IN ('lenovo', 'laptop', 'computer')
      GROUP BY pw.productid
      -- HAVING COUNT(DISTINCT w.word) >= 2
    ) AS g
GROUP BY g.words, g.wordcount ;


Pass (comma separated) -yet single parameter to cursor

The simplest little dirty trick I know for this is xmltable(). With xmltable()you can convert a comma-seperated list of values into rows, for example:

select * from xmltable('1, 2, 3')

COLUMN_VALUE
------------
1
2
3

Notice how the column is named column_value automatically. This is an XMLType column, that you need to convert to be able to compare it with  your regular types, for example (column_value).getnumberval() or (column_value).getstringval()

Now using this, a cursor could be coded like this:

set serveroutput on
declare
  l_parameters varchar2(50) := '101, 102, 103, 104';

  cursor my_cursor (params varchar2)
  is
  select employee_id, first_name, last_name from hr.employees
  where employee_id in
    (select (column_value).getnumberval() from xmltable(params));
begin
  for c in my_cursor(l_parameters) loop
    dbms_output.put_line(c.employee_id || ': ' || c.first_name || ', ' || c.last_name);
  end loop;
end;
/

101: Neena, Kochhar
102: Lex, De Haan
103: Alexander, Hunold
104: Bruce, Ernst


Solve conflict between affinity mask and I/O affinity mask?

The 'affinity mask' and 'affinity I/O mask' configuration values of
  SQL Server should not conflict. Performance may suffer if you choose
  to affinitize a processor for both SQL Server worker thread scheduling
  and for I/O processing.

What is meant by the above statement is:

  DO NOT CONFIGURE SUCH THAT A PROCESSOR IS SET IN BOTH WORKER AND
  I/O MASKS!

It's stated in the documentation and is protected from a mistyped attempt by the error message and requirement to specify WITH OVERRIDE to force the change through. That was your warning that something bad could happen.

  Msg 5834, Level 16, State 1, Line 1 The affinity mask specified
  conflicts with the IO affinity mask specified. Use the override option
  to force this configuration.

IO affinity masking is a rarely used optimisation, which offloads IO completion activity to a dedicated CPU. When set, a hidden scheduler is created that handles only IO operations for that instance. If you configure identical CPU and IO masks, the two schedulers will battle it out for CPU cycles and cause chaos.

The masks you've set are creating exactly that scenario with 1111110 specified for both.

If your intention is the more common requirement to dedicate CPUs to particular instances on a shared server, use just the CPU affinity mask and leave the IO mask as 0 (disabled).

I've encountered a mis-configured server with matching masks only once. I was put in front of it because the logs suddenly started filling up with the message we all dread to see:

  I/O requests taking longer than 15 seconds to complete on file...

After resetting the IO mask and restarting, the server behaved perfectly and the error above was never seen again.

Useful references:

Books Online - affinity I/O mask Server Configuration Option.
CSS - How it works: IO Affinity Mask - Should I Use It?.


how to use fn_xe_file_target_read_file to read files over network

As Kin suggested in his comment, the F$ share is problematic since that only allows administrator access.  Although you've provided file-system access to the folders in question, the account does not have access to the share.  Create a share specifically for the xel files, and provide access to the SQL Server Service account on both the Share, and the folder.

Using the Universal Naming Convention name, or UNC, is preferable to accessing the file via a mapped-network-drive.  A mapped network drive just obfuscates the true location of the file, and you'll need to somehow create the mapping inside the profile for the SQL Server Service account (that's what Kin is suggesting with the net use command he shows in his comment.)

My SQL Server 2012 instance reads files across the network perfectly like this:

SELECT *
FROM sys.fn_xe_file_target_read_file(
    '\\machine\share\ErrorLogs\system_health_*.xel' --this is the UNC
    , null
    , null
    , null
    );


Should I separate frequently updated columns?

It seems what you want is a so-called Index-Only Scan for the query on the "index" page. That means that you have all the data (columns) that you select on the index page in the index you are using for this query. This makes it unnecessary to go to the table when executing this query.

Example:

SELECT name, basic, information
  from users
 where filter = something

(I just suppose you are using some filter).

If you create the following index:

 ALTER table users add index (filter, name, basic, information);

MySQL can execute the above query without actually reading anything from the table (just the index—hence, Index-Only Scan). In other words, the number of columns in the table does not affect the performance of this query.

You can recognize an Index-Only Scan by the presences of "Using Index" in the Extra column of the execution plan (explain).

If you update columns that are not part of this index, the index doesn't need to be updated.

Some links to my site http://use-the-index-luke.com/

Index-Only Scan (aka. covering indexes)
Update performance
Getting MySQL execution plans & Operations


Table structure-wise, you are not doing anything wrong that would slow down inserts:

You (presumably) insert in PRIMARY KEY order via the auto_increment value.
There are few secondary indexes on the table.  Both posted_by and posted_to are non-unique, which means InnoDB can use the insert buffer.

I would suggest looking at either configuration or hardware.  I would agree that 200ms is a lot, so it looks like there has to be some contention somewhere.


Queries using GIN index on f_unaccent() seem slow?

I have seen GIN indexes to generally perform much faster than GiST for these queries. Try this index instead:

CREATE INDEX price_item_occurrence_name_trgm_gin idx ON price_item_occurrence
USING GIN (f_unaccent(name) gin_trgm_ops);

Related:

Does PostgreSQL support “accent insensitive” collations?
Using ILIKE with unaccent and with only right end wildcard
How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
Trigram index for for ILIKE patterns not working as expected

All the basic advice for performance optimization applies. For starters, your table needs to be VACUUMed and ANALYZEd enough to be fast.

Configuring PostgreSQL for read performance


SQL Server is supposed to use all the memory.  If you want to reserve memory for other things set the max server memory setting in SQL Server so that it'll stop eating up all the memory.


What is not persisted in a database backup / restore? (SQL Server 2005)

Plans will be recreated at first run.

Otherwise, everything database level will be there except where it interacts at the instance level, such as:

replication
backup jobs/schedules
maintenance plans/schedules
login mappings to users

For a standard, everyday database, you've no worries.


Query and index optimization for a table containing 10 million records

The scan of itt is expensive:

So to take advantage of an index or partition using itt.ForwardFeed,
change

    where
      (convert(varchar(20),itt.ForwardFeed,23) >= convert(varchar(20),'2018-06-01',23)
AND     convert(VARCHAR(20),itt.ForwardFeed,23) <= convert(VARCHAR(20),'2018-06-30',23))

to

where itt.ForwardFeed >= '2018-06-01'
  AND itt.ForwardFeed <= '2018-06-30'

This should work whether itt.ForwardFeed is a Date or a varchar in ODBC Canonical YYYY-MM-DD format.


Optimizing mysql queries running on 50 million rows

There are useful tips in the mysql online documentation:

http://dev.mysql.com/doc/refman/5.7/en/optimization.html

It's worth reading about indexing, like James Green has suggested.

Also you may wish to use the keyword "EXPLAIN" to look at the query plan to find potential bottlenecks. This will highlight which indexes are being used in your query. To make sure you're query plan is correct you will need to keep updating index statistics on your database.

Hope this helps and let me know if you find anything!

Many Thanks,

Simon.


How to select data in a sub query only once?

If you LEFT JOIN the tables you can then check if the left ordno does not exist. Something like this:

SELECT o.ordno
FROM orders AS o
LEFT JOIN invoices AS i
ON i.ordno = o.ordno
WHERE i.ordno IS NULL;

I hope this helps you.


You should call a Microsoft licensing expert and talk to them. They would be best person to resolve all your queries. Microsoft licensing is bit comlex and varies per environment.

  I have 2 servers and I'm looking to have the first as a Primary and the second as a mirror (as a reporting server and DR server), from what I can see on Microsoft's comparisons I should be able to do standard but am I missing anything?

You can use standard edition in SQL server database mirroring but you cannot use Snapshot functionality to get report as Mirror server is always restoring and you need to create database snapshot on mirror to run reports but snapshot is enterprise only feature. If you use transaction log shipping with Secondary database in standby mode you can run reports although when log backups will be restored users will be disconnected.

  I understand I would need to manually promote the mirror in the case of DR, but would enterprise make it easier/faster?

Whether you need to manually promote mirror to principal or it does automatically depends on your mirroring configuration. If you have witness with automatic failover it will take care of automatic failover in most cases IF it is able to form QUORUM. Also database comes online after second phase of recovery in Enterprise edition but after 3rd phase in Standard edition. There are 3 phases of recovery analysis redo and undo.

PS: Please tell me if any further clarification required


Does a data type affect performance in a view?

To answer the general question first, yes, data type in views absolutely can affect performance. Let's mock up some data to illustrate a trivial example:

DROP TABLE IF EXISTS X_162798;

CREATE TABLE X_162798 (
NUM_VARCHAR VARCHAR(10),
NUM_INT INT,
[Status] INT,
IncludeInJobTotals as (CONVERT(bit,
case
when [Status]=(5)
  or [Status]=(3)
  or [Status]=(2)
then (1)
else (0)
end,(0)))
);

-- insert 1 M rows with 999990 of them having a value of 1 for IncludeInJobTotals
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_162798 WITH (TABLOCK)
SELECT
CAST(n AS VARCHAR(10))
, n
, CASE WHEN n <= 10 THEN 0 ELSE 5 END
FROM Nums
WHERE n <= 1000000;

UPDATE STATISTICS X_162798 WITH FULLSCAN;

-- index all the columns
CREATE INDEX IX_162798_NUM_VARCHAR ON X_162798 (NUM_VARCHAR);
CREATE INDEX IX_162798_NUM_INT ON X_162798 (NUM_INT);
CREATE INDEX IX_162798_STATUS ON X_162798 ([Status]);
CREATE INDEX IX_162798_JOB ON X_162798 (IncludeInJobTotals);

DROP VIEW IF EXISTS V_162798;

GO

CREATE VIEW V_162798
AS
SELECT
CAST(NUM_VARCHAR AS INT) NUM_INT_CASTED
, NUM_VARCHAR
, IncludeInJobTotals
, CONVERT(INT, IncludeInJobTotals) IncludeInJobTotals_INT
FROM X_162798;

GO

The following two queries return the same results but the performance is very different:

-- this query does a table scan and does an estimate based on density
SELECT *
FROM V_162798
WHERE NUM_INT_CASTED = '1';

-- this query does an index seek and does an estimate based on the histogram
SELECT *
FROM V_162798
WHERE NUM_VARCHAR = '1';

If I do the wrong type of data type conversion the filter is no longer sargable. It cannot use an index on the column and cardinality estimates offer suffer because the histogram cannot be used.

Does it matter in your case where you cast a BIT column to an INTEGER? We can filter on the IncludeInJobTotals and IncludeInJobTotals_INT columns in the view to run a few tests. Based on data type precedence we should expect that a BIT will be converted to an INTEGER in any comparison between a BIT and an INTEGER. SQL Server should be able to do an implicit conversion between them so I think that it will be difficult to find a performance problem.

Since we're dealing with a bit column there aren't that many possible test cases for simple filters. If I filter on either column of the view looking for rows with a 0 value I always get index seeks and a correct cardinality estimate:

-- all queries do an index seek and estimate 10 rows will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 0;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 0;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = CAST(0 AS BIT);

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = CAST(0 AS BIT);

If I filter on either column of the view looking for rows with a 1 value I always get a table scan and a correct cardinality estimate:

-- all queries do a table scan and estimate 999990 rows will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 1;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 1;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = CAST(1 AS BIT);

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = CAST(1 AS BIT);

Looking for out-of-range values works as expected too:

-- these queries do an index seek and estimate 1 row will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 2;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 2;

I wouldn't worry about performance for this type of data type change. It's hard to imagine a scenario in which you would run into performance issues. However, it is important to note that queries using the view can return different results depending on how you use the column. The BIT data type behaves differently from INT:

  Converting to bit promotes any nonzero value to 1.

Going back to the data type precedence list, a VARCHAR value will be converted to a BIT value. This means that one way to get different results is to filter or join on an out-of-range VARCHAR value:

CREATE TABLE TABLE_WITH_THE_NUMBER_2 (NUM VARCHAR(1));
INSERT INTO TABLE_WITH_THE_NUMBER_2 VALUES ('2');

-- this query returns 999990
SELECT COUNT(*)
FROM V_162798 v
INNER JOIN TABLE_WITH_THE_NUMBER_2 t ON v.IncludeInJobTotals = t.NUM;

-- this query returns 0
SELECT COUNT(*)
FROM V_162798 v
INNER JOIN TABLE_WITH_THE_NUMBER_2 t ON v.IncludeInJobTotals_INT = t.NUM;

Perhaps your application will never do this, but what do you want to happen in that scenario? I would focus on correctness here over performance.


to 1) Any other session will read the data modified by your transaction as it was before your "BEGIN" statement as long your transaction didn't commit. As soon your transaction committed, it will read the new value of the counter. The point being that others don't have to wait and will always see a consistent data base.

to 2), 3) Why don't you try it with "ACCESS EXCLUSIVE"? (see http://www.postgresql.org/docs/current/static/explicit-locking.html)

EDIT: If you dislike locking the whole table with an "ACCESS EXCLUSIVE" lock, you could also use an "Advisory Lock" (see section 13.3.4 in the link above).


Compare value from xml node that contains decimal

It looks like you've got the wrong path in your where predicate.  If I do this, it works:

IF OBJECT_ID(N'dbo.x', N'U') IS NOT NULL
DROP TABLE dbo.x;
CREATE TABLE dbo.x
(
    xm xml
);
INSERT INTO dbo.x (xm)
SELECT N'<Document>
   <PolicyId>0</PolicyId>
  <ChangeAmount>1.4-</ChangeAmount>
  <PolicyTerm>
    <OutstandingBalance>35.04+</OutstandingBalance>
  </PolicyTerm>
  <OriginalCollectionAmount>36.44+</OriginalCollectionAmount>
</Document>';

SELECT x.xm.value('(/Document/PolicyTerm/OutstandingBalance)[1]', 'nvarchar(20)')
FROM dbo.x

╔══════════════════╗
║ (No column name) ║
╠══════════════════╣
║ 35.04+           ║
╚══════════════════╝

Testing:

SELECT *
FROM dbo.x
WHERE (x.xm.value('(/Document/PolicyTerm/OutstandingBalance)[1]', 'nvarchar(20)') <>
     (x.xm.value('(/Document/OriginalCollectionAmount)[1]', 'nvarchar(20)')))

╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                xm                                                                                ║
╠═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ <Document><PolicyId>0</PolicyId><ChangeAmount>1.4-</ChangeAmount><PolicyTerm><OutstandingBalance>35.04+</OutstandingBalance></PolicyTerm><OriginalCollectionAmount>36.44+</OriginalCollectionAmount></Document> ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

In your code, Payload.value('(/Document/OutstandingBalance)[1]', 'nvarchar(20)') should be Payload.value('(/Document/PolicyTerm/OutstandingBalance)[1]', 'nvarchar(20)')


Close this Query window.
Click home button in the top left corner below the phpMyAdmin logo.

Click on the database ijdb, to RUN QUERIES on DATABASE ijdb
CLick the tab SQL which is second from left in the menu.
Enter the query and run it. It should work now.

Read this reference book - PHP & MySQL: Novice to Ninja


Need to change existing design to accomodate more detailed tracking of time

Can't see the attachment, but if I understand correctly, any one contact can only have one date. Each contact is made up of (potentially) more than one contact_services, each having it's own duration. In this case, a suggestion would be:

contact_table:
contact_id
time_stamp
duration (of the contact)

contact_service_table:
unique_id
contact_id
contact_service_type_id (or something along those lines)
duration (of the service)

Elaborate further if I am misunderstanding.


I would take a look at not just the SQL jobs that are running, but see if Windows (or antivirus) happens to be running something at that same time. I know McAfee has a tendency to run almost at random, and it can really drain my machine's resources.


Equivalent to PostgreSQL's Dollar-quoted String Constants for SQL Server

This is a PostgreSQL extension of the spec. There is nothing like it in SQL Server. The idea is that you can quote everything that doesn't have the $$[token]$$. If the optional token is absent it looks like $$. Thus the name, Dollar-quoted String Constants

  Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. It is particularly useful when representing string constants inside other constants, as is often needed in procedural function definitions. With single-quote syntax, each backslash in the above example would have to be written as four backslashes, which would be reduced to two backslashes in parsing the original string constant, and then to one when the inner string constant is re-parsed during function execution.

So you can pass something like this,

$$ SELECT foo FROM "mySchema"."myTable" WHERE bar='foo''bar'; $$

or, even this.

$OUTER$ SELECT foo FROM "mySchema"."myTable" WHERE bar=$$foo'bar$$; $OUTER$

And we do that frequently with modules like tablefunc

As a workaround, in SQL Server, you just have to be escape the single quotes and double escape the single-quotes in the single quotes. This is the standard method of literal-quoting.

' SELECT foo FROM "mySchema"."myTable" WHERE bar=''foo''''bar''; '

The '''' is what we're trying to avoid here. That's fugly.


For SSIS you can implement logging on the packages.  A good place to start looking would be at the ProcessInput and PrimeOutput steps of the components within the packages.  The PipelineComponentTime event will be useful to check for these steps, as it records the time spent in each of them.  You can read more about logging for SSIS here.

For SSAS you can use Extended Events to monitor the performance while the processing is being done.  The ProgressReportEnd and ResourceUsage events are two that you may want into look into examing during the times the cubes are processed, and well as the RequestProcessBegin and RequestProcessEnd events.  This link will further outline the use of Extended Events in SSAS.


Direct vs Linked Server Connection in Query Execution Time

In the left window, when you connect directly to FORBISPRD08:

SQL Server Management Studio sends a request to
FORNSQPRD02\NAV01, who has the data, and sends it back to
SQL Server Management Studio

In the right window, when you use a linked server

SQL Server Management Studio sends a request to
FORBISPRD08\BIS01, who sends a request to
FORNSQPRD02\NAV01, who has the data, and sends it back to
FORBISPRD08\BIS01, who forwards it on to
SQL Server Management Studio

See the difference? Same data might be sent, but there's more steps involved. When you need data, connect to the server that has the data.

Linked servers are like going to a bar, seeing someone you like, and then starting a chat with their friend instead. You might make progress - but it's not gonna be as fast.


If you are using csv , you can not do this. But if you are willing to use other tools like Microsoft SQL Server Reporting Services or Crystal Reports you may do this.

SSRS Chart Example, here another one with code example.

If you can not install Reporting Services Server or do not want to, you can still use it for basic reporting purposes. Take a look at here,Microsoft Reporting without SQL Server Reporting Services.

Note that , I gave you examples for reporting services since it is easy to use out of box and in most cases free. But there are a lot of other tools exists which may be more suitable/powerful for your use case.


How do I get full details of each person when joining tables

I think you are looking at this the wrong way around. You should be selecting from the client_consumer table and joining to the person table. This way the query is easily read and understood.

SELECT
    cc.id,
    consumer.full_name as consumer_full_name,
    client.full_name as client_full_name
FROM client_consumer cc
JOIN person consumer ON
    consumer.person_id = cc.consumer_person_id
JOIN person client ON
    client.person_id = cc.client_person_id


Storing Different Products and Joining on table Names

I'm not sure why you need different tables for different product details. I'm going to guess though, that it's because your product types are so different that they have very different attributes (clothing will have sizes: S, M, L whereas books will have have an indicator of hardcover vs. paperback, etc...). You might want to try a more generic solution, where you have a table of product attribute types and another table of attributes. Something like this:

product
-------
  product_id
  name

product_attribute_type
----------------------
  attribute_type_id
  attribute_type_name

product_attribute
-----------------
  product_attribute_id
  attribute_type_id (FK to product_attribute_type)
  product_id (FK to product)
  value

Then you can have data that looks like this

product
-------
ID | name
-----------
1  | Awesome pants
2  | Book of cool stuff
3  | 1337 software

product_attribute_type
----------------------
ID | attribute_type_name
------------------------
1  | waist size
2  | number of pages
3  | system requirements
4  | inseam

product_attribute
-----------------
attribute_type_id | product_id | value
--------------------------------------
1                 | 1          | 33 inches
4                 | 1          | 34 inches
3                 | 3          | Win7, 4 GB RAM
2                 | 2          | 703 pages

Getting info on a product is easy:

select product.name, product_attribute.value, product_attribute_type.name
from product
inner join product_attribute on product.product_id = product_attribute.product_id
inner join product_attribute_type on product_attribute_type.attribute_type_id = product_attribute.attribute_type_id
where product.product_id = $PRODUCT_ID;


MRG_MyISAM with myisampack tables blocking inserts

This might be a bug. In essence, it sounds like the Mrg_MyISAM definition must be using the structure of the first member of the UNION clause as the basis for the rest the tables in the list.

Before you report the bug, try reversing the order of the UNION clause so that all the compressed tables are first and the uncompressed one is last

CREATE TABLE `ah_sales` (
 `server` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `itemId` smallint(5) unsigned NOT NULL DEFAULT '0',
 `countof` tinyint(1) NOT NULL DEFAULT '0',
 `seller` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `buyer` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `saleon` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `buyer` (`buyer`),
 KEY `itemserver` (`countof`,`server`,`itemId`),
 KEY `seller` (`seller`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
INSERT_METHOD=LAST
UNION=(`ah_sales_4_2013`,`ah_sales_8_2012`,
`ah_sales_4_2012`,`ah_sales_12_2011`,`ah_sales_6_2011`,
`ah_sales_3_2011`,`ah_sales_12_2010`,`ah_sales_6_2010`,
`ah_sales_3_2010`,`ah_sales_11_2009`,`ah_sales_8_2009`,
`ah_sales_5_2009`,`ah_sales_2_2009`,`ah_sales_11_2008`,
`ah_sales_8_2008`,`ah_sales_5_2008`,`ah_sales_2_2008`,
`ah_sales_11_2007`,`ah_sales_9_2007`,`ah_sales_8_2007`,
`ah_sales_6_2007`,`ah_sales_5_2007`,`ah_sales_3_2007`,
`ah_sales_2_2007`,`ah_sales_1_2007`,`ah_sales_12_2006`,
`ah_sales_11_2006`,`ah_sales_10_2006`,`ah_sales_9_2006`,
`ah_sales_8_2006`,`ah_sales_4_2014`);

Then, see if the problem persists. Either way, you may have to submit a short or long bug report.

IMHO, running myisampack across all MyISAM tables is a good idea PROVIDED YOU ONLY SELECT FROM ALL THE TABLES. I do not believe it was meant to be used the way you are doing it (performing INSERTs when there is a mix of compressed and uncompressed). I do not think you will have this problem if all the MyISAM tables were uncompressed.


You can use string functions like INSTR(), LEFT(), RIGHT(), LENGTH() for this purpose.

create table market(id int, col varchar(100), exchange varchar(100));

insert into market values
(1, 'EOS/ETH',  'bittrex'),
(2, 'EOS/BTC',  'other'),
(3, 'EOS/BTC',  'bittrex'),
(4, 'XMR/BTC',  'other'),
(5, 'LTC/BTC',  'other'),
(6, 'DASH/ETH', 'other');

select *
from   market t1
where  right(t1.col, length(t1.col) - instr(t1.col, '/')) IN ('ETH','BTC')
and    exchange = 'bittrex'
and    exists (select 1
               from market t2
               where left(t2.col, instr(t2.col, '/')) = left(t1.col, instr(t1.col, '/'))
               and right(t2.col, length(t2.col) - instr(t2.col, '/')) IN ('ETH','BTC')
               and   exchange = 'bittrex'
               group by left(t2.col, instr(t2.col, '/'))
               having count(*) > 1)

id | col     | exchange
-: | :------ | :-------
 1 | EOS/ETH | bittrex
 3 | EOS/BTC | bittrex

dbfiddle here
As Rick James has pointed out on his comments you can use SUBSTRING_INDEX() and get the same result.

select *
from   market t1
where  substring_index(t1.col, '/', -1) IN ('ETH','BTC')
and    exchange = 'bittrex'
and    exists (select   1
               from     market t2
               where    substring_index(t2.col, '/', 1) = substring_index(t1.col, '/', 1)
               and      substring_index(t1.col, '/', -1) IN ('ETH','BTC')
               and      exchange = 'bittrex'
               group by substring_index(t1.col, '/', -1)
               having   count(*) > 1)

id | col     | exchange
-: | :------ | :-------
 1 | EOS/ETH | bittrex
 3 | EOS/BTC | bittrex

dbfiddle here


Upgrade Requirements
To upgrade an existing MongoDB deployment to 3.0, you must be running 2.6. If you’re running a version of MongoDB before 2.6, you must upgrade to 2.6 before upgrading to 3.0. See Upgrade MongoDB to 2.6 for the procedure to upgrade from 2.4 to 2.6. Once upgraded to MongoDB 2.6, you cannot downgrade to any version earlier than MongoDB 2.4.

http://docs.mongodb.org/manual/release-notes/2.6-upgrade/

One of the most important thing to remember after you are on 2.6 if you are trying to use WiredTiger as your storage engine, you do need to take a mongodump by connecting into current mongodb version and then switch to mongodb 3.0 with a different db path,

IMP: You will not be able to use existing DB path as previous storage on mmapv1

Command to use: mongod --storageEngine wiredTiger --dbpath

mongodump and mongorestore can operate against a running mongod process, and used to take a backup and restore the backup respectively.

http://docs.mongodb.org/manual/reference/configuration-options/

storage.engine
Default: mmapv1

New in version 3.0.0.

Specifies the storage engine for the mongod database.
Valid options include mmapv1 and wiredTiger.

If you attempt to start a mongod with a storage.dbPath
that contains data files produced by a storage engine other
than the one specified by storage.engine,
mongod will refuse to start.


MySQL Index for searching for NULL values in text fields

Yes, a prefix length of one will allow you to have a working index for looking up NULLs.

Here's the SQLFiddle: http://sqlfiddle.com/#!2/12bf3/1/0

The EXPLAIN says that it is using the desired index.

Of course you might want to try a larger test to make sure it performs as well in reality as you hope.


How to join the latest previous record with SQL

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists:

SELECT P.product_id AS product_id,
      ,P.deal_dt    AS deal_dt
      ,C.deal_dt    AS previous_deal_dt
      ,C.cancel_dt  AS previous_cancel_dt
FROM   product_shipping p
LEFT   JOIN product_shipping C ON C.product_id = P.product_id
                              AND C.cancel_reason_no IN (2, 4)
                              AND C.cancel_dt < P.deal_dt
WHERE  P.deal_reason_no IN (1, 3, 5)
AND    P.deal_dt BETWEEN '2014-04-01 00:00:00' AND '2014-04-11 00:00:00'
AND    NOT EXISTS (
   SELECT 1
   FROM   product_shipping C1
   WHERE  C1.product_id = C.product_id
   AND    C1.cancel_reason_no IN (2, 4)
   AND    C1.cancel_dt < P.deal_dt
   AND    C1.cancel_dt > C.deal_dt
   );

Assuming (product_id,cancel_dt) to be UNIQUE, else you need tiebreaker criteria to be unambiguous. This way you do not need GROUP BY, because only one (or no) row is left-joined.

Also assuming deal_dt and cancel_dt are supposed to come from the same cancelled deal.

This would be simpler with the window function lag(), but your version does not seem to support that according to the discussion under the duplicate post.


MySQL fulltext query is very slow | What I'm doing wrong?

Give this formulation a try:
SELECT ..., x.relevance
    FROM (
        SELECT  id,
                MATCH(...) AGAINST(...) AS relevance
            FROM products
            WHERE MATCH(...) AGAINST(...)
         ) AS x
    JOIN products AS pr  USING(id)
    WHERE pr.city_id = 1
    ORDER BY x.relevance DESC


Generate script for all stored procedures in Azure SQL database

The solution can be found below:

https://www.geekshangout.com/powershell-an-example-of-how-to-save-a-queries-results-to-a-csv-file/
https://www.reddit.com/r/PowerShell/comments/530jhx/csv_file_to_txt_file/

First save as .csv and later convert to .txt or .sql


How to test if a column definition allows nulls?

You can also look at sys.columns.is_nullable ...


If you just want to see the number of free slots, you can simplify your query using conditional aggregation:

select event_date,
       event_start,
       event_end,
       count(*) filter (where not status) as free_slots
from events e
group by event_date, event_start, event_end
order by event_date, event_start;

The filter() clause was introduced in Postgres 9.4. If you are still using an older version you can use a CASE statement instead:

select event_date,
       event_start,
       event_end,
       count(case when not status then 1 end) as free_slots
from events e
group by event_date, event_start, event_end
order by event_date, event_start;

Incidentally, status is not a very good name for the column. Something like is_taken or is_full would be better and likely cause less confusion.


Unable to save a stored procedure which contains a temporary table creation and update query

You have to specify LANGUAGE plpgsql rather than LANGUAGE sql.
The function does not consist of only SQL statements, and other than for PL/pgSQL, the SQL language validator parses the SQL statements and checks them for validity.


AlwaysOn Availability Groups on Secondary Replica

Out of curiosity, it is possible to place Server B on AlwaysOn and move it to another Server C Asychronous Read Only.

If it's the same databases in the already existing Availability Group, then the only way you could theoretically do this is by re-architecting your solution so that Server B and C were in their own cluster and utilizing distributed availability groups from Server A.

I would not do this.


Cluster Size Configurations for SQL Server at RAID Level

Think of the right answer in terms of the COALESCE command, which returns the first non-null value from a list. The prioritized list of recommendations are:

Your tested and proven settings. Some folks have enough time to do repeated benchmarks of their own applications on their own hardware. That's fairly unusual, though.
Microsoft's Fast Track Reference Architectures. These are very specific documented setups done in partnership with hardware vendors. They include everything from firmware versions to SQL config settings. Unfortunately, they're not available for all combinations of hardware. (Don't worry too much about the SQL Server version - SQL IO hasn't changed much since SQL 2000, with the exception of SQL 2014's new in-memory OLTP.)
Your storage vendor's SQL-specific guidelines. Major storage vendors like Dell, EMC, and HP provide detailed documentation for SQL Server as well as other database platforms. Make sure to get the docs for your exact make/model of storage - different models can have totally different recommendations.
General advice from some bozo on the web. Myself included - my work was quoted in one of the other answers. General advice just doesn't hold true for all combinations of storage hardware. This is a last resort.


Delete SQL Server 2008 R2 server logins mapped to offline databases

Here is a script to identify orphaned logins.

SELECT SL.name, SL.dbname
, 'USE [master];
ALTER LOGIN [' + SL.name + '] WITH DEFAULT_DATABASE=[master];
CREATE USER [' + SL.name + '] FOR LOGIN [' + SL.name + ']
WITH DEFAULT_SCHEMA = [dbo];' AS SQL_command
FROM sys.[syslogins] SL
LEFT JOIN sys.[databases] SD
ON SL.[dbname] = SD.[name]
WHERE SD.name IS NULL
ORDER BY SL.[name], SL.[dbname];

The full article can be found on http://sqlmag.com/database-administration/identifying-orphaned-logins


XAMPP - Error: MySQL shutdown unexpectedly

first suspect .. the usual - "windows happened" - Wednesday = patchday - there is potential risk that one of the updates "f**ked" things up - and in most cases a simple reboot should do it.

But if you tried with a second mysql server ... make sure that the port is free when you try to start this one the next time.


Group by data by month with selected fields not to be included in group by class

It doesn't make sense (even a as concept) to be able to SELECT non-aggregated fields that aren't part of the GROUP BY clause, because when you group on a field, you're literally condensing many rows into one row for each unique value of that field you're grouping on. So it wouldn't be possible to display a non-grouped (and non-aggregated) field because that field may have multiple different values within the grouping of the field in the GROUP BY clause, so it would not be possible to show two different values of the non-grouped field in the same row for the grouped field.
That being said, I think I understand the reasoning behind your question, which I assume is you're more so looking for a way to aggregate across a grouping and display that aggregation with the other non-grouped fields. This is possible to accomplish with window functions via the OVER and PARTITION BY clause like this:
SELECT no_inv, date_inv, SUM(total_unpaid) OVER (PARTITION BY MONTH(date_inv)) AS unpaid,
    SUM(total_paid) OVER (PARTITION BY MONTH(date_inv)) AS paid
FROM invoice
LIMIT 3

Please note the OVER() clause (and  window functions) was introduced in MySQL 8.0.


Find source of spids that show no hostname

Looks like I was just asking the question wrong, I found the answer under: How to get the client IP address from SQL Server 2008 itself?:

As Aaron mentioned in the comments, I had to use sys.dm_exec_connections to get the source IP. Here is the final version of the query I used:

SELECT s.session_id, s.login_name, DB_NAME(s.database_id) as database_name,
    s.host_name, s.program_name, c.client_net_address, client_tcp_port,
    c.net_transport, c.auth_scheme, s.login_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
WHERE s.login_name = 'MYSTERY_USER'
ORDER BY s.session_id


What is included in ALL permissions for functions in PostgreSQL?

The only permission applicable to functions is EXECUTE, so when you use ALL postgresql will try to apply all the permissions available for functions, and for now this is just "EXECUTE".

So it means exactly the same.

  EXECUTE

  Allows the use of the specified function and the use of any operators
  that are implemented on top of the function. This is the only type of
  privilege that is applicable to functions.

https://www.postgresql.org/docs/10/static/sql-grant.html

But be carefully if more kind of permissions for functions be implemented in new postgresql versions this can do wrong (but i can't see other than execute).


Forcing MS SQL Server to rollback on error

You should check for existing metadata rather than creating transaction logs with queries such as:

If OBJECT_ID('dbo.t1') is null
begin
    CREATE TABLE t1 (c1 int);
    print 'create';
end
else print 'nothing';

or

if not exists(Select 1 From sys.tables where name = 't1' and SCHEMA_NAME(schema_id) = 'dbo')
begin
    CREATE TABLE t1 (c1 int);
    print 'create';
end
else print 'nothing';

or

If OBJECT_ID('dbo.t1') is not null
begin
    DROP TABLE t1
    print 'drop';
end
else print 'nothing';
CREATE TABLE t1 (c1 int);

It is still recommended to use Transaction and Try/Catch.

Unless you are creating objects such as view, you don't need GO everywhere and this will work:

begin try
    begin tran
    DROP TABLE t1
    print 'drop';
    CREATE TABLE t1 (c1 int);
    print 'create';
    if @@trancount > 0 commit
    print 'commit'
end try
begin catch
    print 'rollback'
    Print ERROR_MESSAGE()
    if @@trancount > 0 rollback
end catch
select * from t1;

You will still see this error:

  Msg 208, Level 16, State 1, Line 17
  Invalid object name 't1'.

It is considered as an error in your query because an object does not exist and it will go to the catch block because T1 does not exists. There is no error with the Transaction itself.


Something that I've done in the past for searching emails by domain name is to reverse the email address and the domain. Then I can use the LIKE operator with a wildcard and the resulting query should be sargable. You can read this Brent Ozar post about why this is helpful. The short version is reversing our email address and search term will allow us to use indexes. I don't think you'll need to do this based on the table sizes you mentioned in the comments but just in case you're curious you can create indexes on temp tables.

Disregarding indexes on the temp tables reversing things would make your original query look like this:

Create Table #Testing
(
  emaildomain varchar(100) -- You can't index max and it isn't needed
);
INSERT INTO #Testing
VALUES (REVERSE('@myfreepaysite.com')); -- Reversed & prepended the @ sign
                                        -- to ensure full domain matching

Create Table #DataToCheck
(
  fullemail varchar(200) -- You can't index max and it isn't needed
);
Insert Into #DataToCheck
VALUES(REVERSE('rr1234@myfreepaysite.com')); -- reversed

Select Top 1
  REVERSE(d.fullemail) -- Changed
  ,REVERSE(t.emaildomain) --Changed
FROM #DataToCheck AS d
  INNER JOIN #Testing AS t ON d.fullemail LIKE t.emaildomain + '%'; -- Changed

That should be easier to read and perform better as well. Also note that I reduced the size of your EmailDomain and FullEmail columns. I did this because you can't index a VARCHAR(MAX) but more importantly you should scope your columns to the data sizes and types they'll actually contain.


Issue with Identifiers in Postgres greater than Oracle's limit while connecting over ODBC (DBLINK)

Creating a view in the Postgres database that assigns shorter names to the columns in question might be an option. It will not affect dependencies on the Postgres side while providing comfortably short column names to Oracle.

dbfiddle


Is it possible to change between SELECT column based on an "IF" statement? and use INNER JOIN variables?

SELECT (IF 'mapped_products'.'product_id' USE 'product_id_to_name'.'product_name' ELSE `non_mapped_products`.`product_name` )
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

can be re-written to:

SELECT COALESCE( product_id_to_name.product_name,  non_mapped_products.product_name )
FROM non_mapped_products
    WHERE EXISTS ( SELECT 1
                   FROM orders_history
                   WHERE orders_history.product_id = non_mapped_products.product_id)
LEFT JOIN product_id_to_name ON
    product_id_to_name.product_id = orders_history.product_id


Is there something to rescue from an Oracle Data Modeler design just with the 'logical' folder?

It could be possible to rescue the ERD - if the files in the "logical" folder are intact.

Perform the following steps:

Load the datamodeler, create a new model (name here: new_model), leave it empty, save it.

{1} Operating system/file manager: Give the new_model's "logical" folder a new name.
(Avoid name clashes with the rescued "logical" folder.)

{2} Operating system/file manager: Copy the rescued logical folder into the new_model's folder.

{3} datamodeler: Close the new_model.

{4} datamodeler: Re-open the new_model.  Right-click Logical Model, click Show.

Example session (Linux)

# Directory listing _after_ the (empty) new_model has been created and saved.

[oracle@vbgeneric Documents]$ ls
new_model  new_model.dmd  erd_one_to_many erd_one_to_many.dmd

[oracle@vbgeneric Documents]$ cd new_model

[oracle@vbgeneric new_model]$ ls
businessinfo          datatypes        dl_settings.xml.merge_conflicts.local  mapping  rdbms
ChangeRequests.local  dl_settings.xml  logical                                pm

[oracle@vbgeneric new_model]$ mv logical renamed_logical

[oracle@vbgeneric new_model]$ ls
businessinfo          datatypes        dl_settings.xml.merge_conflicts.local  pm     renamed_logical
ChangeRequests.local  dl_settings.xml  mapping                                rdbms

[oracle@vbgeneric new_model]$ cd ../erd_one_to_many/

[oracle@vbgeneric erd_one_to_many]$ cp -R logical ../new_model

[oracle@vbgeneric erd_one_to_many]$ cd ../new_model

[oracle@vbgeneric new_model]$ ls
businessinfo          datatypes        dl_settings.xml.merge_conflicts.local  mapping  rdbms
ChangeRequests.local  dl_settings.xml  logical                                pm       renamed_logical

# Close the new_model.
# Re-open the new_model. Show Logical


Deny access to one database, allow all the rest

I would highly suggest you not assign a login to db_owner just "to have access to" a database unless you want them to have "admin" privileges in that database.

You also have to consider what permissions the login has at the instance level first. Assigning a user to db_denyXXX will have no affect if the login is sysadmin or securityadmin privileges.

If we are talking about a "regular" login on the instance you prevent them from accessing the database by not mapping that login to a user in the database. If no database user exist in the database for the login then they cannot connect to it, as long as they do not have higher privileges at the instance level.


Elements inside a container: How to perform atomic operations on them?

I'd suggest adjusting your model and doing away with the trigger altogether.  While the example is somewhat mind-bending, your data model contains a duplicated dependency which serves as the root of your update anomaly problem.

T-shirts ( T ), Jeans ( J ) and Shoes ( S ) can be placed inside a Box ( B ) and they can have a Color ( C ).
Each Element ( E ) of the Box ( B ) MUST have the same Color ( C ) of the other.

Okay, so T → B, T → C, B → C.  That is, if we know the T-shirt, we know the Box, if we know the T-shirt, we know the Color, and if we know the Box, we know the Color.  Since Color is dependent on Box and Box is dependent on T-shirt, while perhaps not particularly intuitive, Color cannot be directly dependent on T-shirt since it is already dependent through a transitive dependency.  That is to say, if we know the T-shirt, we cannot know the Color ( the Color is NULL ), since we must first know the Box.
The existing trigger is actually attempting to compensate for a potential update anomaly a 3NF+ data model would mitigate.  If the color of a T-shirt is known at creation time, such a T-shirt could ( and likely should ) be immediately added to a box of the same color.  In this way, the need to change the color of all items in a box is performed by merely updating the color attribute of the box itself.
Also, unless there's good reasons to split T-shirt, Jeans and Shoes into their own tables, it might be very much preferable to just consolidate those to an Element table with and ElementType lookup attribute, but much like @dezso, I suspect this is not even kind of really about t-shirt, jean and shoe colors as they are arranged in various boxes.


Can you Identify the sql server name from sql backup file(.bak)?

You could run a RESTORE HEADERONLY on the backup file to read the contents and get the ServerName column.

For example:

CREATE DATABASE BLABLA
GO

backup database BLABLA
to disk = 'c:\temp\BLABLA.BAK'

RESTORE HEADERONLY FROM DISK = 'c:\temp\BLABLA.BAK'

Result:

With the servername column being the one you want for the backup(s) residing in the backup file.


There is nothing obviously wrong with the plan, so there is no obvious large optimization to be done. You are fundamentally doing a lot of work, and it takes a lot of time.

I would probably start by taking a step back and looking at your business case.  Why do you need exactly this output?  Could you perhaps "need" something easier to optimize?  Like, something where chunks slide past in their entirety, rather than individual rows slowly passing from chunk to chunk on their way oblivion?  Or maybe where partitions are defined by predictable timestamp slices, rather than as having exactly 1000 rows per partition?

You didn't give your version, but your fiddle uses 9.5.  If I use the latest version and turn on JIT (just-in-time) compilation, I get about a 15% improvement over JIT off.

If I build an index suitable for index only scans, I don't get much improvement, it is sometimes faster and sometimes slower (it also interacts with JIT).  But if everything were not in already memory, it might give a huge improvement:

create index  on measurements (device_id, process_id, timestamp, current);

If you are looking for more complex solutions, you could write a process which will connect to the database and LISTENs for new INSERTs, then updates the data by sliding rows out of one partition and into the next one using incremental calculations, rather than recomputing each partition from scratch.  This is similar to how moving aggregate works, except that that only works between frames within a statement, not between statements like you need. This should be very fast.  For strict accuracy, this would depend on each inserted row committing in the same order of their timestamp column--that requirement is hard to guarantee in general, but might be easy for your exact situation.


How to create a unique index with only certain values required to be unique?

In versions MySQL 5.7 and MariaDB 5.2+ you can use a (generated) VIRTUAL column to accomplish this. You defineucode as a virtual column and then add a UNIQUE constraint:

CREATE TABLE codes
  ( product_id  INT NOT NULL,
    code INT NOT NULL,
    ucode BIT AS (CASE WHEN code  = 11 THEN b'1' ELSE NULL END)
        VIRTUAL,
        -- PERSISTENT,    -- for persistent storage of the value in MariaDB
        -- STORED,        -- for persistent storage of the value in MySQL
    CONSTRAINT code_11_product_id_unique
        UNIQUE (ucode, product_id)
  ) ;

Test at dbfiddle.uk:

insert into codes
  (product_id, code)
values
  (1, 11),
  (1, 222),
  (1, 222),
  (1, 333),
  (2, 11),
  (2, 222),
  (2, 222);

✓

select * from codes;

product_id | code | ucode
---------: | ---: | :----
         1 |   11 | 1
         1 |  222 | null
         1 |  222 | null
         1 |  333 | null
         2 |   11 | 1
         2 |  222 | null
         2 |  222 | null

insert into codes          -- should fail
  (product_id, code)
values
  (2, 11) ;

Duplicate entry '\x01-2' for key 'code_11_product_id_unique'

select * from codes;

product_id | code | ucode
---------: | ---: | :----
         1 |   11 | 1
         1 |  222 | null
         1 |  222 | null
         1 |  333 | null
         2 |   11 | 1
         2 |  222 | null
         2 |  222 | null


The method of using 'innodb_plugin' instead of default InnoDB is a MySQL 5.1 feature. The InnoDB plugin of 5.1 is the default of MySQL 5.5, and thus, in MySQL 5.5 we see this information for the 'ignore_builtin_innodb':

  In MySQL 5.1, this option caused the server to behave as if the built-in InnoDB were not present, which enabled InnoDB Plugin to be used instead. In MySQL 5.5, InnoDB is the default storage engine and InnoDB Plugin is not used, so this option has no effect. As of MySQL 5.5.22, it is deprecated and its use results in a warning.

This would be true for MariaDB 5.5 as well.


You are, as per your tag, not using Oracle 12c.

The syntax id NUMBER DEFAULT t1_seq.NEXTVAL, is only valid from 12c onwards, so you get an error.

On Oracle 11 and prior, you want to use an insert trigger to apply the sequence's nextval to the primary key:

create or replace trigger binaries_ins
  before insert on binaries
  for each row
  when (new.id is null)
begin

  select binaries_seq.nextval into :new.id from dual;

end binaries_ins;


Is it possible to mix mongod version in a sharded cluster

It is possible to mix versions from adjacent major MongoDB server releases (for example: 3.6 and 4.0) for the purposes of upgrading, but in general a cluster should have consistent versions. Successive server versions are expected to add new features (additional data types, authentication mechanisms, protocol improvements, new commands, ...) that do not necessarily interoperate with every prior release. Upgrade steps and compatibility details are included in the release notes in the MongoDB documentation.

As part of the upgrade process for MongoDB 3.4+ there is now an intentional step of setting a Feature Compatibility Version (FCV) to toggle features that persist incompatible data changes while an upgrade is in progress. If you started with a MongoDB 4.0 deployment (rather than upgrading from a prior version), new 4.0 features will be enabled by default.

I highly recommend matching your mongo shell and command line tools with the version of your MongoDB server, and checking the compatibility for your driver.

  I cannot add a 3.4 shard into a 4.0 cluster

Correct. If the 3.4 deployment is currently an independent replica set, it would have to be upgraded to MongoDB 3.6 and then upgraded to MongoDB 4.0 before adding to a 4.0 sharded cluster.

If you were starting with a 3.4 sharded cluster, all components of the cluster would have to be upgraded to MongoDB 3.6 (including setting FCV to 3.6) before continuing with the upgrade to MongoDB 4.0.

  Reversely I cannot add a 4.0 shard into a 3.6 cluster

In theory you could downgrade a 4.0 replica set if you want to add it to a 3.6 cluster, but this is more challenging than upgrading. Any backwards incompatible data that has been persisted would have to be removed or updated.


how to deal with foreign keys with fixed values (on a db-table-basis)

The bottom line is that you can't define a foreign key constraint that uses anything other than just the columns in the primary key that you are referencing.

One of the ways that people sometimes use to consolidate keys across multiple tables is to use a GUID as the primary key.  If the tm_key in each of your tables was a GUID then you could impose the foreign key using declarative referential integrity without carrying the tm_db_tbl column in your some_client_table.

Failing that, your options are:

Forego declarative referential integrity and just ensure that you
have indexes that make your joins efficient.
Impose referential integrity procedurally.
Take ypercube's suggestion and find a more
efficient way to represent some_client_table.tb_db_tbl.


Any existing programs that track the origin of data in your database?

The answer to the "systems/practices" would be to include a column with all data that is imported that signifies its source.

As for whether the issue was caused by your cleaning process, you simply need to save or log the changes that were made to each row, or enable change tracking.  Any of these solutions will introduce overhead that will affect performance, so let's be careful out there!


High Log Space Used for model System database

I think you made a leap. If you change model to simple recovery, the log won't autogrow constantly, because that allows the log space to be re-used (and it is unlikely there is anything going on in model that would prevent that from happening). But, I don't know how you're using model. If it's being used to store objects and data that you want created in every database, maybe FULL is a better idea, and taking log backups will allow you to restore to a point in time. FULL is also a good idea if you create databases manually (using CREATE DATABASE or the UI, without attaching), and you want all of them to be in full or simple recovery by default, then that should drive your decision, not some observation about log space used.


On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?

The answer by kevinsky is one path towards a solution for you.

A different path for your specific issue is to create a second reporting (and maybe a third dev) server(s).  With copies of the production data.  Your reporting people and development people can do all kinds of stupid, and not impact production.

There are multiple ways of achieving this, depending on how fresh your report/dev data needs to be.

A common solution is a nightly backup and restore to the secondary server. If you need really current data (like no lag or one second) look into SQL Server Always On


First of all, if custId is the Customer's Primary Key, then there's no need to specify DISTINCT at all. Second, grouping and then filtering by the grouped key may be very inefficient when the filtering will likely render a small subset: filter first, then group. Third, I assume you don't require casting to varchar when removing the DISTINCT clause. Fourth, no need to include the alias of every selected field when there's only one table in the selection, it just makes reading the query more cumbersome!

This query can really benefit from indexes on EMailAddr and Phone.

This may perform better, especially if those indexes exist:

SELECT DISTINCT CustId
    , PriceClassID
    , Name
    , S4Future01 as First
    , S4Future02 as Last
    , Addr1
    , Addr2
    , City
    , State
    , Zip
    , Country
    , emailAddr
    , Phone
    , lastorder
    , snotetext
FROM Customer
WHERE
    EMailAddr IN (
        SELECT EmailAddr
        FROM Customer
        WHERE EMailAddr IN (
            SELECT EMailAddr
            FROM Customer
            WHERE EmailAddr > '' AND Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
        GROUP BY EmailAddr
        HAVING COUNT(*) > 1)
    OR Phone IN (
        SELECT Phone
        FROM Customer
        WHERE Phone IN (
            SELECT Phone
            FROM Customer
            WHERE Phone > '' AND Crtd_DateTime >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
            )
        GROUP BY Phone
        HAVING COUNT(*) > 1)
ORDER BY EMailAddr
    , Phone ASC
    , Name
    , Last
    , First ASC;


How to change pgAdmin 4 to display rows retrieved and runtime?

It's kind of a shame that there is no good solution to this. I found that adding EXPLAIN ANALYZE to the beginning of my query will display results. Some documentation can be found here.

Example:

EXPLAIN ANALYZE
select * from schema.my_table limit 1000

Yields:

"Limit  (cost=0.00..168.48 rows=1000 width=2644) (actual time=0.048..5.214 rows=1000 loops=1)"
"Append  (cost=0.00..13935338.46 rows=82710326 width=2644) (actual time=0.045..4.880 rows=1000 loops=1)"
"Seq Scan on event_analysis_vw  (cost=0.00..459263.46 rows=3764446 width=3186) (actual time=0.040..4.579 rows=1000 loops=1)"
"Seq Scan on dva_event_analysis_field  (cost=0.00..6640119.59 rows=43856549 width=2623) (never executed)"
"Filter: (is_deleted IS NULL)"
"Seq Scan on dva_event_analysis_field_archived  (cost=0.00..6046496.61 rows=35089331 width=2612) (never executed)"
"Filter: (is_deleted IS NULL)"
"Total runtime: 5.977 ms"


The problem was that the person who set the database to OFFLINE also set it to SINGLE_USER, and his session was still open and "using" the database, even if it was OFFLINE. When he closed that session we were able to drop the database.

Thx to everyone in the comments for helping.