Recommended memory configuration for many SQL Server instances per server

Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack.
We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size.

IMHO, your total RAM is too low. Please read my answer (with relevant links) SQL Server Maximum and Minimum memory configuration. They change when you have multiple instances of sql server running on a given host.
Capping SQL server max memory on a multi instance server is a balancing act and max memory is applicable to only buffer pool. If sql server needs more memory, it is going to use it.
You can even use Lock Pages in Memory (I would still opt for more memory before enabling LPM in your case).
As a starting point,

Baseline your instances. This will help you gauge what is good / acceptable for your workload.

Use OptimizeInstanceMemory script from Aaron's blog to help you get started. The blog post covers how to balance max memory dynamically when failover happens.

As a side note, you should monitor CPU, memory and disk utilization and based on the usage per client, you should charge them as well. Alternatively, you can move to Azure :-)


SQL Server Agent Job - Adding Version Number

This is a very interesting question. I think I agree with @Gareth Lyons in some way. My suggestion is that you append your version information in the step name of the first job step (after all, all jobs have at least a job step to be meaningful)
So what you can do is like this:
For the first time, say my job [test_job] has the following steps

Now I can run the following code to append the version info to the 1st step name
-- initially appending version name to step 1 name
declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 1.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name + ' - ' + @ver --adding version string to the step 1 name
from msdb.dbo.sysjobsteps s
inner join msdb.dbo.sysjobs j
on s.job_id = j.job_id
and j.name=@job_name
and s.step_id = 1;

exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

After running the script, you will see the job step name changed to the following

Later, if we need to update the version, we can easily operate on the 1st step name as the following
-- updating version name later when it is necessary

declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 2.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name
from msdb.dbo.sysjobsteps s
inner join msdb.dbo.sysjobs j
on s.job_id = j.job_id
and j.name=@job_name
and s.step_id = 1;

-- replace old version with new version number
set @step_1st_name = substring(@step_1st_name, 1, charindex('<V', @step_1st_name)-1) + @ver;
exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

If we run the script, we can see the job 1st step name is changed to the following

It is also very easy for you to extract the version number from any job's first step name.
I hope this may help you.


Run aspnet_regsql.exe from SQLCMD Post-Deployment Script

SQL Server Management Studio (SSMS) only supports a subset of the SQLCMD commands, and SQL Server Data Tools (SSDT) only supports a subset of the SQLCMD commands that are allowed in SSMS. The [:]!! SQLCMD command, while supported by SSMS, is not supported in SSDT Pre-Deployment or Post-Deployment SQL scripts. This is most likely due to the large amount of confusion that would be caused by people not realizing that the command that is executed by the [:]!! SQLCMD command actually runs on the machine where SQLCMD is running, which is not guaranteed to be consistent across executions, and is not necessarily the same machine that SQL Server is running on.

For example, when doing development in Visual Studio on your local workstation or laptop and running this deployment, the command would execute on your local workstation or laptop. Then, when deploying to testing / QA, it would run on whatever machine does that deployment, which is typically not your local workstation. And then when deploying to Production, it would again run on the build server, which may or may not be the same server that handled the deployment to the QA server.

If you change your Post-Deployment to do just the following and then do a build:

:!! dir

then you would see this error:

  72006: Fatal scripting error: Command Execute is not supported.

You have three choices (that I can think of, at least):

Run aspnet_regsql.exe via xp_cmdshell (which will run under the security context of the Log On account associated with the SQLSERVER service). This option allows you to manage the call to aspnet_regsql.exe within the deployment SQL script and will get executed whenever you do a build.
Edit the "Post-build event" to call this same command line. This will allow aspnet_regsql.exe to be executed in the security context of whatever Windows / Active Directory Login runs the deployment. But, the execution of aspnet_regsql.exe is not in the SQL script, so the publish process needs to use SqlPackage.exe so that it knows to execute whatever commands are in the "Post-build event". But, this is at least still part of the SSDT publish process and will get executed when you do a build.
Handle this outside of the SSDT deployment by creating a CMD script that will be executed on the server running SQL Server, and that calls SQLCMD to run your deployment SQL script and then aspnet_regsql.exe. This option, however, is outside of the SSDT publish process and won't get executed when doing a build in Visual Studio.


sp_addUser through sp_execute (SQL) in master db

Your dynamic text is not valid:

you should add spaces between IMMEDIATE and RESTORE, STATS = 5 and ALTER,
you should remove additional unclosed quotation mark here: SET MULTI_USER'' --> SET MULTI_USER'

Your

@sql = 'use ' + @dbname
    exec sp_executeSQL @sql

is executed in it's own context so you changed db context not for your session but only within your dynamic code execution so your next piece of dynamic code that grants is still executed in the context of current database.

You should always print your dynamic code before try to execute it and you'll see where the errors are.

But the best solution for your situation is to write once a plain(not dynamic) code for all databases, save it and run every time you need it.


Based on your story mine first idea what crosses mine mind was.

**article.**
 article_id
 article_name
 ...
 ...

**topic**
 topic_id
 article_id
 topic_name
 ...
 ...

**topic_has_many_articles** (many to many relation and your connection table)
  topic_id
  article_id

You need to use correct indexing for this to scale well with large datasets.


Stored procedure to change string to datetime in MySQL

Based on the fiddle you may apply
ALTER TABLE test
DROP COLUMN Date_TIMESTAMP,
ADD COLUMN Date_TIMESTAMP TIMESTAMP AS (STR_TO_DATE(Date_VARCHAR, '%Y-%m-%dT%h-%i-%s'));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=104e72ae4730b2c8c30f84f61bf392d7
The column Date_TIMESTAMP may be defined as STORED (occupies disk space, fast access) or VIRTUAL (calculated each time its value is retrieved, does not occupy disk space).

Alternatively you may use BEFORE INSERT and BEFORE UPDATE triggers which will convert inserted data into correct datetime literals (but the column will stay VARCHAR nevertheless). In BEFORE UPDATE you may check current data format to avoid double convertion which will cause an error.
CREATE TRIGGER trigger_name
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.Date_VARCHAR = STR_TO_DATE(NEW.Date_VARCHAR, '%Y-%m-%dT%h-%i-%s');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=08eb56a0b8846297c81c07e2703c0ab9


what is the best approach to send report by Email?

Yes sure.. you can use the subscription and delivery of Reporting Services to send a report.
The available formats are:

XML file with report data
CSV (comma delimited)
PDF
MHTML (web archive)
Microsoft Excel
TIFF file
Microsoft Word

(source)
For delivery via email look here.


PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID

This doesn't sound like the same thing.  The GCC version is different and your PostgreSQL version is different.  IIRC it was an issue with GCC 4.6 optimizations and was fixed.  I don't believe it should have impacted your setup.

More likely something changed which caused this.  Perhaps the slave was promoted to a master briefly and then demoted again?  That will create problems like this all the time.

The timeline issue suggests to me that this is in fact what happened. It doesn't matter whether you write to a db or not, just completing recovery and promoting is enough to break the timeline.


The maximum number of connections across SQL Server versions and editions is 32,767.
You can determine how many connections SQL Server currently has by looking at:
SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END
    , CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
    INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END;

If the ratio between used and unused connections from the above query is concerning, it is likely that connection pooling is enabled by client applications connected to the server, and those connections are not being efficiently used.  You may want to have developers modify the connection string for these applications to limit the size of the connection pool, and ensure they are properly disposing connections.  If connections are not being disposed correctly, they will remain open as long as the client application is running.
If you are feeling particularly rabid, and need to get rid of all the connections that haven't performed anything recently (regardless of if they are actually currently performing work), you can run the following code, which will generate a list of sessions that can be killed.  You'd need to copy-and-paste the generated commands into a new SSMS window to actually run the commands.  I'd also recommend having your résumé up-to-date just in case.

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';
SELECT @cmd = @cmd +
    CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END
    + 'KILL ' + CONVERT(VARCHAR(MAX), dec.session_id) + ';'
FROM sys.dm_exec_connections dec
WHERE dec.most_recent_sql_handle = 0x0;

PRINT @cmd;

It is possible to linearly scale the number of connections beyond 32,767 by sharding the data across multiple SQL Server nodes.  However, in my opinion, using sharding as a way to get around the limit on the number of connections is similar to using an atom bomb to kill a spider.  It will kill the spider, but you just might have bigger problems at the end of the day.  Not to mention it's pretty darn hard to build an atom bomb, not to mention implement sharding properly.


I am not sure your logic behind those joins but some part of the query can be optimised as; (for ex; WHEN t.total_ordered < t.total_received THEN 18 of your first CASE does not make sense at all as ELSE part is also equal to18`)

EDIT: Sub query brought back in with some optimisations. Syntax looks alright now.

UPDATE c2 SET stage_id =
         CASE
           WHEN t.required_quantity  <= t.total_received THEN 27
         --WHEN t.total_ordered < t.total_received THEN 18 (NOT NEED THIS BIT)
           ELSE 18
         END

FROM component c2 INNER JOIN
     (SELECT c.component_id,
            CASE mp.component_name_id WHEN 2 THEN l.quantity*2
                 ELSE l.quantity
            END AS required_quantity,
            SUM(received) AS total_received,
                    --(NOT NEED THIS BIT AND OTHER LIST OF COLUMNS)
                    --SUM(ordered) AS total_ordered
     FROM line l
        LEFT JOIN component c ON c.line_id = l.line_id
        LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND
              l.line_no = mp.line_no AND c.component_name_id =
                                                   mp.component_name_id
     WHERE
            mp.job_id IS NOT NULL
            AND (ISNULL(mp.cancelled,0) = 0) --(OR CONDITION REMOVED)
            AND (ISNULL(mp.manual_override,0) = 0) --(OR CONDITION REMOVED)
            AND c.stage_id = 18
     GROUP BY  mp.job_id,  mp.line_no,  mp.component,
                l.quantity, mp.component_name_id, component_id) AS t

ON c2.component_id = t.component_id --(INNER JOIN)


SQL Server : database recovery from snapshot files

Depends on what these bcp files are. If there is a plenty of them and they are named after tables in your database, then chances are, they are table dumps created with (probably) bcp.exe. Also looking at their creation dates might provide some insight.

If the naming pattern does not correlate with tables, maybe these are database backups. You can check this by running restore headeronly against them - if they are indeed MSSQL backups, you will see some data about when they were created and what kind of backups they are.


Delete document from nested array in MongoDB

If you want to use the $ positional operator, you have to have matched the array items already, something like this:

db.test.update(
  {"attendance.students.studentId":{$in:[1,2,3]}},
  {$pull: {"attendance.$.students": {studentId: {$in:[1,2,3]}}}},
  {multi:true}
)

Remember that this will only update the first matching item in the attendance array in each document, so you will have to run this update more than once to make it update each attendance item.


Finding films where 2 specific actors appeared together. (sakila db)

In the first case you get a combination of all pairs of actors for each film. Try this query:
select f.film_id, fa1.actor_id, fa2.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join film_actor fa2
    on f.film_id=fa2.film_id

    inner join actor a2
    on fa2.actor_id=a2.actor_id
where f.film_id = 82;

In the second case you just join the same actor details twice:
select f.film_id, fa1.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join actor a2
    on fa1.actor_id=a2.actor_id
where f.film_id = 82;

So there are no rows which correspond to your where conditions (a1.first_name="CATE" and a2.first_name="cuba")
Additional explanation:
SELECT * FROM film - you get the list of films (one row per film)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id - you get the list of actors for the film (multiple lines for each film and each line contains one actor id)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id - you get the same rows as above but with additional details for each actor (each line is still related to only one actor, there is no pairs of actors)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id JOIN actor a2 ON fa1.actor_id=a2.actor_id - the same results as above, you just joined the same details for the same actor twice
SELECT *
FROM film f
JOIN film_actor fa1 ON f.film_id = fa1.film_id
JOIN film_actor fa2 ON f.film_id = fa2.film_id

After the first join you get the list of actors for the film. And on the second join you add the same list of actors to each line from the above result and get the list of all pairs of actors for each film (as you can see on screenshot above)


Executing a SQL Job only once a month and after completion of a specific job

For SQL Server, you can specify On success action to go to the next step, only when the job step #1 is completed. There, use a date/time condition to execute the job#2

if (day(getdate()) = 1 and DATEPART(hh, GETDATE())= 1)
EXEC dbo.sp_start_job N'job#2'


MySQL Workbench doesn't show relationships between tables when using Reverse Engineering(Mac OS)

After further research it seems this is actually a bug.
Instead of directly reverse-engineering your database:

create a new model

save all your code in an .sql file

while in the model tab go to file-import-reverse engineer create script

check "place imported objects on diagram" and execute and there you go

Still unsure why the first above example won't work(with the straightforward approach) and the second one will but well... hope this helps.


SQL Server - Log file not shrinking in AlwaysOn group

The issue is resolved. Apparently one of programs connecting to same large DB had been running for more than 24-hours, seemed it was in "hung" state, had several open connections to secondary replica-DB caused blocking. I guess that was causing not completing log replication from primary to secondary, because when I ended those programs, immediately blocking were gone and Redo Queue Size started decreasing instead of previously increasing. After about an hour or so, log file in primary node showed more than 80% available free space.

dbcc opentran and sp_who2 didn't give that much details to track this issue. What helped was

select * from sys.sysprocesses where blocked<>0
dbcc inputbuffer(spid)

The first statement was to find out what was causing blocks, and second one to kill.
I am just sharing if it helps others in similar situation.

TLog backup is being taken for all user-Databases in every hour only on primary node as mentioned in my post. So far TLog physical file-size was small, wouldn't increase and most of the time had more than 90% available free space - which is what its supposed to be. So after the issue is resolved, only difference is file-size is very big which I understand because it kept on growing. Now I need to do a TLog shrink (we did before) and it should go back to initial size.

TLog back doesn't need to be taken on all replicas. Technically you can, but if you are taking on all replicas, in case of Database restoration you have to gather all TLog backups from all Nodes and restore them which may be complicated.


OPENJSON running once per row of an INNER JOIN unnecessarily?

I ended up having to solve this by creating a table variable to hold the results of OPENJSON and joining on that variable.  This forces SQL Server to actually create a temp table containing the query data rather than repeatedly running OPENJSON.  Apparently a CTE doesn't do that, even though I thought it did.  Presumably this applies to any table-valued function, actually.
This works, and results in OPENJSON only being run once (when deriving the data to fill the table variable with):
DECLARE @jsonQueryText nvarchar(max) = '[{"UniqueId":"0xy-5244249247924974924C","MobileNo":null,"Email":"joe.bloggs@domain.co.uk"},{...},...]';
DECLARE @Qry TABLE(UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));
INSERT INTO @Qry SELECT * FROM OPENJSON(@jsonQueryText) WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));

SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN @Qry qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])


Slave start to generate relay log files but not master server

You need a log-bin entry in your master my.cnf file

Per http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin. If the basename is given, the server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory.

After setting the log-bin and restarting the master, you then can run RESET MASTER; and then SHOW MASTER STATUS; to get the correct values for your CHANGE MASTER command for your slave.


Executing query against Azure SQL db using Linked server takes hours

I think you're running into a key limitation of distributed queries (e.g. four part notation).  As per this MS blog post:

  data for remote queries are collected from the remote servers,
  scrubbed locally, combined together and presented to end user as single record set

Basically the source tables in their entirety are trying to get pulled over the linked server and all the joins and filtering are happening locally.  When you're not working with millions of records this is often transparent, but when you include large tables you can immediately feel the pain.

In your situation, I suggest you push a copy of the SAExMark.dbo.RealGreenZipCodeData table up to your Azure DB and then run this UPDATE statement from a connection made directly to the AzureDB.  If you need to initiate the process from a different server, stuff the update into a Stored Procedure in the Azure DB and then execute the SP remotely.

Alternatively, if you don't want/can't push that table to Azure, you could convert the query to utilize OPENQUERY for all the remote join operations.  This will quickly get ugly, but it's possible.  OPENQUERY will force execution of whatever query you pass to it remotely, therefore allowing the join logic against that large table to be done remotely so only whatever records are returned from that query travel back over the linked server.  These would then be joined against the local copy of the SAExMark.dbo.RealGreenZipCodeData to identify what records in AZUREDB.dbname.dbo.DealerMedias would need to be updated.  I really don't like this approach just to update a table on a remote database, but it's an option.


(Seems like I'm able to post detailed answer here today... let me know if this has helped you)
Here is a test output for you...

mysql> truncate table t1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 select * from t;
Query OK, 3842688 rows affected (36.19 sec)
Records: 3842688  Duplicates: 0  Warnings: 0

mysql>
mysql> \! rm /tmp/t.txt
mysql> select *  from t into outfile '/tmp/t.txt';
Query OK, 3842688 rows affected (7.24 sec)

mysql> load data infile '/tmp/t.txt' into table t1;
Query OK, 3842688 rows affected (25.20 sec)
Records: 3842688  Deleted: 0  Skipped: 0  Warnings: 0

With larger records and data-size you might see increased margin of difference.

If those are MyISAM tables then you might want to review following notes  from documentation:

  To ignore foreign key constraints during the load operation, issue a
  SET foreign_key_checks = 0 statement before executing LOAD DATA.

  If you use LOAD DATA INFILE on an empty MyISAM table, all nonunique
  indexes are created in a separate batch (as for REPAIR TABLE).
  Normally, this makes LOAD DATA INFILE much faster when you have many
  indexes. In some extreme cases, you can create the indexes even faster
  by turning them off with ALTER TABLE ... DISABLE KEYS before loading
  the file into the table and using ALTER TABLE ... ENABLE KEYS to
  re-create the indexes after loading the file.

Also loading into/from memory tables is much faster but based on size you've  mentioned I suspect we have sufficient memory (you might see table-full errors and issues due to that...)!

Anyways, try following commands and share the speed results:

select *  from t into outfile '/tmp/t.txt';

load data infile '/tmp/t.txt' into table t1;

(I guess <60 mins but no magic...)


How to connect to a Postgres server in a Docker (1.12.0) container on a Mac?

I had the same problem today. I discovered that you do have to specify the -h flag for it to work:

psql -U postgres -p 5433 -h localhost

You can also use the URI style connection string:

psql postgresql://postgres:<your_password>@localhost:5433

The default is "local socket" which does not work in the case of docker.


There is a similar Stack Overflow Post  on the subject. Your idea of mapping Oracle packages to schemas in SQL Server is the approach deemed most directly analogous.


Trigger function does not exist, but I am pretty sure it does

You must use a function, not a procedure:

CREATE FUNCTION pro_update_last_read() RETURNS trigger ...

The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function.

The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.


Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id

Okay, had to do this in three steps:

To mark which records we want to keep (first record for each customer, records where the status changed)
To get the prior row's stream_datetime and calculate the the difference between
Select only the rows where the order_status was 'cancelled'.

SELECT
  customer_id
 ,purchase_day
 ,lifetime
FROM
(
  SELECT
    customer_id
   ,order_status
   ,LAG(stream_datetime) OVER (PARTITION BY customer_id ORDER BY stream_datetime) AS purchase_day
   ,CAST(stream_datetime AS DATE) - LAG(CAST(stream_datetime AS DATE)) OVER (PARTITION BY customer_id ORDER BY stream_datetime) AS lifetime
  FROM
    (
      SELECT
        customer_id
       ,stream_datetime
       ,order_status
       ,CASE
          WHEN customer_id <> LAG(customer_id,1,-1) OVER (PARTITION BY customer_id ORDER BY stream_datetime) THEN 'Y'
          WHEN order_status <> LAG(order_status,1,'') OVER (PARTITION BY customer_id ORDER BY stream_datetime) THEN 'Y'
          ELSE 'N'
        END AS change_ind
      FROM
        Payment
      WHERE
        order_status IN ('completed','cancelled')
    ) payment_summary
  WHERE
    change_ind = 'Y'
) payment_change
WHERE
  order_status = 'cancelled'
    AND purchase_day IS NOT NULL

Results in:
customer_id     purchase_day            lifetime
3               2020-01-11T17:59:00Z    60
4               2020-01-13T15:29:00Z    60
4               2020-04-13T15:29:00Z    122

You can test with this Fiddle.
I'd recommend running the subqueries by themselves to get a feel for how each step works and so you can be confident in the results.

Your query worked pretty well!. But I forgot to tell something: maybe
the order_status = cancelled come first than order_status = completed.
This is due a mistake in the business, but it can happen. If this
happen, so we cannot consider as a churned client.

Ok, this is easy enough to fix given how things are set up.  If the cancel has been entered erroneously, purchase_day will be NULL (we already ignore instances with two consecutive statuses), so we can just change the final WHERE to:
WHERE
  order_status = 'cancelled'
    AND purchase_day IS NOT NULL

Updated fiddle for you to test.


Should I nest dependent outer joins in SQL Server?

This is absolutely not a canonical answer but I noticed that for the nested loops query plans shown in the SQL Fiddle it was possible to apply the plan from Query 2 to Query 1 with the use of the USE PLAN hint but attempting the reverse operation fails with

  Query processor could not produce query plan because USE PLAN hint
  contains plan that could not be verified to be legal for query. Remove
  or replace USE PLAN hint. For best likelihood of successful plan
  forcing, verify that the plan provided in the USE PLAN hint is one
  generated automatically by SQL Server for the same query.

Disabling the optimizer transformation rule ReorderLOJN prevents the previously successful plan hint from succeeding too.

Experimenting with greater quantities of data shows that SQL Server is certainly capable of transforming (A LOJ B) LOJ C to A LOJ (B LOJ C) naturally as well but I didn't see any evidence that the reverse is true.

A very contrived case where the first query performs better than the second is

DROP TABLE  MyGrandChild , MyChild,  MyParent

CREATE TABLE MyParent
(Id int)

CREATE TABLE MyChild
(Id int PRIMARY KEY
,ParentId int,
Filler char(8000) NULL)

CREATE TABLE MyGrandChild
(Id int
,ParentId int)

INSERT INTO MyChild
                      (Id, ParentId)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY @@SPID),
                     ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values  v1, master..spt_values

INSERT INTO MyGrandChild
                      (Id, ParentId)
OUTPUT INSERTED.Id INTO MyParent
SELECT TOP (3000) Id, Id AS ParentId
FROM MyChild
ORDER BY Id

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN MyChild AS c
         ON c.[Id] = gc.[ParentId]
       LEFT OUTER JOIN MyParent AS p
         ON p.[Id] = c.[ParentId]

SELECT gc.Id       AS gcId,
       gc.ParentId AS gcpId,
       c.Id        AS cId,
       c.ParentId  AS cpId,
       p.Id        AS pId
FROM   MyGrandChild AS gc
       LEFT OUTER JOIN( MyChild AS c
                        LEFT OUTER JOIN MyParent AS p
                          ON p.[Id] = c.[ParentId])
         ON c.[Id] = gc.[ParentId]

Which gives plans

For me Query 1 had an elapsed time of 108 ms vs 1,163 ms for Query 2.

Query 1

Table 'Worktable'. Scan count 0, logical reads 0
Table 'MyChild'. Scan count 0, logical reads 9196
Table 'MyGrandChild'. Scan count 1, logical reads 7
Table 'MyParent'. Scan count 1, logical reads 5

Query 2

Table 'MyParent'. Scan count 1, logical reads 15000
Table 'MyChild'. Scan count 0, logical reads 9000
Table 'MyGrandChild'. Scan count 1, logical reads 7

So it might be provisionally assumed that the first ("unnested") syntax is potentially beneficial as it allows more potential join orders to be considered but I haven't done exhaustive enough testing to have much confidence in this as a general rule.

It may well be entirely possible to come up with counter examples where Query 2 performs better. Try both and look at the execution plans.


Improve the performance of ST_Within boundary lookups in PostGIS

I had a similar issue, I find a faster way (30% than using joins an ST_Within in my case) using python with the shapely package, shapely has a method called .contains that works similar to ST_Within. I'm not sure if .contains is faster than ST_Within but using a scripting language allows you to split the processes at different times, and that's the key point from my understanding.
These would be the steps I done:

Store all geometries as a huge .json or several .json files (in my case I have about 15,000 geometries divided into about 500 .json files), you should find fast the geometry or the groups of geometries you want using a key. You could store a geo_json or the hexadecimal, see documentation.
Iterate over all geometries in the .json and keep it in memory as shape, maybe as a list() of  dict() making a relation like [{"id":5, "shape": <shape object ..>}]. In this step you have all relevant geometries in memory and you could access them instantly using a key or keys. The limitation here is the memory available.
Iterate over points (as Point) and inside iterate over precalculated shapes using .contains. Be careful because we are comparing all points to all geometries.
Store the result you want, depending on the implementation. This is one connection and one query (insert, update..).

Step 1 should be run only once or every time there is an update on any geometry.
Step 2 should be executed at the beginning of the script only once outside the loops.
Step 3: Depending on the exact implementation this needs to be improved, for example you should reduce as much as possible the geometries to iterate, maybe at first match you could break the iterations.


INDEX(source_id, date_time, value) is optimal, partially because it is "covering".  "Covering" means that the query can be completely handled by the columns in the index.  I am assuming you did not water down the query?
The column tested by = must come first, regardless of cardinality.  Else, If date_time is first, it will have to read the entire index.
The EXPLAIN estimates that source_id = 2 44% (4418476/10M) of the time.  Reading 4M rows is a lot better than 10M.
Please provide SHOW CREATE TABLE; without it, I am making guesses in my Answer.
Both of your attempted indexes have to bounce between the index BTree and the Data BTree.
Probably between 1GB's and 4GB's worth of blocks (16KB - data or index) needed to be pulled into cache (buffer_pool) to satisfy your query.  Probably it was entirely in the 4GB buffer_pool when it took 7s.  And in the 1GB, there was not room, so it was at least partially I/O-bound.
The suggested index will easily fit in 1GB buffer_pool.  And only 44% of it is needed for this query.
When you add my composite query, get rid of the existing one that is a prefix of it; it will be redundant and unnecessary.
As your data grows, someday the index will become too big for 1GB.  That's a fact of life.
However, by building and maintaining a [Summary table(http://mysql.rjweb.org/doc.php/summarytables), you can make the equivalent query take less than 1 second on either server, even as the table grows.  (It would probably have 3 columns: source_id, date (no time), and sum(value).)


how to sync local mysql database and live server database?

Hello you can sync your local database data with your live mysql server. I had done it making a local sync file that will present all the tables of the local database that I wanted to update to live database and then calling a call back function in footer file that function calls the function to sync local data to live server

Footer.php

    jQuery(document).ready(function($){
    working = false;
    var do_sync = function(){
        if ( working ) { return; }
        working = true;
        jQuery.post(
            "<?php echo $this->config->item("sync_url"); ?>",
            {},
            function(ret){
                working = false;
            }
        );
    }
    window.setInterval(do_sync, 10000);
});

Config.php

$config['sync_server_url'] = "http://livesite.com/index.php/sync_server";
$config['sync_url'] = "http://localhost/localsite/index.php/sync";

sync.php contents Also you need to on CURL extension in local site wamp so that it can communicate with the server side, If database record that is already synced to server edited then sync field in DB should be set to 0 so that on edit it can overwrite server DB again.

Sync.php

<?php
    class Sync extends CI_Controller {
        private $tables;
        function __construct() {
            parent::__construct();
            $this->load->database();
            $this->tables = array(
                "customers" => array("per_id"),
                "db_log" => array("id"),
                "inventory" => array("tr_id"),
                "material" => array("id_mat"),
                "mtracking" => array("id_tra"),
                "orecipie" => array("id_rec"),
                "moutlets" => array("id_st"),
                "opeople" => array("per_id"),
                "oreceivings" => array("rec_id"),
                "osales" => array("sal_id"),
                "osales_items" => array("sal_id", "itm_id"),
                "osales_payments" => array("sal_id"),
                "osales_sus" => array("sal_id", "emp_id"),
                "osales_sus_items" => array("sal_id", "itm_id"),
                "osales_sus_payments" => array("sal_id"),
                "osuppliers" => array("per_id"),
            );

            $this->id_store = $this->config->item('id_store');

        }

        function index() {
            echo '
'.date("H:i:s A");

            $newLine = "\r\n";
            $output = $update = "";

            foreach($this->tables as $table_name => $columns ){

                $col_check = "SHOW COLUMNS FROM `{$table_name}` LIKE 'is_sync'";
                $col_exists = $this->db->query($col_check);
                if ( $col_exists->num_rows > 0 ) { // is_sync column exists for this table
                    $sql = "SELECT * FROM {$table_name} WHERE is_sync = 0 LIMIT 500" ;
                    $rows = $this->db->query($sql);
                    if ( $rows->num_rows > 0 ) { // where is_sync = 0
                        foreach ( $rows->result_array() as $row ) {
                            $col_val = $update_col_val = $already_exists = array();
                            foreach($row as $name => $val) {
                                if ( is_null( $val ) ) {
                                    continue;
                                }
                                $val = mysql_real_escape_string($val);

                                $update_col_val[] = " `{$name}` = '{$val}' ";

                                if ( "is_sync" === $name ) {
                                    $val = 1;
                                }

                                if ( "id_store" === $name ) {
                                    $val = $this->id_store;
                                }

                                $col_val[] = " `{$name}` = '{$val}' ";

                                if ( $name === $columns[0] ){
                                    $already_exists[] = " `$columns[0]` = '{$val}'";
                                }

                                if ( $name === $columns[1] ){
                                    $already_exists[] = " `$columns[1]` = '{$val}'";
                                }

                            }

                            if ( is_array($col_val) && count($col_val) > 0 ) {

                                //echo $this->table_keys[$table_name];
                                $output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists) . " ||";
                                $output .= "INSERT INTO `{$table_name}` SET " . implode(",", $col_val) . " ||";
                                $output .= "UPDATE `{$table_name}` SET " . implode(",", $col_val) . " WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists);
                                $output .= " ;; ";

                                $update .= "UPDATE {$table_name} SET is_sync = 1 WHERE " . implode(" AND ", $update_col_val) . " ;; ";
                            }
                        }
                    } // if is_sync = 0

                } // if is_sync column exists
            }

            if ( !empty($output) && $this->post_data($output) ) {
                $update = explode(" ;; ", $update);
                foreach($update as $upq){
                    if (!empty($upq) ) {
                        $this->db->query($upq);
                    }
                }
            } else {
                echo $result;
            }

            echo date("H:i:s A");

        }

        function post_data($qry){
            $qry = htmlspecialchars(urlencode($qry));
            $data = "qry=" . $qry;
            $ch = curl_init();
            curl_setopt( $ch, CURLOPT_URL,$this->config->item("sync_server_url") );
            curl_setopt( $ch, CURLOPT_AUTOREFERER, 1);
            curl_setopt ( $ch, CURLOPT_HTTPHEADER, array ( 'Content-length: ' . strlen($qry) ) );
            curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt( $ch, CURLOPT_POST,1);
            curl_setopt( $ch, CURLOPT_POSTFIELDS,$data);
            curl_setopt( $ch, CURLOPT_CRLF, 1);
            $result = curl_exec($ch);
            curl_close($ch);
            if ( 1 === intval($result) ) {
                return TRUE;
            } else {
                echo $result;
                return $result;
            }
        }

    }
?>

Then after that you need to make a controller on live server I named it sync_server.php

sync_server.php

<?php
    class Sync_server extends CI_Controller {
        function __construct() {
            parent::__construct();
            $this->load->database();
        }

        function index() {
            if (isset( $_POST['qry'] ) ) {
                $qry = htmlspecialchars_decode( urldecode( $_POST['qry']));

                //$this->connect_local_db();

                $qry = explode(" ;; ", $qry);

                foreach($qry as $q) {

                    $q = explode( "||", $q );

                    //print_r($q);

                    $exists = mysql_query( $q[0] ) or die ("
" . mysql_error() ."
" . $q[0]);

                    if ( $exists && mysql_num_rows( $exists ) ) {
                        mysql_query( $q[2] ) or die ("
" . mysql_error()."
".$q[2]);
                    } else{
                        mysql_query( $q[1] ) or die ("
" . mysql_error()."
".$q[1]);
                    }

                    //$this->db->query($q);
                }
                die("1");
            } else {
                echo "qry not found";
            }
        }

    }
?>


SQL Server 2012 AlwaysOn Availability Group active/passive

You have to do two things

ALTER AVAILABILITY GROUP group_name SECONDARY_ROLE ALLOW_CONNECTIONS =  NO

The above will not allow connections to secondary. So your secondary will be TRUE standby and will be used when you do a failover.

ALTER AVAILABILITY GROUP group_name AUTOMATED_BACKUP_PREFERENCE = 'PRIMARY'

The above will indicate that your maintenance especially, backups will always happen on PRIMARY replica.

Since we are talking about maintenance, its worth mentioning the Recommendations for Index Maintenance with AlwaysOn Availability Groups


Is there any database system that allows to have different schemas for different users but the same content for all users?

Do you define "different content" as row filtering via security settings? That is pretty standard - and pretty unusual (as in: standard for high security, not all databases support this thinking)... SQL Server for example is just adding this with version 2016 due this year. In most cases... people do not bother as the filtering of what data is visible is done on the application layer (possibly in a server obviously).


Getting the Date of Some Day of Last Week

This takes advantage of the fact that 1900-01-01 is a Monday. So adding n number of weeks will also be a Monday.

Here are the datetime values that are being represented by the integers in that expression:

dateadd(week, datediff(week, 7 /*'19000108'*/, getdate()), 0 /*'19000101'*/)

Adding the number of weeks since 1900-01-08 (the week after the first Monday) to 1900-01-01 returns one week less than the current week, i.e. Monday of last week.

These three are equivalent:

select dateadd(week, datediff(week, 7 , getdate()), 0)
select dateadd(week, datediff(week, '19000108', getdate()), '19000101')
select dateadd(week, datediff(week, 0, getdate())-1, 0)

rextester demo: http://rextester.com/BQX59903


Normalised database with one-to-one relationship

The attributes should be split in 2 tables because the configuration details are not depending on the user but on the configuration of an external system. If a user can have only 1 configuration then you must use an 1-1 relation. If a user can have multiple configurations then you must use the 1-M relation.

This is the theory. If you want (in case of a 1-1 relation) put all in 1 table than this is also 'allowed'. Remember well that if one day the relation becomes 1-M that you are in problems. This is also why it is preferred to split the information into 2 tables.


The root cause could be very varying, specially if your slave is not in read_only mode. Clearly, the table has a data drift (means that both master and slave tables have different contents).

I suggest you to use pt-table-checksum in order to check the table consistency.

However, at this point I think that your table lacks of consistency, so I will recommend to dump and restore that table into the slave or, in s straightest way, clone again your slave. If you plan to rebui


MYSQL Calculate ranking of row that is not yet inserted

BEGIN;
INSERT the row
calculate the rank; put it into an app variable
ROLLBACK;

The ROLLBACK lets you undo the INSERT.  But meanwhile, you have grabbed the rank.


Changing primary key from IDENTITY to being persisted Computed column using COALESCE

FIRST

You probably don't need all three columns: old_id, external_id, new_id. The new_id column, being an IDENTITY, will have a new value generated for each row, even when you insert into external_id. But, between old_id and external_id, those are pretty much mutually exclusive: either there is already an old_id value or that column, in the current conception, will just be NULL if using external_id or new_id. Since you won't be adding a new "external" id to a row that already exists (i.e. one that has an old_id value), and there won't be any new values coming in for old_id, then there can be one column that is used for both purposes.

So, get rid of the external_id column and rename old_id to be something like old_or_external_id or whatever. This shouldn't require any real changes to anything, yet reduces some of the complication. At most you might need to call the column external_id, even if it contains "old" values, if app code is already written to insert into external_id.

That reduces the new structure to be just:

PkId AS AS COALESCE(old_or_external_id, new_id, -1) PERSISTED NOT NULL,
old_or_external_id INT NULL, -- values from existing record OR passed in from app
new_id INT IDENTITY(2000000, 1) NOT NULL

Now you have only added 8 bytes per row instead of 12 bytes (assuming that you aren't using the SPARSE option or Data Compression). And you didn't need to change any code, T-SQL or App code.

SECOND

Continuing down this path of simplification, let's look at what we have left:

The old_or_external_id column either has values already, or will be given a new value from the app, or will be left as NULL.
The new_id will always have a new value generated, but that value will only be used if the old_or_external_id column is NULL.

There is never a time when you would need values in both old_or_external_id and new_id. Yes, there will be times when both columns have values due to new_id being an IDENTITY, but those new_id values are ignored. Again, these two fields are mutually exclusive. So what now?

Now we can look into why we needed the external_id in the first place. Considering that it is possible to insert into an IDENTITY column using SET IDENTITY_INSERT {table_name} ON;, you could get away with making no schema changes at all, and only modify your app code to wrap the INSERT statements / operations in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements. You then need to determine what starting range to reset the IDENTITY column to (for newly generated values) as it will need to be well above the values that the App code will be inserting since inserting a higher value will cause the next auto-generated value to be greater than the current MAX value. But you can always insert a value that is below the IDENT_CURRENT value.

Combining the old_or_external_id and new_id columns also does not increase the chances of running into an overlapping value situation between auto-generated values and app-generated values since the intention of having the 2, or even 3, columns is to combine them into a Primary Key value, and those are always unique values.

In this approach, you just need to:

Leave tables as being:

PkId INT IDENTITY(1,1) PRIMARY KEY

This adds 0 bytes to each row, instead of 8, or even 12.
Determine the starting range for app-generated values. These will be greater than the current MAX value in each table, but less than what will become the minimum value for the auto-generated values.
Determine what value the auto-generated range should start at. There should be plenty of room between the current MAX value and plenty of room to grow, knowing at the upper limit is just over 2.14 billion. You can then set this new minimum seed value via DBCC CHECKIDENT.
Wrap app code INSERTs in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements.

SECOND, Part B

A variation on the approach noted directly above would be to have the App code insert values starting with -1 and going down from there. This leaves the IDENTITY values as being the only ones going up. The benefit here is that you not only don't complicate the schema, you also don't need to worry about running into overlapping IDs (if the app-generated values run into the new auto-generated range). This is only an option if you aren't already using negative ID values (and it seems pretty rare for people to use negative values on auto-generated columns so this should be a likely posibility in most situations).

In this approach, you just need to:

Leave tables as being:

PkId INT IDENTITY(1,1) PRIMARY KEY

This adds 0 bytes to each row, instead of 8, or even 12.
The starting range for app-generated values will be -1.
Wrap app code INSERTs in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements.

Here you still need to do the IDENTITY_INSERT, but: you don't add any new columns, don't need to "reseed" any IDENTITY columns, and have no future risk of overlaps.

SECOND, Part 3

One last variation of this approach would be to possibly swap out the IDENTITY columns and instead use Sequences. The reason to take this approach is to be able to have the app code insert values that are: positive, above the auto-generated range (not below), and no need for SET IDENTITY_INSERT ON / OFF.

In this approach, you just need to:

Create Sequences using CREATE SEQUENCE
Copy the IDENTITY column to a new column that doesn't have the IDENTITY property, but does have a DEFAULT Constraint using the NEXT VALUE FOR function:

PkId INT PRIMARY KEY CONSTRAINT [DF_TableName_NextID] DEFAULT (NEXT VALUE FOR...)

This adds 0 bytes to each row, instead of 8, or even 12.
The starting range for app-generated values will well above what you think the auto-generated values will approach.
Wrap app code INSERTs in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements.

HOWEVER, due to the requirement that code with either SCOPE_IDENTITY() or @@IDENTITY still functions properly, switching to Sequences is not currently an option as it appears that there is no equivalent of those functions for Sequences :-(. Sad!


Oracle: i want to setup some variables for login.sql

Unless you absolutely have to have the platform as part of the alias, I would simply pull the platform from v$database:

select 'You are:          '||upper(user)  "Welcome" from dual union all
select 'Logged into:      '||upper(sys_context('userenv','db_name')) from dual union all
select 'Database Version: '||banner from v$version where rownum <= 2 union all
select 'On Platform:      '||platform_name from v$database;


Get database creation timestamp from PostgreSQL?

There's no built-in method to do so, but there are workarounds.

Note however that even PG_VERSION changes when the server is updated to a new major version.

So the only reliable way to store a creation date is to make a one-row table with the date in it.

If this feature is important to your application you could consider funding its development. I think it'd be pretty trivial to implement.


Obvious inefficiencies in this Netezza query?

I have noticed that the optimizer doesn't enforce the constraints on left joins efficiently.  I have success in this situation by forcing the constraints in sub queries.  Consider revising your query like below:

CREATE TABLE T3 AS
SELECT
        A.*,
        COALESCE(C.FIELD1,0) AS FIELD1A,
        COALESCE(B.FIELD2,0) AS FIELD2A,
        COALESCE(B.FIELD3,0) AS FIELD3A,
        COALESCE(C.FIELD4,0) AS FIELD4A,
        COALESCE(C.FIELD5,0) AS FIELD5A,
        B.KEY1,
        COALESCE(C.FIELD6,0) AS FIELD6A
FROM T1 A
LEFT JOIN
(select * from T2 where TIMEFIELD1 <= DATE '28FEB2013'
AND  TIMEFIELD2 > DATE '28FEB2013'
AND  FIELD7= 'X' ) B
ON   A.KEY2 = B.KEY1
LEFT JOIN
(select * from T2  where AND  TIMEFIELD1  <= DATE '28FEB2013'
AND  TIMEFIELD2 > DATE '28FEB2013'
AND  FIELD7= 'X') C
ON   A.KEY3 = C.KEY1

DISTRIBUTE ON RANDOM


You would need to connect to the ASM instance using a user ID that has the SYSASM system privilege:

connect scott as sysasm;

Balaz Papp's comment above is correct in the explanation why connected without a connect string is working. And his comment below is correct; any user could be granted query access to the view, when connected to the regular Oracle database instance.


How can I delete a corrupted table space?

If you just want to remove the tablespace have you tried logging in as sys and dropping it?

DROP TABLESPACE <tablespace>
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

If so, what error message do you receive? (I'm assuming you've made a cold backup of the database at this point in case the tablespace is needed.)

Alternatively, if you can determine which datafile is corrupt you can remove this from the tablespace:

ALTER DATABASE DATAFILE '<filename>' OFFLINE DROP;

Make sure that <filename> is the filename known to Oracle, especially if any links may be involved on disk! Otherwise you will get misleading errors about non-existent files.


You want RETURN QUERY EXECUTE for dynamic SQL.

With RETURN QUERY you're trying to use a string literal as the start of a query. That's nonsensical and raises a syntax error.

Also, use format with the %I and %L specifiers for identifiers and literals, instead of using concatenation. Otherwise you have SQL injection issues in your function.


SQL Server switched to use pagefile exclusively instead of RAM and effectively froze the world

This surprising behavior caused downtime. Is this a documented feature?

Yes this is, its documented in RAM, virtual memory, pagefile, and memory management in Windows. Quoting from this suport article

  RAM is a limited resource, whereas for most practical purposes, virtual memory is unlimited. There can be many processes, and each process has its own 2 GB of private virtual address space. When the memory being used by all the existing processes exceeds the available RAM, the operating system moves pages (4-KB pieces) of one or more virtual address spaces to the computer’s hard disk. This frees that RAM frame for other uses. In Windows systems, these “paged out” pages are stored in one or more files (Pagefile.sys files) in the root of a partition. There can be one such file in each disk partition. The location and size of the page file is configured in System Properties (click Advanced, click Performance, and then click the Settings button).

The reason why it froze because disk speed is much much slower than speed in memory. After paging portion of disk was behaving like RAM.

To find out how much memory was paged at given time you can use DMV sys.dm_os_process_memory. Look at difference of column physical_memory_in_use_kb and virtual_address_space_committed_kb. The difference of these two columns would give you SQL Server using paged memory.

select (virtual_address_space_committed_kb/1024) as Total_Memory_Used_MB, --Ram+page file
(physical_memory_in_use_kb/1024) as Total_physical_memory_used_MB ,--PhysicalMemory Used
((virtual_address_space_committed_kb-physical_memory_in_use_kb)/1024) as Page_file_Utilization_MB
from sys.dm_os_process_memory.

You can to larger extent avoid this by giving SQL Server service account Locked Pages in Memory Privilege. This will not allow SQL Server memory to be paged BUT still windows can ask SQL Server to trim its memory utilization uptill min server memory after that if there is still a severe memory pressure OS process will be paged and this can even lead to unexpected OS shutdown so be careful. Better solution is to add more RAM, in this case specifically.

EDIT: From your comment

  I already limit the entire SQL Server instance to 2GB of memory

Ohh!! that is really too small amount of memory for SQL Server to work and that too when you are doing some severe testing


Is there a way to have SQL work in parallelism with two computers?

If you want a solution using plain PostgreSQL, you will have to use sharding, that is, you will have to split the database into parts that you keep on different machines.

You can use PL/Proxy to have a single query run on all machines in parallel.


How to change the output encoding of Oracle command line tools to UTF8?

Thank you very much - I found the solution.

First, the output encoding of the Oracle command line tools can be controlled by

either the NLS_LANG environment variable (in my situation, a value of GERMAN_GERMANY.AL32UTF8 was needed)
or through the registry by the key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG

Thank you very much for all - I leave this question to make the googlers of the future happier. :-)


Setting up a many-to-many relationship based off of two other many-to-many relationships

It looks like you're almost where you want to be. I typically design those linking tables a bit differently, as well, because I don't like compound PKs and that may not be a "best practice" as much as a personal preference:

Primary Tables:

Create Table Projects (
    ProjectID int not null identity(1,1) primary key,
    ProjectName nvarchar(2000) not null,
    StartDate datetime2,
    FinishDate datetime2
    -- Other fields here
    )

Create Table People (
    PersonID  int not null identity(1,1) primary key,
    PersonName nvarchar(2000) not null,
    CreateDate Datetime2
    --other fields here
    )

Create Table Products (
    ProductID  int not null identity(1,1) primary key,
    ProductName nvarchar(2000) not null,
    CreateDate datetime2
    --other fields here
    )

Linking Tables:

Create Table ProjectManagers (
    ProjectManagerID   int not null identity(1,1) primary key,
    ProjectID int not null,
    PersonID int not null, --this is the manager
    -- other fields here
    Constraint FK_ProjectManager_Project Foreign Key (ProjectID) References Projects (ProjectID),
    Constraint FK_ProjectManager_Owner Foreign Key (PersonID) References People (PersonID)
)

Create Table ProjectProducts (
    ProjectProductID int not null identity(1,1) primary key,
    ProjectID int not null,
    ProductID int not null,
    -- other fields here
    Constraint FK_PRojectProduct_Project Foreign Key (ProjectID) References Projects (ProjectID),
    Constraint FK_PRojectProduct_Product Foreign Key (ProductID) References Products (ProductID)
)

Create Table ProductOwners (
    ProductOwnerID int not null identity(1,1) primary key,
    ProductID int not null,
    PersonID int not null,  --the owner
    -- other fields...
    Constraint FK_ProductOwner_Owner Foreign Key (PersonID) References People (PersonID)
    )

And now let's put some data in...

Insert into projects (ProjectName, StartDate) values ('Project 1', SYSDATETIME());
Insert into projects (ProjectName, StartDate) values ('Project 2', SYSDATETIME());
Insert into projects (ProjectName, StartDate) values ('Project 3', SYSDATETIME());

insert into People (PersonName, CreateDate) values ('Alan Alda', SYSDATETIME());
insert into People (PersonName, CreateDate) values ('Ben Bova', SYSDATETIME());
insert into People (PersonName, CreateDate) values ('Claire Collins', SYSDATETIME());

Insert into Products (ProductName, CreateDate) values ('1 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('2 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('3 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('4 product', SYSDATETIME());

And now let's link these together.

Insert Into ProjectManagers (ProjectID, PersonID) values (1, 1);
Insert Into ProjectManagers (ProjectID, PersonID) values (1, 2);
Insert Into ProjectManagers (ProjectID, PersonID) values (2, 1);
Insert Into ProjectManagers (ProjectID, PersonID) values (3, 3);

Insert into projectproducts (projectid, productid) values (1, 1)
Insert into projectproducts (projectid, productid) values (2, 1)
Insert into projectproducts (projectid, productid) values (3, 1)
Insert into projectproducts (projectid, productid) values (2, 2)
Insert into projectproducts (projectid, productid) values (3, 3)
Insert into projectproducts (projectid, productid) values (1, 4)
Insert into projectproducts (projectid, productid) values (2, 4)
Insert into projectproducts (projectid, productid) values (3, 4)

Insert Into ProductOwners (ProductID, PersonID) values (1, 1)
Insert Into ProductOwners (ProductID, PersonID) values (1, 3)
Insert Into ProductOwners (ProductID, PersonID) values (3, 1)
Insert Into ProductOwners (ProductID, PersonID) values (1, 2)

So now you can link a product up to a project and from there down to the owners or vice versa.

select proj.ProjectName, peMgr.PersonName [Proj Manager], prod.ProductName, peOwn.PersonName [Product Owner]
from projects proj
left join ProjectManagers pm on pm.ProjectID = proj.ProjectID
left join ProjectProducts pp on pp.ProjectID = proj.ProjectID
left join people peMgr on peMgr.personID = pm.personID
left join Products prod on  prod.ProductID = pp.ProductID
left join ProductOwners po on po.ProductID = prod.ProductID
left join people peOwn on peOwn.PersonID = po.PersonID
order by 1, 2, 3

This gives us :

ProjectName |  Proj Manager |  Product      |   Product Owner
Project 1   |   Alan Alda   |   1 product   |   Alan Alda
Project 1   |   Alan Alda   |   1 product   |   Alan Alda
Project 1   |   Alan Alda   |   1 product   |   Ben Bova
Project 1   |   Alan Alda   |   1 product   |   Ben Bova
Project 1   |   Alan Alda   |   1 product   |   Claire Collins
Project 1   |   Alan Alda   |   1 product   |   Claire Collins
Project 1   |   Ben Bova   |   1 product   |   Alan Alda
Project 1   |   Ben Bova   |   1 product   |   Alan Alda
Project 1   |   Ben Bova   |   1 product   |   Ben Bova
Project 1   |   Ben Bova   |   1 product   |   Ben Bova
Project 1   |   Ben Bova   |   1 product   |   Claire Collins
Project 1   |   Ben Bova   |   1 product   |   Claire Collins
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 3   |   Claire Collins   |   3 product   |   Alan Alda
Project 3   |   Claire Collins   |   3 product   |   Alan Alda
Project 3   |   Claire Collins   |   3 product   |   Alan Alda

Similar queries can reverse that to show the owners and their products or other combinations. Some XML work can be used to turn this into comma-separated lists instead of more rows...


Logical reads time 8KB more than memory on instance

If SQL Server reads 10 pages they don't all have to be in memory at the same time. It can also count reads of the same page multiple times.
To simplify, let's say SQL Server has limited the buffer pool to 8KB (conveniently, the size of a page). Now, you run a query that requires SQL Server to read 3 pages. It can't fit all the pages into memory at once, so:
Action                    Number of reads so far
------------------------  ----------------------
Loads page 1 into memory                       1
Discards/disfavors page 1                      1
Loads page 2 into memory                       2
Discards/disfavors page 2                      2
Loads page 3 into memory                       3

Now, let's say you use a CTE or self-join or something else, if SQL Server isn't able to re-use the same page while it is in memory, it will have to do that all over again. Even if it doesn't, if it has to access the same page twice while it is in memory, that's two logical reads. If an operator in a query plan (like a key lookup) has 10 executions, those are additional logical reads. If a foreign key has to be validated, there are some more. If a trigger runs as part of the transaction, worktables are involved, a sort spills to disk, etc. just keep piling on the reads. Just because all those reads can't fit into memory doesn't mean they don't still have to happen.
There is no direct correlation between read activity and max server memory / buffer pool size. You just need to remember that page reads aren't distinct and they don't all have to happen at the same instant in time, especially if you have a lot of churn in your buffer pool, in which case they have be spread out.


INSERT INTO table FROM SELECT * with nextval() specification

As noted in the comments there is no special syntax for such task.
You could to use combination of functions to_json(b), json(b)_set and json(b)_populate_record:

--drop table if exists t;
create table t(i serial primary key, x int, y text, z timestamp);
insert into t values(default, 1, 'a', now()),(default, 2, 'b', now());

insert into t
select n.*
from t, jsonb_populate_record(
  null::t,
  jsonb_set(
    to_jsonb(t.*),
    array['i'],
    to_jsonb(nextval('t_i_seq')))) as n;

select * from t;

But I think it is not much simpler then just enumerate all columns. You always can use this relatively simple query to get the columns list of table:

select string_agg(attname,',' order by attnum)
from pg_attribute
where attrelid = 'public.t'::regclass and attnum > 0;


What's a good (free) client for Informix on Windows?

I would suggest SQuirreL (free) but Informix doesn't appear to be supported

A Google search shows some older pages though so YMMV


Slow SQL Server query using a DB2 linked server in virtual environment

Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.

We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):

IBM - Slow performance in Controller due to inability to use available CPU cores in SQL server

Removing the automatic affinity option and setting the affinity mask option to use all the CPUs available resolved the issue.

Affinity Mask Option

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

We have updated our image to include this option. Thanks everyone for the help in this issue.

Please note, this option is deprecated in SQL Server 2012 and is not recommended for use. Additionally, it is related to processor thread handling in Windows 2000 and Windows 2003 operating systems.

Affinity Mask Option - SQL Server 2012


pg_dump - table dependencies when using --table

pg_dump -t will dump the definition of foreign key constraints.  But will not dump the definition (or data) of the table which that constraint points to.

Similarly, if you have a check constraint which uses a function, the definition of the function will not get dumped.

I don't know the thought process behind dumping sequences.  That does seem like a documentation bug.


At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on a fulltext index?

You can try to use the index statistics report by first enabling reporting on the index  (BEGIN ctx_output.enable_query_stats(ix_my_indexname); END;).  Then you can use the procedure ctx_report.index_stats.  While sifting through the CLOB of info in the report, the most useful information to me is the FRAGMENTATION STATISTICS...estimated row fragmentation: 23%

see Oracle documentation here for more thorough examples of the stats report.


How to design a query that gives count of different parameters based on different condition from a single table

select sum(case when issue_date = return_date then 1 else 0 end) SAME_DAY,
       sum(case when return_date is null then 1 else 0 end) NOT_RET,
       sum(case when issue_date <> return_date then 1 else 0 end) SEP_DAY
  from books;


Can someone help me with this PL/SQL block?

Your code has a few syntax issues, it should be like this:

DECLARE
  Cursor C is
  select * from Employee ORDER BY salary DESC;
  Edet C%ROWTYPE;

BEGIN
 Open C;
 Loop
    fetch C into Edet;
    EXIT WHEN C%ROWCOUNT = 5;
    DBMS_OUTPUT.PUT_LINE(c.salary);
 end loop;
 Close c;
END;

However, there are some design issues.

You will get an error if table Employee has less than 5 records because C%ROWCOUNT = 5 does never become true.

If you have Oracle 12 running then you can simple do

select *
from Employee
ORDER BY salary DESC
fetch first 5 rows only;

For earlier releases you can do

select *
from (select e.*, ROWNUM as RN from Employee e ORDER BY salary DESC)
where rn <= 5;


SQL Server performance issue (CXPACKET wait type)

General consensus seems to tend torwards setting Cost Threshold for Parallelism to 50 and go from there.
Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

This even includes official reccomendations from VMWare regarding SQL Server Setup with vSphere. Section 4.5 of the Guide "Architecting Microsoft SQL Server on VMware vSphere®" states:

  The default value is 5 which is usually considered too low. [...] The recommendation instead is to increase the CTFP value from 5 seconds to approximately 50 seconds to make sure only large queries run in parallel. Set the MAXDOP according to Microsoft’s recommendation for the number of cores in the VM’s NUMA node (no more than 8).

The usage of of the unit "seconds" should be taken with a grain of salt because on modern systems and versions of SQL Server it will not reflect the actual duration in seconds.

Regarding MAXDOP there is a comprehensive guide found here: Configure the max degree of parallelism

So in your case I would start with the following settings and test them out:

Cost Threshold for Parallelism: 50

Maximum Degree of Parallelism: 8


How to structure table in MySQL for group chat application?

With relational databases, or mostly-relational databases as is the case with MySQL, you should think mostly in terms of entities and relationships.

You might want one table for each of the following entities:

messages: the actual messages and a reference to the user (author)
users: details such as user name, email address, ...
groups: maybe just the name of the group
group memberships: one row per user per group. This allows for a many-to-many relationship, so that many users can be in many groups at the same time.

So then you could have tables maybe like such:

CREATE TABLE users (
  user_id bigint unsigned auto_increment PRIMARY KEY,
  username varchar(60) NOT NULL UNIQUE
  -- other user columns here
);

CREATE TABLE groups (
  group_id bigint unsigned auto_increment PRIMARY KEY,
  groupname varchar(60) NOT NULL UNIQUE
  -- other group columns here
);

CREATE TABLE group_users (
  group_id bigint unsigned,
  user_id bigint unsigned,
  PRIMARY KEY (group_id, user_id),
  CONSTRAINT group_users_fk1 FOREIGN KEY (group_id) REFERENCES groups (group_id),
  CONSTRAINT group_users_fk2 FOREIGN KEY (user_id) REFERENCES users (user_id)
);

CREATE TABLE messages (
  message_id bigint unsigned auto_increment PRIMARY KEY,
  user_id bigint unsigned,
  message TEXT,
  -- other columns here, e.g. timestamp
  CONSTRAINT messages_fk1 FOREIGN KEY (user_id) REFERENCES users (user_id)
);

Edit

The lines starting with CONSTRAINT specify foreign keys which enforce integrity of data between the tables. E.g. they will prevent rows from being deleted in the users table that are referenced in the messages table.

To retrieve a list of users in a particular group, use a query like this:

SELECT u.user_id, u.username
FROM users u
  INNER JOIN group_users gu USING (user_id)
WHERE gu.group_id = 3;

The INNER JOIN means the query will only retrieve the users who have at least one row in the group_users table, and the WHERE ensures that we only retrieve results for group_id 3.


Created database does not appear in database list

You have two commands confused:

initdb is used once to initialize a DB environment directory structure. That does not create a new database.
createdb is used for each DB you want to create.

When you ran your initdb you were just initializing the folder /var/lib/pgsql/test to hold ALL future databases, but you did not actually create ANY database.

  initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.

Reference: initdb (PostgreSQL Documentation)

You did not specify if this is a test machine or planned for prod, but you probably should use the service command to run the initdb and after that sudo to the postgres user to run the createdb. Often in *nix systems the installer setups up PGDATA variable for you so you don't have to specify the directory. There are some installers that even run the initdb for you; you did not specify the OS, so no idea of how your OS / installer handles setup/installation.

So, you may do something like:

sudo service postgresql initdb
sudo su - postgres
createdb test
psql -c "\l test"


How many cores are used for serial plans in SQL Server?

Your query execution plan can, depending on the complexity, contain both serial and parallel components.

In SQL Server speak your complex or simple query/batch is called a request and as such is monitored via the sys.dm_exec_requests management view.

  Returns information about each request that is executing in SQL Server.

The request is then split into different workloads which in SQL Server is represented as a task. A request can be a number of sequential or parallel tasks which all belong to the same request. Task are monitored in the sys.dm_os_tasks management view.

  Returns one row for each task that is active in the instance of SQL Server. A task is the basic unit of execution in SQL Server.

The tasks in turn are handed over to worker threads which are logical representations of Operating System threads. A sqlsrvr.exe process will have a number of threads which can perform computation on the processor (core).

When the thread is waiting for example on data being returned, then it would be better if the thread could be freed to perform another task while the current task is waiting. This is performed by the scheduler which is ...

  ...also known as SOS scheduler, manages worker threads that require processing time to carry out work on behalf of tasks. Each scheduler is mapped to an individual processor (CPU). The time a worker can remain active in a scheduler is called the OS quantum, with a maximum of 4 ms. After its quantum time expires, a worker yields its time to other workers that need to access CPU resources, and changes its state. This cooperation between workers to maximize access to CPU resources is called cooperative scheduling, also known as non-preemptive scheduling. In turn, the change in worker state is propagated to the task associated with that worker, and to the request associated with the task.

The threads are not bound to a specific core/processor unless process affinity has been turned on a the OS level.

(emphasis mine)

  By default, each instance of SQL Server starts each thread, and the operating system distributes threads from instances of SQL Server among the processors (CPUs) on a computer, based on load. If process affinity has been enabled at the operating system level, then the operating system assigns each thread to a specific CPU. In contrast, the SQL Server Database Engine assigns SQL Server worker threads to schedulers that distribute the threads evenly among the CPUs.

  To carry out multitasking, for example when multiple applications access the same set of CPUs, the operating system sometimes moves worker threads among different CPUs.

Reference: Thread and Task Architecture Guide (Microsoft | SQL Docs)

Answering Your Question

  So, my question is...will all the serial plans with a cost less than 55 use only the first core?

No, your serial tasks (not plans) will be executed on any available core depending on the overall workload of the system.


Once created an Availability Group (AG) cannot be renamed, although the databases within the AG can be renamed. You'd have to re-create it.
See here for the options for ALTERing an AG: https://msdn.microsoft.com/en-us/library/ff878601.aspx
When creating the AG using the GUI (wizard), once all the steps are completed you will see a 'Script' button which gives the option to generate a script of those steps to a new window.
You can then either save the script for later and quit the GUI, or just "finish" the wizard to actually create the AG. (The script will stay in the window)
It's a good idea to keep the script in case it needs to be re-created later (e.g. if you want to rename the AG...)

It doesn't seem to be currently possible to "Script As..." an AG in the way you would for a table or stored procedure for example.

You could also write the CREATE AVAILABILITY GROUP statement from scratch using the MSDN boilerplate code as a starting point (https://msdn.microsoft.com/en-us/library/ff878399.aspx) but that is likely to be very cumbersome and error-prone compared to using the GUI which will check you've got everything right before it lets you proceed!


How to properly index hstore tags column to faster search for keys

You could try indexing the keys like in the following examples and see how that works:

CREATE INDEX nodes_tags_key_historic_idx on nodes ( (tags->'historic') );
CREATE INDEX nodes_tags_key_tourist_idx on nodes ( (tags->'tourist') );
ANALYZE nodes;

What does your query plan look like when run with EXPLAIN ANALYZE?

[edit]: Alternative indexes:

CREATE INDEX nodes_tags_key_tourist_idx2 on nodes ( (tags ? 'tourist') );
CREATE INDEX nodes_tags_key_historic_idx2 on nodes ( (tags ? 'historic') );

The latter shouldn't require any changes to your query.


Creating and dropping partition in PostgreSQL "on the fly"?

Usually it is the best thing to create partitions for the data you expect ahead of time and handle the error when unexpected data arrive.
There is also the option of a default partition that receives data that belongs into no other partition, but that is usually only a good idea if you have a fixed number of partitions, because conflicting data in the default partition prevents creation of a new partition.


Automatic slave reconfiguration after updating master

The solution I found for myself is:
stop both master and slave
remove logs
replace ib* files and /var/lib/mysql/mybase/* with files from prod backup
start mysql on master
start mysql on slave with --skip-slave-start, then SLAVE RESET; SLAVE START;

Not sure if this is correct, but it works for now.


What is the difference between optimize table and analyze table in mysql

To expand on @MitchWheat's answer (+1 for directly answering first):

ANALYZE TABLE examines key distribution and stores them in INFORMATION_SCHEMA.STATISTICS.

OPTIMIZE TABLE performs ANALYZE TABLE after doing some table compression. The equivalent of OPTIMIZE TABLE mydb.mytable; if the table was MyISAM is this:

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

MyISAM

For the MyISAM table mydb.mytable in datadir /var/lib/mysql, you have the following files:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.MYD (data)
/var/lib/mysql/mydb/mytable.MYI (indexes)

OPTIMIZE TABLE mydb.mytable would shrink the .MYD and .MYI files for the table.

This is not the same for InnoDB. Here is how it is different:

InnoDB (innodb_file_per_table enabled)

Each table's data and indexes are stored in an external tablespace file. For datadir is /var/lib/mysql  and the table mydb.mytable, it would be stored as follows:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

When OPTIMIZE TABLE mydb.mytable is executed, mytable.ibd gets shrunk.

InnoDB (innodb_file_per_table disabled)

Only /var/lib/mysql/mydb/mytable.frm would exist. All the data and index pages for the table mydb.mytable are stored in the system tablespace file /var/lib/mysql/ibdata1.

When OPTIMIZE TABLE mydb.mytable is executed, the data and index pages are written contiguously in ibdata1. Unfortunately, this make ibdata1 grow in leaps and bounds.

See the Pictorial Representation from Percona CTO Vadim Tkachenko

UPDATE 2013-02-26 22:33 EST

You comment was

  I think, optimize table for innodb is not supported. I got a message, index will be recreated. How does it work?

I tried this out

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table dat (a int, primary key (a));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into dat values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> analyze table dat;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.dat | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.06 sec)

mysql> optimize table dat;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.dat | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.dat | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.14 sec)

mysql>

You are correct. You cannot run OPTIMIZE TABLE as a single operation. What InnoDB does instead is the following:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

You could also just run these steps yourself.

However, in all honesty, you should not have to run ANALYZE TABLE against an InnoDB table because each time a query is executed, the InnoDB Storage Engine performs an estimation on table cardinality based on passing through pages in the indexes. If there are a high number of INSERTs, UPDATEs, and DELETEs, then you will need to ANALYZE TABLE. When there is a high number of DELETEs, then ALTER TABLE mydb.mytable ENGINE=InnoDB; is needed to shrink the table.

I actually wrote posts about the futility of ANALYZE TABLE on InnoDB in certain instances:

Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down
Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?


Is it possible to create a function for transactions that use more than one database?

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. Is this what you're looking for?

delimiter //
CREATE PROCEDURE yourdb.yourprocname (_first_name varchar(100), _last_name varchar(100), _create_time datetime, _update_time datetime,  _hash_id varchar(32),
  _id_status int, _id int, _criminal_status int, _day int, _month int, _year int, _state_id int, _state_id_status int)
begin
  START TRANSACTION;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
    VALUES (_first_name, _last_name, _create_time, _update_time, _hash_id);

  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, _create_time, _update_time, LAST_INSERT_ID());

  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_day, _month, _year, _state_id, _state_id_status, _create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Example call:

call yourdb.yourprocname ('Lola', 'High', CURRENT_TIMESTAMP, ... etc)


Multi-instance 2-node SQL Cluster - do instances need their own disks?

When I install a second instance, do I HAVE to use a separate set of disks, or can it be installed on the same disks as the default instance?

Yes you need to have a separate set of disks for the new instance.

  I'm not even sure if it is possible, let alone whether it is preferable) Am planning to host BOTH instances on a single node

That is not a bad idea, lot of people do and we call it as multi-instance cluster. Both the instances would be on same node in your case. They can also be on different nodes but you need to look at licensing part then.

  My concern is that one of the instances 'could' fail over to the second node. But the second node is not licenced, so am not allowed to run an instance on each node

In your scenario you have 2 instances on same node, this is fine since 2nd node is purely passive. BUT if a failover happens for "an" instance on 2nd node which is not licensed then you would have an instance running both on 2nd node and 1st node, this is a license violation if you run this scenario for long time, unless you immediately move it back such that both instances are on same node. The term "immediately" here means as soon as possible, MS is not going to charge you if let us say for hour you run 2 different instances on 2 different nodes.

PS: I am not a licensing expert but you should get this verified from local licensing expert of your region.


It looks like there is an odbc driver for project

You could try attaching access directly via a linked table.


Oracle set schema in a PreparedStatement

Always use the DBMS_ASSERT package when dealing with variable Database Objects that are used within Dynamic SQL statements.

Example

create or replace
procedure owner_of_proc.setCurrentSchema( schema_name in varchar2 )
as
  asserted_schema_name  varchar2(30 byte); -- set to 128 BYTE for 12c+ extended names
  sql_text              varchar2(200);
begin

  asserted_schema_name := DBMS_ASSERT.SCHEMA_NAME( schema_name );

  sql_text := 'ALTER SESSION SET CURRENT_SCHEMA = "' || asserted_schema_name || '"';

  -- always have the ability to log the generated SQL text for Debugging purposes
  -- logger.debug( 'SQL =' || chr(10) || sql_text );

  execute immediate sql_text;
end;
/

Your Java code would be:

public void executeSetCurrentSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();

     // I think this is how you use BIND variables in Java
     stmt.execute("begin owner_of_proc.setCurrentSchema( ? ) end;", schema);

  } catch(Exception e){
    /* Please fill this out
       It is the Java equivalent of WHEN OTHERS THEN NULL;

       You need to catch (and handle) "ORA-44001: Invalid schema name"
     */
  }
}


Is having relationships between separate databases bad practice?

The only issue is that there isn't really a convenient way to enforce these relationships. You can use a boatload of triggers, or store the master table in each instance database and simply expose all of them as metadata to the master, using a view perhaps.

The biggest downside, I think, has to do with keeping the data accurate and synchronized, in at least three scenarios off the top of my head (all of which can still be an issue even if you use triggers):

improperly coordinated cross-database transactions - for example one transaction adds a new row to master, hands off the identity to another transaction, and then the first transaction rolls back.
recovery from a single database failure - a database crashes, you have to restore to a point in time, that may have been before other successful transactions in other databases had finished. This could mean if an instance database has to revert to an earlier time, it is missing rows that master expects; if master has to revert, it's possible that all of the instance databases will have values that don't exist in master.
backup consistency in general - if you need disaster recovery, you're going to have a hard time keeping all your full and log backups transactionally consistent to a point in time. File system snapshots don't help with this, and even Availability Groups don't guarantee cross-database transactional consistency for the databases in a single Availability Group. If you have a disaster and need to restore your databases to a new system, there's no way to be sure they will be transactionally consistent.

All that said, I have always been and always will be a fan of separating tenants out into their own databases, and I acknowledge there is risk associated with the central database - but it is necessary.

(As an aside, you should use names for your databases other than master / instance. master in SQL Server definitely has an explicit meaning, and even reading my own words above they could be ambiguous. Same for instance. In a previous life I ran a multi-tenant system and we called the central database Control and the tenant databases, well, Tenants.)


SSDT - Deprecated table trigger preventing publish

There is a setting on the "drop" tab of the advanced publish settings called "Drop DML Triggers not in source."  This is checked by default, and thus triggers should be dropped by the publish process.  This is true even if "drop object in target but not in source" is unchecked.

It sounds like this has been unchecked in your publish process.  You can confirm that by looking for this element in your publish XML file (if that's how you're publishing):

<DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource>

Removing that element, or setting it to true, should resolve your issue without setting the undesirable "drop object in target but not in source" or having to use a pre-model script.

Regarding the inciting issue:

  There is a table trigger that was removed in the past that is blocking SSDT publish preview (due to a dependency)...

If the setting I mentioned above doesn't resolve the problem, it would be helpful if you could provide a repro steps for this scenario.


Suggestions for your my.cnf-ini [mysqld] section from the limited available information provided,

max_connect_errors=10  # from 1000000 - allow hackers/crackers nn pw guesses
sort_buffer_size=4M  # REMOVE or lead with # for default
read_buffer_size=4M  # REMOVE or lead with # for default
read_rnd_buffer_size=4M  # REMOVE or lead with # for default
thread_cache_size=100  # from 128 see V8 REFMAN for CAP to prevent OOM

Take 2 minutes with MySQLCalculator.com to see what your values have done to the RAM footprint requirements that are extreme and will negatively impact performance.

Eliminate the duplicate variables in your my.cnf-ini and be consistent with underscores rather than mixing in hyphens.

Provide the additional information requested a few hours ago for additional helpful suggestions to improve performance.

Good luck with this new venture on this nice equipment configuration.  Any SSD's?


SSIS package Job Fails with Unexpected Termination

This problem occurs with SQL Server 2012 SP1.

I went through these KB articles

https://support.microsoft.com/en-us/kb/2837964
and
https://support.microsoft.com/en-us/kb/2993859

and installed the suggested hot-fixes that resolved the error.


Use current_date:

where SALEITEMS.FP_DATE = current_date

This assumes that FP_DATE has the data type DATE .

If FP_DATE is a timestamp column, then the above will only return rows where the time part of the column is 00:00:00. To ignore the time part you could cast the column to a date value:

where cast(SALEITEMS.FP_DATE as date) = current_date

That however will not make use of an index on the column so it might not be fast enough. In that case the usual approach is to use a range query:

where SALEITEMS.FP_DATE >= current_date
  and SALEITEMS.FP_DATE < current_date + 1

current_date + 1 is "tomorrow" (at midnight)


I would say consider logon triggers to accomplish this. This way you aren't using a server side trace. You can also audit all sucessful logins in the SQL Server error log (right click on instance, properties, security and then choose both successful and failed logons.. I don't believe this shows you the DB context info, though)

Also - I would seriously consider upgrading to a later version of SQL if possible. SQL 2005 is two (three if you count 2008 R2) versions back. I know you probably know that but I'd feel bad if I didn't call it out :-)

I would also highly discourage the use of SA for any logons. SA is a highly privileged account. It is -the- highly privileged account in SQL Server. Everyone knows there is an account named SA and it can be prone to hack attempts. I tend to push for windows authentication only and ensure a group is added that the proper DBAs team can properly and securely added to. I often disable the SA account in mixed mode and will create another account with SA rights but a non descript name. If, for some reason, the SA account needs to stay around, I try and give that a horrible password and store it someplace really safe and not use it.

By not having an SA account used by so many people that you need to audit its usage, you can give more granular permissions to do the required activities in SQL and no more. Least privilege and tight access lists will take you much further than watching SA account activity.

At any rate, Logon triggers may be the best bet. Trace would work but there is a cost associated with that (there is with logon triggers also but my guess is the cost of trace will be more expensive for you)


How to obtain SIDs of Sql Server installed NT Service Accounts

From the WMI documentation, I couldn't find any way to directly obtain a service SID, probably because as far as I've seen, service SIDs aren't physical directory objects, and thus couldn't be queried except where they appear as part of ACLs. This would also explain why the values aren't persisted in the registry.

Having said that, the service SIDs definitely do not appear in Win32_SystemAccount. Using PowerShell on a sample machine, you can verify this by running Get-WmiObject -Query "SELECT * FROM Win32_SystemAccount".

Luckily, however, there are alternatives:

Per this TechNet blog post, the non-well-known portion of a service SID is generated by taking a SHA1 hash of the uppercase service name, and then splitting apart the results into five unsigned 4-byte numbers. The following code performs this computation and returns the SID as displayed in Windows:

DECLARE @serviceName nvarchar(128) = 'MSSQL$SQL2008R2DEV';

SELECT
    'S-1-5-80' +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 17, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 13, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 9, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 5, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 1, 4), 2)))
    FROM
    (
        SELECT
            CONVERT(varbinary(20), REVERSE(HASHBYTES('SHA1', UPPER(@serviceName)))) AS BinSid
    ) a;

The binary SID can also be obtained by using the SUSER_SID function (with some extra processing to handle the fixed portion) in lieu of HASHBYTES. (Note that the raw binary SID must have its endianness reversed before conversion and output. Also, the @serviceName variable being nvarchar is important.)

If you have .NET available, doing the hash calculation is trivial. Here's the C# code to do it:

using System.Security.Cryptography;

...

string serviceName = "MSSQL$SQL2008R2DEV";

HashAlgorithm ha = HashAlgorithm.Create("SHA1");
byte[] hash = ha.ComputeHash(serviceName.ToUpper().SelectMany(c => BitConverter.GetBytes(c)).ToArray());

string sid = "S-1-5-80";

for (int i = 0; i < 5; i++)
    sid += "-" + BitConverter.ToUInt32(hash, i * 4);

I suspect the SIDs could be found using classes in the .NET System.DirectoryServices namespace, either by inspecting group membership (fragile), or by some other method. Unfortunately, the methods I tested to directly translate a principal name into a SID failed, so I'm not sure how SQL Server does it (PInvoke?). In any event, computing the hash isn't a big deal if .NET is available (in fairness, the code will be longer without the use of LINQ).
Capture the relevant part of the output of the sc utility as used below, or run this manually if you don't care about being able to automate the solution:

sc showsid MSSQL$SQL2008R2DEV


How to allow for ampersand in XMLtable()?

The error message clearly told you to use &amp;.

with data as (
  select 'Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;ina
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina

Update (will not work for other special characters such as &gt;):

with data as (
  select regexp_replace('Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina', '(&)([^#])', '\1amp;\2') str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;ina
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina


I need help to migrate denormalized table content to normalized form- MS Access

I don't really have any links, but I can give a general how-to of this:

After importing into Access:
- A series of update scripts (or a single procedure, really) that copy the data into the appropriate tables
- A purge statement clearing out the temp table for the next upload.

It sounds really simple, and it (actually) shouldn't be too difficult, but without knowing your table structure, it's hard to give true specifics.

Are the skills and projects using defined language? if so, this greatly simplifies the job.