No magic. CTE in this case is an updatable view.

Updatable and Insertable Views:

SQL Server
Oracle
MySQL
DB2
Teradata

  The following statement seems to do exactly the same:

In general - no. Simply imagine that id is not unique.

  Is the first option better?

Two queries with different (general) logic cannot be compared. - akina


Update column with data from another table

The proper form would be (assuming current pg version 9.3 for lack of information):

UPDATE C
SET    column1 = A.column1
     , column2 = B.column2
     , column3 = A.column1 + B.column2
FROM   A
JOIN   B ON A.id = B.id  -- ??? not specified in question!
WHERE  C.id = A.id      --  ??? not specified in question!
AND   (C.column1, C.column2, C.column3) IS DISTINCT FROM
      (A.column1, B.column2, A.column1 + B.column2);

The last WHERE clause is optional to avoid empty updates that would not change anything (but still write a new row version at full cost).

ypercube already gave a basic explanation in his comment:

  You don't get duplication. Your derived table is cross joining A and B (i.e. without any joining condition) and then choosing an arbitrary row (LIMIT 1 without ORDER BY). It then uses the values from that arbitrary row to update all rows of table C. If you want different values to be used for different rows of C, you'll have to join the 3 tables (using JOIN - ON and WHERE)

Refer to the manual on UPDATE for details.


Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses

One method is with a common table expression like the example below.  Note that the preceding statement must be terminated with a semicolon.

WITH cte AS (
SELECT  a.database_name ,
        a.collection_time ,
        ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                                              '+00:00')) ORDER BY a.collection_time DESC ) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE   a.database_name = 'MDWDB'
    )
SELECT database_name
    ,collection_time
    ,row_ID
FROM cte
WHERE Row_ID = 1;


AlwaysOn availability group DR failover with Powershell

I think what you are trying to achieve via powershell is for planned manual failover as mentioned in msdn link.  here

A planned manual failover is supported only when the primary replica
and the target secondary replica are running in synchronous-commit
mode and are currently synchronized. A planned manual failover
preserves all the data in the secondary databases that are joined to
the availability group on the target secondary replica

And what you exactly are trying to achieve " failover between async replicas " is more of forced manual failover with/without data loss which can be achieved via TSQL as mentioned in same msdn link. I don't see PS there but may be someone here who have used can answer. I am not sure but may be you can check dbatools if it provides one
Go through the steps as mentioned laster in that article

Manual failover without data loss
Use this method when the primary replica is available, but you need to
temporarily or permanently change the configuration and change the SQL
Server instance that hosts the primary replica. To avoid potential
data loss, before you issue the manual failover, ensure that the
target secondary replica is up to date.


In general, a well designed cluster can live for YEARS without being touched. I've had clusters that ran for years hands-off. However, here are some guidelines:

Monitoring is hugely important:

1) Monitor latencies. Use opscenter or your favorite metrics tools to keep track of latencies. Latencies going up can be signs of problems coming, including GC pauses (more common in read workloads than write workloads), sstable problems, and the like.

2) Monitor sstable counts. SSTable counts will increase if you overrun compaction (each sstable is written exactly one time - deletes are handled by combining old sstables into new sstables through compaction).

3) Monitor node state changes (up/down,etc). If you see nodes flapping, investigate, as it's not normal.

4) Keep track of your disk usage - traditionally, you need to stay under 50% (especially if you use STCS compaction).

There are some basic things you should and shouldn't do regularly:

1) Don't explicitly run nodetool compact. You mention that you've done it, it's not fatal, but it does create very large sstables, which then are less likely to participate in compaction moving forward. You don't necessarily need to keep running it, but sometimes it may help to get rid of deleted/overwritten data.

2) nodetool repair is typically recommended every gc_grace_seconds (10 days by default). There are workloads where this is less important - the biggest reason you NEED repair is to make sure deletion markers (tombstones) are transmitted before they expire (they live for gc_grace_seconds, if a node is down when the delete happened, that data may come back to life without the repair!). If you don't issue deletes, and you query with sufficient consistency level (reads and writes at QUORUM, for example), you can actually live a life without repair.

3) If you are going to repair, consider using incremental repair, and repair small ranges at a time.

4) Compaction strategies matter - a lot. STCS is great for writes, LCS is great for reads. DTCS has some quirks.

5) Data models matter - just like RDBMS/SQL environments get into trouble as unindexed queries hit large tables, Cassandra can be problematic with very large rows/partitions.

6) Snapshots are cheap. Very cheap. Nearly instant, just hard links, they cost almost no disk space immediately. Use snapshot before you upgrade versions, especially major versions.

7) Be careful with deletes. As hinted in #2, delete creates more data on disk, and doesn't free it for AT LEAST gc_grace_seconds.

When all else fails:

I've seen articles that suggest Cassandra in prod requires a dedicated head to manage any sized cluster - I don't know that it's necessarily true, but if you're concerned, you may want to hire a third party consultant (TheLastPickle, Pythian) or have a support contract (Datastax) to give you some peace of mind.


FLUSH TABLE : can't execute the given command because you have active locked tables mysql

This is because

flush table tablename1,tablename2 with read lock;

is contained within one transaction and

flush table tablename1 with read lock;

flush table tablename2 with read lock;

Is not.

In this second scenario the first table is locked for that session. That session can't acquire a second lock on another table because the session already has one table open. If you tried to access another different table by using a SELECT, within that same session, for example tablename2, that would also be refused.

MySQL [testlocks]> select * from tablename2;
ERROR 1100 (HY000): Table 'tablename2' was not locked with LOCK TABLES

So to make a long story short, by locking only one table you are saying that the current session only has access to that one table. At least that's how I understand MySQL's documentation.

Or you could just use

flush table tablename1,tablename2 with read lock;

as you already figured out and this is the syntax given by MySQL's documentation here


SQL Server 2012 autoupdate statistics default behaviour

I was just about to create a table, insert a bunch of data, create some statistics and do a test. But then I remembered this excellent blog post Erin Stellato wrote over on her blog. She's done that already.

Her tests show 20% in action. You can take what she's done her and the scripts provided, test it on your environment and witness the behavior yourself.

I've not experienced, seen or read anything that indicates this traceflag, or its behavior, is on by default in SQL Server 2012. I can't point you to official articles or documentations to prove a negative, though.

You can see for yourself which traceflags are enabled, however, by running

DBCC TRACESTATUS(-1)

Biggest advice here is - Empirical evidence rules the day. Not only will you know for sure, but you'll learn a lot in the process of figuring it out.


Ignoring temp table in Postgres event trigger

You'll have to exclude temporary objects by schema name:
FOR r IN
   SELECT * FROM pg_event_trigger_ddl_commands() AS ddl
   WHERE ddl.schema_name NOT LIKE 'pg_temp%'
LOOP
   ...
END LOOP;

The strange pattern is because temporary objects in PostgreSQL are created in schemas that are actually called pg_temp_1, pg_temp_2 and the like (a different one per user), and even though your example suggests that you always see pg_temp, I'd prefer to use the pattern just to be on the safe side.


Is ANALYZE useful immediately after creating an InnoDB index?

CREATE INDEX does not issue ANALYZE TABLE.

When it comes to InnoDB, running ANALYZE TABLE is kind of useless. Why ?

I wrote this post From where does the MySQL Query Optimizer read index statistics? 2.5 years ago.

In it, I explained that InnoDB can overwrite statistics by means of just running queries. The Query Optimizer will do depth searches in BTREE indexes and take guesses at the cardinality of index column combinations. In a relatively short time, I would say that index stats can get stale. This applies to older versions of MySQL 5.

From the ANALYZE TABLE Documentation for MySQL 5.6, InnoDB's stats are persistent by default. Thus, running ANALYZE TABLE on InnoDB tables for MySQL 5.6 actually means statistics are computed and will only deteriorate via heavy INSERTs, UPDATEs, and DELETEs.

As for you question, I say : CREATE INDEX does not issue ANALYZE TABLE automatically.


How would you make your case for RDP access to a Data Warehouse?

I'd do the following:

Make a list of the reasons why you connect to the server's console today.
Identify things that you easily could do another way, and start to work around them.
For the rest, identify what can only be done from the console, and what can be done without the console, but is significantly more difficult.

First, share this list internally - make sure your request to the hosting company won't blindside your own employer.
Then, talk to the hosting company, and ask them for their solutions to these problems. I'd expect three possible answer to each of your items:

contact the hosting company, and they will do it themselves;
hosting company provides a tool that lets you do it without RDP; and
you can't do that.

If the hosting company will do it, get details on turnaround time; it may not be acceptable.

Follow up by:

trying out the tools, to make sure they do what you need.
review the "call the hosting co" and "you can't do that" items; identify the impact of each; and review with your company.

The issue could resolve at many points. Your review may convince you that you don't absolutely require RDP. Your conversation with the hosting company may give you satisfactory answer (no "you can't do", and SLAs on "call us" are acceptable. Alternately, your concerns may convince the hosting company you do need RDP.

Ultimately, if you can show RDP access is absolutely necessary, and the hosting company won't provide it on request, then your company may need to be ready to walk away unless the hosting company relents. That requires sign in from your company, which is why it's important to involve them. You don't have to say you're heading for such a confrontation (even if you believe you are). You have to sound reasonable to your own people.


The documentation that I can find on this procedure is somewhat incomplete, especially with regards to the meaning of the input parameters.

The following are some guesses.

Looking at the output messages in your question and examining the actual stored procedure code, my suspicion is that the database you are attempting the restore already exists.  The stored procedure has a parameter called @ExistingDBAction which seems to control what the procedure is supposed to do if the database already exists.  The default value of this parameter is zero (0), which means do nothing.  You can actually see that in your output.

@ExistingDBAction 0 so do nothing

So, the procedure attempts to restore the backup to an existing database and receives

 Msg 3153, Level 16, State 2, Line 1
 The database is already fully recovered.
 Msg 3013, Level 16, State 1, Line 1
 RESTORE DATABASE is terminating abnormally.
 Outcome: Failed

The procedure apparently accepts values 1, 2 and 3, but the procedure doesn't really have good documentation on exactly what those parameters do.

Option 1 appears to set the existing database to single user mode.
Option 2 tries to kill all existing connections to the existing database
Option 3 tries to drop the existing database.

I'd suggest running the procedure and include the @ExistingDBAction parameter with a value of 3.  This should kill all existing connections to the database and then attempts to drop it.

You could also run your process under SQL Server Management Studio debug.  Stepping through the stored procedure code may give you additional insight regarding the problems you are encountering.

As far as your last question about

  must sp_DatabaseRestore be created on the destination server I want to
  restore in order to work?

That does appear to be the case.

I'm also wondering about the original command in your question.  Specifically, the value you gave for the @Database = 'LOTR_recovery'.  According to the documentation for sp_DatabaseRestore, that parameter should specify the source database.  Is that the actual name of the source database.  If you want to give the restored database a different name from the source database, you should include the parameter @RestoreDatabaseName with a value of the restored database name.

These additional points may or may not be of interest.  I just wanted to make sure you were aware of the parameters and their meanings.


Have a cluster with nodes A,B,C, but not D.
Have D be a Slave to one of the nodes.  Do not configure it as a wsrep node.  Do not write to it.
More
A cluster with 3 or more nodes provides HA and failover if any one node goes down.
If those 3 nodes are in the same datacenter, then they are vulnerable to a disaster that takes down the DC.  (That can happen.)
If those 3 nodes are in 3 geographical locations, the cluster can survive the lost of any one of them.  Hence, it is better than 3 in a single DC.
When something happens with a cluster, all the alive nodes vote to see what the situation is.  If more than 50% can talk to each other, they will declare that they are the (degraded) cluster, and that the other node(s) will need repairing when (if) they reconnect.
If you have 3 nodes in one DC and a 4th in another DC, the cluster will not survive an outage of either DC.
If those two DCs lose connectivity, but everything other than the network stays up, the DC with 3 nodes won't be able to see the 4th; they will kick it out of the cluster.  The lone #4 will see that it can't get 50%+, so it stops accepting writes (at least).
Back to your question...
A 3-node cluster in one DC; a 4th node in another DC.  If the 3-node DC goes dark, you could manually build a new cluster around the 4th.  But that is far from automated.  You could, however, say that a DC going down (and not coming back up 'soon') is so rare that you are willing to build this topology.  And, if the DC never comes back up (tornado, flood, earthquake, tsunami), you would have a copy of the data in #4.  Better than nothing.
So, if you go with 3+1, I recommend that the 3 is a fully functioning Galera cluster.  (Or it could even be 2 live + 1 garbd.)  Then #4 would be an ordinary replica that is asynchronously replicating from one of the 3 nodes.
With that config, Galera does not know or care about #4, and is not burdened by long-distance syncing of gcache.
It's a tradeoff between vulnerability and speed.  You can't solve both.  Hopefully, I have given you enough understanding of what can be done so that you can decide what "keeps you up at night" the least.


Cross Database Chaining vs. Trustworthy Option

Option 1 is the worst of all.. would not use that..

Option 2 a lot of administrative effort..

Option 3 (see last sentece :) )

Option 4 recently I had to think about how to secure databases.. My solution was this one. there is great article on security here. Even thought it was a bit complicated to use Certificates at beginning (never done with them anything before) I am very happy of this choice. I grant permissions to Certificate Login/User. I sign procedures by that certificate and everything works. I use the same system to grant permissions for dynamic SQL (so if there is sp_executesql I grant minimum rights to necessary tables to Certificate User, so caller of proc can not see tables and i do not have to make Execute as Owner or smthng). And the same to grant rights to use Asymmetric Keys. And I always know WHO was the caller of procedure (no Execute As), even though its not very important in my case..

Its a bit cumbersome to sign procedures during development time (trying to find good way around that). Currently I am searching procedure text (DDL trigger) for use of other database and if I find it, I print out warning, that procedure possibly has to be signed..

Another thing I miss is that its not possible to sign functions or views (there must be a good reason) so you cant use them to access other DB..

Last thing I would like to say- I would prefer to solve as many problems I possibly can with minimum variety of patterns.. So if you extensively using "Execute As" and proxy users then maybe Trustworthy option is better choice.


Why do some write sometimes go faster than expected for MEDIUMTEXT

I suspect the answer goes something like this...

Approach 1:  800KB takes a lot of disk activity to write.  This takes perhaps 1000ms.  When you "wrote" a row in 31ms, that was because of most of the I/O being delayed until after acknowledging the action.  When it took 718ms, the disk was busy writing other things, so it was slow at accepting the current row.

Approach 2:  A rule of thumb for I/O with a HDD is 100/second.  800KB = 50 * 16KB blocks to write (plus some overhead).  That would compute out to 500ms per row.  So 718ms sounds a little high, and 31ms sounds like a lot of caching going on.

So the question should be "Why does this write sometimes go faster than expected?


Can I have a sql 2017 read-scale AG on different Windows server OSs; i.e. Windows server 2016/2019?

Availability Groups are built upon Windows Failover Cluster.  As such, it won't (probably?) be a supported scenario to run long term with different OS versions in the same cluster.  Assuming it lets you set it up in the first place.
You might ask in System Administration if it's possible to setup a windows cluster between a OS 2016 and 2019.  If you can and if it's supported, then SQL won't have any issues.
You could do a distributed Availability Group as that is not built upon Windows Failover Cluster, but you lose a lot of advantages (no listener, to start with).


Is it possible to setup always on QL Server 2016/2017 without windows clustering and domain? My intention of implementing always-on and secondary replica is offloading the select query from the primary database server.

Yes, it is possible but will require SQL Server 2017 (I'd also apply the latest CU, which at time of posting is CU4).

Please note that without a domain, SQL Authentication is the only available authentication type and there is also the additional endpoint setup of using certificates.

If you'd like to read more about read-scale availability groups (Not for HA or DR) the Docs articles are a good start. I've also blogged about it when it comes to things like read only routing.

  I'm not expert at windows clustering [...]

You really don't have to be since you're not looking for HA or DR. That's my opinion, though, and won't translate well everywhere depending on the resources and familiarity with Windows Server.


How to rollback a SQL Server transaction and use output param?

A more robust way to write this stored procedure would be:

CREATE PROCEDURE dbo.sp
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT 1/0; -- An error!

        /* Other good code omitted*/

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        DECLARE @Message nvarchar(2048) = ERROR_MESSAGE();
        DECLARE @Severity integer = ERROR_SEVERITY();
        DECLARE @State integer = ERROR_STATE();

        RAISERROR(@Message, @Severity, @State);
        RETURN -1;
    END CATCH;
END;

Note the use of XACT_ABORT and nothing of consequence before the BEGIN TRY. Also RAISERROR is often preferred over THROW for reasons covered in the reference below, but importantly:

  In difference to RAISERROR, ;THROW always aborts the batch.

Aborting the batch results in any output parameters not being assigned.

Error handling in SQL Server is quirky in the extreme, so I would encourage you to review the seminal work on the topic by Erland Sommarskog: Error and Transaction Handling in SQL Server.


How do I get it to do a seek?

Given the predicate on the table you have shared with us:

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]

It seems likely you are using an OPTION (RECOMPILE) hint on the query in a version of SQL Server that does not support the Parameter Embedding Optimization (PEO) fully. This requires at least SQL Server 2008 SP1 CU5.

Without that, the recompile hint is not an effective way for dynamic search queries to seek on an index. This type of query is characterized by expressions like:

column = @variable OR @variable IS NULL
column = COALESCE(@variable, column)

If you are not using one of those constructions, it would be something broadly similar. If full parameter embedding were available, the predicate you have would be simplified to:

'SGD' = [UserTbl].[Name]

...which could use a nonclustered index with [Name] as the leading key to perform a seek.

You need to do one or more of the following:

Upgrade to a more recent version of SQL Server (adding an OPTION (RECOMPILE) hint if not already present)
Rewrite the query, perhaps using dynamic SQL, so that the expression is seekable using an index with Name as the leading key
Ensure Name is the leading column in the index

Further reading:

Parameter Sniffing, Embedding, and the Recompile Options
Dynamic Search Conditions in T-SQL


What will happen with MySQL transaction if one of the queries fails?

Deadlock or lock wait timeout == implicit transaction rollback.

All another errors will rollback only the statement, not whole transaction.

See InnoDB Error Handling in the documentation.


Oracle: Create a hidden user for RMAN backups only

In order to do RMAN backups you need the SYSDBA or SYSOPER roles. In order to grant those you need to be able log on as a user with those roles: ie the SYS role.

There are variations where you use OS authentication or if your OS user is a member of the ORA_DBA group.

Easiest is if you can do this:

CONNECT / AS SYSDBA

from the server then you can run an RMAN script like

RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

This very basic script should be configured with details particular to your setup such as the location to backup the files to, crosscheck existing backups, etc.


rsExecutionNotFound when directly accessing an SSRS report

What I have seen in a case like you ask about is you hit a report, and copy the url out and then try to hit it later. Reports have session state, especially if you change paramaters,etc. The url will look like a report url with some funky querystring data added to the end. You can't share that because SSRS can't take your "session state" and then find it later.


A table with all fields NULLable with important, sensitive data

I wouldn't be too concerned about 25 columns.  That's not an extremely high number.  It doesn't even approach violating Swart's Ten Percent Rule (102 columns).  Of course, the "rule" there is discussing a limit, not a goal, so keep that in mind

In my mind, ideally, each of these separate "forms" (tabs) would be backed by its own table (AlcoholAbuse, DrugAbuse, Injections, etc), which would then be linked back to the patient by a foreign key constraint.

However, you mentioned that there are data validation concerns that cross forms.  Maybe if you answer "yes" to certain questions on the drugs form, then certain questions on the injections form are required.

Since CHECK constraints are really the best way to enforce what you're describing, that would be mean they all need to be in the same table.

Your other options, if things were in separate tables, would be to move validation into the application layer, or use triggers to ensure the data in the different form tables is valid on each insert / update.

Since the number of columns isn't outrageous, and you probably don't want to incur the complexity of dealing with triggers, I think one table with conditional CHECK constraints is a valid solution.

This solution seems maintainable to me, and fairly easy to reason about, since all of the "rules" are in one place (one table, and the constraints applied to that one table).


Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

Check the connection for being character set utf8
Check the application for whether it is using UTF-8

Meanwhile, that looks like an awfully slow query -- lots of ORs and leading wildcard on LIKEs.


How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express

You have a 32-bit instance of SQL Server 2005 and are trying to upgrade to a 64-bit instance of a newer version. This is not supported; you can check any of the upgrade documents about this, e.g. http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx:

  Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit.

It says stuff about WOW64 but my recommendation for your scenario is to install a separate 2008 R2 instance, backup/restore your databases from the 2005 instance (and transfer jobs/logins), then remove that instance. You will need to choose a different instance name or use a default instance.


Is it safe to stop the shrinking of MSDB?

Yes, it is safe to cancel that operation (but you need to be patient and let it finish rolling back - do not panic and stop the SQL Server service or reboot the server; all that will do is make the rollback start over).

While you're waiting, let's address your underlying problem.

Outside of a scenario where you manually increase the size to prepare for more data (or just get dibs on space before someone else takes it), data files increase in size because you added data to the file, and it wasn't already big enough to store that data. Telling SQL Server to shrink a file that had to grow to accommodate more data is unlikely to accomplish anything unless you remove some of the data that caused it to grow in the first place.

This could be from a rogue backup job - maybe 3rd party - that is running every second and filling the backup history tables. Or a job run amok that is generating job history and filling that table with errors. Or an infinite loop that is filling database mail tables with junk. Or dozens of other things, especially if you've put user tables for your own processes into msdb.

To find out what is taking up the space, you can use a query like this and another like this (many others exist). It could be that the tables are massive due to fragmentation, or lots of LOB data, or really bad fill factor, or oversized but underpopulated fixed-width columns, or just sheer number of rows. But you're going to have to find this and clear it up before shrinking has any hope of accomplishing anything.

Remove the data you no longer need (and take whatever steps necessary to ensure that whatever process did that is not going to do it again). Rebuild the indexes with a proper fill factor, and set up some kind of maintenance routine (like Ola's scripts) to manage indexes, and possibly an alert on too many file growth events for the msdb database (or all databases), so you can catch this problem earlier. You can see all file growth events that happened recently, for example, using the default trace.

Then, use DBCC SHRINKFILE (not some UI hinkiness in SSMS, or DBCC SHRINKDATABASE, which will also try to shrink the log, and will try to shrink everything at once), a little bit at a time, to bring the file size back down. If the file is currently 450 GB, and you free up 300 GB (you can use sp_spaceused to see the result of the above), I would get to a 200 GB file, let's say (leaving room for future growth), by first shrinking to 425 GB, then 400 GB, then 375 GB, and so on... you may need to do that in smaller chunks, say starting with 2 GB or 5 GB at a time, depending on the capabilities of the underlying disk subsystem.


Pull more records based on IDs in comma separated list returned by GROUP_CONCAT

I think you could use the following query to get the data you need:

SELECT
  users.username,
  title, content
FROM articles
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id , title
ORDER BY  articles.author_id ,articles.published DESC;

The presentation of it depends on the programming language you will use to display the results.


Recommendations to prevent failover (scheduled)

The AG usually fails over when the secondary and witness can't talk to the primary for a length of time. (I'm simplifying that a little, but for more technical details, check out MS's documentation on cluster quorum.)

To fix this, I'm going to suggest a few different things. You can do one, two, or all of 'em - just start with the things that are easiest for you, and add more as necessary.

First, leave some breathing room between index rebuilds. If you're using the built-in maintenance plans to rebuild indexes, consider switching to Ola Hallengren's maintenance scripts instead. (They do backups, checkdb, and index rebuilds, but for now, let's only focus on your index rebuilds.) They operate via a stored procedure, and one of the parameters for the IndexOptimize stored proc is Delay. By default, there's no delay between index rebuilds, but you would want to add a 5-10 second delay. This will give your IO subsystem and network some time to clear the air.

That by itself may not be enough, so keep reading.

Next, consider tweaking the default AG timeouts. In sections 7-8 of this Microsoft article, they explain how the default timeouts may be too quick for low-quality network connections. In the comments, you also noted that you're hitting lease timeouts, so check out these additional resources:

Microsoft support post: How It Works: SQL Server Always On Lease Timeout - in which they write, "If the lease timeout is expiring you likely have a system wide, impacting event taking place that needs to be resolved.  If you are experiencing lease timeouts troubleshoot the entire computer you are likely encountering a broader issue than just a SQL Server lease timeout." My money's on this - since you're seeing it during index rebuilds, I'm guessing you have an issue that makes SQL Server unresponsive during that time. For community help with that, follow the instructions in Getting Help with a Slow SQL Server, especially the last section around including sp_Blitz's results in Markdown format in your question.
Improvements in 2012/2014 patches for lease timeouts - KB #3112363 adds better diagnostic data for lease timeouts in 2012 SP3 and 2014 SP2. This is only going to give you more troubleshooting data - it's not going to fix the timeouts. (The link I posted above is more about fixing the timeouts long term by fixing the underlying performance problem.)

Then, consider adding network capacity. I'm guessing your servers have just one 1Gb Ethernet port each, and it's getting saturated during your index rebuilds. You could consider upgrading to teamed cards with load balancing, or to 10Gb cards, or to separate heartbeat network cards. I'm not usually a big fan of separate heartbeat networks because they increase complexity, but that might be the cheapest/easiest solution here. (Start with teaming first though.)

The above steps will let you keep your automatic failover AND your index rebuilds. However, I'd be a bad database guy if I didn't suggest that maybe rebuilding every index, every night, is a little bit overboard. Not only is it causing your AG problems, but it's also inflating your tranaction logs, causing those backups to take longer too. For more on that, watch my GroupBy session, Why Defragmenting Your Indexes Isn't Helping.


Fast query for 'between' with overlapping ranges

Your current code is O(N), even with the index, where N=700K.

Solution.  That blog explains how to restructure the table of IP addresses to have only one column (start, no end), and provides code for searching/inserting, etc., efficiently.  This technique is O(logN)* which is a vast improvement compared to O(N).

It involves ORDER BY + LIMIT to get the efficiency.  If you have gaps in IP ranges, then extra rows are needed to indicate "unowned".  Routines take care of properly inserting/removing these rows when you insert new entries.

*: In real world scenarios, to compare performance, we can ignore non-leaf nodes because (in a production system) they are almost always fully cached. Then, assuming the leaf node is not cached, the query only needs to do 1 disk hit. The OP's code is comparison would (estimated) need hundreds of disk hits.


select  industry_id
,       sum(case when current_date <= date then clicks end) as today
,       sum(case when current_date-1 <= date and
                      date < current_date then clicks end) as yesterday
,       sum(case when current_date-4 <= date and
                      date < current_date-1 then clicks end) as last3days
from    phone_clicks
group by
        industry_id

See it in your SQLFiddle.


Multiple Replication Type Publications on Single DB

The following article appears to answer this question:

https://technet.microsoft.com/en-us/library/ms172911%28v=sql.105%29.aspx

Yes, you can run multiple publications from the same Db as long as there are no intersecting articles.


SQL Server 2014 - Replica Server not available

Instead of giving the name of the other node, I gave the IP address of the other node. Then everything went well.
BUT what the reason was that it did not accept the name, I still do not know.


How can I create a TRIGGER in MySQL from this Oracle syntax?

Check the MySQL documentation about the trigger syntax and examples.

You cannot have AFTER INSERT OR UPDATE triggers in MySQL. Define 2 triggers, one for INSERT and one for UPDATE. Another limitation is that AFTER triggers cannot change the updated rows, so you can use BEFORE triggers. Like this:

delimiter //
DROP TRIGGER IF EXISTS TRG_PRODUCT_REORDER_BEFORE_INSERT ;
CREATE TRIGGER TRG_PRODUCT_REORDER_BEFORE_INSERT
BEFORE INSERT ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < NEW.P_MIN THEN
            SET NEW.P_REORDER = 1;
        END IF;
    END; //
delimiter ;

and similarly for the BEFORE UPDATE trigger


Oracle ASMM - Are specific values ignored?

These are minimum values for those components.

http://docs.oracle.com/cd/E18283_01/server.112/e16638/memory.htm#i58728

"If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly."


What are the pros and cons of using Postgres over SQLite for tiny and small projects?

The main differences will be the features you need, for the applications you'll be developing and their use cases. It sounds like if your PostgreSQL instances are only consuming up to 200 MBs of memory at a time, you already have tiny databases, and it doesn't really matter too much if you choose one way or the other due to data size, rather it'll be more feature specific which as you realize SQLite is a much simpler database lacking a lot of addition features a full RDBMS like PostgreSQL has.
Personally, I'd say unless the environment you're working in / hosting your data on is constrained to use SQLite like mobile development (because of its portability for offline storage) I'd opt for a full RDBMS system by default.


Looks like, when the alternate server is configured, the CLI driver prefers its proper hostname over "localhost". If you specify the machine hostname or IP address in the connection string instead of "localhost" the script works as expected.

The test script can be modified as:

[...]
import traceback
import socket
[...]

def f1(dbname, username, pwd):
    print "start"
    ip = socket.gethostbyname(socket.gethostname())
    cfg = (dbname, ip, username, pwd)
    conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=50000;PROTOCOL=TCPIP;UID=%s;PWD=%s" % cfg
    [...]

It is recommended to explicitly bind Db2 to an IP address via nicbinding.cfg, in which case "localhost" wouldn't work anyway.


DBeaver's Auto Increment field max is 2147483647 - why?

2 to the power of 32 is 4294967296.

If you divide that by 2, you get:

(2^32/2) = 2147483648

This second value minus 1 is 2147483647 = 2^31 - 1, i.e. your value of interest.

Mathematically, it's the 8th Mersenne prime and is also equal to the hexadecimal (base 16) value 7FFF,FFFF. It's also the largest known double Mersenne prime and (probably) the biggest of that class of number!

In databases and computing, it's the maximum size of a 32 bit SIGNED INTEGER. The signed/unsigned INTEGER type is one of the few areas in which MySQL is superior to PostgreSQL. I think that unsigned INTEGERs have many potential uses, but then again, PostgreSQL has CHECK CONSTARAINTs which mean that it's not obligatory to enforce positive integers as a type. Still (personal opinion), I feel it's a good type to have, if only for brevity and clarity!

Previously (in many 32 bit systems), it was the maximum number (i.e. 2147483647) of records allowed in a table and/or the maximum that an AUTO_INCREMENTing (i.e. SERIAL or SEQUENCE) integer field could reach - but this is not now normally the case with 64 bit machines.

When this number was reached, either for the number of records, or for a key of some sort, then the behaviour of many database systems was/is either undocumented or erratic or both. The value could start again from zero (possibly breaching integrity constraints) or start at an arbitrary value or cause the system to crash completely!

On the 19th of January 2038 (at 03:14:07 UTC), there will be more than that many (2147483647) seconds elapsed since Jan 01, 1970, i.e. the beginning of time as far as Unix is concerned. Aircraft will drop out of the sky, nuclear missiles will be launched without provocation and there will generally be a great wailing and gnashing of teeth - unless, of course, you're a progrmamer who can make a mint out of this (just like the COBOLers in 2000!).

  The Year 2038 problem relates to representing time in many digital
  systems as the number of seconds passed since 1 January 1970 and
  storing it as a signed 32-bit binary integer. Such implementations
  cannot encode times after 03:14:07 UTC on 19 January 2038. Just like
  the Y2K problem, the Year 2038 problem is caused by insufficient
  capacity of the chosen storage unit.

Best to avoid completely and use 64 bit these days!


How can I disable database lock in sqlite with python?

Using embedded sqlite is good for developing and testing, and I'm assuming you are using db like this:

db = sqlite3.connect('data/mydb')

In this case you could use several db like mydb1, mydb2 and so on. It would prevent the lockdown but overall it's not the solution.

I would suggest using several tables in single database instance via any RDS service you find appropriate. For example this one

Another solution could be moving from relational database to NoSQL solution. It would completely prevent the lockdown situation and it's highly scalable and affordable when data exchange volumes are relatively low.


Is Memory Grant size 'cached' for subsequent executions of a cached plan?

The team lead is correct in the statement about memory grants given the version of SQL Server you're using (SQL Server 2014, based on the tags here).  They are part of the cached execution plan, and the values are re-used across executions as long as the cached plan is in place.

Improvements have been made in this area on newer versions of SQL Server:

SQL Server 2017 introduced batch mode memory grant feedback: SQL Server 2017: How does Batch Mode Memory Grant Feedback Work?
SQL Server 2019 has introduced row mode memory grant feedback: Row mode memory grant feedback

These improvements allow the cached plan to be updated based on runtime feedback (how much of the memory grant was used on the previous execution).

I can't find a specific source in the documentation that confirms memory grants are reused as part of the cached plan, but hopefully it's implied well enough from the memory grant feedback improvements that have been introduced in newer versions.


What frequency of hash/sort spills into tempdb is concerning?

Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency?

Yes it is possible, though typically it is the average size of the spills, and how deep they go (i.e. recursive hash spills, multi-pass sorts) that matters more than the frequency per se.

SQL Server provides a wide range of metrics and DMV information to help you troubleshoot the various contributing factors to tempdb pressure, many of which are discussed in the Microsoft Technical Article, "Working with tempdb in SQL Server 2005" (applies to all versions 2005 onward).

You should be able to use the guidance and diagnostic queries contained in that document to start identifying the primary causes of any tempdb pressure. Do not disregard e.g. version store activity simply because ALLOW_SNAPSHOT_ISOLATION is not enabled. Many features use the version store (e.g. triggers, MARS, RCSI) aside from snapshot isolation.

If sort and hash spills do turn out to be significant at a high level, you will probably need to set up some specific monitoring for this. Depending a little on your SQL Server version, this is not always a straightforward as one might hope. To connect sort and hash spills with the particular query that caused them requires Event Notifications or Extended Events. The SolidQ article, "Identifying and Solving Sort Warnings" contains details and some good general advice about resolving common causes.

You should also work with your storage team to determine how much of the high latency is attributable to your workload, how much comes from other shared uses, and what options there are for reconfiguration. Your analysis of SQL Server's metrics will help inform this discussion, as will any metrics the SAN people are able to provide.


What's wrong with this update RANK query?

I created a random sample of 30 rows based on the table above as follows:

DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;

Here is that output

mysql> DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;
Query OK, 1 row affected (0.04 sec)

mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

mysql> USE sample
Database changed
mysql> CREATE TABLE users
    -> (id int not null auto_increment,
    -> users_tbl_points int not null,
    -> users_tbl_rank int not null default 0,
    -> primary key (id),key (users_tbl_points));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO users (users_tbl_points) VALUES
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
Query OK, 30 rows affected (0.08 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 30 rows affected (0.05 sec)
Rows matched: 30  Changed: 30  Warnings: 0

mysql> SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 27 |           974504 |              1 |
|  3 |           942712 |              2 |
| 26 |           914442 |              3 |
| 23 |           867887 |              4 |
| 13 |           828882 |              5 |
| 12 |           809094 |              6 |
| 29 |           722458 |              7 |
| 14 |           717128 |              8 |
|  4 |           702457 |              9 |
|  1 |           691932 |             10 |
|  5 |           684147 |             11 |
|  8 |           631802 |             12 |
|  9 |           615874 |             13 |
|  7 |           514378 |             14 |
| 17 |           420973 |             15 |
| 24 |           360579 |             16 |
| 16 |           343592 |             17 |
| 22 |           326285 |             18 |
| 20 |           315427 |             19 |
|  6 |           313363 |             20 |
| 21 |           254513 |             21 |
| 30 |           224710 |             22 |
| 25 |           199236 |             23 |
| 10 |           183963 |             24 |
| 28 |           129194 |             25 |
| 19 |           107540 |             26 |
| 15 |            98995 |             27 |
| 18 |            74092 |             28 |
| 11 |            72197 |             29 |
|  2 |             3702 |             30 |
+----+------------------+----------------+
30 rows in set (0.01 sec)

mysql>

Let me update the scores

mysql> UPDATE users SET users_tbl_points = FLOOR(rand()*1000000);
Query OK, 30 rows affected (0.08 sec)
Rows matched: 30  Changed: 30  Warnings: 0

Let me rerank them

mysql> SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 29 rows affected (0.06 sec)
Rows matched: 30  Changed: 29  Warnings: 0

Let me display them:

mysql> SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 28 |           994943 |              1 |
|  7 |           983596 |              2 |
|  4 |           964909 |              3 |
| 24 |           921117 |              4 |
| 14 |           885399 |              5 |
|  8 |           881325 |              6 |
|  3 |           858830 |              7 |
| 11 |           808514 |              8 |
|  1 |           785523 |              9 |
| 26 |           751842 |             10 |
| 22 |           749931 |             11 |
| 21 |           705158 |             12 |
| 25 |           703038 |             13 |
| 27 |           650093 |             14 |
| 15 |           649229 |             15 |
| 10 |           635204 |             16 |
| 23 |           634182 |             17 |
| 16 |           589948 |             18 |
|  9 |           455836 |             19 |
|  2 |           443080 |             20 |
|  6 |           345553 |             21 |
| 13 |           259255 |             22 |
| 20 |           258620 |             23 |
|  5 |           248056 |             24 |
| 18 |           240429 |             25 |
| 19 |           195981 |             26 |
| 30 |           137355 |             27 |
| 12 |           136960 |             28 |
| 29 |            24437 |             29 |
| 17 |             2055 |             30 |
+----+------------------+----------------+
30 rows in set (0.00 sec)

mysql>

Looks like it works to me !!!

CAVEAT

Make sure you have an index on the users_tbl_points column


ERROR: invalid XML content, when Select unnest(xpath(... on a text column

Basically double-quote all attribute values.

You can read about it in this tutorial.

I used an on-line xml validator and this is the result:

  The value following "version" in the XML declaration must be a quoted string.

Change:

<?xml version=1.0 encoding=UTF-8?>

by

<?xml version="1.0" encoding="UTF-8"?>

  Open quote is expected for attribute "xmlns:dpid" associated with an element type "dpid:DpidDatabase".

Change:

<dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation=http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd>

by

<dpid:DpidDatabase xmlns:dpid="http://ddex.net/xml/dpid/11" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd">

  Open quote is expected for attribute "SequenceNumber" associated with an element type "DpidOwner".

Change all occurrences of:

<DpidOwner SequenceNumber=XX>

by double quoting SequenceNumber values

<DpidOwner SequenceNumber="1">

You can check the result db<>fiddle here

sequencenumber                                                                                | dpid                            | companyname                                     | address
:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------ | :---------------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------
<DpidOwner SequenceNumber="1">
  <DPID>PADPIDA2006111001O</DPID>
  <CompanyName>234AG</CompanyName>
  <Address>Riedtlistrasse 23, ZÃƒ????????Ãƒ???????Ãƒ??????Ãƒ?????Ãƒ????Ãƒ???Ãƒ??Ãƒ?Ã‚Â¼rich, 8006, CH</Address>
 </DpidOwner> | <DPID>PADPIDA2006111001O</DPID> | <CompanyName>234AG</CompanyName>                | <Address>Riedtlistrasse 23, ZÃƒ????????Ãƒ???????Ãƒ??????Ãƒ?????Ãƒ????Ãƒ???Ãƒ??Ãƒ?Ã‚Â¼rich, 8006, CH</Address>
<DpidOwner SequenceNumber="2">
  <DPID>PADPIDA2007011501Q</DPID>
  <CompanyName>OpenIMP</CompanyName>
  <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
 </DpidOwner>                                                                         | <DPID>PADPIDA2007011501Q</DPID> | <CompanyName>OpenIMP</CompanyName>              | <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
<DpidOwner SequenceNumber="3">
  <DPID>PADPIDA2007040501K</DPID>
  <CompanyName>The Harry Fox Agency</CompanyName>
  <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>
 </DpidOwner>                                                | <DPID>PADPIDA2007040501K</DPID> | <CompanyName>The Harry Fox Agency</CompanyName> | <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>


Can I use Default Accounts for setting up AOAGs 2014\2016

Can I use the default accounts which SQL Service provides by default

Yes you can use that. From Prerequisites and Recommendation for AG

  if the SQL Server service is running as a built-in account, such as Local
  System, Local Service, or Network Service, or a nondomain account, you
  must use certificates for endpoint authentication, and the wizard will
  be unable to create a database mirroring endpoint on the server
  instance. In this case, we recommend that you create the database
  mirroring endpoints manually before you launch the wizard.

The advantage with using domain account is that they are more secured and when you configure availability groups using SSMS GUI the endpoints are created by the SSMS GUI and also granted the connect permission(. As you can note from above that when using built in account you need to create endpoints using certificates manually and grant connect permission.

You also have option of running SQL Server services with different domain account, if you do so you just need to make sure both the logins are created on remote machine in master database.

More over before changing I would suggest you to read Troubleshooting Always ON Configuration( see the accounts section)


Materialized View does not import properly when importing a db for a second time in another schema

This is happening because it's trying to create a duplicate refresh DBMS_JOB with the ID 438.

Just re-run the failing statements manually and specify an unused job ID.

SELECT MAX(JOB)+1 FROM DBA_JOBS;

... will get you the next available ID.

(You may be able to omit the JOB=> parameter and have Oracle auto-generate the ID, but I can't remember off-hand if that'll work).


MySQL right join with tables with 100's of 1000's of rows

Make new columns to handle those clumsy expressions.
Make them reasonably sized VARCHARs, not LONGTEXT.
Write a 1-time script to populate those column(s).
Change code so that future INSERTs (etc) maintain the columns.
Add a suitable index so that the JOIN works efficiently.
Use JOIN unless you really need RIGHT JOIN.  Are you currently hoping to get a lot of NULL columns.


This isn't really a reliable queue pattern

You have a long running transaction that depends on the client processsing time
A client time out won't release locks because connection pooling and you don't have SET XACT_ABORT

Typically, you'd set a row as "In Processing" with an UPDATE using ROWLOCK, HOLDLOCK, READPAST hints. This allows multiple clients to not block each other and you can

Then, when the client has done it's stuff, you can DELETE the row in a separate transaction. Or, flag "Has Error" on the same row as needed.

See these for more:

https://stackoverflow.com/a/940001/27535 (SO)
FIFO queue table for multiple workers in SQL Server


The indexes are similar but not identical. The order of the columns in an index matters - a lot - and affects what queries might benefit from them.

We don't know your queries but for example a query like the following would benefit most from index A:

SELECT TOP (3) ChannelSKUID, Checked, SalesRank
FROM dbo.SalesRankCheck
WHERE ChannelSKUID = 'XXX'
ORDER BY Checked ;

while this one would benefit from index B:

SELECT TOP (3) ChannelSKUID, Checked, SalesRank
FROM dbo.SalesRankCheck
WHERE SalesRank = 1
ORDER BY Checked ;

If only one of the two indexes were available, the query plans might still use it but the plan would not be optimal, due to the difference in column order in the index and the mismatch with the optimal column order needed for the query. The index would still be "covering" the query but the plan would probably be a full index scan instead of an index seek and would have additional (sort) operations as the rows provided by the index would not be in the order the query wants.

For the question now:

  Am I right in thinking that this isn't a sensible way of having it organised and it would make more sense to have a single index with all 3 columns indexed or does that depend on the way the database is being used?

No, it is a sensible way and I wouldn't expect a single index to cover queries that might choose either index otherwise.

But it depends on what queries run against the table. Any analysis should be based on the workload you have and the performance you expect. Analyze the queries (all of them or the ones most often run) and what indexes they use and what performance you get, before you decide dropping or adding an index.

Run tests in test installations with different indexing, if performance is not good. Or experiment (say with dropping an index), if you think that the index is not used.

For a quick and dirty advice, if the number of indexes on the table is small and the performance is good, don't bother changing anything. The extra load that indexes impose on write operations is usually small, unless you have a table with 10+ indexes or a table that is under extreme write load (thousands of writes per second).


No, it means connections with applicationintent:readonly will be routed to the readable replica. If one of them tries to modify data it will get an error message. Something like below:

  Msg 3906, Level 16, State 2, Line 2

  Failed to update database "XXX" because the database is
  read-only.

Note: if the secondary readable replica is not available the connection will be able to be established to the primary one depending on Connections in primary Role option.


Preferred database solution for storing sensor data

In general you do not want to normalize the data you are stuffing into whichever NoSQL solution you are using, in other words, you want to leave the redundant information there. Otherwise you should be using a relational database system like MySQL,Postgres,SQLite, or MSSQL (Express edition is free, but has database size limits). If you use any of those systems, reducing redundant information would be beneficial.

Honestly, if you are looking to grab a bunch of sensor data, then display it as a graph at a later point, you might want to look into setting up a box with Elasticsearch + Kibana on it. You can get something up and running in a matter of minutes with VERY minimal amounts of configuration: https://www.elastic.co/products/kibana

When Neo4j calls itself a "graphing database" it is not graphs in the visual sense of the word, it is graphs in the relationship sense: https://en.wikipedia.org/wiki/Graph_database

Hope some of this helps.


Postgres latency issues during memory compaction on EC2 (Ubuntu 14.04)

As dezso mentioned in the question comments, this did seem to be an issue with (possibly more recent versions of the) 3.13 kernel in Ubuntu Trusty - we switched to the Xenial HWE 4.4 kernel in Trusty and the problem seems to have gone away and compaction stalls are now very small and don't interfere.


How to find out the models and prices for all the products (of any type) produced by maker B

In a UNION/UNION ALL query, a WHERE clause filters a single sub-SELECT (the one immediately preceding it) rather than the entire UNIONed set – thus, each sub-select can have its own WHERE clause. (This is unlike e.g. ORDER BY, which you would be allowed to specify only once and it would apply to the combined set.)

So, if you want products by the same maker B from each of the three category tables, you need to repeat the filter for each sub-select:

SELECT PC.model, price FROM PC
INNER JOIN Product ON PC.model = Product.model
WHERE Product.maker='B'
UNION
SELECT Laptop.model, price FROM Laptop
INNER JOIN Product ON Laptop.model = Product.model
WHERE Product.maker='B'
UNION
SELECT printer.model, price FROM printer
INNER JOIN Product ON printer.model = Product.model
WHERE Product.maker='B'

That may look like redundant coding, which it probably is, and there are ways to eliminate the redundancy in this case – for instance by using a derived table:

SELECT
  derived.model, derived.price
FROM
  (
    SELECT model, price FROM PC
    UNION
    SELECT model, price FROM Laptop
    UNION
    SELECT model, price FROM printer
  ) AS derived
  INNER JOIN Product ON derived.model = Product.model
WHERE
  Product.maker='B'
;

But in SQL, the DRY principle does not apply as often as in other languages, meaning that repetition is not always bad in SQL and eliminating it is not always good. In theory, the two queries above should be equivalent. In practice, however, not every query optimiser might recognise that, and the second query might result in a less efficient execution plan.

In short, repeating a condition several times across a single query is not uncommon in SQL. Sometimes repetition becomes too much and, to help maintainability of your queries, you may want to employ views or stored routines. But in such cases, too, revisiting your schema often has an even better impact.


I have imported SCOTT schema using following script

C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

Then I reset the password for this user using

alter user scott identified by tiger;

Now, I was able to login to scott and able to use existing table like EMP, DEPT etc..


Getting Information on active Restore in TSQL / SQL Server Management Studio

To see the restore progress, use below t-sql

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

  Is there any other system-table where I can find out who started the restore and when?

You can find out the restore history - especially the user_name will give you who restored the database.

SELECT  [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[user_name],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
--where [rs].[destination_database_name] like '%database_name%' -- OPTIONAL FILTER
ORDER BY [rs].[restore_date] DESC


GRANT EXECUTE ON SomeProc TO SomeRole AS dbo

From BOL:

  Use the AS principal clause to indicate that the principal recorded as
  the grantor of the permission should be a principal other than the
  person executing the statement. For example, presume that user Mary is
  principal_id 12 and user Raul is principal 15. Mary executes GRANT
  SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; Now the
  sys.database_permissions table will indicate that the
  grantor_prinicpal_id was 15 (Raul) even though the statement was
  actually executed by user 13 (Mary).

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql

So in this case the engine is recording the grantor as dbo, rather than the account running the actual code.

I'm unsure why this would be - perhaps due to some aspect of the schema compare, dbo is used to ensure there are no orphaned records in terms of the grantor? Perhaps someone else can shed some light.
It should do this anyway - do you mean in this case the caller is the ExecSP role or the user themselves? Either way this should already be happening, if you've assigned the role or not.


MySQL 5.6 upgrade - reducing code changes / developer burden

I ran headlong into this one 6 months

The only sane thing you can do is the following two steps

STEP 01

Add this to my.cnf under the [mysqld] group header

[mysqld]
sql_mode = ''

STEP 02

Login as root@localhost and run this

mysql> SET GLOBAL sql_mode = '';

Restarting mysql is not required.

STEP 03 (Optional)

STEP 02 only affects incoming connections after the change. You may have to kill all current DB Connections. Let the app reconnect and it will begin with sql_mode as blank. While restarting mysql is not needed for incoming connections, you could just run service mysql restart instead of custom scripting the killing of DB Connections.

If you want to custom script the killing of your connections, please see some of my past posts for exmaples on how to kill many connections:

May 10, 2011 : kill all queries - MySQL
Jun 05, 2013 : Too Many database connections on Amazon RDS
Dec 25, 2013 : Mysql. Block/disconnect slow queries

STEP 04 (Optional, Last Resort)

If you would like to make code changes, the only change would be to run

SET sql_mode = '';

as the first command for the connection. Then, you could run your regular queries thereafter.

GIVE IT A TRY !!!

NOTE #1 : The first two steps are more than enough for your app if you close your connection right after running your query. If your connections are persistent, then you will need STEP 03.

NOTE #2 : If you are not allowed to change the configuration, you could skip STEP 01 and STEP 02 and just run STEP 03 and STEP 04.


How does one create a Matrix Report to convert sql columns as rows?

The functionality you are looking for goes under the name UNPIVOT.  I'd suggest you have a good search on that subject and post a new question if you have further, specific question.


I would do something like:

pg_dump -h localhost -W -U postgres --clean -f dbname.dump DBName

The move the file to the new server, create the new database with

createdb DBName

then restore with:

psql -d DBName -f dbname.dump -v ON_ERROR_STOP=1

Remember that if you have tablespaces created on the old DB you will have to manually recreate them in the new DB before importing the dump.


Are there any tools to go from ERD to DB design and vice versa?

Since you're using MySQL, you might want to take a look at MySQL Workbench.

It might not quite display the ERDs you're expecting but it gets the job done.

And it does have an import feature. I have only used it to import SQL files but apparently you can import from existing MySQL databases.

Also, a long time ago I found the following tools; but were not quite useful to me back then (perhaps they've matured by now):

Database Design Tool - DDT
Mogwai ERDesigner


You have at most 1000 rows from the natural join of R1 and R2 because there are two possible cases: either 1) all the values of  R1.C are present in R2.C (i.e. R1.C is a “foreign key” for R2), or, 2) there are values in R1.C not present in R2.C.

In the first case you have exactly 1000 rows in the join, since the for each row of R1 there is exactly one row in R2 with the same value of C, since C is a key of R2, and for a certain value of C there is only one row in it, so you can join a row in R1 with only one row in R2, and the result has exactly 1000 rows.

In the second case you have instead n in rows in the result, with 0 ≤ n ≤ 1000, where n is the number of row in R1 whose value of C is present in R2: in fact, when a row of R1 has a value of C present in R2, you obtain again a single row in the result.

Combining these two possibilities, we can say that the number of rows in R1 ⨝ R2 is a value  n, with 0 ≤ n ≤ 1000.

Now consider the second join. Since you have at most 1000 rows in R1 ⨝ R2, in each of those 1000 rows you can have a value of E which is present or not in table R3. But since E is a primary key in R3, when you join each of those n rows with R3 you can find at maximum one corresponding row in R3. So your result will have a number of rows which will be equal to m, such that m ≤ n.

Putting together these results, we know that for m, the number of rows of  R1 ⨝ R2 ⨝ R2, holds the following: 0 ≤ m ≤ 1000.

Added

(Based on the comment of @ypercubeᵀᴹ).

Note that the result can be easily generalized. If you have a natural join or an inner equijoin between k tables Ri, i = 1, ..., k, and if you can reorder the join as: R1 ⨝ R2  ⨝ ... ⨝ Rk, such that Ri ⨝ Ri+1 is on the primary key (or unique attribute) of Ri+1, then the cardinality of the result will be always less than or equal to the cardinality of R1.


Can connect to SQL Server 2012 via OSQL and SQLCMD but not via SSMS

Can't explain why, but the firewall seems to be a problem on this computer. Turning the firewall off completely blocked all access (can't even ping or connect to network shares using UNC). We enabled the firewall and removed all of the blocking rules and it now works.


Main Considerations When Moving From MS Access Programming to SQL Server

As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include:

Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the table.

Stored Procedures: Somewhat similar to Access macros and those little VBA procedures (Subs) you built in Access to do "useful stuff", except that in SQL Server they are part of the database itself. So, you can write a Stored Procedure (SP) once and use it (almost) anywhere, even across applications that share the same database.

Functions: These are somewhat analogous to the little VBA Functions you wrote in Access to incorporate into queries, except that SQL Server Functions, like SPs, are more tightly bound to the database. Also SQL Server Functions can be Scalar-valued (return a single scalar value) or Table-valued (return a rowset).

Fun SQL tricks: There are lots of SQL features available in SQL Server that are not supported in Access (Jet/ACE) SQL. "Common Table Expressions" (CTEs) and "RANK and PARTITION" are the ones that gave me "'aha' moments" as I was getting started.

I could go on, but these are the things I remember discovering early on that got me "thinking in SQL Server".

Don't get me wrong, I still think Access is an excellent tool when used appropriately, and should be given serious consideration as a way to

build (or just mock up) front-end applications against a SQL Server back-end, and
do data cleanup, create ad-hoc queries and reports, and perform similar tasks on your SQL Server data.

Finally, even though you will be working with SQL Server 2005 I highly recommend that you get a copy of SQL Server 2008 (or 2012) Express for learning, mainly because of the IDE enhancements in SQL Server Management Studio (namely, auto-completion and interactive debugging). Just be aware of any newer (2008+) features you may encounter and don't rely on them for your production code. (Or, use them as bullet points for your pitch to upgrade from SQL Server 2005....)


How to select Data from ODBC Source in MySQL

MySQL won't do this. It doesn't have that feature. Sql Server, Oracle, Postgresql, and even MariaDB and MS Access all have the ability to create views which rely on remote databases of other types (including MySQL), but MySQL does not.
So, you could go the other direction, and let Sql Server create a view that uses MySQL data, but you cannot have MySQL create a view using data from Sql Server.
The only workaround is an ETL (Extract/Transform/Load) process in a cron job, the queries Sql Server and copies the data into MySQL table on some kind of schedule.


SQLite's C API has functions to create custom collations.

SQLiteStudio allows you to create such collations in any of its supported scripting languages.


Do I need to back up the log file drive when using VSS snapshots in simple mode?

Couple thoughts on this one:
Do you have more than one database per-SQL instance?  Are they all "owned" by the same application?  If you've got multiple DBs for multiple apps, can your backup solution only restore ONE database (both the MDF and LDF) or will you have to restore all the DBs?

As Kris G pointed out, how do you KNOW your VSS snapshots are restorable to a working SQL DB?  Have you test restored them somewhere?

Lastly, to cover your question, it's a bad idea to not backup the transaction log.  Even in Simple Recovery, you can have "in flight" transactions that haven't gotten to the point of being written into the MDF yet.  Not having the Trans Log when you restore one of these will make getting the DB back online and functional a much more difficult task.

Frankly, I'd take the safe way out, turn off the VSS snapshots of your SQL data and log drives, set up a backup process using the native SQL backup command on a set schedule to a location that is covered by the VSS.  More work for you, a bit more work for whoever's in charge of the VSS snapshots, but you'll be much happier if / when you have to restore a database down the road.


Oracle: REGEXP_LIKE: Strings with angle brackets aren't found (whether they are escaped or not)

select 1 from dual where
regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~',
            '%\.,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~');

This gives, as expeced, 1.

You must escape .?$)(*+[]\^{} and |

You do not need to escape , (but it works nonetheless):

select 1 from dual where
regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~',
            '%\.\,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~');

This works:

select 1 from dual where
regexp_like('
Lets check for an inner tag :)
',
            '<(\w+)>[^<]*</\1>');

Gives 1. So does what you ask for:

select 1 from dual where
regexp_like('
Lets get this
example going :)

',
            '><div class=');

For which you don't even need regex, by the way.

EDIT:
By guess is, that you are using a programming language to build your SQL. In that case, it is highly likely that you are not properly escaping your . If you want the SQL to include '\.' (an escaped .), you need to escape it for your lanuage too: '\\.'
Likewise, if you want '\\\^' you'd need to write '\\\\\\^'


found xmin X from before relfrozenxid Y during vacuum

You might be in luck.

The last but one point release for 9.3 has this change:

  Ensure that updates to the relfrozenxid and relminmxid values for "nailed" system catalogs are processed in a timely fashion (Andres Freund)

  Overoptimistic caching rules could prevent these updates from being seen by other sessions, leading to spurious errors and/or data corruption. The problem was significantly worse for shared catalogs, such as pg_authid, because the stale cache data could persist into new sessions as well as existing ones.

See the discussion on the -hackers list.


You should separate the concepts. The combination {prisoner_id, person_ssn} is perfectly valid for Primary Key in the Prisoner Table. But you do not need to do that, if prisoner_id is unique. In that case, adding person_ssn to the PK is not optimal.

Adding a foreign key constraint on person_ssn checks that no person_ssn is added to Prisoner table if there is not a corresponding entry (for the same person_ssn) in the Person table. So, you should definitely add person_ssn as a foreign key in Prisoner table.

If you want fast look_ups on Prisoner table according to person_ssn, you should have an index at person_ssn column at Prisoner table. Luckily as the SO question/answers Does MySQL index foreign key columns automatically? suggest, InnoDB already adds indexes to foreign key columns (from your comments I assumed you are using MySQL.)

So:

Primary keys -> unique identifiers of each table row
Foreign key -> referential integrity with referenced table
Index -> Fast look ups

Having said that and as comments suggest, the same column can be used as a Foreign key and participate in a Primary Key as well. Those two concepts are independent from each other.


The courses have a many-to-many relationship, but the functional dependencies can't be expressed using a single intermediate table. Consider the following:

Course A School 1 = Course X School 2
Course A + B School 1 = Course Y School 2
Course C School 1 = Course Y + Z School 2

When a course from School 1 is contained in more than one equivalency, there is no way to determine from a tuple (row or record in the table) which courses are a single equivalency:

course_school1  course_school2
A               X
A               Y
B               Y
C               Y
C               Z

This is ambiguous, as it could mean either the statement above or that A = X + Y, C = Y, C = Z etc. You will need another table to express the notion of an individual equivalency. The schema would be something like:

CREATE TABLE EquivalencyBase (
    EquivalencyId int NOT NULL,
    CourseId varchar NOT NULL )

where EquivalencyId is a user- or application-generated key, but is not unique, and CourseId is a course from School 1. So the data in this table (Courses would probably have a generated primary key of type int/bigint, but I use the letters for clarity) for the relationships above would be:

EquivalencyId  CourseId
1              A
2              A
2              B
3              C

Then the equivalent courses from the second school would be expressed in another table:

CREATE TABLE CourseEquivalency (
    EquivalencyId int NOT NULL,
    CourseId varchar NOT NULL )

where EquivalencyId is a foreign key from EquivalencyBase. The data in this table would be:

EquivalencyId  CourseId
1              X
2              Y
3              Y
3              Z

To select the groups of equivalent courses, join the tables. Obviously another table would contain the course descriptions, etc., which would simply require an additional JOIN on CourseId.

SELECT
  eb.EquivalencyId,
  eb.CourseId AS School1Course,
  c1.Title AS Course1Title,
  ce.CourseId AS School2Course,
  c2.Title AS Course2Title
FROM EquivalencyBase eb
INNER JOIN CourseEquivalency ce
ON eb.EquivalencyId = ce.EquivalencyId
INNER JOIN Courses c1
ON eb.CourseId = c1.CourseId
INNER JOIN Courses c2
ON ce.CourseId = c2.CourseId
WHERE eb.CourseId = 'C'

Result:

EquivalencyId  School1Course  Course1Title  School2Course  Course2Title
3              C              Cowboy 101    Y              Yak herding
3              C              Cowboy 101    Z              Zoology

This design could be extended to map equivalent courses for multiple schools with the addition of a school identifier in each table.


Ola Hallengren's backups scripts, cleanup only mode

There are a few ways to try this, but from what I can tell they either won't work, or will still have to do at least part of the backup process, or will require modifications to Ola's stored procedures.
Don't back up any databases?
Looking at the code inside Ola's DatabaseBackup stored procedure, the cleanup portion is inside the loop that goes through all of the databases that are to be backed up. So passing a dummy parameter or just asking it to back up only a small token database isn't going to delete old backup files from the other databases.
Backup to NUL?
You can pass NUL as the directory, but the code has a check for that and won't perform cleanup if you are writing the backups to NUL.
  IF @CleanupTime IS NOT NULL AND EXISTS(SELECT * FROM @Directories WHERE DirectoryPath = 'NUL')
    BEGIN
      INSERT INTO @Errors ([Message], Severity, [State])
        SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported when backing up to NUL.', 16, 4
  END

And even if you commented that check out, it will try to clean up from the same place you are writing the current backups to. So that won't help.
CopyOnly backups?
Using the CopyOnly parameter will prevent these one-off backups from affecting your backup chain. But the directory and/or filename will have a CopyOnly indicator in there, so the only old files that will get deleted are only going to be other CopyOnly backups.
Modify the code?
You might be able to comment out the line that executes the "Backup Database..." command (something like EXECUTE @CurrentCommandOutput =...), and use the Before_Backup value for the CleanupMode parameter. That way, the cleanup will happen and then the backup will be skipped. However, there are a lot of checks in the code to prevent things from going sideways, and I haven't read through it in enough details to know if commenting out that line would be safe. I wouldn't recommend it.
Let it work as designed?
Can you just correct the CleanupTime parameter that was too long, and let it clean everything up the next time it runs? Is there a reason you need to do the cleanup before the next scheduled backup?


SQL Server : Merge and update multiple rows across columns

If you only ever have one row were the city and occupation columns are populated you can achieve it with a windowing function:

E.g:

DECLARE @Source TABLE(
    sl_no       INT
    ,name       NVARCHAR(30)
    ,dob        DATETIME2(3)
    ,city       NVARCHAR(30)
    ,occupation NVARCHAR(30)
);

INSERT INTO @Source
VALUES
    (1, 'galileo-galilei', '1900-01-01 00:00:00.000', NULL, NULL),
    (2, 'galileo-galilei', '1900-01-02 00:00:00.000', 'venice', NULL),
    (3, 'galileo-galilei', '1900-01-05 00:00:00.000', NULL, 'astronomer'),
    (4, 'issac-newton',    '1900-01-01 00:00:00.000', 'london', 'mathematician-scientist')

SELECT DISTINCT
    name
    ,MAX(dob)           OVER(PARTITION BY name) AS dob
    ,MAX(city)          OVER(PARTITION BY name) AS city
    ,MAX(occupation)    OVER(PARTITION BY name) AS occupation
FROM
    @Source

However, I suspect the reality is you could have multiple records and you always want to return the value from the most recent record that has data in those columns.  E.g. if your source was:

DECLARE @Source TABLE(
    sl_no       INT
    ,name       NVARCHAR(30)
    ,dob        DATETIME2(3)
    ,city       NVARCHAR(30)
    ,occupation NVARCHAR(30)
);

INSERT INTO @Source
VALUES
    (1, 'galileo-galilei', '1900-01-01 00:00:00.000', 'rome', NULL),
    (2, 'galileo-galilei', '1900-01-02 00:00:00.000', 'venice', NULL),
    (3, 'galileo-galilei', '1900-01-05 00:00:00.000', NULL, 'astronomer'),
    (4, 'issac-newton',    '1900-01-01 00:00:00.000', 'london', 'mathematician-scientist')

You could achieve what you want with:

SELECT
    s.name
    ,s.dob
    ,sc.city
    ,so.occupation
FROM
    @Source AS s
    CROSS APPLY(
        SELECT TOP 1 city
        FROM @Source AS s2
        WHERE s2.name = s.name
        AND city IS NOT NULL
        ORDER BY sl_no DESC
        ) AS sc
    CROSS APPLY(
        SELECT TOP 1 occupation
        FROM @Source AS s3
        WHERE s3.name = s.name
        AND occupation IS NOT NULL
        ORDER BY sl_no DESC
    ) AS so
WHERE
    s.sl_no = (SELECT MAX(sl_no) FROM @Source AS s4 WHERE s4.name = s.name)

Wrap that up into a merge or update (I'll do a merge for you) and you will get:

WITH src AS (
    SELECT
        s.name
        ,s.dob
        ,sc.city
        ,so.occupation
    FROM
        @Source AS s
        CROSS APPLY(
            SELECT TOP 1 city
            FROM @Source AS s2
            WHERE s2.name = s.name
            AND city IS NOT NULL
            ORDER BY sl_no DESC
            ) AS sc
        CROSS APPLY(
            SELECT TOP 1 occupation
            FROM @Source AS s3
            WHERE s3.name = s.name
            AND occupation IS NOT NULL
            ORDER BY sl_no DESC
        ) AS so
    WHERE
        s.sl_no = (SELECT MAX(sl_no) FROM @Source AS s4 WHERE s4.name = s.name)
)
MERGE INTO Destination AS tgt
USING tgt.name = src.name

WHEN MATCHED THEN UPDATE
SET dob = src.dob
    ,city = src.city
    ,occupation = src.occupation

WHEN NOT MATCHED THEN INSERT(name, dob, city, occupation)
VALUES(src.name, src.dob, src.city, src.occuptaion);

You are going to want to index the column you join on all the time (name in the above examples) for performance. Otherwise you will get many scans.


Speeding up COUNT(*) - WHERE clause slowing query

Another approach to remove the OR.
You could try this and have 2 indexes on the table to satisfy the WHERE clauses.

SELECT
    COUNT(*)
FROM
    (
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND hidden = 0
    UNION
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND message_id = @message_id
    ) X


No it isn't. Or at least not in its entirety.

Part of the transaction log will be included though reflecting concurrent activity during the backup. This is to allow the database to be restored to a transactionally consistent state.

Much more detail in this article by Paul Randal Understanding SQL Server Backups


In pgPool-II 3.7.5 configuration pgpool.conf, is recovery_user a role or a system user?

I don't use pgpool, but it must be the OS user who runs the database server.  It cannot be a user within the PostgreSQL database, as a failed database cannot accept connections until after it has been recovered.


mysqldump is not dumping data by default

Use only

mysqldump -u user -p database

The -d option means no-data :

   --no-data, -d

       Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE
       statement for the table (for example, to create an empty copy of the table by loading the dump file).


Integrated Security Success on Master, Fails on Other Database

In SQL Server, as long as a LOGIN is valid, it will have access to the master database in a public role.  This is both for historical and practical reasons.  For example, stored procedures beginning with "sp_" are searched first in the master database and will execute from there.  The implication is that users need to first have access to the master database to begin with.  tempdb is another database that allows user access via the public role.

User databases do not have this as a default setup, so unless the Login is specifically given permission, or has server-wide roles such as "sysadmin", it will not be able to switch to the database context, hence your error.

The fact that you manage to log in at all using the machine account means that it is either explicitly a created LOGIN, or is a member of a group that appears under "Logins" when you browse the Object Explorer in SQL Server Management Studio.

[SERVER\Instance]
    Security
        Logins
             .... here ....

You can specifically assign roles or grant execute permissions to the machine account:

use other;
exec sp_addrolemember db_datareader, [DOMAIN\IIS_SERVERNAME$]
GRANT EXECUTE ON sp_myproc TO [DOMAIN\IIS_SERVERNAME$]


I don't think there is a canned view for that purpose, but such a query is possible to write.

You'd have to query each object type independently, forming a big UNION. A number of system information functions are available for checking permissions on objects, see the Access Privilege Inquiry Functions table.

Combine them with queries over information_schema, or over the internal PostgreSQL tables like pg_class, pg_proc, etc, and you'll be able to collect the desired information.

You might find the queries psql uses to implement \dp and the various + commands like \df+ informative. Run psql with -E to see them.


Query Takes more time in SQL Server 2008

Sure; upgrade your hardware.  Note that rendering results in Management Studio is notoriously slow, so keep that in mind if that is your performance benchmark tool.  However ...

The big question I have is: why do you care?  I'm hard pressed to come up with a legitimate reason to return the entire 550k record set.  You're not going to display that much on any screen in an application.  No one would ever view it.  If you're returning it for post-processing an aggregation or something of the sort, you should be aggregating it in SQL, then returning it.  If you are dumping it in an ETL process, long queries basically come with the territory (unless you're clever and come up with an incremental Extraction).


Drop auto increment hack w/o alter table?

I tried something similar just now
Here is MySQL for My PC
mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.
Here are Steps 1-4
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6
C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7
mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???
Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?
You got guts, @atxdba !!!


Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

When did you check this data?  The msdb.dbo.sysjobschedules table refreshes every 20 minutes.  So if you had it set and then changed it, then ran the sp_help_jobschedule stored procedure, the underlying data might not be updated yet.

What do you get for next_scheduled_run_date when you execute this query?

exec sp_help_jobactivity @job_name = 'YourJobScheduleName'


Not quite what you asked for, but this query may help:

mysql> SELECT db, name FROM mysql.proc WHERE body LIKE '%temporary%';
+---------------+-----------------------------------+
| db            | name                              |
+---------------+-----------------------------------+
| common_schema | rdebug_compile_routine            |
| common_schema | _get_sql_dependencies_internal    |
| common_schema | query_checksum                    |
| common_schema | _split_cleanup_dependency_tables  |
| common_schema | _split_generate_dependency_tables |
+---------------+-----------------------------------+

If /tmp is in a separate filesystem, you are cruisin' for a bruisin'.  Change tmpdir to point to some directory in the main FS.


How to compare two last updated entities from two different table Postgresql?

You are missing some parenthesis around your select queries.
But i don't understand completely what you try to achieve, i think you should at least also transfer the produdct_id, because for now you get a price from a unknown product.
create or Replace function test() returns trigger as $$ begin

update change
set change_= (SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1) - (SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1) ;
return new;

end; $$ language plpgsql;

CREATE TRIGGER testtrig BEFORE UPDATE ON price
FOR EACH ROW
  EXECUTE PROCEDURE test();


How to join a table with a table valued function?

You need CROSS APPLY not join.

The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table.

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...