Autovacuum is not running or working We're currently using Postgres 9.4.14 hosting Zabbix 2.4 database. For a while now, the following message started to happens:

< 2017-11-23 15:15:20.037 -03 >HINT:  Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.048 -03 >WARNING:  oldest xmin is far in the past
< 2017-11-23 15:15:20.048 -03 >HINT:  Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.059 -03 >WARNING:  oldest xmin is far in the past

We restarted the datase and after startup the same message start to be happen:

< 2017-11-22 10:25:04.262 -03 >LOG:  database system was shut down at 2017-11-22 10:00:04 -03
< 2017-11-22 10:25:04.387 -03 >LOG:  MultiXact member wraparound protections are now enabled
< 2017-11-22 10:25:04.389 -03 >LOG:  autovacuum launcher started
< 2017-11-22 10:25:04.389 -03 >LOG:  database system is ready to accept connections
< 2017-11-22 10:25:15.910 -03 >WARNING:  there is no transaction in progress
...
< 2017-11-22 10:25:20.259 -03 >WARNING:  oldest xmin is far in the past
< 2017-11-22 10:25:20.259 -03 >HINT:  Close open transactions soon to avoid wraparound problems.

Vacuum daemon is running:

postgres@bchlnx361:pg_log $ ps -fea | grep vacuum
postgres   409   403  0 Nov22 ?        00:00:02 postgres: autovacuum launcher process

and there are my postgresql.conf settings..

maintenance_work_mem = 2280MB
autovacuum_work_mem = -1
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 2
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 5000
autovacuum_analyze_threshold = 50000
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 1000000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1

But tables are'nt being vacuumed, when I've checked on pg_stat_user_tables, all rows has last_Autovacuum with null values, and some tables really need to be vacuumed:

zabbix_db=# select tuple_count, dead_tuple_count from pgstattuple('public.triggers');
 tuple_count | dead_tuple_count
-------------+------------------
       73657 |          5161183
(1 row)

Someone could help as?

Regards,

We finally found the problem!! This database had a Standby Read-Only database but We broked the replication, removed all Standby parameters but We forgot to remove the  replication slot.
Yestarday we tried to remove the replication slot but We couldn't  because the database was not in standby mode:

zabbix_db=# select pg_drop_replication_slot('standby2');
ERROR:  replication slots can only be used if wal_level >= archive

We had to

Shutdown the database,
Change wal_level to archive Startup
Run again the pg_drop_replication (now it works)
Shudown
Disable wal_level
Startup

And.. the autovacuum comes back!!

postgres 19381 19089  1 13:07 ?        00:09:59 postgres: autovacuum worker process   zabbix_db
postgres 19411 19089  2 13:07 ?        00:11:20 postgres: autovacuum worker process   zabbix_db
postgres 19430 19089  1 13:07 ?        00:09:51 postgres: autovacuum worker process   zabbix_db


SQL Server Log Shipping: Where is the hottest IO during RESTORE LOG: To db or to log? Using SQL Server 2008.

I have used SQL log shipping for years. Works great.

Working with a new standby server (recipient of log shipping), I am seeing very poor log restore performance, and wonder: Of the MDF and LDF, is one hit particularly harder than the other during log restore?

Background:
With dedicated hardware, I have no problem with this area. (To be fair, I have always been unhappy with the ldf size on the target... but what are ya going to do with a db in restore state?)

I am now experimenting with a cloud server and block storage. So far.... I am rather underwhelmed with the IOPS of block storage. The log restore operation is really slow (our main db server puts out a log file every 4 mins, and it takes 2+ mins to restore each one on the target!).

So I am debating whether:

get a faster block storage device (mucho $$)
--- But this only makes $$ sense for either the LDF or MDF... If I have to do both, it is just too expensive (ergo my question)
figure out another solve for SQL Server in the cloud
Forget the cloud for SQL Server, 'cause the iops just aren't there...

Further digging around uncovers: https://serverfault.com/questions/261631/temporary-io-boost-for-an-mssql-server

Which indicates that the hot IO is going to be to the LDF... and that would be the spot to optimize... (though "restore log" is not explicitly the agenda of this thread).

Are we agreed that boosing the IOPS of the LDF disk should boost RESTORE LOG performance?

Thank you!

The data is in:

Improving speed of the LDF storage makes a dramatic improvement in log restore time.

Thank you all for the input!

What I did:

I am using Rackspace Cloud servers. Not their "SQL Server" instances, just plain jane Win 2008 images, that I load SQL 2008 onto. For storage I use Rackspace block storage. I also evaluated Amazon EC2 with EBS (the Amazon block storage).

The core questions I was attacking:

1) Is it viable to run SQL Server on a cloud server for a 300+ GB database?

2) Is there benefit for the extra spend to get the Rackspace SSD based block storage? (It is rather pricey).

The methodology I used:

I did two kinds of testing:

i) SQL IO test tools, to measure disk performance of the block storage, and to compare to dedicated hardware (both "on the metal" and in virtual machines)

ii) An actual restore of a big database, and automated log shipping from the production database.

Answers:

a) Rackspace regular block storage is much faster than Amazon AWS (EBS). Much much.

b) Based on my measurements and experience, I would not put a production SQL Server DB onto Amazon EBS. There are just too many "pauses" and other bad performance issues. Note that SQL Server will work on EC2 w EBS -- many folks do it -- just that based on my measurements there will be IO delays (a lot of them). This will particularly impact RESTORE LOG operations, and all write-intensive operations (reads can be cached... but LDF writes are not cached...).

c) Rackspace cloud with regular block storage can absolutely be used for SQL Server. It is not mind blowing fast, but it is great for reads, and OK for writes.

d) Rackspace SSD block storage makes a dramatic difference for log restore operations, and (I extrapolate here), any write-intensive use of SQL server will get a big boost.

So the bad news is: When you are pushing around a 500gb database, you can't get the performance of a $150K SAN for $300/month.

The good news is: You can get really decent performance, that matches or beats RAID5 from a virtual machine on dedicated hardware, for about $480/month

The Data

Here is the data of the log restore operation, showing first the duration of log restore operations on the slower system, then on the faster system, and then the difference. This log restore is happening every 10 mins on both systems.


How to best handle MAX within a query/view I have two tables named "Visit" and "VisitMovement" and one Visit can contain many VisitMovements.

Within my query I need to join to the latest record for the Visit in VisitMovement so I can get back the latest VisitMovement details.

To do this I have a view like this that I join to:

SELECT
    VisitID,
    max(VisitMovementID) as VisitMovementID
FROM
    dbo.VisitMovement
GROUP BY
    VisitID

and finish up with:

    SELECT
        ......
        FROM
            Visit V
        INNER JOIN VisitMovement VM ON
            V.VisitID = VM.VisitID
        INNER JOIN vwLatestVisitMovement LVM on
            VM.VisitMovementID = LVM.VisitMovementID

I can't Index this View due to the max operator, and I can see over-time this becoming a performance issue with the Index Scan operation.

I wondered if anyone had any good ideas on how to approach this? I was thinking of having a trigger and maintaining the latest VisitMovementID in the Visit table but this doesn't sit well with me.

I think (not very clear) that you want something either:

For a VIEW solution - use something like this

SELECT v.visitor_id AS vid, v.visitor_name
FROM visitor v
INNER JOIN vw_max_visit_movement vmw ON
v.visitor_id = vmw.visitor_id;

Personally, I might want to use a CTE (unless I had millions of visitors), as per here. The reason I mentioned that you might want to avoid this approach for large tables is best explained here.

WITH visitor_max AS
(
  SELECT visitor_id, MAX(visit_movement_id)
  FROM visit_movement
  GROUP BY visitor_id
)
SELECT v.visitor_id AS "Vistor No.", v.visitor_name AS "Visitor name"
FROM visitor v
INNER JOIN visitor_max vmax ON
v.visitor_id = vmax.visitor_id;

In any case, the results are the same (not sure if this is what you're asking - if not, please expand the question)

Vistor No.  Visitor name
         2        Paulie
         3           Jim
         4          Bill
         5          Fred

===  DDL and DML for the tables used =====

CREATE TABLE visit_movement
(
  visit_movement_id INTEGER,   -- possibly a timestamp?
  visitor_id INTEGER
);

INSERT INTO visit_movement
VALUES
(34, 2),
(35, 2),
(34, 2),
(37, 3),
(38, 3),
(39, 3),
(40, 3),
(41, 4),
(42, 4),
(43, 5);

CREATE TABLE visitor
(
  visitor_id INTEGER,
  visitor_name VARCHAR (20)
);

INSERT INTO visitor
VALUES
(1, 'Mary'),
(2, 'Paulie'),
(3, 'Jim'),
(4, 'Bill'),
(5, 'Fred'),
(6, 'Xavier');

CREATE VIEW vw_max_visit_movement AS
SELECT visitor_id, MAX(visit_movement_id)
FROM visit_movement
GROUP BY visitor_id;


Deadlock graph: Show SQL statement containing the phrase "password" We need to debug a MS SQL Server 2014 deadlock situation. We got our deadlock graph, but the statement seems to contain the string "password", so the SQL is obfuscated ("*password---..."). Is there a way to turn this behaviour off and get the actual statement?

No, there is no trace flag or way to override this behavior.

If any statement has a restricted word in it, it will be overwritten with "-------" or "** restricted **". This is the same whether it is a trace, in cache, or using XEvents.


SQL Server - Inconsistent, hidden machine precision error when truncating result of LOG to integer I noticed some strange rounding behavior in SQL Server (2008R2 at least) when casting log base 10 of 1000 to an integer. The answer is clearly the exact value 3, but there must be some decimals SQL server is hiding from the output:

SELECT LOG(1000, 10)--returns 3 with no visible decimals
SELECT CONVERT(INT, LOG(1000, 10))--returns 2

I reverse-engineered the minimum epsilon to get the correct value when added to the result of LOG before conversion as approximately:

SELECT CONVERT(INT, LOG(1000, 10)+0.0000000000000002220446049250313)
SELECT CONVERT(INT, LOG(1000, 10)+0.0000000000000002220446049250312)

It seems I could go on all day making this value more precise.

This only appears to be necessary for the case of 1000, as I get the proper integer values for 1, 10, 100, and 10000 and larger without using any epsilon term.

The error term appears to be the tail of the Taylor Series expanded out 16 terms (as per this page), but why is SQL Server suddenly behaving as expected (no hidden machine precision error) when I try this for the case of 10000 and larger?

Why is SQL Server inconsistently demonstrating a machine precision error?

Thanks.

The problem is simpler than that.  LOG outputs a floating point number, and you should always ROUND when converting form a floating point value to an int instead of truncating.

What you are doing is similar to:

select cast(0.99999999999999999999999999999999999 as int)

The result of LOG(1000,10) is not 3, which you can see

select convert(varchar(200),LOG(1000, 10), 3)

is closest to

   2.9999999999999996e+000

(although internally the significand is stored in base-2).

There's nothing inconsistent about this behavior.  LOG is an estimate based on a series expansion that converges to the logarithm, and the result is returned with a limited amount of precision.

The LOG function is designed to return a result accurate to 15 digits of decimal precision, but depending on the arguments the result might be slightly higher or slightly lower than the correct answer.


Is it possible to resize a blob image? I've got a table with an image blob field in it, which stores jpg files.

As those jpg files are shown in a web page, I would like to resize them in order to spend less bandwidth when my web page call them.

First, I thought I could resize a jpg file, stored in blob field, when doing a select statement and using command ORDimage.process. But it seems it's not possible.

CREATE OR REPLACE FUNCTION ORGPOLV2.resize_img (i_cod_op INTEGER)
      RETURN blob
   IS
      v_photo     blob ;
   BEGIN

     select img_simbolo_op
      into v_photo
      from tbl_op
      where cod_op=i_cod_op for update;

     ordimage.process(v_photo, 'maxScale=32 32');

 update tbl_op set img_simbolo_op = v_photo where cod_op=0;
--
commit;

    select img_simbolo_op
    into v_photo
    from tbl_op
    where cod_op=0;

      RETURN v_photo;
   END resize_img;
/

With this function, I got error:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "RESIZE_IMG", line 10

My question is: Is it possible to resize a jpg file in a blob field?

Maybe my approach with ordimage.process is wrong and there is another way.

Try this:

CREATE OR REPLACE FUNCTION resize_img (i_ID INTEGER)
   RETURN BLOB
IS
   vImageData     BLOB;
   vSizedImage BLOB;

BEGIN

  SELECT img_simbolo_op
         INTO vImageData
         FROM tbl_yourtable
        WHERE my_ID = i_ID;

  DBMS_Lob.createTemporary(vSizedImage, FALSE, DBMS_LOB.CALL);

  ORDSYS.OrdImage.processCopy(vImageData, 'maxScale=75 75', vSizedImage);

  return vSizedImage;

END resize_img;


DNS Alias for SQL Instance - LOTS of Instances - All going to default instance We run an SQL server (2012 SP4) that has 40+ named instances (one for each client) and the default instance. We are thinking about replace the single server with multiple servers over the next few years. I'd like to setup a DNS alias for each of the instances so that we can make switch for the users more seamless (plus the instance names are long and gross and we get users complaining about them all the time).

We also have software the uses the full instance name (Server\Inst1) to connect, and i'd like to keep that functionality.

I was trying to implement DNS Aliases, the alias connects, but only connects to the default instance. Below is what i've setup so far, I'm not sure what is causing all of the aliases to connect only to the default instance but the normal connection (server1\inst1) works fine. It seems like it should work. What am i missing?

Server: Server1

Network Cards: 2
1. normal traffic and default instance (10.10.0.236)
2. SQL Alias connections w/ 40+ IP's all pingable; separate NIC so that "Register this connection address in DNS" can be unchecked and manually setup.

DNS: Host A records for all Aliases created. Verified that pinging the Alias resolves to the correct IP.

Instance: Server1\Inst1; DNS Alias: Inst1; IP for Inst1: 10.10.0.237;

SQL Server Config Manager:
Network Config for Default Instance:
SM/NP/TCP Enabled;
TCP: ListenAll=No;
entry for 10.10.0.236 TCP port 1433 - enabled;
Removed Port from IPAll;

Network Config for Inst1:
SM/NP/TCP Enabled;
TCP: ListenAll=yes; (to support normal Server\Inst1 connection)
Changed entry to 10.10.0.237 TCP port 1433 - enabled;
Left Port in IPAll;

Breakdown of the below query results for different types of connections:

    SELECT @@Servername, @@ServiceName
    SELECT * FROM sys.dm_exec_connections WHERE session_id = @@SPID

Alias INST1 - SQL Authentication

    @@Servername=Server1, @@ServiceName=MSSQLSERVER
    SQL Auth: Net_Transport=named pipe; Auth_Scheme=SQL

Alias INST1 - Windows Authentication

    @@Servername=Server1, @@ServiceName=MSSQLSERVER
    Net_Transport=named pipe; Auth_Scheme=NTLM

Server1\Inst1 - SQL Authentication

    @@Servername=Server1\Inst1, @@ServiceName=Inst1
    Net_Transport=TCP; Auth_Scheme=SQL

Server1\Inst1 - Windows Authentication

    @@Servername=Server1\Inst1, @@ServiceName=Inst1
    Net_Transport=TCP; Auth_Scheme=NTLM

.
.
.
EDIT (Solution):

There were two changes that i had to make to get this to work:

Disable IPAll and manually configure both 10.10.0.236 connection
Full Inst1 Network config now:
Protocol tab:
ListenAll=no;
IP Address tab:
CUT port from IPAll to IP(n) that had 10.10.0.236 IP; Enabled .236 IP; (to support normal Server\Inst1 connection)
Changed entry to 10.10.0.237 TCP port 1433 - enabled; (in my case i had to manually overwrite one of the unused IP(n) connections because .237 wasn't showing up)
After restarting the SQL Inst1 instance i had to restart the SQL Browser before it would actually update the port listeners and link .237:1433 with Inst1

Thank you David Browne - Microsoft & BradC for responding.

Below is what i've setup so far, I'm not sure what is causing all of
  the aliases to connect only to the default instance but the normal
  connection (server1\inst1) works fine. It seems like it should work.
  What am i missing?

You're allowing Named Pipes connections to the default instance:

@@Servername=Server1, @@ServiceName=MSSQLSERVER
SQL Auth: Net_Transport=named pipe; Auth_Scheme=SQL

You need to turn off Named Pipes if you want to control connectivity through specific IP addresses and ports.  It's only the default instance that listens to the \\.\pipe\sql\query pipe. Named instances will listen on a pipe called \\.\pipe\MSSQL$SOMEINSTANCE\sql\query.  And all the named pipes are available on all the IP addresses.

So basically in your case, the client is connecting to

\\INST1\pipe\sql\query

Which is the default instance's pipe. When a named pipes client connects to the server without an instance name, it will always connect to the default instance's pipe.


How to combine not like and like? Using MariadB, how can I combine NOT LIKE and LIKE, so that I can search for the string FHD but exclude from the searches if the string H265 is also present?

This didn't work for me:

SELECT  `id` FROM `qualities`.`streams` WHERE quality LIKE '%FHD%' AND NOT LIKE '%H265%';

You probably got an error like Incorrect syntax near the keyword LIKE, because each of the ("NOT LIKE" and "LIKE") conditions needs to specify the column is is working on. If so, this should work:

SELECT  `id` FROM `qualities`.`streams` WHERE quality LIKE '%FHD%' AND quality NOT LIKE '%H265%';


Get rows inserted and updated dynamically in SSIS I need to get the rows inserted and updated in each table through my SSIS package.
Is there any way to do it except using the row count transformation component  before each insert or update component.

Is there any option in SSIS that provide that information without doing it manually?

It all depends on your SSIS version, but see Integration Services (SSIS) Logging in the official documentation.

Assuming a reasonably modern deployment I can show you an example of SQL 2012 Basic Logging for SSIS packages. It's very simple, as it is the default configuration, and all you need to do is drill down the provided reports by right clicking on your package and selecting the appropriate report.

First confirm your logging level in SSISDB:

Basic reporting will include a final rows written for your destination. It doesn't provide row counts throughout, if you want that level of detail either increase the logging level (will affect performance and storage!) or create your own solution.

You should be able to query all of the log tables and generate your own reports. Microsft just ships a pre-built set of reports that sit atop the basic logging.


Basic Geometry Representation I have an assignment in my introduction to database course. I chose to try and do a database model to represent basic geometric figures and mathematical objects (functions, polynomes, etc). For example, my db should be able to store (partially) what you can make with a software like GeoGebra (https://www.geogebra.org/classic).

I am doing an ERD, but I have some problems representing relations and entities... The base of my model is the entity Point with three attributes label, x and y. I am having troubles representing figures that are solely composed of Point, but that can contain more Point, e.g.. polygons (composed of N > 3 points, and there can be points INSIDE the polygon area), ellipses (characterised by 3 points, but can contain more points on the edge), etc.

For example, how do I represent a polygon? Is it a weak entity? Can it have two relations (one for the characterisation and one for the content) ?

Here's an extract what I have right now :

I added the id identifiers because... Else there would be nothing inside my tables (which is bad I think).

So I am wondering if these entities should be weak, as they are identified by a set of identifiers of another entity? Can they have no attributes? And is it okay to have those two relations like so for each entity?

I guess what I would like to do is something like Ellipse(focalPoint1, focalPoint2, characterPoint) (3-tuple as compound key) and Has (label, focalPoint1, focalPoint2, characterPoint), but I do not know how to represent this.

basic geometric figures and mathematical objects

That isn't the clearest design goal.

"mathematical objects" as I know them are designated with formulas and permit infinity, and other abstract notions.
"geometric figures" are usually just a subclass of Cartesian polygons.

If you need true circles defined with a radius, you're in for a lot of work. Even professional systems like PostGIS struggle with this, reducing them to polygons. If you need simple geometric polygons modeled in the database, it's significantly easier. You can basically look at the geometric primitives that most systems use point/line/polygon.


tsql Process running forever... what is it doing Trying to create a big test DB, I have an SP that I created that uses a predefined list of tables (35 That I'm interested in) and multiplies the data in each one of them.

In other words, A table has data for one store.  I insert into this table the same data but with a different store number (which gives me 2 stores). I then call the same SP that multiplies the 2 Stores data to 4 stores, and so on.  Over the weekend I started the process of 6 consequent calls of the SP thinking that when I come back Monday my DB would have data for 64 stores.  Unfortunately, this wasn't the case...

When I came in Monday, I found the data HD full but the process never crashed and seemed to be suspended.  my DB is in simple recovery model, I made space by moving files and by shrinking the TempDB (through the shrink file option in SSMS) and created an extra ndf file with 100GB initial size on an external drive... The process is still running (over 24 hours after making space).

The spid's task state shows suspended with wait type PAGEIOLATCH_EX but with small wait times (below 100ms) which makes me believe that the process is still running.

34 tables out of 35 are decent size and are quick enough to multiply...
The process has multiplied the 1 store of data in all tables to 8 stores and is still in the process of completing the multiplication of the data from 8 to 16 stores... the one table it seems to be working on is the biggest table of all...

The actual process of multiplying the data from 8 to 16 stores for this table, is inserting 400Million Records from a select of 400million records and the table has 200 columns all different types.

Am I wasting time waiting? I'm afraid that killing the process will take several days to roll back... Should I just wait it out? Has it become a Phantom  process?  How can I tell what it's doing?

Any opinion/ideas would be greatly appreciated.

Thank you

Download sp_whoisactive stored procedure written by Adam Machanic if you do not have it already. Execute with @get_full_inner_text = 0 option.

From the comment section of the stored procedure:

  --If 1, gets the full stored procedure or running batch, when available
  --If 0, gets only the actual statement that is currently
  running in the batch or procedure
  @get_full_inner_text  = 0


MySQL: Use Sysbench on Windows Sysbench is a tool to analyze the MySQL performance. I downloaded it and extracted but there is no executable file. Files having extensions like .sh or .c or .h are available. I gone through the documentation. Even, I did not get any info about how to install and use this great tool on Windows machine?

You need to compile it yourself.

I downloaded the tarball and read the README-WIN.txt.  It certainly indicates that it can be compiled on Windows using CMake.


Recreate existing database with a different charset I want to change the character set of an existing database from none to utf8,
it seems that the safest way is to create a new one then pump data to it.

I have tried two methods, both gave me the same error:

Create a new database with utf8 charscter set, then create a simple stored procedure to pump data via "execute block on external datasource"
use fbclone with this command line

fbclone -l fbembed.dll -v -s source.gdb -t destination.gdb -u SYSDBA -p masterkey -tc UTF8 -wc UTF8

Both gave me a malformed string error, and most of the rows were not copied to the destination database.

Example verbose error

Incompatible column/host variable data type
GDS Code: 335544569 - SQL Code: -303 - Error Code: 249
fields values ---
VANUM = 244458
RUBNUM = 5054
VALEUR = Absence de germes pathogènes.
ATBANA =
DATEMODIF = 16/05/2018

In your current setup, you seem to try to convert directly from NONE to UTF8, when this is done, the bytes stored as NONE are checked if they are valid UTF8, and then stored as-is. A malformed string error means that the data in a column NONE was not actually a valid combination of bytes in UTF8.

You will need to find out what the actual character set is of the data before you can convert it correctly (and even if you don't get errors, that does not necessarily mean the conversion was logically correct). You may want to try if the data currently stored isn't a different character set (for example WIN1252 is common in Western Europe and the US).

When you pump manually, this means that you either have to explicitly cast data. That is from NONE to WIN1252 (or whatever it is) and then to UTF8. Eg if the data is stored in column1, then you'd use

cast(cast(column1 as varchar(<length>) character set WIN1252) as varchar(<length>) character set UTF8)

I have no experience with fbclone, but the fact you specify UTF8 twice suggests that one is the source and the other the target character set. You may want to try if changing one or the other to WIN1252 (or a different one) to see which works.

Make sure to check the data afterwards.

In the worst case, you may have NONE data that is actually a mix of different character sets, and in that case you may have to inspect and convert each row manually.


MySQL user permission on stored procedure I've created a simple stored procedure:

mysql> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

But failed to run it:

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

Is it possible that my user name is an empty string? How do I create users and grant privileges? Can I grant a user all the privileges on all entities within a database?

As the error message itself says that  user do not have the execute permissions.

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

You need to grant the Execute Permission to that user.For that you need to login as root user and grant the permission as

 grant execute on db.* to user@localhost;

For your other queries :

Yes It is possible that your username is an empty string but it is not safe to create the users like this.
For creating and granting privileges in brief  have a look at This Link.
Yes you can grant  all the privileges on all entities within a database.
for this you can execute a command like

Login as root user and issue a command

GRANT ALL ON DB_NAME.* TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;


cross self join to get more rows - better solution? I have a table employees:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| persons_id     | int(10) unsigned |
| restaurants_id | int(10) unsigned |
+----------------+------------------+

and a table bills:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| customers_id   | int(10) unsigned |
| employees_id   | int(10) unsigned |
| restaurants_id | int(10) unsigned |
| date           | date             |
+----------------+------------------+

I want to insert 10,000 rows with an employees_id, restaurant_id and a date into the table bills by using cross joins. The table employees is filled with 15 rows (employees).

Currently I do following:

insert into bills (employees_id, restaurants_id, date) select e.id, e.restaurants_id, '2012-01-01' from employees e cross join employees e2 cross join employees e3 cross join employees e4 order by RAND() limit 10000;

With this query I get 50,625 rows (without to limit to 10,000) (15rows*15rows*15rows*15rows=50,625rows). Is there an more elegant solution to get 10,000 rows? So I don't have to write X times cross join employees eX.

I'm using MySQL. Thanks.

No, their is no better way then cross joining on yourself in MySQL. You can make the SQL slightly more elegant using, say, a CTE in other RDBMS, but what you have is the best way IMO.

By "best", I include "efficient" too: set based/no loops, and no temp tables


Problem adding a new node to a SQL Server 2012 Failover Cluster I tried to add a new node to SQL Server (2012 Standard) Failover, that is already installed on another node in Windows2012 Failover Cluster
I have an issue with non editable field wiht input for SQL Server Agents Account Name
there could be the same AD name as for SQL Server Database Engine (e.i.)
my account access haven't AD admin privileges
access, account and setting for SQL Server Agent on parent node are accesible and settable

As per your comments in your question, what you need to do is two-fold:

Add SQL Server Agent as a cluster resource type
Add the SQL Server Agent cluster resource to the failover cluster resource group

For the first step, one way to do this is through PowerShell (utilizing the FailoverClusters module):

Import-Module -Name FailoverClusters

$ClusterName = "YourClusterName"
$FciClusterGroupName = "SQL Server (MSSQLSERVER)" # may need to modify to reflect your environment

# check to see if you have the SQL Server Agent cluster resource type
Get-Cluster -Name $ClusterName |
    Get-ClusterResourceType |
    Where-Object {$_.Name -eq "SQL Server Agent"}

# if not, add it
Add-ClusterResourceType -Name "SQL Server Agent" -Dll "sqagtres.dll"

Execute each of those blocks of code separately instead of just all at once.  You'll be able to confirm whether or not that cluster resource type exists before just blindly attempting to add it.

Then for the second step, here's what you need to do to add the SQL Server Agent as a resource in your failover cluster resource group:

Right-Click on your FCI resource group within the failover cluster manager
Select "Add a resource" and select the SQL Server Agent resource type
Rename the new cluster resource to "SQL Server Agent"
Go into the new SQL Server Agent resource properties and set the virtual network name and instance name properties
On the dependency tab, set SQL Server as its dependency resource

You may need to go into the registry at the following location and ensure those keys (one will contain the string "AGENT", another "SQL_ENGINE") are set to 1 (this is where it is recorded on what was installed correctly.  1 denotes correct installation):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YOURINSTANCE\ConfigurationState

That should get you to a place where you can now install an additional node and have the SQL Server Agent service account appear.  Of course, prior to starting ensure that you have a back out here (especially with the registry editing).


MS SQL Error 26 in MS SQL Express 2016 I installed MS SQL Express 2016 edition and when I log in it, it gave me following error.

TITLE: Connect to Server
------------------------------

Cannot connect to 192.168.50.173\KONSYS.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

SQL Server also running and it can see in services.

I also add rules for 1433 and 1434 ports in Windows Firewall.

How to solve this problem

SQL Server Named instances by default are running on a dynamic TCP/IP port (so it won't be default 1433). You can try to do a few things to enable access to your instance (assuming that Firewall is configured properly).

SQL Server Browser

You need to ensure that the SQL Server Browser service is running.

Open SQL Server Configuration Manager
Go to SQL Server Services
Right-click on SQL Server Browser service
Click Start

Try to connect to your instance providing <hostname>\<instancename> or <IP>\<instancename> as Server Name. If that doesn't help go to the next step.

TCP\IP Protocol

To enable TCP\IP protocol for your instance:

Open SQL Server Configuration Manager
Go to SQL Server Network Configuration
Go to Protocols for
Right-click on TCP/IP
Click Enable
Restart your SQL Server instance

If you want to use a static port for your instance (instead of dynamic that changes after every restart) you can change it here.

Open Properties for TCP/IP protocol
Go to IP Addresses tab
Scroll down to IPAll section
Remove 0 value from TCP Dynamic Ports
Specify your port in TCP Port

You can use this port to connect to your instance by providing <servername>,<port> or <IP>,<port> as Server Name (yes, there is a comma, not a colon).


Why isn't my includeThreadNamesAsStatementComment JDBC parameter working? The JDBC API lists a includeThreadNamesAsStatementComment parameter that seems to indicate it will prefix all queries with the Java thread ID.  I thought this would be useful in tying back DB activity to application logs.  I added it to my resource URLs and it seemed to only be working part of the time.
Curiously it was only the applications heartbeat thread that periodically pings the database as part of a health check.  None of the "real" queries were getting tagged.
Why isn't this working for all queries?

It turns out there's a known bug where this only works when calling the the JDBC API directly.  It does not work when building queries through something like SimpleJDBCTemplate.  All of the applications real work was being done using templates whereas the healtcheck threads were using the direct API.


Creating a constraint I created a report that records entries
from a table which has 4 columns

1 Shipper ID
2 SHipper Reference
3 loading
4 Completion

My primary keys are columns 1 and 2 there are no foriegn keys, columns 3 and 4 are mandatory fileds
with a yes or no value constraint

what I want is to create a constraint that doesnt allow the user to pick these two fields (completeion and loading) as N in the same time but they can be both Y at the same time

what is the code for that constraint?

this code was my best shot

ALTER TABLE Table_Name
ADD CONSTRAINT Double_constraint check (Completion!='N' and Loading!='N')

but this one wont allow anything but completion to be Y and Loading to be N

You'd probably be better off making this a bit field with NOT NULL or otherwise you also need to validate that only the strings 'Y' and 'N' were entered.

With the bit column arrangement the check constraint would be

CHECK (1 IN (Completion, Loading))

Otherwise with char(1) the check constraint could be

CHECK ('Y' IN (Completion, Loading))

but you'd need two additional column level constraints ensuring Completion IN ('N','Y') and similar for Loading.


update column based on sum of data from another table I have two tables :

Table1 (col1, col2, col3, col4, val1, status)
Table2 (col1, col2, col3, col4, val2)

For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key.

Table2 may have duplicated rows, for that reason I would like to group by (col1, col2, col3, col4) and sum(val2)

After that, I would like to update Table1.val1 by the value of sum(Table1.val2) when Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2 and Table1.col3 = Table2.col3 and Table1.col4 = Table2.col4 and status= 'V'.

I did something like that:

UPDATE Table1
SET val1 = (
  select t_sommevbrute.sumvalbrute
  from (
    Select  col1,col2,col3,col4,SUM(val2) sumvalbrute
    From Table2
    Where col3 = 2014
      And col2=51
      And status= 'V'
    GROUP BY col1, col2, col3, col4
    ) t_sommevbrute
  WHERE Table1.col1 = t_sommevbrute.col1
    and Table1.col2 = t_sommevbrute.col2
    and Table1.col3 = t_sommevbrute.col3
    and Table1.col4 = t_sommevbrute.col4)

Could someone help me please?
Thank you

If you have a primary key, you don't have duplicate rows.

In Oracle, you do multi-table UPDATEs by using MERGE, and not UPDATE, because that is inefficient.

merge into table1
using (select col1, col2, col3, col4, sum(val2) sum_val2 from table2 group by col1, col2, col3, col4) table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.sum_val2, table1.status = 'V';

If col1, col2, col3, col4 are really a composite primary key, no need to GROUP BY and SUM:

merge into table1
using table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.val2, table1.status = 'V';


How would I implement interprocess communication from Oracle (10g R1) trigger using DBMS_ALERT? Let me state up front that I'm not proficient in Oracle at all, MS SQL yes but not Oracle; which is why I'm asking this.

Current Setup

Oracle 10g Release 1 running on Red Hat Ent. 3 (no support)
Can not upgrade OS or Database
Can not extensively modify Database (read-only would be preferred)
Using Visual Studio 2013 and .net 4.5 for data access to Oracle
Using ODAP/ODP.NET 11.2 Release 5 (can not be newer due to using Oracle 10g R1)
Using System.Data.Oracle instead of the ODP.NET classes to avoid file bloat
Can not use Data Change Notification because it isn't supported in 10g R1

Under this current setup I can easily connect to the database, issue sql statements and read/write data. The application being written is to be used as a sort of adapter or bridge between the Oracle database and MongoDB where only a couple tables are meant to replicate changes into MongoDB.

The current implementation of this uses DBMS_ALERT and triggers to communicate events into the .net application at which point it will read the data, convert it and write it into MongoDB.

Here is an example trigger that is being used

CREATE OR REPLACE TRIGGER EXAMPLE.TABLENAME_ALERT
AFTER UPDATE OR INSERT OR DELETE
    ON EXAMPLE.TABLENAME
    FOR EACH ROW

BEGIN

    IF INSERTING THEN
        DBMS_ALERT.SIGNAL('DATA_INSERTED', :new.data_id);
        DBMS_OUTPUT.PUT_LINE('INSERTED');
    ELSIF UPDATING THEN
        DBMS_ALERT.SIGNAL('DATA_UPDATED', :old.data_id);
        DBMS_OUTPUT.PUT_LINE('UPDATED');
    ELSIF DELETING THEN
        DBMS_ALERT.SIGNAL('DATA_DELETED', :old.data_id);
        DBMS_OUTPUT.PUT_LINE('DELETED');
    END IF;

END;

If I issue the following SQL statements:

update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 1;
update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 2;
update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 3;

The result is getting 3 separate DATA_UPDATED signals and 3 separate UPDATED lines in the database output. On the other hand, if I issue this statement:

update EXAMPLE.TABLENAME set example_column = 'test' where data_id <= 3;

The result I get is 1 DATA_UPDATED signal that contains the id of the last row updated and 3 separate UPDATED lines in the database output. I need to get all 3 signals.

My guess is, that because DBMS_ALERT.SIGNAL doesn't fire until a transaction is committed, only fires the most current signal at that time and a single batch update/insert/delete is considered 1 transaction then all I will ever receive is 1 signal from the database.

Is there a way I can get the multiple calls per trigger to DBMA_ALERT.SIGNAL to actually send all the signals? Or perhaps there is a better way for me to detect changes to data in the database from an external application?

EDIT I know this implementation has a code smell, but my hands are tied, I have to work with what we have and can't really change anything.

This could be done this way.  I don't like it much as it is not very elegant and looks fragile if unexpected things happen.

--1)Create a table in the user's schema or another new schema if you want separation.

CREATE TABLE DATA_AUDIT
( Primary_key RAW(16) default SYS_GUID() not null,
  Data_id  Number(10),
  Action   VARCHAR2(10),
  Date_created TIMESTAMP(6)DEFAULT CURRENT_TIMESTAMP);
--add foreign key constraint for Data_id to the parent Table
--add check constraint: Action in ('INSERT','UPDATE','DELETE');

--2) create a trigger on the parent table
CREATE OR REPLACE TRIGGER TGR_PARENT_TABLE
 BEFORE INSERT OR UPDATE OR DELETE
 ON PARENT_TABLE
 FOR EACH ROW
 v_action DATA_AUDIT.action%type;
BEGIN

    IF INSERTING THEN
       v_action := 'INSERT';
    ELSIF DELETING THEN
        v_action := 'DELETE';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSE
        --raise an error for an unexpected event and log it
        RAISE;
    END IF;
       Insert into DATA_AUDIT(Primary_key,Data_id, Action,Date_created) VALUES
       (SYS_GUID(), :old.Data_id, v_action,CURRENT_TIMESTAMP);
END;

--3) create a trigger on DATA_AUDIT
CREATE OR REPLACE TRIGGER TGR_DATA_AUDIT
 BEFORE INSERT
 ON DATA_AUDIT
 FOR EACH ROW

BEGIN

DBMS_ALERT.SIGNAL(:old.action, :old.data_id);
END;


Trying to recover a database with just .ibd and .frm files, but no idbata1 file Trying to restore a WAMP database with just .frm and .ibd files. The uninstall of WAMP left .ibd and .frm files, but no ibdata1 file. I have read several posts here to figure out the process for doing this.

I read this post:  Recover MySQL database from data folder without ibdata1 from ibd files . I also got advice to bring in a ibdata1 file in from a fresh WAMP MySQL installation . (My old WAMP was a 5.6.12, this new one installed 5.6.17). I ran the .frm files through a utility at TwinDB: https://recovery.twindb.com/ which I understand helps get the schema restored.

The instructions are this: to run commands through a MySQL utility after copying .ibd files to the data level folder, then moving the .ibd files back to the database level. I assume the .frm files that were run through TwinDB's utility are to replace the existing .frm files in my database folder. There are eleven .ibd files to run through this process.

My hope when I'm done is that my 5.6.12 installation will be legitimate, and that WAMP's tray menu will recognize my 5.6.12 installation once again. I have several years of archived posts there that I want to re-use again online.

I downloaded the MySQL Utilities (1.5.6) and installed them. I've read here that I should be using mysql.exe. But that file is not in the folder that was installed. I'm stuck. There are 24 .exe files in that folder. The READ ME file gives me no instructions. Which utility am I supposed to use if mysql.exe is not in there? Why didn't the installation of the Utilities create an app that opens up all these tools?

My database is located at C:\wamp\bin\mysql\mysql5.6.12\data\wordpress. I know I need that path to log in. What is my command to log into this server path so I can run my commands and restore these connections between my eleven .ibd files?
My database user is root. There is no password since this is an internal server.

I've read that I need to log in to mysql so I can run these commands to put the database info in the directory with the ibdata file so that everything can "connect" again. The .ibd files have all the data for the internal WordPress site. Their files sizes are clearly indicative that all the data is there.

Answer copied from this site, but abridged and adjusted so the path matches your directory structure.

Run cmd command
Type C:  and hit enter
C:>cd wamp\bin\mysql\mysql5.6.12\bin
C:\wamp\bin\mysql\mysql5.6.12\bin>mysql.exe -u root


PostgreSQL: Find row with JSON object attribute in array I have a postgres database which is set up like this:

CREATE TABLE arr_test (
  id serial primary key,
  data jsonb
);

INSERT INTO arr_test (data) VALUES
  ('{"test_row": 1, "my_arr": [{"serial":"AAA", "content":"123"}, {"serial":"BBB", "content":"345"}]}'),
  ('{"test_row": 2, "my_arr": [{"serial":"CCC", "content":"456"}, {"serial":"DDD", "content":"567"}]}'),
  ('{"test_row": 3, "my_arr": [{"serial":"AAA", "content":"678"}, {"serial":"EEE", "content":"789"}]}');

I am trying to write a query where the data contains a list which has an object which contains a value. In this case it is the serial-field of the objects in the array that I want to query for.
For example, if I query for serial 'AAA' I want test row 1 and 3 to be returned, and if I query for serial 'EEE' I want only test row 3.

I have experimented with jsonb_array_elements and jsonb_to_recordset but am getting nowhere and would appreciate any help.

You need to unnest the array elements and combine that with an EXISTS condition

select t.*
from arr_test t
where exists (select *
              from jsonb_array_elements(t.data -> 'my_arr') as x(o)
              where x.o ->> 'serial' = 'AAA');

Alternatively you can write that with a contains operator @>:

select t.*
from arr_test t
where exists (select *
              from jsonb_array_elements(t.data -> 'my_arr') as x(o)
              where x.o @> '{"serial": "AAA"}');

Try it on dbfiddle


Getting ORA-01031:insufficient privileges during first time login to oracle 11.2.0.1.0 database on my windows 8 PC I installed oracle database 11.2.0.1 on my windows 8.1 PC. I downloaded the zip files from oracle website viz. win64_11gR2_database_1of2 and win64_11gR2_database_2of2.

I followed the installation steps and while attempting to login through sysdba, i got the insufficient privileges issue.
I used below command:

sqlplus "/as sysdba"

Please help me to resolve this issue.

There are some things which have to be set up correctly:

The user you are logged on has to be a member of the ora_dba operating system group.
The parameter SQLNET.AUTHENTICATION_SERVICES=(NTS) must be set correctly. It can include more options but "NTS" is required.
You have to connect to the database correctly which is: sqlplus / as sysdba. You can also add these quotes but that was Oracle 9i style. But you need a space betweed "/" and "as".

  Note: If you want to connect to Oracle as sysdba from a remote computer, you need a password file.


Self referencing scalar function nesting level exceeded when adding a select Purpose

When trying to create a test example of a self referencing function, one version fails while another one succeeds.

The only difference being an added SELECT to the function body resulting in a different execution plan for both.

The function that works

CREATE FUNCTION dbo.test5(@i int)
RETURNS INT
AS
BEGIN
RETURN(
SELECT TOP 1
CASE
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN  dbo.test5(1) + dbo.test5(2)
END
)
END;

Calling the function

SELECT dbo.test5(3);

Returns

(No column name)
3

The function that does not work

CREATE FUNCTION dbo.test6(@i int)
RETURNS INT
AS
BEGIN
RETURN(
SELECT TOP 1
CASE
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
END
)END;

Calling the function

SELECT dbo.test6(3);

or

SELECT dbo.test6(2);

Results in the error

  Maximum stored procedure, function, trigger, or view nesting level
  exceeded (limit 32).

Guessing the cause

There is an additional compute scalar on the estimated plan of the failed function, calling

<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END">

And expr1000 being

<ColumnReference Column="Expr1000" />
<ScalarOperator ScalarString="[dbo].[test6]((1))+[dbo].[test6]((2))">

Which could explain the recursive references exceeding 32.

The actual question

The added SELECT makes the function call itself over and over, resulting in an endless loop, but why is adding a SELECT giving this result?

Additional info

Estimated execution plans

DB<>Fiddle

Build version:
14.0.3045.24

Tested on compatibility_levels 100 and 140

This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function.

To explain, we need to note two things up front:

SQL Server cannot execute subqueries directly, so they are always unrolled or converted to an apply.
The semantics of CASE are such that a THEN expression should only be evaluated if the WHEN clause returns true.

The (trivial) subquery introduced in the problematic case therefore results in an apply operator (nested loops join). To meet the second requirement, SQL Server initially places the expression dbo.test6(1) + dbo.test6(2) on the inner side of the apply:

[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))

...with the CASE semantics honoured by a pass-through predicate on the join:

[@i]=(1) OR [@i]=(2) OR IsFalseOrNull [@i]=(3)

The inner side of the loop is only evaluated if the pass-through condition evaluates to false (meaning @i = 3). This is all correct so far. The Compute Scalar following the nested loops join also honours the CASE semantics correctly:

[Expr1001] = Scalar Operator(CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END)

The problem is that the project normalization stage of query compilation sees that Expr1000 is uncorrelated and determines that it would be safe (narrator: it isn't) to move it outside the loop:

[Expr1000] = Scalar Operator([dbo].[test6]((1))+[dbo].[test6]((2)))

This breaks* the semantics implemented by the pass-through predicate, so the function is evaluated when it should not be, and an infinite loop results.

You should report this bug. A workaround is to prevent the expression being moved outside the apply by making it correlated (i.e. including @i in the expression) but this is a hack of course. There is a way to disable project normalization, but I have been asked before not to share it publicly, so I won't.

This problem does not arise in SQL Server 2019 when the scalar function is inlined, because the inlining logic operates directly on the parsed tree (well before project normalization). The simple logic in the question can be simplified by the inlining logic to the non-recursive:

[Expr1019] = (Scalar Operator((1)))
[Expr1045] = Scalar Operator(CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(int,[Expr1019],0)+(2),0))

...which returns 3.

Another way to illustrate the core issue is:

-- Not schema bound to make it non-det
CREATE OR ALTER FUNCTION dbo.Error()
RETURNS integer
-- WITH INLINE = OFF -- SQL Server 2019 only
AS
BEGIN
    RETURN 1/0;
END;
GO
DECLARE @i integer = 1;

SELECT
    CASE
        WHEN @i = 1 THEN 1
        WHEN @i = 2 THEN 2
        WHEN @i = 3 THEN (SELECT dbo.Error()) -- 'subquery'
        ELSE NULL
    END;

Reproduces on the latest builds of all versions from 2008 R2 to 2019 CTP 3.0.

A further example (without a scalar function) provided by Martin Smith:

SELECT IIF(@@TRANCOUNT >= 0, 1, (SELECT CRYPT_GEN_RANDOM(4)/ 0))

This has all the key elements needed:

CASE (implemented internally as ScaOp_IIF)
A non-deterministic function (CRYPT_GEN_RANDOM)
A subquery on the branch that should not be executed ((SELECT ...))

*Strictly, the above transformation could still be correct if evaluation of Expr1000 was deferred correctly, since it is referenced only by the safe construction:

[Expr1002] = Scalar Operator(CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END)

...but this requires an internal ForceOrder flag (not query hint), which is not set either. In any case, the implementation of the logic applied by project normalization is incorrect or incomplete.

Bug report on the Azure Feedback site for SQL Server.


Using a Having clause with an outer join I want to use a "having" clause with an outer join, using a column that is used in the outer join.

This is my query:

SELECT
  I.CARD_BIN BIN
FROM
  GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION P ON I.CARD_BIN = P.CARD_BIN
LEFT OUTER JOIN GE_PAYEE PP ON PP.PAYEE_CODE = P.PAYEE_CODE
ORDER BY I.CARD_BIN
HAVING COUNT(*) > 1;

But this gives me this error:

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 36 Column: 19

Line 36 is ORDER BY I.CARD_BIN.

Can someone tell me how I can solve this problem?

This is not my forte, but I am sure that you need a GROUP BY in there.

Also, out the ORDER BY line at the end of your statement.  Something like this :

SELECT
  I.CARD_BIN BIN
FROM
  GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION P ON I.CARD_BIN = P.CARD_BIN
LEFT OUTER JOIN GE_PAYEE PP ON PP.PAYEE_CODE = P.PAYEE_CODE
group by I.CARD_BIN
HAVING COUNT(*) > 1
ORDER BY I.CARD_BIN;


Should I create a multi-column UNIQUE index? I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits):

CREATE TABLE `Visits` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `User` int(10) unsigned NOT NULL,
  `EventKind` enum('search','purchase','subsectionList','event','like','superprice','toursList','abonementsList','abonement') NOT NULL,
  `SiteSection` enum('section','subsection','superprice','region','city','children','tour','abonement') NOT NULL,
  `Object` int(10) unsigned NOT NULL DEFAULT '0',
  `Count` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `User` (`User`),
  KEY `EventKind` (`EventKind`),
  KEY `SiteSection` (`SiteSection`),
  KEY `Object` (`Object`)
) ENGINE=MyISAM;

The quadruple User/EventKind/SiteSection/Object is always unique. My question: Is it worth (especially due performance considerations) to add a 4-columns UNIQUE key?

This data will be used to show a user these sections of the site which are related with his previous visits.

You would create the index if any of this is true:

There are queries or DML statements that benefit from this index.
You want to enforce uniqueness of the combination of the index columns.

You would not create the index if all of this is true:

There are no queries or DML statements that benefit from it.
You don't want to enforce uniqueness of these columns.
DML statement performance against this table is critical and the benefit of one fewer index offsets the loss (if any) of query performance.

This applies equally to non-multi-column indexes.


Using MAX text or more specific, smaller type Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should always just use VARCHAR(MAX) and linked Why use anything but varchar(max). I read it but it seemed dated, as it was focusing on 2005, and didn't seem to offer any real justification to allocate potentially up to 2 GB per row on all text fields.

From a performance, storage, etc. standpoint, how should one go about deciding whether to use VARCHAR(MAX) or a smaller more specific type for modern versions of SQL Server? (e.g., 2008, 2012, 2014)

Should I always use (n)varchar(max) for text columns?

No.
For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored.
Physical storage is identical whether the column is typed as varchar(n) or varchar(max), so that is not the concern.
The reasons to not choose (n)varchar(max) everywhere revolve around features, plan quality, and performance.
An exhaustive list is probably not practical, but among other things, max columns:
Features

Require a separate constraint to enforce a maximum length
Cannot be a key in an index (so no unique constraints either)
May prevent online DDL (including index rebuilds and adding a new non-null column)
Are generally not supported for 'newer' features e.g. columnstore
See the product documentation for more specific features and limitations. The general pattern is that there are awkward limitations and restrictions around max data types. Not all limitations and side-effects are documented.

Performance

Require special handling in the execution engine, to account for the potentially very large size. Typically, this involves using a less efficient code path, with a streaming interface
May have similar unanticipated consequences for external code (and other SQL Server components like SSIS), which must also be prepared to handle data up to 2GB in size
Are assumed to be 4000 bytes wide in memory grant calculations. This is likely to lead to excessive memory reservation, which limits concurrency, and pushes valuable index and data pages out of cache memory
Disable several important performance optimizations
May extend lock duration
May prevent the optimizer choosing a (non-dynamic) seek plan
Prevent filters being pushed into scans and seeks as a residual
May increase tempdb pressure and contention (version dependant), since variables and parameters are also likely to be typed as max to match column definitions

In summary, there are so many subtle (and undesirable) side effects of unnecessarily using the max specifier that it makes no sense to do this. The minor 'convenience' of using a single declaration is no sort of compensation.
Evaluate each type in context, use the correct base type (varchar or nvarchar), and a sensible explicit length.
Further reading:

Performance comparison of varchar(max) vs. varchar(n) by Remus Rusanu
Read Committed and Large Objects by Craig Freedman
Capacity planning for tempdb
Deletes that split pages and forwarded ghosts (me)


Does updating a jsonb column trigger updates on all expression indexes on that column? I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column):

UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123

I have a number of expression indexes on the jsonb column:

CREATE INDEX ix_b ON my_table some_function(jdoc->>b)
CREATE INDEX ix_c ON my_table some_other_function(jdoc->>c)

As you can see, I only change jdoc->a, but I have to set the whole jdoc column. Will I therefore suffer the full performance penalties of updating the indexes on jdoc->b and jdoc->c? Or is PostgreSQL smart enough to notice that those values did not change, and that the indexes therefor does not need to be updated? If I suffer the full performance penalty, is there any way to avoid it?

Will I therefore suffer the full performance penalties of updating the indexes on jdoc->b and jdoc->c?

Yes.

  Or is PostgreSQL smart enough to notice that those values did not change, and that the indexes therefore does not need to be updated?

Nope, because the index probably did change, you just don't see that.

When you update the json document PostgreSQL writes a new version of the row that contains it to the table. There's a new row in the table and the old row gets marked as effectively deleted. (It's not that simple, but this is good enough.) The new row has a new address, and the indexes need to be updated to point to the new row address, even though the value in the index might be exactly the same.

PostgreSQL has some optimisations that let it avoid such index updates in a few cases (read about HOT if you want to know the gory details) but they're no help if you are updating an indexed column.

  If I suffer the full performance penalty, is there any way to avoid it?

No, there isn't. You'd have to split the json document into two columns.

This will not change with 9.6's new support for updating json documents. It'll still write the whole updated document to a new TOASTed field as a new tuple, and that'll require index updates. It's more efficient to create the new json document but no faster (or smaller) to update write it to disk and the indexes.


Return int value from boolean field in SQL select Is there a way to return an int value for a boolean field in a sql select statement?

declare @b bit = 1

select convert (int, @b)

or

select cast(@b as int)

more


Oracle: How can I add ROWDEPENDENCIES to an existing table? If I have a table t

create table t (x var number);

How can I modify the table so that it appears to have been created with rowdependencies?

create table t (x var number) rowdependencies;

Unfortunately, you can't (unless something has changed in 12c that I'm not aware of).  You'll have to recreate the table with rowdependencies and move all the data from the old table to the new one.

Of course, you could use the dbms_redefinition package to do this redefinition online.  But under the covers, you'd still be creating a new table with rowdependencies enabled, copying the data over, dropping the old table and renaming the new one.  It would just involve potentially less downtime (at a cost to how long it takes and how much DBA time is required).


Query returns all values instead of the row of the one with the max value I have this database
https://pastebin.com/WWRxApWn
I need the row of the product with the greatest unitprice. I did:
SELECT id, MAX(unitprice)
FROM product
GROUP BY id;

Instead, it returned every row in products. If I do this:
SELECT MAX(unitprice)
FROM product;

It returns the greatest price, as expected.
I looked into this (https://stackoverflow.com/questions/18957372/sql-max-product-price) and this (https://stackoverflow.com/questions/12366390/how-to-select-product-that-have-the-maximum-price-of-each-category) thread but I can't figure it out.
Help?

The difference in what you linked is the asker wants the max value per a specific field (i.e. aggregate of a GROUP BY), where you rather want the whole row that contains the max value of a specific field (i.e. take the top 1 row ordered by an aggregate).
To achieve what you want you need to use a window function to order the records by the unitprice field and then filter on that the top 1 of that result set. Here's an example query to achieve this:
WITH CTE_Product_Sorted AS -- CTE of the product dataset with a SortId generated from the order of the unitprice field
(
   SELECT *, ROW_NUMBER() OVER (ORDER BY unitprice DESC) AS SortId -- Generates a unique ID for every record, in the order of unitprice, from greatest to least
   FROM product
)

SELECT *
FROM CTE_Product_Sorted
WHERE SortId = 1 -- Filter out every other record except the one with the greatest unitprice

Note when you use the ROW_NUMBER() window function, if there's a tie in the logic of the ORDER BY clause, the ordering between the tied records is non-deterministic (random). Depending on the case, this is ok, or when it's not then a unique field needs to be added to the ORDER BY clause as a fallback (e.g. ORDER BY unitprice DESC, productid ASC results in the product that was created first to win in the case of a tie by unitprice.)


How to know when a refresh of a materialized view has been completed After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results.

Now, my problem is that I need a way to know when the refresh is complete so that I can then query and be sure to obtain an up to date result.

The MV is specified in this way:

CREATE MATERIALIZED VIEW ASSAY
("TYPE","METHOD",COMPOUND_CORPORATE_ID,RESULTS_AVG,RESULTS_ST_DEV,RESULTS_N)
AS
select "TYPE","METHOD",AVG("RESULTS"), STDDEV("RESULTS") , COUNT("RESULTS")
from ASSAY_INHIB
where upper("VALIDATION")='YES'
group by "TYPE","METHOD",COMPOUND_CORPORATE_ID;
CREATE INDEX assays_inhib_parent ON ASSAY(compound_corporate_id);
ALTER MATERIALIZED VIEW ASSAY  REFRESH FORCE   ON DEMAND;

I call from the GUI a stored procedure with the command exec REFRESH_MV() and the stored procedure is like this :

CREATE  PROCEDURE REFRESH_MV () AS
BEGIN
    DBMS_MVIEW.REFRESH('ASSAY');
END REFRESH_MV;

The problem is that I don't know when I can query the MV and be sure that data are up to date. Can anybody help?

Thanks

You you have said-

  After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results...

You may use ON COMMIT refresh instead of ON DEMAND BUT I just said may be, not sure about your requirement and implementation.

  ON COMMIT Refresh

  A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view.

The following query can be used to know when the MV was last refreshed.

SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select owner, mview_name, last_refresh_type, last_refresh_date
    from all_mviews;

Sample output:

USERNAME         M_VIEW_TEST        COMPLETE   02/02/17 01:01:19

You can also use v$mvrefresh dynamic performance view to know which MV is being refresh.

select currmvowner, currmvname from v$mvrefresh;

References:
ALL_MVIEWS
ON COMMIT Refresh


Why does SQL Server need permissions to access data and log files? When you want to attach a database, you can open them up if you put those mdf files in a folder such as:

  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

But SQL Server will not attach them if they are located on your desktop. If you go to your desktop path, e.g.:

  C:\users\username\desktop\

It states that it cannot find the object because it does not exist or you do not have permissions. I checked the security of the folder that is located on my desktop and I have full access. Can anyone explain this?

The SQL Server service account needs to have read/write access to the folders where the data and log files exist. Because it needs to read and write.

By default the service account has access only to SQL Server's own folder structure. This has nothing to do with what access you have.

Your options are:

Grant explicit read/write access to your desired folder/tree
Change the service account to be you
Put the database files in their proper location for that instance

I prefer the latter so that you don't have active database files scattered all over your machine.


pg_upgrade issue from postgres 9.3 to 9.6 I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6.
I have both binaries on the windows server R8.

Here are the 2 pg_hba.conf :

> 9.3 :
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#local    all             all             127.0.0.1/32            TRUST
host    all             all             127.0.0.1/32            TRUST
host    all             all             10.0.0.0/8              md5

9.6 :
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#local    all             all            127.0.0.1/32           TRUST
host    all             all             127.0.0.1/32            TRUST
host    all             all             10.0.0.0/8              md5

I have all permissions on the directories.
From 9.6 bin I execute :
pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata

All I got is :

  connection to database failed : could not connect to server : connection refused

I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?

I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.

I am running PostgreSQL in Windows 10.

I received the same error message.

I was confused about running cmd.exe under user: postgres.  My system doesn't have an account with that name.

I did two things.

pg_hba.conf file were modified for both versions to add the "method" "trust".  I did not add extra lines as you might have found in other references.  However, comparing my entries with the original poster, I had the default localhost entries as per what they added.  I would comment md5 lines temporarily:

I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6
Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user.  Extra consideration  may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.

In my case, my setup has the PostgreSQL application files stored in C:\program files directory and data is stored in C:\APPS\PostgreSQL\version\data

Referencing my setup, I ran the following command:

-c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.

-U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:

The -U option allows you to specify a different postgreSQL user account for the command to use.  This is where I specified the user to be postgres.  Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option

This is the result I received after running modified command:

Hope this helps.


Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it? I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is still there. In fact if I try to re-create it I get an error like "object already exists".

I see that there is a table with the same name belonging to another schema. I assume that should not cause the problem, but I felt like mentioning it.

Here is the SQL*Plus output:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 17:01:50 2011

SQL> select object_name, object_type, owner from all_objects where object_name = 'MY_MVIEW';
OBJECT_NAME OBJECT_TYPE       OWNER
----------- ----------------- ----------
MY_MVIEW    MATERIALIZED VIEW DI_TEST_AL
MY_MVIEW    TABLE             DIDEV11

SQL> DROP MATERIALIZED VIEW MY_MVIEW;
Materialized view dropped.

SQL> select object_name, object_type, owner from all_objects where object_name = 'MY_MVIEW';
OBJECT_NAME OBJECT_TYPE       OWNER
----------- ----------------- ----------
MY_MVIEW    MATERIALIZED VIEW DI_TEST_AL
MY_MVIEW    TABLE             DIDEV11

SQL>

I double checked my permission and DROP ALL MATERIALIZED VIEWS is granted to the DI_TEST_AL user.

Connect as sysdba and check if there is any entires in dba_summaries for the MV. If there is run,

drop summary <OWNER>.<MV_NAME>;`


Merge multiple rows containing different values into one row I have a database with the structure:
Region    | OpenServicesID | DFC
K00001    | 1400           | 4
K00002    | 1300           | 3
K00001    | 1200           | 4
K00001    | 1100           | 4
K00002    | 1000           | 4

I have to present the data table in the following format:
Region | OpenServicesID | DFC | OpenServicesID | DFC | OpenServicesID|DFC
K00001 | 1400           | 4   | 1200           | 4   | 1100          | 4
K00002 | 1300           | 3   | 1000           | 4   | Null          |Null

So, I want to thank all for your answers. The code that I came up with looks like this:
/* This Code section is to take different prefernces (in multiple rows) and put them in one row (with multiple columns)*/
SELECT account_id, A[1] IS_ID1, B[1] IS_TYPE1, C[1] IS_TITLE1, D[1] IS_QUERY1, A[2] IS_ID2, B[2] IS_TYPE2, C[2] IS_TITLE2, D[2] IS_QUERY2,
 A[3] IS_ID3, B[3] IS_TYPE3, C[3] IS_TITLE3, D[3] IS_QUERY3, A[4] IS_ID4, B[4] IS_TYPE4, C[4] IS_TITLE4, D[4] IS_QUERY4, A[5] IS_ID5, B[5] IS_TYPE5, C[5] IS_TITLE5, D[5] IS_QUERY5
from (
    select account_id, array_agg(id) A, array_agg(type) B, array_agg(title) C, array_agg(query) D
    from temp01
    group by 1
    order by 1) z
/* This is the end of this section*/


PostgreSQL isn't listen on 5432 in Windows 7 I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented:

listen_addresses="*"
port=5432

And this is the configuration at pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             127.168.1.0/24          md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32           md5
#host    replication     postgres        ::1/128                md5

Apparently all is fine but if I run the command netstat -an I don't see the port 5432 open to any host unless this line:

0.0.0.0:5432

means that. The problem is I can't connect from any PC in subnet 192.168.1.x and the message says PostgreSQL isn't installed on the PC or it doesn't listen. I have disable any firewall in all PC looking for a solution but can't. Can any help me to find the problem here?

UPDATE

This is the error I'm getting:

i have the same problem. I disabled all my third party antiviruses and firewalls and reinstall PostgreSQL again and its now working fine... :)


Tool to export data with all relational data? Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design?

The purpose is to ease migrations of bits of data between servers for adhoc migrations. I am looking specifically for a tool for MySQL InnoDB with defined foreign keys.

In reality, you could just script it yourself using mysqldump.

Here my posts using the --where option for mysqldump:

Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query?
Mar 14, 2012 : Can I mysqldump an entire database while using where='condition' on individual tables?
Jul 15, 2013 : Export just rows modified in last month (date defined by user)

With a good review of your tables and some SQL elbow grease, you can script the dump for any table and its child records with the corresponding foreign key references.

Give it a Try !!!


Window Function to get pairs of records and compute the time difference I'm working with an event dataset as the one in the example below:

Event_Type  Event_Timestamp         Is_Active
A           2010-10-01 00:00:00     1
B           2010-10-01 00:00:01     1
A           2010-10-01 00:00:02     0
D           2010-10-01 00:00:03     1
B           2010-10-01 00:00:04     0
C           2010-10-01 00:00:05     1
A           2010-10-01 00:00:06     1
A           2010-10-01 00:00:07     1
A           2010-10-01 00:00:08     0

The dataset is ordered by the timestamp of the events and the data grows as events happens in real time. More event types could be included in the dataset at any time (not limited to only A, B, C, D as in the example) and the same event type could appear in the table multiple times. The Is_Active boolean field serves as a way to indicate whether the event is still active (1) or not (0).

In this sense, I tried to do some transformations on this data by using SQL Window Functions. I'm not necessarily restricted to a specific product or technology, so feel free to tell how you would address the problem below.

What I want to do is dynamically pair each event of the same type when they have opposite Is_Active values and then get how long that event was active. In other words, given an event X, I would need to get the Event_Timestamp for it when Is_Active had 1 for the first time (Begin_Timestamp), and then ignore the rest of the rows for this event X with Is_Active 1 until I get Is_Active 0, so I could take the Event_Timestamp again (End_Timestamp). Then, I would continue applying this logic when I find event X having 1 in the Is_Active column again.

An example of the resulting table would be:

Event_Type  Begin_Timestamp         End_Timestamp           Duration
A           2010-10-01 00:00:00     2010-10-01 00:00:02     2 seconds
B           2010-10-01 00:00:01     2010-10-01 00:00:04     3 seconds
D           2010-10-01 00:00:03     null                    null
C           2010-10-01 00:00:05     null                    null
A           2010-10-01 00:00:06     2010-10-01 00:00:08     2 seconds

Is there any window function that would help me get these pairs of events so I could compute the duration of each event?

You need something like

-- get previous status, use -1 value if no previous record (because NULL value needs more complex condition in cte2)
WITH cte1 AS (SELECT Event_Type,
                     Event_Timestamp,
                     Is_Active,
                     COALESCE(LAG(Is_Active) OVER (PARTITION BY Event_Type
                                                   ORDER BY Event_Timestamp ASC), -1) prev_event
              FROM datatable),
     -- remove records where status not altered
     cte2 AS (SELECT Event_Type,
                     Event_Timestamp,
                     Is_Active
              FROM cte1
              WHERE Is_Active != prev_event)
-- get data we need
SELECT Event_Type,
       Event_Timestamp Begin_Timestamp,
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type
                                   ORDER BY Event_Timestamp ASC) End_Timestamp,
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type
                                   ORDER BY Event_Timestamp ASC) - Event_Timestamp Duration
FROM cte2
WHERE Is_Active = 1

Of course you can combine it into the query using one CTE which calculates all window values. The above long way is used for to explain the algorythm.


Primary keys: composite and singular In general when I design tables I always include a single unique field, even if it there is already a unique combination of columns available to create a primary key. This field is usually either a UUID (if application-generated) or a sequence (if database-generated).

Some questions:

are there any downsides to this approach (apart from storage)?
the sole exception to this rule is (for me) the join table that exists only to join other tables. Is there an argument to be made that a join table should also have a single field acting as primary key?

The benefits of using a surrogate key (that's your newly created single field), in my opinion, outweigh the downsides in most cases.

But there are a few cases that you may experience some drawbacks.

Think of a company's employees that work different shifts on different days of the week. Here's a simplified database:

SHIFT(ShiftID, Shift, SomeOtherColumn);
EMPLOYEE(EmployeeID, FirstName, LastName);
[TRANSACTION](TransactionID, ShiftID, EmployeeID, Date);

So, as constructed, we've created a surrogate key for each table. If we need to, say, find out what shift correspond to a particular shiftID from a particular transactionID, we'd have to run a second query. That's the downside of using a surrogate key in that case.

If the fields Shift and SomeOtherColumn from the SHIFT table can be assumed to be a candidate key (a composite primary key, in other words), then we would have to declare both fields in the our FOREIGN KEYconstraint DDL when we created the [TRANSACTION] table. So, we would be able to directly query the actual Shift from the [TRANSACTION] table without having to run an additional query.

Another issue with using a catch-all surrogate key is that it has no intrinsic value. So, if you work for a larger organization, but there are different databases (in other departments, for examples) that refer to the same items with different primary keys (surrogate keys), you may have a bit of an issue if you have join their data regularly.

Ditto for the developer who has 2 or 3 apps, maintains a different user database for each, with a different surrogate ID for each user but may have a ton of duplicate users between them.

Personally, I go on a case by case basis, but lean toward using a surrogate key more unless there is an outstanding reason I should not.


Is there a free software addition to SQLite available for data-replication? I am aware of the possibility to create my own trigger(ed) solution, but I didn't want to reinvent the wheel :-)

Berkeley DB's SQL API is completely SQLite compatible and offers HA/replication for high availability. The combination of SQLite's easy-of-use and Berkeley DB's scalability and reliability are a great combination.

Disclaimer: I'm the Product Manager for Berkeley DB, so I'm a little biased. However, one of the main benefits of adding the SQLite API to Berkeley DB is that SQLite applications can now have HA/replication for mission-critical, highly available applications.


Always On DDL operations Always On availability group with two nodes, synchronous commit.

Redo thread contention on the secondary replica regularly creates a very large redo queue. I have confirmed the wait types are similar to the following:

https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/

In my case, this is one extended events session(captured when the redo queue was large) output grouped and aggregated like in the above link:

My question:

How can I find out the exact source of the DDL operation which is causing the LCK_M_SCH_M wait?

As Brent Ozar mentioned in the comment section that this is not a simple task to find wait type (and what is causing the wait) between primary and secondary with correlation to time.  I am answering your question about finding the source.
I modified extended event trace definition given in the blog post you mentioned.  Removed the where clause so you can capture all the sessions that is causing wait.

Added few more actions to capture more information. For example:

sqlserver.client_hostname
sqlserver.plan_handle
sqlserver.session_nt_username
sqlserver.sql_text

Here is the full definition.

CREATE event session [redo_wait_info] ON server ADD event sqlos.wait_info( action(package0.event_sequence,sqlos.scheduler_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.plan_handle,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) ) ADD target package0.event_file(SET filename=N'C:\Redo_Wait_Info.xel',
  max_file_size=(50),
  max_rollover_files=(100)) WITH (max_memory=4096 kb,
event_retention_mode=allow_multiple_event_loss,
max_dispatch_latency=120 seconds,
max_event_size=0 kb,
memory_partition_mode=none,
track_causality=OFF,
startup_state=ON)
GO


Find records with the same relationships Say I have Orders and Products.
For a given Order I want to find all the other orders that are made up of the exact same products.
So the schema would look something like this:

CREATE TABLE Order (
    id int
    ...
)
CREATE TABLE Product (
    id int,
    name text
)
CREATE TABLE OrderProduct (
    OrderId int,
    ProductId int
)

Consider the following data.

Order| Product
1|Burger
1|Drink
1|Fries
2|Burger
2|Drink
3|Burger
4|Burger
5|Burger
5|Drink
5|Fries

The desired query would return a list of every order that has the exact same products:

Where order ID -> Returned Result rows
1-> 1
    5
2-> 2
3-> 3
    4
4-> 3
    4
5-> 1
    5

Everything I have come up with so far requires ugly queries, multiple steps and knowing how many products will belong to an order up front.
Is there a simple way to achieve this?

One way to achieve that would be using Group_Concat() to stuff all the products for each order into one line, then match up all of the orders that have the same products and use Group_Concat() again to stuff all of them into the same line. See example:

Select `cte`.`Order` As `Queried order`,
    Group_Concat(`cte2`.`Order` Order By `cte2`.`Order`) As `Returned Orders`
  From (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte`
  Join (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte2` On `cte`.`Products` = `cte2`.`Products`
  Group By `cte`.`Order`
;

This may be ugly, but it doesn't require multiple steps, or knowing the number of products up front.

Edit
Should be easier to get the output that you now describe. Just remove the final GROUP BY and GROUP_CONCAT:

Select `cte`.`Order` As `Queried order`,
    `cte2`.`Order` As `Returned Order`
  From (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte`
  Join (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte2` On `cte`.`Products` = `cte2`.`Products`
;


AlwaysOn Availability Groups and Encrypted Channels If I want to implement both AlwaysOn Availability Groups and Encrypted Channels, how would I do this?

There is a desktop application (Visual Studio 2008 built) that interacts with this database, and the client wants traffic between the application and the database encrypted.  They also want redundancy in the database.

You can simply enable SSL on each replica as per normal, but the certificate you provision should have a CN that matches the Listener name that you're connecting to.

See here for more info.


how to run DBMS_RULE_ADM Oracle 11.2.0.3

My instructions are

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '<schema_name>');

I tried to run them at sqlplus /as sysdba

but it tells me unknown command beginning DBMS_RULE

Is there a different way to run these?

You need to use EXEC[UTE] or BEGIN ... END ;.

EXEC DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '');

Or:

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '');
END;
/


How to disable Read-Only routing in SQL Server I know I can just change the connection string to remove the applicationintent property, but how do I remove the configuration from within SQL Server?

For example, if we query sys.availability_read_only_routing_lists, how do I get an empty result?

Ref:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql

Use ALTER AVAILABILITY GROUP command with MODIFY REPLICA ON option.

<modify_replica_spec>::=
  <server_instance> WITH
    (
       ENDPOINT_URL = 'TCP://system-address:port'
     | AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }
     | FAILOVER_MODE = { AUTOMATIC | MANUAL }
     | SEEDING_MODE = { AUTOMATIC | MANUAL }
     | BACKUP_PRIORITY = n
     | SECONDARY_ROLE ( {
          ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
        | READ_ONLY_ROUTING_URL = 'TCP://system-address:port'
          } )
     | PRIMARY_ROLE ( {
          ALLOW_CONNECTIONS = { READ_WRITE | ALL }
        | READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE }
          } )
     | SESSION_TIMEOUT = seconds
    )

  NONE

  Specifies that when this availability replica is the primary
  replica, read-only routing will not be supported. This is the default
  behavior. When used with MODIFY REPLICA ON, this value disables an
  existing list, if any.


Table-Valued Functions from Linked Server Why does when I try to run:

SELECT * FROM [servername].master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK)

I get error:

Msg 195, Level 15, State 15, Line 45
'servername.master.sys.dm_os_volume_stats' is not a recognized function name.

And when I run it in the server (ex: servername), I get different error:

SELECT * FROM master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK)

Msg 317, Level 16, State 1, Line 1
Table-valued function 'dm_os_volume_stats' cannot have a column alias.

But it gets no error if I removed the AS btb (NOLOCK)

My goal is to query from a linked server and get free disk space for each server (using union). Below is part of the query:

FROM [servername].[master].sys.master_files AS f WITH(NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

But Im still looking for a solution for the issue regarding TVFs.

Just use a passthrough query. And get rid of NOLOCK (it doesn't even do anything here as the catalog is always read with locking read committed semantics).

EG

exec ('
select *
FROM [master].sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
') at [servername]

or

select *
from openquery (servername, '
select database_id, file_id,  ...
FROM [master].sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
')


Postgresql 9.5 BRIN index dramatically slower than expected I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations.  I assumed that my situation was made to order for BRIN indexing given the natural ordering by date of my log lines.

However, I'm started by the results. BRIN is more than an order of magnitude slower than btree.

Original Btree index:

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

 Aggregate  (cost=153488.38..153488.39 rows=1 width=0) (actual time=7672.508..7672.509 rows=1 loops=1)
   ->  Index Only Scan using logline_date on logline  (cost=0.43..145945.76 rows=3017046 width=0) (actual time=18.548..4084.455
rows=2977593 loops=1)
         Index Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
         Heap Fetches: 5809
 Planning time: 0.293 ms
 Execution time: 7672.562 ms
(6 rows)

DROP index logline_date
CREATE index logline_date_brin on logline using BRIN(date)

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

 Aggregate  (cost=1230518.30..1230518.31 rows=1 width=0) (actual time=105789.131..105789.133 rows=1 loops=1)
   ->  Bitmap Heap Scan on logline  (cost=31543.27..1222862.87 rows=3062173 width=0) (actual time=103.876..100675.372 rows=2977593 loops=1)
         Recheck Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
         Rows Removed by Index Recheck: 2899899
         Heap Blocks: lossy=696832
         ->  Bitmap Index Scan on logline_date_brin  (cost=0.00..30777.73 rows=3062173 width=0) (actual time=103.079..103.079 rows=6968320 loops=1)
               Index Cond: ((date >= '2016-01-15 00:00:00-05'::timestamp with time zone) AND (date <= '2016-01-31 00:00:00-05'::timestamp with time zone))
 Planning time: 0.377 ms
 Execution time: 105805.567 ms
(9 rows)

The BRIN index was >600 times smaller than the Btree, but I didn't expect the execution time to be so much slower.

Does that mean BRIN is not for me, or am I doing something wrong?

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.


Copy a large data from sql query result In my sql server 2008, after running the query the data return is very huge say in millions. I tried copying but it gives an exception sql memory error. Can you let us know how can i copy the entire data and paste it into excel.

I don't want to export it to txt file as the data is not getting aligned.
So i want to manually copy and paste it into excel.
Please let me know the solution for thhis

You aren't going to be able to copy and paste like you want.  However you do have several options.  Simplest is to right click on the output and save as a CSV file.  That assumes that you don't have an commas in your text data though.

Your next option (and probably best) is to use the export wizard.  Right click on your database name, then Tasks, then Export Wizard

Your source will be the database you right clicked on.

Pick an excel destination.

Select the Write a query to specify the data to transfer.

Paste in your query

You can change the sheet name in your spreadsheet here.

Next is the list of mappings for you to review

And from here you can either run the package immediately or save the SSIS package for later use/fixes.  If your export doesn't work the first time and you are at all familiar with SSIS I would save the package so you can go into it and make corrections outside the wizard.

Then just hit finish and finish again.


Performance of dynamic SQL Server reporting queries? I have to build an application that allows the end user to select what columns they want in their report.

For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns available that could potentially be selected.

I am worried that this will greatly harm the performance of the query. What are my options? Create indexes for each possible query is not really possible.

Would a columnstore index be beneficial?

Dynamic SQL isn't in and of itself a performance problem. After all, the queries run against SQL Server regardless of how they were assembled, and still generate (and cache) the same execution plans as identical queries not constructed dynamic SQL. In ancient SQL Server versions, things like stored procedures were compiled as a whole, but SQL Server has been able to optimize at the statement level for a long time now.

What you need to be sure of is:

that the rows you are after can be seeked efficiently. You're never going to be able to create a covering index for all 60 columns, but if you can reduce the number of rows, any lookups or other mechanisms to get the columns not in the index shouldn't be too terrible. Hard to get into more specifics here, without any specifics to start with. I'm not sure if a columnstore index is going to give you what you need, especially because in SQL Server 2012 at least, there are a lot of limitations that most shops can't work around.
another option is to ensure that your index(es) used for searching to locate rows have all the output columns that aren't used as predicates as INCLUDE columns. These don't take up space in the index key but can help prevent lookups without creating a different index for every output combination. The trick here is to simply always return all columns, but move the logic of which columns to show to the reporting tool. Another nice thing about this is that if the user decides they want to add these three columns and hide those other two, the application should be able to do that without making another round-trip and re-querying all the data.
make sure you are using the optimize for ad hoc workloads server setting. This prevents single-use plans from occupying space in the plan cache wastefully.