How much the decision to use ternary or binary relationship influences the accuracy of the model with regards to a given business model?

Answers

The ERD shown does not match the rules stated.
I believe your second ERD shows these rules.
A ternary table would not match because of the many-to-many between sector and product.

Exposition

The rules you give are all binary rules.  They relate one entity type to another.  If you have a rule which mentions three entity type then a ternary table would be appropriate, but you have not.  For example the intersection entity type "TargetMarket" would be ternary - Red Bull (company) targets energy drink (product) to software (sector).

I'm inferring from the many-to-many between company and product that the products are generic.  For example "chocolate" and "spreadsheet".  They cannot be "Toblerone" and "MS Excel" as they are trademarked and can be produced by only one company (ignoring licencing agreements).  If all companies stoped producing chocolate (God forbid!) I imagine you would still like to record that "chocolate" was in the sector "food".  With a ternary table this would not be possible.  If all companies ceased producing chocolate (i.e. deleted corresponding rows from the ternary table) the chocolate <-> food association would disappear.  Similarly a newly-formed company could not be recorded in this system until it was producing products.  Should it ever cease producing products (e.g. become a shell company, go into administration) it would have to be removed from the ternary table and hence from the system entirely.

As I mentioned in the parent question, the full answer depends on the meaning of the relationships between the entity types, as embedded in their names, and both binary and ternary tables may be required.  Say a company produces 4 products, each of which is categorised in 3 sectors.  That would produce 12 possible sector-product-company combinations.  If your system requires to capture that only, say, 9 of these possibilities are valid or exist in fact, or some other well-named constraint then the ternary table would be the right way to do this.  But this is a rule which is not mentioned in your set above.

I would suggest you examine the constraints between a company's sector and those of the products it produces.  There may be redundancies there which should be removed for the model.


Is it possible to use cross database queries in both Azure and SQL Server 2016?

The 'New Polybase connectors' added in SQL Server 2019 CTP 2.0 give you what you're looking for.  These are new in 2019, and are not in the 2016 and 2017 releases.

See 'New PolyBase connectors' here:

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions#databaseengine


Infinite scrolling with full text search in PostgreSQL

It seems correct to me, but I get this error: column "rank" does not exist.  It works if I replace rank with its expression in the WHERE clause. Will that impact performance?

Yes it will in as much as without that change the query does not work. So that's a positive effect, however rank will not be computed twice so there is no penalty for that.

Nevertheless the whole table needs to be scanned to generate the rank column before the order-by can be done, so efficiency is about the same as offset.

  A second question: how can I make keyset pagination work if I wanted to sort id ASC instead of id DESC

If it's a number type, use -id instead. else you'd have to expand the tuple comparison out into a big messy expression.


How does clustering on a field other than autoincrement id affect inserts?

In your actual design you have unique, static,narrow, ever-increasing clustered index key that causes no page splits when rows are inserted.

If you create your clustered index on TrackerId and DateRecorded (you want to use these columns in this order, right? because your seek predicate has an equality on TrackerId and range on DateRecorded) you'll get non-increasing clustered key that will cause page splits on INSERT.

It will be wider and maybe not unique.

But final decision is up to you. We don't know if this table has more reads or writes, and page splits can be mitigated by choosing appropriate Fill Factor and regular index rebuild.

Here your can read more on it: Mitigating Index Fragmentation by Paul Randal


So if these are your requirements:

User select a few countries on a web page.
Countries selected by that user are stored in database.

You need to do the following:

create table country(
countryID number,
name varchar(50));

Set countryId as a primary key.

create table user(
userId number,
username varchar(50));

set userId as a primary key.

Since they are connecting to your page to click on these countries, we can call these "sessions", so new rule:

Every time a user connects to the web site, a session is logged.

Which then produces this table

create table session(
sessionId number,
userId number,
countryID number))

Set sessionID as a primary key.
Set userID as a Foreign key referencing table user.userId.
Set countryID as a foreign key referencing table country.countryId.

If you want to count how many countries each user has selected in a given session, you would then do the following:

select sessionId,UserId, count(*)
from session
group by sessionId,UserId

If you want to know all countries selected for every time a user went on your web page, group by UserId only.

Again, this is based on very very limited requirements.
You should be able to reuse the concept for your own purpose however.

I hope that helps.


AdventureWorks2012: "The FILE SIZE property is incorrect" error while trying to attach sample database "Adventure Works for SQL Server 2012"

Try downloading from AdventureWorks Databases – 2012, 2008R2 and 2008.

This might be due to corrupt installs.


Log all table updates for all tables in MySQL

I got some interesting news for you. You cannot use information_schema.tables because it does not track changes to InnoDB tables.

The best way to find out when a table changed is to go to the OS and get the most recent timestamp of each table. Rather than plagiarize my own posts, here are my posts where I show you how to do this

Sep 25, 2014 : want to find out which databases are used in last 30 days or not
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
Dec 21, 2011 : Fastest way to check if InnoDB table has changed

Give Them a Try !!!


Under the covers, SQL Server will return the entire rowset from the SELECT, and then assigns every value (really quickly) to the variable. Of course, the last value in the SELECT will be what is finally assigned.

Here's where it gets tricky. The order of what is returned, with no WHERE or ORDER BY clause, can depend on any number of conditions. Usually it's returned in the order of the clustered index, but that's not guaranteed.


Upgrade path - Oracle Standard to Oracle Enterprise

It's easy because internal database structure almost identical:

Backup all database files and /network/admin (just for sure);
Uninstall Standard Edition;
Install Enterprise Edition;
Point ORACLE_SID to old database;
Startup database and run catalog.sql and catproc.sql from new version;
Recompile all invalid objects.

Note that software version, release number and patch level must be identical.


DBD::mysql complains "Character set 'latin1' is not a compiled character set"

At MySQLBugs they suggested trying with binaries available at MySQL Community Downloads .

Downloaded:

mysql-5.5.8-freebsd8.0-x86_64.tar.gz

Extracted to:

/usr/local/mysql-5.5.8-freebsd8.0-x86_64

Linked to

/usr/local/mysql

And re-compiled/re-installed p5-DBD-mysql against it by doing the following:

export PATH=/usr/local/mysql:$PATH
cd /usr/ports/databases/p5-DBD-mysql
make cleanenter
make deinstall
make install

This is supposed to fix your perl module.

Are you still following FreeBSD Bugzilla – Bug 153588 ?

You may also refeer to http://www.freebsd.org/cgi/query-pr.cgi?pr=150959*

Hope you can keep going from here!


query to select data between todays date as well as tomorrows date time upto specific time

This is the expression for 6:00 PM yesterday

SELECT DATE(NOW()) - INTERVAL 6 HOUR;

Craft the SELECT with that expression

SELECT * FROM pagination WHERE
created >= DATE(NOW()) - INTERVAL 6 HOUR;

Give it a Try !!!


Storage does not failover in Windows cluster

Can you please post screen shots of the storage from Failover Cluster Manager? I'm interested in seeing the data and log drives in Failover Cluster Manager.


Adding hstore entries to an uninitialized (NULL) column

In SQL, NULL (operator) (value) is generally NULL.

This is not unique to hstore, and is the norm for everything.

The empty string '' is different to NULL. '' || 'somestring' is 'somestring', wheras NULL || 'somestring' is NULL.

The same is true for hstore. Just like NULL + 1 is NULL.

If this is a problem for you, you should probably store empty hstore values instead of NULL and assign a NOT NULL constraint on the column.


How to know how healthy an SQL Server database is now?

Look at sp_blitzfirst from www.brentozar.com
This store procedure will give you a good status of your SQL servers.


Can non-domain-connected users use Report Builder

I cant comment (not enough rep on this site) so I have to submit this as an answer.

1) I have successfully set up a SharePoint SSRS portal using forms authentication (FBA). The SSRS part was not hard - very straightforward actually, just perform the SSRS install as SharePoint integrated.  However, the SharePoint FBA part was very painful (not difficult, just lost a lot of hair and sleep over little stuff you didnt think would be necessary and couldnt find straight answers to.)

I know that you can use Report Builder 3.0 to save to a SharePoint site.  I have not tried that using the FBA SharePoint portal.  I dont work at that company anymore, so I dont have access to test that either.  I would think it is very possible though.  The portal I set up was accessed by non-domain users, which is why I had to use FBA.  I was using SharePoint 2007 and SQL Server 2008 R2.

I know the following link tells of the permissions needed for Report Builder to save to a SharePoint library: http://technet.microsoft.com/en-us/library/dd255209(v=sql.105).aspx

2) I did find a specific blog post about using Basic Authentication: http://blogs.msdn.com/b/bobmeyers/archive/2006/09/28/how-to-enable-report-builder-for-non-domain-users.aspx.
There is also an article on MS technet titled: "How to: Configure Report Builder Access"

3) Another thing that is worth trying - You can use a domain account for report viewing only, then use the ReportViewer Web service/API/webpart/etc to have a report embedded within a web application.  That way, the report connects using the username/password specified, not the user viewing the application.  That has worked for an application I helped work on that was exposed on a DMZ web server, but used an SSRS report that was on the internal network.  In that case, the SSRS instance was a Native install.  If you are using .NET - you can use Visual Studio.  It already has controls built in.

The only think I dont know about in this case is the use of Report Builder.  Just thought I would mention it because it might help spur some thoughts.


This should return tag the records that need attention.  I put the tagging in SELECT, but you could easily turn this into a second CTE and simply select out the payments to clean up.

--
-- find all accounts with more than one payment and mark payments to cancel
--
WITH cte_DuplicatePayments AS
(
SELECT COUNT(*) OVER(PARTITION BY accountID) AS numberOfPaymentsPerAccountID
, COUNT(*) OVER(partition BY accountID, amount) AS numberOfPaymentsPerAccountIDAndAmount
, ROW_NUMBER() OVER(partition BY accountID ORDER BY amount asc) AS PaymentsNumberPerAccountID
, *
FROM ScheduledPayment
)
SELECT CASE
    WHEN numberOfPaymentsPerAccountID != numberOfPaymentsPerAccountIDAndAmount THEN 'MARK AS CANCELLED: Duplicate Payments with amount mismatch'
    WHEN PaymentsNumberPerAccountID > 1 THEN 'MARK AS CANCELLED: Duplicate Payments with matching amount'
    ELSE ''
   END AS PaymentAuditAction
, ScheduledPaymentID, accountID, amount,
FROM cte_DuplicatePayments
WHERE numberOfPaymentsPerAccountID > 1


I noticed that in the sql failover clustering under the possible owners none of the nodes has been checked, I have a two node cluster does that mean in the event of failover it will failover to any of the active nodes.

You only have two nodes. If there is a failover... there is only one node to fail to, so having preferred owners set is... kind of a wash. It doesn't get you anything, to be quite honest.

  Whats is the difference if both of the nodes are checked vs if both of the nodes are unchecked as possible owners.

In a two node cluster there is no difference. There is only one other node to go to so it doesn't make much sense.

Now, assume you had an X node cluster where X is > 2 and you're still using SQL FCI's (Note: AGs work differently, don't check or uncheck these values).

Now, if you choose "Move service to best possible..." it'll go to a preferred first, additionally if there were a fail over it'd move to a preferred first as well. Since it's all about preference and not about actual possible owners, having a preference set is unlikely going to really change anything, in my honest opinion.

Some will argue about fail back, which I don't like to enable and requires there to be a cluster event for failback to occur anyway... so it's a wash.


Analyze logs to tell if database was moved or copied

I think you can keep an eye on below kind of alerts to find out any change in the database movement:

EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE';
EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'ONLINE';

Message ID of 5084 if you're monitoring database option changes

You would indeed see a logged message in the SQL Server error log:

  Setting database option OFFLINE to ON for database 'TargetDBName'.

  Setting database option ONLINE to ON for database 'TargetDBName'.

Why I say is the process includes :

Issue an ALTER DATABASE command using the SET OFFLINE option to take the database off-line.

2.Move the files to their new locations.

3.Issue the appropriate ALTER DATABASE commands using the MODIFY FILE option to tell SQL Server where the new locations are.

4.Issue an ALTER DATABASE command using the SET ONLINE option to bring the database back on-line.

Finally you can cross check the below command and match the timings of creations date and the log dates:

SELECT Name,create_date, state_desc FROM sys.databases

You can even rely on Profiler traces to find out the changed locations of the existing Database files to another location and you can keep a point on who changed it too.

Hope this helps a bit...


Have less tables with a lot of data or a number of tables with smaller amounts of data?

Only you can really answer that question.  If you are trying to determine whether to have a global Types table and a global Statuses table for all other tables that have this data, or whether to have a Types table and a Statuses table for each table that requires type/status data.

Would the pertinent data fields be the same or similiar?  In other words, would the multiple tables have the same schema and structure?  If so, you could think of combining them.  How many tables are we talking about, and how many Types and Statuses would there be?  You don't want to saturate what would normally have been a quick operation (though choose indexes and keep statistics updated often obviously).

Answering these questions will allow you to determine what to do.

But the main point here is that, if the Types and Statuses are not related then they should be kept in separate tables.  You never know if down the line you want to add a certain field.  That'd be much easier if it's isolated to a smaller Type and Status table.


Getting rows in left table only if not present in the right table

Here is an example :

SELECT
    a.col1
    , a.col2
    , a.col3
FROM
   table1 a
   LEFT JOIN table2 b
       ON
           (a.pkcol = b.pkcol)
WHERE
   b.pkcol IS NULL

So in your case, it will most likely be :

SELECT
    n.seqid
    ,n.wordindex
    ,n.label
    ,n.arg1
    ,n.arg2
    ,n.sublabel
FROM
    newparsed n
        LEFT JOIN   advlabel a
            ON
                ((a.`seqid` = n.`seqid`)
            AND
                (a.`wordindex` = n.`wordindex`))
WHERE
   a.`seqid` IS NULL

You just have to use LEFT JOIN and select any field (I suggest a primary key) in the right table that is NULL


Postgres database dump and restore on different database

It sounds like you're trying to refresh a database that's a snapshot of another DB elsewhere, and do so without interrupting read access to the first DB.

If so, the easiest way is actually to do it live in the snapshot DB. Just begin a transaction, truncate all the tables, and populate them with new data, then commit. Concurrent transactions will see the old data until you commit, then see the new data.

If for some reason you can't do that, you may instead wish to populate one DB offline, then ALTER DATABASE ... RENAME to swap them. Doing this will require a brief period where all connections are forced offline and currently running transactions are aborted, which is why I suggest doing it with the first method above.

If all your tables are in one schema in the main DB you could instead populate a copy of the schema then ALTER SCHEMA ... RENAME to swap them. This doesn't require disconnection.


Why should I save both markdown and the converted HTML in my database?

Your argument is flawed.

You are concerning yourselves with doubling a 15kB per article  × 10k rows = 150MB table. (Excluding images)

And, you will be accessing individual rows via a PK.  You can have a trillion rows and the database won't care.  10k rows is tiny.

Additionally, the database is already going to be inflated due to Revisions and Text Indexing .  Adding the HTML version shouldn't hurt you.  Not even at 10M rows.

Oh, if the author is editing the Markdown version, you better record what she/he wrote.  (Ie always convert from the author's format to the printed format; never the other way around)


I used to manage an environment where we had transactional replication and the backup schedule was Weekly Full, Daily Diff and log backups several times per day.

The backups didn't have any effect on replication. We had a few dozen publications consisting of hundreds of articles. If we had an issue with one, we would reset it and it would simply snapshot the tables in that publication.

The way it worked was that SQL did something similar to a select * from the tables involved along with reading their schema and uploading them to snapshot files on the distributor.  Then it dropped or truncated the destination tables on the replica server and used the files to rebuild them.


How to get last inserted id and insert it into another table using stored procedure?

Simply. Just add SET @ClientID = SCOPE_IDENTITY() after first INSERT statement. So you don't need @ClientID input parameter.

ALTER PROCEDURE dbo.My_SP
-- Add the parameters for the stored procedure here
--@ClientID         BIGINT,
@CreatedBy        INT           = NULL,
@CreatedDateTime  DATETIME      = NULL,
@ModifiedBy       INT           = NULL,
@ModifiedDateTime DATETIME      = NULL,
@Name             NVARCHAR(255)

AS
     SET NOCOUNT ON;

     DECLARE @ClientID as BIGINT

     INSERT INTO dbo.Clients
     (CreatedBy,
      CreatedDateTime,
      ModifiedBy,
      ModifiedDateTime,
      Name,
      Active
     )
     VALUES
     (@CreatedBy,
      @CreatedDateTime,
      @ModifiedBy,
      @ModifiedDateTime,
      @Name,
      1
     );

     SET @ClientID = SCOPE_IDENTITY();

     SELECT @Name AS ClientName;

     INSERT INTO BusinessRules
     (ClientId,
      ProjectId,
      TemplateName,
      Revised,
      Serial,
      Topic,
      QualityItem,
      CriticalToQC,
      Description,
      OwnerNotes,
      RelevantDocs,
      WhereToCheck
     )
            SELECT @ClientID,
                   NULL,
                   b.TemplateName,
                   b.Revised,
                   b.Serial,
                   b.Topic,
                   b.QualityItem,
                   b.CriticalToQC,
                   b.Description,
                   b.OwnerNotes,
                   b.RelevantDocs,
                   b.WhereToCheck
            FROM BusinessRules AS b
            WHERE ProjectID = 394;


I believe you can. We backup to a network location although we have the location mapped to a drive.

It seems you can use a UNC as well though according to this answer http://social.msdn.microsoft.com/Forums/sqlserver/en-US/965cd9c4-f4d5-4492-a202-f09b78d89ed1/how-to-backup-a-database-to-a-network-drive?forum=sqltools.

Have a look at the answer as there's some good considerations on here like if the sql service login has access to the network location.


Rolling sum / count / average over date interval

The query you have
You could simplify your query using a WINDOW clause, but that's just shortening the syntax, not changing the query plan.
SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date)
             ORDER BY trans_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Also using the slightly faster count(*), since id is certainly defined NOT NULL?
And you don't need to ORDER BY entity_id since you already PARTITION BY entity_id

You can simplify further, though:
Don't add ORDER BY to the window definition at all, it's not relevant to your query. Then you don't need to define a custom window frame, either:
SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date);

Simpler, faster, but still just a better version of what you have, with static months.
The query you might want
... is not clearly defined, so I'll build on these assumptions:
Count transactions and amount for every 30-day period within the first and last transaction of any entity_id. Exclude leading and trailing periods without activity, but include all possible 30-day periods within those outer bounds.
SELECT entity_id, trans_date
     , COALESCE(sum(daily_amount) OVER w, 0) AS trans_total
     , COALESCE(sum(daily_count)  OVER w, 0) AS trans_count
FROM  (
   SELECT entity_id
        , generate_series (min(trans_date)::timestamp
                         , GREATEST(min(trans_date), max(trans_date) - 29)::timestamp
                         , interval '1 day')::date AS trans_date
   FROM   transactiondb
   GROUP  BY 1
   ) x
LEFT JOIN (
   SELECT entity_id, trans_date
        , sum(amount) AS daily_amount, count(*) AS daily_count
   FROM   transactiondb
   GROUP  BY 1, 2
   ) t USING (entity_id, trans_date)
WINDOW w AS (PARTITION BY entity_id ORDER BY trans_date
             ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING);

This lists all 30-day periods for each entity_id with your aggregates and with trans_date being the first day (incl.) of the period. To get values for each individual row join to the base table once more ...
The basic difficulty is the same as discussed here:

Referencing current row in FILTER clause of window function

The frame definition of a window cannot depend on values of the current row.
And rather call generate_series() with timestamp input:

Generating time series between two dates in PostgreSQL

The query you actually want
After question update and discussion:
Accumulate rows of the same entity_id in a 30-day window starting at each actual transaction.
Since your data is distributed sparsely, it should be more efficient to run a self-join with a range condition, all the more since Postgres 9.1 does not have LATERAL joins, yet:
SELECT t0.id, t0.amount, t0.trans_date, t0.entity_id
     , sum(t1.amount) AS trans_total, count(*) AS trans_count
FROM   transactiondb t0
JOIN   transactiondb t1 USING (entity_id)
WHERE  t1.trans_date >= t0.trans_date
AND    t1.trans_date <  t0.trans_date + 30  -- exclude upper bound
-- AND    t0.entity_id = 114284  -- or pick a single entity ...
GROUP  BY t0.id  -- is PK!
ORDER  BY t0.trans_date, t0.id

SQL Fiddle.
A rolling window could only make sense (with respect to performance) with data for most days.
This does not aggregate duplicates on (trans_date, entity_id) per day, but all rows of the same day are always included in the 30-day window.
For a big table, a covering index like this could help quite a bit:
CREATE INDEX transactiondb_foo_idx
ON transactiondb (entity_id, trans_date, amount);

The last column amount is only useful if you get index-only scans out of it. Else drop it.
But it's not going to be used while you select the whole table anyway. It would support queries for a small subset.


Best Possible Solution for Reporting Mirroring or Replication?

Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying.  You have to create and maintain snapshots, which can be annoying.

Your options are, in no particular order:

Mirroring with snapshots : effective but has management overhead.
Backup and restore : Are you restoring from full backups rather than differential or logs?  If so, you may be able to reduce the time spent on restores.
Log shipping : management overhead, database is unavailable when log backups are being applied
Replication : challenging from a management perspective, not really intended for syncing full databases
Report from transactional database - This option is all too often discarded over unsubstantiated performance concerns.  Those concerns can also be mitigated somewhat through the use of Resource Governor, snapshot isolation, etc. This is an underutilized option, IMO.
ETL with SSIS or a similar tool. You would have to do your own schema changes, however.
Availability Groups, if you can upgrade to 2012 (I assume you're on 2008 R2).  This is really the best option available these days.
Idera makes a tool called Virtual Database that allows you to mount and query a backup file.  It's an interesting option in some cases.

It's difficult to make a clear recommendation without knowing a lot more information about your environment.  I've used most of these methods to varying degrees of success.  Note that most places end up building out more robust data marts / data warehouses for reporting and analytics, so you'll probably end up with the ETL route one day.

Oh, and make sure you have licensed the secondary server ;)


PostgreSQL ignores implicit casts in some situations

The difference is this:
In the first two (working) examples, 'a' is just an untyped string literal - which is strictly different from varchar. The conversion to your custom enum type is provided by basic input/output functions of the enum type. (There is no explicit entry in the system catalog pg_cast.) Postgres determines the best match for a data type as the user did not provide one. Your custom cast is not even needed. Neither for type resolution nor for operator resolution.
In the following examples, you pass a typed value (varchar). Now, your custom cast may be of use. For example, this wouldn't work without your custom cast:
INSERT INTO t VALUES ('a'::varchar);

Your examples are not plain assignments, but expressions with the operator =. We are in the realm of Operator Type Resolution. Item 2a states:

If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check.

That would cover my_enum_column = 'foo' - with untyped literal.
But there is nothing that can be stretched to cover my_enum_column = 'foo'::varchar. Seems like the fact that only a generic = operator for left and right operand anyenum is available is not enough to clue in operator resolution:
-- missing operators:
SELECT oprleft::regtype, oprright::regtype, *
FROM   pg_operator
WHERE  oprname = '='
AND   ('anyenum'::regtype IN (oprleft, oprright) OR
       'my_enum'::regtype IN (oprleft, oprright));

 oprleft | oprright | ...
 --------+----------+----
 anyenum | anyenum  | ...
(1 row)

You would have to register a more explicit operator for this. But I wouldn't do that. I would do things like (also note various orthogonal syntax fixes):
PREPARE p(my_enum) AS SELECT * FROM t WHERE x = $1;  -- prepare with type my_enum
EXECUTE p('a'::varchar); -- then your cast kicks in
EXECUTE p('a'); -- untyped string literal works in any case

CREATE FUNCTION f1(ix my_enum, OUT ox my_enum)   -- again, declare enum
  RETURNS SETOF my_enum AS
$$
SELECT x FROM t WHERE x = ix
$$ LANGUAGE sql;

SELECT * FROM f1('a'::varchar);

CREATE FUNCTION f2(ix my_enum)  -- same here
  RETURNS SETOF t AS
$$
BEGIN
   RETURN QUERY
   SELECT * FROM t WHERE x = ix;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM f2('a'::varchar);
SELECT * FROM f2('a');

db<>fiddle here
Or just add explicit casts. That's simplest and safest. The manual warns (on the same page you quoted):

It is wise to be conservative about marking casts as implicit. ...

There is more - recommended reading.
If you must create that custom cast (or even add an operator), consider using text instead of varchar, which is the "preferred type" among string types and hence more robust against possible corner-case issues.


If MAX(id) != COUNT(id), does that mean rows have been deleted?

I assume you are asking about columns with the AUTO_INCREMENT property.

No, deleted rows is not the only reason, there are several others:

inserts that did not succeed (due to duplicate unique or primary key violations or other reasons).
inserts inside transactions that did not commit and were rolled back.
inserts where the id was explicitely defined (and id numbers were skipped or negative values added). In the second case, you can have MAX(id) less than COUNT(id) as a result.
variation of the previous case: column was not AUTO_INCREMENT when the table was created - so there were explicit inserts for some period - and then modified.
updates of the auto-incrementing column.
the id was set to start from a value different than the default 1.
the auto_increment step setting (system variable: auto_increment_increment) of the server is not 1 (or had been changed to something else than 1 for some period).
and (something that is usually neglected because the id is usually the primary key), the column was not defined as the PRIMARY or UNIQUE key. That allows duplicates and one more case where the MAX can be lower than the COUNT.
a variation of the above is (with MyISAM engine) an auto-incrementing column that is part of a composite primary key.


Privileges for database owner; application user

You want ALTER DEFAULT PRIVILEGES.

Give the rds_superuser default access rights to all new tables.

This only affects tables created after the ALTER. For existing tables you must GRANT rights.


USERS are themselves ROLES that can login. CREATE USER is actually just an alias. What you probably want is some kind of query that psql issues with \du as a starting point. You can see what psql issues with -E,

********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************


How to update an element in json array with where clause?

You can use a query like the one below;

UPDATE table SET
  data = jsonb_set(data,'{pattern, values}', ((data->'pattern'->'values') - 'foo') ||  '["bar"]' )
WHERE
  (data #> '{pattern, values}') ? 'foo';

Details;

First you have to find records which have "foo" value in "values" array;

(data #> '{pattern, values}') ? 'foo'

After that remove the value "foo" from the array;

((data->'pattern'->'values') - 'foo')

Create a new array by concatenating your new value(s).

((data->'pattern'->'values') - 'foo') ||  '["bar"]'

Replace new array with the old one.

jsonb_set(data,'{pattern, values}', new_array)


oracle 12c rac switch from mounted to open

Connect to third instance and issue

SQL> alter database open;

then to fourth instance and do the same.

But if you want to open all instances at once, you need to use srvctl utility:

srvctl start database -db db_name

https://docs.oracle.com/database/121/RACAD/GUID-C9DB4DCB-34B9-42F0-8AB4-33199087CE98.htm#RACAD900


You could try using sed and substitute | (the pipe character - not normally found in data dumps) for the " (double quote) character

sed -i 's/"/|/g' My_File.csv

sed -i does an inline replace - take a backup before messing with your original file. If you're on Windows - as you appear to be, you can get sed functionality from the answer here or use the editor of your choice - VIM for Windows for example or whatever you prefer.

Then when you've loaded the data, do a REPLACE() on your tables


min/max performance versus order by limit in sqlite?

When there is an index, both queries just take the first entry from the index.

When there is no index, MIN() does a single pass through the table, and ORDER BY sorts only as much as is needed for the LIMIT, which ends up the same effort. (Older versions of SQLite did sort everything.)


Row locking or avoiding duplicate insertion on primarykey/otherfield combo

The OP answered their own question in the comments (now deleted):

  Never mind I figured it out, using a unique index combo, ALTER TABLE files ADD UNIQUE INDEX (file_relation, file_relation_id, file_dimension) does the trick.


Zombie Sql Server DB on Secondary Replica is both in and out of an Availability Group

I pulled the oldest full backup I could find and was able to restore that over the Restoring db using:

RESTORE DATABASE [ZOMBIEDB] FROM  DISK = N'D:\ZOMBIEDB.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

I was then able to delete the restored database.


I agree with Jack that your schema needs help. But you can still do this. Here we do this with one index lookup, using two core extensions intarray and btree_gist

CREATE EXTENSION intarray;
CREATE EXTENSION btree_gist;

CREATE INDEX ON public.schools_messages
  USING gist(school_id, parents_ids gist__int_ops);

VACUUM ANALYZE public.schools_messages;

SELECT *
FROM public.schools_messages
WHERE school_id = 42
  OR parent_id @> ARRAY[42];


Optimise Percona 5.7 my.cnf for a 32GB mixed nginx/php/mysql server

32GB RAM, 12G buffer_pool --> lots of room left over for resizing images.
12G buffer_pool, 0.5GB ibdata1 and 5.7 --> innodb_file_per_table is probably ON.  Is it?  If so, you have not given us a clue of how much data you have.  Look at the .ibd files.

query_cache_size = 256M is probably bad.  Any write to a table causes the purging of all QC entries for that table.  The purging is proportional to the size of the QC.  Keep it under 50M.  (Or turn it off.)

A Rule of Thumb:  "Don't put a cache in front of a cache."  MySQL does a lot of caching internally; adding a webapp-level cache may not help, and may rob RAM from the buffer_pool, where it is important.

Do you know if you are I/O-bound?  CPU-bound?

I see that you have the slowlog on.  Use pt-query-digest to summarize it.  Then, let's discuss the worst couple of queries.  Fixing slow queries is more likely to give your performance a boost than any tuning.

But, if you would like more tuning advice, let's see the GLOBAL STATUS.  In particular, follow the directions here .


The difference between local time and UTC time can be reliably calculated by looking at the difference between GETDATE() and GETUTCDATE(), assuming the server operating system is configured in whatever the desired "local" time is (i.e. if local time for you is Central European Time, then the server must be configured to be in Central European Time).
As an example, consider the following:
GO
DROP FUNCTION dbo.LocalTimeToUTC;
GO
CREATE FUNCTION dbo.LocalTimeToUTC
(
    @LocalTime datetime
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT UTCTime = DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), @LocalTime)
)
GO

DROP FUNCTION dbo.UTCToLocalTime;
GO
CREATE FUNCTION dbo.UTCToLocalTime
(
    @UTCTime datetime
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT LocalTime = DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @UTCTime)
)
GO

SELECT *
FROM (
    VALUES ('2020-09-10T08:47:00')
    )
    v(LocalDate)
CROSS APPLY dbo.LocalTimeToUTC(v.LocalDate) u
CROSS APPLY dbo.UTCToLocalTime(u.UTCTime) l

The output looks like:
╔═════════════════════╦═════════════════════════╦═════════════════════════╗
║      LocalDate      ║         UTCTime         ║        LocalTime        ║
╠═════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2020-09-10T08:47:00 ║ 2020-09-10 13:47:00.000 ║ 2020-09-10 08:47:00.000 ║
╚═════════════════════╩═════════════════════════╩═════════════════════════╝
For clarity, this only works assuming all your clients are in the same time zone, and the server is installed to match your desired "local" time zone.
GETUTCDATE() is supported in all versions of SQL Server since at least 2008.
It's also worth noting that if you use this on datetime values saved in a table, and your local timezone uses daylight savings time, where the difference between local and UTC time varies during the year, you would need to adjust for the difference between standard time and daylight savings time.
A more reliable method of storing datetime data in SQL Server when you're concerned about converting between time zones is to the use datetimeoffset data type instead of datetime, and using the AT TIME ZONE modifier when querying the stored values.
An example of how that works:
DECLARE @dt datetimeoffset;
SET @dt = GETDATE() AT TIME ZONE 'Central Standard Time';

SELECT @dt;

(No column name)
2020-10-19 10:26:37.8100000 -05:00
To get the time at UTC:
SELECT @dt AT TIME ZONE 'UTC';

2020-10-19 15:26:37.8100000 +00:00


The big issue when upgrading across SQL Server 2014, meaning, going from anything less than 2014 to anything 2014 or greater, is the fact that the 2014 release of SQL Server included a new Cardinality Estimation Engine.
For the majority of queries, the new CE won't affect them in any way. Some queries will run faster. A few queries, usually queries that were already problematic edge cases, will indeed run radically slower.
The best way to deal with this would have been to use Query Store as part of your upgrade, as I outline in this recent blog post. You could, possibly, still do this. Change the compatibility mode of the database back to the old version, then follow the steps outlined. However, that's predicated on the fact that you haven't already started using new coding mechanisms in the database that are 2019 only.
Otherwise, you'll need to look first at the execution plans for the queries involved. Compare the plans from 2012 with the plans from 2019. See where the issues lie. It's likely that what you really need are updates to the code and underlying data structures. However, many can't, or won't, do that work. Instead, read through the details of the CE in this Microsoft article. There, you can see the options available to you from leaving the compatibility mode at the old setting, to changing the CE through Database Scoped Configuration changes, to query hints, and finally, back around to the Query Store.
One of these solutions will help. However, the best one, is to look at the code & structures and adjust them to be more optimal.


Difficulty with nested query order & grouping

This sort of thing can be done fairly elegantly using Window Functions.  While I was researching this answer, I found that MySQL doesn't support them.  The code that follows is will likely have some performance problems since it uses nested GROUP BY select statements.  Proceed with caution.

SELECT UserID
     -- potentially add a date column here
     , sum(Counter) Updates
FROM (
     SELECT UserID
          , ObjectID
          --- potentially add a date column here
          , 1 Counter
     FROM Object_Update_Table OUT inner join
          (
          SELECT ObjectID
               -- potentially add a date column here
               , max(timestamp)
          FROM Object_Update_Table OUT
          WHERE timestamp >= today 00:00:00
                AND timestamp < tomorrow
          -- if you add a date column, you'll need to add a GROUP BY clause with the date column here
          ) last_update_day ON OUT.ObjectID = last_update_day.ObjectID
                           AND OUT.timestamp = last_update_day.timestamp
     ) Join_Back_User
GROUP BY Join_Back_User.UserID
     --, potentially add a date column here

The case above only works for one day due to the WHERE clause in line 12 and 13.  If you need to do this for more than one day at a time, you should add a function which returns the date to the inner-most sub-query and then carry that date in the Join_Back_User subquery to the outermost query...


Remove Database from SQL Management Studio Database List

You could detach the database, that way the file isn't deleted and it won't display inside of SSMS.


Socket recv() timeout on secondary nodes

Just a shot in the dark. Could it be a problem with the primary servicing too many connectionss simultaneously?

This information comes from the mongodb 202 training.

If a primary can no longer service requests, there will be

socket exceptions
timeouts
the over servers can no longer ping the primary possibly causing an election
--maxConns limit connections to mongos can insulate mongods from getting into unpredicatble states

From MongoDB best practices

Connections MongoDB drivers implement connection pooling to facilitate efficient use of resources. Each connection consumes 1MB of RAM, so be careful to monitor the total number of connections so they do not over-whelm the available RAM and reduce the availablememory for the working set. This typically happenswhen client applicationsdo not properly close their connections, or with Java in particular, that relies ongarbage collection to close the connections.

If there are too many connections open on the primary at any one given time, secondaries, administrative, clients, etc, then your primary could also be running out of memory.

Here is an example layout, with connections coming from drivers, secondaries, to and from different processes. This can all quickly add up depending on the number of clients and machines in your infrastructure!


How to upgrade Mysql 5.5 to 5.6 without data loss on aws-ec2?

You don't need to dump and import the databases, a copy of the folder /var/lib/mysql should be ok for backup.
I suggest you upgrade to MySQL 5.7 as its the currently supported release and has better performance,
here are the steps, I implemented it many times with no issues
1. # service mysql stop

backup your databases before the upgrade
$ cp /var/lib/mysql /var/lib/mysql.original

Download the MySQL 5.7 RPM
$ wget http://repo.mysql.com/mysql57-community-release-el7.rpm -P /tmp/

Remove the MySQL-Community RPM that contains MySQL 5.5
$ yum remove mysql-community-release

Install the MySQL 5.7 RPM
$ rpm -ivh /tmp/mysql57-community-release-el7.rpm

Update the MySQL Repository to 5.7
$ yum update mysql

Let MySQL know that it received an upgrade
service mysql start
mysql_upgrade


Microsoft Access database to FileMaker Pro

You have basically two options (without resorting to any third party tool, besides ODBC):

Use intermediate "common import/export formats": Export data from all the different tables in Access in a format that FileMaker Pro can immediately import, then import the exported files from FileMaker. The safest format is normally CSV --comma separated values--; although it won't work with FileMaker if you have data with line-feeds or carriage-returns inside. You can also try Excel as an intermediate export format (with this format, you may have some limitation if you have long texts). Check: Introduction to importing and exporting data (Access) and Bringing data into FileMaker Pro - Overview and troubleshooting.
Direct import through ODBC: Directly import the tables from FileMaker by means of an ODBC connection**. When you install Office, you normally get ODBC controllers installed by default, so, if you have Access installed in your computer, you're very likely to have ODBC controllers as well. Make an ODBC connection to your Access database, and use it from within FileMaker to import your tables. Check Querying an ODBC data source from FileMaker Pro.

FileMaker can import table data from Access (or other databases). It cannot import table relationships (or foreign key relationships) or primary key definitions, null/not null constraints, index definitions or check constraints. All these metadata you'll need to transfer "by hand". The Access database documenter can help you get most of it.


How do others know what is delivery address and invoice address if they are in the same table?

An address is an address. There’s no need to store the same address details twice because it’s used for a delivery address and also invoicing.
In your orders table you would have columns like delivery_address_id and invoice_address_id, they could have the same value in them.
If you wanted to list all addresses that were used as a delivery address you can do a simple semi-join
Select *
from address
where address_id in (select delivery_address_id from orders)


@Marine1,You mongoimport syntax should be like as mention below example.

mongoimport --db test --collection stuff --type csv C:\data\Employee.csv --headerline

In the above example test is database name, stuff is collection name and Employee.csv is the file which i want to import in MongoDB database through mongo shell. And the Employee.csv file location is C:\data\Employee.csv.
In My case it has worked properly.

In your case the syntax should be like that

mongoimport --db databasename --collection collectionname --type csv  locationofbouffe.csv  --headerline

The syntax should be in the above mention format, if you want to import the header of the file then mention --headerline.

Note:- Make sure you are running the above mention script in inside the BIN of MongoDB Server.

Very Important Note : mongoimport would be to import a non-BSON file (such as JSON or CSV) into mongodb.


Yes, there is a difference, and the issue is that the estimated selectivity (thus cardinality) will be different in the 2 cases.

For a simple example, lets say the year column has years in it from 2000 (low_value) to 2014 (high_value), so there are 15 distinct values (num_distinct), they are evenly distributed, 1000 rows for each, 15000 rows (num_rows) in total in your table.

In the first case, where year < 2010 (limit = 2010), the estimated selectivity will be:

(limit - low_value) / (high_value - low_value) = (2010 - 2000) / (2014 -2000) = 10 / 14

Cardinality = 10/14 * 15000 = 10714

For the second case, where year <= 2009 (limit = 2009), selectivity is calculated as:

(limit - low_value) / (high_value - low_value) + 1/num_distinct = (2009 - 2000) / (2014 - 2000) + 1/15 = 9/14 + 1/15

Cardinality = (9/14 + 1/15) * 15000 = 10642

Not a big difference, but it can still affect the optimizer so that it choses another plan.

Above is explained in detail by Jonathan Lewis in his book "Cost-Based Oracle Fundamentals".


Determining max database and table size supported and present size

Here are some nice queries I have used for years:

This reports the amount of data and indexes for each storage engine:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

This reports the amount of data and indexes for each database:

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

This reports the amount of data and indexes for each database  by storage engine:

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size" FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

Please notice at the end of each query there is an inline query (SELECT 3 pw). The pw is simply an exponent used as a power of 1024. Here is how to use this:

(SELECT 0 pw) reports in bytes
(SELECT 1 pw) reports in kilobytes
(SELECT 2 pw) reports in megabytes
(SELECT 3 pw) reports in gigabytes
(SELECT 4 pw) reports in terabytes
(SELECT 5 pw) reports in petabytes (If you got this much data, write me or at least post the display in DBA.SE. I'm sure we'd all love to see that)

Give it a Try !!!

As for table limits, here are those limits for MyISAM and InnoDB.


The Cloud Manager (née MMS) documentation includes a list of the Database Commands Used by the Monitoring Agent.

The main command to start with is serverStatus, which includes most metrics specific to a mongod instance (as opposed to replica set or sharded cluster information).

You can run this command via a driver and preview the results with the db.serverStatus() helper in the mongo shell.

Available subsections and output may vary depending on the version and configuration of your MongoDB server.

Interesting sections of serverStatus typically include:

opcounters: insert, query, update, delete, command, getmore
opcounters-repl: opcounters that are the result of replicated operations applied on a secondary
connections: current, available, and total created connections
asserts: number and type of assertions that have been raised since the server started

For more information see:

serverStatus command description
Server Status Output


Terribly slow Disk Read (I/O) When fixing corrupt MyISAM table (9GB)

To anyone whom it may concern;

The problem of such slow disk read lies in the fragmentation of the data. A subsequent repair of the same table gives very high disk read i/o, indicating sequential access as opposed to random access.

The table has plenty of deleted rows, and the newly inserted records must then be read in a random fashion, resulting in slow read speeds. Again, after the table was repaired, the records where stored sequentially, and the same repair operation gave very fast results. I guess this is another reason to use InnoDB over MyISAM.

Thanks, Michael.


I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore.

The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version.

(Note that all of these solutions will only work well if you try to change exactly one row with each transaction. As soon as you try to do multiple changes in one transaction things get messy because of the need for retry loops on rollbacks. At minimum you'd need to use a savepoint between each change.)

Two-statement version subject to lost updates.

The version that uses two separate statements is subject to lost updates unless the application checks the affected-row count from the UPDATE statement and the INSERT statement and retries if both are zero.

Imagine what happens if you have two transactions in READ COMMITTED isolation.

TX1 runs the UPDATE (no effect)
TX1 runs the INSERT (inserts a row)
TX2 runs the UPDATE (no effect, row inserted by TX1 isn't visible yet)
TX1 COMMITs.
TX2 runs the INSERT, *which gets a new snapshot that can see the row committed by TX1. The EXISTS clause returns true, because TX2 can now see the row inserted by TX1.

So TX2 has no effect. Unless the app checks the rowcount from the update and the insert and retries if both report zero rows, it won't know that the transaction had no effect and will merrily carry on.

The only way it can check the affected rowcounts is to run it as two separate statements rather than a multi-statement, or use a procedure.

You can use SERIALIZABLE isolation, but you'll still need a retry loop to deal with serialization failures.

The wCTE version protects against the lost updates issue because the INSERT is conditional on whether the UPDATE affects any rows, rather than on a separate query.

The wCTE doesn't eliminate unique violations

The writeable CTE version still isn't a reliable upsert.

Consider two transactions that run this concurrently.

Both execute the VALUES clause.
Now both of them execute the UPDATE portion. Since there are no rows matching the UPDATEs where clause, both return an empty resultset from the update and make no changes.
Now both run the INSERT portion. Since the UPDATE returned zero rows for both queries, both attempt to INSERT the row.

One succeeds. One throws a unique violation and aborts.

This isn't cause for concern about data loss so long as the app checks for error results from its queries (i.e. any decently written app) and re-tries, but it makes the solution no better than the existing two-statement versions. It doesn't eliminate the need for a retry loop.

The advantage the wCTE offers over the existing two-statement version is that it uses the output of the UPDATE to decide whether to INSERT, instead of using a separate query against the table. That's partly an optimisation, but it partly protects against a problem with the two-statement version that causes lost updates; see below.

You can run the wCTE in SERIALIZABLE isolation, but then you'll just get serialization failures instead of unique violations. It won't change the need for a retry loop.

The wCTE does not appear to be vulnerable to lost updates

My comment suggested that this solution could result in lost updates, but upon reviewing that I think I may have been mistaken.

It's over a year ago, and I can't recall the exact circumstances, but I think I probably missed the fact that unique indexes have a partial exception from transaction visibility rules in order to allow one inserting transaction to wait for another to insert or roll back before proceeding.

Or perhaps I missed the fact that the INSERT in the wCTE is conditional on whether the UPDATE affected any rows, not on whether the candidate row exists in the table.

Conflicting INSERTs on a unique index wait for commit/rollback

Say that one copy of the query runs, inserting a row. The change is not yet committed. The new tuple exists in the heap and the unique index, but it isn't visible to other transactions yet, irrespective of isolation levels.

Now another copy of the query runs. The inserted row is not yet visible as the first copy hasn't committed, so the update doesn't match anything. The query will go on to attempt an insert, which will see that another in-progress transaction is inserting that same key and will block waiting for that transaction to commit or roll back.

If the first transaction commits, the second one will fail with a unique violation, per the above. If the first transaction rolls back then the second will proceed with its insert instead.

The INSERT being dependent on the UPDATE rowcount protects against lost updates

Unlike in the two-statement case, I don't think the wCTE is vulnerable to lost updates.

If the UPDATE has no effect, the INSERT will always run, because it's strictly conditional on whether the UPDATE did anything, not on the external table state. So it can still fail with a unique violation, but it can't silently fail to have any effect and lose the update entirely.


You have to install SQL Server 2008 r2 Standard edition side by side (if you want to do it on the same server) and then move the database using backup/restore method.

Below is an outline of what needs to be done (if you want to do it on the same server without installing a side by side SQL 2008R2 instance).

backup all database on Enterprise Edition & script out the logins as well.
uninstall Enterprise Edition
install Standard Edition
Patch it to latest SP
restore databases
Create logins and map orphan users (if any).

Also, Unless you're using Enterprise-only features, you should be able to use detach and attach method from Enterprise to Standard.

Refer to answer by Jonathan here.

Also, refer to this approach with scripts... BUT WITH EXTREME CAUTION !! ... it uses detach/attach method and if you are using any enterprise features (which wont work in Standard edition) then the attach wont work.

Also, refer to Version and Edition Upgrades Matrix.


Should these two indexes suggested by the SSMS missing index feature be combined?

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized]
  ON [dbo].[PackageEvents] ([PackageID])
  INCLUDE ([EventDate], [EventDescription], [EventID],
    [LastSyncDate], [Notes], [UserName], [Version])
  WHERE IsSynchronized = 0;

Of course you may want to make that even smaller and test to see the difference in impact if the query has to look up the data (should be pretty efficient if the number of rows is small), so - assuming PackageID is the clustering key:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized]
  ON [dbo].[PackageEvents] ([PackageID])
  WHERE IsSynchronized = 0;

The overhead of maintaining this index may very well be worth the space savings compared to a full index, especially if it's only being used to optimize this query (or query pattern, at least).

Filtered indexes are not magic, though; JNK brought up some limitations below:

  Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail.

Also you'll want to read these posts:

How filtered indexes could be a much more powerful feature
Optimizer Limitations with Filtered Indexes
An Unexpected Side-Effect of Adding a Filtered Index

If you don't want to use a filtered index, you can probably test variations of these:

CREATE NONCLUSTERED INDEX [IX_Covering_try1] ON [dbo].[PackageEvents]
  ([PackageID], IsSynchronized)
INCLUDE ([EventDate], [EventDescription], [EventID],
  [LastSyncDate], [Notes], [UserName], [Version]);

CREATE NONCLUSTERED INDEX [IX_Covering_try2] ON [dbo].[PackageEvents]
  (IsSynchronized, [PackageID])
INCLUDE ([EventDate], [EventDescription], [EventID],
  [LastSyncDate], [Notes], [UserName], [Version]);

(For a long time I thought that including BIT columns in the key was wasteful but Martin Smith demonstrated a case where it worked quite well - worth a try. I can't find the post now.)

Without your full schema, data, query patterns etc. we can only guide you and have you test our suggestions in your environment. We can't say, "Ding! This is the one that will work for you!"


I would probably add a boolean column to the rooms table indicating "virtual rooms" (room "ABC", for example, would have this set). Then add another table called VirtualRoomMembers, with two columns, both of which are foreign keys back to your Rooms table's primary key (assuming room_id int here):

CREATE TABLE VirtualRoomMembers (
    room_id int NOT NULL,
    sub_room_id int NOT NULL
)

A many-to-many design would account for the possibility that you could have multiple usable groupings for a set of rooms; perhaps you can book ABC, AB, or BC.

Now you insert records in that table to define which rooms make up which virtual rooms, and you can easily take these into account when anti-joining to the reservations to see what's available:

SELECT
    r.room_id FROM Rooms r
    LEFT OUTER JOIN VirtualRoomMembers vrm --Get the sub-rooms
        ON r.room_id = vrm.room_id
    LEFT OUTER JOIN VirtualRoomMembers vrm2 --Get the parent rooms
        ON r.room_id = vrm2.sub_room_id
    LEFT OUTER JOIN Reservations rs
        ON (
            r.room_id = rs.room_id OR
            (r.virtual_room = 1 AND vrm.sub_room_id = rs.room_id) --Make sure none of the child rooms are booked
            OR (r.virtual_room = 0 AND vrm2.room_id = rs.room_id) --Make sure the combined parent room isn't booked
        )
        AND rs... (filter the reservations for the date range in question)
WHERE rs.reservation_id IS NULL


Create limited admin role in SQL Server that cannot alter row level security

A better approach to me would seem to be setting up the LimitedAdmin group at the server level, then set up triggers at the server and/or or DB level that check when the command(s) you want to block is run.

In each trigger, check to see if the user is in the LimitedAdmin group, then abort the DDL trigger if they are. This handles multiple different conditions with multiple similar triggers (i.e. ALTER SERVER ROLE and ALTER ROLE are two different triggers you would need). This would seem to offer finer control than the simple DENY statements.

As an example, suppose LimitedAdmin user [bob] was to trigger an ALTER SERVER ROLE event. The trigger could check what role was being altered, and decide if it was okay for LimitedAdmin users could alter that role in that way for the target user.

For example, adding another LimitedAdmin user should be disallowed for a LimitedAdmin user to perform, but they might be allowed to add an 'AccountingSystem' user. One trigger could be used to check all of the possibilities. This would mean that once the triggers are configured, adding a user to the LimitedAdmin group should be all of the configuration that you need.


SQL Query with Aggregate Count, where at least 1 of the counts has a value in a field

I suspect you can do it using a subquery, something like:

SELECT PERSON_ID, COUNT(APPL_ID) COUNTS
FROM APPLICATIONS
WHERE PERSON_ID IN (
    SELECT DISTINCT PERSON_ID
    FROM APPLICATIONS
    WHERE APPL_DATE > 2018-01-01
)
GROUP BY PERSON_ID
ORDER BY COUNTS DESC


List certain user ids and drop them in Oracle

To select a list if users connected to the database:

select distinct USERNAME
from v$session;

Note that some will be internal Oracle sessions.

To drop a user in Oracle, you use the DROP USER command:

DROP USER USERNAME;

Scripting it is easy, you can run in a loop and use EXECUTE IMMEDIATE, or spool a query from SQL*Plus to create the script for you:

  select 'drop user '||USERNAME||';'
  from v$session
  where username not in (... LIST OF USERS YOU DONT WANT TO DROP ...);

I do wonder why on earth you'd want to automate this though. Seems like a very bizarre business case.


Count total number of nested one to many relationships

Let me know if this covers your desired result.

create table users (user_id int primary key, name varchar(20));
insert into users values(1, 'john'), (2, 'anne'), (3, 'peter');

create table costs(cost_id int primary key, user_id int);
insert into costs values (1, 1), (2, 1), (3, 2);

create table payments(payment_id int primary key, cost_id int);
insert into payments values
(1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 3);
GO

13 rows affected

select   c.user_id, count(*) num_payments
from     payments p
join     costs c
on       p.cost_id = c.cost_id
group by c.user_id
;
GO

user_id | num_payments
------: | -----------:
      1 |            5
      2 |            2

select    u.user_id, u.name, coalesce(p.num_payments, 0) num_payments
from      users u
left join (select   c.user_id, count(*) num_payments
           from     payments p
           join     costs c
           on       p.cost_id = c.cost_id
           group by c.user_id) p
on         u.user_id = p.user_id
GO

user_id | name  | num_payments
------: | :---- | -----------:
      1 | john  |            5
      2 | anne  |            2
      3 | peter |            0

dbfiddle here

Or better a left join solution:

select    u.user_id, u.name, count(p.payment_id) num_payments
from      users u
left join costs c
on        u.user_id = c.user_id
left join payments p
on        p.cost_id = c.cost_id
group by  u.user_id, u.name
;
GO

user_id | name  | num_payments
------: | :---- | -----------:
      1 | john  |            5
      2 | anne  |            2
      3 | peter |            0

dbfiddle here


Database performance for determining entities assigned to users

The short answer...

This has the hallmarks of solving the wrong problem with the wrong solution. It sounds like you're looking for a workaround to a fundamental design flaw, introducing an entirely new set of problems on the way.

An attempt at a longer answer...

It's almost impossible to offer any practical advice without a better understanding of the database, the application and interaction between the two. That said, there are one or two warning signs.

Logic in a table function

It sounds like you have a hierarchy of entities, which could be represented in a relational model and navigated in a set oriented fashion. If complicated logic is required in the database to navigate the hierarchy, the model is probably wrong.

Do you have a relational database or an object store/bit bucket?

Your question refers to entities, rather than tables and records. Did you build an object model and dump it to tables, rather than map your objects to a relational model?

How much data are you returning to the user?

A 2 second stored procedure call could be sub-optimal, accessing a sub-optimal model or returning too much data. Requiring 10 calls to this procedure suggests it could well be all 3. Navigating a 40k record graph is not big data territory, unless you're application is loading 40k records from the database to determine what's navigable.

You already have a better model

Your workaround may actually be the solution, if applied differently. If the output from your table function is a model which works, perhaps it should be a permanent part of the model. Instead of building this temporary table every time a user logs in, why not keep the data in that form?


Error moving SQL Server 2000 to SQL Server 2005 NOLOCK Error

The best online resource for database corruption in SQL Server world is Paul Randal's web page. He is our world's number one star in corruption matters. And that's because he wrote all dbcc check commands in SQL Server (and he's the only one who can understand them properly :-).
What I'd do in your place now would be to try the backup-restore method of moving the database to new SQL Server or try dbcc with repair option (not before making a backup of the database).
There is some good info also on MSDN about how to treat these errors. Check the article Troubleshooting DBCC error 2570 in SQL Server 2005.


How do I make PostgreSQL log the file path and line number of the errors? (If it's even possible at all.)

Database logging is the wrong end to handle the problem, since the database does not have the required information.
You will have to write your client application so that it logs the information you need whenever it receives a database error.


Cassandra multidatacenter configuration with 1 external ip

You can try to make each node unique for some of the ports, but this isn't really a good path to go down. Cassandra nodes need to see each other on the ports listed here for the best setup: http://www.datastax.com/documentation/cassandra/1.2/index.html#cassandra/security/secureFireWall_r.html

One option that I have seen work well is a host VPN like Openswan: https://www.openswan.org/projects/openswan/


How to handle failed Index Optimization (Ola Hallengren) step in SQL Azure Elastic Job Agent?

In your case you should add the @Resumable = 'Y' option to your job and either restart or just wait for the next schedule to run the job and finish, depending on time and concurrency issues, as running index rebuild while many users are using the database can be an issue.

Statistic and index rebuilds were transactional in earlier versions so if an alter index rebuild statement failed the results were rolled back and the old object used but in 2017 onward there is a new option WITH (RESUMABLE=ON) that allows you to resume the index rebuild later.

You should also look at the @WaitAtLowPriorityMaxDuration and @LockTimeout parameters in Ola's scripts as those can help in minimizing blocking.


Should Full Text Filter Daemon Service be a cluster resource?

The Full-Text-Search daemon launches as required, and is triggered by the SQL Server engine itself.
So, in short, you don't need to add the Full Text Filter Daemon Launcher as a resource in a SQL Server failover cluster.
From the Docs page concerning Service Account settings for the Full Text Filter Daemon Launcher:

Make sure the SQL Server service group has permissions to the SQL Full-text Filter Daemon Launcher service account. During the installation of SQL Server, the SQL Server service group is granted default permission to manage, query, and start the SQL Full-text Filter Daemon Launcher service. If SQL Server service group permissions to the SQL Full-text Filter Daemon Launcher service account have been removed after SQL Server installation, the SQL Full-text Filter Daemon Launcher service will not start, and full-text search will be disabled.


Restore database to a instance in network

So are there multiple backups that may be selected?  I'm having a hard time following that portion of the question.

If there are a large number of backups that you don't want to port with the application installation, then you would be storing them on a network share accessible by all client machines that would be installing your application.  If there was only one backup, and it was reasonably sized, or multiple backups that are reasonably sized, why wouldn't you just have those ship with the installation?

Provided that's not an option, what you could do is create logic to first copy the backup file from the share to a local directory.  Think of it like this:

Copy backup file from "\yourShareName\Dir1\backup_file.bak" to "C:\Some\Local\Dir\backup_file.bak"
Restore your database from the local copy of the backup file


This query avoids the Compute Scalar of your query and avoids any changes in your table structure.  Should perform well.

SELECT dt.email
    , accept = CASE WHEN dm.[DOMAIN] IS NOT NULL THEN 'NO' ELSE 'Yes' END
FROM dbo.[Data] dt
    LEFT OUTER JOIN dbo.[DOMAINS] dm ON dt.email LIKE ('%@' + dm.DOMAIN)


The message "Running the Merge Agent process would exceed the maximum number of processes allowed" appears, but it doesn't seem likely

For merge replication issues, first stop is to read Chris Skorlinski's blog post at http://blogs.msdn.com/b/repltalk/archive/2010/06/28/merge-replication-capacity-planning.aspx

He links you to several resources at the top of his blog post.  The post is from 2010, but merge replication has not had any significant update as far as I know.

Chris's bottom line is that there are too many variables: complexity of queries, power of the server, concurrent processes, et cetera, to be able to provide a recommended configuration. For example: "I (have) seen simple designs with little or no filtering supporting 1000 of users to very complex filtering/join design barely able to hand 10 subscribers."

One link that he particularly recommends is: http://robtiffany.com/mobile-merge-replication-performance-and-scalability-cheat-sheet/

Rob's focus is on merging replication to a SQL Server for later shipping to SQL Compact.  But focus on the issues of merge replication and he has several suggestions, such as:

Ensure your schema is sufficiently de-normalized so that you never have to perform more than a 4-way JOIN across tables.
In order to prevent SQL Server from performing Metadata Cleanup every time a Subscriber synchronizes, set the –MetadataRetentionCleanup parameter to 0.
As SQL Server has to scale up ... locking contention will increase due to more (concurrent) Merge Agents.  Adjust the parameters of the Default Profile so that both  –SrcThreads and –DestThreads are equal to 1.

Note, I do not use merge replication.  But by the nature of the issues with merge, there are more resources needed than in standard replication.


Which SQL Server license is appropriate for my use case? Can I use a free license?

Free Edtions
SQL Server 20xx Express Editions
SQL Server 2019 Express Edition is free for production, non-production, and redistribution.  After install you'll find the license terms at
C:\Program Files\Microsoft SQL Server\150\License Terms\SQLServer2019_License_EXPR_1033.rtf
SQL Server 20xx Developer Editions
If you install the free SQL Server 2019 Developer Edition, you'll find the license terms at
C:\Program Files\Microsoft SQL Server\150\License Terms\SQLServer2019_License_DEV_1033.rtf
Which reads in relevant part:

Non-free Editions
For for the non-free editions of SQL Server refer to the licensing guide here.
You can also run these editions of SQL Server for non-production purposes if you have an appropriate Visual Studio Subscription (formerly called MSDN).


SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.


You'll need to rethink your shard key approach.

As at MongoDB 3.2:

All fields in a compound shard key must be present in all documents and will be immutable (i.e. the shard key for an existing document cannot be changed).
A hashed shard key is based on a single field, and does not support range queries.

It generally makes sense to have a shard key that supports your common queries so they can be targeted at a subset of shards with relevant data, but this doesn't appear to be possible in your case as both foo and bar are optional fields.

If your _id field provides good cardinality (i.e. large number of values) but is monotonically increasing (eg. default ObjectIDs) you could consider a hashed shard index on the _id field for good write distribution. The hashed index wouldn't support your common read queries (unless by specific _id values) so you would need a secondary index for your queries on foo and bar (i.e. {foo:1, bar:1}). The recommended secondary index(es) and order will depend on your common queries and sort order.

For further background information I suggest reviewing:

Considerations for Selecting Shard Keys
Use Indexes to Sort Query Results
Optimizing MongoDB Compound Indexes


Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?

From 10.1.14.1 Unicode Character Sets in the MySQL 5.5 Reference Manual (emphasis added):

  For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

and:

  The current rule that all supplementary characters are equal to each other is nonoptimal but is not expected to cause trouble. These characters are very rare, so it will be very rare that a multi-character string consists entirely of supplementary characters.

So the answer to your question appears to be "no".

finally:

  If you really want rows sorted by MySQL's rule and secondarily by code
  point value, it is easy:

  ORDER BY s1 COLLATE utf32_unicode_ci, s1 COLLATE utf32_bin

Though it is not clear to me how this should be applied for comparisons.

The documentation extracts quoted above are unchanged for MySQL 5.7.


Impacts of a change to the Page Verify option

Q1

  Does the backup process buffers data in cache ? If yes, could this
  cause a variation of PLE trend or memory pressure ?

The backup process does not read data into the buffer pool, it uses separate threads that use buffers to read and write the data pages.
More on that here.

It does create a checkpoint prior to starting the backup. This checkpoint will flush dirty pages to disk.

  A full backup does the following:

  Force a database checkpoint and make a note of the log sequence number
  at this point. This flushes all updated-in-memory pages to disk before
  anything is read by the backup to help minimize the amount of work the
  recovery part of restore has to do. ...

Source

Q2

  If backups with checksum are 20% slower what happens with application
  queries? I don't think my tests are the absolute truth but..

Performance benchmarking with or without checksum has been tested before.

See here.

In that test, the average was 94.1 seconds for an update query on a checksum enabled database & 93.7 seconds for the same update query on a database without any page verify option. I performed some similar tests (1M row updates with different page verify options) and also found negligible differences.

Some test results:

4x 1M rows updated (nvarchar(256), 156 characters) without checksum:

CPU time = 1235 ms,  elapsed time = 1231 ms.
CPU time = 1218 ms,  elapsed time = 1213 ms.
CPU time = 1547 ms,  elapsed time = 1583 ms.
CPU time = 1203 ms,  elapsed time = 1199 ms.

4x 1M rows updated (nvarchar(256), 156 characters)  with checksum:

CPU time = 1312 ms,  elapsed time = 1310 ms.
CPU time = 1282 ms,  elapsed time = 1282 ms.
CPU time = 1343 ms,  elapsed time = 1361 ms.
CPU time = 1282 ms,  elapsed time = 1291 ms.

Also I did several backup tests on a database with a data file of 34GB and I am getting nowhere near 20 percent difference.

The backups to 'NUL' getting faster due to caching does appear to be correct.

4 WITH CHECKSUM

BACKUP DATABASE successfully processed 4224190 pages in 89.307 seconds (369.528 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 89.339 seconds (369.395 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 85.930 seconds (384.050 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 83.255 seconds (396.389 MB/sec).

4 WITHOUT CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 75.234 seconds (438.650 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 74.964 seconds (440.230 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 78.338 seconds (421.269 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 77.531 seconds (425.654 MB/sec).

4 WITH CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 74.598 seconds (442.390 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 71.718 seconds (460.155 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 71.044 seconds (464.521 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 66.624 seconds (495.338 MB/sec).

4 WITHOUT CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 63.593 seconds (518.947 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 65.338 seconds (505.088 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 62.314 seconds (529.599 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 61.529 seconds (536.355 MB/sec).

Q3

  Is there any kind of waiting time linked to the checksum process?

Not sure what the question is here, maybe the following part of a blogpost can give more insight:

  Of course, WHEN you switch a database over to using CHECKSUM there is
  NOT some magical process or operation that goes through and ‘writes’
  CHECKSUMs into your EXISTING pages. If there were, toggling this
  option could obviously cause some ugly problems. But, instead, since
  this is effectively a ‘meta-data’ change that instructs SQL Server to
  start using a new behavior (i.e., CHECKSUM functionality for writes
  and reads of previously CHECKSUM’d pages), you can safely toggle this
  change at pretty much any time without fear of bogging down your
  systems.

  Of course, since this, in turn, means that you may have gobs of
  existing/older data that’s been laying around without CHECKSUM
  configured, that data (or the data on those pages) will NOT end up
  getting CHECKSUM values until that data is finally pulled into SQL
  Server and then re-written. Consequently, if you want to ‘force’
  CHECKSUMs into your pages, you’ll need to do full-blown index rebuilds
  or other modifications that force all data to be pulled in and then
  rewritten.

Source

Checksums are not applied immediately when enabling the setting, the page needs to have been modified to apply the new checksum.

Q4

  Do you know if there are people online who have tested the impacts of
  this change ? Any additional material can be useful.

See answer to Q2.


SQL Server - redirect synonym without downtime

Answer left in comments by Dan Guzman

DROP/CREATE all the synonyms within a transaction in steps 2 and 4. There may be some (hopefully) short-term blocking, but no downtime.


Alter default privileges in postgres for other roles

To add default privileges for a different user, use the FOR ROLE clause:

ALTER DEFAULT PRIVILEGES FOR ROLE user1 ...

See the documentation for details.

As to the required permissions to run that statement, the documentation says:

  You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

That means that, unless you are a superuser, you can only use roles in FOR ROLE of which you are directly or indirectly a member.


Why are mysql password hashes internally saved with a star (asterisk)?

Ok, found about this in the documentation itself.

This was a change introduced in mysql 4.1 so that the earlier password lengths of 16 characters and newer password lengths of 40 characters could be simultaneously supported. The Password column was made 41 bytes (chars) long, and the newer passwords would begin with a mandatory * to identify them.

From the documentation:

  Password hashes in the 4.1 format always begin with a “*” character, whereas passwords in the pre-4.1 format never do.


When you take a backup all information including the file path is included as part of the backup.  As you found out if you just run restore database it will try to restore to the exact same location.

when you include with replace, the backup overwrites the existing .mdf and .ldf files.  I normally also have the data and log path directives to specify where to put the files (on my phone will edit to include the exact command).

this is normal behavior since if I am restoring a database on the same server you are most likely recovering from something and would want the db files to be the same.

EDIT: Here is my normal restore command, whether on the same server or different.  I like to specify exactly where things are going.

RESTORE DATABASE dbName
   FROM DISK = N'Path\To\Backup.bak'
   WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
        MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
       REPLACE,
       STATS = 10

By always specifying the files I am writing to it cuts out some uncertainty and when some one comes and looks it is very clear what is going on.


The quick way to see it is to run SHOW ENGINE INNODB STATUS\G

You will see

exclusive locks
shared locks
corresponding queries
number of read IO thread in use
number of write IO thread in use

Here are some sample questions I answered that uses the output of SHOW ENGINE INNODB STATUS\G

Will these two queries result in a deadlock if executed in sequence?
Trouble deciphering a deadlock in an innodb status log

Give it a Try !!!


mysql server password change while restoring

According to me you have also dumped the mysql  database which contains the users privileges in the users table of mysql database.  & one more-thing you may have used --add-drop-database  or --add-drop-table while generating the dump

i.e     mysqldump -u user -p passwordd --add-drop-database  --all-databases > /home/dump.sql

and when you have restored it on your local machine as your dump contains the
drop table statements.so it had firstly dropped your user table in mysql database and then restored new one from the dump thats why you are facing the problem. so don't include the mysql database while generating the dump.

please see various option of mysqldump  using mysqldump --help.

Thanks....


Compound unique constraint with NULL values

If you can identify a value that will never be stored in the referenced tables (e.g. a negative value), you can use a unique index that uses expressions:

create unique index
  on favorite_comments( coalesce(opinion_id,-1), coalesce(review_id, -1), list_id );

the coalesce() will turn all null values in the same "real" value, forcing a unique constraint violation when you try to insert another NULL value.

The only drawback of a unique index compared to a unique constraint is, that the unique index can't be a target of a foreign key (while a unique constraint would be)


Use conditional aggregation:

select count(*) filter (where order_status_id in (5,4,15)) as a,
       count(*) filter (where order_status_id in (6)) as b,
       count(*) filter (where order_status_id in (4,10,9,12,7)) as c,
       count(*) filter (where order_status_id in (11,16,17,13)) as d
from order_item_histories;


How to access pg_settings from separate connection

pg_settings is a view, not a table.

And every session has its own version of it. Per documentation:

  The change only affects the value used by the current session.

The attempt to access it from a different connection (session) is futile.


are there significant differences with 500+ million row tables in Oracle?

The fundamentals of indexing etc. all work in exactly the same way, so strictly speaking the only difference is the cost of getting this wrong!

That said, here's a (not necessarily complete) list of things worth bearing in mind:

B-tree indexes are likely to have an extra level in them, so the cost of using them is slightly higher. However, in a DW you should be using bitmap indexes (assuming you've got enterprise edition)
It'll take a lot longer to calculate stats for the whole table - to the point where it may not be possible in the normal overnight window. This can be overcome by

Using a smaller estimate_percent when gathering stats so less of the table is sampled.
Using incremental stats gathering (only relevant if you have global indexes on partitioned tables however)

Histograms for indexes are limited to 254 buckets. More rows likely means more distinct values, meaning that "nearly popular" values can be a larger issue for skewed data.
The chances your whole table will fit in the buffer cache approaches nil, meaning you're more likely to have more physical (disk) reads. Your normal working set may also be too large to be cached.
Partitioning can be your friend - if you get it right! If you're usually modifying and query data across multiple partitions then it can cost you more than plain tables.
Materialized views can be very useful for reducing your working set. e.g. if you have 10+ years worth of data, but the vast majority of user queries are just against the past 2 years, then creating an MV limited to just this data can be a big help.
The bigger the dataabase, the less likely it is the business will (be able to) fund a test database that is a full duplicate of the live environment. This makes it harder to reproduce performance issues in test as slow queries may be due to the scale and/or physical storage of the data. You can't count on being able to extrapolate query results from a much smaller test DB to corresponding performance in live.

If you're not already familiar with reading and understanding execution plans I'd spend some time learning these: your bound to run into performance issues at some point so knowing how to diagnose the problem correctly will become more important as it's harder to add new indexes or make schema changes when your row counts are larger.


oracle: No more data to read from socket

No more data from socket means that the shadow process crashed, with an
ORA-600 or ORA-7445, for example. Look for a trace file in the
user_dump_dest directory and/or an entry in the alert log. This is
likely a bug

See this : https://blogs.oracle.com/db/entry/ora-600_troubleshooting


Is it better to have a CASE expression or a JOIN with a dimensional table?

For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale)

SELECT to_char(timestamp, 'Day')
FROM fact_table

In general the table form is, to me, cleaner (when there is no suitable function)

Perhaps with an in-line table if there is a good reason to not have a permanent table.

SELECT day_name
FROM fact_table
LEFT JOIN (VALUES (1,'Monday'),(2,'Tuesday'),(3,'Wednesday')) AS days(day_number ,day_name )
on day_number = date_part('dow',timestamp)::int


MySQL Replication Lag Behaving Erraticly

MySQL Replication performs weird calculations for Seconds_Behind_Master based on a few things.

What NOW() returns for SQL run on the master as recorded in relay logs
What NOW() returns on the slave
The last position from the master that was executed

Here is a SHOW SLAVE STATUS\G and how to identify the Last Executed SQL from the Master

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.17.20.102
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002814
          Read_Master_Log_Pos: 823078734
               Relay_Log_File: relay-bin.007364
                Relay_Log_Pos: 823078879
        Relay_Master_Log_File: mysql-bin.002814
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 823078734
              Relay_Log_Space: 823079071
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

Notice the following fields in SHOW SLAVE STATUS\G

Master_Log_File (Line 6) : Log File on the Master whose position was last read
Read_Master_Log_Pos (Line 7) : Last position read on the Slave from the Master
Relay_Master_Log_File (Line 10) : Log File on the Master whose position was last executed
Exec_Master_Log_Pos (Line 22) : Last position executed on the Slave from the Master
Relay_Log_Space (Line 23) : Sum of bytes from all relay logs

To calculate Seconds_Behind_Master, it would essentially figured out

NOW() as recorded in Master LogFile 'Master_Log_File' LogPos 'Read_Master_Log_Pos'
minus
NOW() on the Slave

This number can intermittently grow because the I/O thread can collect more entries from the Master and loading it into the last relay log while processing the SQL statement located at Master LogFile 'Relay_Master_Log_File' and Master LogPos 'Exec_Master_Log_Pos'. This increasing number is also manifested by three(3) variables changing: Relay_Log_Space, Master_Log_File, Read_Master_Log_Pos. If Seconds_Behind_Master is 0, this is good indication that Read_Master_Log_Pos and Exec_Master_Log_Pos virtually the same. Also worth noting is the fact that long running SQL can fool you into thinking that replication is behind. Once that SQL statement is complete and no other statements have sufficiently backlogged, Seconds_Behind_Master can drop dramatically, even to 0.

So, why would Seconds_Behind_Master bounce back and forth between 0 and an increasing number. It is possible for network latency in transmitting SQL through the I/O thread to contribute to miscalculation because the needed TIMESTAMP variable was not reached yet in the back of the last relay log (remember this is asynchronous replication). To verify this, run mysqlbinlog against any binary log or relay log and see where the TIMESTAMP variables are written between statements.

This behavior was first addressed June 22, 2007 and the bug was supposedly fixed then.

Here is one sure way to take MySQL Replication and screw its head on correctly (For this example, use the same SHOW SLAVE STATUS\G)

Using Relay_Master_Log_File mysql-bin.002814 and Exec_Master_Log_Pos 823078734 as the Log FilePosition to Restart From, run these commands

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.002814',MASTER_LOG_POS=823078734;
START SLAVE;

These steps should 1) kill both I/O and SQL Threads, 2) wipe out All Collected Relay Logs, 3) start with a New Relay Log, and 4) establish New I/O and SQL Threads.

Replication should be good to go from here. As to whether or not this bug can truly be addressed, this now in Oracle's hands (That last statement does not inspire any confidence, does it ???)


Using unique non clustered index with unique clustered index

Yes having a column in multiple unique keys is sometimes perfectly reasonable.  In the case that you gave above I'm not sure I would bother since the ProductId key is unique regardless.  But let's say that you have a product table like this:

ProductVendor  PK
ProductCode  PK
ProductDescription
.....

In this particular case the ProductVendor and ProductCode are together unique and are your primary key and clustered index.  However there is an additional business rule that ProductDescription must also be unique by ProductVendor.  In this case you could create a non-clustered index on ProductVendor, ProductDescription.


Create a user account login that cannot login from SQL Server Management Studio

This is what an Application Role is meant for.

  An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.


Search SQL Server table for a hexadecimal range

Assuming that you can't do the sensible thing and change the datatype of the column to be VARBINARY(10), then just convert the value in the VARCHAR column into VARBINARY(10) and then test to see if it is in that range. For example:

SELECT tmp.*
FROM   (VALUES ('000FFF5060B0'), ('000FFFD00DEE')) tmp(col)
WHERE  CONVERT(VARBINARY(10), tmp.[col], 2) BETWEEN 0x000FFF300000 AND 0x000FFF7FFF05;

returns only the following row:

000FFF5060B0

The style number of "2" in the CONVERT function allows a string of hex bytes without the preceding "0x" to be converted into binary without assuming that the hex bytes are encoded characters.

IF you will be doing such queries frequently, then it would be best to do the following:

Create a non-persisted Computed Column for that expression (i.e. ALTER TABLE {schema_name}.{table_name} ADD {column_name} AS (CONVERT(VARBINARY(10), [{varchar_column_name}], 2)); )
Create a non-clustered Index on that non-persisted computed column