This is basic sql
For your second query there is only 1 drug at the given day
for the rest enter Postgre and what you are searching for ijn plain woirds and you usually get a result
The secnd query wrks like this. A subquery grabs with a window functions the last update date for everys drug.
When the originbal table is joined with the drug name and date changed you get the latest price
Which will be sorted by price and grab only the 10 with the lowest prices
Schema (PostgreSQL v12)
CREATE TABLE table1 (
  "drug" VARCHAR(13),
  "date" Date,
  "new_price" FLOAT
);

INSERT INTO table1
  ("drug", "date", "new_price")
VALUES
  ('acetaminophen', '2020-01-09', '0.25'),
  ('oxycontin', '2020-01-10', '1.40'),
  ('valaxirin', '2020-02-10', '2.34'),
  ('oranicin', '2020-02-11', '1.54'),
  ('acetaminophen', '2020-02-12', '1.47');

Query #1
SELECT
     "new_price"
FROM table1
WHERE "drug" = 'acetaminophen'
AND "date"  < '2020-02-01'
ORDER BY "date" DESC
LIMIT 1;

| new_price |
| --------- |
| 0.25      |

Query #2
SELECT
    t1."drug",
    "new_price"
FROM table1 t1
INNER JOIN (SELECT
            "drug",
            MAX(date) OVER (PARTITION BY "drug"  ORDER BY "date")     AS max_date
        FROM table1
        WHERE
    "date"  <= '2020-02-10') t2 ON t1."drug" = t2."drug" AND t1."date" = t2.max_date
ORDER BY "new_price" ASC
LIMIT 10;

| drug          | new_price |
| ------------- | --------- |
| acetaminophen | 0.25      |
| oxycontin     | 1.4       |
| valaxirin     | 2.34      |

Query #3
SELECT
            "drug",
            MAX(date) OVER (PARTITION BY "drug"  ORDER BY "date")     AS max_date
        FROM table1
        WHERE
    "date"  <= '2020-02-10'
;

| drug          | max_date                 |
| ------------- | ------------------------ |
| acetaminophen | 2020-01-09T00:00:00.000Z |
| oxycontin     | 2020-01-10T00:00:00.000Z |
| valaxirin     | 2020-02-10T00:00:00.000Z |

View on DB Fiddle


The simple answer is yes, it will contain the transaction.  Also simply the backup contains anything that happens before the end of the backup, not as of the start.  The full backup actually contains the transaction log information as of the completion of the backup.  When  you do your restore any transactions that were not committed at that time get rolled back.

If you want to learn more about it you can research backup internals.

Here is a technet article on it and the Kin's answer to this question has a number of additional good links.


Replicating two PostgreSQL databases without using master db

As far as which server Bucardo should run on, if both of your servers have the same resources, I would just pick one, because in multi-master, with two servers, the bucardo daemon won't have anything to do in a situation where the other server is down anyway. When the other server comes back up, you should just be able to restart the bucardo daemon and have it sync from the server that didn't go down.

https://mail.endcrypt.com/pipermail/bucardo-general/2013-June/001882.html

If you need something with HA, then, I would ask specifically on the bucardo-general mailing list, which is a wealth of knowledge.

You also might find these resources useful for dealing with Bucardo, in addition to the mailing list and the documentation.

Bootstrap Bucardo Multi-Master

Bucardo Multi-Master for PostgreSQL

Conflict Handling with Bucardo


Variable problems in PostgreSQL with language plpython3u

It is necessary to declare a variable within the function. Add "global square" here:
[..]
AS $BODY$

global square

square ='ABC'+square
[..]

You will have to do the same for depthlevel.


As a_horse indicated, select min(date)... is sufficient.  The DECODE and multiple conversions just slow things down without accomplishing anything.  Even as parts of queries, you should eliminate this needless processing.

As Phil indicated, the displaying of dates is controlled by the NLS_DATE_FORMAT parameter and can be altered to display dates as you wish.  This does change how the queries run (unless there are missing format specifications or SQL injection is being done).  Change the display format like this:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';

When doing a to_date on a two digit year, use YY when you want the first two digits of the year to match the current year, but use RR when you want the first two digits to be 20 for years ending in 0-49 and 19 for years ending in 50-99.  See the SQL Language Reference for more information:

  The RR datetime format element is similar to the YY datetime format
  element, but it provides additional flexibility for storing date
  values in other centuries. The RR datetime format element lets you
  store 20th century dates in the 21st century by specifying only the
  last two digits of the year.

  If you use the TO_DATE function with the YY datetime format element,
  then the year returned always has the same first 2 digits as the
  current year. If you use the RR datetime format element instead, then
  the century of the return value varies according to the specified
  two-digit year and the last two digits of the current year.

  That is:

  If the specified two-digit year is 00 to 49, then

  If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2
  digits of the current year.

  If the specified two-digit year is 50 to 99, then

  If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2
  digits of the current year.
  If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

  The following examples demonstrate the behavior of the RR datetime
  format element.

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL;
Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL;
Year
----
2017


Permission for sequence in another schema

This can be done.
The column default for your serial primary key is typically defined as:
ALTER TABLE schema_a.tbl ALTER COLUMN tbl_id
SET DEFAULT nextval('schema_a.tbl_tbl_id_seq'::regclass);

Two options:
1. Move sequence (my preference)
to the public schema - or any schema with sufficient privileges:
GRANT USAGE ON SCHEMA public TO public; -- or: my_group

Moving the sequence to another schema is easy:
ALTER SEQUENCE schema_a.tbl_tbl_id_seq SET SCHEMA public;

Now you can grant USAGE:
GRANT USAGE ON SEQUENCE public.tbl_tbl_id_seq TO public; -- or: my_group

That preserves all references (incl. column defaults).
2. Function wrapper with SECURITY DEFINER
If you cannot move the sequence for some reason (can't think of one), you can alternatively wrap access to it in functions of your own with SECURITY DEFINER. Again, create those functions in the public schema (or any schema with sufficient privileges for your users):
CREATE OR REPLACE FUNCTION public.next_tbl_tbl_id_seq()
  RETURNS bigint AS
$func$
SELECT nextval('schema_a.tbl_tbl_id_seq'::regclass)
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = schema_a, pg_temp;
ALTER FUNCTION shop.f_deswap_name(text) OWNER TO owning_role;

Where owning_role is the owner of the sequence or any role with sufficient privileges. Similar function for currval() ...
Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.


How to optimize this select with join and order-by?

I have never used Aurora and there might differences with MySQL but there is a method that works very often in MySQL in similar issues, when the execution plan is not optimal, i.e. when it does the joins first and then has to do the ORDER BY of the big intermediate result set.

Instead of joining the 2 tables, we try to first LIMIT the results in a derived table and then JOIN back. This way indexes will be used for the ORDER BY - LIMIT and then it will only have to do N seeks (20 in this case) in the 2nd table:

SELECT
    m.*,
    u.administrator_group_id
FROM
    ( SELECT id
      FROM messages
      ORDER BY id DESC
      LIMIT 20
    ) AS mi
  JOIN
    messages AS m ON m.id = mi.id
  LEFT JOIN
    users AS u ON m.user_id = u.id
ORDER BY
    mi.id DESC ;

And a variation:

SELECT
    m.*,
    u.administrator_group_id
FROM
    ( SELECT mi.*
      FROM messages AS mi
      ORDER BY mi.id DESC
      LIMIT 20
    ) AS m
  LEFT JOIN
    users AS u ON m.user_id = u.id
ORDER BY
    m.id DESC ;

Try both and check execution plan and performance. In any reasonable hardware a query that just gets 20 rows from a table or two and uses indexes should be really efficient. In milliseconds range, not seconds or minutes.


Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?

TRUNCATE TABLE and DROP TABLE are both metadata-only operations.  They make changes to the system catalog, including which extents are allocated to a particular object.  All these changes are logged.  But the cost is very small, regardless of the size of the table.

The only thing that will create log records for each row is DELETE.


What happens at low level when I add a column to a table in PostgreSQL?

In Postgres nothing really happens.

It just adds a row to the system catalogs so that it knows that you added a column. It doesn't even touch the table data. So even on a table with a billion rows this would only take a few milliseconds once it could obtain the exclusive lock on the table.

Starting with Postgres 11, this even is the case when adding a NOT NULL column with a constant as the default value, e.g. add some_flag integer not null default 42

If the default value is a volatile function (e.g. current_timestamp), Postgres is forced to call this once for each row and put in the result of that. Then this would take a substantial amount of time to add the column with billions of rows.


There are two tools which regularly get "honourable mentions" in this category. They are:

http://www.liquibase.org/

and

https://flywaydb.org

They are specifically for database version control - Open Source, Apache licence.
I believe that it's good to go to the source (pardon the pun...) in these matters. A Google of "database version control" leads one to this post by Jeff Atwood (one of the founders of StackExchange). Be sure to follow the links to K. Scott Allen's series of articles. This is an excellent thread which pretty much sums matters up (i.e. version control your database (or its metadata!)).
You could also look at this by Martin Fowler which is a thought provoking article on database version control and software development generally.


How to Prevent heavy queries on one database, affecting the performance of other databases on same server?

I'll quote a few things from Postgres wiki, Priorities:

  Prioritizing users, queries, or databases

  PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system facilities to achieve what limited prioritization is possible.

  There are three main resources that PostgreSQL users, queries, and databases will contend for:

  Memory
     CPU
     Disk I/O

  Of these, disk I/O is commonly a bottleneck for database applications, but that's not always the case. Some schema designs and queries are particularly CPU heavy. Others really benefit from having lots of memory to work with, typically for sorting.

Consider this part in in particular:

  Are priorities really the problem?

  Before struggling too much with prioritizing your queries/users/databases, it's worthwhile to optimize your queries and tune your database. You may find that you can get perfectly acceptable performance without playing with priorities or taking extreme measures, using techniques such as:

  Improving your queries
  Tune autovacuum to reduce bloat
  Generally polishing your cluster's performance
  Avoiding use of VACUUM FULL. That eat lots of memory and take forever to scan, wasting disk I/O bandwidth. See the wiki page on VACUUM FULL for more information.

And especially this:

  Is CPU really the bottleneck?

  People often complain of pegged (100%) CPU and assume that's the cause of database slowdowns. That's not necessarily the case - a system may show an apparent 100% CPU use, but in fact be mainly limited by I/O bandwidth. Consider the following test, which starts 20 `dd' processes, each reading a different 1GB block from the hard disk (here: /dev/md0) at 1GB offsets (run the test as root).

... test follows ...

  ... which could be confused for a busy CPU, but is really load caused by disk I/O. The key warning sign here is the presence of a high iowait cpu percentage ("%wa"), indicating that much of the apparent load is actually caused by delays in the I/O subsystem. Most of the `dd' processes are in 'D' state - ie uninterruptable sleep in a system call - and if you check "wchan" with "ps" you'll see that they're sleeping waiting for I/O.

  Rather than assuming that CPU contention is the issue, it's a good idea to use the available Performance Analysis Tools to get a better idea of where your system bottlenecks really are.

So, I'd say that you should:

first, follow the advice above, perform some more analysis tests and make sure that the bottleneck is from the database you suspect and that it's caused by CPU contention (which may well be the case if your queries are so computationally expensive). Or it may be partially true and there's CPU contention but also room for query improvements.
follow the advice in the 2nd paragraph above (improve the queries, generally polish the cluster, check and optimize vacuum and autovaccum settings.)
You should also optimize the various memory settings (mostly work_mem) with different values for the clients that connect to this database that has different workload.
If after all those, the issue remains, and there are still CPU or disk IO contention or both, there are some options described in the linked Priorities page that you can try ("renice", prioritize extension, etc).
One of the suggestions there (that is certainly worth considering) is separating your databases into 2 clusters (instances), one for the computanionally expensive db and another for all the rest, with the usual workload. See Prioritizing databases by separating into multiple clusters, where it mentions the likely cleaner option:
Separate machines

  Your best option is to separate PostgreSQL instances onto different physical machines. They won't compete for resources at all.


Sqlite: strange behavor on index , about group by operation

It's probably because your index doesn't include the size field so it doesn't cover it so when aggregating over size with a GROUP BY the operation cost determined by the query optimizer thinks using a TEMP B-TREE is the better choice. See the Temporary Sorting B-Trees section of the "EXPLAIN QUERY PLAN" doc.
You can create an index on newtable that includes the size field as well and re-check the EXPLAIN. More information on a similar issue in this DBA.StackExchange answer.


What is the optimal way to upgrade production RDS instance?

Upgrading an instance in RDS means RDS will be physically migrating the database to a new instance, likely on a different physical host, so downtime would not be avoidable.  Migrating to provisioned IOPS would likely mean your data would be migrated to a new EBS volume (and the server might be migrated to a new instance as well with this change, depending on whether, internally, machines capable of accessing EBS volumes with provisioned IOPS are physically segregated from machines that aren't, so that they can be on a different class of network hardware) so downtime would again be inevitable.
There appears to be a way to avoid this disruption: a Multi-AZ deployment, which creates an invisible and inaccessible (to you) replica in another availability zone within the region.

In the case of system upgrades like OS patching or DB Instance scaling, these operations are applied first on the standby, prior to the automatic failover. As a result, your availability impact is limited only to the time required for automatic failover to complete.
— http://aws.amazon.com/rds/multi-az/

That should provide a quick and seamless migration path, though I have not had occasion to test this capability.  "Modify" in the console appears to allow you to convert an instance to Multi-AZ.  Presumably, this would result in brief I/O freeze as the instance is cloned, so I of course would recommend testing all of this functionality before trying it.
Alternately, RDS supports an internal mechanism that should allow you to emulate the "add slave; promote to master; switch clients" operation, and this also should allow you to achieve a near-zero-downtime conversion:

Create an actual RDS read replica of your database with the desired instance class
Wait for the replica to come online and be synched with the master
Modify the replica's configuration to add Provisioned IOPS
Wait for the replica to come online and be synched with the master
Verify that both systems have identical data using 3rd party tools
Disconnect your application from the old master
Verify matching binlog coordinates on master and replica to assure that all application writes have replicated
Split the systems with "Promote Read Replica" on the new replica in RDS
Connect your application to the new master

http://aws.amazon.com/about-aws/whats-new/2012/10/11/amazon-rds-mysql-rr-promotion/


MYSQL testing string field contains another altered string field


Compare SQL Server high availability methods

Database mirroring is the fastest HA method for SQL Server - you can expect as little as 10 seconds (maybe less) downtime when manually failing over. Perhaps 40 seconds when an automatic failover occurs.  Automatic failovers take longer since they rely on the "timeout mechanism" to detect a problem.  Manual failovers are very quick since you are initiating the process, thereby removing the timeout.

Mirroring consists of two entirely separate SQL Server instances, utilizing a "shared-nothing" approach.  This means none of infrastructure required by mirroring is shared by more than a single instance.  The mirrored database(s) served by the instances involved in the mirroring partnership typically reside on separate storage subsystems - the mirroring process sends each and every transaction from the primary to the secondary instance thereby providing two physically independent copies of each mirrored database.

If you have a requirement for automatic failovers without data loss, which I highly recommend, you will need a mirroring witness instance (this can be a SQL Server Express instance, by the way).  The witness acts as a quorum to allow SQL Server to determine which of the two instances will actually provide access to the mirrored databases.

Database Mirroring has been deprecated since SQL Server 2012.  What this really means is at some point in the future, Microsoft will remove Database Mirroring from the product.  The pertinent point is "at some point"; Microsoft is almost never explicit about when they will actually remove support for deprecated items.  One good example is they removed support for the antiquated RAISERROR @errno @errmsg syntax in 2012.  This syntax has been on the "deprecated" list since around the year 2000 - it took them 12 years to truly disable support for this syntax.  Database Mirroring is supported in the as-yet unreleased SQL Server 2016 product.  Presuming Microsoft removes mirroring support from the next version of the product, which may come out in 2018, you could expect to be officially supported by Microsoft running on SQL Server 2016 until at least 2021, since Microsoft typically provides support for at least 5 years after the product is released to manufacturing.

One downside to Mirroring is each client needs to be aware that there are two possible hosts for the database; and needs to be coded to handle that.  There is a special entry you can provide through the connection string, namely "failover partner", that helps automate some of that; however the client still needs to be coded in such a way that it will re-try transactions gracefully during the failover period.
SQL Server Clustering provides high-availability by allowing one of several instances to "serve" the databases residing on the clustered instance.  SQL Server Clustering relies on Windows Server Failover Clustering, and as such they are essentially one-and-the-same for the purposes of comparing HA methodologies.  The process of failing over a clustered instance consists of:

  a. Shutting down the currently-running instance.

  b. Moving resources (disks and network connections, etc) from the currently-running instance to the next-to-run instance.

  c. Starting up the SQL Server instance on the next-to-run instance.  This startup process is exactly like starting any other SQL Server instance; the recovery process must be ran against each database on the instance, including roll-back and roll-forward of any transactions that were in-flight at the time the prior instance shut down.

Because of the steps I outlined above, the process of failing over a clustered instance is generally slower than failing over a mirrored database.
Always On is the technology Microsoft is pushing as the replacement for Database Mirroring.  It is essentially a hybrid of both mirroring and clustering, providing both the faster failover speeds of mirroring along with the useful management aspects of clustering.  With Always On, clients do not need to be aware of the multiple instances of SQL Server involved; they simply "speak" to the virtual network interface.  Always On is a great technology, however it can be a bit daunting to setup, and it requires Enterprise Edition, among a number of other prerequisites.
HA at the hypervisor level - SQL Server does not have control over any aspect of this process.  If vMotioning a server causes its response time to slow, you may get client timeouts, etc.  If the SQL Server is part of critical infrastructure, which it almost certainly is if you are looking for HA, why would you take a chance on some process that SQL Server cannot understand?


Getting total number of counts for distinct entrys for the same id

First you need to group by ad_id and then group by visitor_ip
then select max for impression, view and click

so your query would look like

SELECT visitor_ip, ad_id,MAX(impression), MAX(view), MAX(clicks)
FROM ad_analytics
GROUP BY ad_id, visitor_ip;

NOTE : You should avoid using reserved keywords as column name.


Best-practice use of Resource Governor in SQL Server 2017

Supporting a hybrid workload requires more than just resource governance.

The docs call this scenario Real-Time Operational Analytics, and it's a bunch of features all working together.

First you need row versioning to ensure that the report users don't create blocking in the database. So set the database to READ COMMITTED SNAPSHOT or force the reports to run with SNAPSHOT isolation.

With locking solved, you then need resource governance and isolation to prevent the report users from using excessive server resources.  For many workloads the OLTP part doesn't need data file physical IO, but does depend on log file IO.  So physical separation of data and log can help prevent IO from reports from interfering with OLTP.  For resource governance I would start by capping the reporting workload's CPU, IOPS, Memory, and perhaps maxdop.

The next thing you need is to make the reporting workload less expensive.  For this use some combination of updatable nonclustered Columnstore indexes, filtered indexes, indexed views, and Columnstore compression delay.

And the final thing is that you need to use Query Store to monitor the workloads and provide visibility into the plans for the report developers.


I think it is because you are selecting from tableA.
I guess the right way to do this is:

insert into tableB(c1) values (AES_ENCRYPT(new.c1,'test'));

So, the trigger will become like this:

delimiter //
    CREATE TRIGGER trig1 before INSERT
    ON  tableA FOR EACH ROW
    BEGIN
          IF LENGTH(LTRIM(RTRIM(new.c1))) > 4 then
            BEGIN
            insert into tableB(c1) values (AES_ENCRYPT(new.c1,'test'));
            Set  new.C1 = RIGHT(LTRIM(RTRIM(new.C1)),4) ;
             END;
       end if;
    END;
    //
delimiter ;


What are the causes and solutions for mutating table errors?

The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example:

you insert a row in table A
a trigger on table A (for each row) executes a query on table A, for example to compute a summary column
Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it

This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees:

(i) that each statement is atomic (i.e will either fail or succeed completely)
(ii) that each statement sees a consistent view of the data

Most likely when you write this kind of trigger you would expect the query (2) to see the row inserted on (1). This would be in contradiction with both points above since the update is not finished yet (there could be more rows to be inserted).

Oracle could return the result consistent with a point in time just before the beginning of the statement but from most of the examples I have seen that try to implement this logic, people see a multi-row statement as a serie of successive steps and expect the statement [2] to see the changes made by the previous steps. Oracle can not return the expected result and therefore throws the error.

For further reading: "mutating table" on Ask Tom.

If as I suspect the cause of the mutating table error is a trigger, one way to avoid the error is to move the logic out of the trigger into procedures.


Postgres 9.2

The benefit of the new feature is two-fold. Columns of type json verify the validity of its content so that what's in the column is automatically valid JSON and you get an error if you try to write anything else to it.
And you have basic functions to create valid JSON on the fly from rows or arrays - which is a very common use-case.

I quote Andrew Dunstan on the pgsql-hackers list:

  At some stage there will possibly be some json-processing (as opposed
  to json-producing) functions, but not in 9.2.

I used that quote before under this related question on SO.

Postgres 9.3

.. finally brings a number of functions and operators. Check out the manual page for JSON functions.

Related answer on SO:

How to query for a specific element of json field.
How to support that with an index.

@Will put up blog post. See comment below.

Postgres 9.4

Be sure to check out the new jsonb type with a host of new functionality.

Above all, the decomposed, binary storage allows for smaller storage on disk and an equality operator for jsonb (unlike json), which makes a number of additional operations possible (like DISTINCT or a UNIQUE index).

Yet more functions have been added for both json and jsonb. json_to_record(), json_to_recordset() etc. More in the release notes.


Effects of turning the transaction safety OFF

The main disadvantage of high-performance mode is going to be your possibilities of failover.  Because the communication between the principal and mirror server is now asynchronous with high-performance mode, the only way to failover is with a forced failover and possible data loss.

If you had to do a forced failover to the mirror server, any transactions that weren't sent to the mirror server would virtually be lost.

The real use of using high-performance mode would be that the following two conditions are true for you:

You want to incur the least amount of database mirroring overhead on the principal
You don't care if there is data loss

You should read up on the following BOL reference on Asynchronous Database Mirroring.  It will answer all of your questions more in-depth.


Refresh postgres database on nightly basis

If you're ok with the development db being read only you could use replication using wal shipping. Instead of continuously shipping, do it on a daily basis.

See: 25.2. Log-Shipping Standby Servers

If you need it in write mode it makes things a bit more complex as you'll need to keep the prod copy in read and clone it.
If your storage subsystem support snapshots than it's trivial.

Btw, 90gb should clone pretty fast - how long does it currently take? What disks are you using?


what is the best practice when making changes to the the application and database?

That's a big topic. The tactics depend in part on how big a change you're making. The strategy is that every move to production breaks nothing. Loosely speaking, that means

you add "new things" to the database,
alter all application code to use the new things instead of the old things,
then, if prudent, remove the old things.

That equates to one to three moves to production. (I prefer three, with a prudent amount of time between them. Other developers might have more appetite for risk than I do.)

The most accessible online document might be Evolutionary/Agile Database Best Practices. It's relevant to some extent regardless of whether you follow agile practices. Look at Database refactoring and Agile data modeling first.

  would it make sense run the t-sqls that I applied in the dev to the production database?

That depends on what kinds of problems you uncovered, how you documented them, and whether the TSQL code is under version control. Writing TSQL is software development; all the principles of good software development apply.


What column type can store txid_current value?

I'm suspicious about your motivations, but I'd say you should either use bigint (which is the data type xid_current returns) or use xid (which is the internal data type for xid's). By way of example, see the following:
pagila=# create table xidvals as select * from txid_current();
SELECT 1
pagila=# \d xidvals
                 Table "public.xidvals"
    Column    |  Type  | Collation | Nullable | Default
--------------+--------+-----------+----------+---------
 txid_current | bigint |           |          |

pagila=# select xmin,xmax,txid_current from xidvals;
  xmin  | xmax | txid_current
--------+------+--------------
 358165 |    0 |       358165
(1 row)

pagila=# select attname, atttypid::regtype from pg_attribute where attrelid = 'xidvals'::regclass and attname in ('xmax','xmin','txid_current');
   attname    | atttypid
--------------+----------
 xmax         | xid
 xmin         | xid
 txid_current | bigint
(3 rows)


I'm not sure what you are saying here. Assume the following:

CREATE TABLE PARENT
( a int not null primary key );

CREATE TABLE CHILD
( b int not null primary key
, a int not null
,    constraint fk foreign key (a)
         references PARENT (a) );

insert into PARENT (a) values (1),(2);

-- What should the default value of a be?
insert into CHILD (b) values (1);

A foreign key is a rule that makes sure that you use a valid value for the column(s) involved. If you want a default value for a in CHILD you can add another constraint (I'm not sure why default values are considered to be constraints):

CREATE TABLE CHILD
( b int not null primary key
, a int DEFAULT 1 not null
,    constraint fk foreign key (a)
         references PARENT (a) );

Without that it is impossible for the DBMS to know what you mean when you don't provide a value for an attribute.

EDIT: Add info on how to retrieve last generated value

If you want to inspect what was generated in the PARENT table, and use that value for the CHILD table, you can use a function LAST_INSERT_ID():

CREATE TABLE PARENT
( a int not null AUTO_INCREMENT primary key
, c int not null );

CREATE TABLE CHILD
( b int not null primary key
, a int DEFAULT 1 not null
,    constraint fk foreign key (a)
         references PARENT (a) );

insert into PARENT (c) values (1);
insert into CHILD (b,a) select 1, LAST_INSERT_ID();

or

insert into CHILD (b,a) values (2, LAST_INSERT_ID());


SQL Server sys.dm_tran_active_transactions long running transaction worktable

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up.  If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.

Since you mentioned that you didn't see these in sp_WhoIsActive, it's worth pointing out that system session's are filtered out of that procedures output by default.  You can see them by passing an extra parameter:

EXEC sp_WhoIsActive @show_system_spids = 1;


Backup and restore SQL Server database with encrypted columns: what should I backup along with database?

You are using column encryption. This is not always-encrypted. When you backup your data base and restore it, you only need to recover the master key. Anything else will be restored through the backup file.
You can restore the master key with this command:
USE [Your_DB_Name]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Your_MasterKey_Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY


Add brackets with table, as Group is a reserved word for Group by clause in SQL Server.

SELECT GroupID,
GroupName
FROM [dbo].[Group]


Is DBCC SHRINKFILE better than DBCC SHRINKDATABASE?

A database can (and is) composed of multiple files. So shrinking the database would shrink all the files it's composed of, whereas shrinking a file is more granular (as Tibor pointed out). Therefore shrinking a database will always be at least as heavy or heavier of an operation than shrinking just a specific file of the database.
That being said, it's generally recommended against to shrink either because they're both heavy operations, generally nonsensical to do because if they grew to a certain size they'll likely grow back to that size soon anyway and a growth operation is also heavy.


Relationship from Orders to Shop and Supplier, or to general User?

Following are my comments:

Users info should be isolated from Other Entities like shop, Supplier etc
Association between User and Shop should be moved to User_Shop table
Association between User and Supplier should be moved to User_Supplier table
There should be association between Shop and Supplier to view the Order from specific Shop by Supplier in the table Shop_Supplier.
There should be a table namely Order_Fulfil to map the orders fulfilled by Suppliers
Not sure about the mapping between the Product and User. Ideally there should be mapping between shop and product and order and Product.


MySQL replication without baseline data import

If the binary logs contain absolutely everything that created that master (including grants, create database, create table, INSERTs, UPDATEs, and DELETEs), then yes it is possible.

The log position you need is not 0 or 1. It is 4 (See my post Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB)


Understanding Stolen and foreign memory usage between 2 app database servers

Stolen memory refers to memory that is being used for something other than buffer, that could be used for buffer if it weren't being used for something else.  See why SQL Server 2012 still needs the "Stolen Server Memory (KB).  To minimize the amount of stolen memory, one would need to optimize queries so that they use as little memory as possible.  For example, if there is an unnecessary sort, that may use some of this memory.  If it's a reporting server, my guess is you're just going to have to account for some amount of stolen memory.

Foreign memory refers to worker memory that is on a remote NUMA node.  See How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks).  When memory is foreign it takes a bit longer to access it, and the impact depends on your system and the speed of the components.  To minimize, you would want to make sure you have the NUMA configuration and Max Degree of Parallelism set correctly.


What you want can be achived in the following fashion:

WITH
c AS  -- This part of CTE for counting on table 1
(
    -- This select will just return ONE row
    SELECT
        sum(case when regdate = 1 then 1 end) AS c_status_yes,
        sum(case when regdate = 2 then 1 end) AS c_status_no
    FROM
        table1
),
u AS   -- And this one for table 2
(
    -- This select will also return just one row
    SELECT
        sum(case when regdate = 1 then 1 end) AS u_status_yes,
        sum(case when regdate = 2 then 1 end) AS u_status_no
    FROM
        table2
)
-- And you just put everything together
SELECT
    c_status_yes,
    c_status_no,
    u_status_yes,
    u_status_no
FROM
    c, u ;  -- This is a 1x1 CROSS JOIN, don't worry

NOTE: the little trick: sum(case when regdate = 1 then 1 end) is in fact counting how many times the regdate = 1 condition is met; when regdate <> 1 the case statement returns NULL, effectively not summing 1 in that case.

You can also use a count(*) filter instead of a sum(case when ...), although I am not sure this could already be done in version 9.3 (I've tested on 9.4):

WITH c AS
(
    SELECT
        count(*) filter (where regdate = 1) as c_status_yes,
        count(*) filter (where regdate = 2) as c_status_no
    FROM
        table1
)
, u AS
(
    SELECT
        count(*) filter (where regdate = 1) as u_status_yes,
        count(*) filter (where regdate = 2) as u_status_no
    FROM
        table2
)
SELECT
    c_status_yes,
    c_status_no,
    u_status_yes,
    u_status_no
FROM
    c, u ;

dbfiddle here


What is the request_id in sys.dm_exec_requests and how does it change?

Can someone tell me how, for a given session, this id can ever be more
  than 0? Is it possible to reproduce this with some sql?

The request_id other than zero indicates a connection with Multiple Active Result Sets (MARS) enabled.  With MARS, a single connection may have multiple active statements, with statement execution interleaved.

From SSMS, open a new query window connection specifying MultipleActiveResultSets=Trueunder "Options-->Additional Connection Parameters".  Then run the query below in the window.  You should see a non-zero request_id value.

SELECT *
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;


The answer to almost all questions about Powershell/SMO is to use SMOs object model less, and TSQL more.  EG

$dbs = Invoke-Sqlcmd "select name from sys.databases where state_desc = 'ONLINE'"

foreach ($db in $dbs)
{
  $dbName = $db.Name
  #. . .
}


MySQL: can deadlocks occur for a query not in a transaction

There are no "non-transactional" statements; in the absence of explicit transaction control by default a transaction simply starts at the beginning of statement execution and ends when the statement completes. As a result, any query will have to wait for a conflicting lock and might encounter a lock timeout or a deadlock.


If your RDBMS has an xml datatype, why would you not use it?

For example, in SQL Server, you should put XML data in an xml column, because:

The XML data can be compared to an XML Schema Collection, to give the functionality of typed XML.
The XML data can be shredded with XQuery later.
The XML data can be indexed with a number of different XML index types, which help the XQuery shredding.

As far as I can see, the only reason to use varbinary(max) over xml is if you expect to be putting more than 2GB of data into one field. (Of course, there may be other reasons, which I'm sure I'll learn from the posts here. :-).


PowerShell Pipeline Binding examples on SQL server?

Getting specific to SQL Server would require there be any cmdlets within SQLPS that actually use the ByValue or ByPropertyName binding. I only know of a few like those for the Backup or Restore, but don't really use them. I think the ones around Azure SQL might use them as well (e.g. Get-SqlInstance). So in the end this is really nothing to do with SQL Server itself, just the way PowerShell works...but still worth learning about I think.

The best way for you to see how they work is just use Trace-Command to look at the binding metadata as PowerShell does it. A simple example would be piping something to a cmdlet like Get-Date or Get-Service.

I am by no means an internal's guy when it comes to PowerShell. Up front from what I can find, ByValue is not actually used or maybe just has a lower precedence than ByPropertyName. If you look at use of this command on my local laptop:

Trace-Command -Name ParameterBinding -Expression {'mssql$number14' | Get-Service | Restart-Service} -PSHost

The output of this command shows a few things:

  DEBUG: ParameterBinding Information: 0 : BIND PIPELINE object to
  parameters: [Get-Service] DEBUG: ParameterBinding Information: 0 :
  PIPELINE object TYPE = [System.String]  DEBUG: ParameterBinding
  Information: 0 :     RESTORING pipeline parameter's original values
  DEBUG: ParameterBinding Information: 0 :     Parameter [Name] PIPELINE
  INPUT ValueFromPipeline NO COERCION  DEBUG: ParameterBinding
  Information: 0 :     BIND arg [mssql$number14] to parameter [Name]
  DEBUG: ParameterBinding Information: 0 :  Binding collection parameter
  Name: argument type [String], parameter type [System.String[]],
  collection type Array, element type [System.String], no
  coerceElementType

The one thing I get form this is that the ValueFromPipeline was not forced, PowerShell went with the ValueByPropertyName and bound the text string I passed in to the Name property.

If you want to see the full output of this:


How do I resolve issue with deleted table in SQL Server Database Diagrams?

This is a bug in Management Studio.

I was able to reproduce it three ways, on both 2008 R2 and 2012 tools:

Create a new diagram; create a new empty table (any name); delete the table; in the same diagram, try to create a new table with the same name.
Create a new diagram; create a new empty table; delete the table; create a new diagram in the same database, and while the first one is still open, create a new table with the same name.
Create a new diagram; create a new empty table; delete the table; create a new diagram in the same database; close the first diagram; in the second diagram, create a new table with the same name.

From this behaviour, I'm guessing it keeps a list of tables per database outside the context of a single database diagram, and when an empty table is deleted, the reference isn't being cleaned up properly. I believe the fix is to close all diagrams for that database, and re-open. I think it's lucky you were able to save the changes you made with no errors!

I would suggest reporting this on Connect if you feel it's really important. IMO, it seems unlikely it will be fixed because it's so minor, but you never know.


Can I import a specific vssettings file when launching SSMS?

I haven't played with themes, but you can set per-connection properties, including the color of the connection info bar at the bottom of the query tab. And these settings are saved.

If you have a query tab open, go to "Change Connection" (either the button in the button bar, or via the Query menu, top item should be Connection >, and when it pops up the "Connect to a Database Engine" dialog, click on the Options >> button. Go to the Connection Properties tab and the bottom option is Use custom color:. Check the check-box for it and click on the Select... button on the right.

Just use different colors for the different servers/environments.

Technically speaking, there is a means of programmatically interacting with SSMS: write a plug-in / extension. I will list some resources below that deal with this topic. I have not tried it yet myself, but it seems as though it might be a lot of effort for a one-time config update. On the other hand, if SSMS will not record the setting for 100, let alone 400, separate connections (not sure what the limit is), then maybe this would be worth the effort.

The art and science of creating SSMS 2012 add-ins – Part 1 – Laying the Foundation (this is a 3 part series with links to parts 2 and 3 towards the bottom; towards the bottom of part 3 are 6 links to other resources, the first of which I will duplicate as the next item)
How to write own add-in for SSMS 2012 (Final release version)
Automation and Extensibility Reference (for Visual Studio, but it should be very similar with SSMS, at least as of SQL Server 2012)


Is it possible to break down query time?

Not really.  But...

Use PHP's microtime(true) to give you precise times.  Then time trivial things such as SELECT 1.  This should give you a feel for the "network roundtrip time".  That is effectively added (once) to each SELECT that you do.

As for queries getting slower...  Look at whether the tables are getting bigger.  Look at the EXPLAIN plans to see what counts it estimates.  Look at the algorithms to see if they are O(1), O(N), or O(N*N), etc.

By "cluster" do you mean "NDB cluster"?  Or "Galera Cluster"?  Or something else?

Another tool is to do this:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

This gives a precise count of the number of rows (index or data) that need to be read/written to perform the SELECT.  Track that over time to see if it is growing due to data size growth (as opposed to something else making things sluggish).


Why are multiple COUNT faster than one SUM with CASE?

The COUNT(*) version is able to simply seek into the index you have on the status column once for each status you are selecting, whereas the SUM(...) version needs to seek the index twelve times (the total number of unique status types).

Clearly seeking an index three times is going to be faster than seeking it 12 times.

The first plan requires a memory grant of 238MB, whereas the second plan requires a memory grant of 650MB.  It may be that the larger memory grant could not be immediately filled, making the query that much slower.

Alter the second query to be:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c
WHERE c.Status = 'Approved'
    OR c.Status = 'Valid'
    OR c.Status = 'Reject';

This will allow the query optimizer to eliminate 75% of the index seeks, and should result in both a lower required memory grant, lower I/O requirements, and faster time-to-result.

The SUM(CASE WHEN ...) construct essentially prevents the query optimizer from pushing the Status predicates down into the index seek portion of the plan.


Database state set to an earlier state before creating new indexes

When you drop the index, the database is in the same state as before in terms of indexing. If you want to go back to a previous state then you'll need to get a backup for that time range. Depending on the requirements it's either just the FULL Backup or additional DIFFERENTIAL Backups or even a point-in-time recovery using the transaction logs.

But the question is: Why do you want to do it in the first place?

The benefit of any index should not be explained by runnig it against an older data set just to see that it would have been better in the past, too, but by explaining the effect itself. Either a Scan has changed into a Seek operation and/or the new index is more narrow than the one previously used and/or a bookmark lookup has been eliminated and/or the index allows for a better performing query plan.

Also, verify that the statistics are up to date before comparing old and new version to eliminate the side effect of a better performance due to better statistics.

The index comparison should be done using the same procedure against the same set of data (e.g. current data) and compare the actual execution plans.


Taking a snapshot is never a bad idea when making changes in a production environment.

That said, it should be safe to delete a replica in RDS as long as you don't have any service connected to it.


Ensure only one of the several records for a given item has a column with true value

You can use a filtered index to enforce uniqueness on id and is_primary when is_primary is 1.

An example here:

create table Computer(
    id char(20) not null
  , NIC_name varchar(100) not null
  , is_primary bit not null
  , constraint pk_computer primary key (
    id asc,
    NIC_name asc
    )
);

create unique index ix_one_primary on Computer(id, is_primary) where (is_primary=1);

-- these three are fine
insert Computer(id, NIC_name, is_primary)
values('computer1','foo',0);

insert Computer(id, NIC_name, is_primary)
values('computer1','bar',1);

insert Computer(id, NIC_name, is_primary)
values('computer1','foobar',0);

-- this one is not allowed
insert Computer(id, NIC_name, is_primary)
values('computer1','oops',1);


MongoDB explain winning plan shows "EOF"

Ok, I found out why. Apparently I was querying against the admin database. Always run use <db> guys


Why is MySQL using this index? Can I force it to use another?

No index will help with the WHERE clause -- part has OR, part has LIKE with an initial wild card.
So, the only hope for optimizing is to work on the ORDER BY.  However, that is risky -- it might finish after looking at 15 rows, or it might look at the entire table without finding 15 rows.  (Or some amount in between).
A single OR can usually be turned into UNION to good advantage.  But 2 ORs is too messy to contemplate.


From reading the documentation, it looks like DML on a synonym for a normal object will not be replicated. For example, you have a table called FOO & a synonym called BAR that points to FOO. An INSERT into FOO will succeed, but an INSERT into BAR will not, as it is a synonym for another table & not a real base table.

Test this in your dev environment to verify.


Strange sql behavior - last character is removed in the Select statement, but actual data has different length

It seems that the problem was an old version of PL/SQL Developer which our customers used. When they updated to the newer version, the problem has been solved.


adding node to existing availability group

I think this is normal behavior as only one cluster IP will be up at one time. When trying to force the secondary or third IP up you should get the error message you stated.


Error 9002 state 3 after RESTORE DATABASE

Please note that restore 'verifyonly' does not checks consistency of backup completely only a successful restore will guarantee that you backup is in consistent state. There is a option to use Continue_after_error can you please use that in your restore script.

    RESTORE DATABASE [RESTORE] FILE = N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8'
    FROM  DISK = N'0000008B.bak'
    WITH
        FILE = 1,
        MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8' TO N'F:\RESTORE.mdf',
        MOVE N'SharePoint_Config_3c0993c1866f419e9f8e906a920a6ae8_log' TO N'F:\RESTORE_log.ldf',
        NOUNLOAD,
        REPLACE,
        STATS = 10,
        CONTINUE_AFTER_ERROR

Please note that this is a temporary workaround if it works. You should get a fresh backup from production again. Also I highly recommend you to run dbcc checkdb on production database


MySQL: What field types for large numbers?

First, if you are wanting to use decimal points, you can scratch INT off your list.

Next, you need to understand that FLOAT and DOUBLE are similar because they store the value in approximate value, but that DOUBLE is 8-bytes, and FLOAT is 4-bytes. So for larger numbers you would want DOUBLE instead of FLOAT.

Now, the choice is between DOUBLE and DECIMAL (DECIMAL can have 65 digits). MySQL tells us that for DOUBLE columns, the value will be rounded to fit into the column size. It also says that for DECIMAL, the behavior is left up to the operating system. It will either ROUND or truncate the digits that don't fit into the column.

My suggestion is to choose the column type from the above description that you think meets your needs (either DECIMAL or DOUBLE), and insert some test data to see if the storage behavior is as you expect.

Also, if you are doing any comparison on these columns, make sure the comparison is as you expect: 2.145<>2.140


How Restore backup as overwrite on a database in use in SQL Server?

You can kill all connections this way:
USE [master];

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

Then you pass to single user mkode:
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

Then we create a backup:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
GO

Then we replace the database:
--Do Actual Restore
Restore Database [MyDatabase] From Disk = 'D:\Path\to\Backup.bak'
With Replace,
Move 'Data file logical name' To 'New physical path and name.mdf',
Move 'Log file logical name' To 'New physical path and name.ldf'';

And then we go back to multi user mode:
/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO


Understanding the given correlated subquery

zgguy's explanation is very good. It is also illustrated nicely in the execution plan (I have used SSMS2014 to generate one):

the inner query shows on the second "row"
nested loops means that for each row in the outer query, inner input in scanned and matching records are selected
filter (=3) is applied on records from previous step


In your first bullet you say the value becomes 00000010. That is incorrect. It actually becomes 000010 because of the RIGHT( , 6).

To clarify, compare the difference:

SELECT LEFT('00000010', 6), RIGHT('00000010', 6);

Results:

------  ------
000000  000010


MySQL: how to prevent deadlock on concurrent inserts

Both insert statements have the same values for the primary key which is why the deadlock occurred.

At the point of the deadlock, the other transaction could rollback. If the INSERT IGNORE did ignore, and the other transaction rolled back, then the definition of 'INSERT IGNORE' inserting if a record doesn't exist is violated.

This is why the deadlock is returned, because its a business logic problem that needs to be resolved by the application. The action to be taken in a deadlock depends on the situation that cannot be known upfront.


Whenever possible, you want to avoid the EAV design, which is basically what you are proposing.

EAV has it's place, but it's overused.  Essentially you are adding a layer of abstraction to your data by storing the meta-data (describing the actual data) in the data tables, then having to query the meta data before you can actually get at the real data.  It gets incredibly complicated very quickly.

For your (relatively simple) use case, you should make a "normal" table along the lines of:

ReceiverID, ReceiverName, ReceiverAddress, ReceiverBirthdate...

As long as you have a couple of MAIN fields (I'm assuming you will want at a minimum an address and an ID), the others can be populated or NULL depending on if you have data for those.

There's no need to use an EAV model for something so straightforward, especially if you  want to use it just to account for missing data.


Use Workbench to migrate the data:

https://dev.mysql.com/doc/workbench/en/wb-migration-database-access.html


SQLPlus Command Line In-line Set Commands

You should not put SET and SELECT in the same line, they are seperate commands. For example this works:

$ alias db='sqlplus -s user/password'

$ db <<< 'set heading off
set timing on
set echo off
select * from dual;'

X

Elapsed: 00:00:00.00

If you insist on using a one-liner, you could try this:

$ echo -ne 'set heading off\nset timing on\nset echo off\nselect * from dual;' | db

X

Elapsed: 00:00:00.00


sp_BlitzIndex draws on the missing index DMVs, and shows you indexes with an estimated daily benefit >100k. This comes from uses * impact * average query cost / days uptime. If a missing index request doesn't show up here, it may not break that threshold.

Missing index requests in query plans draw from the index matching portion of the optimization process. They don't pull from DMVs at all, but rather will log to the DMVs that sp_BlitzIndex pulls from.

If you want to look more closely at just missing indexes, run sp_BlitzIndex in @Mode = 3 for that database. It will show you all of them.

Full disclosure: I am employed by Brent Ozar Unlimited, a limited liability corporation, and I contribute code to sp_BlitzIndex.

Thanks!


As the years tick over will a member's level change?  Is it important to have history?  For Example Chris was Secretary while at level 100 and became President while in level 300?  If so you'll have to separate Level out of Members into a new table and make that the foreign key into Executives.


PMA export with all attributes in CREATE TABLE

I shows all changes you have made, as you can see in the example
Schema (MySQL v8.0)
CREATE TABLE `bar` (`id` int(11) UNSIGNED,PRIMARY KEY (`id`));
CREATE TABLE `foo` (
  `id` int(11) UNSIGNED NOT NULL,
  `fk_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `foo`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`);

Query #1
SHOW CREATE  TABLE foo;

| Table | Create Table                                                                                |
| ----- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| foo   | CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fk_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_ibfk_1` (`fk_id`),
  CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

View on DB Fiddle


How to select data from differents servers with a single query?

UNION is what you're looking for.

Select * from server1.tableS1.sys.view
union all
Select * from server2.tableS2.sys.view
union all
Select * from server3.tableS3.sys.view
union all
Select * from server4.tableS4.sys.view

You might want to do:

Select 'server1',* from server1.tableS1.sys.view
union all
Select 'server2',* from server2.tableS2.sys.view
union all
Select 'server3',* from server3.tableS3.sys.view
union all
Select 'server4',* from server4.tableS4.sys.view

... if you need to identify which rows came from which.


Would daily/weekly db memory cache clearing be of use?

Would clearing the database memory cache on a daily/weekly schedule be of use?

The straight answer is No. This would be a very bad idea, I suggest you don't do this.

  So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed? Would this be suitable case of FreeSystemCache/DropCleanBuffers

You probably need to understand how SQL Server memory works. SQL Server caches as much data pages as possible to avoid physical I/O, which of course is costly. If you clear memory used by database somehow, next day when it has to read pages it would have to bring it back into memory forcing I/O, perhaps lots of I/O on a busy system. Please note reading of database pages is always done in memory. I would also question your assumption about being sure that pages which are read today won't be, in any case, required tomorrow. This is not a correct idea.

It does seems like SQL Server is holding memory so how would it cater to new requests? This is simple, if your request needs pages which are already in memory, read would be done in memory. If the page is not there in memory I/O would be issued to bring it in memory and if space is not there in memory, least used pages would be flushed out to the disk to create space. This happens many a times on busy system without affecting performance.

It's costly for SQL Server to keep on taking and releasing memory because this process involves CPU and other resources; so SQL Server limits these by grabbing as much as it can in cache.

  long term would be better to look a reducing size of database/indexes

If you take my advice and considering you don't have unused indexes, it would be better to increase RAM on system and provide more to SQL Server. Yes, of course your database design must be of a standard which does not have redundant indexes, and properly sized data and log files with an appropriate autogrowth value.


Disk usage of integer column vs boolean column in Postgres

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen
---------+--------
 bool    |      1
 int4    |      4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign
---------+--------+----------
 bool    |      1 | c
 int4    |      4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
   b boolean,
   i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by the data in each row would only be 5 bytes.

The table row itself (the “tuple”) does not only consist on the raw data, but there is a 23-byte “tuple header” for each row (see the documentation). There may be padding after the header so that the actual tuple data are aligned at a multiple of MAXALIGN (typically 8).

So if you want to optimize your table to use as little storage as possible, you need to take the order of the columns in the table into account.


Does shrinking log file affects the indexing in SQL Server?

I will answer this specific part

  Does shrinking log file removes the indexing of database.

I believe you meant does shrinking of log file brings index fragmentation, not it does not causes index fragmentation. It definitely does not removes indexes I am not sure what made you asked this but rest assured no indexes would be removed.

  For other parts of your question

Shrinking of log file is as bad as shrinking of data file. The reason is, when you log file grows again after shrinking and autogrowth event is triggered which forces the process to stop until space is made available via autogrowth event. So now think this autogrowth happening frequently on large OLTP databases you can see how many a times a process would have to wait for autogrowth to complete. The best way is to presize the log file. Please refer Autogrowth article from Simple Talk. This will help you in presizing the log file and setting appropriate autogrowth value. Both are equally important.

If space is really a problem and you anyhow need it now and have no option you my shrink the log file but DON'T make it a habit. Find out process which forced it to grow more than expected and size your log file accordingly.

Some blogs for reading

Steps for better transaction log throughput

Transaction Log VLF too many too few


How to automate basic ddl scripts execution and management

Development Life-cycle requires at least:

A Code Repository (eg Git) to store your CREATE TABLE scripts
Some tool (eg Liquibase) to auto-generate the ALTER TABLE scripts
Unit Testing tools (SQL Developer has one built in)
DevOps framework (eg Azure DevOps) to automate

the build up of QA/TEST environment
deploy scripts to QA/TEST
run Unit Test on Test
deploy scripts to prod

A future version of Oracle SQL Developer might include Liquibase.


Can a database's files be copied while a database is online?

Why does it take 3 days to backup / restore a 2.1 TB database? If it is all about moving the backup, I'd suspect copying the MDF/LDF files would actually be slower since at least the backup could be compressed. And if it's just about restoring, then writing the copies of the file there shouldn't be any faster than the restore process itself - make sure you have instant file initialization enabled. Or get faster I/O on the secondary system.

Anyway, no, you can't copy the MDF/LDF files while the database is online, you'd have to take it offline to do so. And this is the absolute LEAST safe way to copy a database. If anything happens to the files while they're detached/offline, you now have zero copies of your database.

I would suggest looking into ways to make the backup/restore faster - whether it be ensuring you're compressing your backups and transferring the files in the best way possible, using the best I/O subsystem available, getting better I/O, making sure that IFI is enabled, etc.

Another option to consider is round robin log shipping - assuming production is in full recovery and you are taking regular log backups, you can perform log shipping to one database while developers work on another, and when you get to a stable point, restore with recovery and have the developers switch, taking their recent changes with them. Now re-initialize log shipping on the copy they just stopped working on, and it will be ready for them at the next "stable" cut-over, with no waiting.


The order is unreliable and is subject to change on different query evaluation plans.

However, I found a reliable hack around this. Please consider my presentation Programmatic Queries: Things you can Code with MySQL. In particular see slides 10 - 15.

The trick presented utilizes the known order of evaluation in a CASE clause, as follows:

SELECT
  CASE
    WHEN (@c3 := @c1 + @c2) IS NULL THEN NULL
    WHEN (@c1 := @c2) IS NULL THEN NULL
    WHEN (@c2 := @c3) IS NULL THEN NULL
    ELSE @c3
  END AS seq
FROM
  mysql.help_topic,
  (SELECT @c1 := 1, @c2 := 0) s_init
LIMIT 10;

The CASE expression has to evaluate the WHEN clauses by order of appearance (to be more specific, it does so, and it doesn't make sense to do otherwise).

In the above none of the WHEN expressions make for a true value, which is why the CASE clause keeps evaluating them one by one. This gives you your required order.


What are exact benefits of using R services on Enterprise vs Standard?

Check out the "Feature availability across editions of SQL Server Machine Learning Services" page of Books Online:

  The difference between basic and advanced integration is scale.
  Advanced integration can use all available cores for parallel
  processing of data sets at any size your computer can accommodate.
  Basic integration is limited to 2 cores and to data sets fitting in
  memory.

Books Online goes into more details on that page about deployment restrictions as well like:

  Only a (Standalone) server offers the operationalization features that
  are included in a Microsoft (non-SQL-branded) R Server or Machine
  Learning Server installation. Operationalization includes web service
  deployment and hosting capabilities.

  For an (In-Database) installation, the equivalent approach to
  operationalizing solutions is leveraging the capabilities of the
  database engine, when you convert code to a function that can run in a
  stored procedure.


How dangerous is it to have a user's password in plaintext in a cgi script?

You should always put application passwords in O/S Environment Variables (envvars).

Can only be read by the assigned O/S user (or root)
You won't accidentally screw up file permissions (if putting passwords in files)
You won't accidentally check passwords in to source control (this is particularly important in open source)
Survives reboots
envvars are easy to read with most languages
You should follow the principle of least privilege and run say your web server as its own user.

http://perldoc.perl.org/Env.html


SQL Server 2014 View any Live execution plan in Activity Monitor

You have to enable trace flag 7412

DBCC TRACEON (7412, -1)

According to blog post, you have to enable xEvent: query_post_execution_showplan

Warning, despite that this feature works starting SQL Server 2014 SP2, it can introduce significant overhead - up to 75%.:

Starting SQL Server 2016 SP1, this was changed and the overhead is only 1-2%
(in case of trace flag 7412) or 15% if query_post_execution_showplan xEvent enabled

In 2019 it is enabled by default

Details:

https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/

Permissions:

  Requires the database level SHOWPLAN permission to populate the Live
  Query Statistics results page, the server level VIEW SERVER STATE
  permission to see the live statistics, and requires any permissions
  necessary to execute the query.


What's the complexity of renaming a column in MySql?

It can be an O(1) operation if only the name is changing, see:

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

and search for:

  Renaming, Redefining, and Reordering Columns

That is in a section that explains that the operation only modifies table metadata.

Back in MySQL 5.5 the manual says it works there too, except for InnoDB tables.


How to determine why a MySQL server has become slow and requests sometimes time out?

You need to tune your database. This is a complicated process with many variables and decision trees. Without specifics we can only really get you started here.
If you have a script that runs lots of SQL and slows down your DB, and your cpu and memory usage are fine while it's running, then you are probably running into I/O problems. i.e. You are hammering the disk. You need to look through the SQL used in the script and tune it. For example, it might be generating lots of full table scans or creating too many transaction logs that are slowing down the DB. To start with, check if any unneccessary full table scans are taking place, and see if you can add indexes to the relevant columns to reduce the I/O. then report back and we can go from there.
The above linked MySQLtuner script is excellent but understanding the results may be challenging for you if you are inexperienced with databases. Do read the disclaimers on the main page before you get started: https://github.com/major/MySQLTuner-perl


In a command line, run:

attrib C:\Users\Pierpaolo\Documents

If you see a capital R in the output, then the read-only attribute is set for that folder (which is the default setting for the Documents folder).

Either remove the read-only attribute from the folder (I do not know what this will mess up in Windows), or specify a different folder for diagnostic dest.
For the latter, on this screen, go to All Initialization Parameters..., and set the value of diagnostic_dest to a directory that has no read-only attribute set.


Galera Arbitrator system requirements compared with the actual Galera MariaDB node requirements

Overall I'd say its best to widen the flow control and send/recv windows, and time-outs between nodes.

If you get down to one node, you can use set global wsrep_provider_options="pc.bootstrap=TRUE" to turn it into an active node without garbs. Lacks automation however is more durable overall.

The nature of the DoS might have an impact in the design. If the app server for the site is down is there sufficiently reduce load on the galera node? Or is the network throughput/latency compromised?

Recommending sticking with a 3 node cluster initially. Its a big step up from a one site.


You can set the Forced Parameterization option at the database level to avoid this issue, but it does have a drawback in that queries could now experience parameter sniffing issues. CPU utilization should decrease as a result of the change. I consulted on a system that had very high CPU utilization and very high compilations per second. Enabling Forced Parameterization dropped the CPU utilization by about 50%. Another system had minimal change in CPU utilization since their compilations per second wasn't that high. In both cases, the plan cache size decreased as queries were now reusing plans in cache rather than compiling a new query plan each time.

Alternatively, you could modify the application so that it is sending over parameterized queries/prepared statements instead of adhoc SQL. Or use stored procedures.


What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology?

The Always Encrypted feature provides end-to-end encryption, which encrypts data both in-flight, and at rest. Data is encrypted & decrypted on the client at the driver. Data is encrypted everywhere except for on he client machine. This is one step beyond encryption at rest (which is handled in SQL Server by Transparent Data Encryption).

Other RDBMS products do not have a feature which is quite the same. To do something similar on another RDBMS, you could perform encryption on the client within your application, and then send data in its encrypted format to the database.

Also, with deterministic encryption, SQL Server Always Encrypted allows point lookups, equality joins, grouping, and indexing on encrypted columns, which is often not handled by home-grown encryption.


Does this plan work as a Failover strategy-SQL Server 2016 Standard Edition

Do you think this plan will work?

Without a lot of headaches, no I don't. Most times I have witnessed an attempt to use replication for DR it has failed. Every time I've witnessed an attempt to 'fail-back' from a replication 'failover' it has failed miserably.

As you've mentioned, replication is not a HA/DR technology and shouldn't be used as such. If you're looking for a simple solution you can implement quickly, and you're going to log ship back to the primary for fail-back already, why not log ship to the secondary now and use an actual DR technology?

This gives you the option of performing tail log backups on the primary to leave it in a RESTORING state and makes fail-back much simpler. Your whole process is this -

Tail-log backup the primary and disable log shipping jobs on primary
Run the LSCopy and LSRestore jobs on secondary then apply tail-log backup to the secondary. Disable log shipping jobs.
Bring secondary Dbs online. Ensure a log backup job is running against the DBs on the secondary.
Point apps at secondary
Patch the primary
Tail-log backup the secondary
Apply logs and tail-log from secondary to primary
Bring primary online
Point apps at primary
Re-enable all log shipping jobs.

Lastly, if you're on SQL Server 2016 Standard Edition - implement Basic Availability Groups. They're available in Standard Edition, you're paying for an additional server anyway with Replication or Log Shipping, and the whole process is much, much simpler.

Since you have an outage for the apps to re-direct them to the secondary, why not take the opportunity to point them at an AG listener and eliminate this whole problem going forward?


Recover MySQL database on windows with ibdata1, ib_logfile and mysql-bin.00000 files

Basically, ibdata1 contains the tables metadata and change buffer and ib_logfiles are having innodb redo logs.

So the important thing is to being your ibdata1 first, the only way use any recovery software, or if you have MySQLdump then restore it.


You are close.  In SQL Server, you have to GROUP BY before you state HAVING.  Personally, I would do something like this

    SELECT COUNT(id) Surveys_w_2_LandOwners
    FROM surveys s
        JOIN
        (
            SELECT COUNT(1),property_id
            FROM owners_properties
            GROUP BY property_id
            HAVING COUNT(1) > 1
        ) p ON p.property_id=s.property_id


Identifying if spid on SQL Server is in rollback after low disk space

We use sp_whoisactive which has a column called Status. When a query is rolling back you can see status = rollback. It also gives a percentage completion.

We're using SQL 2014, but I'm pretty sure the status was there on 2012 as well


MySQL Group Concat one cell when other cells also match

You need to divide the rows into groups using GROUP BY. Something like:

select group_concat(product_no), date_sold, shift_start
from sales
group by date_sold, shift_start;

I assume you did something like:

select group_concat(product_no), date_sold, shift_start
from sales

The latter is invalid in most (all but MySQL?) DBMS products, but MySQL allows it by default.


Why do you want to partition? Is you performance not good, or do you have other reasons?

I don't see an index for the lastupdatetime column, and you wrote that you query for it a lot.

An alternative to partitioning could be creating tables for each year, and use merge tables to access the subsets you need.

Would have just left a comment, alas, not enough reputation.


How to change default db2 database admin username to something else in Linux?

It is not possible to directly rename the db2 instance name, But following steps can be used to move the db to new instance

  Take the backup of DB's and provide 755 permissions to backup images. Offline backup is recommended
  db2stop command to stop the instance
  drop current instance name by using db2idrop command
  create new instance by using db2icrt command
  once instance is created, switch to the instance id and  please start the instance by db2start command
  Restore the db to new instance name by using below command incase of auto storage
   db2 restore db  from  ON  NEWLOGPATH
  If there are user defined tablespaces, Which will need redirect restore
  db2 connect to

or Run below command to grant DBADM access to dbroot.

db2 " grant DBADM WITH DATAACCESS on database to user dbroot";


How to real-time sync local Postgres instance to Cloud Postgres

Londiste, Bucardo or Slony-I can do this.

See Replication, Clustering and Connection Pooling on the PostgreSQL wiki for details.

Londiste would probably be my first choice for this use case, mainly for simplicity.


Why does connecting to a database use tempspace?

During the connect process Oracle builds information on what access you have.  This remains static for the length of the connection.  (If you need to access newly granted privileges, you need to reconnect.)  Creating this data as temporary tables would seem the appropriate thing to do.  This would require temporary tablespace and could trigger the problems you were encountering.

The lack of locks, does not mean there isn't in-flight information floating around.  If the transaction inserting the data doesn't commit, then the database will need to track the block containing the row until the transaction is committed.  You can check for this by opening two connections and seeing if both can see the information from the login trigger before doing a commit.

There are views which will show what locks exist, and a wide variety of other information which is useful in managing the database.


There aren't any great answers here.

I don't know of any commercial database systems that store trailing zeros after the decimal, as these are considered insignificant to meaning.

However, in scientific measurement, those zeros can be significant, and knowing if something is 0.05 or 0.050000 is a major difference in precision of measurement.

One option is to store your number as a string/varchar value. This gets icky because now you have to do extra work to protect someone from saving a non-numeric value there.  That's more work for you, and more work for the database every time a value is stored. You can add a check constraint and use TRY_CONVERT() but in my opinion, this is trying to cram a numeric peg into a string hole, and that isn't quite right from a data model perspective.

A better option might be to simply store your measurement/value in a decimal column, then also store the precision in a separate integer column. When you insert your decimal value of 0.050000, you store 6 as your precision in a separate column. Then, when you consume the data later, your query will return a decimal value of 0.05, and precision 6 ... Which is exactly the data you need to know the original value is 0.050000. Here, there is still some extra work on maintaining your precision column, and it will need to be considered carefully on display (everywhere, including reporting), and in the case of maintaining scientific measurement you'll need to determine significant digits manually when performing math operations.


Mongodb creating historical cluster replicaset

MongoDB does not (as at 4.0) have a supported feature or tool for selective replication or syncing between two distinct deployments.

I would recommend upgrading from 3.4 to a newer version of MongoDB (ideally 4.0) and building a sync solution using the Change Streams API. You can choose which events to replicate to a historical archive or deployment based on the event type, namespace, or other criteria. Change streams are available for replica sets and sharded clusters using the WiredTiger storage engine and replication protocol version 1. MongoDB 3.6 is the first version to add the Change Streams API and includes support for watching individual collections. In MongoDB 4.0 the change stream support was extended to enable watching all non-system collection changes at a database or deployment scope.

If you are unable to upgrade from MongoDB 3.4 in the near future, you could also consider the approach of directly tailing the replication oplog. This would be a less robust solution than the Change Streams API, but there are some third party tools such as  mongo-connector that may be helpful. The Change Streams API also uses the oplog but adds a supported API, only includes majority-committed operations (that won't rollback), and scales to support sharded clusters. The oplog format is internal and subject to change between releases of MongoDB.


Plan guide validation with fn_validate_plan_guide gives false positives

Yes, this is a limitation of sys.fn_validate_plan_guide that can cause a false negative (not a false positive).

The server attempts to compile just the statement in the plan guide, not the whole batch (stored procedure in this case). The request fails because the temporary table definition is not part of the compilation.

I could not reproduce the same issue with a table variable on:

Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64)
    Mar  2 2016 21:29:16
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3  (Build 10586: )

The plan validation and guide application worked correctly.

Workarounds

Requesting an estimated plan for a call to the procedure succeeds, with the resulting batch plan showing the guided plan, with the properties of the statement root node showing the correct PlanGuideDB and PlanGuideName properties in SSMS:

An alternative is to create the temporary table (copied from the procedure definition) on the session that calls sys.fn_validate_plan_guide.

CREATE TABLE #test
(
    ID INT
);

-- Succeeds
SELECT
    PG.plan_guide_id,
    PG.name,
    PG.scope_type_desc,
    PG.hints,
    FVPG.msgnum,
    FVPG.severity,
    FVPG.[state],
    FVPG.[message]
FROM sys.plan_guides AS PG
CROSS APPLY sys.fn_validate_plan_guide(PG.plan_guide_id) AS FVPG;

DROP TABLE #test;

Either of these can be used to validate the negative result from sys.fn_validate_plan_guide when the error refers to a missing #object.


f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.

No. Dont do detach/attach. Instead use backup/restore.

If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.

I have detailed about pre and post steps for migration.

  How to migrate the user records with their passwords

Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.

  How to migrate the Linked Servers with their passwords

use Copy-DbaLinkedServer


'kill ' gives ERROR 1095 (HY000): You are not owner of thread ... even though I am

The error message is misleading. Galera can mark user threads as high priority which means they can't be killed. Currently, this gives the ERROR 1095 (HY000): You are not owner of thread... See MDEV-12008 for details.

This will probably affect other distributions of Galera as well, such as Percona XtraDB Cluster.


SQL Index order and performance based on cardinality and data

If ID1 is more selective than ID2, then construct your index over B with (ID1, ID2, Type_Code) , and make sure there is an index over A by (ID1, ID2).

If ID2 is more selective than ID1, then construct your index over B with (ID2, ID1, Type_Code) , and make sure there is an index over A by (ID2, ID1).

If you are using DB2 for i, then you might also try an Encoded Vector Index [EVI] over B on (Type_Code), in combination with an index by (ID1, ID2) [or the reverse order, as above].  However, the EVI seems unlikely to help in this case.


How to Variable table name in Insert into Table statement?

You're not quoting your strings properly.

When you insert a string, it has to be surrounded by single quotes, which in Dynamic SQL-ese, is three ticks. This should work.

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @TBL AS NVARCHAR(MAX);
DECLARE @Name_Edit NVARCHAR(MAX);
DECLARE @Name1 AS NVARCHAR(MAX)= N'B';
DECLARE @Price AS NVARCHAR(MAX) = N'12';
DECLARE @NameTBL AS NVARCHAR(MAX)  = N'KeyBoard';

SET @Name_Edit = N'A';
SET @TBL = N'TBL_Price' + @NameTBL;
SET @sql = N'INSERT INTO [DB1].[dbo].' + QUOTENAME(@TBL) + '
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             ''' + @Name_Edit + '''
            ,''' + @Name1 + '''
            ,''' + @Price + '''
            )';
              PRINT ( @sql );
            --EXECUTE sp_executesql @sql

When I print the code, I get this.

INSERT INTO [DB1].[dbo].[TBL_PriceKeyBoard]
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             'A'
            ,'B'
            ,'12'
            )


Since you're using Datastax Enterprise search nodes, solr is integrated with cassandra. What has happened here is that when you changed your solr schema, you did not change the associated cassandra schema. The error message is telling you that there is a mismatch between your new solr schema and the cassandra schema for the same table. Check the cassandra schema for seriesdata and make it consistent with the solr schema.


How to Import an Oracle dump version 5.1 into 12c?

Of course Oracle did not make this information public, but if you can access MOS, then go ahead:

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)

12.1 uses dump file sets of version 4.1. 12.2 uses dump file sets of version 5.1

You can not import a dump file created in 12.2 to 12.1, unless the VERSION parameter was specified to allow lower versions to import it.

You will need at least a 12.2 version database to import that dumpfile.


Find ClientHost using ClientIP - SQL Server 2014

Try the following (taken from https://stackoverflow.com/questions/15873060/how-to-get-machine-name-from-ip-address-in-sql-server-2008)

declare @IP as varchar(15)
declare @cmd as varchar(1000)
set @IP='xxxxx'
SET @cmd = 'ping -a -n 1 ' + @IP
Create Table #Output (Output varchar(150) default(''))
INSERT INTO #Output
EXEC xp_cmdshell @cmd
Begin try
Select top 1 Replace(LEFT([Output],CHARINDEX('[', [Output])-2),'Pinging ','') as HostName from #Output where Output like 'Pinging%'
End Try
Begin catch
Select 'Host name for:' + @IP +' could not be find'
End catch


Convert String date format in Jalali to Gregorian

Try this one:
ALTER SESSION SET NLS_CALENDAR = 'Persian';

SELECT TO_DATE(THETIME || THEH, 'YYYYMMDDHH24MI')
FROM dual;


How to generate replication snapshot without locking tables

The whole point of snapshot is to provide a point in time copy of the articles involved in replication. It does it when generating the snapshot files from the publisher by locking the tables until the snapshot is finished for that particular article.

You should plan to reinitialize during less activity time of your server.

  Is there anyway to do this without locking?

In SQL Server 2005, Concurrent snapshot is by default (applies to only PUSH subscriptions)

sync_method = concurrent

What it does is that it produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot.

  Not if the new snapshot should include schema changes. –  Kenny Johansen Sep 16 '14

Also, as per your comment, if you are doing schema changes (e.g. adding new columns) and want to replicate those across to subscribers, you can use this method.


Optimize query collecting all related items

with    cte
        as
        (
            select      CategoryID  as root_CategoryID
                       ,1           as lvl
                       ,CategoryID

            from        TB_CATEGORY_MASTER

            where       ParentID is null

            union all

            select      c.root_CategoryID
                       ,c.lvl + 1
                       ,t.CategoryID

            from                    TB_CATEGORY_MASTER  as t

                        join        cte                 as c

                        on          c.CategoryID = t.ParentID
            )

select     t.CategoryID

          ,(select      case when lvl = 1 then '' else ',' end
                      + cast (c.CategoryID as varchar(10))

            from        cte as c

            where       c.root_CategoryID = t.CategoryID

            order by    lvl

            for xml path('')
            )                   as AllRelatedCategory

from        TB_CATEGORY_MASTER  as t

where       ParentID is null

+------------+--------------------+
| CategoryID | AllRelatedCategory |
+------------+--------------------+
|          1 | 1,3,4,7            |
|          2 | 2,5,6              |
+------------+--------------------+


ORA-12700 to ORA-19400

  ORA-16857: standby disconnected from redo source for longer than specified threshold

  Cause: The amount of time the standby was disconnected from the redo source database exceeded the value specified by the
  'TransportDisconnectedThreshold' database property. It is caused by no
  network connectivity between the redo source and the standby
  databases.

  Action: Ensure that there is network connectivity between the redo source and standby databases, and the redo source is working properly.


MySQL: Alter Table on very active table gives "Waiting for table metadata lock"

You can achive nonbloking AlTER TABLE using tools like http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html and http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html

They are both tested in production and reliable. Percona's tool is very smart about checking slave lag.
Also checkout other percona tools, they are very handy.


Getting File not found error doing point in time database restore in SQL Server 2008

When you open that dialog, SQL Server populates that windows with a lot of backups for the history of backups taken for that database. This history is stored in msdb and is not synchronized with the file system, so it is possible that the backup files themselves were deleted  or moved already.

Instead of restoring from that history, select to restore from disk and pick the file(s) yourself.

Restoring a Backup

You need to first restore a full backup. That restore has to happen without recovery.

Then, if you have one, restore the latest differential between the full backup and the point in time you want to restore to. this also has to happen without recovery.

Finally, you need to restore all log backups between that differential (or the full if you didn't have one) and the point in time you want to restore to. Those log backups need to be restored in chronological order, also without recovery. The final log backup you can restore with recovery to bring the database back online.

SQL Server does not force a specific extension for any backup type. Most likely you will have .bak for full backups. Differentials might be either .bak or .dif. For log backups I have seen .trn, .log and others. Some people don't use extensions at all for their backups.

But it is probably a safe guess, that your .trn file is a log backup, so you will need additional backup files to restore that database.


Can I use Database Engine Tuning Advisor for Azure?

No. The error message is correct - it does not support Azure SQL. There is no SQL 2016 for "Azure SQL", but there is SQL 2016 on an Azure VM, which is the same as any traditional SQL Server installation (more or less).

That thread was confused, I assume, between SQL Server on an Azure VM and Azure SQL.

Using the Azure Portal you should be able to check out index autotuning.


As you said you want to do this at report level here is what i can think of:
You can try reducing the value in Height section in Interacivesize property of your report according to your needs,which is set to 11in by default and shows whatever records can fit in that much space :

Note: In case you have a small database and want to show all the values in 1 page itself setting the Height value to 0 will do the trick for you. Hope this helps.


The original question was:

  I want to add a column that gets the PRICE from the PRODUCT table and
  insert it to the ORDERS table according to the PRODUCT_NAME,
  obviously. How can I do it? Are there any workarounds? Please, keep in
  mind that I'm newbie and my knowledge is very limited.

and the ultimate reply (after much communication through comments - see above), my response was

  And you can't use triggers to make updating automatic? I'd say they
  are the way to go!

Which worked.


One alternative is to encrypt and decrypt in the app, and SQL server will of course have no knowledge that this data was born as anything else. Or, in SQL Server 2016, use Always Encrypted which means that the database API (ODBC, ADO.NET, JDBC...) does the job for you. Regardless of which solution, make sure that the DBA don't have access to the key. And, since the whole point is that the SQL Server never sees the clear-text data, you cannot manipulate using pure T-SQL. (In SQL Server 2019 will will have secure enclaves allowing for the database engine to decrypt the data to allow for things such as rotation of keys, LIKE, BETWEEN etc, but that isn't released yet.)


Numbered stored procedures: introduction and deprecation?

Looking at the SQL Server 2000 T-SQL help, there is no mention of deprecation so it seems numbered stored procedures were deprecated with the release SQL Server 2005.

I don't recall numbered procs ever not being in the Microsoft SQL Server product on Windows so I'm pretty sure the feature stems from it's Sybase roots and is in the 4.21a Windows release. I can't speak to the OS/2 version since I dodged that bullet :-)


Two Oracle ORDS on one web server accessing one database

This can be done.

Make a copy of the existing ords.war (call it, say, ords2.war) and just follow the same process that you would when creating your initial ORDS .war file.
Just be sure to use different configuration directories for each ORDS .war file in order to avoid possible collisions.

You name the two ORDS .war file differently in order to change the 'root'.  For example:

https://mywebsite.com/ords/...
https://mywebsite.com/ords2/...

has the 'root' of ords and ords2, respectively


This is an issue external to SQL Server. You should check that your authenticating AD servers for this machine are working correctly, and that you are not accidentally attempting to authenticate to AD in another data center (presuming that you have one). Additionally look for problems on the AD servers, such as high CPU utilization.
For more details on this wait type look at https://www.sqlskills.com/help/waits/preemptive_os_authenticationops/
This may also be due to inadvertent routing of authentication traffic to an AD domain controller in another data center as identified via a network trace. The trace will identify the DC(s) involved for further troubleshooting.


Dealing with single quotes in a dynamic query

You can escape single quotes by using 2 single quotes, that way your query would work. I'm not sure about the java syntax for a replace but your query should look something like this

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D''ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

instead of this

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D'ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

In any case, as has been suggested parametrized queries or stored procedures should be investigated (again, i'm not sure how to parametrize queries in java) .

You should also be very careful about SQL injection, what happens for example if someone enters a name "';drop table patients;--" ? Suppose the line breaks are not sent to SQL as I would expect from your example you get this:

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = ''; drop table patients;--ARC'     AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'     AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

OWASP is a good resource on security and has a resource on Java SQL injection and prepared statements here

From that page:

  Prepared Statements

  Variables passed as arguments to prepared statements will
  automatically be escaped by the JDBC driver.

  Example: ps.1

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();