I can't connect to my servers SQL database via an IP Address

Your SQL Server is installed as named instance, so first of all try connecting to your server using the following server name: IP Address\SQLEXPRESS.
When you install SQL Server as named instance it uses dynamic TCP/IP ports by default, so it is not possible to connect to it whitout specifying instance name (just IP address). If you need to connect to your server without using instance name you have to reconfigure your server to use static TCP port. To do it please perform the following:

open SQL Server Configuration Manager;
switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;
double-click the TCP/IP protocol;
select the Yes value in the Enabled field;
switch to the IP Addresses tab;
find the IPAll section;
clear the TCP Dynamic Ports field in that section;
specify the 1433 value in the TCP Port field:

restart your server
Try to connect to your server using just its IP address.


How to block external connections for an Oracle database?

The solution is really dependant on your environment.  There are numerous options, from hardware firewalls, to software firewalls, to sqlnet.ora parameters.  The first two are beyond the scope of this site.

The quickest, probably, is sqlnet.ora.

Have a look at the tcp.invited_nodes parameter:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF237

This goes hand in hand with the tcp.validnode_checking parameter:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF238


SQL Server 2012 doesn't use enough memory

Be sure that memory is the bottleneck

Here is a query that will tell you for every session, how much memory is being used versus the ideal amount of memory the optimiser would like to assign to the query. If the latter is higher than the former, then memory is a (but not necessarily the only) issue for you.

SELECT  s.session_id
        , r.granted_query_memory * 8 AS memory_used_kb
        , a.ideal_memory_kb
FROM    sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_query_memory_grants a ON a.session_id = s.session_id


Tempdb growing, however it has a lot of free space

What is the autogrow model for the TempDB files? Tiny increments will incur many small autogrowths whereas large increments will incur fewer, but more intensive, growth routines.

Presumably TempDB is growing because queries that are running on the server are constructing a lot of temporary tables, table variables, or cursors and so on?

Is it known what queries are running, and do you have historical baseline to compare the autogrowths and sizes to?

If things have suddenly changed, and the organisation has not grown the amount of actual data it uses significantly, I would guess that a developer or reporting user has changed the way in which they run their queries.

I think we need a bit more information from you, regarding the above.


SQL 2008 R2 Datafile Too Large To Empty or Remove

Shot in the dark without any information on how the database is structured i.e. what tables are on what filegroups, is there an existing partition scheme?.

How about rebuild the tables on the big filegroup to one of the other existing filegroups, or a new smaller one?

CREATE UNIQUE CLUSTERED INDEX PK_MyTable ON dbo.MyTable
(
<column list>
)
WITH (DROP_EXISTING=ON, ONLINE=ON)
ON <new filegroup>


A partition can again be a partitioned table, so using subpartitions you can partition a table in two different ways:
CREATE TABLE calendar (
   id bigint NOT NULL,
   day date NOT NULL
) PARTITION BY RANGE (day);

CREATE TABLE calendar_2020 PARTITION OF calendar
   FOR VALUES FROM ('2020-01-01') TO ('2021-01-01') PARTITION BY LIST (id);

CREATE TABLE calendar_2020_a
   PARTITION OF calendar_2020 FOR VALUES IN (1, 2, 42);

Make sure to test carefully if you get the desired performance benefits; you probably have to turn on enable_partitionwise_join.
Also make sure that you use a recent PostgreSQL version (≥ x12) and that you don't end up with thousands of partitions.


right join leaving one row from right table

You are grouping by the thing you are counting, not by the department name.
Change your group by to:
group by b.deptname

Edit:Adding a little flavor text as requested.

Basically what is happening is you are choosing to aggregate one of the values (in this case the counts of the salary) so that it "rolls up", and the group by generally indicates which value you want to do the rolling up by.

It makes some sense to say "I want to group by the number of employees" but you are actually trying to express "I want to return the number of employees grouped by department."


I learned how to enable general logging on the db and i found a possible issue with the wordpress theme being used. We will be replacing this soon anyway so hopefully there won't be any issues moving forward


What does it mean when Postgres query state is null

It means that the session with pid 2036 belongs to a different user, and you don't have the privileges to see that data.

Consider that the queries run in that session might contain data that you have no right to see.

You must either be a superuser or (in recent versions) be a member of the pg_read_all_stats role to see these data.


The easiest way to import a complete schema to the database would be to drop the existing user/schema beforehand.

A simple:

drop user percon cascade;

...should be enough to have all objects that are related to your schema percon to be dropped before you continue with the import.

When you run the import you could just define the schema to import as suggested in a comment:

imp fromuser=percon touser=percon file=yourdump.dmp log=imp_percon.log

This should import all relevant percon schema/user data. If any errors occur you will find them in the log file.


clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server

It looks like something went wrong when you upgraded. You have to check that the port and listen_addresses are correct. Perhaps the listen_address changed and the instance isn't listening on the host anymore?

The reason that pg_dump and the other tools are still working is probably because they're connecting over the unix socket instead over TCP/IP. clusterdb should also be able to do that if you just leave out the -h and -p options.

The pg_hba.conf is not relevant because your client can't connect to the server at all. You can verify that the server is listening on the expected host and port with telnet <host> <port>.

http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html


Sql server 2014: backup log to 2 destinations

I've never used SSIS to use the backup task so I'm not sure exactly how great this solution is however it should theoretically work and it took me about 10 minutes instead of 10 days.

First off create a package and make your backup database task. Set it up however you need to set it up like this.

Then make a for each file loop and have it search through the directory you're adding it to.

Map the results to a variable.

Then use file System tasks to move it wherever the hell you want.


disallow SET command in a postgresql server

I agree with Tometzky, just want to add some thoughts.

Firstly, if I had to publish my DB server, I would separate it from my own servers (or server processes) - either physically, if possible, or putting the public DB server into a VM, which is constrained in its resource consumption to a degree when it can't really affect other processes on the same physical machine.

Secondly, limiting changes to resource-related configuration parameters can be only of limited use.  One can set work_mem, for example, to a fairly high value, and make huge sorts, but this will not affect the overall server performance more than, say, a four-way Cartesian product of one million rows each - which you can't prevent by any means...

And, unfortunately, I don't know any means of modifying a session's settings from an other session (be the latter connected with the postgres user).  You can't even really see (to my knowledge) the changed settings of the other session.  One possibility to control the usage of the public servers is checking resource consumption at the OS level, and kill the processes that show signs of abuse.  This is, of course, not really friendly, but may be useful as a last resource.


Connecting to Oracle Pluggable database using service name

It registers them automatically.

Very easy to test...

Listener status shows no PDB:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:41:15

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-MAR-2015 15:03:10
Uptime                    8 days 18 hr. 38 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$ echo $ORACLE_SID
CDB1
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0

Create a new PDB:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 13 09:41:27 2015

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

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

create pluggable database pdb_phil
admin user phil identified by phil
  3  file_name_convert = ('/pdbseed/', '/pdb_phil/');

Pluggable database created.

SQL> col name for a20
SQL> col open_mode for a10
SQL> select con_id, dbid, name, open_mode from v$pdbs;

    CON_ID       DBID NAME                 OPEN_MODE
---------- ---------- -------------------- ----------
         2 4096360259 PDB$SEED             READ ONLY
         3 3580684491 PDB_PHIL             MOUNTED

SQL> quit

It is now registered with the listener:

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:44:06

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                04-MAR-2015 15:03:10
Uptime                    8 days 18 hr. 40 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
  Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "pdb_phil" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$


Why does a fresh SQL Server instance with the same details as the original think that my DB backup is in the original location?

The .\ is not interpreted in the Query parameter as 'current directory' and so SQL looks for the file in its default backup location. You have a couple of options

Change the Query value and specify the full path to the file
Pass a variable into the Query string (see below)

Option 1 is the simplest, but you may have need of using variables elsewhere, so here's an example:

$StrArray = "VAR1='C:\Temp\db-name.bak'"
invoke-sqlcmd -query "RESTORE HEADERONLY FROM DISK = `$(VAR1)" -Variable $StrArray

$StrArray is an array of strings that take the form Variable Name='Variable Value' and each is substituted in your query string at run time.

Invoke-SqlCmd


Why does this procedure raise a privilege error?

You need to examine the privileges of the connect user. The next time you connect run this:
SHOW GRANTS FOR CURRENT_USER();

This will tell you two things:

How you you were allowed to authenticate
What privileges you have with that user

Look at the beginning of the Stored Procedure definition
CREATE DEFINER = 'root'@'localhost' ...

The fact that it says DEFINER (the SQL Security) means that you assume the privileges of 'root'@'localhost' when you call the Stored Procedure. Nevertheless, there is a catch: you need the EXECUTE privilege. Why ?
According to the MySQL Documentation on Stored Procedures:

Definer and invoker security contexts differ as follows:
A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.
A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.

Therefore, whatever you get as CURRENT_USER(), simply grant EXECUTE to that user. For example, if CURRENT_USER() is 'myuser'@'somedb', login as 'root'@'localhost' and run
GRANT EXECUTE ON *.* TO 'myuser'@'somedb';

Then, that user can run any Stored Procedure. If you want to restrict it to just the Stored Procedures in the medilife database, then do:
GRANT EXECUTE ON medilife.* TO 'myuser'@'somedb';

Give it a Try !!!


Well, a couple things.

GETDATE() returns a datetime value, which has a valid range of January 1, 1753, through December 31, 9999. 1753 is when the civilized (or is it civilised?) world switched to the Gregorian calendar from the Julian one. So you won't be able to have it return the date you're after; it's out of range.
You can use SYSDATETIME() to get you there, because it returns a datetime(2) value. However, it won't necessarily be any cleaner code-wise than using CONVERT on that string. You'd have to throw some weird date math at it, like SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, SYSDATETIME(), '00010101 12:00:00.0000000'), SYSDATETIME()).

If this is just going to be a canary value, you're better off setting it as the column default value.

ReferralDate DATETIME(2) DEFAULT '00010101 12:00:00.0000000'

If you need to update, it might be a little simpler to set it in a variable and then update using the variable.

CREATE TABLE #crap (Id INT IDENTITY(1,1), Dt DATETIME2(7))
GO

INSERT #crap ( Dt )
    VALUES ( NULL)
GO 1000

SELECT *
FROM #crap AS c

DECLARE @dt DATETIME2(7) = '00010101 12:00:00.0000000'

SELECT @dt

UPDATE c
SET c.Dt = @dt
FROM #crap AS c
WHERE c.Dt IS NULL

SELECT *
FROM #crap AS c


Connecting SQL Anywhere with Sybase database file on local computer

Here is a way to reset the password using the free SQL Anywhere 17 Developer edition. The paid edition should work also.
"C:\Program Files\SQL Anywhere 17\bin64\dbsrv17.exe"  dbsrv17 -orp "UID=DBA;NEWPWD=newpassword;AUTHUID=reset_user;AUTHPWD=sql456" C:\database.file.db


How can I trigger this stored procedure on insert?

I figured out that the following lines of code weren't allowed to run in a trigger:

CREATE TABLE IF NOT EXISTS interactionrecord
      (`datediff` INT);
   TRUNCATE TABLE interactionrecord;

I had had them in there for debugging purposes and forgot to remove them.

Once I removed them, I could run the code as a trigger, until I tried updating the error table, since apparently updating the table the trigger is on isn't allowed either.

What I'm doing now is a trigger which simply inserts new error Id into a small table, then having an event run every minute or so, calling my stored procedure on all the newly inserted errors.


Mysql error "#1030 - Got error -1 from storage engine" when doing insert/update queries

You will notice in the errors this message

  InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database
  modifications by the user. Shut down

So please remove the innodb_force_recovery option from my.cnf


Activity Monitor Showing Long Duration for WAITFOR

This is a Service Broker process listening to a queue for messages. 300 seconds will be the time out specified in @rec_timeout. See RECEIVE reference in books online.


SSIS package succesfully completes but does not import 100% of records from flat file into table

Yesterday when I was working with the file I went to the preview tab and I set the Data rows to skip to around 45k so I could preview what some of those rows were looking like.

I was under the impression that the Data rows to skip value would only affect the preview and not actually be used at run time.

Setting the Data rows to skip back to 0 made it so all the records were once again being imported into the table

Some of the other things I did while trouble shooting which might help someone else who is having a similar issue  but due to a different reason can be found here:

ssis-text-file-import-skips-some-rows


Is this table-backed-queue implementation safe?

Check out the locking that occurs in your stored procedure - the following is a simple example, you can adapt it to test your query and others.  In case it needs to be clear, go to a quiet server for this, like a development enivironment or better yet, a local install.  The last thing you want to be doing is holding transactions open for giggles in a production environment.

USE tempdb;
GO

IF ( OBJECT_ID( 'dbo.TestTable', 'U' ) IS NULL )
BEGIN
    --DROP TABLE dbo.TestTable;
    CREATE TABLE dbo.TestTable
    (
        Foo                 INTEGER,
        BAR                 INTEGER
    );

    INSERT  INTO dbo.TestTable( Foo, Bar )
    SELECT  TOP 10 1, 1
    FROM    sys.objects;
END;
GO

IF ( OBJECT_ID( 'dbo.TestProcess', 'P' ) IS NULL )
BEGIN
    --DROP PROCEDURE dbo.TestProcess;
    EXEC( 'CREATE PROCEDURE dbo.TestProcess AS SET NOCOUNT OFF;' );
END;
GO

ALTER PROCEDURE dbo.TestProcess
AS BEGIN
    SET NOCOUNT ON;

    --SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    SELECT  isolation_level = CASE transaction_isolation_level
                WHEN 0 THEN 'Unspecified'
                WHEN 1 THEN 'Read Uncommitted'
                WHEN 2 THEN 'Read Committed'
                WHEN 3 THEN 'Repeatable'
                WHEN 4 THEN 'Serializable'
                WHEN 5 THEN 'Snapshot'
                ELSE 'UNKNOWN ( ' + CONVERT( NVARCHAR( 8 ),
                    transaction_isolation_level ) + ' )'
            END
    FROM    sys.dm_exec_requests
    WHERE   session_id = @@SPID;

    SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
            request_session_id, resource_type,
            request_mode, request_type, request_status
    FROM    sys.dm_tran_locks
    WHERE   request_session_id = @@SPID
        AND resource_type = 'OBJECT';

    UPDATE  dbo.TestTable
        SET Foo = 1
    OUTPUT  INSERTED.Foo, INSERTED.Bar
    WHERE   Bar = 1;

    SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
            request_session_id, resource_type,
            request_mode, request_type, request_status
    FROM    sys.dm_tran_locks
    WHERE   request_session_id = @@SPID
        AND resource_type = 'OBJECT';

    IF ( @@ERROR <> 0 )
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR( 'Rolled Back Update', 16, 1 ) WITH NOWAIT;
    END ELSE BEGIN
        COMMIT TRANSACTION;
    END;

    SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
            request_session_id, resource_type,
            request_mode, request_type, request_status
    FROM    sys.dm_tran_locks
    WHERE   request_session_id = @@SPID
        AND resource_type = 'OBJECT';

    SET NOCOUNT OFF;

    SELECT  isolation_level = CASE transaction_isolation_level
                WHEN 0 THEN 'Unspecified'
                WHEN 1 THEN 'Read Uncommitted'
                WHEN 2 THEN 'Read Committed'
                WHEN 3 THEN 'Repeatable'
                WHEN 4 THEN 'Serializable'
                WHEN 5 THEN 'Snapshot'
                ELSE 'UNKNOWN ( ' + CONVERT( NVARCHAR( 8 ),
                    transaction_isolation_level ) + ' )'
            END
    FROM    sys.dm_exec_requests
    WHERE   session_id = @@SPID;
END;
GO

With that set up, open a new session, which opens a transaction and runs the "guts" of the procedure:

USE tempdb;
GO

BEGIN TRANSACTION;

UPDATE  dbo.TestTable
    SET Foo = 1
WHERE   Bar = 1;

--COMMIT TRANSACTION;

Open another session and do the same thing, closing the transaction this time:

USE tempdb;
GO

BEGIN TRANSACTION;

UPDATE  dbo.TestTable
    SET Foo = 1
WHERE   Bar = 1;

COMMIT TRANSACTION;

This example will hang, as update locks are being held on the test table.  In yet another session, run the following:

USE tempdb;
GO

SELECT  ObjectName = OBJECT_NAME( resource_associated_entity_id ),
        request_session_id, resource_type, request_mode,
        request_type, request_status
FROM    sys.dm_tran_locks
WHERE   resource_database_id = DB_ID()
    AND resource_type = 'OBJECT';

SELECT  session_id, wait_type, resource_description
FROM    sys.dm_os_waiting_tasks
WHERE   session_id IN ( SELECT  request_session_id
                        FROM    sys.dm_tran_locks
                        WHERE   resource_database_id = DB_ID()
                            AND resource_type = 'OBJECT' );

Here you will see that when the "guts" are run, transaction locks are granted to both the uncommitted session and the hanging session, but the hanging session is waiting for the modify update lock to be granted ( LCK_M_U ) while it waits for the original session to finish.  Go back to the first session and commit the transaction, then go ahead and run the procedure.

USE tempdb;
GO

EXECUTE dbo.TestProcess;

You should see that same intent-exclusive ( IX ) lock having been granted while the procedure runs.  This will lead to the same scenario as outlined above, where further transactions against the affected objects will "hang" while the active transactions complete / the procedure runs.

This is all using the default SET TRANSACTION ISOLATION LEVEL READ COMMITTED, well, transaction isolation level.  With the TOPs and CROSS APPLYs, it's potentially possible that a READ UNCOMMITTED or even a READ COMMITTED isolation level would allow concurrent transactions to effectively SELECT the keys about to be operated on before granting the LCK_M_Us, but in a single statement such as in your procedure, I would be somewhat surprised if that were the case.  If so, then a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; may be the best option to ensure asynchronous access to the objects.


How do I alter index rebuild an entire database?

To do all tables:

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

There is no statement to do all tables in the database like you tried above

However, sometimes you don't want this, say for read only tables or huge tables. Also, some tables/indexes will hardly be fragmented.

A more intelligent way is to check fragmentation first then rebuild or reorganise. One common and widely used example is SQL Fool's script


Best pratice to store dates group by months-year key par value

I'd use a date field with the 1st of a month and a CHECK constraint to ensure it stays day 1.

This keeps it in the native date/time format (which is your observation about option 3)

Option 1 would requires less storage, but complicates comparisons. And 5 million rows isn't much: you use less storage but add code and query complexity


No, modifying CTEs still have to be at the top level.

Testing at Postgres 10.0 produces the error:

  WITH clause containing a data-modifying statement must be at the top level.

select version() ;
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

x=# create table t (id int, x int) ;
CREATE TABLE
x=# insert into t (id, x) select i, i*2 from generate_series(1,10) as g(i) ;
INSERT 0 10
x=# select * from t ;
 id | x
----+----
  1 |  2
  2 |  4
  3 |  6
  4 |  8
  5 | 10
  6 | 12
  7 | 14
  8 | 16
  9 | 18
 10 | 20
(10 rows)

x=# with a as
      ( with b as
          ( update t set x = x + 3 returning *
          )
        select * from b
      )
    select * from a ;
ERROR:  WITH clause containing a data-modifying statement must be at the top level
LINE 2:   with b as (update t set x = x + 3 returning *) select * fr...
               ^
x=#


At first glance, this sounds like a classic parameter sniffing problem.

SQL Server builds an execution plan for the first set of parameters that get called when the plan needs to be compiled, and then reuses that plan over and over through the day. You can see what parameters they are - when you're viewing the serial plan, right-click on the select statement, and go into properties. On the properties window, look for Parameters, and then Compiled Values. That'll show you which values produce the serial plan.

To force the plan to always run parallel, you have several different options (many of which Erland covers in his excellent post that I linked to above), including:

Tune the indexes so everything gets a better plan (to get advice on that, post the plan & server details as described in Getting Help with a Slow Query)
Temporarily, use a plan guide to pin the parallel plan in cache (but just know that if the query changes by a single letter, the plan guide will silently fail since it no longer matches the query)
Use an OPTIMIZE FOR hint in the plan to guide it towards values that produce a parallel plan
Use the new ENABLE_PARALLEL_PLAN_PREFERENCE hint in SQL Server 2016

That's just a quick answer - but for much, much more, read Erland's excellent post, Slow in the App, Fast in SSMS that explains how one query can get different plans, and how to fix it.


Copy a database to a new schema in an existing database

For copy all tables into new schema in current database :

SELECT 'ALTER SCHEMA New Schema TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' as 'queries'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'Old Schema' -- 'dbo' if that is schema name of tables
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Running the above script will generate the SQL commands needed to transfer objects from one schema to another inside result view table in bottom of page.

Something like this :

ALTER SCHEMA Inf TRANSFER [dbo].[t_Link];
ALTER SCHEMA Inf TRANSFER [dbo].[t_News];

Now click on column header ( queries ) and right click, copy all rows, then paste in query windows and Execute.

Now all of objects are in your new schema.


Refactoring the query like this gives better performance (from ~7s to ~0.364s on my local):
http://pastebin.com/7VpLGdQB

EXPLAIN now shows this: http://pastebin.com/q8zEkXbx

Joining the node table twice was the bottleneck. The subquery with node and content_type_profile tables is much more efficient.

On the Drupal side I'll find a way to change this using hook_query_alter or by creating a custom block.


Tempdb tables, table in database or even cte for usp for reporting

If your data needs to be persistent over a failure & restart then a "real" table in your database would be best.  You need a mechanism to isolate one user's execution of this from another's and to tidy up nicely.

If data is to be disposed of if the SP crashes and will be re-created on a restart then a table-valued variable would be adequate.  These are not indexed, however.  If the data will be re-read according to different predicates this may be a problem.

Temporary tables e.g. create table #abc... will keep one user's data isolated from another users'.  The tables will be tidied away at the end of the SP.  Indexes and statistics are held for #Tables so these perform better for larger row counts or multiple access paths.


SQL Query to delete while database size is larger than 200GB

In a maintenance plan run something like this like hourly

While((select count(*) from From [dbo].[logs]) > 1000000)
begin
    delete top (10000) From [dbo].[logs] OrderBy DateTime
end


MySQL upgrade to 5.6.4 from 5.6.33 on Ubuntu

I think you are wrong.

MySQL 5.6.33 is bigger than 5.6.4

For more information look at MySQL 5.6 release note:

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/

MySQL 5.6.4 released at 2011-12-20 So MySQL 5.6.33 has already DateTime feature.


Data from Same Column Joined in Two Different Ways

Assuming ecrs is a user/schema, and keeping in mind I'm not quite following the textual description (eg, not sure which column(s) from the Vendor table you're trying to display twice), see if the following gets you started ... we can tweak based on your feedback:

SELECT v.VEN_Code,
       v.VEN_CompanyName,
       sil.SIL_VEN_FK_Default,
       rcw.RCW_VEN_FK

FROM   ecrs.Vendor v

JOIN   ecrs.StockInventoryLocal sil
ON     v.VEN_PK = sil.SIL_VEN_FK_Default

JOIN   ecrs.ReceiveWorksheet rcw
ON     v.VEN_PK = rcw.RCW_VEN_FK

I'm assuming this isn't really what you want since you mention wanting to pull different vendors, ie, v.VEN_PK = sil.SIL_VEN_FK_Default = rcw.RCW_VEN_FK (all the same vendor).

If sil.SIL_VEN_FK_Default and rcw.RCW_VEN_FK are different values, then we'll need two references to ecrs.Vendor, but then I don't understand what the relationship is between StockInventoryLocal and ReceiveWorksheet.

In other words, let's say we join Vendor(#1) with StockInventoryLocal, and we join Vendor(#2) with ReceiveWorksheet, how do we join these two sets of joins to each other (eg, is there a relationship between StockInventoryLocal and ReceiveWorksheet? is there a relationship berween the two Vendors?)


Why integer search string is slower on char field?

The quotes define the expression as a string, whereas without the single quote it is evaluated as a number. This means that MySQL is forced to perform a Type Conversion to convert the number to a CHAR to do a proper comparison.

As the doc above says,

  For comparisons of a string column with a number, MySQL cannot use an
  index on the column to look up the value quickly. If str_col is an
  indexed string column, the index cannot be used when performing the
  lookup...

However, the inverse of that is not true and while the index can be used, using a string as a value causes a poor execution plan (as illustrated by jkavalik's sqlfiddle) where using where is used instead of the faster using index condition. The main difference between the two is that the former requires a row lookup and the latter can get the data directly from the index.

You should definitely modify the column data type (assuming it truly is only meant to contain numbers) to the appropriate data type ASAP, but make sure that no queries are actually using single quotes, otherwise you'll be back where you started.


Can sys.database_permissions contain REVOKE?

REVOKE doesn't just remove access, rather it undoes a specific permission that was either explicitly given by a GRANT or DENY. In other words, if a permission is explicitly denied on an object for a security principal (e.g. User, Login, etc), that explicitly denied permission can be removed by using REVOKE on it.
After some research it looks like the use-case for "R" as a potential value of the state column in the sys.database_permissions view is for column level permissions that contradict the parent table's or view's permissions as per this Stack Overflow answer by Mark Chesney:

For objects which can have column permissions, such as tables or views, the presence of DENY or GRANT object permissions requires REVOKE for column permissions to be persisted.


Running ratio of unique counts to total counts

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:

WITH CTE AS
(
    SELECT  *,
            Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
    FROM #x
)
SELECT  A.dt,
        A.[Name],
        B.Unq_Cnt,
        A.Tot_Cnt,
        Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [Name])
             FROM CTE
             WHERE dt <= A.dt) B
;

Here is a demo of this.


Why is removing the Identity property on a column not supported

Your question is, essentially:

  Why can I no longer do this risky thing that I should never have been allowed to do in the first place?

The answer to that question is largely irrelevant (though you can see some Microsoft comments in these Connect items asking for this functionality: #294193 and #252226). For completeness, my synopsis is: The ability to remove the identity property was an unintended side effect of having the ability to mess with the system tables in the first place. This was not intended to be used in the many ways that it was, often with very bad consequences, and thus it was removed. It was an undocumented, unsupported, system table hack. The ability to change data in system tables wasn't removed because Microsoft no longer wanted you to hack your way out of a column being an identity column, it was removed because mucking with the system tables is extremely risky. Removing the IDENTITY property itself wasn't a specifically targeted feature removal, and I would have never fully trusted this approach even back in the ancient days when it was possible.

That said, how about we answer this question instead?

  How do I remove the IDENTITY property of a column with minimal or no downtime?

This you can do easily, using ALTER TABLE ... SWITCH, a technique I am certain I first learned from our own Paul White in the workarounds for Connect #252226. Quick example, given this simple table:

CREATE TABLE dbo.Original
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  name SYSNAME
);
GO

INSERT dbo.Original(name) VALUES(N'foo'),(N'bar');
GO

SELECT * FROM dbo.Original;
GO

Results:

ID  name
--  ----
1   foo
2   bar

Now, let's create a shadow table, and switch to it, then drop the old table, rename the new one, and then resume normal activity:

CREATE TABLE dbo.New
(
  ID INT PRIMARY KEY,
  name SYSNAME
);
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
  ALTER TABLE dbo.Original SWITCH TO dbo.New;
  DROP TABLE dbo.Original;
  EXEC sys.sp_rename N'dbo.New', N'Original', 'OBJECT';
COMMIT TRANSACTION;
GO

INSERT dbo.Original(ID,name) VALUES(3,N'splunge');
UPDATE dbo.Original SET ID = 6 WHERE ID = 1;
GO

SELECT * FROM dbo.Original;
GO

Results:

ID  name
--  -------
2   bar
3   splunge
6   foo

Now clean up:

DROP TABLE dbo.Original;

This is a metadata operation only, with no data movement, and will only block other users while the metadata is being updated. But, admittedly, it is a very simplistic example. If you have foreign keys or are using other features like replication, Change Data Capture, Change Tracking, etc. you may need to disable or remove some of those before making this change (I haven't tested all combinations). For foreign keys specifically, see this tip which shows how to generate scripts to drop and re-create all (or selected) foreign key constraints.

Additionally, you will need to update your application code to not expect SQL Server to populate this column, and check any insert or select statements that may be depending on the order of columns or the columns they need to specify. Generally, I would grep your entire code base for any mention of this table.

Also see this script from Itzik Ben-Gan (source: this ancient article) for another way to handle this, but there is data movement involved here, so it doesn't deliver on the "no or minimal downtime" requirement.


Based on comments on the question: The desire is to have RemoteSP on [REMOTE] execute within the security context of GenericUser, but only executable by [MYDOMAIN\OneUser] even though everyone accesses [REMOTE] as GenericUser via the same Linked Server with a static security setup.

That seems like an inconsistent security setup. You have a single login on [REMOTE] that you want to have two different sets of permissions for. At the very least you will have to DENY EXECUTE ON [RemoteSP] TO [GenericUser]; in order to prevent everyone outside of [MYDOMAIN\OneUser] from executing the proc over the Linked Server. But you want it to execute in the context of GenericUser so you need to add WITH EXECUTE AS 'GenericUser' to that proc. Both of these steps get you the security context with the restriction on who can run it, but so far there is no way for [MYDOMAIN\OneUser] to execute it either. And yes, it seems counter-intuitive to DENY execute to the User that is then used as the security context, but a) that be the requirements, and b) it does work as I tested it since it seemed like there was a possibility that SQL Server might give the "Did you even look at what you were trying to execute" error ;-).

Allowing only [MYDOMAIN\OneUser] to execute means creating another Login on [REMOTE] for this purpose, call it UserForRemoteSP. Be sure to GRANT EXECUTE ON [RemoteSP] TO [UserForRemoteSP];.

Another desire is that [MYDOMAIN\OneUser] still access [REMOTE] via the current Linked Server (and hence as GenericUser) for everything else besides [RemoteSP]. That can be accomplished by creating another Linked Server, call it [REMOTE_SPECIAL], that has a mapped "local login to remote login" list with only [MYDOMAIN\OneUser] defined in it, mapping to UserForRemoteSP. For the "For a login not defined in the list above, connections will:" option, select "Not be made".

Last step: in [LocalSP], in the IF SUSER_NAME block, just do:

EXEC [REMOTE_SPECIAL].[B].[dbo].[RemoteSP] @MyVar = @Id;

Nobody else will be able to use the [REMOTE_SPECIAL] Linked Server, and the only thing that [MYDOMAIN\OneUser] can use that Linked Server for is to execute RemoteSP, which will run as GenericUser due to the EXECUTE AS clause.


Master slave replication with different mysql version is on both slave and master

When replicating, the master version must be the same or less than the version on the slave. If this isn't the case, the master may inject binary log messages the slave doesn't understand.

The master version / slave version should stay between on major version. So a 5.6 master is ok to a 5.5 slave but a 5.7 slave is too much, there may be deprecated messages in 5.6 that a 5.7 slave will not understand.

Multiple masters are possible with 5.7 multi source replication..


Produce fixed length output to file regardless of the length of data in each column/row

The select below will add the spaces required for each field.  The date format is fixed to 10 characters.  The "|" sign is was added to show that the size of each coloum is fixed.  Can be removed if not needed.

SELECT LEFT(AccountCode + SPACE(20) , 20) +
'|' + LEFT(AccountName + SPACE(50) , 50)  +
'|' + LEFT((CAST(Account_Id AS NVarchar(10)) +
SPACE(10)), 10) + '|'+
FORMAT(LastPayDate , 'yyyy-MM-dd')
FROM [Accounts]


In Postgres 8.4 or later you would use the USING clause of EXECUTE to pass values safely and efficiently. That's not available in your version 8.3, yet. In your version it could could work like this:

CREATE OR REPLACE FUNCTION test_conditions()
  RETURNS SETOF bigint AS
$func$
DECLARE
   _rec    record;
   _func   text;
   _result bigint;
BEGIN
   FOR _func in
      SELECT  p.proname
      FROM    pg_catalog.pg_namespace n
      JOIN    pg_catalog.pg_proc      p ON p.pronamespace = n.oid
      WHERE   n.nspname = 'operacional'
      AND     p.proname LIKE E'fn\\_condition\\_%'  -- no parens, proper string
      ORDER   BY p.proname  -- no parens
   LOOP
      FOR _rec in
         SELECT * FROM my_table WHERE id IN (1,2,3)  -- no parens needed
      LOOP
         EXECUTE 'SELECT ' || quote_ident(_func) || '(' || quote_literal(_rec) || ')'
         INTO _result;
         RETURN NEXT _result;
      END LOOP;
   END LOOP;
END
$func$  LANGUAGE plpgsql SET search_path = 'public';

Call:

SELECT * FROM test_conditions();

If you use set search_path = 'pg_catalog'; in the function body, then your table in the public schema is not visible any more. And it would be a very bad idea to globally SET the search path. The effect stays for the duration of the setting. You could use SET LOCAL to contain it to the transaction, but that would still be a bad idea. Instead, if you really need to, set the environment of the function only, like demonstrated.
More about the search path in Postgres:

How does the search_path influence identifier resolution and the “current schema”

Just executing a SELECT without assigning or returning the result would be pointless. Use the INTO clause of EXECUTE and then RETURN NEXT. In modern Postgres you would replace the inner loop with RETURN QUERY EXECUTE.
Use quote_ident() and quote_literal() to escape identifiers and literals properly when building a dynamic query string. In modern Postgres you would use format().
It's not very efficient to cast the whole row to it's string representation, escape and cast back. This alternative approach has to read from the table repeatedly, but is cleaner otherwise (the row is passed as value directly):

  FOR i IN
     VALUES (1), (2), (3)
  LOOP
     EXECUTE 'SELECT ' || quote_ident(_func) || '(t) FROM my_table t WHERE id = ' || i
     INTO _result;
     RETURN NEXT _result;
  END LOOP;

Example function

You can also radically simplify your example function with this SQL function:

CREATE OR REPLACE FUNCTION fn_condition_1(p_record my_table)
  RETURNS bigint AS
$func$
   SELECT CASE WHEN $1.atributo1 > $1.atributo2 THEN bigint '1' END
$func$  LANGUAGE sql;


Yes, using PERCENTILE_CONT(0.50) is a way of finding the median. You can check the Basic syntax example section of the PERCENTILE_CONT doc to see an example.
About the GROUP BY, the PARTITION BY clause is already creating groups of years, so you don't need to use a group by on the subquery. Also, you shouldn't use ORDER BY on that subquery, or you'll get the error:

The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET
or FOR XML is also specified.

Try your query like this:
SELECT
    years,
    MAX(NET_MERIT) AS high_end,
    MAX(q3) AS q3,
    MAX(MEDIAN) AS median,
    MAX(q1) AS q1,
    MIN(NET_MERIT) AS low_end

FROM(

    SELECT YEAR(FA_BDATE) AS years ,NET_MERIT,
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS MEDIAN,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q3
    FROM HerdAnalytics_tbl

) AS sub
GROUP BY years
ORDER BY years;


Aggregated functions does not consider rows with NULL values.

You could use a WINDOW FUNCTION to enumerate rows and then get first row only:

with ct as
(
    select id, Parent, Date,
           row_number() over (partition by id order by id, date desc) rn
    from   tbl
)
select id, Parent, Date
from   ct
where  rn = 1
order by id;

id | Parent | Date
-: | :----- | :------------------
 1 | Alex   | 01/10/2018 00:00:00
 3 | Cath   | null

db<>fiddle here


Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

Last time I have used it (but that's a long time ago) Sybase PowerDesigner was able to handle triggers and procedures for all supported DBMS.

Looking at the current manuals, that still seems to be the case.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38058.1600/doc/html/rad1232020719541.html

Although I don't see MySQL mentioned there.


How do I force SQL Server to use my spatial index through a view?

Window functions & Views

Recently I answered a different question regarding views & window functions but not all answers given there apply here.

Two differences are that this example uses geography datatypes and filters on the window function. The positive part here is that you are not bound to the window function and could use something like CROSS APPLY to get the closest results for one latitude & longitude combination.

The previous question + Answer

In short, the window function is calculated before the filtering is applied.

E.G.

where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

Extra information can be found in this blogpost  from 2013 by Paul White on window functions and views.

Testing

As a first test, the result is clear when omitting the ROW_NUMBER() from the view

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

set statistics xml on;
select *
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1

Resulting in the expected performant query plan.

DB<>Fiddle

But you obviously still want the additional filtering

Keeping the view + window function

You could choose to add the window function afterwards like so:

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

&

set statistics xml on;
;WITH CTE AS
(
select most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
,*
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)
SELECT
*
FROM CTE
WHERE most_recent is not null;

Again resulting in the expected execution plan.

DB<>Fiddle

Using an inline table valued function  + window function

CREATE FUNCTION dbo.F_Example
(
    @P1 INT

)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent FROM
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <= 1609.344e1
) AS A
WHERE most_recent= @P1
);

EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.F_Example(@P1)',N'@P1 INT',@P1 = 1

Resulting in the query plan you expected.

DB<>Fiddle

Using a CTE + window function

;WITH CTE AS
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= row_number() over (partition by latitude,longitude order by transaction_dt desc)
FROM dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)

SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent
FROM CTE2
WHERE most_recent < 5;

Using CROSS APPLY and TOP(4) without the window function

SELECT example_id
      ,a.transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example e1
CROSS APPLY(
SELECT TOP(4) transaction_dt
FROM dbo.example e2
WHERE e1.latitude = e2.latitude  and e1.longitude = e2.longitude
GROUP BY latitude,longitude,transaction_dt
ORDER BY transaction_dt desc
) as a
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
AND e1.transaction_dt = a.transaction_dt
ORDER BY transaction_dt desc;

I used top(4) since the resultset was based on < 5 not <= 5

When using the CROSS APPLY method there is one more index to be added, to remove the index spool & sort:

CREATE INDEX IX_latitude_longitude_transaction_dt
ON dbo.example(latitude,longitude,transaction_dt);

It goes without saying that you could add the CROSS APPLY solution to a view and query it the same way as you used to, an example of this here.
All above examples in one DB<>Fiddle here.

End solution comment by MichaelB

  Thanks Randi, I was able to rewrite my logic into a select subquery
  using the idea of your cross apply. As an added bonus, if I don't
  mention the field in the columns I select, I do not get a performance
  hit.

DB<>Fiddle

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= (
          select a.transaction_dt
          intersect
          select top 5 b.transaction_dt
          from dbo.example b
          where a.latitude=b.latitude
                and a.longitude=b.longitude
          order by transaction_dt desc
      )
from dbo.example a;

&

select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
      and most_recent is not null


What is the best identifier for a UserID?  64 bit integers, UUID V5,  or 64 character SHA256 UID?

Use integers. This is the simplest and most efficient way (especially for InnoDB).

That is

don't use hashes or UUIDs or varchars in place of int
don't use the natural key when you have foreign keys to it

Reasons?

varchar comparisons require collation + case tests
index architecture in InnoDB favours integer fields (ideal clustered index is narrow, numeric, monotonically increasing)
hashes and GUIDs and varchar cause fragmentation
wider data + indexes rows when not using integer
64 characters requires 64/66 bytes to store. 64 bit in requires 8 bytes

Other observations:

Do you need 64 bit numbers?


As a minimum, you should use OR instead of AND, because it is impossible for a date to be simultaneously in February AND January.

SELECT sum(sell) AS sell
  FROM stat
  LEFT JOIN stat_key ON stat.stat_key = stat_key.id
 WHERE stat.date BETWEEN '2017-02-16' AND '2017-02-26'
    OR stat.date BETWEEN '2017-01-16' AND '2017-01-26'
    OR stat.date BETWEEN '2016-12-16' AND '2017-12-26';

Now that I see your expected output, I know that you want something like this:

WITH ct(d) AS (
  SELECT d::date
  FROM generate_series('2016-11-26', '2017-02-26', INTERVAL '1 month') AS gs(d)
)
SELECT COALESCE(SUM(s.sell), 0) AS sell,
       ct.d AS dt
    FROM  ct
    LEFT JOIN stat s ON s.date BETWEEN ct.d + '-10 days' AND ct.d
    GROUP BY ct.d;

Unfortunately, your expected output cannot be generated by the sample input, so I had to test it with my data.

Notes:

generate_series works with timestamps: you can convert them to dates in the main SELECT or the CTE. Other approaches are possible.
It is possible to avoid the CTE (i.e. the WITH at the beginning) and join directly with SELECT generate_series(.... However, I find it more readable this way, given that in this way the report dates are near the top and that is what you will change most often.
LEFT JOIN and COALESCE() let you display results (0) for ranges that have no values at all in the data table.


Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it exclusively as a queue. Under load I found that a critical issue was to batch dequeue (dequeue 100 web service call request in one DB operation) and handle the dispatch in the app (place the dequeue request into an in memory list, have the web call handlers pick up work from this list). Doing the web service call async is critical. Also, you must read about the ServicePointManager.DefaultConnectionLimit.


Extracting data from SQL Server linked server

Check the link server setup and see HOW the users connect to the link server. This can be done by going to Server Objects in SSMS -> Linked Servers -> [Link server name] ->  right click then select Properties -> security tab. It can be done without a security context, using the current login's security context, or it can be done using a specific login. Find out which account is being used and then make sure that account has access to the table on the linked server.


Accessing Shared Folder Using Oracle Database

To troubleshoot this type of erro follow the steps bellow :

1- make sure that the location where you want to write exists.
2- make sure that the oracle(or the user who runs the database instance) user has the right to write in that
location.(try to create txt file in that location as Oracle user.)

Ok , more steps for you to troubleshoot:
- i see you are running Oracle on Windowzzz! Correct ?
 Go to your Services tool and see who(what account) is running the Oracle services, form here make sure this user has access to the directory where you what to write to!

Now maybe is better for you to write into another location(your user home directory):

SQL> CREATE pfile='C:\Users\user.name\Downloads\pfile' FROM spfile;

if the error persists then your problem is different !
From this point you can think of security baselines etc...


Primary Key Constraint Violation in SSIS with Quotation Mark

Solved. See Comment above. Turns out that the quotation mark wasn't the problem.


Ample disk space with error: "1034 Incorrect key file for table 'table_name'; try to repair it"

Well, I'm a big dummy.  When I went to check the size of the database via the method requested by @a_vlad, I realized that previously I was checking the free space on /tmp on the wrong server.

Gah.  Executing the command again while tracking the free space on /tmp on the correct server reveals that lack of free space is, indeed, the problem.

...and here's the slice of humble pie I'm eating right now:


I solved it myself.
In case someone runs into a similar problem:
[dbo].[MemoCount] also has an ID Column.
so i changed

AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID]))

to:

AND ([Reference]  = [Foobar].[dbo].[Expense].[ID])) AS [MemoCount]

Apparently SQL took the column [dbo].[MemoUnion].[ID] to compare it with [Reference].
That problem was of course not solvable for you guys because I never posted the Columns of [dbo].[MemoUnion] in the first place.
Thanks for all the suggestions nonetheless!


Can I choose which CPUs / Cores to be used by MySQL?

Maybe not the answer you were looking for, but you could use a hypervisor like vmware esxi and use processor affinity to lock the virtual machine to any number of specific cpus.


Will FILLFACTOR = 99 slow down my SQL Server?

Fill factor is critical in fragmentation

  What does the author mean with "page splits occur when there is no room for data"?

There are basically two types of splits good page split or sequential page split which occurs when leaf level of index page is full and when new record is inserted page split occurs which results in allocation of new page and data is written in sequential order because new page is added at last of all pages.

The other one is bad page split or non sequential page split which occurs due to insert or update operation on a page resulting in page split in between. What happens is when record is updated a space for new record is created and this might splits page and move some of record on new page now this new page would be just after page that was updated hence severely creating a mismatch in order by which Logical clustering keys are arranged and how data pages are arranged. In fact depending on how fast new page can be allocated for page split it can also cause performance issue. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. This article aptly demonstrates how page split can occur. You can also read this article by Paul about how to see bad page splits. Both of these use undocumented commands if you have SQL Server 2012 and above you can use Extended events trace to track nasty page splits

One more drawback associated is when huge number of page splits occur they could saturate I/O by utilizing it. Consider a scenario where update causes page split now one I/O would be required for update to index key one for update to page(normally) it would also require additional I/O for new page addition and updating index after new page is created, on a busy system with not so upto date hardware this could cause performance issue

There is other way using DMV to find number of page splits happening in database but it wont tell you which one is good and which one is bad

--Script to check page split for index
SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Please let me know if you need further explanation


Understanding Galera Status wsrep_local_recv_queue_avg

According to the MariaDB KB: Galera Cluster Status Variables:

  Description: Average length of the receive queue since the most recent
  status query. If this value is noticeably larger than zero, the node
  is likely to be overloaded, and cannot apply the writesets as quickly
  as they arrive, resulting in replication throttling.

So it not the average receive queue size for the node's uptime, but since the last time you queried this status.


How to change the create and use statement in a large sql file without opening it

I created a script so that you switch the old DBName with the new DBName

Here is the script

DUMPFILE=junk.txt
DUMPFILENEW=junk.new
DBNAME_OLD=dbAccessData
DBNAME_NEW=rolando

LINE_CDB=`grep -n "^CREATE DATABASE" ${DUMPFILE} | sed 's/\`/ /g' | sed 's/:/ /g' | grep " ${DBNAME_OLD} " | awk '{print $1}'`
LINE_USE=`grep -n "^USE " ${DUMPFILE} | sed 's/\`/ /g' | sed 's/:/ /g' | grep " ${DBNAME_OLD} " | awk '{print $1}'`
(( LINES_IN_THE_MIDDLE = LINE_USE - LINE_CDB + 1 ))
LINES_IN_THE_DUMP=`wc -l < ${DUMPFILE}`

DUMP_SECTION1=${DUMPFILE}.Section1
DUMP_SECTION2=${DUMPFILE}.Section2
DUMP_SECTION2A=${DUMPFILE}.Section2a
DUMP_SECTION3=${DUMPFILE}.Section3

(( X = LINE_CDB - 1 ))
head -${X} ${DUMPFILE} > ${DUMP_SECTION1}

head -${LINE_USE} ${DUMPFILE} | tail -${LINES_IN_THE_MIDDLE} > ${DUMP_SECTION2}

(( X = LINES_IN_THE_DUMP - LINE_USE ))
tail -${X} ${DUMPFILE} > ${DUMP_SECTION3}

echo "sed 's/${DBNAME_OLD}/${DBNAME_NEW}/g' < ${DUMP_SECTION2} > ${DUMP_SECTION2A}" > change_dbname.sh
chmod +x change_dbname.sh
./change_dbname.sh
rm -f change_dbname.sh

cp ${DUMP_SECTION1} ${DUMPFILENEW}
cat ${DUMP_SECTION2A} >> ${DUMPFILENEW}
cat ${DUMP_SECTION3}  >> ${DUMPFILENEW}

rm -f ${DUMP_SECTION1}
rm -f ${DUMP_SECTION2}
rm -f ${DUMP_SECTION2A}
rm -f ${DUMP_SECTION3}

cat ${DUMPFILENEW}

Here is the sample file (junk.txt, 30 lines)

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbAccessData` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `dbAccessData`;

--
-- Table structure for table `tblAccountLinks`
--

DROP TABLE IF EXISTS `tblAccountLinks`;

Here is the output

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `rolando` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `rolando`;

--
-- Table structure for table `tblAccountLinks`
--

DROP TABLE IF EXISTS `tblAccountLinks`;

Give it a Try !!!


How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

My first impression is that you should redesign your tables a bit. I'm not sure what SPLIT_PART does, but it sounds as if it grabs part of a string, and having such functions in an ON clause is really bad for performance. Assume table A and B has 1000 rows each:

SELECT ...
FROM A
JOIN B
    ON f(A.x) = f(B.x)

In a nested loop join there will be 2*1000*1000=2 miljon calls to f. I would investigate the possibilities to add a generated column for the join conditions, and add an index.

To remove redundancy from the answer you can use distinct, but you will still get redundant info in the following situation:

GOLD, SILVER, ...
SILVER, GOLD, ...

Does it matter if a_commodity and b_commodity switches place for a certain row? If not you can arrange the in lexigraphical order like:

SELECT distinct
       LEAST(minfac.mineral_commodity_general, outlook.commodity, table2.commodity)
     , ...
       GREATEST(minfac.mineral_commodity_general, outlook.commodity, table2.commodity)
     , ...

For the middlest one you will have to exclude the least and the greatest one.


Neo4j service won't start with an imported database on Debian

It turns out that what I needed to do is make sure my permissions are right.
I fixed it with

chown -r Neo4j:adm graph.db

and everything seems to work fine now. I think Neo4j should produce some sort of warning if it can't access graph.db


What will be the best suited datatype to store a text file in database

If your files are going to be >1MB, then FILESTREAM is your best option. You can look at BOL documentation here, which clearly describes and advises you when is the best situation to use it.


Do SQL Server disabled databases cause any usage concerns?

I don't think you'll see much overhead except for system processes that enumerate databases for certain tasks. E.g. trace the server and expand the databases node in Management Studio - I bet you see a LOT more activity on the system when there are 50 active databases and 500 disabled databases compared to just 50 active databases.

Once a client has left, I would typically make a final backup of the database, mark the database as offline, wait 30 days in case they make any post-requests for data or reports, then drop the database once the backup is off-site (or whatever your "really backed up" status means).


Is 1.6 seconds a typical time to fetch nearly 3.7 million rows from a table?

Both 1.6s and 7s seem reasonable.  Some issues that lead to variations:

Caching (Was I/O needed, or was everything sitting in RAM -- a factor of 10 variation is possible)
Distance to other server (Ping time)
Bandwidth to other server (Mb/sec)
Client's ability to absorb that much data that fast
Whether client is receiving it in chunks or all at once

But my question to you is -- what can you do with 3.7M rows all at once?  That is, this does not seem like a reasonable benchmark or whatever.

Back to the "caching".  Try this:

restart mysqld
run your test
run your test again -- likely to run faster.

Main cache:  for a 4GB server and InnoDB tables, innodb_buffer_pool_size should be something like 2G.


Maintenance plan to backup differentials hourly each to a new file name in SQL Server 2012

You should use Ola's backup solution. Brad talks about it in more details on simple talk.

The solution is very simple to implement and its tested by many organizations.

  Value  Description

  FULL   Full backup

  DIFF   Differential backup

  LOG        Transaction log backup


Authoritative source that  and != are identical in performance in SQL Server

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present:

This occurs well before anything in the optimizer gets involved.

  From Comparison Operators (Transact-SQL)

Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register eax** as follows:

╔════╦══════╗
║ Op ║ Code ║
╠════╬══════╣
║ <  ║    1 ║
║ =  ║    2 ║
║ <= ║    3 ║
║ !> ║    3 ║
║ >  ║    4 ║
║ <> ║    5 ║
║ != ║    5 ║
║ >= ║    6 ║
║ !< ║    6 ║
╚════╩══════╝

!= and <> both return 5, so are indistinguishable in all later operations (including compilation & optimization).

Though secondary to the above point, it is also possible (e.g. using undocumented trace flag 8605) to look at the logical tree passed to the optimizer to confirm that both != and <> map to ScaOp_Comp x_cmpNe (not equal scalar operator comparison).

For example:

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID != 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID <> 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

both produce:

LogOp_Project QCOL: [P].ProductID
    LogOp_Select
        LogOp_Get TBL: Production.Product(alias TBL: P)
        ScaOp_Comp x_cmpNe
            ScaOp_Identifier QCOL: [P].ProductID
            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
    AncOp_PrjList

Footnotes

* I use WinDbg; other debuggers are available. Public symbols are available via the usual Microsoft symbol server. For more information, see Looking deeper into SQL Server using Minidumps by the SQL Server Customer Advisory Team and SQL Server Debugging with WinDbg – an Introduction by Klaus Aschenbrenner.

** Using EAX on 32-bit Intel derivatives for return values from a function is common. Certainly the Win32 ABI does it that way, and I'm pretty sure it inherits that practice from back in the old MS-DOS days, where AX was used for the same purpose - Michael Kjörling


Can I Upgrade SQL Server 2008 R2 Express to SQL Server 2014 Developer edition

No you cannot do inplace upgrade from SQL server 2008 R2 express to SQL server 2014 developer edition. Its not supported as per Version and edition upgrade supported matrix

You can only upgrade to below using inplace upgrade method

  SQL Server 2014 Enterprise

  SQL Server 2014 Business Intelligence

  SQL Server 2014 Standard

  SQL Server 2014 Web

  SQL Server 2014 Express

But there is other option to do side by side migration. You install SQL server 2014 DEV edition. You backup your user database from SQl server express and restore it on SQl Server 2014.

You can migrate logins using This support article


How is the Database Encryption Key for SQL Server TDE linked to the certificate?

"Create a database encryption key and protect it by the certificate". What does protect mean?

It means that TDE uses the Database Encryption Key (DEK) to encrypt/decrypt the database BUT the Database Encryption Key REQUIRES the existence of the server certificate in order to do this.  Thus, the Database Encryption Key is 'protected' by its requirement that the server certificate exist.

This 'requirement' is quite obvious if you back up a TDE database and try to restore or attach it to another Sql Server instance that does not have that server certificate.  You'll get an error similar to this:

My answer to a related question here provides some additional information about TDE requirements.


Select same records that only have different values for a particular column

Here's an example of using EXISTS and a correlated subquery.  I tested on SQL Server, but will probably work on other RDBMS's.

drop table if exists table1
CREATE TABLE Table1
    (LASTNAME varchar(7), MIDDLENAME varchar(1), FIRSTNAME varchar(5), SSN int, BIRTHDAY varchar(9))
;

INSERT INTO Table1
    (LASTNAME, MIDDLENAME, FIRSTNAME, SSN, BIRTHDAY)
VALUES
    ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
    ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
    ('Jones', 'M', 'Carol', 4425, '20-APR-70'),
    ('Black', 'S', 'Ted', 5555, '15-MAY-57'),
    ('Roberts', 'T', 'Cole', 1412, '14-MAY-57'),
    ('Roberts', 'T', 'Cole', 1412, '20-OCT-57'),
    ('Roberts', 'S', 'Cole', 1412, '15-MAY-57')
;

SELECT *
FROM table1 t1
WHERE EXISTS (
        SELECT *
        FROM table1
        WHERE LASTNAME = t1.LASTNAME
            AND MIDDLENAME = t1.MIDDLENAME
            AND FIRSTNAME = t1.FIRSTNAME
            AND SSN = t1.SSN
            AND BIRTHDAY <> t1.BIRTHDAY
        )

| LASTNAME | MIDDLENAME | FIRSTNAME | SSN  | BIRTHDAY  |
|----------|------------|-----------|------|-----------|
| Roberts  | T          | Cole      | 1412 | 14-MAY-57 |
| Roberts  | T          | Cole      | 1412 | 20-OCT-57 |


Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server

linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping.

tnsping searched to the path C:\product\11.1.0\db_1\bin initially, this will initially use the used parameter file
C:\product\11.1.0\db_1\network\admin\sqlnet.ora

tnsping successfully connected to the Oracle database from the SQL Server box. Then check the Provider details while creating the Linked Server.

The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components.

Error 7302 can be a result of MSDAORA.dll not being registered correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues,to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry.

Error 7303 means that the username and password combination is not correct, hence the login failed for the user specified in the Linked Server. When the correct login credentials for the Oracle database were entered, the Linked Server got created successfully!


You should create a table that holds the form and it has an form ID and the form name. Then another table that holds the questions for that particular form. The fields would be the formid from your first table and then the quesiton ID and the question. The question id would be continuous unique range of numbers (so if you have 3 forms and each has 10 questions, questionid needs to be 1 through 30). And finally a third table that has the questionid and the answer. You can also have other fields on these tables such as who took it, who created it, when it was created, when it was taken, etc.


One way to do this is to query the Query Store views directly for the info you've gained from the plan cache:

SELECT
    qsq.query_id,
    qsq.last_execution_time,
    qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt
        ON qsq.query_text_id = qsqt.query_text_id
WHERE
    qsqt.query_sql_text LIKE '%your query text%';

The sys.query_store_query table also has some of the other fields (query_hash, last_compile_batch_sql_handle, statement_sql_handle, etc), which might find the query you're looking for more reliably.

Basically the point of that is to find the "query id" in Query Store, as you'll need that to find plans for it and force one.

Once you have the query_id in hand, you can go to the UI in SSMS and find the query using the "Tracked Queries" node:

From there you can click on the plan you want, and click "Force Plan" to force it:


Your error message says:

psql:cobertura.sql:29: ERROR:  "sql " is not a known variable
LINE 14: sql := format('insert into cobertura_tmp select count(*) as ...

Look closely: "sql ", not "sql"

That means you have a sneaky, invisible character right after "sql" instead of an innocent space character. It's not in your question, probably got lost in translation when you copied & pasted it in your question here. Your original code contains something like:

sql　:= format ...

Do you see it? No? Because one cannot see it. (You might notice the space is a tad bit wider in this particular case - if your browser, font and character set produce the same result as mine.) It's an "ideographic space", Unicode U+3000, HTML &#12288. Just a random example. There are various characters like that in Unicode. Replace it with a simple space to fix your problem.

dbfiddle here

I hate this about Unicode, too many characters that mostly just confuse people ...

For a quick test:

SELECT ascii(' ');   -- 32    -- plain space
SELECT ascii('　');  -- 12288 -- not a plain space
SELECT '　' = ' ';   -- f     -- not the same!

A quick test whether there are any non-ASCII characters in a string:

SELECT octet_length(string) = char_length(string)  -- f
FROM  (SELECT text 'sql　:= format' AS string) t;

Assuming encoding UTF8, non-ASCII characters occupy 2-4 bytes, while plain ASCII characters occupy a single byte. octet_length() returns the

  Number of bytes in string

while char_length() (same as length()) returns the number of characters. For all-ASCII, both return the same. If octet_length() returns a higher number, you have suspicious characters. Does not have to mean anything. Any accented character in a string is good for it.

Or use an editor that can highlight suspects.

Aside, while being at it, your plpgsql code would be considerably more efficient like this:

DO
$$
DECLARE
   _imei text;
   _sql  text;
BEGIN

IF to_regclass('pg_temp.cobertura_tmp') IS NULL  -- if tmp table does not exist
THEN
   CREATE TABLE cobertura_tmp (
      num      integer
    , realtime char(1)
    , lat      numeric
    , lng      numeric
    );
ELSE
   TRUNCATE TABLE cobertura_tmp;
END IF;

FOR _imei IN
   SELECT e.imei_equipo::text  -- imei is NOT NULL & *safe* against SQL injection
   FROM   cliente c
   JOIN   cliente_avl_equipo e ON e.id_cliente = c.id  -- assuming c.id is UNIQUE
   WHERE  c.nombre ILIKE '%enangab%'
   LIMIT  3
LOOP
   _sql := format(
   $s$
   INSERT INTO cobertura_tmp (num, realtime, lat, lng)
   SELECT count(*)::int * (CASE WHEN a.tipo = 'S' THEN -1 ELSE 1 END) -- AS num
        , a.tipo                         -- AS realtime
        , round(a.latitud::numeric, 4)   -- AS lat
        , round(a.longitud::numeric, 4)  -- AS lon
   FROM   reports.avl_historico_%s a
   WHERE  a.latitud  BETWEEN -38.67405472 AND -36.75131149
   AND    a.longitud BETWEEN -73.08429161 AND -69.65333954
   GROUP  BY 2,3,4
   $s$
 , _imei);

   EXECUTE _sql;
END LOOP;

/* integrated above to avoid costly extra update:
UPDATE cobertura_tmp
SET    num = -1 * num
WHERE  realtime = 'S';
*/

-- ... etc

END
$$;

Many little details, but that's not the topic of the question.
I doubt you need a temporary table at all ...

Try it if you want to improve the code. Related:

How to check if a table exists in a given schema
Insert text with single quotes in PostgreSQL


Can SQL Server 2008 R2 get email name resolution from Office 365?

The answer seems to be "no, you can't".  Thanksfully our email addresses were consistant, so I was able to write a function that transforms the [firstname lastname] or [groupname] into the valid email  address.  For anyone outside our company, their raw email address can be supplied.


use of crows foot or chens notation to draw an extended ER diagram

It seems to me that you should be able to draw your ERD in the same way that you would for a regular relational database. Take a look at the wiki article for Object-Relational databases; there is a lovely example image on the page that might be of use.

While the functionality of an object-relational database might be somewhat different than that of a regular relational database (joins are understood etc.), it seems that they can both be represented in the same way.

It should appear that it is not so much the design of the database that is different, but instead the functionality of the underlying database management system. Crow's foot notation is currently the recommended standard.


Like this?

 INSERT INTO b SELECT FLOOR(LOG2(Col1)) + 1 FROM a;


How do I rewrite my query to use less temporary space?

Try the query using window functions which gets rid of the self join:

select id, name
from (
 select id, name, my_object_id, time_in_ms,
        count(*) over (partition by name, my_object_id, time_in_ms) as cnt
 from my_object_times t1
) t
where cnt > 1;

The partition by will still need to sort the rows, so it might be that it still needs a lot of temp space. If your server has a lot of memory, you can also increase work_mem for your session before running the statement.

Another possible way is to use an exists query instead of the self join:

select id, name
from my_object_times t1
where exists (select *
              from my_object_times t2
              where t1.my_object_id = t2.my_object_id
                and t1.name = t2.name
                and t1.time_in_ms = t2.time_in_ms
                and t1.id <> rt2.id);

An index on (my_object_id, name, time_in_ms, id) would help for this.

Check the execution plan for both to see if one is cheaper then then other.


innodb_flush_logs_at_trx_commit
When 1:
for each Txn Commit {
    - Write log buffer to log file
    - flush log files to disk
    }

When 0:
Approx every second {
    - Write log buffer to log file
    - flush log files to disk
    }

When 2:
    {
    - For each commit: { Write log buffer to log file }
    - Appx every sec: { flush log files to disk }
    }

Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.

Well I just copied all of the above from documentation ;)

Anyways, now about your points.

innodb_flush_logs_at_trx_commit changed: Choice is between ACID vs speed. You changed it to 2 for performance.okay but you know the risk.
Moving to SSDs: They're proven to be faster than their ancestors... and considering that, you better benchmark your traffic with the preferable value of innodb_flush_logs_trx_commit of 1 (default) on SSDs.
Is the same amount of data...?: Well yes amount of data will be ofcourse same; though you might wanted to ask something else. We know  that how it improves the perfomance is by reducing number of disk ops.

So, "Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2?" -> Regardless of disk type, value of 1 is bound to do more work (disk ops) than value of 2 and hence the performance difference.


SQL Server Mirror failover on failing storage

Just to move my part into an Answer.
It would appear the "problem" is that the script in the blog ( http://sqlserverperformance.wordpress.com/2012/03/27/sql-server-database-mirroring-tips-and-tricks-part-3/ ) is using an ALTER DATABASE dbname SET PARTNER FAILOVER; to failover the rest of the 'failover' group.

As all the dbs were on the same disk, this fails as SQL SERVER can't run the ALTER DATABASE command if it can't get in to the database. So moving the databases onto seperate disks fixes that.

But that sort of makes sense. If all the databases are on the same disk then they should failover automatically as a 'group' as they all need to be fail overed. The referenced script would make sense if you did have databases on seperate disks that needed to failover together if just one of the disks failed.


How do I get TABLE OF TOURNAMENT POSITIONS according to score table

Looking the task and sample data and ignoring sample output...

For MySQL 8+ :

WITH cte AS (
SELECT categoria,ndeporte,equipo1,score1,equipo3,score3
FROM score
UNION ALL
SELECT categoria,ndeporte,equipo3,score3,equipo1,score1
FROM score
)
SELECT UPPER(equipo1) EQUIPOS,
       COUNT(*) PJ,
       SUM(score1>score3) PG,
       SUM(score1<score3) PP,
       SUM(score1=score3) PE,
       SUM(score1) GF,
       SUM(score3) GC,
       SUM(score1-score3) DfG,
       SUM(3*SIGN(score1-score3)) TOTAL
FROM cte
WHERE categoria = 'CAT 3'
  AND ndeporte = 'Futbol'
  GROUP BY UPPER(equipo1);

... and for MySQL 5+ :

SELECT UPPER(equipo1) EQUIPOS,
       COUNT(*) PJ,
       SUM(score1>score3) PG,
       SUM(score1<score3) PP,
       SUM(score1=score3) PE,
       SUM(score1) GF,
       SUM(score3) GC,
       SUM(score1-score3) DfG,
       SUM(3*SIGN(score1-score3)) TOTAL
FROM ( SELECT categoria,ndeporte,equipo1,score1,equipo3,score3
       FROM score
       UNION ALL
       SELECT categoria,ndeporte,equipo3,score3,equipo1,score1
       FROM score) cte
WHERE categoria = 'CAT 3'
  AND ndeporte = 'Futbol'
  GROUP BY UPPER(equipo1);

If you need the data for all categoria and/or ndeporte then add them to output fields list and GROUP BY clause, and remove from WHERE clause (or edit if you need the result for some values only).

fiddle


You may have some messy registry keys hurting you.  I've filled more than a few swear jars trying to figure out Oracle Client install issues on Windows, so hopefully my pain can be your gain.  I made a couple of blog articles on the matter outlining it all.

I would suggest you remove the existing client first which may require some manual work, then install a new client on the cleaned system.

Hopefully that will help.


PostgreSQL vagrant VM cannot create extension

postgres has no password after an automated install, it's expected to authenticate through the peer method.  sudo su - postgres is the first step to it. The second step is to connect through the Unix local domain socket, but your command doesn't do that, it connects through TCP, that's why you're stuck with the password problem.

The installed pg_hba.conf file probably starts with these two rules, apart from comments:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
[...]

When doing a TCP connection with -h localhost... the first rule doesn't match. The second rule matches and it triggers the demand for a password, but as none was set this can only fail (empty passwords are not allowed either).

The solution is to remove -h localhost from psql invocation, for the first rule to be taken instead. As it's on a Unix system, it will attempt a connection through the Unix domain socket.

Once logged to psql as the postgres user, you may set a password with the \password command or ALTER USER postgres PASSWORD 'foobar';

See also https://help.ubuntu.com/community/PostgreSQL
In the section Using pgAdmin III GUI , they suggest to change the pg_hba.conf first rule from auth peer to md5, after having set a password. Personally I don't quite get why. I'd rather leave that rule alone and run pgAdmin from my own Unix account, choosing localhost TCP connections.


Full Text Search not searching special character

I can't verify this right now, sorry, but IIRC you have to quote special characters because SQL Server assumes @ and # are word delimiters

SELECT * FROM TblName WHERE CONTAINS(ColName,'"@#"')

Also see the SQL Server FTS Engineering team blog


Is there a way to truncate table that has foreign a key?

No, you either need to delete and re-create the keys, or wait for the delete and re-seed. Disabling the foreign key temporarily might make the delete faster, but it still won't allow a truncate.

ALTER TABLE [dbo].[tablename] NOCHECK CONSTRAINT ALL;

-- delete, reseed, etc.

ALTER TABLE [dbo].[tablename] WITH CHECK CHECK CONSTRAINT ALL;

Very easy to automate this by building dynamic SQL from the metadata tables, depending on exactly which table(s) you need to target. The above is just a sample to demonstrate how it is done for a single table. For example, this will do so for each table that is the target of a foreign key and has an IDENTITY column:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

;WITH s(t) AS
 (
   SELECT
     QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id))
     + '.' + QUOTENAME(OBJECT_NAME(referenced_object_id))
  FROM sys.foreign_keys AS k
  WHERE EXISTS
  (
    SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = k.referenced_object_id
  )
  GROUP BY referenced_object_id
)
SELECT @sql = @sql + N'
  ALTER TABLE ' + t + ' NOCHECK CONSTRAINT ALL;
  DELETE ' + t + ';
  DBCC CHECKIDENT(''' + t + ''', RESEED, 0) WITH NO_INFOMSGS;
  ALTER TABLE ' + t + 'WITH CHECK CHECK CONSTRAINT ALL;'
FROM s;

PRINT @sql;
-- EXEC sp_executesql @sql;

It may be that the output gets truncated, but this is just a limitation of PRINT (8K) - the actual command is complete.

PS this assumes SQL Server 2005 or better. Always useful to specify your version as part of your question (usually with a tag).


Oracle: Recover source code/table structures from "utterly wiped out" development database

UPDATE

In order to start the instance without interfering with an existing running instance (e.g ORCL), before starting anything:

sqlplus / as sysdba
create pfile='/tmp/initORCL2.ora' from spfile='?/dbs/spfileORCL.ora';
exit

Edit /tmp/initORCL2.ora, and modify (add if doesn't exist) the instance_name:

*.instance_name='ORCL2'

Prevent listener registration, e.g:

*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DUMMY)))'

Create the new spfile:

sqlplus / as sysdba
create spfile='?/dbs/spfileORCL2.ora' from pfile='/tmp/initORCL2.ora';
exit

And continue according to the original steps:

export ORACLE_SID=ORCL2
sqlplus / as sysdba

If you don't use spfile, simply copy and edit the initORCL.ora.

/UPDATE

startup nomount

You have lost the UNDO tablespace, so switch to manual undo management:

alter system set undo_management=manual scope=spfile sid='*';

You have lost the redo logs and your remaining datafiles are in an inconsistent state, so you will not be able to open the instance, unless you allow resetlogs corruption:

alter system set "_allow_resetlogs_corruption"=true scope=spfile sid='*';

Mount the database:

startup force mount

You have lost the datafiles, so you will not be able to open the instance, unless you offline drop the datafiles:

select name from v$datafile;

alter database datafile '/database/users01.dbf' offline drop;
alter database datafile '/database/undotbs01.dbf' offline drop;
...

In order to perform open resetlogs, and incomplete media recovery should be performed (even if it can not do anyhting):

recover database until cancel;

At this point you should be able to open the instance:

alter database open resetlogs;

Should be, because depending on the scale of inconsistency of the remaining SYSTEM datafile, the  instance may not open at all, or it may crash immediately or in best case scenario, it may open and remain stable so you can query DBA_SOURCE, SYS.SOURCE$, etc.


Exit code 1638 is Another version of this product is already installed. Installation of this version cannot continue. To configure or remove the existing version of this product, use Add/Remove Programs on the Control Panel


The problem you're facing is that you are not actually re-enabling your constraint (as defined by the NOCHECK).

The syntax for re-enabling a constraint is as follows:

ALTER TABLE <tablename>
WITH { CHECK | NOCHECK }
CHECK CONSTRAINT <constraintname>;

The WITH CHECK|NOCHECK tells SQL Server whether or not you want to check the existing data in the table against the constraint. If you state NOCHECK here, then your constraint will be untrusted, defined by is_not_trusted = 1 in sys.foreign_keys.

By stating NOCHECK as the second part, you're actually saying disable the constraint.

In your case, if you do not want to check the existing data, you'll need to NOCHECK CHECK.

ALTER TABLE [dbo].[Table] WITH NOCHECK CHECK CONSTRAINT FK_ConstraintName;

Further reading on trusting your constraints.


Restoring a database using Postgres data files instead of .sql files

Rsync is a supported backup method for postgresql.
However it has some caveats that you need to be aware of. Namely the database should be offline when the sync is done.

So if your database was offline when you synced, you could restore it jsut fine.


Clustered index performance consideration in MySQL & PostgreSQL

MySQL

While the MySQL Documentation literally says Typically, the clustered index is synonymous with the primary key, they are not one and the same. Please keep in mind that the clustered index (called gen_clust_index) was created in such a way that the index pages for the PRIMARY KEY and the table's row data coexist in the same pages. Having wide PRIMARY KEY values, such as a UUID, would make BTREE pages much wider. It could even cause data pages to split. Since the default innodb_page_size is 16KB in MySQL (this was a fixed compiled-in value in MySQL 5.5 and back), you must expect data pages to have fewer rows and less room for PRIMARY KEY navigation per 16KB page.

I have discussed PRIMARY KEY implications before : See my post InnoDB primary key efficiency

PostgreSQL

A StackOverflow post from Peter Eisentraut says The maximum length for a value in a B-tree index, which includes primary keys, is one third of the size of a buffer page, by default floor(8192/3) = 2730 bytes.

According to the PostgreSQL Wiki

  The maximum table size, row size, and maximum number of columns can be quadrupled by increasing the default block size to 32k. The maximum table size can also be increased using table partitioning.

From this, let's say you use 32K blocks instead of the default 8K. You could fit 4 times more info, but there is still a limit of some kind.

Fortunately, UUID is just 16 bytes. I would not expect earth-shattering drawbacks from it.

Analysis

InnoDB's use of the Clustered Index, order inflexible as it may be, would benefit from smaller keys and would lend itself to fast writes because of not having to manage as much space for distributing keys within the Clustered Index.

While PostgreSQL's storage engine is not bounded or tethered like that of MySQL's InnoDB, smaller keys definitely have to process faster and consume less space. This would increase read and write performance for PostgreSQL, MySQL or any other RDBMS.

To demonstrate how structural changes can make a difference, let's use MySQL other storage engine MyISAM (which is non-transactional and does not have Clustered Index). I once took a MyISAM table and changed its row format from Dynamic to Fixed-Length and got a 20% increase in performance without touching anything else. I made the data bigger to get better read performance. The write performance increased for the better as well because there were fewer mechanisms to trigger any space management (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?).

Just reading through MySQL Documentation on Optimizing Data Size you get phrases like

  Smaller tables normally require less main memory while their contents are being actively processed during query execution.

  Any space reduction for table data also results in smaller indexes that can be processed faster.

  Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

To further my point on smaller datatypes, I mention MySQL's SELECT ... PROCEDURE ANALYSE();. When you run SELECT * FROM tablename PROCEDURE ANALYSE();, the output is an analysis of your data, min values, max values, avg values, STD of values, and (here is the main point) the recommended data type for each column.

If you apply the ALTER TABLE commands to apply the recommended data types, the table has to end up smaller.

Even PostgreSQL would have to benefit from smaller data types. How ?

Please recall the PostgreSQL has this mechanism called TOAST (The Outside Attribute Storage Technique) that I have discussed before (See my post Proposal: MySQL blob handling revision has to juggle row data if there are large columns. Obviously, this mechanism would never be triggered is all row was small and many rows would fit comfortably in the 8K blocks PostgreSQL has.

Conclusion

Since your question seems to be more focused on PostgreSQL, let me answer your question like this:

  What is the impact if I pick UUID as the PK? Does the database write performance killer also exists in PostgreSQL like in MySQL?

PostgreSQL would process write faster with smaller column values. UUID is 16 bytes. Using an 8-byte integer as a PRIMARY KEY would be faster to write and process than a UUID. A 4-byte integer is even faster than that. Lesson from all this ? Don't slow yourself down with wider PRIMARY KEY values if you don't have to.


This depends on the specific application. Most AS400 DB2 applications have initial  Application Administration which configures privileges assigned to each application user. The administration role is normally a DBA or programming staff responsibility. To extract a list of Groups and/or users accessing specific functions speak to the DBA or Application Administrator.


How can I RAISE_APPLICATION_ERROR and do an INSERT in the same trigger?

Log type operations are usually done via AUTONOMOUS_TRANSACTION procedures.

Don't have a logging package, get one from GitHub.

https://github.com/OraOpenSource/Logger


Grrr.  Neither of the answers so far quite says the right things, so I feel obligated to give yet another Answer.

@jkavalik gives a nice description of why the problem occurred.  (But not what to do next.)

@DaveRix starts out good by saying "use RAM for caches, not RAM disk".  I very much agree.  But then he goes on to advocate dangerously high values for max_heap_size and tmp_table_size.  Those settings limit the amount of RAM allocated multiple time per connection.  That is, a complex query might allocate N*64MB.  This risks running out of RAM.  I suggest no more than 1% of RAM for them.

Another common mistake is to put tmpdir on a small disk filesystem.  This also causes abrupt halts.

Once tmpdir has room, @Cosi's query should run.  But perhaps not efficiently.  The next thing to do is look at the query, its EXPLAIN SELECT ..., the schema of the table(s), and especially the indexes.  Some common mistakes:

Subqueries instead of JOINs.
Single-column indexes when "composite" indexes would be better.


This is likely to be a many to many relationship.

create table drivers (
  driver_id int primary key,
  ...
);

create table trucks (
  truck_id int primary key,
  ...
);

create table drivers_trucks (
  driver_id int references drivers(driver_id),
  truck_id int references trucks(truck_id),

  primary key (driver_id, truck_id)
);


I hope this is not the case, or you're going to get tons of problems down the line.
It's a Bad Idea to have any kind of "special" character in column names and single quotes would be just mind-bendingly difficult to work with.
Try this:
select `'ID'` from table1 ;

(That's Back-Tick, Single-Quote, capital-Eye, capital-Dee, Single-Quote, Back-Tick.)
If that does work (and I really, really hope it doesn't), then go find out who created this table and give them a damned good kicking!
If it doesn't work, then run this command and send us the output from it:
show create table table1 \g


Why my transaction has not been replicated? What was the transaction sequence number?

This isn't a verified query as I'm not running replication, but after scripting out definitions for both sp_MSenum_distribution_sd and sp_MSget_repl_error via sp_helptext and smashing together the underlying logic, I came up with the following that may give you what you're looking for.

DECLARE @name NVARCHAR(100), @time DATETIME

SET @name = N'SQLAPPLON1-Product-Product-REPLON1-184',
    @time = N'20170410 15:50:27.650'

SELECT  msre.source_type_id,
        msre.source_name,
        msre.error_code,
        msre.error_text,
        sys.fn_replformatdatetime(msre.time) AS 'time',
        error_type_id,
        CASE WHEN msre.xact_seqno IS NULL OR msre.xact_seqno = 0x0
            THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS has_xact_seqno,
        msre.xact_seqno,
        msre.command_id,
        rh.error_id
FROM    [distribution].dbo.MSrepl_errors msre
        INNER JOIN [distribution].dbo.MSlogreader_history rh WITH (READPAST)
            ON msre.id = rh.error_id
WHERE   rh.agent_id = (SELECT TOP 1 ID
                       FROM MSlogreader_agents
                       WHERE name = @name)
        AND rh.start_time = @time
        AND rh.comments not like N'<stats state%'
        AND rh.error_id <> 0
        -- rows with error_type_id are placeholders
        AND msre.error_type_id IS NOT NULL
ORDER BY 5 ASC