SQL Agent Job or SQL Alert for Long Running Queries

You can't use the INTO keyword inside of an IF clause, so the syntax error you're currently getting is due to trying to insert the results into a temp table as part of the IF clause.
There's no performance benefit stuffing the query inside the IF clause, so for better readability I would run the query first then check the if results exist like so:
-- Get any long running queries
DROP TABLE IF EXISTS #LongRunningQuery
SELECT
    'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text) AS EmailBody,
    CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) AS Runtime
INTO #LongRunningQuery
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
    AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 60

-- Send email when long run queries found
IF EXISTS (SELECT 1 FROM #LongRunningQuery)
BEGIN

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Alerts', @recipients = 'test@test.com', @subject = 'Warning', @query = 'select * from #LongRunningQuery'

END


SQL Server: how to avoid sysadmin rights but give adequate rights

For server logins, you can grant "securityadmin". The "newer" way is to run

GRANT ALTER ANY LOGIN TO AColleague

Edit: Securityadmin allows someone to bootstrap themselves to sysadmin. Not good. Don't know how to workaround this at the server level

For jobs, look at the "SQL Server Agent Fixed Database Roles"

For maintenance plans, it looks like "sysadmin" only


Resolving DUPLICATE for a column with UNIQUE

I would look at your model first.  Placing a unique key constraint on a non-unique column gets you into this kind of problem.  What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title.

If I had to resolve your problem, I would build a query for each unique key of the form:

SELECT  title
FROM    table1
WHERE   title STARTS WITH ?

The results will need to be parsed to see what the suffix should be.  It might be possible to do this in a stored procedure.  However, I would strongly argue that the data model needed to be fixed, not the code.


MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB)

Okay, in case anyone stumbles upon a similar problem and Google directs them here:
I don't exactly figured out why, but sort of figured out what causes it, phpMyAdmin was the culprit.

Issuing the same commands directly in the mysql command line did not cause the temporary tables to be written to disk.
Now, I also have phpMyAdmin on my development machine and it's not an issue here. But I'm running PHP as a module in Apache, whereas on the production server it's IIS with PHP running as FastCGI. So some configuration or limitation must've caused this, not exactly sure.


Populate columns of table with other table that doesn't have relationship

The more compact way to write this would be:

UPDATE Products
SET factor1 = article.factor1
from Article
WHERE Article.id = Products.id and products.factor1 is null


how to change nls_language in nls_instance_parameters

Example below on how to update this:

[oracle@mydbserver dbs]$ unset LANG
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:18:26 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydbserver dbs]$ unset NLS_LANG
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:18:51 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

This is where I edited the init and spfile - you might want to take a backup !!

[oracle@mydbserver dbs]$ ls
hc_scpp.dat  init.ora  initscpp.ora  lkSCPP  orapwmydb  spfilemydb.ora  teste.ora

edit initscpp.ora and spfilemydb.ora with the values you want for the NLS parameters
Then I proceeded to restart

[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:25:30 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown
^CORA-01013: o usuario solicitou o cancelamento da operac?o atual

shutdown was slow so I [ctrl+c] and tried to force restart then restart normally !!

SQL> startup force
ORACLE instance started.

Total System Global Area 2,0724E+10 bytes
Fixed Size                  3158000 bytes
Variable Size            3070230544 bytes
Database Buffers         1,7645E+10 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.0724E+10 bytes
Fixed Size                  3158000 bytes
Variable Size            3070230544 bytes
Database Buffers         1.7645E+10 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:31:17 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


Cannot connect to DB2 database after restoring an offline backup

Found the answer here.

I needed to run the command

db2 rollforward db database_name to end of backup and complete;

This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.

  EDIT: Found this nifty nugget while attending a DB2NightShow episode. With 9.7 FP2, they introduced a registry variable called
  DB2_RESTORE_GRANT_ADMIN_AUTHORITY. This is handy if you are restoring
  a backup from one instance to another, as it automatically grants
  instance owner SECADM, DBADM, DATAACCESS, and ACCESSCTRL on the
  databases restored. With 9.7 FP5, the setting is dynamic and does not
  require bouncing of the instance. I realize my original question had
  nothing to do with security, but it can be encountered and thought
  that this would be a handy tidbit placed here.

  EDIT #2: I recently found out that if the backup is an offline backup based on an archival logged database, I could have included the
  clause WITHOUT ROLLING FORWARD in my RESTORE command. This would
  have taken the database out of roll forward pending state immediately
  upon successful restore, which would mean that I would not have had to
  then issue the ROLLFORWARD DB command.


Can I delete SQLServer2017Media folder from my C drive?

is it okay if I delete the SQLServer2017Media folder (takes 1.6 GB) now that set up us complete and application is running fine?

Yes it is totally safe to delete it, this would not affect running SQL Server application. But sometimes during un-installation later or may be future installation or adding feature you may need the whole installation file. So my suggestion would be to copy it to pendrive and keep it safe if you "really" do not want to keep it on desktop or any other drive on your PC. Plus would it not be possible to move it to other drive on your PC ( ofcourse if you have one then) ?


This has been asked and answered many times, so there is even a couple of FAQ sections on it.  First you will need to understand the difference between resident memory usage and the cache being full (very different things), then you need to look at how memory mapped files behave in general.  Here are a couple of starting points from the MongoDB Docs:

http://docs.mongodb.org/manual/faq/fundamentals/#does-mongodb-require-a-lot-of-ram
http://docs.mongodb.org/manual/faq/diagnostics/#memory-diagnostics

Short version, no it is not a memory leak, and yes it is expected behavior over time when the data set exceeds available physical memory.


To answer your questions in order:

The cube doesn't store medians, modes (or even averages), but you can write queries that calculate them and embed them as calculated measures in the cube.  The ability to embed this sort of computation is one of the main unique selling points of OLAP technology.
If you have a dimension that can identify individual rows (which could be a degenerate or 'fact' dimension derived from an identifier on the fact table) then you can do a query based on individual rows.  However, OLAP works in terms of dimensions and aggregates, so you would need to have a dimension capable of identifying individual rows (with an aggregate composed of one value).
Any OLAP tool can do what's described in (2), plus they generally support a mechanism known as 'drill-through' where the cube will return a bordereaux of the transactional data underlying a given slice that you drill through into.

If you want to do calculations that aren't directly available in the cube script, many OLAP tools such as the late, lamented ProClarity will allow you to formulate queries involving custom MDX based calculations.  Unless the cube doesn't have the information you need to do the actual calculations, the custom MDX calculations should be able to support any computation you need.

Although OLAP queries are traditionally associated with statistical queries in aggregate, if you have a dimension that allows drill down to detail you need it is certainly possible to formulate queries that will calculate medians, percentiles or histogram queries from which modes can be inferred or computed.

For example, this has an example of a pareto analysis query, which is based on rankings.

Many cube products can operate in a hybrid or relational OLAP mode where they do not persist the data themselves but query it from an underlying database.  In addition, pure ROLAP tools such as Business Objects, Report Builder or Discoverer can query from an underlying database and do work row by row.  However, they tend to lack the sophistication of dedicated OLAP products, and they don't have much in the way of statistical analysis capability out of the box.


This should do the trick.

SELECT COUNT( * ) AS  `how much` ,  `name` ,  `location`
FROM  table
WHERE  name NOT IN (SELECT name FROM table WHERE location = 'y')
GROUP BY   `name`
ORDER BY   `name`

The idea behind this is : we are counting the number of name but we have to exclude rows of name where in ANOTHER row there is location = 'y'.

If you do WHERE location <> 'y', you just exclude the line including location = 'y' but the others rows with location = 'x' are still there and used in count(*).

So, the goal is to exclude ALL names where a minimum of one row has location = 'y'. This is why we do name NOT IN (...).

The part between the brackets select all name where location = 'y'. This outputs all names we want to exclude from our COUNT(*). We just have to exclude all these results from our query.

That's all folks !


I would definitely use a DRBD pair in one DataCenter and a DRBD pair in the other DataCenter.

I have written posts about using DRBB in this way:

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Mar 26, 2012 : 3 Databases, Best Replication Setup?
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication

Note that each DRBD pair would have a DBVIP for doing local failovers. Just setup Circular Replication between the DBVIPs only (which would be between the DRBD Primaries of each DataCenter).

Since you need MySQL Replication in addition to all this, I would use Semisync Replication to lower sensitivity to network latency.

Give it a Try !!!


Customizing OEM notification title and body

In OEM 12c Enterprise Manager, you can change both the body and the subject of email alerts through the web-based EM console.

Once logged in, you should see menu options in the top right corner like "Setup" and "Help" with down arrows indicating a drop-down menu.

Click "Setup" -> "Notifications" -> "Customize Email Formats..."

From here, you can select the type of email that is being sent (Metric Alerts, Job Status Changes, etc.), the format (Long or Short), and you can also customize the subject and body of the email by clicking "Customize".  A sample email based on your changes will be shown below the "Customize" button.

When customizing an email format, you can add plain text in both the subject and body, and you can also use the predefined labels and attributes (links to these are on this same page) to include dynamic information based on the type of alert and other status values.

Once you save this, your email notifications from OEM 12c should reflect whatever changes you made.

In addition, if you go to "Setup" -> "Notifications" -> "Notification Methods", you can change the name that appears as the sender of the email (e.g., "OEM 12c Administrator") as well as the email address that the alert appears to be sent from.  This also allows you to use rules and filtering on the email alerts by filtering on the "FROM:" email address and sender name.


Use expression in window function that references columns from the current row

Is this what you are looking for ?

create table #t(id int, vars decimal(3,1), num int)
insert into #t VALUES
    (1, 0.1, 7),
    (2, 0.7, 1),
    (3, 0.3, 9),
    (4, 0.9, 5),
    (5, 0.5, 3)

;With CTE as
(
select
      t.id
     ,t.vars
     ,t.num
     ,isnull(lag(vars ,1) OVER (ORDER BY id ),0) AS vars1
     ,isnull(lag(vars ,2) OVER (ORDER BY id ),0) AS vars2
     ,isnull(lag(num ,1) OVER (ORDER BY id ),0) AS num1
     ,isnull(lag(num ,2) OVER (ORDER BY id ),0) AS num2
FROM  #t t
)

select *
,iif(vars<=0.1,vars,vars2)*num2 +
iif(vars<=vars1,vars,vars1)*num1 +
iif(vars<=vars,vars,vars)*num
from CTE

drop table #t


Dropping empty partitions in Oracle 11g DB

I am using a procedure like this:

PROCEDURE MaintainPartitions IS

    CURSOR PartTables IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME LIKE ....
    ORDER BY TABLE_NAME, PARTITION_POSITION;

    cur SYS_REFCURSOR;
    r INTEGER;
    highValue VARCHAR2(100);

BEGIN
   FOR aTab IN PartTables LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
      IF highValue NOT IN ('DEFAULT', 'MAXVALUE') THEN
         OPEN cur FOR 'SELECT ROWNUM FROM '||aTab.TABLE_NAME||' PARTITION ('||aTab.PARTITION_NAME||') WHERE ROWNUM <= 1';
         FETCH cur INTO r;
         IF cur%NOTFOUND THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' DROP PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
         END IF;
         CLOSE cur;
      END IF;
   END LOOP;
END;

Note, you cannot drop a subpartition. In such case you have to use TRUNC.

Another note, in case of INTERVAL partitions you may face ORA-14758: Last partition … cannot be dropped. Have  a look at https://stackoverflow.com/questions/1395379/unable-to-delete-oldest-table-partition to manage it.


You should use the nodes() method of the XML data type to shred your XML.

If I understand you correctly you want to fetch the nodes from the root node where the attribute type is line and from that node you want the nodes where the attribute type is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you are using but that the node names are valid XML names.

declare @XML xml = '
<root>
  <A123 type="line">
    <aa type="item">
      <value>1</value>
    </aa>
  </A123>
  <B234 type="line">
    <bb type="item">
      <value>2</value>
    </bb>
  </B234>
</root>';

select T.X.value('text()[1]', 'int') as Value
from @XML.nodes('/root/*[@type = "line"]/*[@type = "item"]/value') as T(X)

A breakdown of the xPath in the nodes function:

From /root give me all nodes /root/* where the attribute type is line /root/*[@type = "line"] and from there give me all nodes /root/*[@type = "line"]* where the attribute type is item /root/*[@type = "line"]/*[@type = "item"].

Finally get the value node /root/*[@type = "line"]/*[@type = "item"]/value and extract the text node using the value() function.


Downside of large "max connections" in a connection pool from a web app

I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling.

Generally, but it depends... If it's a long lived process or connection, a dedicated connection can be more performant and less overhead.

  If you set your max pool size too small - let's say, to 1 connection max - then the downside of that is your app has to share that single database connection for all requests, and requests will have to wait for the previous one to finish with the database connection before they can fetch it from the pool and re-use it.

They could also get an error. I wouldn't assume it'll multiplex perfectly. This is why setting the maximum value too low can have a detrimental effect (also setting it too high!).

  But what if you just set your max pool size really big, like 1000 connections max. Shouldn’t it then favour re-using connections over establishing new ones, and only max-out the pool size if it actually needs to in order to service demand?

I'd say, if 1000 concurrent connections doesn't break anything in your app/database processing then go for it. Realistically, unless this app becomes extremely popular or there is an issue in the app/database there shouldn't be a need to hit 1000 concurrent requests from the same pool on the same app server. If it is, chances are you'll kill the app server before the database server (maybe not, depends on hardware and config) or there will be some concurrency issue at the database level (again, depends).

This will allow the pool to grow but hopefully not overwhelm the database server. If you had 10 app servers all set to this, then 10k connections may be an issue. There are a good amount of moving parts, even in this simple example but I'm trying to be optimistic.

  What's the down side of making connection pools bigger than they need to be to service the app's demand, assuming unused connections are closed / removed from the pool after an idle timeout?

The downside is that you could:

Overwhelm the database
Overwhelm the application server
Have high overhead for setting up/tearing down connections

Eventually the connections will be removed and freed, but setting them up takes resources and time. The point of the pool is to reuse those resources to reduce the cost and resource usage, overall.


How do I write a mysql query to combine data from related tables into a single summary?

Yes, there is a way. Just add an identifier column to the selection list. E.g.:

select count(warr_id), year(prod_date) AS prod_year, month(prod_date) AS prod_month, 'W' as type
from product_t, warr_t
where prod_date is not null and serial_number = product_look
group by year(prod_date), month(prod_date)

union

select count(serial_number), year(prod_date) AS prod_year, month(prod_date) AS prod_month, 'P' as type
from product_t
where prod_date is not null
group by year(prod_date), month(prod_date)

When you build the chart for the query use the type column for different series


Error ORA-12505 when connecting to Database Oracle 11g SQL Developer

The way you are connecting to the database (connection type "basic") isn't using your tnsnames.ora file at all. It is using a basic JDBC connect string - which is fine. That said, the error indicates that the database "xe" hasn't registered with the listener, meaning that the database itself probably isn't running at all. Check to make sure that your database instance is actually up. Check this document for details: https://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm#XEGSG101


Is there any trick that allows to use Management Studio's (ver. 2008) IntelliSense feature with earlier versions of SQL Server?

Aside from using a third party tool, no. I run into this problem a lot too. The deal is the SqlServer2005 doesn't support the meta data to do that.

However, I can suggest (cos I used it for a bit and loved it) RedGates SqlPrompt product to do this. It's incredibly handy. However, it does cost money, so there's a tradeoff.

If someone DOES have a workaround for this, I would love to hear of it.


Redirecting Report Server connections using host file

For anyone who's facing the same issue, following the steps in this article fixed my issue. For some reason, it's required to make an adjustment in registry to allow for this use. Also, remember to add report server site as INTRANET (in Internet Option/security).


Designing a multi-tenant database for a scenario with multiple user types

Using multiple schemas to implement a multi-tenant scenario is a feasible solution if you have few tenants.  If you have too many tenants, you will end up with too many tables, and that will make life quite painful for you.

If you have many tenants, you could use one table for all tenants and use a column like tenant_id to identify them. If you don't want to trust to your application logic to restrict query results to the appropriate tenant, you could use row level security and let the database do it for you.

Another useful tool for the “single table” approach is partitioning. You can use list partitioning to split that large table in partitions based on the tenants. That way, you can bundle several (smaller) tenants together in a single partition. Partitioning will make it easier to delete all data of a single tenant (this is its main advantage), and it may save you an index on the tenant_id. Most queries won't get faster through partitioning though.


SQL query to return result with different row

SELECT id,
       MAX(CASE WHEN `day` = 1 THEN item END) day_1,
       MAX(CASE WHEN `day` = 2 THEN item END) day_2,
       MAX(CASE WHEN `day` = 3 THEN item END) day_3,
       MAX(CASE WHEN `day` = 4 THEN item END) day_4
FROM source_table
GROUP BY id


Large Fact table and partitioning key dilemma

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one).  The reason I state this is because I don't know what your code is querying.  Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column?  For example, are your queries actually looking for the row ID often?  If you're not sure, you might be surprised by getting some performance benefits.  The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you.  Check out this article by Kim Tripp:
http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx

"What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits.  However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list.  Page Splits are managed by proper FILLFACTOR not increasing INSERTS.  Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting.
In short, the argument runs shallow
"

Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes.  I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.


Type of an id field that references another id field

It should be int. The referencing and the refernced columns have to be of the same datatype but it doesn't make sense for a serial to reference another serial because serials are just int with auto incrementing values provided by a sequence.

And when you will be inserting in the second, referencing table (client), you'll want to use the values already inserted in the 1st, referenced table (user). You don't want automatically provided values as these may not match the existing values you already have in the 1st table.

The Postgres docs about numeric datatypes explain:

  The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;


Dump Oracle database into a textual SQL script

Does it exist on Oracle?

Similar utility exists in Oracle environment called Data Pump.

  How can it be done?

Use expdp command line tool to perform full database export.

Example:

expdp hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=YES NOLOGFILE=YES

Then can use impdp to import it in another database.

Example:

impdp hr DUMPFILE=dpump_dir1:expfull.dmp FULL=YES LOGFILE=dpump_dir2:full_imp.log

If you specify DUMPFILE parameter it writes a binary file called dump file. With the SQLFILE parameter(SQLFILE=/my/file/name.sql) you can get all of the SQL DDL that Import would have executed.

More on it depends upon the requirements.

Oracle Documentation explains more than I can here.

Overview of Oracle Data Pump


XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it

This is a bug in mysqldump that Oracle has characterized as a "feature request."

http://bugs.mysql.com/bug.php?id=66821

In normal mode (not XML), mysqldump just uses the server's output from SHOW CREATE TABLE tablename.

if (!opt_xml ...
    ...
    my_snprintf(buff, sizeof(buff), "show create table %s", result_table);

When running in --xml mode, mysqldump uses the output from SHOW KEYS FROM 'tablename', which doesn't include the foreign key constraints.

my_snprintf(buff, sizeof(buff), "show keys from %s", result_table);

Since the two different operating modes of mysqldump fetch the data from the server in such different ways, so the addition of this information to an XML dump is not exactly a trivial change.

There is not a simple workaround, although as noted in the bug report, the information is available in information_schema and you can access this from mysqldump if you supply the --skip-lock-tables option (locking information_schema isn't allowed).  You'll have to take the data you retrieve, parse and extract the relevant bits, and reformat it as needed before it would be all that useful.


Why OUTER APPLY forces scanning the entire table?

Your Scan is running a single time, to populate an Index Spool. This spool is called 3032 times. If you create an index that will suit better, you should be able to avoid the spool, and it probably won't scan any more either.

Edit: But I think what you want is the left-most TOP to be on the right of the Nested Loop. This is a common thing with Key Lookups as well, that they often appear to the right of Top operators when you'd prefer they be on the left. Here, the Spool is quite cheap to query, so there's no much advantage to having it to the side. With a better index, you could probably persuade it not to Spool, and therefore it might see real benefit in pushing the Top further right.

Another edit: Presumably if you left out ServerPath, the Nested Loop disappears completely, as that table is no longer needed at all. But I'm sure you're trying to get it to work without changing the queries much.


Map login to user in restored database in SYBASE ASE 16

Sybase (ASE) does not provide any method for syncing system tables between a user db and the master db.  It's up to the DBA/dbo to manually fix this after loading a database from a different dataserver (or loading an old db dump after the local dataserver's logins/roles/users/aliases has been (heavily) modified).
There are a few issues you'll want to consider:

users mapped to the wrong login (need to remap suid)
users with no login to map to (need to delete)
(db) roles mapped to the wrong (srvr) role (need to remap id)
(db) roles with no (srvr) role to map to (need to delete)
aliases mapped to the wrong login (easier to delete and re-run sp_addalias)
aliases with no login to map to (need to delete)
permissions with no associated grantee/granter (after cleaning up sysusers/sysalternates/sysroles)

Assuming you're looking for input/suggestions on how to go about patching sysusers ...

Do NOT perform these operations without first:

testing/verifying in a test database
making sure all changes are performed within a transaction and said changes are double-checked/verified before issuing a commit tran

Anyone proceeding with the following steps is solely responsible for any adverse outcomes!!

NOTE: sysusers contains data on users, groups and roles; the following applies to the entries for users.
Assumptions:

db user names are the same as the associated login name (ie, sysusers.name should match syslogins.name)
if logins have been mapped to users with different names (on purpose) ... the following will not work; the DBA will need to have a master list of what logins are supposed to be mapped to what users

General steps involved in patching user entries in sysusers:
-- mismatched sysusers entries

select  suid,suser_name(suid),name
from    sysusers
where   suid > 1
and     isnull(suser_name(suid),'DOESNOTEXIST') != name
and     name not in ('dbo','guest','probe','usedb_user')  # operator should review and update this list as necessary
go

-- assuming sysusers needs to be patched ...

-- enable updates of system tables

exec sp_configure 'allow updates to system tables',1
go

-- perform all updates inside a transaction !!!

begin tran
go

-- update sysusers based on sysusers.name = syslogins.name

update  sysusers
set     suid = suser_id(name)
where   suid            > 1
and     name            not in ('dbo','guest','probe','usedb_user')
and     suser_id(name)  != suid
and     suser_id(name)  is not NULL
go

-- delete sysusers where there is no syslogins.name = sysusers.name

delete  sysusers
where   suid            > 1
and     name            not in ('dbo','guest','probe','usedb_user')
and     suser_id(name)  is NULL

/* if any errors, or rowcounts are greater than expected:

   rollback tran

else

   commit tran
*/

-- disable updates of system tables

exec sp_configure 'allow updates to system tables',0
go


SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME

I can't reproduce this problem using the same version of SSDT as you.  I do have a slightly newer Visual Studio (15.9.24), but I'd be surprised if that matters.  Here's my (very minimal) setup, please let me know if I've misunderstood some part of your scenario:
SSDT Project

clr.sql
CREATE SCHEMA [clr]

Stats.sql
CREATE SCHEMA [Stats]

StatsEntropy.cs
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct StatsEntropy
{
    public void Init()
    {
        // Put your code here
    }

    public void Accumulate(SqlString Value)
    {
        // Put your code here
    }

    public void Merge(StatsEntropy Group)
    {
        // Put your code here
    }

    public SqlString Terminate()
    {
        // Put your code here
        return new SqlString(string.Empty);
    }

    // This is a place-holder member field
    public int _var1;
}

Default Schema

Normally that failure during preview generation indicates that there are objects in your target database that don't exist in the SSDT model (dacpac).  That's discussed on Stack Overflow here: SSDT Publish errors on Creating Publish Preview
You can use the "Schema Compare" feature to check and see if that's the case for you.
If there are no conflicting objects in the target, then it would be helpful if you could edit your question to include your publish settings (the ".publish.xml" file, or at least screenshots of the "Advanced" window when publishing).  It could be some odd combination of options causing the problem.


MySQL can take more than an hour to start

The solution is to drastically decrease the number of files.  I see 3 approaches, depending on your version:

Plan A (the only option for 5.6 and earlier):

Set innodb_file_per_table=OFF, restart your client, then do ALTER TABLE ... ENGINE=InnoDB all but the 1000 largest tables.  This will shovel all the non-huge tables into ibdata1.

Plan B (if you have at least 5.7, and you have 10-1000 databases):

If you have multiple databases, create a General Tablespace per database.  Put non-huge tables into those tablespaces.

Leave the huge tables in their own tablespaces.

To facilitate either Plan A or B, write a SELECT against information_schema that creates the desired ALTER statements, then execute them.

Plan C:  Move to 8.0, which might actually work reasonably fast with a million tables due to its Data Dictionary.  (Or, you might need a combo of Plan C together with A or B.)


Does SQL Server allow INSERTs into tables and indexes to be written in parallel?

I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or use parallelism?

Before SQL Server 2016, an individual T-SQL INSERT statement always used an execution plan that inserts rows and maintains associated nonclustered indexes using a single logical processor. This answers one aspect of your question.

The question of inserting rows 'one at a time' requires a bit of explanation. Fundamentally, data modifications in an INSERT...SELECT execution plan always operate as one or more row-at-a-time streams that execute in serial order.

That said, the query optimizer often has a choice between two execution plan strategies:

Insert rows to the base table and nonclustered indexes using a single plan operator; or
Use a separate plan operator for each nonclustered index, clustered index, or heap.

The first option means that each row is inserted to the base table, and all nonclustered indexes are updated in a serial sequence, before moving on to the next row. This is known as a narrow or per-row, insert strategy.

The second option means all rows are inserted to the base table (though still one at a time) before doing the same thing for each nonclustered index, again in a serial sequence. This is known as a wide or per-index insert strategy.

The optimizer makes a cost-based choice between narrow and wide strategies for each nonclustered index, so it is quite common to see an execution plan that maintains some nonclustered indexes in the same operator as the base table, while other indexes are maintained using separate operators.

The wide/per-index strategy also allows potential optimizations such as sorting the set of rows in key order for each index in order to promote a sequential access pattern.

You can find more details about this and execution plan examples in my blog article, Optimizing T-SQL queries that change data.

There is a feedback suggestion to allow parallel maintenance of nonclustered indexes. It was received favourably on Connect (and later migrated to the new platform), but the idea has not been implemented yet.

While it is limited to a single processor for data modifications, INSERT...SELECT can use parallelism in the part of the plan that identifies and creates the data to insert. From SQL Server 2016, INSERT...SELECT can perform parallel inserts to a heap.

For best insert performance, check that your query meets the criteria for minimally-logged operations, possibly requiring trace flag 610. Details can be found in the Data Loading Performance Guide.

You might also want to investigate using other SQL Server facilities that do allow parallel inserts (with some caveats) using multiple processes:

SSIS (SQL Server Integration Services)
T_SQL BULK INSERT (from a file)
The bcp utility

SELECT INTO does allow parallelism (Server 2014+) but this requires that the table does not exist before the process begins, and any nonclustered indexes would have to be added afterward.

If you are using Enterprise Edition, partitioned tables offer additional possibilities to optimize bulk insertions. Again, see the Data Loading Performance Guide for details.

Related articles written by me:

Minimal Logging with INSERT…SELECT into Empty Clustered Tables
Minimal Logging with INSERT…SELECT and Fast Load Context


How to regenerate execution plans after clearing the plan cache?

The only way the plan cache can be repopulated is by running the relevant T-SQL from stored procs or ad-hoc T-SQL.

If you have several stored procs that need to be "primed" at the start of the day, you could use SQL Server Agent to run them with predefined parameters prior to your business coming "on line".


Data truncation: Incorrect datetime value: '7/21/15' for column 'announced_on' at row 1

the normal date format is 2020-04-19.

If you try to insert 7/21/15 into a date field this will fail as you noticed.

Most imports allow to reformat input.

In mysql you would use STR_TO_DATE('7/21/15', '%m/%e/%y') to import data.

If you don't want to rewrite or need the dates that way , you could define the field as varchar(10) that is big enough to hold it.


Shorthand for "UNIQUEIDENTIFIER" data type name?

The words guid and uid are coloured like any other object or possible column in a SQL statement in SSMS.
When typing out the columns in the following statement, each column is coloured blue, because it is a column from the tables I am referencing.
SELECT sao.name AS object_name,
       sao.type_desc AS object_type,
       sac.name AS column_name
FROM   sys.all_columns sac
       JOIN sys.all_objects sao
            ON  sac.object_id = sao.object_id
WHERE  sac.name LIKE 'guid'
       OR  sac.name LIKE 'uid'

Coloured as per my SSMS:

The above statement queries all objects in the sys views all_objects and all_columns and displays the corresponding object type.

+--------------------------------------+----------------------------------+-------------+
|             object_name              |           object_type            | column_name |
+--------------------------------------+----------------------------------+-------------+
| sysguidrefs                          | SYSTEM_TABLE                     | guid        |
| sysobjects                           | VIEW                             | uid         |
| sysusers                             | VIEW                             | uid         |
| systypes                             | VIEW                             | uid         |
| sysprotects                          | VIEW                             | uid         |
| sysprocesses                         | VIEW                             | uid         |
| cryptographic_providers              | VIEW                             | guid        |
| syscacheobjects                      | VIEW                             | uid         |
| fn_trace_gettable                    | SQL_INLINE_TABLE_VALUED_FUNCTION | GUID        |
| dm_cryptographic_provider_properties | VIEW                             | guid        |
| dm_xe_packages                       | VIEW                             | guid        |
+--------------------------------------+----------------------------------+-------------+

As you can see, there are a couple of columns named either guid or uid in a SQL Server instance, that could possibly be referenced in a statement and this is why SSMS will colour code them blue.


Database having only db_datareader shows invalid properties for a index

It's by design.

I can understand why it could be confusing to be able to see partial meta data. However, the idea behind the data role db_datareader is to grant a user read access to both user data as well as column information. (you wouldn't be able to create a SELECT statement if you didn't have access to the  column names as well.)

So with that in mind, showing only the part of the index definition that contains column info would be perfectly within the boundaries of the datareader permission.

The filtered index clause however is considered non column information so therefore you need more permission to be able to see this part of the definition as well.

You need VIEW DEFINITION permission on that object, or VIEW ANY DEFINITION to be able to see that extra bit of meta data.

I think it would be nice if they would give a warning when you generate a " incomplete"  CREATE script.

You can test it yourself:

--create a database
CREATE DATABASE [testdb]
go

--change context to new database
USE [testdb]
GO

--create table
CREATE TABLE [testtable] ( col1 int, col2 int)
GO

--create filtered index
CREATE INDEX [fix_testtable_col1] ON [testtable](col1) WHERE col1 is not null
GO

--create a login
CREATE LOGIN [testlogin] WITH   PASSWORD=N'test',
                                CHECK_EXPIRATION=OFF,
                                CHECK_POLICY=OFF

--create a user in your database
CREATE USER [testlogin] FOR LOGIN [testlogin]

--add the user to the data reader role
EXEC sp_addrolemember N'db_datareader', N'testlogin'

--Change your execution contect to the new user with just datareader rights.
EXECUTE AS LOGIN='testlogin'

--try and view the filter definition of the index. It will not show the definition.
SELECT  name,
        has_filter,
        filter_definition
FROM sys.indexes
WHERE name='fix_testtable_col1'

--go back to being you
REVERT

--try and view the filter definition of the index. It will succeed.
SELECT  name,
        has_filter,
        filter_definition
FROM sys.indexes
WHERE name='fix_testtable_col1'

--give the user VIEW DEFINITION rights
GRANT VIEW DEFINITION ON OBJECT::testTable to testlogin

--switch context once again
EXECUTE AS LOGIN='testlogin'

--try and view the filter definition of the index. this time it will work.
SELECT  name,
        has_filter,
        filter_definition
FROM sys.indexes
WHERE name='fix_testtable_col1'

--don't forget to switch back to being you.
REVERT


Oracle 10 RAC VIP returns non-VIP address for client

It is even more complex when it comes to 11gR2 and SCAN listeners. But let's say that reason behind is load balancing. The listener will redirect client onto least loaded node.

Another reason can also be workload balancing. Imagine that you have two services (SERVICE_NAME) on our cluster. One called LOADERS and is used by processes which push data into the database. The other is called WEB and is used by application, which fetch data from database. Each service is located on different node. Then this redirect will guarantee that the database is used effectively and all the loaders use the same cluster node even if one of the nodes fails.

BTW: I think that the redirect message does not contain an IP address, but a hostname. Therefore there should not be any NAT between DB client and DB server and also they should use the same DNS server.


file sql.gz give error 1064 when import in phpmyadmin

extract and load sql:
gzip -d filename

or edit the phpmyadmin code to fix this permanently as shown here.


Does Postgres support stored column labels that can be queried?

You might be refereing to catalog level comments, which can be used to get a description of any table and or column in the database.

Those are created using:

COMMENT ON TABLE yourtable IS 'A good description';
COMMENT ON COLUMN yourtable.yourcolumn IS 'Column desc';

To query those, you can use this kind of query:

SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
and  c.table_schema=st.schemaname and c.table_name=st.relname)
where table_name = 'yourtablename'

Beware however that this cannot be localized.


Get data from different tables indexed by same ID

Well, you can aggregate the rows into arrays:

select p.person_id, p.name,
       array_agg(cc) as contracts,
       array_agg(pl) as plants
from people p
  left join car_contracts cc on p.person_id = cc.person_id
  left join plants pl on p.person_id = pl.person_id
group by p.person_id;

(This assumes that person_id is declared as the primary key)

Aggregating the rows into json values might be easier to work with in the front end:

select p.person_id, p.name,
       json_agg(to_jsonb(cc)) as contracts,
       jsong_agg(to_jsonb(pl)) as plants
from people p
  left join car_contracts cc on p.person_id = cc.person_id
  left join plants pl on p.person_id = pl.person_id
group by p.person_id;

To avoid duplicates, do the aggregation in a derived table:

select p.person_id, p.name,
       cc.contracts,
       pl.plants
from people p
  left join (
    select person_id, jsonb_agg(to_jsonb(c)) as contracts
    from car_contracts c
    group by person_id
  ) cc on p.person_id = cc.person_id
  left join (
    select person_id, jsonb_agg(to_jsonb(p)
    from plants
    group by person_id
  ) pl on p.person_id = pl.person_id;


Does this create a copy of the key in master DB also?

AFAIK, no.

  In case of TDE, how often does SQL Server has to contact the EKM device? Does it have to contact the ekm device veytime while reading data file from disk?

This depends on the HSM, the driver they provide which you need to have installed and loaded into the SQL Server process space but generally it will be cached for an undetermined period of time.


Need help to understand how information_schema.TABLES get updated

InnoDB is telling you that the data pages are the same size.
The DELETE you ran simply makred the rows delete within the associated data pages.
You have to run
OPTIMIZE TABLE big_data;

or
ALTER TABLE big_data ENGINE=InnoDB;

to shrink it.
Since you inserted data into a table with no indexes, that explains 0 for index_length.
The data_free column is really superfluous when innodb_file_per_table is 1 (Thta's the default for MySQL 5.6). The data_free column was used to give you the total space free inside ibdata1 when innodb_file_per_table = 0 and all data and indexes were inside ibdata1. Since innodb_file_per_table = 1 and you loaded data into MySQL from the its beginning, I believe 0 will always appear for data_free for every InnoDB table in INFORMATION_SCHEMA.TABLES.
Here is something else to consider: The INFORMATION_SCHEMA.TABLES does not properly update because of the Storage Engine's ineraction with it.
For example, I noticed you used
select count(*) from big_data;

Why did you not use
select data_length, index_length, data_free, table_rows
from information_schema.TABLES
where TABLE_NAME = 'big_data';

Using this Pictorial Representation of the InnoDB Architecture
InnoDB Architecture

try imagine what is happening when you delete 4 million rows:

Drag data pages for the 4M rows into the buffer pool
Capture those rows in the Rollback Segments
Mark the rows delete inside those memory-resident data pages
Write the changes into the Double Write Buffer
Migrate changed data pages through the Log Buffer to commit the changes back to the .ibd file
Write Redo Log Info into the Transaction Logs (ib_logfile0, ib_logfile1).
Clean up the Rollback Segments upon the completion of the DELETE.

In light of this, it would take a considerable amount of time for the INFORMATION_SCHEMA.TABLES to catch up with all these changes.
Please note that you could never reliably extract exact row counts from INFORMATION_SCHEMA.TABLES. The MySQL Query Optimizer always approximates row counts for InnoDB tables by scanning non-leaf BTREE nodes. I have written about this consistently in the DBA StackExchange for almost 3 years.

Mar 03, 2014 : The most efficient method to obtain the total entries into a mysql table
Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables? (See ASPECT #3)
Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
Aug 04, 2011 : Optimizing InnoDB default settings
Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

Therefore, it is not surprising that other metadata aspects of InnoDB do not update column info in INFORMATION_SCHEMA.TABLES effectively .
What if the table was MyISAM ???
MyISAM does not have to jump through all these hoops like InnoDB. Notwithstanding, even if the big_data table was MyISAM, the .MYD file would remain the same size after the DELETE. You would still have to run OPTIMIZE TABLE big_data; to shrink the .MYD


This article covers the topic: https://msdn.microsoft.com/en-us/library/ms178117.aspx

Basically, you have to configure log shipping from the former secondary (now primary) server to the former primary (now secondary) server.


What is the behavior of MIN_ACTIVE_ROWVERSION() on replicas in SQL Server AlwaysOn?

On further investigation, it appears than MIN_ACTIVE_ROWVERSION() only behaves correctly on the primary instance, as that is the only place the full transactions are running on.


INSERT Statement error message references column I am not including in query

The table SpotArtSong which you are trying to insert some records has at least these columns:

ID
Artist

You are just supplying a value for ID, but column Artist has a NOT NULL constraint. When you don't supply a value for a column (by omitting it from the INSERT list), NULL is inserted by default unless there is a DEFAULT constraint. Because this column doesn't allow NULL values, the insert statement fails.

Either supply a value for the Artist column (and any other column that is NOT NULL) or change the NOT NULL constraint on that column.

To supply the artist name, add the Artist column name to the INSERT and supply a value through the SELECT, like the following:

INSERT INTO [DB1].[dbo].[SpotArtSong](
    ID,
    Artist)
SELECT
    ID = SNG.S_ID,
    Artist = ART.Artist_Name
FROM
    [DB1].[dbo].[SpotArtSong] SPOT
    INNER JOIN [DB2].[dbo].[nt-art] ART ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
    INNER JOIN [DB2].[dbo].[nt-sng] SNG ON
        ART.Artist_ID  = SNG.Artist_ID AND
        left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE
    spot.BDSID is NULL

To change the NOT NULL constraint  use ALTER TABLE (note the lack of NOT NULL at the end):

ALTER TABLE [DB1].[dbo].[SpotArtSong] ALTER COLUMN Artist VARCHAR(100)

Althought it seems to me that you are actually trying to do an UPDATE instead of an INSERT:

UPDATE SPOT SET
    ID = SNG.S_ID
FROM
    [DB1].[dbo].[SpotArtSong] SPOT
    INNER JOIN [DB2].[dbo].[nt-art] ART ON ART.Artist_Name LIKE SPOT.[Artist]+'%'
    INNER JOIN [DB2].[dbo].[nt-sng] SNG ON
        ART.Artist_ID  = SNG.Artist_ID AND
        left(SNG.SONG_TITLE,10) = left(SPOT.[Song],10)
WHERE
    spot.BDSID is NULL


No, is unlikely normalization will help. What you have there looks more like missing indexes. 29 columns is not much. And BLOB columns are already stored by the system on separate allocation units (out of row), see Table and Index Organization.

  The columns in the where clause are indexed, and reindexing doesn't affect the speed so I assume the indicies are in good shape.

I recommend you read How to analyse SQL Server performance. Your indexes are not used, or not useful. For more useful answer post the exact table definition, all indexes, the exact query, and the execution plan XML.


How to circumvent ORA-01775 looping chain of synonyms in Oracle Forms?

Set the below event in the database:

alter system set events '1775 trace name errorstack level 1';

This will make all the ORA-01775 errors logged in the alert log plus a trace file, and that trace fail should contain the Current SQL statement, that caused the error.


How do I get a list of all the partitioned tables in my database?

Methinks a better query is as follows:

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id

This looks at the 'proper' place to identify the partition scheme: sys.partition_schemes, it has the right cardinality (no need for distinct), it shows only partitioned object (no need for a filtering where clause), it projects the schema name and partition scheme name. Note also how this query highlights a flaw on the original question: it is not tables that are partitioned, but indexes (and this includes index 0 and 1, aka. heap and clustered index). A table can have multiple indexes, some partitioned some not.


Gaps and Islands Across Fields With Start and End Timestamps

Inevitably I worked this out after posting and taking a bit of a break. I've also created a dbFiddle for it.

The solution appears to be to tidy up my part-solution so that it uses a common table expression (CTE):

WITH base_cte AS (
    SELECT
        latitude,
        longitude,
        generated_at AS arrived_at,
        LAG (latitude) OVER w AS lagLatitude,
        LAG (longitude) OVER w AS lagLongitude
    FROM locations
    WINDOW w AS (ORDER BY generated_at)
)
SELECT
    latitude,
    longitude,
    arrived_at
FROM base_cte WHERE
    latitude IS DISTINCT FROM lagLatitude
    or longitude IS DISTINCT FROM lagLongitude;

Then it became more obvious that I could add a second CTE which uses LEAD:

WITH grouped_cte AS (
    WITH base_cte AS (
        SELECT
            latitude,
            longitude,
            generated_at AS arrived_at,
            LAG (latitude) OVER w AS lagLatitude,
            LAG (longitude) OVER w AS lagLongitude
        FROM locations
        WINDOW w AS (ORDER BY generated_at)
    )
    SELECT
        latitude,
        longitude,
        arrived_at
    FROM base_cte WHERE
        latitude IS DISTINCT FROM lagLatitude
        or longitude IS DISTINCT FROM lagLongitude
    )
SELECT
    latitude,
    longitude,
    arrived_at,
    LEAD(arrived_at, 1) OVER (
        ORDER BY arrived_at
    ) left_at
FROM grouped_cte;

This outputs:

latitude    longitude   arrived_at              left_at
51.123456   -1.123456   2020-04-27 17:00:00     2020-04-27 17:10:00
51.654321   -1.654321   2020-04-27 17:10:00     2020-04-27 17:25:00
51.444555   -1.444555   2020-04-27 17:25:00     2020-04-27 17:30:00
51.123456   -1.123456   2020-04-27 17:30:00     2020-04-27 17:45:00
51.444555   -1.444555   2020-04-27 17:45:00     2020-04-27 17:55:00
51.654321   -1.654321   2020-04-27 17:55:00


VACUUM FULL fails with 'ERROR: Check free disk space'

According to postgresql documentation:

  VACUUM FULL actively compacts tables by writing a complete new
  version of the table file with no dead space. This minimizes the size
  of the table, but can take a long time. It also requires extra disk
  space for the new copy of the table, until the operation completes.

So if you have one big table vacuum full can easily eat all your disk space. Maybe the best thing would be to do a full backup/restore - the result will be the same as if you did a vacuum full.


Why is my database stuck in the Restoring state?

If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new.

Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests.

To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot again.


How to store/organize all of these scripts?

Get them into source control, pronto. They are your code, your source of truth about how the environments should be configured and operate. They are as important as the application and schema and deserve the same level of care and attention.

Once there you can version and branch them as required. Having 20 copies with no indication which is for what purpose will be a thing of the past.

Within the root folder I'd split them by purpose. For me this means a sub-folder for migration scripts, one for analysis, one for maintenance etc. Use what makes sense for your environment and for your way of thinking.


Why does DBMS_UTILITY.COMPILE_SCHEMA break function-based indexes?

Oracle is compiling all objects, so as far as Oracle is concerned all code in all objects is new code.

As a consequence, Oracle has no way of knowing that your function Str2Number still returns the same values that were used when the index was created.

Instead you should only recompile invalid objects,

exec dbms_utility.compile_schema(user, false);

will do this.


Based on your timestamps, the 2nd section of errors are probably caused by your connection reset error above.

As for the connection reset error, it is probably being caused by some limit configured in pentaho or your database server. Is there anything helpful in your database logs?

If its not something obvious on the db logs... I haven't had too much experience with pentaho, can you provide the job that is failing in a text format? And just checking, how many rows are in your table?


How to debug when OS kills postgres for high memory usage

It's the OOM killer in the Linux kernel. Disable VM overcommit, per the advice in the PostgreSQL manual.


Is it safe to insert the newest row with SELECT INTO?

You could kind of make it work if you included a change_id in the table, had a unique index on (account_id, change_id), and always incremented change_id by 1 from the previous value for that account_id.  Then on conflicting inserts to the same account_id, one will fail with a unique constraint violation and need to re-try.

You could do it in one insert into ... select statement.  Or you can do it in two separate statements as long as they are in one transaction, and the first statement selects both balance and change_id simultaneously.

I think it is horrible design.  But it can work.  Really you have two entities, a transaction and an account, and so should have two tables rather than trying to cram them into one.


postgresql explain analyze from sql file?

You could use cat to create a command you pass to psql. For example like this:

psql -d database -c "explain analyze $( cat myquery.sql )"

Editing a file and adding those statements at the beginning are an option too.


I need to import into mysql data from report exported by infobip

In order to solve this I ran the following to pre-process file to remove
problem chars, ��, = as well as remove all null bytes.

tail -n+2 report.csv | tr -d '\000' | tr -d "=" > ./report.fix.csv

I created the following table in mysql database:

CREATE TABLE `2016_feb_msg` (
  `SendDateTime` datetime DEFAULT '2016-01-31 23:59:00',
  `ExternalMessageId` varchar(20) DEFAULT NULL,
  `SMSCount` int(11) DEFAULT NULL,
  `AccountName` varchar(65) DEFAULT NULL,
  `Country` varchar(65) DEFAULT 'South Africa',
  `CountryPrefix` varchar(45) DEFAULT NULL,
  `Network` varchar(60) DEFAULT NULL,
  `DestinationAddress` varchar(45) DEFAULT NULL,
  `Sender` varchar(45) DEFAULT NULL,
  `PricePerMessage` DECIMAL(10,4) DEFAULT '0',
  `CreditsPerMessage` DECIMAL(10,4) DEFAULT '0',
  `ParentAccountId` varchar(45) DEFAULT NULL,
  `ParentCreditsPerMessage` varchar(9) DEFAULT '0',
  `ClientMetadata` varchar(25) DEFAULT NULL,
  `Status` varchar(45) DEFAULT NULL,
  `Reason` varchar(45) DEFAULT NULL,
  `MessageText` varchar(900) DEFAULT NULL,
  `DRArrivalTime` datetime DEFAULT '2016-01-31 23:59:00',
  `idx` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=1277493 DEFAULT CHARSET=utf16;

I used the following to load the data into the database:

LOAD DATA INFILE '/tmp/report.fix.csv'
INTO TABLE infobip_reports.2016_feb_msg
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@SendDateTime
,ExternalMessageId
,SMSCount
,AccountName
,Country
,CountryPrefix
,Network
,DestinationAddress
,Sender
,@PricePerMessage
,@CreditsPerMessage
,ParentAccountId
,ParentCreditsPerMessage
,ClientMetadata
,Status
,Reason
,MessageText
,@DRArrivalTime
,idx)
SET SendDateTime = STR_TO_DATE(@SendDateTime, '%e.%c.%Y. %k:%i:%S'),
DRArrivalTime = STR_TO_DATE(@DRArrivalTime, '%e.%c.%Y. %k:%i:%S'),
PricePerMessage = CONVERT(REPLACE(@PricePerMessage,",","."),DECIMAL(10,4)),
CreditsPerMessage = CONVERT(REPLACE(@CreditsPerMessage,",","."),DECIMAL(10,4)),
idx = null
; -- format this date-time variable


One needs to use recent enough a version of sqlcmd. The parameter for application intent is -K. Note that sqlcmd's parameters are case sensitive, which is uncommon in Windows environment.

To check sqlcmd's version, run it with -? parameter. Like so,

C:\>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 14.0.1000.169 NT
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
...
[-k[1|2] remove[replace] control characters]
...
-K application intent

For updated version, check Microsoft's product documentation,
 there's a link to Download center.


Writing SQL query equivalent to relational division of relational algebra?

The third seems correct.  The first and second strike me as wrong.  You are looking for projects where any employee contributed, not where all contributed.  In essence you are doing a division on an ANY rather than an ALL criteria.

In these cases you need to change your queries to use NOT EXISTS and look for mismatches.


Is there any reason not to use foreign keys in MySQL?

Because of the foreign key placed on the posts table with reference to the username in the users table, does that then cause not only that row in the users table to be locked but also all rows in the posts table that reference the users username to be locked as well?

If you are concerns about transaction (insert, update, delete)
Locking will occur when Mysql is trying to update some rows. In your situation, chances are you'll either update the user table OR the post table.
The fastest updates or deletes are done using primary keys.
PK in innodb are clustered (physically ordered) so it is very fast.
If you were to update a post for a user, you would first grab the user_id (select user_id from user where username =?). You would then update the post later (update post set .. WHERe userid = x). I would avoid the select for Update.
I would encourage you to use innodb for transactional application. Not only it supports FK, but support row locking and point in time recovery (to name a few).

If you system will be 90% select, few updates, or data warehouse style, then I would recommend Myisam.


How does PostgreSQL's citext type affect string comparisons made using LOWER()?

tldr; when comparing case insensitive and sensitive things for equality, you have to be explicit. text is explicitly case-sensitive; citext is explicitly case-insensitive. You should provide a cast for both sides and be explicit

A few things about lower()

lower() is typed
When it's argument are text, it always returns text

A few other points

When you do a comparison with a literal, the type isn't known (it's explicitly unknown internally).
Operators are functions.
Functions coerce the types in PostgreSQL in runtime.

In this case types are as follows, with description

-- text = unknown
-- unknown promoted to text, this has nothing to do with citext
lower(email) = 'user@example.com';

-- text = text
-- this has nothing to do with citext
lower(email) = lower('user@example.com');

-- text = text
-- this has nothing to do with citext
lower(email) = lower(lower('user@example.com'));

-- citext LIKE text
-- LIKE is smart `operator ~~(citext,text)` via `texticlike`
-- WORKS
email like lower('user@example.com');

-- citext = unknown
-- unknown promoted to citext, there is an `operator =(citext,citext)`
-- WORKS
email = 'user@example.com';

-- citext = text
-- citext promoted to text, there is no `operator =(citext,text)`
-- FAILS
email = lower('user@example.com');

In summary, there is an operator =(citext,citext). So you can

email = lower('user@example.com')::citext;

If you want, or you can define your own operator that sets = to the case insensitive route rather than the case sensitive route. I find that to be horrible practice though, I'll always cast.


Why pg_wal dir does not clean up while logical replication

archive_mode is used for for archiving, regardless of standby configuration. It can be used for point in time recovery. So when you perform DML on any table, it will generate WAL.

For cleaning up you can use archive_cleanup_command from recovery.conf


Case when not return more than one value

I think you are looking to do something like this

WHERE CP.NumIntAut IN (
                       SELECT  CASE WHEN @NumIntAut IS NOT NULL
                                    THEN @NumIntAut
                                    ELSE NumIntAut END
                                    FROM @TAB_NumIntAut )

Assuming that I understand your question correctly.  But it is worth noting that it will return the value @NumIntAut for every record in the @TAB_NumIntAut table if it not NULL. Which may or may not be what you are looking for.


Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?

Perhaps you've noticed the large discrepancy between rows and r_rows for customeritem in the second analysis.  According to MariaDB's manual, that table could use an "analyze" itself.


How can I most closely monitor this table in production to find why rows are disappearing?

If you don't expect any rows to be deleted from this table, then the simplest, least impact way to catch anyone running deletes would be a delete trigger. You can use an INSTEAD OF trigger if you want to log and prevent, or an after trigger if you just want to log. First, a logging table:

CREATE TABLE dbo.CatchTheDoNoGooders
(
  [JOB_NAME] [nvarchar](200),
  [JOB_GROUP] [nvarchar](200),
  [SCHED_NAME] [varchar](120),
  UserName SYSNAME,
  HostName SYSNAME,
  Program NVARCHAR(4000),
  IP VARCHAR(15),
  EventDate DATETIME
);

Now, an INSTEAD OF INSERT trigger, could log the DELETEs but not carry through:

CREATE TRIGGER dbo.InsteadDelete_QRTZ_JOB_DETAILS
  ON dbo.QRTZ_JOB_DETAILS
  INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ip VARCHAR(15), @hn SYSNAME, @prog NVARCHAR(4000);

  SELECT TOP (1) @ip = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID;

  SELECT TOP (1) @hn = [host_name], @prog = LEFT([program_name],4000)
    FROM sys.dm_exec_sessions
    WHERE session_id = @@SPID;

  INSERT dbo.CatchTheDoNoGooders
  SELECT JOB_NAME, JOB_GROUP, SCHED_NAME,
    SUSER_SNAME(), @hn, @prog, @ip, CURRENT_TIMESTAMP
    FROM deleted;
END
GO

Change INSTEAD OF to FOR and now you have an AFTER trigger that will still allow the delete to happen, but will log them all anyway. This can be better if you do have some legal deletes going on but want to determine who is deleting the wrong things.

The beauty is that in both cases this only has any impact whatsoever if and when a delete is actually issued against the table.


Cluster IP's under Access Point for Administering the Cluster

Please see No such thing as a Heartbeat Network and Everything You Know About Clustering is Wrong.  These don't really answer your question, but perhaps will change the question to, "Do we need a heartbeat network?"

With Windows 2008 and later, a heartbeat network is not required, and it's really only going to benefit if your "public" network gets so saturated that cluster communications are delayed, in which case the "heartbeat" network may stop a failover from occurring (ref. Shanky's comment below).  This was a nice safeguard that made more sense in the days of 10/100 Mbs networks, and although it's certainly possible to saturate a 10 Gps link, it's far less likely.  I.e., if you anticipate having major issues with your public network, then a private network might ease some of the pain, but you'll still need to address the public network issues to cure the disease.

Additionally, the use of file witness and majority node quorum features additionally reduces the need for a private network for cluster stability.  Many clusters for availability groups now span geographic regions where it doesn't make sense to bridge your network just for the purpose of putting the DR node on the same subnet.  Again--if it added a huge amount of value, it might make sense, but the benefit just isn't there.

The monitoring built-in to availability groups can initiate failover even if the cluster doesn't detect any problem, and I don't think it would care about a private network as it would only care about the route it is using for replication.  Hopefully someone can post a reference to how AGs might benefit, or not, from a private network in different scenarios, but I'm guessing that it doesn't exist because the private network has not been required for 10 years now.

If you think that the replication of the database will put significant load on the public network, you can use a private network for the replication by setting the endpoints to the private addresses.  See Configuring a Dedicated Network for SQL Server Always On Availability Groups Data Replication Traffic.  Yes, I know there is a link in the article to a previous article advocating a private network.  My point is that if your public network is so bad that you need a private network, you are probably going to have a lot of failovers and other issues.  It's like buying insurance for a leaky boat--great, you've got insurance, but the boat is still going to sink.


psql "invalid client_encoding" error on OS X, PostgreSQL 9.4.5

client_encoding is a libpq connection option that was added circa 2011 and will not be recognized by older versions.

The error:

  psql: invalid connection option "client_encoding"

comes from dynamically linking a newer psql with an older libpq, for instance the one that ships with the system.

On Mac OS X, this can be worked around by setting DYLD_LIBRARY_PATH (the equivalent of linux's LD_LIBRARY_PATH) to the path of the newer PostgreSQL installation.

Per comments and update, it's confirmed to work when setting this variable globally in /etc/profile


Moving data into an archive table and reducing the size of the original table

If allocating space or another drive is impossible, then there might be ways you can do this with minimal growth, assuming you can put the database into RESTRICTED_USER mode or otherwise prevent users/applications from trying to modify the data while you fix it.

First off, don't shrink your data or log files at this point. You'll free up space on the disk temporarily, but the files will just grow again - so as with most shrink operations this is pointless.

Next, take one of the char(10) columns. Ensure that you know the largest string contained:

SELECT MAX(DATALENGTH(CONVERT(varchar(10), char10_column)))
FROM dbo.table;

Add a new, nullable, varchar(that length) column. (I haven't supported 2005 in some time, so I forget if this is fully or partially online - in theory, this shouldn't be a size-of-data operation, but I don't know if 2005 was smart enough to not treat it as one.)

ALTER TABLE dbo.table ADD newvarchar10_column varchar(10);

In small chunks, as I describe here for deletes, update that new column to have the value of the old column, and set the old column to NULL. This will add to the table size gradually, but will have minimal impact on the log.

UPDATE TOP (?top?) dbo.table -- not sure what your ?top? sweet spot will be
  SET newvarchar10_column = oldchar10_column,
      oldchar10_column = NULL
  WHERE oldchar10_column IS NOT NULL;

-- CHECKPOINT / BACKUP LOG

Once the data is all located in the new column, dropping the old column should be minimal work for the database, since there isn't any actual data to log. If this column is involved in any indexes or constraints, or schema-bound views/functions, you'll have to deal with those first.

ALTER TABLE dbo.table DROP COLUMN oldchar10_column;

Then rename the new column so that it look like the old column.

EXEC sys.sp_rename N'dbo.table.newvarchar10_column',
       N'oldchar10_column', N'COLUMN';

Don't forget to update any procedures or code that still think this column is a char(10).

This is just a theory. You might want to test it on a non-critical system first to test the effects on data/log files, figure out the sweet spot for ?top?, etc.


Does partitioning help with lookups and inserts/updates by primary key?

Your questions

Q1:  If PK is the Partition key, a "point query" (WHERE PK = constant) on the Partition key will do "pruning", then (hopefully) use an index in the single partition it found.  No improvement over having a simple table with a suitable index.
Q1:  If PK is the PRIMARY KEY, but not the Partition key, then the query must open and look in every partition to find the row, hence slower.

Q2:  Generally IN ( SELECT ... ) performs poorly, very poorly in some cases, and should be avoided.  Use a JOIN instead.  For partitioning, "pruning" will probably not be used.  Hence slow.

Q3:  INSERT may be about the same speed, regardless.  A plain table may be faster due to less complexity.
Q3:  REPLACE is DELETE + INSERT.  INSERT ... ON DUPLICATE KEY UPDATE ... is usually a better construct; see if it applies.  But, again, no performance improvement.

IN

WHERE PK IN (1, 2, 3, 4...) -- I think I heard that if the list is 'short', it will use partition pruning.  Or skip the pruning if the list is long.  In either case, a simple table with an index on PK will be at least as fast.

Pruning

Think of pruning this way:  First it must find the partition (which acts very much like a "sub table"), then it must use an index (if available) to drill down to the desired row(s).  Without partitioning, it skips the pruning step; but the BTree index is slightly deeper.  So it is a trade-off.

Note:  This often means that the optimal set of indexes for a partitioned table and the equivalent non-partitioned table are different.

Case 3

The caveat "A table's index is too big to be cached, but the index for one partition is cacheable" does not seem to apply to your situation.  Case 3 came from a time-series wherein most of the activity was in the latest partition (and PARTITION BY RANGE(TO_DAYS(...))) and that partition fit in the buffer_pool but the entire table did not.

(That is, I agree with Natan, not Jarwad, about the relevance of Case 3.)

BY HASH

BY HASH -- not useful.  (From the link:  "PARTITION BY RANGE is the only useful method.")  (Or at least, I have yet to see a use case of Hash where performance benefits.)

Ingestion

"growing a few million per month" That's not very fast.  "growing a few million per day" would begin to be challenging, at which point I would point you at my high speed ingestion blog .  Even so, you might take one tip from there -- Load the changes into a transient table, then do the insert/replace/iodku/update/etc from it to the 'real' table.


How do I use the unload command to export a table to a IXF file

Ah ok.

If you are referring to the UNLOAD command, that is only available on DB2 for z/OS. DB2 LUW does not have an UNLOAD command....except if you purchase IBM's separate product Optim High Performance Unload. (I am assuming you are wishing to use that?)

Optim High Performance Unload uses the db2hpu command. And though we do not use the tool where I work, it appears that the syntax you are after is the following:

db2hpu -t 'S.T' -d <database-name> -format ixf -m 'filesystem/messages.MSGS' -o 'filesystem/myfile.IXF'


psql: FATAL: role "postgres" does not exist

Not a full answer, but consider the manual about initdb here:

-Uusername
--username=username

  Selects the user name of the database superuser. This defaults to the
  name of the effective user running initdb. It is really not important
  what the superuser's name is, but one might choose to keep the
  customary name postgres, even if the operating system user's name is different.

Meaning ...

When running initdb as OS user postgres, the initial DB superuser role is postgres as well - in standard Postgres. I have heard of systems bending this rule ...
You should be able to force the name postgres with --username=postgres for the initial superuser - unless your package seriously messed with all this.


What operations result in automatic creation of SQL Server statistics?

AUTO_CREATE_STATISTICS Option

When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan.
These single-column statistics are created on columns that do not already have a histogram in an existing statistics object.
When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.
You can use the following query to determine if the Query Optimizer has created statistics for a query predicate column.
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

That includes WHERE predicate:
SELECT *
FROM tab
WHERE col = ?;    -- here tab.col

and JOIN predicate(to determine physical join type: NESTED LOOPS/MERGE/HASH):
SELECT *
FROM tab1
JOIN tab2
  ON tab1.id = tab2.tab_1_id; -- here tab2.tab_1_id


How to index dynamic attributes in MongoDB

I also asked this same question (in a bit expanded form) on the mongodb-user mailing list, where I got an answer. Read from there to get more details. The short answer is, that the strategy used in the question should work fine, but there's an issue that makes it very inefficient. Hopefully, the issue will be fixed soon.

For my case, I only need to query for exact matches for the tuple {n,v}, so I can create a multikey index:

db.mycollection.ensureIndex({"attrs":1})

and make they query like this:

db.mycollection.find({"attrs": {n: "subject", v: "Some subject"}})

which works great and uses the index very effectively.


How can I efficiently delete records when a counter reaches zero in PostgreSQL?

Targeting a single row, this avoids an "unneeded record change", i.e. writing a new row version without need:
DO
$$
BEGIN
   DELETE FROM tbl WHERE … AND counter = 1;  -- common case first!

   IF NOT FOUND THEN
      UPDATE tbl SET counter = counter - 1 WHERE …;
   END IF;
END
$$;

Should also be cheaper than a trigger solution, where a trigger function is executed for every affected row (and may or may not interfere).
However, this will not fly with possible concurrent write operations. When two or more transactions try the same at virtually the same time, the logic can break. Or additional rows could become visible in between the two commands in default READ COMMITTED transaction isolation.
This is inherent to the problem itself, rather than to my solution. (Applies to other solutions all the same.) Under concurrent write load, you'll have to at least write-lock the row to avoid race conditions, so you are back to finding the row twice. You can avoid writing rows without need (like UPDATE + DELETE) in any case, though.


Insert into a DB that was selected via other DB?

What you need is some Dynamic SQL.

STEP 01 : Retrieve the DB

SET @IMEI = 123456789123456;
SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI;

STEP 02 : Create the SQL to do the INSERT into the retrieve DB

SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"');
SELECT @sql\G

STEP 03 : Execute the SQL Statement you just created

PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

EPILOGUE

Execute them in that order

SET @IMEI = 123456789123456;
SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI;
SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

GIVE IT A TRY !!!


The keyboard combination you're after (Ctrl+K, Ctrl+D) is for "formatting" - but not to the extent you seem to expect. It is not a prettifier, it is just used to insert correct spacing and tabs as found in Tools > Options >  Text Editor > Transact-SQL > General/Tabs - so if you highlight a portion of text and hit the keyboard combination, it is supposed to convert tabs to 4 spaces (if you've selected insert spaces), apply the type of indenting you've specified, etc.
This option is NOT intended to make code more readable - this is not functionality that Management Studio currently offers natively. Though there are several 3rd party options - some are external to Management Studio, like:

Simple-Talk's SQL Prettifier
Poor SQL (which Thomas suggested)

And there are add-ins as well for various levels of formatting assistance within the editor:

Red-Gate's SQL Prompt
SSMS Tools Pack
ApexSQL's Refactor
SSMSBoost
dbForge SQL Complete (which Robert suggested)

Now, the reason you are getting the message...

The key combination (Ctrl+K, Ctrl+D) is bound to command (Format Document) which is not currently available.

...is because SSMS has mapped that key combination to a different context. The way you should be able to "fix" this - again, it still wouldn't do what you want it to do, even if the "fix" worked - is by doing the following:

Go to Tools > Options > Environment > Keyboard

Place your cursor into the Press shortcut keys: box

Hit Ctrl+K, Ctrl+D

Change the Shortcut currently used by: drop-down from DataWarehouse Designer to Text Editor

Press OK

Now, this is supposed to map the keyboard combination to the text editor, but Management Studio reverts it after hitting OK (you will continue to get the error message. So, I think the problem is that the documentation believes this functionality exists, but Management Studio knows better and simply doesn't offer it (and Microsoft will likely write this off as a bug in the documentation, and correct it, rather than a shortcoming in the tool). There may be hope in the future but, for now, this is a known and largely ignored issue. You'll note that the Formatting tab that the documentation refers to is simply not present (though it is there for XML, where the keyboard combination does work). The documentation should probably state:

Applies the indenting and space formatting for the language specified on the Formatting pane of the language in the Text Editor section of the Options dialog. Available only in the text editor and only for certain languages.

Another way to get an idea of what type of formatting options SSMS offers natively is to go to Tools > Customize > Commands > Edit > Add Command... > Format and look at the list of possible commands there. Nothing that indicates there is any knowledge of the actual language, so it wouldn't know where to insert line breaks or add additional indents or assist with parentheses etc.

If you want language-specific formatting to make existing T-SQL code more readable, you won't get much from SSMS, and you'll need to seek other options.


MySQL, wait_timeout, Mac OS X Mountain Lion - how do i set it?

I believe you're running into a few different problems:

1) For interactive clients like the mysql client program (and not for most web applications), session wait_timeout is initialised from interactive_timeout when your client connects, so changing the global wait_timeout has no effect on the effective wait_timeout for your mysql client. This is covered in the MySQL documentation here:

  On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

2) You're changing a global setting (set global ...) and then looking at session variables ('show variables' instead of 'show global variables')

3) Session variables are only reinitialised from global variables when you reconnect

For the solution, the following should set the effective wait_timeout for your mysql client to 333:

set global wait_timeout=333
set global interactive_timeout=333

Then disconnect.

To persist the changes across restarts, you can add the following to the [mysqld] section of my.cnf:

wait_timeout=333
interactive_timeout=333

If the reason you're making these changes is because of a problem a client that doesn't set CLIENT_INTERACTIVE is having, then you can disregard interactive_timeout.

Before making changes, I recommend reviewing this blog post and the MySQL documentation to ensure you understand how these settings function and the impact of changing them.


Applying patch oracle 11.2.0.4 on 11.2.0.2

Using the Pre-Upgrade Information Tool

Important:

The Pre-Upgrade Information Tool must be copied to and must be run from the environment of the database being upgraded.

...

Run the Pre-Upgrade Information Tool:

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql


SELECT COUNT(*) returns no data SQL Server 2012

Is there a class of error that will thrown an exception out of the
  query, but not raise it to the application when executing a command or
  reading from the resulting SqlDataReader?

If the query errs in the scope of T-SQL structured error handling, the empty or partial result set is returned to the client followed by the error message in the TDS protocol stream. The implications are that client code should not assume SqlDataReader always returns true and that all rows and result sets must be consumed before the exception is raised in the client code. Consider the following example:

USE tempdb;
GO
CREATE PROCEDURE dbo.usp_ExampleError
AS
SET NOCOUNT ON;

BEGIN TRY

    SELECT COUNT(*)
    FROM (VALUES('NotAnInteger')) AS Example(BadData)
    WHERE BadData = 0;

END TRY
BEGIN CATCH
    THROW;
END CATCH
GO

using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"))
using (var command = new SqlCommand("dbo.usp_ExampleError", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        Console.WriteLine(reader.Read()); //returns false
        Console.WriteLine(reader.NextResult()); //raises the conversion error
    }
}

Note that ExecuteNonQuery implicitly reads and ignores all rows and result sets (which will raise an exception if the batch erred) but ExecuteScaler and ExecuteReader do not. Also, DataAdaper.Fill consumes all results with a DataSet target overload but not with the DataTable overload,

This is one reason I suggest one generally avoid handling errors in T-SQL unless it provides value and specify SET XACT_ABORT ON to avoid continuing after errors and leaving transactions open.


According to rule to find median(average if even number of values & middle number if odd number of values). It can be done like this.

CREATE PROCEDURE dbo.spAvg_Median
    @input nvarchar(20)
AS
BEGIN
    declare @split table(id int primary key identity (1,1), number int)
    declare @count int

    INSERT INTO @split SELECT value FROM string_split(@input, ',')
    select @count=count(*) from @split

        SELECT AVG(A.number) as [Average],B.Median
            FROM @split A
        CROSS APPLY
                    (
                    SELECT Median FROM (
                    SELECT
                              o.number AS [Median]
                            , rn=ROW_NUMBER() OVER (ORDER BY o.number ASC)
                    FROM    @split AS o
                    ) AS x
                    WHERE rn =ROUND(@count/2.0,0)
                    ) B
        GROUP BY B.Median
END
RETURN 0
GO

Thanks!


MySQL - Can we use the same temporary table name in different stored procedure?

in my case all the sp are running on the server side

Any case the SP is executed using some client connection (from Event Scheduler, for example). If they are called one-by-one from some outer SP/UDF - they are executed in the same connection, and temp table created by 1st SP exists when 2nd starts. If they are executed independently (for example, as/from 2 separate event procedures) - they are executed in different connections, and each have its own independent (local) temp table.

Server only accept queries and returns its results. Anything that sends queries and receives its results is client. See SHOW PROCESSLIST - you will see all client connections, including Event Scheduler service connection and executed events connections, separate connection for each separate event.

  Will the temporary table be automatically destroyed after one SP is finished?

Temporary tables have a connection scope. If you execute 2 SPs in two different connections, each have its own temp table with its own structure and data despite the fact that their names are the same.

If you execute one SP then another, but close connection after executing 1st and create new for executing 2nd - each SP will have its own temp table again.

But if you execute one SP then another in the same connection then temptable created by 1st will exist while 2nd start, with the data inserted by 1st. - akina


You need two joins here, one with the "matching" type set, another with "default" type.

I'm making two assumptions:

"Missing" p_type is an empty string, not null, as follows from your query.
There is at most one row in t_price for each p_route and empty p_type.

:

select r_route, r_type, coalesce(match.p_price, dflt.p_price)
from t_route
left outer join t_price match
  on (r_route, r_type) = (match.p_route, match.p_type)
left outer join t_price dflt
  on r_route = dflt.p_route and dflt.p_type = ''

dbfiddle link (It would be nice if you'd included it yourself).


Can SQL profiles be deployed to other Oracle Databases?

SQL Profiles are basically a kind of statistics rather than a database object like an index, but they can be transported to another database - see this SF question for how


Normalising a database for monitoring stations

Yes, you can. However, some database implementations allow for enum or enumerable types, so if your type of station can come from just a small list, it may be better using an enum types sir, which has a similar effect to having a lookup table but runs much faster.
Also, you may not need a station I'd. Primary keys can be made of multiple columns and I don't know, but I would guess that there are no stations with the same name and type. If this is the case, you can make the primary key composed of both he'd columns!
Search for primary composite keys in your database implementation for how to do this, but in SQL it is done like so:

name VARCHAR(100) NOT NULL
type VARCHAR(10) NOT NULL
PRIMARY KEY (name, type)

Although bear in mind that your full create statement will have more information, and you may not want those lengths for your text.


Dynamic SQL UNPIVOT and putting results into existing table

Take away the text INSERT INTO dbo.WEEKLY_SALES... and everything after it on that line. Then change SELECT src.AREAS to:

SELECT INTO dbo.WEEKLY_SALES
src.AREAS, src.COMPANY ...etc

Also, before your SET @query statement, add:

DROP TABLE dbo.WEEKLY_SALES

That way, when it runs, it will drop yesterday's values (the entire table definition as well).  Then the rest of the statement will create and load the table on the fly.

Honestly, if you're going to truncate each time anyways, you might want to consider creating the table dynamically using Select into. Basically just drop the table first, then add the into [table name] after the select keyword.  In terms of straight easy coding, that's as simple as it gets. It won't give you the best performance on reading the table though because there won't be any indexes. That's really the only downside.


innodb_buffer_pool_size, optimize and mysqltunner

innodb_buffer_pool_size

No; wrong approach.

Simply set innodb_buffer_pool_size to about 70% of available RAM.

If you make it too much larger than that, you risk swapping, which hurts performance terribly.

If you make it too much smaller than that, your queries run slower.

If you have a lot more data than that, you will simply hope that the "working set" is smaller, and the performance will still be good.

If you have a lot less data than that, you are wasting some RAM that you won't be using anyway.

Fragmentation

Bogus.

There are at least 3 ways in which InnoDB fragments.  The available metrics measure only one of them.

Fragmentation has very little to do with performance.  So, as long as you are not threatened with running out of disk space, don't defragment.  Furthermore, promptly after defragmenting, the freed up space will be quickly gobbled up.  The encouraging thing is that the used space will stop growing as fast after it gets to some steady state.

Catch 22:  If you are about to run out of disk space, you probably can't run OPTIMIZE because it needs space to run!

OPTIMIZE TABLE

Ignore their advice; never use it for InnoDB tables.

It copies the table over, thereby removing any "free" space because the copying compacts things better.  But, I repeat, this only squeezes out some of the "free" space.

Well, OK, there are rare cases where OPTIMIZE is useful.  However, MysqlTuner "cry's wolf" a lot on this item.

innodb_buffer_pool_instances

Simple; don't worry.

Just set it to an integer that is innodb_buffer_pool_size/1G, but no less than 1 and no bigger than 16.  No risk.  But, also, virtually no gain.  In a very busy system, it may speed things up by only a few percent.  If you have a very busy system, you need to be looking at other ways to avoid falling off the cliff.

The application probably does not mention it because either (1) they did notice it when it was added (MySQL 5.5), or (2) they realized it did not matter much.


So you want one row for each (employee,date), with shift details filled in for days on which the employee has a shift?

For this kind of problem use the "cross join, left join" pattern.  Start with a cross join that establishes the granularity of the result set, and then left join to the matching rows.  Something like:

SELECT
    e.emp_id,
    e.emp_number,
    a.day_date,
    ea.area,
    ea.shift_type,
    ea.start_time,
    ea.finish_time
FROM Employees e
CROSS JOIN AvailableDates a
LEFT JOIN EmployeeAvailability ea
   on a.AvailableDate = ea.day_date
   and e.emp_id = ea.emp_id


Most major DBMS products today have facilities for this: it may be called

Change Data Capture (Oracle, SQL Server)
logical replication (Postgres, MySQL)

Most of these facilities operate on the publish/subscribe model and build upon the DBMS's built-in transaction logs to provide a stream of change events. Often the amount of logging will increase slightly because the primary key has to be included in the stream rather than just physical information about which blocks have changed.
Organisationally, you may encounter resistance to implementing things like this with conservative DBAs and organisations (I have encountered this several times when trying to implement Oracle CDC), while it's not likely to be an issue where they have switched to, or are using open-source DBMSes (more  open to change).