mysql database connections will be migrated

This answer was given at the MySQL forum by Javier Treviño from the  MySQL on Windows Team:

  Some products are able to share connection information to MySQL Server
  instances with MySQL Workbench. The products that do this are MySQL
  for Excel, MySQL Notifier and MySQL for Visual Studio.

  By sharing connection information this means that if MySQL Workbench
  is installed in the same computer, all of these programs will attempt
  to use the same configuration file where MySQL Workbench stores the
  information about connections.

  So, if any of the 3 aforementioned products are installed but MySQL
  Workbench is not, they will fallback to use their own separate
  configuration file to store connection information, but as soon as
  they detect MySQL Workbench is present, they will attempt to migrate
  or move the connections stored in the separate configuration file to
  the file MySQL Workbench uses.

  The product presents a dialog with the message you have been talking
  about in this forum thread. You should notice there is a More Info
  button displayed in that dialog. If you click it an information
  message like this will be shown:

    {APP_NAME} now works with the connections file used by MySQL Workbench
    if it is installed.

    {APP_NAME} will attempt to merge all existing connections with MySQL
    Workbench and in order to do this MySQL Workbench must not be running.

    Please make sure MySQL Workbench is closed to continue with the
    merging operation.  If you choose not to migrate connections now, you
    will be prompted later to perform the migration.

  Also note that in the latest versions of the 3 aforementioned products
  the migration of connection information can be delayed to be done at a
  later time or delayed indefinitely, which means the connection
  information will remain in the product's configuration file until a
  manual migration of this information is performed.


Find common attributes among given products in SQL Server

Use a COUNT() on the rows in your temp table to simplify your query, e.g.:

SELECT
    sa.Attribute_ID,
    sa.Value,
    Count(DISTINCT sa.Stock_ID) as Matches
FROM Stock_Attributes sa
INNER JOIN #TempItemNumbers tin
    ON tin.Stock_ID = sa.Stock_ID
GROUP BY sa.Attribute_id,
    sa.Value
HAVING Count(*) = (SELECT Count(*) FROM #TempItemNumbers)

Only those rows where all the items have matching attributes will have the same count as the number of items in your temp table. Making the COUNT() be of the distinct Stock_IDs handles duplicate entries for a given Attribute/Value pair for an item - just in case. You can replace that with a simple COUNT(*) if you are guaranteed not to have duplicates.


Try

sudo apt-get install postgresql-9.3-postgis-2.1

http://packages.ubuntu.com/trusty/postgresql-9.3-postgis-2.1


MongoDB - handling embedded documents and relations

You are not missing the point :)

The key is how many reads and how many writes do you have to do when you store/update/fetch various entities in your collections?

You are presumably constantly (daily?) creating new posts and frequently creating new comments, and very frequently querying posts.

Actions like updating authors' email are very rare compared to the above.  You want to optimize the performance of reads and writes that happen hundreds or thousands of times per  in your application, and worry less about performance of very infrequent actions.

Having said that, I would store the basic author information embedded in the post, but I would only have information there that must be displayed with every post.  If there are additional details about the author I want to keep in its own collection, I would expect that would be details that the user needs to click additional link to see (allowing for time to do another read).


How does one identify if Oracle Data Guard is enabled in Oracle database?

There is a built-in procedure for that.

set serveroutput on
declare
  feature_boolean number;
  aux_count number;
  feature_info clob;
begin
  dbms_feature_data_guard(feature_boolean, aux_count, feature_info);
  dbms_output.put_line(feature_boolean);
  dbms_output.put_line(feature_info);
end;
/

Sample output when Data Guard is not used:

0
Data Guard usage not detected

Sample output when Data Guard is used:

1
Number of standbys: 1, Redo Apply used: TRUE, SQL Apply used: FALSE, Snapshot
Standby used: FALSE, Broker used: TRUE, Protection mode: MAXIMUM PERFORMANCE,
Log transports used: LGWR ASYNC, Fast-Start Failover used: FALSE, Real-Time
Apply used: TRUE, Compression used: FALSE, Flashback used: FALSE

The source of the above procedure is not wrapped, so one can browse it and find how the database detects Data Guard usage.

-- check for Data Guard usage by counting valid standby destinations
execute immediate 'select count(*) from v$archive_dest ' ||
    'where status = ''VALID'' and target = ''STANDBY'''
    into num_standbys;

if (num_standbys > 0) then
    feature_boolean := 1;

Basically, if the result of this query is greater than 0, than the Data Guard is in use as per the procedure:

select count(*) from v$archive_dest where status = 'VALID' and target = 'STANDBY';


Concerns regarding changing virtual NIC on SQL Server 2008 R2 / Windows Server 2008 R2

Assuming SQL Server is configured to use a dynamic TCP port via SQL Server Configuration Manager, and the SQL Server Browser service is running and not blocked by a firewall, replacing the network adapter should not change connectivity significantly, within the bounds of the network adapter operational characteristics (i.e. its speed, etc).

If, however, you're using a specific TCP port for SQL Server, you'll want to ensure you document that port number before replacing the network adapter.  Once you install the replacement adapter, and remove the existing adapter, set the new adapter to use the same port via SQL Server Configuration Manager.

This is a snip of the configuration screen:


How is powershell commonly used in SQL server?

So the main thing is that you can set these up to be dynamic. You wouldn't have to log into each instance to find out the settings. It does make it easier to perform a health check and just keep track of the lot. I used it for Parsing Excel documents to SQL server. This turned out to be very simple instead of converting it to csv documents and importing from there.

But what I would do is check into this site, it has a pretty good repository. A bit old though...

Additionally, Microsoft is making a huge amount of enhancements to powershell into SQL server. It was ignored previously as it was already a scripting language. Most people now are going into proper desired state configuration where you can spin up a SQL server instance with memory and all the settings you would like inside the SQL server instead of it just setting up the windows box and you needing to log into the SQL instance to run scripts. Pretty good post about it here.


Access Denied error in CREATE DATABASE ... FOR ATTACH

When you get this message it can be a few things. Some examples:

1.) Read Only Marked Files

2.) Insufficient permissions to the files for the SQL Server Service account

3.) Insufficient permissions to the folders the files live on.

You indicated in our chat that this was not read only. You also verified permissions looked like full access. That ruled out these three items.

The fact that, from our chat conversation, you were not experiencing this issue when you properly detach a database using sp_detach_db - which actually changes permissions around a bit on the files led me to suggest trying to run SSMS as administrator.

Why? Well this article gets into a lot more nitty gritty about some of the impersonation issues here - but my understanding is:

1.) When you just stop the SQL Server Service and move the file around. The owner of the file is the service account that created it.

2.) When you detach the database - you'll potentially notice a permissions change. The owner has changed basically and the file is not really associated with that SQL Server service account the same way.

3.) So it works when you try to attach because the permissions can get assigned, because the security of the file allows it..

But if you just stop the service the security hasn't changed, and you can't attach that if you have UAC enabled unless you are impersonating administrative access.

I've probably made the file ownership and access more confusing here but the article I shared probably does a better job :-)

But one slight moral is - do a detach next time ;-) Or just be prepared to run SSMS as administrator from time to time when interacting with the OS in various ways.


Can I update old data and insert new data if not exists currently in a single query

I  think you will want something like the following - see the fiddle here. It's based on Common Table Expressions (CTEs) and that fact that with PostgreSQL, you can perform not only SELECTs, but also INSERTs, UPDATEs and DELETEs (see here also).

First, your service table:

CREATE TABLE service
(
  name VARCHAR (10) NOT NULL PRIMARY KEY,
  id INTEGER NOT NULL,
  active BOOLEAN NOT NULL
);

populate it with your data:

INSERT INTO service VALUES
('Test1', 3, true), ('Test2', 4, true), ('Test3', 5, true);

Now, you receive your data from your API - I'll assume that you put that into some sort of temporary table - the keyword TEMPORARY just means that the table will be dropped at the end of your session - I've tested with both TEMPORARY and normal tables with the fiddle and the results are the same, so we'll go with TEMPORARY:

CREATE TEMPORARY TABLE api
(
  name VARCHAR (10) NOT NULL PRIMARY KEY,
  id INTEGER NOT NULL
  -- active BOOLEAN NOT NULL
);

I've assumed that your API doesn't know the status of the service, so it only has two fields - the name and the name and the id.

Populate it:

INSERT INTO api VALUES
('Test2', 4), ('Test3', 5), ('Test4', 6);

Notice that service Test1 is missing and that service Test4 is an additional service.

So now, because of PostgreSQL's ability to perform INSERTs and UPDATEs within CTEs, we can now do the following:

WITH cte1 (nom) AS
(
  INSERT INTO service  (name, id, active)
    SELECT a.name, a.id, true FROM api a
    WHERE a.name NOT IN (SELECT name FROM service) RETURNING name
),
cte2 (nom2) AS
(
  UPDATE service s SET active = false
    WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name
)
SELECT * FROM service;

The first CTE

INSERT INTO service  (name, id, active)
  SELECT a.name, a.id, true FROM api a
  WHERE a.name NOT IN (SELECT name FROM service) RETURNING name

inserts new services from the api table into the service table and the second:

UPDATE service s SET active = false
  WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name

sets the service status = false where a service in the service table isn't present in the api table.

Now, the result of the SELECT * FROM service at the end of this query is:

name    id  active
Test1   3   t
Test2   4   t
Test3   5   t

So, you might think "Drat, it hasn't worked!" - but in fact, it has worked!

In the next section, you rerun

SELECT * FROM service
ORDER BY name;

and you get:

name    id  active
Test1   3   f
Test2   4   t
Test3   5   t
Test4   6   t

So, we can see that service Test1's active field has been set to false and that service Test4 has been added. The reason it doesn't show up in the SELECT immediately after the CTEs has to do with the scope of the transaction - the first SELECT shows the service table as it was at the beginning of the transaction - the second shows the state of the table after the transaction.


PostgreSQL storing vectors and computing dot product

You should write the function in C if you want it to run as fast as possible. Then it might also be a good idea to write your own data type that is stored with a hash table.

If that is too hard, and you don't need that last little bit of performance, you could try to rewrite your function in Perl or Python — both can be run inside the database.


Just how secure is the data I put on SQL Azure?

Irrelevant. Seriously. It is irrelevant how secure it IS - if she is in a regulated industry, the provider has to provide the necessary guarantees, whether he fullfills them or not.

And you will find that Azure tells you nothing in the contracts. It is thus not good to use them, regardless how good their factual security is.

Example answer:

https://stackoverflow.com/questions/11167152/hosting-a-pci-compliant-app-on-azure

about azure PCI compliance - the asnwer is: not.

Check:

https://www.windowsazure.com/en-us/support/trust-center/compliance/

for more information. This is what you are guaranteed - the factual implementation is then not your problem.


The 2 queries that you show are different in meaning.

The first one will select all upvotes for that ip and content in that timeframe. If you create the index:

mysql> ALTER TABLE content_votes_tmp ADD INDEX(up, ip, content, datetime, record_num);
Query OK, 753676 rows affected (3.85 sec)
Records: 753676  Duplicates: 0  Warnings: 0

You can get good performance results- the first 2 columns will be used and you will get the Using Index optimization:

mysql> EXPLAIN SELECT record_num
               FROM content_votes_tmp
               WHERE up = 1 AND
                     ip = 1  AND
                     content = 1 AND
                     UNIX_TIMESTAMP(datetime) > @number\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_votes_tmp
         type: ref
possible_keys: up
          key: up
      key_len: 12
          ref: const,const,const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

We can do even better than that. Remove the UNIX_TIMESTAMP() function -compare dates instead of ints by transforming $old_time- and will be able to apply all conditions using the index (3 consts and 1 range):

mysql> EXPLAIN SELECT record_num
               FROM content_votes_tmp
               WHERE up = 1 AND
                     ip = 1  AND
                     content = 1 AND
                     datetime > now()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_votes_tmp
         type: range
possible_keys: up
          key: up
      key_len: 17
          ref: NULL
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

Be careful if the logic of the application requires to know if a downvote was done, this query will not help you with that.

The second one will require a slightly different index:

mysql> ALTER TABLE content_votes_tmp ADD INDEX(ip, content, datetime, up);
Query OK, 753676 rows affected (5.95 sec)
Records: 753676  Duplicates: 0  Warnings: 0

In order to get a good query plan, also needing the "move the function to the other side of the operand":

mysql> EXPLAIN SELECT SUM(up) as up_count
       FROM content_votes_tmp
       WHERE ip = 1 AND
             content = 1 AND
             datetime > now()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: content_votes_tmp
         type: range
possible_keys: ip
          key: ip
      key_len: 13
          ref: NULL
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

I do not like the range + SUM(), so I would prefer the first one, which is simpler (unless you intend to return lots of rows). But there is nothing on the query plan to support my fears (it has the covering index optimization too, and no filesort, so both can be very fast with the appropriate indexes- they take 0.00s with my fake 1M data).

Remember that the logic is slightly different, so be aware of that- if you allowed upvotes and downvotes, that could be a problem.


Table valued function VS Stored procedure

If the data changes infrequently, package the fast-running SELECT as a view and define an index on it.

An indexed view comes with some restrictions which minimally limits flexibility in future changes. There is nothing terribly bad, however.

If the data changes very often, and updates are performance-critical, the extra time to maintain the view may be a concern, but no more than for any other index.


Upgrading Microsoft SQL 2014 and Always On AG Configurations

What you're reading is referring to changing the failover mode of an availability group replica:

  To change the failover mode of an availability replica

  In Object Explorer, connect to the server instance that hosts the
  primary replica, and expand the server tree.
  Expand the Always On High Availability node and the Availability
  Groups node.
  Click the availability group whose replica you want to change.
  Right-click the replica, and click Properties.
  In the Availability Replica Properties dialog box, use the Failover
  mode drop list to change the failover mode of this replica.

At this point you'll want to set the failover mode to manual. Voila!

I assume you're doing an in place upgrade by the nature of the question, so to answer your other concerns:

Removing the secondaries from the availability group would effectively do the same thing (can't fail over when there's nothing to fail over to...) but only changing the failover mode lets you keep more of the existing architecture in place without having to re-seed every replica all over again.

You also could disable the entire availability group feature in Configuration Manager, but I think that requires a restart of the service and requires you to completely setup availability groups from scratch.


Query to obtain cccurrences within a certain period of time

You need to do some grouping for it

CREATE TABLE Table1
    ([ClientId] int, [RecordDateTime] datetime)
;

INSERT INTO Table1
    ([ClientId], [RecordDateTime])
VALUES
    (1, '2019-02-14 14:05:49'),
    (1, '2019-02-14 14:06:34'),
    (1, '2019-02-14 14:07:19'),
    (1, '2019-02-14 14:08:49'),
    (1, '2019-02-14 14:09:34'),
    (1, '2019-02-14 14:10:32'),
    (1, '2019-02-14 14:12:32'),
    (1, '2019-02-14 14:14:18'),
    (1, '2019-02-14 14:15:10')
;

select
  ClientID
  ,min(RecordDateTime) as [Occurrence Start]
  ,max(RecordDateTime) as [Occurrence End]
from
(
select
  ClientID
  ,RecordDateTime
  ,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp
from
  (
   select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <=60 then 0 else 1 end as grp
  from
    (
    select
          ClientID, RecordDateTime,
          LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
    from Table1
    ) as s
  ) as g
) as a
group by ClientID,sgrp

output of it:

ClientID    Occurrence Start    Occurrence End
1   14/02/2019 14:05:49 14/02/2019 14:07:19
1   14/02/2019 14:08:49 14/02/2019 14:10:32
1   14/02/2019 14:12:32 14/02/2019 14:12:32
1   14/02/2019 14:14:18 14/02/2019 14:15:10

dbfiddle
 source of inspiration

A short description:

select
      ClientID, RecordDateTime,
      LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
from Table1

key point here is the LAG function , to get the previous value

ClientID    RecordDateTime  prevRecordDT
1   14/02/2019 14:05:49
1   14/02/2019 14:06:34 14/02/2019 14:05:49
1   14/02/2019 14:07:19 14/02/2019 14:06:34

Then, do a difference, in seconds , to test if it's in interval:
,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp

select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp
  from
       (
          select
               ClientID, RecordDateTime,
               LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
          from Table1
        ) as s

ClientID    RecordDateTime  prevRecordDT    grp
1   14/02/2019 14:05:49 14/02/2019 14:05:49 1
1   14/02/2019 14:06:34 14/02/2019 14:05:49 0
1   14/02/2019 14:07:19 14/02/2019 14:06:34 0
1   14/02/2019 14:08:49 14/02/2019 14:07:19 1

After this, just do a sum base on grp column
,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp

select
  ClientID
  ,RecordDateTime
  ,sum(grp) over(PARTITION BY ClientID ORDER BY RecordDateTime) as sgrp
from
  (
   select
       ClientID
       ,RecordDateTime
       ,isnull(prevRecordDT,RecordDateTime) as prevRecordDT
      ,case when datediff(second,prevRecordDT,RecordDateTime) <= 60 then 0 else 1 end as grp
  from
    (
    select
          ClientID, RecordDateTime,
          LAG(RecordDateTime,1,NULL)OVER(PARTITION BY ClientID ORDER BY RecordDateTime) as prevRecordDT
    from Table1
    ) as s
  ) as g

ClientID    RecordDateTime      sgrp
1         14/02/2019 14:05:49   1
1         14/02/2019 14:06:34   1
1         14/02/2019 14:07:19   1
1         14/02/2019 14:08:49   2
1         14/02/2019 14:09:34   2

And finally, do min and max grouping by ClientID and sgrp


Assuming that we are talking about dbms_scheduler jobs (rather than, say, jobs scheduled using the dbms_job package), you can use the dbms_metadata package to generate the DDL. Specifically, the dbms_metadata.get_ddl function.  Something like

SELECT dbms_metadata.get_ddl( 'PROCOBJ', <<job name>>, <<schema>> )
  FROM dual

should work, for example.

Here is a list of the object types that dbms_metadata.get_ddl supports.

Here is an example of using the PROCOBJ trick to get dbms_metadata to script out a job.


Merge join turns to hash match when adding a 2nd join

I think this stack exchange link gives a good rundown of why it is doing what it is doing, and I believe it has most to do with the size of the results sets and how they are indexed/sorted in your case.    Order and Order Details, from this database are 830 rows  and 2155 rows respectively.   These are similar results sets where one has 2 rows (roughly) for every one row in the other table.   Order Details is clustered on  OrderID, ProductID - meaning that the table is also sorted that way.   Orders is obviously clustered on OrderID as well, meaning that it is also sorted on orderid.   I believe this makes it easy to merge these two data sets that are already sorted in this way together.

Then you throw in Products.    If you run the query

SELECT * FROM dbo.[Order Details] AS od
ORDER BY OrderID, od.ProductID

This is basically showing you what the logical order of the Order Details table is.   And if you look at it from a products perspective it looks basically random.   So now you are throwing in a join to a table that is only 77 rows and where the sorting does not really match up.

I believe that is why you see the Merge Join for the first and the Hash for the second.

Read that link above as it gives a nice description of these things and how they work.    Another good one is at this other SE article.

Lots of information in those two and at many other places (including your book).


Altering table Foreign Keys produces an error (errno:150) (MySQL)

While you seem to have fixed the issue, I will quickly explain why it happened in case anyone finding this will want to understand where the problem was.

When setting foreign keys, the Primary Keys Columns must be of exact same type and attributes. E.g. If you have unsigned attribute on one primary key, you must have it on another. If you have INT data type on one column, then another column must also be INT (NOT TINYINT, MEDIUMINT etc.).

As you have only one ID set to unsigned, I would go and set it to all IDs. As it is usually good idea to have unsigned attribute on primary keys (if you do not use negative IDs), I would have changed all IDs to have unsigned attribute, as it will improve your query performance.

Also, take a look at what values you can get with various integers (when they are unsigned). WHat you set your lenght  to - does not matter:

Integer Type    Max Value
TINYINT         255
SMALLINT        65535
MEDIUMINT       16777215
INT             4294967295
BIGINT          18446744073709551615


Postgres, return 0 rows if any row matches condition, otherwise return all rows

SELECT * FROM table_name
WHERE NOT EXISTS
(
  SELECT 1 FROM table_name WHERE is_activated IS TRUE
);


mysqldump, or .sql files, which is what that module uses is probably the least efficient way to import a database (the only less efficient way I can think of is to import and commit each row at once). If you want to speedup the import process, you should change the method.

There are several things that you can do in the MySQL configuration that will speed up the import, but you will be missing the most important one: parallel import. If you can import using the same file in 4-8 threads, you will not get a linear performance grow, but it will scale without problems. Split the file in different tables/partitions and you will get almost a linear speedup. With specialized tools like mydumper you can get 5-10x speedup. With raw backup tools you would probably get almost-infinite speedup.

Regarding configuration, which is probably the only thing you are interested in, you can set:

innodb_flush_log_at_trx_commit = 2. This is the most important suggestion. Makes the writes inconsistent with disk, but provides a great improvement if you are not using a hardware cache. It dependes on how slow is your disk, but can give you a 10x speedup. Not safe for normal operation, many people use it for unimportant data.
innodb_doublewrite = 0. Disables corruption checks. Very unsafe, and likely to corrupt your database in case of a crash. Can give you in your case around 2x speedup. Do not run MySQL never on this mode except for the import.
checksums = none (the syntax may have changed in 10). Disables page checksum. it wont't be very useful, as your CPU is not the bottleneck
performance_schema = off- not sure the default in Maria 10, probably can be disabled for the import without problems, but it won't give you a large speedup
innodb_buffer_pool_size and innodb_log_file_size they are already large on your setup -way larger than your data-, so no need to change in your case)

There are other things, but they require changing the file, like creating the secondary indexes afterwards.


Don't even try.

InnoDB has years of optimization behind it.  It persists to disk even across unplanned power failures due to its integrated logs.

Furthermore, because of MEMORY using table locks (as opposed to InnoDB's row locks), there are benchmarks that say that InnoDB is faster than MEMORY.  There could be cases where Memory is faster than InnoDB.  But between your moderately high write rate and the JOINs, InnoDB is very likely to have less interference between the two tasks.

If you want full persistence, you need to write to the disk 500 times per second.  Are you using SSDs?  RAID?  An ordinary HDD cannot run at 500 flushes per second.  If you have a fast enough drive, you may as well let InnoDB take care of everything for you.

InnoDB's automatic saving and restoring across power failures is very likely to be faster, and much less programming effort for you.


How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

A very common misconception about Databases can be dispelled with this:

    Database != File

When you update a Row in a database, the underlying data file on disk isn't touched at all - at least not for "some time".  Instead, the database makes a note of the change in its Transaction Log, then updates the value in memory.  "Some time" later, the database might get around to needing that bit of memory for something else and will write the changed value to disk.  How often that happens and how large a chunk of memory get written at a time varies from DBMS to DBMS.

Data storage in databases is measured in Pages, each of which can contain a number of Rows; those things that make up the Tables that you and I play with.  When a Database needs some data, it works out where that data is in its data file(s) and then loads only those Pages that are relevant into its Buffer Cache (memory).  This is why some queries run slowly the first time you run them, but are lightning fast thereafter - serving the same Page over and over from the Cache is way faster than hauling it up from the data file on disk.

  ... I am not using SQL database as my table contains numerical column names ...

Here's another misconception about databases, again easily dispelled:

    Database != SpreadSheet

The way you structure data in Databases can seem quite "alien" when you're starting out; you seem to need to use "complicated", "artificial" constructs instead of just "rows" and "columns" of data.  But, once you gain an understanding of why you need these structures and the power that they give you over your data, you'll get over it pretty quickly.

  ... operations ... on single cell out of trillion by trillion table ...

Do you really have a useful value for every single value in a trillion by trillion table?  Personally, I'd doubt it, unless you work for Google.

I'd suggest what you actually have is a Sparse Array, where you have more "holes" than data.  That's a structure that Relational Tables can support very easily and really quite efficiently.


Postgresql 9.5 BRIN index dramatically slower than expected

I'm guessing when migrating you didn't import rows ordered by date. You could check this by issuing

select * from logline;

And check if date looks like it's increasing monotonically. If that's not the case, you could try sorting the table, for example:

select * into logline2 from logline order by date asc;

... create index on second table ...

CREATE index logline2_date_brin on logline2 using BRIN(date)

... and try 'luck' with second table:

EXPLAIN ANALYZE SELECT COUNT(*) from logline2 where date BETWEEN '2016-01-15' and '2016-01-31';

If the time is drastically better, perfect.

If you have disk space limitations, you should also check extension cstore_fdw. It's really good for analytics and can store data in compressed form. It features indexing similar to BRIN, but has some limitations: you can only append data and transactions are not supported.


Postgres tsrange index for small sliver of time

I think that there is no way to do this - making the indexed rows dependent on the current time would mean that the index has to change all the time, and that is something PostgreSQL doesn't do.
You could of course introduce a condition:
CREATE INDEX ON events USING gist
    (tstzrange(start_time, end_time, '[)'))
WHERE status = 'PUBLISHED'
  AND start_time > '2021-01-01 00:00:00';

and drop and re-create the index each year (or month).
But then your queries would have to contain the same condition that changes yearly, which does not seem practical.


A simple question regarding database design that has always bothered me

There really is a whole raft of pros to normalizing your database. If the main issue you're having is naming, then you're in pretty good shape. Apart from space which you've already mentioned:

The width of your table will be smaller, this will increase the speed of your selects.
Updates and inserts will be quicker because you're inserting/updating integers instead of text
Maintaining your data will be easier ie if there was a new type of school which needed to be added, you should just be able to add it to the table.
You can create hierarchies in your lookup tables
You don't need to reply on your application layer to enforce referential integrity.
You can manage changes to your lookup options over time (ie what is "Public" is no longer applicable after July 1st but older records created before this date must still show this as an option?)
Loading data into a data warehouse is simplified
It makes it easier to other DBAs to understand your schema

Normalization is usually going to have a level of subjectivity. You database schema should reflect a balance between maintainability, complexity and performance. On the flip side, if you're only going to have 1 or 2 tables and a small amount of records, maybe normalization is overkill? I would tend to normalize anyway, because if the database needs to scale up, you're already in a good position to do so.

As for naming, have a think about the most sensible name that will describe the table's contents without it sounding crazy like the example you gave above. Otherwise you'll end up with a schema which is hard to understand.

Hope this helps.


What is the difference between ON SCHEMA and ON DATABASE triggers?

A trigger on schema (with no schema specified) doesn't fire for all schemas. It only fires when the triggering action is run by the user who owns that trigger.

So they are quite different, and are not interchangeable.

Here's an example that sets up a logging table, and three create triggers: on database, and on schema for users foo and bar:

Connected. -- as mat
SQL> create table mat.log (dt timestamp, who varchar(3),
  2      cur varchar(10), own varchar(42), obj varchar(42));
Table created.

SQL> create or replace trigger db_trig
  2  after create on database
  3  begin
  4    insert into mat.log values (systimestamp, 'db', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect foo/foo
Connected.
SQL> create or replace trigger foo_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'foo', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect bar/bar
Connected.
SQL> create or replace trigger bar_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'bar', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /

Trigger created.

Now let's create a table in foo's schema, as foo:

SQL> connect foo/foo
Connected.
SQL> create table foo.foo_stuff (id number);
Table created.

And let's create a table in foo's schema, as bar:

SQL> connect bar/bar
Connected.
SQL> create table foo.bar_stuff (id number);
Table created.

Here's what we've logged:

SQL> select * from mat.log order by dt;

DT                             WHO CUR        OWN        OBJ
------------------------------ --- ---------- ---------- ---------------
25-NOV-12 07.52.03.797794 PM   db  FOO        FOO    FOO_TRIG
25-NOV-12 07.52.03.828670 PM   db  BAR        BAR    BAR_TRIG
25-NOV-12 07.52.03.865334 PM   foo FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.865579 PM   db  FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.894672 PM   bar BAR        FOO    BAR_STUFF
25-NOV-12 07.52.03.894911 PM   db  BAR        FOO    BAR_STUFF

6 rows selected.

So:

the two create trigger statements were logged by the "global" after create on database trigger. That trigger also logged everything else.
foo's after create on schema logged the table creation that was done by foo
bar's trigger logged the table creation that was run by bar himself, even though bar created a table in foo's schema.


How can I measure the performance hit of tablespace autoextensions?

If you know the original size, it is easy to calculate the number of extends that have occurred.  The metadata for the datafile will include its age.

The overhead for an auto extend is low, but datafile fragmentation may cause problems.

I generally try to extend by 10 to 25 percent of the original size, and aim for as few extend operations as possible.   Try to allocate enough space for at least a year's worth of data.

For development databases, I tend to size smaller, and extend more often.

You can find out how full a tablespace by querying dba_tablespace_usage_metrics. The Orafaq Tablespace page will give you additional information.


Postgresql Physical Reads and Buffer Reads

If you want to know it per database:
SELECT datname, blks_read, blks_hit
FROM pg_stat_database;

If you are interested in values per table, use
SELECT * FROM pg_statio_all_tables;

If you want to have statistics per query, install pg_stat_statements and use
SELECT query, shared_blks_read, shared_blks_hit
FROM pg_stat_statements;

Don't forget that since PostgreSQL uses buffered I/O, a “block read” might come from the kernel cache rather than from disk.


Configuring slave from EC2 master to Amazon RDS slave

Community Wiki answer generated from question comments by jynus:

You may not have opened the right ports or provided the REPLICATION SLAVE permissions:

Using Replication to Export MySQL 5.6 Data

Also see this answer:

Replication from EC2 Master to RDS Slave - Connecting to master


Optimize MySQL InnoDB query for SUM and Order By

You have indexes on item_link and sale_date but you are not using item_link.  When The first column of index are not in use in the where clause, the index are tends to be skipped while query execution.

Plan you index as per you where clause which in this case sale_date would be a good

Thanks,


Does the sql server compare strings by hash values?

If the query optimizer decides that the execution plan should use a hash join this is exactly what it does. (It can be used in other places too - such as aggregation).
re the quote that you give - I think this relates to a varchar(n) column, so the storage is the number of characters you want to store in bytes plus a 2 byte overhead to manage the variable length.
It´s also quite common to add some sort of hashing or checksum programmatically, for example to represent a long strings checksum using an integer as a computed column, then add an index and search for a checksum match (plus a test to eliminate any false matches caused by a collision).


How do you read a one-to-many relationship in both directions?

There are a lot of different graphical conventions for relationships in a data model.  See some examples in Wikipedia: Cardinalities.

The vastly most common way to represent cardinality in a diagram is to have the symbol beside an entity type represent the cardinality in the direction of that entity type.  In other words, the symbol meaning many is at the many end of the relationship.

Specifically in your example: Each PROFESSOR teaches one or more CLASSES and each CLASS is taught by exactly one PROFESSOR.

Very rarely I've seen people do this backwards (e.g. (min, max)-notation), but if you do that you're going to confuse most people.


PostgreSQL requirements for multi workstations

Yes, PostgreSQL is fully network aware. You can put it on a separate machine or not, this all depends on how you use it, so you will need to state your requirements, type and volume of data, etc.

See for example https://www.postgresql.org/docs/current/static/runtime-config-connection.html and https://www.postgresql.org/docs/current/static/ssl-tcp.html

For development purposes, you can install it on your own machine to develop your programs and later just move your application to use the production database.


How to have PostgreSQL running forever with constant insert and delete per day

based on the requirements.

Option 2 looks like the best solution,  creating new partitions at X internal and dropping the older data

Partitions in Postgresql creates independent tables when a partition is dropped the file is deleted reclaiming disk space

You can do this with a PgAgent task,  to pre-create the partitions for the next 24 hours, then a task that runs every hour dropping the older partitions.

You want to use Range type partitions then use Alter Table Detach Partition or Drop Table

this code will get you close the partition rule will have to tweaked

create table  table_of_records(
insert_date  timestamp default now(),
insert_hour integer default date_part('hour', now())
other_columns) PARTITION BY RANGE (insert_hour);

 -- pre create the next days tables
 create or replace function create_partitions();
returns boolean
language 'plpgsql'
as $code$
declare
 _counter integer = 0;
 begin

loop
   if _counter > 24 then
      exit;
   end if ;

     execute 'CREATE TABLE table_of_records_' || _counter::text
         || ' PARTITION OF table_of_records
          FOR VALUES FROM (_counter) TO (counter+1);';

   _counter = _counter +1;
end loop;
return true;
end;
$code$

create or replace function drop_partition(phour integer);
 returns boolean
 language 'plpgsql'
 as $code$

 begin
 execute 'drop table table_of_records_' || phour::text;
 return true;

end;
 $code$

https://www.postgresql.org/docs/10/ddl-partitioning.html


How to block pg_stat_reset() statement in DB

Mutilating PostgreSQL would not be a good idea, so go and fix your application.

One thing that stands out here is that pg_stat_reset can only be executed by a superuser, so either you have a SECURITY DEFINER function that allows your application to call it (in which case you could simply change that function) or you are running your application as superuser, in which case you have bigger problems than performance issues.

Never, ever, run an application as superuser. It is never necessary, and it is an immense security problem.


need the rows with a unique value on certain columns but date column doesn't help?

Perhaps I'm misunderstanding the objective but, to take the data that you helpfully provided in the INSERT and determine both the period when the record changed and the most recently paid period, you can do this:
SELECT
  tn.[name],
  tn.[home],
  tn.[current],
  tn.[effective_date],
  tn.[not_to_exceed_date],
  tn.[flag],
  tn.[grade],
  TRIM(tn.[position]) as [position],
  MIN(tn.[last_pay_period_paid]) as [first_pay_period_paid],
  MAX(tn.[last_pay_period_paid]) as [recent_pay_period_paid]
FROM
  [dbo].[tableName] tn
GROUP BY
  tn.[name],
  tn.[home],
  tn.[current],
  tn.[effective_date],
  tn.[not_to_exceed_date],
  tn.[flag],
  tn.[grade],
  TRIM(tn.[position])

This will give you the following output:

Note that there are two records for ACCOUNTANT. This is because there are two distinct values in home. Not sure if this is due to a typo in the sample data or if the column is even necessary. Either way ... this may give you the information you seek.


Pick up the name of the column by two diff column in single table

Use aliases like that:

Select a.Column, b.Column
FROM TableA AS a
JOIN TableB AS b on a.TableBId = b.Id

This way the engine knows when you want to use Column from TableA and when from TableB.

It is a good practice - you should always use aliases when writing SQL queries.

The query you are looking for might be:

SELECT c.catname
    ,(
        SELECT c1.catname + coalesce('>' + cast(
                    SELECT c2.child_id
                    FROM categories c2
                    WHERE c2.idcat = c1.child_id
                    ) AS char(50)), '')
        FROM categories c1
        WHERE c.parent_id = c1.idcat
        )
    ,c.catDesc
    ,c.catstat
FROM categories c


RID Lookups occur on a heap data structure in SQL Server (as opposed to a B-Tree). This occurs when a non-covering nonclustered index is used to fetch the data and it needs to lookup the remaining fields it's missing. Your table data is stored in a heap when there is no clustered index on that table (as the clustered index defines the ordering the records are sorted into a B-Tree normally).
If your table had a clustered index and this query wanted to use the same nonclustered index it's currently using, you'd see a Key Lookup operation in the execution plan instead.
If you created a clustered index on your table for the two fields Timestamp and ComponenentID then that'll be covering for your query and you should see the clustered index used in the execution plan instead which will eliminate any kind of additional lookup operation.

Regarding your second question in the comments, based on your recent comment updates, it sounds like the difference in runtime you're seeing is due to the first run pulling the data into memory from disk (which is generally the most bottlenecking part of the process, from a hardware perspective) and the second run leveraging the existing data in memory.
Depending on how big your Table and Page Sizes are, this normally shouldn't be too much of a concern (based on the number of rows I see your query is returning from its execution plan). All subsequent runs of the query (while the data is still in memory) will have optimal performance.
If the initial run to pull the data off the disk becomes a problem then you can either look into Compression or analyze if you can upgrade your disks to something faster (not sure if you're still on a mechanical hard drive currently, and can switch to an SSD or even better is an NVMe).
I'll also add a final note that was made in the comments that is true and related but not the root issue itself, that your query which does additional filtering on ComponentId results in more data being read. This is evident in your IO statistics screenshots if you compare the grand totals for Logical Reads between the two queries. The one query filtering on Timestamp and ComponentId results in 396 Logical Reads whereas the other query filtering on just Timestamp only results in 90 Logical Reads. Logical Reads are the number of 8 KB Pages that are read from memory. Physical Reads are the number of 8 KB Pages that were read from the disk. It's not a huge difference, but would account for a small difference in time between running the first query vs the second query, on their first runs. This becomes a moot point on subsequent runs of both queries when the data is already in memory from disk (as discussed in my previous few paragraphs).


Inserts/updates are extremely slow with Merge Replication in SQL Server 2008

I had a very similar sounding problem.  We replicated a table with millions of rows.  One of the subscribers would take up to 2 minutes to insert 100 new rows.

I solved the issue by adding an index to the rowguid column of the table it was inserting to.  I know this sounds like it should degrade performance but it looked like it was doing a full table scan before inserting, probably to make sure a duplicate row was not inserted.

Once I added this index replication was running smoothly again


The cluster log, SQL Server Error Log and the application log in Event Viewer should give you the reason.

It sounds like quorum is not setup properly. Without a 3rd resource to act as the witness, a network glitch will cause a failover.

Setup a witness, such as a file share on another server, or a witness disk. Some people use a file share on a file server or an AD server. I've also used the witness disk approach where the disk is a shared drive, like what you'd have on a Failover Cluster.


First of all true test of sql is to take variable instead of constant value.
So ,

Declare @From date='2017-10-01'
Declare @To date='2018-09-30'

Immediate correction ,put the filter here

select
g.*,
a.StateId,
a.CountryId
into #Temp
from #TempGuidelineLog g
JOIN [vwCustomerAddress] a ON a.CustomerId = g.CustomerId
where g.CountryId = 1 and g.StateId NOT IN (65,66)

and remove the same filter from where it is now.

I see no requirement of LogDate in #TempGuidelineLog so remove it.

so new index,

CREATE NONCLUSTERED INDEX ix_temp1Customer ON #TempGuidelineLog (CustomerId)

Since countryid is more selective,

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CountryId,Stateid,CustomerId)

CREATE NONCLUSTERED INDEX ix_temp1Country ON #Temp (CustomerId)

IMO, group by St.Name and co.Name is also hurting your performance.

so you can seperatly query group by function and then with that resultset join State and country table.hope it is clear.

The way you are creating XML is also complicated.You can choose one of the xml design which suite you from here,XML


In sql-server:

One option is dynamic sql, another is option (recompile).

using option (recompile):

select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile);

dynamic sql example:

declare @sql nvarchar(max);

if nullif(@MyParam,0) is not null
  begin;
  set @sql = 'select * from MyTable where MyColumn=@MyParam;'
  exec sp_exeuctesql @sql, '@MyParam int', @MyParam;
  end;
else
  begin;
  set @sql = 'select * from MyTable;'
  exec sp_exeuctesql @sql;
  end;

Reference:

Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
Dynamic Search Conditions - Erland Sommarskog
Catch-all queries - Gail Shaw
An Updated "Kitchen Sink" Example - Aaron Bertand


You can generate a script to do that using dynamic TSQL and then run it

SET NOCOUNT ON;
SELECT  'USE [MASTER] ' + CHAR(13) + 'ALTER DATABASE [' + d.name + N'] '
    + 'MODIFY FILE (NAME = N''' + mf.name + N''', FILEGROWTH = 10000 KB)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM    sys.master_files mf
    JOIN sys.databases d ON mf.database_id = d.database_id
WHERE   d.database_id > 1
    AND d.state_desc <> 'offline'
    AND mf.type_desc = 'LOG';

Original script from another answer here.


Keep count in temporary variable for later use in query

You need to base your insert statement on a select:

WITH my_count (cnt) AS (
  SELECT count(emp_name)
  FROM employee
  WHERE joined_date < '2017-11-30'::Date AND region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value)
select 'count des', '2016-11-30', cnt
from my_count;

Or use a scalar select:

WITH my_count (cnt) AS (
  SELECT count(emp_name)
  FROM employee
  WHERE joined_date < '2017-11-30'::Date AND region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value)
values
('count des', '2016-11-30', (select cnt from my_count));

Also, both methods could be implemented without a CTE. Taking the first query as an example, the variation without a CTE would look like this:

INSERT INTO my_summary (property_key, month, property_value)
SELECT 'count des', '2016-11-30', count(emp_name)
FROM employee
WHERE joined_date < '2017-11-30'::Date AND region = 'UK';


That's consistent with other objects, which cannot be DROPped except by the owner. It seems kind of painful for large objects, though; maybe bug -hackers?

I'd work around this by using oid references instead of lo, and then using vaccumlo to remove them once the references to them are removed.


What GUI tools can be used for PostgreSQL in Mac OS X?

Check the wiki page, many different tools available.


Unable to find server name of SQL Server

You can go with two methods as given in answers to the Stack Overflow question How to find server name of SQL Server Management Studio.

User PoweredByOrange:
Open up SQL Server Configuration Manager (search for it in the Start menu). Click on SQL Server Services. The instance name of SQL Server is in parenthesis inline with SQL Server service. If it says MSSQLSERVER, then it's the default instance. To connect to it in Management Studio, just type  . (dot) OR (local) and click Connect. If the instance name is different, then use .[instance name] to connect to it (for example if the instance name is SQL2008, connect to .\SQL2008).
Also make sure SQL Server and SQL Server Browser services are running, otherwise you won't be able to connect.

or:

User sansa:
Run this Query to get the name
SELECT @@SERVERNAME


How do I resolve Postgresql error, "PL/Perl function must return reference to hash or array"?

Short answer, coerce what you return to a string, should work fine in 8.4 and 9.1:

return URI->new('http://www.google.com/')->canonical().'';

See http://archives.postgresql.org/pgsql-bugs/2012-05/msg00096.php for my full response.


Is there a way in SQL Server to make a table only able to insert by trigger?

This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated (see final test case), another module cannot be signed without knowing the password for the Certificate, and if anyone changes any module that you sign (such as the Trigger), then the signature is automatically removed, alerting you to that change and then you can decide whether to resign it with the current changes or reject the changes ;-).

Also, trapping ApplicationName / ProgramName in a Trigger is not reliable as it is easy to pass in that value in a ConnectionString.

Please note that the Audit table is in a different Schema -- Auditing -- than the main table -- in dbo -- to prevent ownership-chaining, assuming that most Stored Procedures would also be in the dbo Schema.

The Setup

USE [...];
GO

CREATE CERTIFICATE [AuditCert]
    ENCRYPTION BY PASSWORD = 'Password Goes Here.'
    WITH SUBJECT = 'Restrict Insert Test';
GO

CREATE USER [AuditUser]
    FROM CERTIFICATE [AuditCert];
    -- no DEFAULT_SCHEMA for Certificate-based Users
GO

CREATE SCHEMA [Auditing]
    AUTHORIZATION [AuditUser];
GO

-- DROP TABLE [Auditing].[AuditLog];
CREATE TABLE [Auditing].[AuditLog]
(
    AuditLogID INT IDENTITY(1, 1) NOT NULL,
    AuditDate DATETIME2 NOT NULL
        CONSTRAINT [DF_AuditLog_AuditDate] DEFAULT (SYSDATETIME()),
    ImportantStuffID INT,
    Column2 VARCHAR(50),
    CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED (AuditLogID ASC)
);
GO

CREATE TABLE [dbo].[ImportantStuff]
(
    ImportantStuffID INT IDENTITY(1, 1) NOT NULL,
    Column2 VARCHAR(50),
    CONSTRAINT [PK_ImportantStuff] PRIMARY KEY CLUSTERED (ImportantStuffID ASC)
);
GO

CREATE TRIGGER [dbo].[AuditImportantStuff]
ON [dbo].[ImportantStuff]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
    SELECT  ins.[ImportantStuffID], ins.[Column2]
    FROM        inserted ins;
END;
GO

ADD SIGNATURE TO [dbo].[AuditImportantStuff]
    BY CERTIFICATE [AuditCert]
    WITH PASSWORD = 'Password Goes Here.';
GO

CREATE PROCEDURE [dbo].[AttemptDirectInsert]
(
    @ImportantStuffID INT,
    @Column2 VARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
    VALUES (@ImportantStuffID, @Column2);
GO

CREATE PROCEDURE [dbo].[ImportantStuff_AddData]
(
    @ValueForColumn2 VARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [dbo].[ImportantStuff] ([Column2])
    VALUES (@ValueForColumn2);
GO

CREATE USER [TestUser]
    WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [dbo];
GO

GRANT EXECUTE ON [dbo].[AttemptDirectInsert] TO [TestUser];
GRANT EXECUTE ON [dbo].[ImportantStuff_AddData] TO [TestUser];
GO

The Test

SELECT SESSION_USER, ORIGINAL_LOGIN();

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-1, 'test 1');

EXECUTE AS USER = 'TestUser';

SELECT SESSION_USER, ORIGINAL_LOGIN();

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-2, 'test 2');
-- Msg 229, Level 14, State 5, Line 102
-- The INSERT permission was denied on the object 'AuditLog', database '...',
--   schema 'Auditing'.

EXEC [dbo].[AttemptDirectInsert]
    @ImportantStuffID = -3,
    @Column2 = 'test 3';
-- Msg 229, Level 14, State 5, Procedure AttemptDirectInsert, Line 115
-- The INSERT permission was denied on the object 'AuditLog', database '...',
--   schema 'Auditing'.

INSERT INTO [dbo].[ImportantStuff] ([Column2]) VALUES ('test 4');
-- Msg 229, Level 14, State 5, Line 114
-- The INSERT permission was denied on the object 'ImportantStuff', database '...',
--   schema 'dbo'.

EXEC [dbo].[ImportantStuff_AddData]
    @ValueForColumn2 = 'test 5';
-- woo hoo!

SELECT * FROM [Auditing].[AuditLog];
-- Msg 229, Level 14, State 5, Line 122
-- The SELECT permission was denied on the object 'AuditLog', database '...',
--   schema 'Auditing'.

REVERT;

SELECT SESSION_USER, ORIGINAL_LOGIN();

SELECT * FROM [Auditing].[AuditLog];

EXECUTE AS USER = 'AuditUser';
-- Msg 15517, Level 16, State 1, Line 143
-- Cannot execute as the database principal because the principal "AuditUser" does not
--  exist, this type of principal cannot be impersonated, or you do not have permission.

UPDATE

Additional notes:

As @Paul mentioned in his answer, this method (as shown) does not prevent privileged Users from doing direct inserts. However, it is still possible to block DML actions not initiated from code signed with the Certificate via a Trigger on the Audit table **. But that mostly prevents accidental inserts since anyone in the db_owner fixed database role should be able to disable the Trigger, and there is potentially at least 1 work around that could be used by someone in the db_datawriter fixed database role if they are fairly crafty.
The method described and shown above doesn't need to use a Certificate. It would be possible to do the same setup using an Asymmetric Key. The nice thing about Certificates is that they are more portable since they can be backed up to a file or you can, starting in SQL Server 2012, extract the Certificate and its Private Key using the CERTENCODED and CERTPRIVATEKEY functions. This allows the exact same Certificate to be created in other databases and even other instances. This is quite helpful when there is cross-database functionality and you don't want to enable Cross-database Ownership Chaining and/or TRUSTWORTHY.
Since it is not apparently obvious from any of the test cases shown in either of the answers here, I will point out a fundamental difference between the two methods (which also happens to be the reason why I prefer Module Signing):

Using EXECUTE AS changes the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B INSTEAD OF mine.
Using Module Signing will add permissions to the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B IN ADDITION TO mine.

** I do have mostly-complete example code (about 75% done) for a Trigger on the Audit table that would disallow updates from anything but code signed by the Certificate, but ran out of time to complete it. The concept is that a lock is taken on the Certificate during the process, and the lock entry  includes the Certificate ID. You can verify that the Certificate ID is the desired Certificate and ROLLBACK if it isn't or no Certificate is used in the Transaction. The problem was that VIEW SERVER STATE is needed to use sys.dm_tran_locks. However, that is a fairly easy problem to solve as it can be granted via a Certificate-based Login, which can even be the same Certificate. In that case, the Certificate can be backed-up and restored into master for the purpose of creating the Login from it. Then just grant that Login the VIEW SERVER STATE permission, and finally sign the Trigger on the Audit table with that same Certificate (already in that DB as it was used to sign the Trigger on the base table).


Thanks for using sp_Blitz!

RESOURCE_SEMAPHORE is indeed a tough wait to run into.

  -Is the time in minutes?

The time is DD:HH:MM:SS, so you've had about 12 hours of it total.

  Is it a number to worry?

Your server has spent 12 hours waiting to get enough memory to run queries. That means queries that appear to be running are actually sitting idle waiting to get memory. How long has your server been up for?

  Can I conclude just by this that I need to buy more memory for the
  server?

That's one 'easy' way to deal with it, but you need to dig into what's going on when that wait type pops up.

For that, you'll want to try using sp_BlitzFirst.

To get total metrics since startup:

EXEC sp_BlitzFirst @SinceStartup = 1

To sample metrics during a 30 second window:

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

You could just plain not have enough memory here -- 30 GB of memory isn't much (for instance, my laptop has 32 GB of memory just to look at gifs and have 100 Chrome tabs open).

How much data are you storing on there?

You could also have awful queries using ridiculous amounts of memory.

To dig into those (if you're on a pretty recent build of a fairly modern version of SQL Server), you could run

EXEC sp_BlitzCache @SortOrder = 'memory grant' to find queries with large memory grants.

Hope this helps, and thanks again!


How would I index a text field that requires explicit characters and wildcards on both ends (postgres)?

trigram indexes are exactly what you do want.  In ILIKE '%abc%', the '%' invite variation, while the 'abc' are exactly 'abc'.  Maybe you are thinking of the <-> and % operators, which trigram indexes also support.  But you don't need to use them if you don't want to, you can just use ILIKE.


Enforce referential integrity between two groups of data

Thanks @Joel Brown, your comment pointed me in the direction I needed to go. This is what I ended up with, which satisfies all of my requirements:

CREATE TABLE RuleGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_rulegroups PRIMARY KEY (ID)
)

CREATE TABLE Rules
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    RequiredKeyInSourceData INT not null,
    RequiredValue int null

    CONSTRAINT pk_rules primary key (ID),
    CONSTRAINT fk_rules_groupID_on_rulegroups_ID FOREIGN KEY (GroupID) REFERENCES RuleGroups(ID) ON DELETE CASCADE
)

CREATE TABLE ActionGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_actiongroups PRIMARY KEY (ID)
)

CREATE TABLE Actions
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    SomeColumn INT,
    SomeOtherColumn INT
    -- ... more columns

    CONSTRAINT pk_actions primary key (ID),
    CONSTRAINT fk_actions_groupID_on_actiongroups_ID FOREIGN KEY (GroupID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)

CREATE TABLE RuleToActionMappings
(
    RuleGroup_ID INT NOT NULL,
    ActionGroup_ID INT NOT NULL

    CONSTRAINT pk_mappings PRIMARY KEY (RuleGroup_ID, ActionGroup_ID),
    CONSTRAINT uc_mappings_rulegroup_ID UNIQUE (RuleGroup_ID),
    CONSTRAINT uc_mappings_actiongroup_ID UNIQUE (ActionGroup_ID),
    CONSTRAINT fk_rulegroup_ID_on_rulegroups_ID FOREIGN KEY (RuleGroup_ID) REFERENCES RuleGroups(ID) ON DELETE CASCADE,
    CONSTRAINT fk_actiongroup_ID_on_actiongroups_ID FOREIGN KEY (ActionGroup_ID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)

How I'm using it:
Get the rule groups from the DB and match the input data against them:

select Rules.GroupID, rules.RequiredKeyInSourceData, rules.RequiredValue from Rules

Then in the application code:

partition the rules into sets (by GroupID)
match the sets against the input data

stop when a complete set matches or all rulesets have been analyzed
exec a select query to find the corresponding action set

select Actions.* from Actions
join RuleToActionMappings as m on m.ActionGroup_ID = Actions.GroupID
where m.RuleGroup_ID = @MatchedRuleGroupID


postgresql: hindi encode and decode error

The documentation tells you what exactly decode() returns:

Function                          Return Type   Description
-------------------------------------------------------------------------------
decode(string text, format text)  bytea         Decode binary data from textual
                                                representation in string.

'\xe0a4a8e0a4aee0a4b8e0a58de0a4a4' is the bytea output.  You need an additional call of convert_from() to turn this back to text:

SELECT convert_from(decode(encode('नमस्त', 'base64'), 'base64'), 'utf8');
 convert_from
──────────────
 नमस्त


postgresql - how and why indexes are bigger than their tables

Possible reasons:

Numerous and probably overlapping indexes on the table; have a look with \d
Bloat due to high update churn can sometimes affect indexes more than tables, depending on update patterns. Examine the size of each individual index to see if it makes sense.
GiST indexes, if used, can be quite large

Unlike what I originally thought this is not an issue with TOAST out-of-line storage not being counted, since pg_table_size includes TOAST tables.

Note that if you're concerned about index bloat and decide to REINDEX some of all of the involved indexes, consider setting a non-default FILLFACTOR first if the table is subject to lots of updates (or inserts+deletes). Otherwise you'll take a write performance hit because the index doesn't have any space to insert new values so it'll force lots of page splits and be less efficiently structured.


Is it possible to prevent SQL Agent Job from running if related job or previous instance has not finished?

Simplest thing is to have all the steps in a single job. It's built-in that a two instances of a job can't run at the same time.


Backing up MS SQL Server database creates a huge transation log

The statement "transaction log is getting properly truncated on the full backup but it immediately fills up after the backup" is not how backups operate though.  All transaction log truncation is done during the first transaction log backup after the full backup, the truncates are deferred until the full backup is completed.

What happens to transaction log backups during full backups?

If you are seeing a bigger transaction log after a full backup but before the first transaction log backup, that's typically by design.  However if you are saying you have almost no activity going on during the full backup which would cause a gig of activity then I would immediately start by looking at your jobs.  Often a reindex will cause a lot of logging but it would be odd for that to be the main culprit.  Carefully monitor what happens during that period and run the same queries.  Feel free to profile what is going on during that time as this could be a compound issue.


Best practices for selecting partition boundary ranges to maintain empty partitions at both end?

Let me start by mentioning that these 4 boundaries result in following 5 partitions.  Partitions 1 and 5 are unbounded one one side and partitions 2, 3, and 4 are constrained to quarterly data:

partition 1: values less than '2014-04-01'
partition 2: values >= '2014-04-01' and < '2014-07-01'
partition 3: values >= '2014-07-01' and < '2014-10-01'
partition 4: values >= '2014-10-01' and < '2015-01-01'
partition 5: values >= '2015-01-01'

The rationale for keeping (at least) the first and last partitions empty is to ensure expensive data movement is not required during partition maintenance of an incremental or sliding window scenario.  If data must be moved to a different partition to accommodate new boundaries during MERGE or SPLIT, the logging is about 4 times greater than normal DDL operations.  This is especially nasty with large tables, as is common with table partitioning.

Data movement is never required when an existing empty partition is split or when 2 adjacent empty partitions are merged.  This is why the best practice is to plan for such, making the maintenance a no-brainer that can easily be automated.

That said, one can split a non-empty partition without overhead in some cases.  For example, you could split the function to add the new '2015-04-01' boundary without data movement as long no existing rows are >= '2015-04-01'.  But to also avoid an expensive scan during the offline operation, you need an index with the partitioning column as the high-order key column so that SQL Server can efficiently validate no data exists beyond the new boundary.  So in your case, you could probably split the last partition in advance of the start of the next quarter without incurring overhead if the partitioning column is indexed (a common design pattern).

Assuming your objective is 4 quarterly partitions of historical data plus the current active quarter in a sliding window, I suggest bounded partitions for each of these 5 quarters plus another bounded one for the future quarter.  This will result in 8 partitions when you include the unbounded first and last ones.

CREATE PARTITION FUNCTION PfDateRange(date)
AS RANGE RIGHT FOR VALUES(
      '2014-01-01'
    , '2014-04-01'
    , '2014-07-01'
    , '2014-10-01'
    , '2015-01-01'
    , '2015-04-01'
    , '2015-07-01');

The reason one might want additional future empty partitions is to provide time to react in case partition maintenance is missed for some reason.  Multiple future partitions are more important with more frequent maintenance (e.g. daily), and less so for quarterly maintenance.

After the start of the '2015-04-01' quarter, the partition maintenance script can:

switch '2014-01-01' to a and empty staging table and truncate staging
merge the '2014-01-01' partition to remove it
spilt at '2015-10-01' for the next future quarter

You may want to do a sanity check before the maintenance in case you have bad data outside of the expected range (less than '2014-01-01' or greater than '2015-07-01').  Alternatively, maintain a check constraint for the expected range so that only valid data can be inserted.


Query for all the Postgres configuration parameters‘ current values?

SHOW ALL

The SHOW ALL command displays the current setting of run-time parameters in 3 columns.

SHOW ALL ;

pg_settings

The pg_settings view shows the same items as SHOW ALL but with additional details, across 17 columns versus 3 columns.

TABLE pg_settings ;

pg_file_settings

To read what is stored in the postgresql.conf file itself, use the view pg_file_settings.

If freshly written, this file will hold values that may not yet be in effect within the Postgres server. After writing, the settings must be loaded in one of various ways including a server restart.

TABLE pg_file_settings ;

Admin tool

Your databased-administration tool may display these settings.

For example, in pgAdmin 4, choose the cluster name (the Postgres installation) in the navigation panel, click the Dashboard tab, and in a list bottom panel titled Server activity, click the Configuration tab to see a list of your settings.

Writing

By the way… For info about writing these settings with ALTER SYSTEM commands, see my Answer on another Question, How to edit postgresql.conf with pgAdmin 4?.


I've got it working.
I first made a PS script in PowerShell ISE like this:

and then added it as a job in MSSQL


how to find a connection string value for a session in sql server?

My question is on sql server side, how can I know which session's connection string has pooling property set to true and which set to false?

You generally won't and can't. The connection string isn't sent to SQL Server, it's a directive for the driver on what information and options it needs in order connect and how it should act in certain situations. The connections string itself, however, is not sent to SQL Server and it's generally not possible to reconstruct it in its entirety just form the information surfaced by SQL Server.

In your example, the pooling options tells the driver whether or not to use connection pooling. SQL Server doesn't care about this, nor does it know about this as it's entirely a driver/application construct.


Get the quantity of sold products on a specific day, keeping all products

If you do a ddl you get quicker a good result.
you have to LEFT JOIN the tables to get the Rows with no partners.
Also add the where clause directly to the ON Clause or else you loose the gained rows.

CREATE TABLE products (
  `id` INTEGER Primary Key,
  `name` VARCHAR(6),
  `deleted_at` VARCHAR(4)
);

INSERT INTO products
  (`id`, `name`, `deleted_at`)
VALUES
  ('2', 'Apple', 'null'),
  ('5', 'Banana', 'null'),
  ('6', 'Pear', 'null');

CREATE TABLE Orders (
  `id` INTEGER PRIMARY KEY,
  `sale_date` DATE
);

INSERT INTO Orders
  (`id`, `sale_date`)
VALUES
  ('1', '2021-02-12'),
  ('2', '2021-02-12');

CREATE TABLE ProductOrders (
  `order_id` INTEGER,
  `product_id` VARCHAR(18),
  `quantity` INTEGER,
  `price` DECIMAL(10,2)
);

INSERT INTO ProductOrders
  (`order_id`, `product_id`, `quantity`, `price`)
VALUES
  ('1', '2', '2', '2.50'),
  ('1', '5', '3', '3.50'),
  ('2', '2', '1', '2.00'),
  ('2', '5', '3', '3.50');

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold
FROM products p
    LEFT JOIN (ProductOrders AS po
    INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id  = o.id)
    ON p.id = po.product_id
GROUP BY p.id, p.name,po.price
ORDER BY p.name;

prod_id | prod_name | quantity_sold
------: | :-------- | ------------:
      2 | Apple     |             1
      2 | Apple     |             2
      5 | Banana    |             6
      6 | Pear      |          null

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold
FROM products p
    LEFT JOIN (ProductOrders AS po
    INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-13') AS o ON po.order_id  = o.id)
    ON p.id = po.product_id
GROUP BY p.id, p.name,po.price
ORDER BY p.name;

prod_id | prod_name | quantity_sold
------: | :-------- | ------------:
      2 | Apple     |          null
      5 | Banana    |          null
      6 | Pear      |          null

db<>fiddle here


why Threads_connected falls down automatically in some cases

It sounds like there are a few long queries that lock lots of things, then runs for a while.  Meanwhile, other connections come and try to get at the locked things, but get stalled.

If you are using MyISAM, switch to InnoDB.

Set long_query_time to 1 and turn on the slowlog.  After a while, it will catch the naughty query.  Show us the query, plus SHOW CREATE TABLE for the table(s) that it touches.


How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?

I had similar problem. First turn on this option of SSMS - it is turned off by default:

SSMS/Tools/Options/Query results/SQL Server/Results to grid/Retain CR/LF on copy or save

Close SSMS, and run it again to get it work.

I use this query to get the full text of object definitions:

create view v_get_text_of_objects as

select top 100000
  [name]
, definition = replace(replace(object_definition(object_id),'create view','alter view'),'create proc','alter proc')
, len = len(object_definition(object_id))
, type
, type_desc
, create_date
, modify_date
from  sys.objects
where object_definition(object_id) like '%your text to find in sp%'
order by type, create_date desc

I like to display LEN of the sp text, so that I am sure it is not cropped.


No permissions to the certificate file when trying to restore a DB from a third party

It seems you have two SQL Server instances installed. LEVVEL and a default instance. Which one are we talking about? I.e., which one are you connected to?

Anyhow, both services uses a virtual service account, so privileges assigned to NETWORK SERVICE is irrelevant.

For the default instance, you should assign privileges to NT Service\MSSQLSERVER.

The the LEVVEL, you should assign privileges to NT Service\MSSQL$LEVVEL.

When you assign privileges, you need to type the above account name.


On SQL Server 2012, you can use LAG and SUM with an ORDER BY clause to get what you're after without any self-joins. LAG is used to figure out if the [End Date] value from the previous row for a partition is exactly one day away from the [Start Date] value of the current row. The running total is used to tie the groups together and you get your desired results with a simple aggregate at the end.

SELECT
  Code
, Decri
, MIN([Active Date]) [Active Date]
, MAX([End Date]) [End Date]
FROM
(
    SELECT
      Code
    , Decri
    , [Active Date]
    , [End Date]
    , SUM(start_new_group) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) group_id
    FROM
    (
        SELECT
          Code
        , Decri
        , [Active Date]
        , [End Date]
        , CASE WHEN DATEADD(DAY, -1, [Active Date]) = LAG([End Date]) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) THEN 0 ELSE 1 END start_new_group
        FROM #x
    ) t
) t2
GROUP BY Code, Decri, group_id;

Results:

╔══════╦═══════╦═════════════╦════════════╗
║ Code ║ Decri ║ Active Date ║  End Date  ║
╠══════╬═══════╬═════════════╬════════════╣
║ 1000 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1001 ║ ABC   ║ 2013-07-01  ║ 2014-06-30 ║
║ 1001 ║ ABC   ║ 2015-12-20  ║ 2017-12-20 ║
║ 1003 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1003 ║ ABC   ║ 2014-07-01  ║ 2017-12-20 ║
╚══════╩═══════╩═════════════╩════════════╝


Does my table qualify for Clustered Columnstore index?

You may find that a clustered columnstore index on your table works well but I would really want to avoid having that VARCHAR column in a columnstore index. One of the problems this can cause is dictionary pressure.

As you are looking to partition the table and alter data via partition switching I would suggest building a non-clustered columnstore index on your table that does not include the VARCHAR column that contains URLs. This will make the table read-only but that doesn't stop you loading data in and out via partition switching.

The way I have done this in the past is to bulk load the data into a staging table, do any post load processing there and then switch the whole table into an empty partition in the main table. It can be fiddly but very effective.

The other option is to use SQL Server 2016 which allows updateable non clustered columnstore indexes.


MySQL ranking one user based on cell value

There are two basic ways to do it:

A join (usually preferred in MySQL)

SELECT a.id, a.stage, count(b.id)+1 AS rank
FROM playertimes a
LEFT JOIN playertimes b on (a.stage=b.stage and a.fTime > b.fTime)
group by a.id, a.stage;

And a dependent subquery which may be a lot slower in some cases but might work OK for you

SELECT id, stage,
  (
    select count(1)+1
    from playertimes b
    where a.stage=b.stage and a.fTime > b.fTime
  ) AS rank
FROM playertimes a
order by id, stage;

You can check it at http://www.sqlfiddle.com/#!9/2d9580/3 - I added an index on (stage, fTime) which can make it faster in both cases.

Both solutions will assign the same rank to players with the same time (if it can happen at all) and then skip the "unused" ranks - like 1, 2, 2, 2, 5, 6, 6, 8.


How do I grant execute permissions to select client_addr from pg_stat_replication; for a regular PostgreSQL user?

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
DECLARE ip text;
BEGIN
select client_addr INTO ip from pg_stat_replication;
RETURN ip;
END;
$$ LANGUAGE plpgsql security definer;

OR

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication()
RETURNS text AS $x$
BEGIN
RETURN client_addr from pg_stat_replication;
END;
$x$ LANGUAGE plpgsql security definer;


MySQL 5.6 replication causes 'waiting for table lock'

It is possible that current replication thread is taking a lot of time updating a (big) change to a (big) table causing a long lock on the table. You may want to examine what the sql thread is trying to execute using mysqlbinlog with --start-position parameter. Hope this helps.


Oracle, in contrast with the name, can not predict the future.

If you have a retention policy configured as recovery window of 14 days or redundancy 2, the database does not know when your next full backup will succeed. Just because you have a full backup scheduled on every Saturady, that does not necessarily mean that backup will succeed and old backups can be deleted after that point of time.

Backups do not have an expiration date, unless you mean backups created with the KEEP option. For a backup created with the KEEP option, the database does list the time until it will be kept:

list backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7       Full    211.59M    DISK        00:00:18     2016-04-04 21:50:15
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20160404T214957
        Piece Name: /nasdata/exdb/0dr27io5_1_1
        Keep: BACKUP_LOGS        Until: 2017-04-04 21:49:57
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/system01.dbf
  2       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/myxml01.dbf
  6       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/users01.dbf
  9       Full 38380954   2016-04-04 21:49:57 /oradata/exdb/ut2.dbf

...

Notice the line:

        Keep: BACKUP_LOGS        Until: 2017-04-04 21:49:57

I created this backup with the keep until time 'sysdate+365' option.


A 32-bit MySQL on a 64-bit OS in 8GB of RAM:

The OS can access all 8GB.
The 32-bit MySQL is given no more than 4GB; it is impossible for it to use more than that.  Actually, it can't quite use all 4GB.
You could (should) reconfigure MySQL (via my.cnf) as if it were on a 4GB machine.
You could get a 64-bit version of MySQL
You could upgrade MySQL -- 5.0 is really antique.  Even 5.1 is 'dead'.  There is 5.5, rapidly aging, 5.6, 5.7, and soon 8.0.

More on configuring memory:  http://mysql.rjweb.org/doc.php/memory


Query for master in Postgres replication

There is no way through SQL to get master information from a slave, as Craig said.

One solution I came up once was to use pg_read_file to get the contents of recovery.conf file, as:

SELECT pg_read_file('recovery.conf');

With that information, we can easily parse it using a regular expression to get all config/values. I used the following to get it done:

SELECT DISTINCT ON (rm[1]) rm[1] AS name, coalesce(replace(rm[4], '''''', ''''), rm[2]) AS setting FROM (
SELECT row_number() OVER() rn, confs, regexp_matches(confs, '^[\s]*([a-z_]+)[\s]*=[\s]*([A-Za-z_\200-\377]([-A-Za-z_0-9\200-\377._:/]*)|''(([^''\n]|\\.|'''')*)'')') AS rm
FROM regexp_split_to_table(pg_read_file('recovery.conf'), '\n') AS confs
) AS recovery_confs
ORDER BY rm[1], rn DESC;

With that, it is just a matter of choosing your config:

WITH recconfs AS (
    SELECT DISTINCT ON (rm[1]) rm[1] AS name, coalesce(replace(rm[4], '''''', ''''), rm[2]) AS setting FROM (
    SELECT row_number() OVER() rn, confs, regexp_matches(confs, '^[\s]*([a-z_]+)[\s]*=[\s]*([A-Za-z_\200-\377]([-A-Za-z_0-9\200-\377._:/]*)|''(([^''\n]|\\.|'''')*)'')') AS rm
    FROM regexp_split_to_table(pg_read_file('recovery.conf'), '\n') AS confs
    ) AS recovery_confs
    ORDER BY rm[1], rn DESC
)
SELECT setting FROM recconfs WHERE name = 'primary_conninfo';

The regex was adapted from PG's source file guc-file.l, but I did not used all the possible variants, which means that it will only work for strings (unquoted or with single quotes), which is Ok for primary_conninfo.

There are, in my opnion, two problems with this approach:

I don't guarantee this regex is really 100% perfect, anyone can see a mistake?
Only a superuser can use pg_read_file, but that is not really a problem, as it is expected or you can wrap it on a function (I did that, because I wanted a non-superuser to check it, in my case was a REPLICATION user).


Filter rows on data saved as JSON arrays

You can't really do this unless you are using MySQL 5.7 and the category column is using the JSON datatype. If it is, you can use the JSON_CONTAINS function:
SELECT * FROM `yourtable`
WHERE JSON_CONTAINS(category, '"11"')

Pay attention to the rules of containment:

A candidate scalar is contained in a target scalar if and only if they are comparable and are equal. Two scalar values are comparable if they have the same JSON_TYPE() types, with the exception that values of types INTEGER and DECIMAL are also comparable to each other.

A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target.

A candidate nonarray is contained in a target array if and only if the candidate is contained in some element of the target.

A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key.

We can see the issue with datatypes in this dataset:
CREATE TABLE myjson (
  id tinyint unsigned primary key auto_increment,
  category JSON
);

mysql> SELECT * FROM myjson;
+----+---------------+
| id | category      |
+----+---------------+
|  1 | ["2", "3"]    |
|  2 | ["254", "34"] |
|  3 | ["11", "25"]  |
|  4 | [2, 11]       |
+----+---------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM `myjson` WHERE JSON_CONTAINS(category, "11");
+----+----------+
| id | category |
+----+----------+
|  4 | [2, 11]  |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `myjson` WHERE JSON_CONTAINS(category, '"11"');
+----+--------------+
| id | category     |
+----+--------------+
|  3 | ["11", "25"] |
+----+--------------+
1 row in set (0.00 sec)


You have to install management tools from sql server 2005/2008 or 2008R2 to manage sql server 2000.

Profiler from sql server 2014 wont work. There were problems from 2014 using profiler to 2005 - this is fixed in 2014 CU2.


Why isn't my new index/table being created on the filegroup I specify?

First thanks to everyone who tried to help me with this here and on twitter.  We did however finally find the answer.  It turns out that someone created a database trigger on model.  The trigger parses every create index/table command and changes it to a DATA filegroup if it's a heap or clustered index and an INDEX filegroup for everything else.  We were then able to track the trigger back to a policy.


WAL log entry for CREATE TABLE statement

wal_level is not specific to "certain queries". It changes the overall information written into a WAL record (e.g. to support a hot-standby).

Even non-transactional statements are logged e.g. create database or nextval().

The only statements that are not logged are statements dealing unlogged tables (and -  currently - hash indexes).

The Write-Ahead-Log is not for managing transactions, it's for managing durability.


Restore Database with Multiple Restore commands

The following code will restore the database to the desired location:

RESTORE DATABASE x
FROM DISK = 'Filepathofbackupfile.bak'
WITH RECOVERY,
MOVE 'logicalDatafilename' TO 'Newfilepath.mdf',
MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'


SQL Server has several built-in possibilities to secure data on a columnar basis.

ENCRYPTBYPASSPHRASE - use this to encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.
ENCRYPTBYKEY - use this to encrypt the data with a key stored in the master database.  Without access to the key, the data cannot be recovered.

Take the following example using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE:

IF OBJECT_ID(N'dbo.EncryptedData', N'U') IS NOT NULL
DROP TABLE dbo.EncryptedData;
CREATE TABLE dbo.EncryptedData
(
    EncryptedDataID int NOT NULL IDENTITY(1,1)
        CONSTRAINT EncryptedData_PK
        PRIMARY KEY CLUSTERED
    , SomeData varbinary(1000) NOT NULL
    , SomeSalt binary(10) NOT NULL
        CONSTRAINT EncryptedDataSalt
        DEFAULT CRYPT_GEN_RANDOM(10)
);

DECLARE @passphrase nvarchar(100);
SET @passphrase = N'FlashFesterMariaAyin';

INSERT INTO dbo.EncryptedData (SomeData)
VALUES (ENCRYPTBYPASSPHRASE(@passphrase, N'This is a test', 1, N'SomeSalt'))

This shows the encrypted data held in the row:

SELECT ed.EncryptedDataID
    , ed.SomeData
    , ed.SomeSalt
FROM dbo.EncryptedData ed;

The encrypted row:

╔═════════════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╦════════════════════════╗
║ EncryptedDataID ║ SomeData                                                                                ║ SomeSalt               ║
╠═════════════════╬════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╬════════════════════════╣
║ 1               ║ 0x01000000DD0FF92C67C40F30C130CED841677AB69D34765D6FD57112AC35F59A87395101B32A145E3D42F8889ECF9BDEEC45F970EDD35432178CBDE896AAC27E9363AA20D09782A385048447 ║ 0x9DB6EDA465A5F93C7ADD ║
╚═════════════════╩════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╩════════════════════════╝

This shows how to display decrypted data:

SELECT ed.EncryptedDataID
    , CONVERT(nvarchar(100), DECRYPTBYPASSPHRASE(@passphrase, SomeData, 1, N'SomeSalt'))
    , ed.SomeSalt
FROM dbo.EncryptedData ed;

The decrypted row:

╔═════════════════╦══════════════════╦════════════════════════╗
║ EncryptedDataID ║ (No column name) ║ SomeSalt               ║
╠═════════════════╬══════════════════╬════════════════════════╣
║ 1               ║ This is a test   ║ 0x9DB6EDA465A5F93C7ADD ║
╚═════════════════╩══════════════════╩════════════════════════╝

The SomeSalt column is a pseudo-randomly-generated value used to guard against whole-value-substitution attacks, and provides significant protection against rainbow-table attacks.  Whole-value-substitution attacks consist of copying the encrypted value from one row to another row in order to bypass the need to know the encryption passphrase.


Restore Error: Could not upgrade the metadata

We were able to resolve the issue by upgrading the lower environment to SP3.  Once that was done the restore completed without any errors.


According to Dave Stockes, a MySQL Community Manager for Oracle:
(from his blog post: MySQL 8 is coming)

  Years ago, before the Sun Microsystems purchase of MySQL AB, there was a version of MySQL with the number 6. Sadly, it was a bit ambitious and the change of ownership left it to wither. The MySQL Cluster product has been using the 7 series for years. With the new changes for MySQL 8, developers feel they have modified it enough to bump the big number.

Wikipedia article about MySQL also has these events:

  Sun Microsystems acquired MySQL AB in 2008.
  Version 5.1: production release 27 November 2008 (event scheduler, partitioning, plugin API, row-based replication, server log tables)
  MySQL 5.1 and 6.0-alpha showed poor performance when used for data warehousing – partly due to its inability to utilize multiple CPU cores for processing a single query.
  MySQL Server 6.0.11-alpha was announced on 22 May 2009 as the last release of the 6.0 line. Future MySQL Server development uses a New Release Model. Features developed for 6.0 are being incorporated into future releases.
  Oracle acquired Sun Microsystems on 27 January 2010.
  Work on version 6 stopped after the Sun Microsystems acquisition. The MySQL Cluster product uses version 7. The decision was made to jump to version 8 as the next major version number.

And for anyone who wants to see the code, here is a repository: MySQL 6.0 binaries and source code (6.0.11-alpha).


One easy way to do this is with HeidiSQL, which can connect to SQL Server and MySQL and is free and open source (https://www.heidisql.com/). Once you set up both connections, you can use the Export Database As SQL feature to transfer data directly between databases.

In the Session Manager, set up the connection to the MySQL and SQL Server computers
Connect to the MySQL server, right-click on the server in the browser on the left, and select "Export Database As SQL".
in the table list, select the table(s) you would like to export
in the Output: on the right, you will see a list of all servers you have configured in the Session Manager. Select the SQL Server 2012 instance.
You will probably want to keep the database the same so leave it as [Same as on source server]
You likely will want to check the create boxes for both database and tables if this is the 1st time you are transferring data
If you want to transfer the data and not just the table structure, in the Data: dropdown select the option that is best for you. I usually use Delete + insert (truncate existing data), but your needs may be different.
Click Export in the bottom right

That should do it.


Connecting SQL Server 2008 R2 to SQL Server 2000

Running SQL Server Management Studio on SQL Server 2000 is not really as supported option. Your best bet is to either upgrade the 2000 instance or to use SQLCMD (which should still work) to manage the instance from the command line.

You might be able to connect to a SQL Server 2000 from management studio 2005 (if you can find the install media).


Update query with two different where conditions

You can use a case expression:

UPDATE students
    set status = CASE WHEN _ID = 1 THEN true
                                   ELSE false
                 END;

A bit shorter (but perhaps not as obvious) is:

UPDATE students
    set status = COALESCE(_ID = 1,false);

COALESCE is need if _ID can be NULL.

One may argue that it is not necessary to update status at all times (for efficiency reasons). If we take into consideration that both _ID and status can be null there are 9 different situations (we let _ID = 0 represent all rows where _ID is not null and _ID <> 1).

_ID    Status    Action
-----------------------
null   null      set false
null   true      set false
null   false     -
   0   null      set false
   0   true      set false
   0   false     -
   1   null      set true
   1   true      -
   1   false     set true

All Actions is handled by:

set status = COALESCE(_ID = 1,false);

So we need to filter out the 3 situations where no update should take place:

(null, false), (0, false), (1, true)

In other words, our WHERE clause should include the other 6 combinations:

UPDATE students
    set status = COALESCE(_id = 1,false)
WHERE ((coalesce(_id, 0) = 1) <> coalesce(status,(coalesce(_id, 0) <> 1)))

This is not trivial (IMO), and even though it probably can be simplified I would not recommend to use it in any real situation (I would recommend disallowing nulls for boolean attributes in the first place, but that's another story).

A useful technique (once again IMO) to verify boolean expressions with truth tableau's, is to use CTE's for the domains and investigate the result. Example from above:

with status (s) as ( values (null),(false),(true))
  ,     _id (i) as ( values (null),(0),(1))
select i, s from status cross join _id
where ((coalesce(i, 0) = 1) <> coalesce(s,(coalesce(i, 0) <> 1)));

IS DISTINCT FROM as demonstrated by @Erwin Brandstetter is a nice abstraction that is very useful in situations like these.


If you have a numbers table populated with integers up to the max length the strings in the data can be

SELECT Y.*
FROM   YourTable Y,
(SELECT 4 AS NumChar) T /* Change this line to alter desired length*/
WHERE  EXISTS (SELECT *
               FROM   Numbers N
               WHERE  N.N < LEAST(LENGTH(name), LENGTH(email)) - FLOOR(NumChar/2)
                      AND GREATEST(LOCATE(SUBSTRING(Name, N, NumChar), email),
                                   LOCATE(SUBSTRING(email, N, NumChar), Name)) > 0)

SQL Fiddle


Can you help clear confusion of ORACLE Timestamp with Local Time Zone?

Normalized means that the timezone information is not stored. When storing such data, the timestamp value is automatically adjusted to be in the same time zone as the database server. When retreiving such data, the timestamp value is automatically adjusted to be in the same time zone as the client.

For example, I simulate that my client is in a different time zone:

$ export TZ=America/Los_Angeles
$ sqlplus user/password@orcl

Create a table to store timestamp values:

SQL> create table t1 (t timestamp with local time zone);

Table created.

Now notice how my local (client) time is different (current_timestamp) than the server time (systimestamp):

SQL> select current_timestamp, systimestamp from dual;

CURRENT_TIMESTAMP                   SYSTIMESTAMP
----------------------------------- -----------------------------------
29-DEC-16 06.36.09.364628 AM -08:00 29-DEC-16 03.36.09.364623 PM +01:00

Now I insert my local time:

SQL> insert into t1 (t) values (current_timestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 06.36.32.100571 AM

Just what I expected. But if I examine the real contents:

SQL> select dump(t) from t1;

DUMP(T)
----------------------------------------------------
Typ=231 Len=11: 120,116,12,29,16,37,33,5,254,151,120

TIMESTAMP is stored as:

1st byte: First two digits of the year: 20 + 100 = 120
2nd byte: Last two digits in the year: 16 + 100 = 116
3rd byte: Month: 12
4th byte: Day: 29
5th byte: Hour: 15 + 1 = 16
6th byte: Minute: 36 + 1 = 37
7th byte: Second: 32 + 1 = 33
8th-11th bytes: fractions of second

The timezone information would be in the 12th and 13th byte, but it is not stored with this datatype.

So the actual data stored is 2016-12-29 15:36:32:..., because my database is in the CET timezone.

If I pretend to be in another different timezone, my query will return a timestamp adjusted to that timezone:

$ export TZ=Asia/Hong_Kong
$ sqlplus user/password@orcl

SQL> select t from t1;

T
---------------------------------------------------------------------------
29-DEC-16 10.36.32.100571 PM


SQL Server Management Studio is not available

to connect to local server and default instance just use any of these ., local, localhost or YourMachineName.

If you are trying to connect to a named instance on your local machine just use any of the above followed by \InstancenName.

i.e  .\InstanceName, local\InstanceName, localhost\InstanceName or YourMachineName\InstanceName.

Note

If you want to connect to the default instance you just use the machine name, if you are trying to connect to a Named Instance then you have to use the machine name and the Instance Name.


Properly pivot an XML column with XQuery

I'm probably missing the point of your question but here is what I would do to get the result you are looking for.
with xmlnamespaces('ModelDescriptive' as dsc)
select M.X.value('@ref', 'varchar(max)') as ref,
       M.X.value('@baseRef', 'varchar(max)') as baseRef,
       M.X.value('@type', 'varchar(max)') as type,
       M.X.value('@productionGroup', 'varchar(max)') as productionGroup
from @xmlDescriptive.nodes('//dsc:Material') as M(X);

Your use of local-name() and //*[text()], //@*' suggests you want some kind of generic pivot but you use Material and the attribute names hard coded in the query so I don't know why you went down that road.