A few places I would expect to start.  Please look at query plans but my guess is that this is a pretty simple case here of antijoins performing poorly (yes, I have been bitten by something similar even on PostgreSQL so I don't think this is unique to MySQL though InnoDB has some design decisions which make me think this could be particularly bad there).

In general, one of the basic problems  with NOT IN is that most db's I have seen end up doing this as a nested loop check using one means or another.

The first thing you should do is rewrite this as an outer join (for example a left join):

SELECT q.id
FROM questions q
LEFT JOIN user_quiz_questions u
          ON (u.question_id = q.id
              && user_id = ? && chapter_id = ?)
WHERE
      q.chapter_id = ?
      && (q.module = 1 || q.module = ?)
      && q.question_type != 4
      && u.question_id IS NULL
ORDER BY RAND()
LIMIT ?

The planner has a lot more freedom for how to address this and left joins are far more mature in MySQL than are subselects making me suspect that this will perform much better.

Edit:  As has been pointed out to me, this may be a misreading of your question.  Are you saying you have 2000 questions or 300000 seen questions plus 2000 questions that haven't been seen yet?  These are big differences and you are likely to need an index on join fields anyway if you don't have one.


Getting 0KB of size file while backup mysql database using mysql dump

I don't know how to write this in php, but you should write mysqldump command like below, there is no need to write three command one is enough

mysqldump --user=root --password=root --host=localhost --single-transaction --routines db_name > db_name.sql

UPDATE

I did it here is the php script

<?php
define("BACKUP_PATH", "/home/abdul/");

$server_name   = "localhost";
$username      = "root";
$password      = "root";
$database_name = "world_copy";
$date_string   = date("Ymd");

$cmd = "mysqldump --routines -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";

exec($cmd);
?>

It generate backup file named as 20140225_world_copy.sql in /home/abdul/

For reference have a look at This link.


Unless I'm missing some nuance, you will simply set the Row Visibility based on the value.

Right click on the detail line and select Row Visiblity...

In the window that pops up, select Show or hide based on an expression

Within that expression, you'll use logic like

=Fields!Total_Allocation.Value = 0 And Fields!Total_Actual_Cost.Value = 0


Number of data files in tempdb in Availability Group

There is not a problem with having different numbers of tempdb files on each machine,  you just need to be aware that should you failover to one of the machines with fewer files that you will run into the contention problem that you worked to alleviate on the new server.


Install the database engine on the server, then Management Studio on each developer's machine.

Assuming they have permissions, the developers can then use Management Studio to connect to the database on the server.

Because Management Studio is free, you only need to ensure the server-based SQL Server instance is licensed.


Is there a way to retrieve the logical filename from a backup file?

Taken from this SO question. This seems to work for all versions up to and including 2019.

Just to note, the definition has changed twice since SQL 2000 - I don't judge 2 changes in 19 years as a frequent change personally. In that sense I wouldn't worry about using RESTORE FILELISTONLY as long as you use a generic script like the one below.

CREATE TABLE #FileListHeaders (
     LogicalName    nvarchar(128)
    ,PhysicalName   nvarchar(260)
    ,[Type] char(1)
    ,FileGroupName  nvarchar(128) NULL
    ,Size   numeric(20,0)
    ,MaxSize    numeric(20,0)
    ,FileID bigint
    ,CreateLSN  numeric(25,0)
    ,DropLSN    numeric(25,0) NULL
    ,UniqueID   uniqueidentifier
    ,ReadOnlyLSN    numeric(25,0) NULL
    ,ReadWriteLSN   numeric(25,0) NULL
    ,BackupSizeInBytes  bigint
    ,SourceBlockSize    int
    ,FileGroupID    int
    ,LogGroupGUID   uniqueidentifier NULL
    ,DifferentialBaseLSN    numeric(25,0) NULL
    ,DifferentialBaseGUID   uniqueidentifier NULL
    ,IsReadOnly bit
    ,IsPresent  bit
)
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(4)) as float) > 9 -- Greater than SQL 2005
BEGIN
    ALTER TABLE #FileListHeaders ADD TDEThumbprint  varbinary(32) NULL
END
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(2)) as float) > 12 -- Greater than 2014
BEGIN
    ALTER TABLE #FileListHeaders ADD SnapshotURL    nvarchar(360) NULL
END
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = N''BackupFileName.bak''')

SELECT * FROM #FileListHeaders

DROP TABLE #FileListHeaders


I wouldn't say that index 2 is a subset of index 1.

An index that had the columns UserId, InstanceId would be.

This could still be useful to queries that do not require the created column as they would be accessing a more narrow index which would require less IO and memory.

Your best bet is to check if your indexes are actually being used by queries. The scripts below will help with this.

SELECT  d.name as [Database]
        , OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]
        , I.[NAME] AS [INDEX NAME]
        , USER_SEEKS
        , USER_SCANS
        , USER_LOOKUPS
        , USER_UPDATES

FROM    SYS.DM_DB_INDEX_USAGE_STATS AS S
JOIN    SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
                            AND I.INDEX_ID = S.INDEX_ID
JOIN    sys.Databases d on s.database_id = d.database_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

ORDER BY USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES DESC

SELECT  d.name
        , t.name
        , OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME]
        , I.[NAME] AS [INDEX NAME]
        , A.LEAF_INSERT_COUNT
        , A.LEAF_UPDATE_COUNT
        , A.LEAF_DELETE_COUNT

FROM    SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
JOIN    SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID]
JOIN    sys.tables t ON i.object_id = t.object_id
JOIN    sys.databases d ON a.database_id = d.database_id
                        AND I.INDEX_ID = A.INDEX_ID

WHERE   OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

ORDER BY    A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT DESC


Overall, splitting tables adds overhead for a perceived benefit that isn't there. Tables of a few billion rows do need to be carefully designed as far as index and normalization, however splitting adds computation /software overheads and may generate far worse queries.

To directly answer the question:

Too many tables on disk will have no impact on MySQL (unless you have a filesystem that can't rapidly open a filename from a very full directory (mitigated in 8.0 (frm data in tablespace) and innodb_file_per_table=0).

As far as active tables, those being used frequently in queries there are effects from:

table_open_cache limits the number of tables cached and so does innodb_open_files.

Exceeding these cache limits will cause tables to close and open and need to be re-examined adding overhead to queries.


Get starting and ending balance, with total credit and debit transactions

I solved the problem using CROSS APPLY. The performance is so much better
the code is below:

select sp.StudentCode,sp.StudentNameEN,sp.Flag,DST.DTAmount-CST.CTAmount StartBalance,TDTrans.DTran  DreditTransactions,TCTrans.CTran Credittransactions,DET.DTAmount-CET.CTAmount EndBalance,@StartDate StartDate,@EndDate EndDate
from Student_Profile sp  cross apply
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)    --Charge & Refund
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2
    and ct.StudentID=sp.Serial
)as DST cross apply
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)     --orders
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
    and ct.StudentID=sp.Serial
)as CST cross apply
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID !=2
    and ct.StudentID=sp.Serial
)as DET cross apply
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
    and ct.StudentID=sp.Serial
)as CET  cross apply
(
select DTran =  ISNULL(SUM(TransactionAmount) ,0)    --charge & Refund
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2
)as TDTrans cross apply
(
select CTran =  ISNULL(SUM(TransactionAmount) ,0)     --order
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
)as TCTrans
inner join  Student_Cards SC on sp.Serial=sc.StudentID
where sp.IsDeleted=0
and sc.IsDeleted=0
and sc.IsActive=1
and (sp.Flag=1)


Append columns with values from has many reletionship

You can join to a single table multiple times. Just use different aliases.

And use LEFT [OUTER] JOIN to accommodate for the possibility of missing rows.

Assuming there can only be a single instance of chair, table and door attached to each order. You did not specify that, but your question would hardly make sense otherwise.

I introduce item_type in the table items to make this work. Consider the schema in the fiddle.

SELECT o.id AS order_id, o.created_at, o.paid_at
      ,c.id       AS chair_id
      ,c.title    AS chair_title
      ,c.quantity AS chair_quantity
      ,c.amount   AS chair_amount

      ,t.id       AS table_id
      ,t.title    AS itable_title
      ,t.quantity AS table_quantity
      ,t.amount   AS table_amount

      ,d.id       AS door_id
      ,d.title    AS door_title
      ,d.quantity AS door_quantity
      ,d.amount   AS door_amount
FROM   orders o
LEFT   JOIN items AS c ON c.id = o.id AND c.item_type = 'chair'
LEFT   JOIN items AS t ON t.id = o.id AND t.item_type = 'table'
LEFT   JOIN items AS d ON d.id = o.id AND d.item_type = 'door';

Use distinct column aliases to make this work.

-> SQLfiddle demo


SQL Server 2005 and Standalone VB application

So there a couple thoughts here from my end. And I think they probably follow an order:

The first principle is - you will never secure your MDF file from being looked into by someone with SQL savvy. There are ways around most of what you can do. If you are deploying this application to a customer, and that customer has enough SQL knowledge and you've given them a data file - they can do what they like with it ultimately.. This principle rules all of the others.
Support contracts and license agreements protect you legally here - that is outside the scope of this format. But you have to trust the folks buying your app to honor their support agreements and EULAs. Every vendor out there has to do that same thing.
You can, however, do things like encrypt data in tables and encrypt/decrypt it in and out from your application. This can help protect data, but it isn't foolproof. Someone can try and attach a debugger, dig into the traffic and try and get a sense for what is going on.
You can encrypt your stored procedures to protect the stored procedure code - again this isn't full proof (remember Principle #1 rules them all here)

So there are things you can do, but the biggest question that I would ask if I were you is:

Why?

What I mean is - what is your goal? Customers see databases all the time. In fact as a DBA and Consultant with 15 years of experience, I can honestly say that 90% or more of the databases I work with are wide open to the folks who purchased the application. If they want to, they can certainly play with the data in their database directly. Most of them don't because they like support contracts to remain valid. Some of them do because their vendors are pains to work with and don't give common sense support and updates.

So you should check your motives. If you've made software that someones wants to buy, and they've paid for it and deploy it locally following your instructions and have entered into an agreement with you - why would they want to build their own app? And if they do? What is the harm? Charge enough money and make your agreement strong enough that if they do it is a non-issue.

Also - Is this a new application? SQL Server 2005 is 3 versions back from the currently released version (SQL Server 2012) and a new release (SQL Server 2014) is right around the corner.

You also said "the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers"

With the right installers and install procedures you should have control over how the application is installed. There is actually a lot in your question sort of behind the question that is hard to answer in this forum without a lot of back and forth. Some of the questions that come to mind are around areas like "How does your app install? Is there an installer app?", "What are the instructions for the installation and use of SQL Server?" "Are there configuration files in place to help determine where things get placed and how to connect to the database?", "Do you provide guidance to the customer on best practices for deployment?"

Don't answer those questions here - they are out of scope, but those are some of the questions you should be asking yourself as you go on this quest. I would recommend reading up on SQL Server application development and spending more time learning about deploying applications in general. I can't think of a good reference right now - but perhaps someone can add an answer with some. There must be something on msdn.com that can help here.


Creating a column in dataset for later filter use (mysql)


Let me suggest a third alternative: You can use triggers to check your business rules and fail the insert/update in a similar fashion a foreign key constraint would.

For your scenario I setup something like this:

CREATE TRIGGER Transaction_BEFORE_INSERT BEFORE INSERT ON Transaction_Table FOR EACH ROW
BEGIN

-- deposit, withdraw must have a bank account and no invoice
if NEW.`Type` in ('deposit','withdraw') then

   if not(select count(*) from Account_Table where id = NEW.id)
      or (select count(*) from Invoice_Table where id = NEW.id)
   then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Error message';
   end if;
-- credit, debit must have an invoice and no bank account
elseif NEW.`Type` in ('credit','debit') then
   if not(select count(*) from Invoice_Table where id = NEW.id)
      or (select count(*) from Account_Table where id = NEW.id)
   then
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Another error message';
   end if;
end if;
END

I've used SQLSTATE 45000 which is a generic state for user-defined exceptions. You may customize that and also set an appropiate error message.

You would also need to create a BEFORE UPDATE trigger that does a similar check.

There will be a performance penalty for abusing triggers this way. Each insert/update will run 2 selects on the other table. You should optimize the selects to be as fast as possible. On high transaction databases it could be a bad solution. So if you choose taking this route please test the impact before running it on production.


Regarding the mismatched regions:  Only the subqueries (i.e. the innermost aggregate queries) are constrained. Such constraints do not directly limit the outer query. So if a score from another S_REGION matches a score from the constrained region, then it will also be returned in the outer query.  That is unless you further constrain the outer query to have the same constraints.

This is true even of your first query even though you might not have noticed: If there ever is another region with tied top scores, you would get values from the other regions also. Try it... add a row with another region matching a USA high score and it will appear in your first top-score query.

Perhaps more useful would be to treat REGION and S_REGION exactly as you have GAME_VER in that you group by these values in all subqueries, then only add specific constraints in the final query.  Not only would this fix your problem of returning mismatched regions, it would easily allow you to view top scores from any region simply by removing or changing the constraints only on the outer query.  This is what I'll do in the examples below.

Embedded subqueries are convenient for posting on Stack Overflow and for keeping everything together in one place.  However, Access is not a fully optimized SQL engine.  It neither creates nor stores optimized execution plans for each subquery.  For subqueries in the WHERE clause, it will almost always re-execute each subquery for every row, even if theoretically it could run it just once and then refer back to the same temporary dataset.

Access does a better job at optimization of saved queries.  Thus, I recommend "saving subqueries" then using JOINS on the saved query rather than embedded SQL.  It allows easier reuse of the subquery and within Access it allows the subquery to be edited and viewed like a normal query.

With all that in mind, here's my suggested solution:

Saved query MAXSCORES:

SELECT e.REGION, e.S_REGION, e.GAME_VER, Max(e.SCORE) AS MAXVAL
FROM dev AS e
GROUP BY e.REGION, e.S_REGION, e.GAME_VER;

Replacement for the first query of top scores (properly filtered on desired region):

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAXSCORES
    ON (e.S_REGION = MAXSCORES.S_REGION) AND (e.REGION = MAXSCORES.REGION)
      AND (e.GAME_VER = MAXSCORES.GAME_VER) AND (e.SCORE = MAXSCORES.MAXVAL)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN;

The 2nd place scores could be obtained just how you did it (of course updated with appropriate constraints), but as already stated, I avoid embedded subqueries in the WHERE clause.  Further, the following query not only returns the 2nd place scores, but it also indicates which groups did not have any 2nd place score--it returns null values.  Lastly, this query returns the 1st and 2nd place scores in one query on the same row which could be convenient for various comparison queries.

Saved query MAX2NDSCORE:

SELECT e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL,
  Max(IIf([SCORE]<[mx].[MAXVAL],[SCORE],Null)) AS MAX2NDVAL
FROM dev AS e LEFT JOIN MAXSCORES AS mx
    ON (e.REGION = mx.REGION) AND (e.S_REGION = mx.S_REGION)
      AND (e.GAME_VER = mx.GAME_VER)
GROUP BY e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL;

A query that returns 2nd place clans for the desired region:

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAX2NDSCORES AS mx2
    ON (e.SCORE = mx2.MAX2NDVAL) AND (e.GAME_VER = mx2.GAME_VER)
      AND (e.S_REGION = mx2.S_REGION) AND (e.REGION = mx2.REGION)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN

Finally, its worth stating that these queries do not properly consider or identify tied scores, but there is nothing in the question or table schema that suggests there cannot be tied scores. The queries could return multiple clans that have the same score.


SQL Server Configuration manager doesn't show the IP

What the version of SQL Server are you using?
Please make sure you open the same version of SQL Server Configuration Manager if you have multiple different version SQL Server instances.
You can use the following query to view the raw data with your network protocols:
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE '%SuperSocketNetLib%'

See this for more details.
Regards,
Amelia


Storing arrays of data in a time-series database

I've read about (but not implemented) Timescale, a time-series database over Postgres. It claims to support all the PostreSQL native datatypes, including arrays. In that product the array type is supported within the query language. I don't imagine Postres disappearing any time soon so a solution based on it would be somewhat future-proof.


I have lots of free memory.  How do I use it to increase performance?

Memory can help you by caching and thus reducing I/O.

However, that won't reduce CPU usage which is your problem. This is an unusual bottleneck, as CPUs are insanely fast for most database work and I/O tends to be the bottleneck.

In your case, it is even more inusual, because you have 16-cores and VPSs tend not to have great I/O performance. First of all, make sure that you are CPU bound. vmstat should help here.If you have high numbers on the wa column, you are probably I/O bound; high numbers on the us column could indicate that you are really CPU bound.

If you are CPU bound, analyze what queries are you executing and why they take long to complete. You are either executing a lot of queries/s or they include complex calculations (i.e. aggregates, functions, etc.). The solution for the former is usually caching on the frontend, which means executing less queries. The latter is solved by simplifying your queries (if possible- you might have queries which are needlessly complex) and calculating stuff once and reusing it (say you have lots of aggregate queries; create a table with the aggregation results and query that instead of running aggregates continuously). The most efficient way to research about this is by logging which queries you are running and analyzing the log- tools exist which do this neatly.

If you are I/O bound, then you can tune memory usage, although the OS cache is often working correctly. Take a look at free:

$ free
             total       used       free     shared    buffers     cached
Mem:       6122892    5903564     219328          0     257020    3119240
-/+ buffers/cache:    2527304    3595588
Swap:     11956220      65980   11890240

The first line of numbers accounts for memory usage including OS caches, the second doesn't; by comparing both you can see how OS caching is working. Also, vmstat will already tell you how much I/O you are performing (bi, bo columns). Often, the key to solving I/O problems is query tuning and indexing; indexing prevents full table scans (i.e. reading the entire table to get a limited set of data, which causes excessive and unnecessary I/O). Again, logging queries is most effective here- running EXPLAIN on the queries will tell you which operations the database is performing to execute the query, which often leads you to understanding inefficiencies in the query (and altering the query to solve them) or finding out about needed indexes.


How to link disaggregate data to aggregate data when aggregation adds 0/NULL values?

It sounds to me like you want the TimeScaleDB extension with a hypertable and 15 min time buckets.

https://www.timescale.com/

It's a free and open source project, like Pg. And it's actively developed.

https://github.com/timescale/timescaledb


The issue wasn't with PostgreSQL itself, but with pgAdmin 4, which apparently is not capable of parsing JSON properly, and truncates the response from PostgreSQL.

I will be using a different tool from now on.


close existing connections during a restore

You will have to set the database to single user with roll back immediate, do the restore , then set to multi user in the same batch

ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [test] FROM  DISK = N'V:\MSSQL2016\Backup\test.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [test] SET MULTI_USER

You could get this script by setting all your options in SSMS and instead of clicking OK, Click Script at the top of the window.


Remote Desktop Session's Machine ID for SQL Server Query

This information is not exposed to SQL Server. All SQL Server sees is the hostname of the machine they actually connected from (e.g. the RDP host).

Since they are connected using Windows Authentication, can you not infer from their login name who they are and what machine they are probably connecting from? You can find this information here:

SELECT session_id, login_name
  FROM sys.dm_exec_sessions;

You may be able to go out externally to Active Directory and somehow determine what machine(s) that user is currently logged into, or perhaps get their connection information from the Windows host, I'm not too sure, but you're going well beyond the built-in capabilities of SQL Server at that point, and I have no idea how you would then correlate that information with a specific session_id.

Another alternative would be to - instead of using RDP (assuming you are doing so because the server is in a different domain) - let the user connect via their local SSMS. Here is a way to do that across domains:

https://stackoverflow.com/questions/9671904/connect-to-sql-via-windows-authentification-over-vpn/9672170#9672170

In this case, the sys.dm_exec_sessions.host_name and sys.dm_exec_connections.client_net_address should be very meaningful.


How to use CREATE TABLE ... AS with Primary Key in SQLite

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );

You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
  PHIL   INTEGER PRIMARY KEY,
  SUCKS  INTEGER NOT NULL
);

INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );

Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);

Hope that helps!


Schema designing for handling multiple payment gateways

It depends on how different the data is between the payment types.

For the sites I support at work, we have one table that stores data for all payment types.  That works for us because our payment types are basically 4 kinds of credit cards and company purchase order.  Most of our customers pay with credit cards, so there isn't a whole lot of deviation in the data.  Of course, queries for those credit card customers always yield NULL values in the PONumber field.  Likewise, queries for PO customers yield NULLs in all the credit card related fields.

If there are a lot of different fields in your data, you could try one master transaction table with individual tables for each payment gateway.  Each payment gateway type table would have a foreign key of transaction_id, which would link back to the master transaction table.

On the other hand if your payment gateway types all have similar fields, then I would stick with one transaction table.


How can I prevent mysqldump from prepending the database name in CREATE VIEW?

They are not bug, but a feature of mysql. Just make sure you are using root or admin account.


Postgres temporarily adds WAL segments beyond max_wal_size to absorb peak transaction loads without forcing a checkpoint -- unlike Oracle.

It removes unneeded WAL seqments to avoid using up the log file system unnecessarily.

There's always a trade-off between performance and resource (in this case log disk space) utilization, and different DBMSes use different approaches to addressing this issue.  In this case PostgreSQL lets you find the balance that works best for you by allowing you to play with the values of wal_keep_segments, max_wal_size and min_wal_size.


Is it possible to create an alias or synonym for a database?

No this isn't possible (Related Feedback Item)
You could create a new database of the original name and fill that with synonyms pointing to all the objects in the renamed database though.


SQL Server Always On and Replication Merge

Can the Express version of the SQL server be within a high availability group in the secondary nodes?

AlwaysON Availability groups require Enterprise edition. There are basic Availability groups for standard edition.

Also, partially contained db do not support replication.

  Partially contained databases cannot use replication, change data capture, or change tracking.


This should be taken care programmatically. Programmers have to close the connection whenever they are done with dB access else memory leaks could happen.
I'm not sure how these processes can be killed as an admin.


The 'answer' was that the ADO.Net providers in SSIS are basically useless and an embarrassment to the '.Net' moniker.  They flake at more than a few thousand rows and are so hobbled as to be effectively useless.  Basically, just stick to the old OLEDB providers and everything will be fine.


What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?

Answer section

There are various ways to rewrite this using different T-SQL constructs. We'll look at the pros and cons and do an overall comparison below.

First up: Using OR

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

Using OR gives us a more efficient Seek plan, which reads the exact number of rows we need, however it adds what the technical world calls a whole mess of malarkey to the query plan.

Also note that the Seek is executed twice here, which really should be more obvious from the graphical operator:

Table 'Users'. Scan count 2, logical reads 8233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 473 ms.

Second up: Using derived tables with UNION ALL
Our query can also be rewritten like this

SELECT SUM(Records)
FROM
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

This yields the same type of plan, with far less malarkey, and a more apparent degree of honesty about how many times the index was seeked (sought?) into.

It does the same amount of reads (8233) as the OR query, but shaves about 100ms of CPU time off.

CPU time = 313 ms,  elapsed time = 315 ms.

However, you have to be really careful here, because if this plan attempts to go parallel, the two separate COUNT operations will be serialized, because they're each considered a global scalar aggregate. If we force a parallel plan using Trace Flag 8649, the problem becomes obvious.

SELECT SUM(Records)
FROM
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

This can be avoided by changing our query slightly.

SELECT SUM(Records)
FROM
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

Now both nodes performing a Seek are fully parallelized until we hit the concatenation operator.

For what it's worth, the fully parallel version has some good benefit. At the cost of about 100 more reads, and about 90ms of additional CPU time, the elapsed time shrinks to 93ms.

Table 'Users'. Scan count 12, logical reads 8317, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 93 ms.

What about CROSS APPLY?
No answer is complete without the magic of CROSS APPLY!

Unfortunately, we run into more problems with COUNT.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age IS NULL
) x (Records);

This plan is horrible. This is the kind of plan you end up with when you show up last to St. Patrick's Day. Though nicely parallel, for some reason it's scanning the PK/CX. Ew. The plan has a cost of 2198 query bucks.

Table 'Users'. Scan count 7, logical reads 31676233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29532 ms,  elapsed time = 5828 ms.

Which is a weird choice, because if we force it to use the nonclustered index, the cost drops rather significantly to 1798 query bucks.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age IS NULL
) x (Records);

Hey, seeks! Check you out over there. Also note that with the magic of CROSS APPLY, we don't need to do anything goofy to have a mostly fully parallel plan.

Table 'Users'. Scan count 5277838, logical reads 31685303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27625 ms,  elapsed time = 4909 ms.

Cross apply does end up faring better without the COUNT stuff in there.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age IS NULL
) x (Records);

The plan looks good, but the reads and CPU aren't an improvement.

Table 'Users'. Scan count 20, logical reads 17564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4844 ms,  elapsed time = 863 ms.

Rewriting the cross apply to be a derived join results in the exact same everything. I'm not going to re-post the query plan and stats info -- they really didn't change.

SELECT COUNT(u.Id)
FROM dbo.Users AS u
JOIN
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

Relational Algebra:
To be thorough, and to keep Joe Celko from haunting my dreams, we need to at least try some weird relational stuff. Here goes nothin'!

An attempt with INTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 1090 ms.

And here's an attempt with EXCEPT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

Table 'Users'. Scan count 7, logical reads 9247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2126 ms,  elapsed time = 376 ms.

There may be other ways to write these, but I'll leave that up to people who perhaps use EXCEPT and INTERSECT more often than I do.

If you really just need a count
I use COUNT in my queries as a bit of shorthand (read: I'm too lazy to come up with more involved scenarios sometimes). If you just need a count, you can use a CASE expression to do just about the same thing.

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END)
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END)
FROM dbo.Users AS u

These both get the same plan and have the same CPU and read characteristics.

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 719 ms.

The winner?
In my tests, the forced parallel plan with SUM over a derived table performed the best. And yeah, many of these queries could have been assisted by adding a couple filtered indexes to account for both predicates, but I wanted to leave some experimentation to others.

SELECT SUM(Records)
FROM
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

Thanks!


Mysql table doesn't exist error, references wrong table name

It sounds like there is a trigger on the search.books table that is attempting to insert into the non-existent table, search._books_new

This query will display a list of triggers in the database:

SELECT *
FROM INFORMATION_SCHEMA.TRIGGERS


How to get the timestamp column in only milliseconds from PostgreSQL?

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

  1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;


How to construct a set-based query for subset problem?

Assuming referential integrity and all columns to be NOT NULL, this should be simplest and fastest:

SELECT *
FROM   Receipe r
WHERE  NOT EXISTS (
   SELECT 1
   FROM   Ingredients i
   JOIN   RecipeIngredients ON ri.IngredientsId = i.ID
   WHERE  ri.RecipeID = r.Id
   AND    ri.RequiredQuantity > i.AvailableQuantity
   );

Basically, use a NOT EXISTS anti-semi-join to rule out recipes with any shortcomings. As soon as the first is found, Postgres can drop the recipe at hand from the result and move on.

Aside: My standing advice is not to use CaMeL-case identifiers in Postgres.


transactional Replication of millions of updates

Panic over !

When adding the stored procedure to the Article, in the properties there is a setting called 'replicate'. By default this is schema only. When i changed this to Execution of the stored procedure' instead of 'Execution in a serialized transaction of the SP'. and restarted all the agents, it worked as one transaction this time


Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

Sorry - the answer is: no.
Indeed what you found is the only thing in I_S regarding views.

Not only is this barbaric on the client side, it is also such on the server side: when you:

SELECT ... FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='your_view'

MySQL actually drills down to referenced table definitions and this type of query is very costly if there are many nested views (I have a particular case on my servers).


Is it possible to have a SQL Server cluster with Hadoop?

You cannot run SQL Server under Hadoop but you can query Hadoop data via T-SQL in SQL Server 2016.
This next edition of SQL Server includes a technology called PolyBase which allows access of Hadoop data and Azure blob storage from T-SQL. Tutorials are available from Microsoft at the bottom of the linked page.

SSIS for SQL Server 2016 has Hadoop connection managers available also: Hadoop File System Task.


Denied updating a field that is modified within a BEFORE UPDATE TRIGGER

Your logic is inverted.  You are setting synced to 0 if either column is not changed.  The correct expression:

IF
    NOT(NEW.FirstName <=> OLD.FirstName) OR
    NOT(NEW.LastName <=> OLD.LastName)
    THEN

The <=> operator is an equality comparison that considers nulls to be equivalent.


Is there a name for a query that returns results which have sub headings?

It is a standard parent-child hierarchy.  It is commonly used in recording manager/subordinate relationships among other uses.  This works well in hierarchical organizations.  It does not record historical relationships which need to be recorded using different techniques.  It also breaks down in matrix organizations, but can still be used to record the organization structure in such cases.  Project/assignment relationships need to be modeled differently.

The report structure appears to use control breaks.  There are a number of query formatting tools which handle this.

There are cases where I might model employers and employees in the same table.  Normally I would model them as separate entities with a join table recording the relationship.

EDIT:  One of the ways to model hierarchical relationships which occur within a table is to include a parent_id column which points to the parent row.  This can be an efficient method to model the relationship where there can be at most one parent, and no other data is required in the relationship.  If there can be more than one parent, or there are attributes related to the relationship, then the relationship should be modeled using the join table.

Control breaks are a reporting structure where control is transferred to the outer loop when a value changes.  There can be multiple levels of control breaks.  When coding this manually, headers for the first row need to handled manually.  These days, the complexities are usually handled by a framework or tool of some sort.  Oracle's SQLPlus query tool uses BREAK ON to specify formatting related to control breaks.


SSMS connections not using KERBEROS over VPN - why?

Assuming the client has SSMS 2012 installed, you might have one of them try running the following from a command-prompt as a test:

  %SystemRoot%\System32\runas.exe /netonly /user:DOMAIN\Username "%ProgramFiles(X86)%\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe"

If they have some other version of SSMS, you'd need to replace the "110" with the appropriate version number, such as 120 for SSMS 2014 or 130 for SSMS 2016, etc.

If that does work, and you are so-inclined, you could use my answer on StackOverflow to create a small User Interface for your users that could prompt them for the domain\username\password then launch SSMS using netonly authentication.  This would essentially mean they wouldn't need to run the "runas" command manually.


Create a DELETE statement that deletes Manufacturers with less than 10 sales

So you're not too far off, you're just missing a couple key things, the first being the JOIN keyword. JOIN is how you can combine two related datasets. There are different types of joins, one being an INNER JOIN which is how you can combine two datasets and only return the rows that match the join condition. For example, if you wanted to get only Customers who have an Order and you want to know the Name of that Customer and the SerialNumber of their Order you could do so with an INNER JOIN like so:
SELECT Customers.Name, Orders.SerialNumber
FROM Customers
INNER JOIN Orders
    ON Customers.ID = Orders.CustomerID

You can join as many datasets as you like in a single query. So if we also wanted to know the ModelNumber for the above Customers' Orders, we could modify the above query like so:
SELECT Customers.Name, Orders.SerialNumber, Machines.ModelNumber
FROM Customers
INNER JOIN Orders
    ON Customers.ID = Orders.CustomerID
INNER JOIN Machines
    ON Orders.SerialNumber = Machines.SerialNumber

Deriving from the above example we can do the same to get a list of all Manufacturers' Orders, by chaining together a bunch of INNER JOIN clauses. Lets only return the ID of the Manufacturer as well, since that's all we care about right now. Note since this is a list of all Manufacturers' Orders, there will be one row for every Order, so you should see the same ID of the Manufacturer repeated in cases where they have more than one order:
SELECT Manufacturer.ID
FROM Manufacturer
INNER JOIN Model
    ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
    ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
    ON Machines.SerialNumber = Orders.SerialNumber

Now you need a way to count how many Orders (how many rows) there are for each Manufacturer.ID from the previous query. To do that we can use the built in COUNT() function. Because we want to count the number of Orders for a certain criteria (by Manufacturer), we'll also need to use the GROUP BY clause. The GROUP BY clause allows us to roll-up (flatten) all the rows by the criteria we specify into a single unique row per grouping based on the columns we specify. In this case we just want a single row per Manufacturer.ID so that when we use the COUNT() function, it counts the number of Orders within that grouping, like so:
SELECT Manufacturer.ID, COUNT(*) AS OrderCount
FROM Manufacturer
INNER JOIN Model
    ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
    ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
    ON Machines.SerialNumber = Orders.SerialNumber
GROUP BY Manufacturer.ID

So the above query now gives us the list of Manufacturer IDs and the count of how many Orders each one has. Let's filter it down to only the Manufacturers who have less than 10 Orders. We will use the HAVING clause to accomplish this, which is the equivalent of using a WHERE clause to filter data but can only be used with aggregate functions like COUNT() on grouped data:
SELECT Manufacturer.ID, COUNT(*) AS OrderCount
FROM Manufacturer
INNER JOIN Model
    ON Manufacturer.ID = Model.ManufacturerID
INNER JOIN Machines
    ON Model.ID = Machines.ModelNumber
INNER JOIN Orders
    ON Machines.SerialNumber = Orders.SerialNumber
GROUP BY Manufacturer.ID
HAVING COUNT(*) < 10 -- Filter out everything except Manufacturers with less than 10 Orders

Finally, we want to use a subquery as you mentioned, so we can DELETE the results in our above query from the Manufacturer table like so:
DELETE FROM Manufacturer
WHERE ID IN
(
    SELECT Manufacturer.ID
    FROM Manufacturer
    INNER JOIN Model
        ON Manufacturer.ID = Model.ManufacturerID
    INNER JOIN Machines
        ON Model.ID = Machines.ModelNumber
    INNER JOIN Orders
        ON Machines.SerialNumber = Orders.SerialNumber
    GROUP BY Manufacturer.ID
    HAVING COUNT(*) < 10 -- Filter out everything except Manufacturers with less than 10 Orders
)

Note that when you use a subquery inside of the IN keyword, you can only return one column, and for your case you only care about the ID of the Manufacturers for who needs to be deleted. So I removed the OrderCount column from the subquery above.
There are definitely alternative ways to accomplish the above as well, but to keep the concepts you need to understand to a minimal, this is a pretty good solution. The concepts I hope you took away from this are:

Joins, specifically the INNER JOIN clause, and you should research Outer Joins as well.
Aggregate Functions such as COUNT(), there are many others too such as MIN(), MAX(), and SUM(), etc.
The GROUP BY clause and how grouping works in general.
The HAVING clause and how and when to use it.


Assuming TABLE 2 already exists with id_org, id_club, and foo, just create the 'the_value' column and issue an UPDATE call using joins

See the UPDATE manual

UPDATE table1 t1, table2 t2 SET t2.the_value=t1.the_value
WHERE t1.id_org = t2.id_org AND t1.id_club = t2.id_club


ORA-02270: no matching unique or primary key for this column-list

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table.

Why do you need to duplicate these columns in the child table?

EDIT:

Locking at you child table you say that:

create table bit_2015_sep_cit4114_fyp_G_
( ...
, Full_Name VARCHAR2 (50) NOT NULL
      REFERENCES STUDENTINFO(Full_Name)

What this means is that there must be a unique row in STUDENTINFO with this fullname. I.e. you need to declare that table:

CREATE TABLE STUDENTINFO
( ...
, Full_Name VARCHAR2 (50) NOT NULL
     UNIQUE,

I doubt that full_name is unique which means you cant declare a foreign key against this column.

You seem to assume that there will be performance problems later on and therefore you de-normalize your database. IMO this is a big mistake, start with a normalized database and de-normalize only when there is reason to do so.

If you insist on de-normalizing you can use a trick like:

CREATE TABLE STUDENTINFO
( Student_ID VARCHAR2 (10) PRIMARY KEY
, Full_Name VARCHAR2 (50) NOT NULL
, Contact_Number NUMBER (15)NOT NULL
, Address VARCHAR2 (50) NOT NULL
, Nationality VARCHAR2 (15) NOT NULL
...
,    constraint AK1_STUDENTINFO unique (Student_ID, Full_Name, Nationality)

Since Student_ID is unique it follows that Student_ID, Full_Name, Nationality must also be unique. I excluded Fingerprint_Template since I doubt that this can be used in foreign keys (not sure though, if it can you can add it)

create table bit_2015_sep_cit4114_fyp_G_
( Student_ID VARCHAR2 (10) PRIMARY KEY
, Full_Name VARCHAR2 (50) NOT NULL
, Nationality VARCHAR2 (15) NOT NULL
...
,    constraint fk_studentinfo foreign key (Student_ID, Full_Name, Nationality)
                references studentinfo (Student_ID, Full_Name, Nationality)

);

But as mentioned, start with a normalized design and see if that works


Migration path for IDENTIFIED BY PASSWORD 'password_hash' when removed in MySQL 8

We will need to perform additional checks, but a workaround we have found is to simply instead manually set "authentication_string" and "plugin" in "mysql.user" to the desired values, and then execute "flush privileges". That is, to keep using 'mysql_native_password'  and sha1(sha1(password)) that we know the hash is calculated as.

We then wouldn't be able to make use of the new default authentication plugin though. A quick check also says that caching_sha2_password (new default) doesn't have a stable authentication_string wrt password. Two users with the same password yielded different values so I'm assuming its implementation is more advanced in what input is used.


How to reinsert corrected rows from the conflict table?

You can use the Conflict Viewer to submit the winning or losing row.


Will frequent delete and inserts of the same data lead to fragmentation?

Let's consider what data will actually be stored on the data pages. You said that your images are around 10 KB - 17 KB which means that the VARBINARY(MAX) column will never be stored in row. Instead, it will always be stored as LOB data. So on the in row page for each row you'll have two INT columns along with a 24 byte pointer to the LOB data. Assuming a generous overhead of 10 bytes per row you can fit around 8000 / (10+4+4+24) = 190 rows on a single page. The row data on each page may be physically out of order and the different data pages may not be in order but SQL Server won't just insert the rows wherever it can find space. It's not true that for a given ProductId you'll have ImageId 1 stored on page 1, ImageId 2 stored on page 2, ImageId stored on page 3, and so on. So when you run the following query:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123

Unless you have hundreds of images for one product, all of the IO is going to be from going to the LOB pages to get the [Image] data. There will only be one or two relevant pages that store the pointers to the LOB data. I don't know how SQL Server picks which page is used for the LOB data. I suppose it's possible for the LOB pages to become more spread out over time.

No one here can say for sure if fragmentation will be a problem for you. As I see it your options are to schedule a maintenance job that deals with any fragmentation, to add code to your application to prevent unnecessary updates, or to simulate your workload to see if you need to take further action. From SQL Server's point of view it doesn't matter that the VARBINARY data corresponds to an image. Anything that you store to the column of the right size should work fine for a test.


It's because you're using the SELECT * and joining a table to itself. The DB engine will try to return all columns, so it needs to know which columns you are referring to if you have multiple with the same name. If you want both you need to specify them with aliases to indicate which order to output them with.

The reason the aggregation works is because you're not trying to return any duplicated columns. Replace the SELECT * with the columns that you want, using the Aliases to specify which tables you want them from.

As a general best practice, do not use SELECT *; always specify your column names.


Neither query_hash nor query_plan_hash are used to look up query plans for execution, so the exact concern in the question does not arise.

The documentation for sys.dm_exec_query_stats explains that:

query_hash is provided to allow users to aggregate resource usage for  queries that differ only by literal values (ones that would parameterize to the same form).
query_plan_hash is provided to allow users to calculate the cumulative cost of queries with similar execution plans.

Unsurprisingly, the SQL Server developers have gone to great lengths to ensure that cached plans are only reused when it is safe to do so. This goes well beyond the obvious requirement that the cached plan must match the 'query' exactly.

Briefly:

SQL Server has multiple plan cache stores, each implemented as a hash table. A hash value and cache key pair is used to connect a compiled plan with a particular 'query'.

The hash value is (database_id * object_id) mod (hash table size). This determines the hash bucket in a particular cache store. The object_id is known metadata for objects like  procedures, functions, and triggers. For ad-hoc SQL, the object_id is a hash of the entire batch text.

The collection of attributes that must match for a cached plan to match a 'query' is known as the cache key. This is used to find and confirm an exact match within the hash bucket. Some (not all) cache key attributes can be found in the documentation for sys.dm_exec_plan_attributes.

Importantly, for ad-hoc SQL, the entire batch text is one of the cache key attributes.

More details can be found in 3.0 How Cache Lookups Work (and other articles in the series) by the Microsoft SQL Programmability & API Development Team.


Postgresql Total elements and affiliation users

You need to remove user_id from your group by clause. But that requires putting it into an aggregate function in the SELECT list. One way to do that is to use string_agg()
SELECT its.item_id,
       its.tag,
       count(its.tag) as tags_count,
       string_agg(u.user_id, ',') as user_ids
FROM items_tags_sets its
  LEFT JOIN users u on u.user_id = its.user_id and u.user_id = 'usr1'
WHERE its.item_id = 'it1'
GROUP BY its.item_id, its.tag
order by its.item_id, its.tag;

Online example


Handling variable fields in a CSV file with JSON?

I wouldn't do all that. I would just use python to transform the CSV file into another CSV where all the variable fields are a string of JSON.

Create the right table with the jsonb column and load direct into it. To do that use psql and \COPY. You can even create a program that reads from STDIN does the reformatting and outputs to STDOUT. And sits in the pipeline. Or you can write a quick perl script to get this done,

perl reformat.pl stupid1.csv stupid2.csv |
  psql -d test -e '\COPY mymastertable FROM stdin WITH ( format = csv )'

Or whatever.


How can I get the Oracle TNSListener to start after changing my host name?

When you run lsnrctl status, one of the things that it tells you is the location of the Listener Parameter File (the listener.ora file)

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-JUN-2012 13:12:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                06-JUN-2012 09:33:58
Uptime                    0 days 3 hr. 38 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\jcave8560w\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jcave11gR2XDB" has 1 instance(s).
  Instance "jcave11gr2", status READY, has 1 handler(s) for this service...
Service "jcave11gr2" has 1 instance(s).
  Instance "jcave11gr2", status READY, has 1 handler(s) for this service...
The command completed successfully

If you open that file, there will probably be an entry like this

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

where the host is specified.  Change the host there and restart the listener.


Yes.

The query in the question would in principal be able to do this more efficiently than the obvious alternative of just doing select count(*) from sys.databases.

For the same reason as EXISTS is generally preferable to COUNT. It can short circuit.

The TOP underneath the aggregate operator can stop requesting rows as soon as the second one is received rather than requesting more and causing additional irrelevant rows to be read.

It isn't absolutely guaranteed to be more efficient however. There are occasionally edge cases where adding a TOP causes a worse overall plan than that for the query which simply brings back the whole result set.


Accumulated logs - Log shipping SQL Server

The log-shipping process includes 3 steps.

T-Log backup of the primary database
Copy the backup file to the target destination (shared)
Restore to the target database with standby or no-recovery.

If we use stand-by, there are 2 option available disconnect user or go without disconnecting it. If we don't disconnect active sessions restore wouldn't begin until there's no active connection with the database.

In this case, steps 1 & 2 (backup & copy) will execute normally but restore will fail.

The log-shipping process wouldn't delete any backup file until it's older than the defined threshold.

Once there's no connection with the database it will restore all transaction log file greater then last LSN.

Thanks!


How to execute SQL against all DBs on a Server

sp_MSForEachDB

One option is sp_MSForEachDB. It's undocumented but useful nonetheless

DECLARE @command varchar(1000)
SELECT @command =
    'USE [?] UPDATE Table1 SET Field1 = ''ninjas'' WHERE Field2 = ''pirates'''
EXEC sp_MSforeachdb @command

A search of the interwebs has many more examples too

Note: Being an unsupported function (which has some known bugs) you may want to write your own version (thanks @Pradeep)

The SQL example above would need to be restructured as:

DECLARE @findKeySQL nvarchar(2000)
DECLARE @searchKey nvarchar(20)

SET @searchKey = lower('%remote%')

SET @findKeySQL = 'IF ''[?]'' NOT IN (''[master]'', ''[model]'',
                                     ''[msdb]'', ''[tempdb]'')
        select
            so.name,
            so.type,
            @@ServerName as Server,
            ''?'' as DBName
        from
            [?].dbo.sysobjects so with (nolock)
            join [?].sys.all_sql_modules sc with (nolock) on so.id = sc.object_id
        where (lower(sc.definition) like ''' + @searchKey + ''')
        group by so.name, so.type
        order by so.type, so.name'

EXEC sp_MSForEachDB @findKeySQL

NOTES:

? is replaced in the query as the
database name, so structure the
query to explicitly define which DB
it is to query against
modified to use sys.all_sql_modules as holds complete module text (syscomments could split the keyword when reach the spanning over rows)


How to commit transaction on an after update event trigger?

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB
after update on B
for each row
declare
v varchar2(5);
begin
  v := :new.val;
end;
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA
after update on A
for each row
declare
pragma autonomous_transaction;
cek varchar2(5);
begin
commit;
  cek := :new.val;
dbms_output.put_line('Cek cek: ' || cek);
update B set val = :new.val where id = :new.id;
commit;
end;
/


SQL Server 2008 commit overhead warnings

Using performance monitor in windows you can for example watch the redo queue.
Also any other db monitoring tool would help.

The next sentence explains the redo queue, since I can't do it better than msdn here the quote:
"The mirror server immediately writes the incoming log to disk, where it is held until it is applied to the mirror database. The log waiting on the mirror's disk is known as the redo queue."

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


Windows Authentication for a non-local non-domain Login

Create matching usernames and passwords. It really is that simple. For your example, create an account for User1 on Server1 with the same password as on PC1.

Just in case this is an issue you're having with connecting SSMS with a non-domain account, that requires jumping through an additional hoop. Some applications don't support stored usernames and passwords via the windows credential store. SSMS is one of them.

I have a bunch of runas shortcuts to deal with connecting SSMS to a variety of servers in different domains:

C:\Windows\System32\runas.exe
/netonly
/user:DOMAIN\USER
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

You could also make use of the SysInternals ShellRunAs extension.


SSMS Restore Database wizard slow to respond

Does the wizard show you stats as you go?

Have you tried using T-SQL to restore?  The wizard is probably that way because it's probably just busy in the background, which is certainly the case if your bak file is big (or trn files are plentiful).  The script below will show you stats every 5% in the "Messages" tab.

Might also help to take the database offline prior to a restore (ensure there are no users while doing this, or it will "do it for you"):

--Taking DB Offline due to errors received without this.
--Error: "Exclusive access could not be obtained because the database is in use."

ALTER DATABASE [DBToRestore]
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DBToRestore]
SET ONLINE
GO

RESTORE DATABASE [DBToRestore] FROM  DISK = N'Directory\SourceDBbackup.bak' --**Replace this value with the most recent FULL Backup file (BAK)
WITH
FILE = 1
,  NORECOVERY  --If Transaction Logs are available, use this
,  NOUNLOAD   --Tape remains loaded on the tape drive.
,  REPLACE    --Replace the destination database.
,  STATS = 5  --Displays stats update message for every ## Percent complete.

GO

--Finally, "restore" the database without actually restoring the data.
--This is done post-full restore in order to bring a database back to working order. (Technically optional)
--Details: https://msdn.microsoft.com/en-us/library/ms188696.aspx

RESTORE DATABASE [DBToRestore] WITH RECOVERY;

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


Why isn't SQL Server using the Max Server Memory setting?

The actual memory in use won't increase until you actually load data into memory. It's not going to jump to 10GB combined allocated just because that's what you've set max memory to and because your databases are at least that large. It's a max, not a min (and never mind that min doesn't work that way either), and it isn't going to try to guess - before you've run enough queries - which tables / indexes it should load into buffer pool memory.

So yes, it is normal to see a gap in these values. You can probably get much closer to the max values if you pump the buffer pool by issuing a bunch of SELECT * FROM dbo.BiggestTables; on each instance, but it is usually better to let SQL Server allocate the memory as it deems appropriate based on actual usage by your real application. And remember too that max server memory doesn't just cover the buffer pool.

In short, SQL Server is pretty good at memory management; let it do its thing, and worry about adjusting max memory on your various instances when you have an actual performance issue.


Drop table without its dependencies and solve block corruption

Apparently you dropped the object already. Now to calm you down - corrupted blocks now most probably are in free space and do no harm. They just annoy you during backups. To check that:

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) corr_start_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
  , 'Free Block' description
from dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1
  and f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

If it really shows here you have two options. You can simply ignore it. Once the block will be assigned to some object it will be reformated and corruption will go away. If you want to fix corrupted blocks then you will have to create the object which would occupy your corrupted blocks.

Let's say your corrupted block exists in tablespace USERS and datafile /ora/users01.dbf. First you have to make datafiles of tablespace not autoextendable. That is not to inflate datafile size. And then you'll create filler table.

create table filler (
  padding varchar2(1000)
) tablespace users nologging pctfree 90 pctused 10;

Then you have to extend table's segment so that it would incorporate corrupt blocks:

alter table filler allocate extent
  (datafile '/ora/users01.dbf' size 100m);

In such way you can add several extents adjusting their sizes. After you'll check that corrupt blocks now longer belong to free space you can fill the table:

insert /*+append */ into filler  select rpad('x',1000) from
dba_objects
--  filler
where rownum <= 50000;

First insert/select goes from dba_objects then it is faster to insert/select from filler table itself.

Now you just have to check datafile using RMAN:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 333;

And drop filler table:

DROP TABLE filler PURGE;

Easy. :)


If you have a table with 1.9 billion rows, I'm guessing it has a fairly low change rate as a percentage. Check how many rows you're actually inserting/updating on a daily basis - it's likely less than 1%.

In that case, it doesn't make sense to reorganize the whole table every 2-3 days (especially given that it takes 10+ hours.) I'd start by only doing index maintenance weekly, if not MONTHLY on a table of that size.

Start by taking a step back and asking, "What's the problem I'm trying to solve by doing index reorganizations?" If the answer is slow select statements, then index maintenance on a 1.9bn row table isn't going to be the answer.


How many SELECT/UPDATE/INSERT Operations can you run on a 4 core / 8 GB RAM server?

The maths behind it is far more complex than you seem to be implying.

Sub-selects count as queries in their own right, so a query with 3 sub-selects counts as 4 queries.

Size of the data matters as does indexing and indexability of every query.

If a query is returning 100,000 rows, it'll be orders of magnitude slower than a query returning 1 row.

If a query needs to check 1M rows to compute the result, it'll be slow regardless of the fact that it returns 1 row.

The "unit" of complexity is not a query. The CPU in question might get you 10,000 queries per second of one query on one data set, but 0.001 queries per second of another query on the same data set or 0.00001 qps of the same query on a different data set.

There is almost no limit on how computationally complex "a query" can be.


Mysql query to retrieve the latest updates based on DATETIME for each parameter

I think you may be looking for something like this:

SELECT s.id,s.station_code,s.parameter_code,s.value,s.date_time
  FROM station_data s
 WHERE s.date_time = (SELECT MAX(date_time) FROM station_data WHERE station_code = s.station_code AND paramter_code = s.parameter_code)

Add whichever parameters are important for uniqueness in your sub-query.

I would also recommend for this to run optimally, you create an index that indexes the three columns in that index, namely date_time,station_code and parameter_code)


Your session is in idle state, not the query.
Sessions that are idle are not a problem. It simply means the backend is waiting for the client to send a SQL query.
The number of sessions is a little bit on the high side though. You will need to have a really powerful server to cope with that, once all of them start doing something.
Sessions that are idle in transaction would be a problem if they stayed in that state for too long. That's what the idle_in_transaction_session_timeout handles, not "idle" sessions.


In the Plan Explorer user preferences (Tools -> User Preferences), uncheck the box reading "Only save history if command text or connection settings change".

If this does not work, try adding something to the query itself that is inconsequential - a line break, an extra space between tokens, something of that nature. This will both trigger a change to the command text and cause a new query plan hash to be generated by SQL Server.


MySQL performance issues when saving Bitcoin data

I am not aware of any Bitcoin-specific best database practices. I also see no need for them, as just general good database design will help you.

The key to achieving decent database performance is to define indices in a way that every access you every need can take advantage of them. Where you do not succeed in this, just about any query will revert to iterating over every record in the table, which obviously becomes slow when you reach gigabytes of stored data.

A good place to start will be to define a (ideally integer, i.e. BIGINT) unique index, also known as primary key. If you do not do that yourself, mysql will just invent one anyways---and if you do, you automatically have a fast way to refer to transactions (in database parlance: records in the vin and vout tables). If you invent a new field for it---as I would suggest---you may want to call it id and use AUTO_INCREMENT to have mysql take care of inventing new unique values for new records.

You may also want to have a close look at your VARCHAR entries. These are essentially just (very big) integers, and if you define them as such (check out the DECIMAL and NUMERIC types), handling indices on them becomes computationally much cheaper because complications in string sorting such as collation do not arise. If you prefer to work with the hexadecimal representations, then that can be achieved with suitable VIEWs into your tables, but be warned that using these is a frequent source of new performance problems since optimization to index-usage after querying a view is a complicated subject. To get this right, it may be safer to do all conversions between hex and large-integer representation outside the database.

To increase your chances at getting better and more detailled answers than I have just given, I very strongly suggest you

Look to a developer or database community, because none of the relevant issues are very Bitcoin-specific, at best they are specific to database applications involving very large integers.
Provide relevant information such as what you've already done---or do you expect someone to completely re-invent an indexing scheme neither knowing what queries you like to run, nor what you have already managed to get right youself?

Good luck!


Importing Oracle database from dump where source dump is spread across different tablespaces

try remap_tablespace parameter:

remap_tablespace:source_tablespace_1:user,source_tablespace_2:user


Create a user with privileges to create users

To be able to create mysql users, you need to be able to insert into the users table in the mysql database. So create your master user and give him write access to the mysql database.

The documentation states ( http://dev.mysql.com/doc/refman/5.7/en/create-user.html )

  To use CREATE USER, you must have the global CREATE USER privilege or
  the INSERT privilege for the mysql database. When the read_only system
  variable is enabled, CREATE USER additionally requires the SUPER
  privilege.

So the process would be something like this:

(root)> create user masteruser ...
(root)> grant CREATE_USER to masteruser ...
(masteruser)> create user foo1 ...
(masteruser)> create user foo2 ...

Also, it is deemed good practice to use a test system to test such behaviour; so that when you mess up, there is no harm done to a actual real database.


Empty partitions have negligible effect on query performance, so having up to 12 empty partitions, one per month, should not be a problem.

Having said that, you could simply automate the creation of partitions, the way I detail in this answer.  That means you'd only ever have a single empty partition, assuaging any fears about performance impacts from too many empty partitions.

Table partitioning is not a panacea for increasing performance and can result in lower performance than a non-partitioned table.  The primary use-case for partitioning is to enable the easy swapping out of old data, and swapping in of new data, using the ALTER TABLE ... SWITCH syntax.  This answer shows the advantages of using ALTER TABLE ... SWITCH to ease data management.

In order to get some performance improvement via partitioning you need to use the partitioning key in queries against the partitioned table.  Queries that don't use the partitioning key in the WHERE clause or in a JOIN clause must inspect all partitions for rows that match the selection criteria.


The ALTER TABLE ... DEFAULT CHARACTER SET ... command sets the default character set and collation for the table. It does not affect the existing VARCHAR/TEXT columns and their data. Only newly created fields will use the specified charset and collation.
The ALTER TABLE ... CONVERT TO CHARACTER SET ... command changes the character sets and collations of all the existing VARCHAR/TEXT fields (and converts the data). It actually should change the default charset for the table as well. It is not specified obvious enough here: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html but it works in that way. On my MySQL 5.1.45 the default charset is changed by the CONVERT operation even it does not include VARCHAR-fields. Also the CONVERT operation may change the datatype if it cannot store the new length of data.


What are the best practices for triggers to maintain a revision number on records?

Along the lines of my response to your earlier question, surely this would be better dealt with in the stored procedure or insert/update query that is responsible for the change. That would be the logical place to increment a version column, not a trigger.

If a trigger is the only way you can be sure the revision count will be implemented, I'd suggest an INSTEAD OF trigger on any tables you need this. You can deal with the revision increment and auditing in one place then.


SQL Server 2012 : how to add missing collation

There never has been a "Latin1_General_CP1_CI_AS": it is "Latin1_General_CI_AS" or "Latin1_General_100_CI_AS"

You may be confusing it with "SQL_Latin1_General_CP1_CI_AS" which is legacy, basically.

Saying that, the collation offered at setup varies based on your regional settings. See SQL Server 2005 collation issue during installation for more (I've no idea why US English locale offers a "legacy" SQL collation)

SELECT * FROM ::fn_helpcollations() FH WHERE name LIKE '%Latin%_CI_AS'


How to combine a parent with all children in the same row

Here is one of the solutions, but in this particular case we have limited number of students per parent. I wrote the query for 3 students.

with cteParent ( ParentID, FName, LName, StudentID )
as (
              select 1, 'John', 'Doe',  11
    union all select 1, 'John', 'Doe',  12
    union all select 2, 'Jane', 'Doe',  11
    union all select 2, 'Jane', 'Doe',  12
    union all select 3, 'Joe',  'Dirt', 13
) ,
cteStudent ( StudentID, FName, LName )
as (
              select 11, 'Janet', 'Doe'
    union all select 12, 'Jackson', 'Doe'
    union all select 13, 'Jane', 'Dirt'
) ,
cteParentNumbered
as (
    select p.ParentID
         , p.FName
         , p.LName
         , p.StudentID
         , row_number() over (partition by p.ParentID order by p.StudentID) as StudentNum
    from cteParent p
)
select
       pvt.ParentID
     , pvt.FName
     , pvt.LName
     , s1.StudentID as Student1Id
     , s1.FName as Student1FName
     , s2.StudentID as Student2Id
     , s2.FName as Student2FName
     , s3.StudentID as Student3Id
     , s3.FName as Student3FName
from cteParentNumbered p
    pivot ( min (StudentID) FOR StudentNum IN  ( [1], [2], [3] /*... add more if needed */ ) ) AS pvt
    left join cteStudent as s1 on s1.StudentID = pvt.[1]
    left join cteStudent as s2 on s2.StudentID = pvt.[2]
    left join cteStudent as s3 on s3.StudentID = pvt.[3]
    --... you can add more if needed

To remove limit of students per parent you should build your sql query dynamically and run it via sp_executesql, but it has no practical use. Usually this handled by client application and/or report engine. For example it can be displayed in report as hierarchical list:

Parent 1

student 1
student 2
...
student N

Parent 2

student 1
student 2

...


Oracle start database with incorrect password

When your OS user is from a DBA group, you can connect AS SYSDBA with OS authentication:

  Two special operating system groups control database administrator
  connections when using operating system authentication. These groups
  are generically referred to as OSDBA and OSOPER. The groups are
  created and assigned specific names as part of the database
  installation process. [..]

  Membership in the OSDBA or OSOPER group affects your connection to the
  database in the following ways:

  If you are a member of the OSDBA group and you specify AS SYSDBA when
  you connect to the database, then you connect to the database with the
  SYSDBA system privilege.
  If you are a member of the OSOPER group and you specify AS SYSOPER
  when you connect to the database, then you connect to the database
  with the SYSOPER system privilege.

You can then use CONNECT / AS SYSDBA or even CONNECT FOO/ANYTHING AS SYSDBA, the DB will ignore the user/password.


Not able to change the AUTO_DEL_REC_OBJ value to ON

You must connect to the database first to change some parameter online, if it's possible for this parameter:
db2 connect to CMNDB
db2 update db cfg using AUTO_DEL_REC_OBJ ON
db2 get db cfg show detail | grep AUTO_DEL_REC_OBJ


Rebuilding a single corrupt table with dual master replication

Ensure host B is your active host in the dual master
Rest of instructions are on host A
As reading from A.tablename will probably crash it, try this with another tablename to be familiar with what its doing. pt-table-sync --dry-run --print --execute --sync-to-master h=hostB,D=db,t=sometablename
look closely at the above.
SET GLOBAL innodb_file_per_table=1 (if not already)
STOP SLAVE
To have a new table idb file: set session sql_log_bin=0; RENAME TABLE tablename TO tablename_orig
quick populate table from B without replicating: ( echo 'set session sql_log_bin=0;' ; mysqldump --single-transaction --host hostB db tablename ) | mysql --host A db
start slave
pt-table-sync --print --execute --sync-to-master h=hostB,D=db,t=tablename

Please read pt-table-sync carefully. Its been a few years since I've used this. Note the bits about it locking tables briefly on the master for small periods of time to read data in small chunks. This, and all the other interactions should be obvious on from the --dry-run --print.


Please make sure you have the value "4" in "id" column in dorogi.activities_category and also change the query to the following:

INSERT INTO dorogi.activities_article (category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, updated_at, hot)
SELECT category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, NOW(), False
FROM dorogirussia.activities_article WHERE category_id=4;

Hope this solves your problem !!


Log File Full -- Database is in AlwaysOn

Will manual log backup affect the AlwaysOn ?
No Manual Log backups wont affect,this is same like you are doing through scheduled jobs..In Summary

Log backups can happen on any node and the log still gets truncated.  The transaction log is basically replicated amongst all replicas.

like log shipping, will AlwaysOn have log backup jobs

AlwaysOn is similar to Mirroring in areas where log will be transferred over wire..

From Bob Dorr article

Always On is a bit different than database mirroring (DBM) with respect to sending the log blocks to the secondary replica(s).   DBM flushes the log block to disk and once completed locally, sends the block to the secondary.
Always On changed this to flush the block(s) in parallel.  In fact, a secondary could have hardened log block(s) before the primary I/O completes.    This design increases performance and narrows the NOT IN SYNC window(s).

Shrinking fails because of pending log backup
if you are saying can i shrink log to reduce space.Don't do that,shrinking is a bad practice.Log space will be reused .Taking into Account factors that can prevent log space reuse,you also need to account for fact that replicas have hardened the log blocks to Disk
References:
https://blogs.msdn.microsoft.com/sqlgardner/2012/07/18/sql-2012-alwayson-and-backups-part-1-offloading-the-work-to-a-replica/


Should I use separate address dimensions?

You should add the address to the user/store dimension as the address is a property of the dimension member, not the fact record.

Adding the address to the dimension allows you to define attribute relationships between country -> city -> user which should improve performance when you create hierarchies using those attributes.

It will usually also allow for easier calculated measures.

As a last point, having the address as attributes on the dimension will allow for easier handling of slowly changing dimensions if a user/store moves to a new address.


Can I log which stored procedure issued a delete statement?

You need to add this implementation in your code base . I donot know If there is any other way in which Sybase itself keeps the track of it .

You can add a column in your table which can keep some identifier to the specific stored procedures and keep the track of the same in your trigger


Manually increase PostgreSQL's table size limit of 32 TB?

Just use a different setting for the block size:

  --with-blocksize=BLOCKSIZE

  The default, 8 kilobytes, is suitable for most situations; but other
  values may be useful in special cases. The value must be a power of 2
  between 1 and 32 (kilobytes).

Using 32 kilobytes, your table has a maximum size of 128TB.


PHP and mySQL - Binding a parameter in an if statement

I'm more mysql than php but are you binding the $guestid php variable twice?  You need to supply a variable for each ? placeholder in prepared statements and presumably so in mysqli.  In a native prepared statement on the server, the ? is legal within an IF() but then again so is ? = ?, which apparently mysqli doesn't allow.

But also, you could rewrite the query:

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that i.guestId has to contain the guestId you're interested in, because you've specified it in the where clause so we should be able to compare it this way.

However, there seem to be other issues with your query.

LEFT JOIN rsvpList AS r ON r.eventId = e._id

It seems like this should actually be:

LEFT JOIN rsvpList AS r ON r.eventId = e._id AND r.guestId = i.guestId

...because otherwise you're joining all of the rsvpList entries for that event, not just those for the guest in question.  You may have tried to filter for this in the WHERE clause at some point, which doesn't work if they haven't rsvp'ed.

This change also illustrates that the first comparison in your IF() is actually unnecessary.

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that r.eventId will match e._id OR will be null if no RSVP, so we don't need to test it, because r.guestId will be what we want or null if no rsvp, so this becomes simply:

r.guestId IS NOT NULL as `RSVP-ed`

the truthiness of the assertion that r.guestId is not null in the joined-up result set is returned as a 1 or 0, and r.guestId will be null with no rsvp because there's no matching row in rsvpList for that guestId.

An equivalent and slightly more intuitive (but redundant) expression of that would be

IF(r.guestId IS NOT NULL,1,0) as `RSVP-ed`


Where to install Oracle client software on SharePoint 2010 Server

I do not know much about Sharepoint but in usual cases Oracle client has to be installed into server where application which connects to Oracle is located. So if your application will be running on Application server you should install Oracle client here.
Just for the information you can also try Instant Client. It uses less disk space and you do not have to install it. Just unpack where you prefer and point TNS_ADMIN environment variable to the directory where you unpacked it.
You can find variables by right clicking Computer icon then select Properties, Advanced System Settings, Environment Variables. And then add TNS_ADMIN to system variables.


Standby logshipping fails when user is doing read operations on the database

When you use secondary database for query processing, you have to check

  database users disconnected when transaction log backups are being restored.


Is there any way to recover lost table data on sql server without backups?

You may actually be able to do a restore.  Grant Fritchey talked about it here.

Basically if you go to the portal and look at the screen for the database there is a restore option.  Once you select it it will give you the oldest restore point available.  You can't overwrite your database but you can restore it to an alternate location.  It looks like you can only do this threw the GUI.  Once you have your alternate location restored you can get your data.

Since you can't do cross database queries you may have to find a third party software or download the data then upload it into the correct DB.

(There was something about being able to do cross database queries if you are in an elastic pool but I've never done that myself.)


Sum after you did the union (hope DB2 does it this way):

SELECT  BRAND, BRAND_GROUP, SUB_BRAND, SUM(INCOME) AS TOTAL_INCOME
FROM    (SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME
         FROM   "tema".MMT
         WHERE  BRAND_GROUP IS NULL
         AND    SUB_BRAND IS NULL
         UNION ALL
         SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME
         FROM   "tema".BGT
         WHERE  BRAND_GROUP IS NULL
         AND    SUB_BRAND IS NULL) AS T
GROUP BY BRAND, BRAND_GROUP, SUB_BRAND;

Just a question. Why do you need the BRAND_GROUP and SUB_BRAND (since they are both NULL)?


Cant start mysql and cant find mysql.sock

Checks:

Is the server actually running locally?
Grep for mysql.sock in my.cnf and provide results
Run: sudo /usr/sbin/lsof  | grep mysql.sock
Check (i.e. grep) mysqld err logs to see if its remarking about mysql.sock or any other startup issues.
Check permissions on the MySQL directories, i.e. /var/lib/mysql/ should be read/writable by the mysql group and sufficient permissions for users to reach the socket file.
How are you starting the mysqld process? The error looks more like a client connection issue?

While you can re-install solutions I have used in the past include:

Touch the socket file and make sure it is owned by the mysql group and user.

ls -l /tmp/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Jun 15 15:08 /tmp/mysql.sock

Check the my.cnf for the setting for where the socket is.

You can use the following steps to re-create the socket. Just change the name to reflect the expected name of the socket as given by my.cnf under the [mysqld] and [client] sections.

sudo touch /tmp/mysql.sock
sudo chown mysql:mysql /tmp/mysql.sock
sudo chmod 0777 /tmp/mysql.sock

Starting mysqld as a service via systemctl mysql start etc.
Make sure you are using sudo etc. as most command require root level privileges to work correctly.


Log shipping to fix unintended deletion?

Because the database at the DR site is in read_only mode, you can export the data from the DR table where the deletes occurred on the primary and 'repopulate' the primary server for just that one table using the data exported from the DR site.  This would be especially useful if the database is extremely large, because you would not have to restore the entire database to get just that one table recovered.
This, of course, assumes the log records containing the deletes have not been applied to the DR database.


Using case and group by in select statement not working

Look at this demo code :

125342', 'Some Fake Name', 1343632, GETDATE(), 3, 'FG-PD-TJ-PQ-20.0K-FORN'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 11, 'FG-PD-TJ-PQ-2.25K-FORN'),
('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 2, 'FG-PD-TJ-PQ-2.25K-FORN'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 7, 'FG-PD-TJ-PQ-2.25K-FORN'),
('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 2, 'Other'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 3, 'Other')

select SOPNUMBE, CUSTNAME, CUSTNBR, DOCDATE,
SUM(QUANTITY) "TotalOnInvoce",
sum(Case when ITEMNBR='FG-PD-TJ-PQ-KILOM-FORN' then QUANTITY*30
when ITEMNBR='FG-PD-TJ-PQ-20.0K-FORN' then QUANTITY*20
when ITEMNBR='FG-PD-TJ-PQ-2.25K-FORN' then QUANTITY *2.5
end) as "TotalShippingWeight"
from #demo
group by SOPNUMBE, CUSTNAME, CUSTNBR, DOCDATE

drop table #demo

I hope this will help you.


Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far?

For obvious reasons (laziness) I used the example for creating the table and index from this post:

https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/

create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue));
insert into muse with g as (select * from dual connect by level <= 1000) select rownum, mod(rownum,100000), 'DAVID BOWIE' from g,g,g where rownum <=  3000000;
commit;
create index muse_id_i on muse(id);
create index muse_code_i on muse(code) global partition by range(code)(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

Next drop a partition:

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

Then analyze the index:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         3000000     1000000

Orphaned entries are presented here as deleted entries.

Here comes my addition. Start the coalesce cleanup manually, then interrupt it in a few seconds (Ctrl-C), then analyze the index again:

SQL> alter index muse_id_i coalesce cleanup;
^C
alter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2723063      723063

As you can see, even with interrupting the command, some of the entries were cleaned up. Now do it again:

SQL> alter index muse_id_i coalesce cleanup;
^Calter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2532434      532434

Next create an index then continue:

SQL> create index muse_name_i on muse(name) local;

Index created.

SQL> alter index muse_id_i coalesce cleanup;
^C
alter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2151894      151894

Finally:

SQL> alter index muse_id_i coalesce cleanup;

Index altered.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2000000           0

SQL>

So no, progess was not lost.


SQL Server : low disk space impacts CPU?

NTFS is susceptible to low disk space. When you fall below 10% file system drivers may start creating various bottlenecks (not only with CPU).


How to search hyphenated words in PostgreSQL full text search?

The key word here is phrase search, introduced with Postgres 9.6.
Use the tsquery FOLLOWED BY operator <-> or one of the related <N> operators. Or better yet, use the function phraseto_tsquery() to generate your tsquery.
Quoting the manual, it ...

produces tsquery that searches for a phrase, ignoring punctuation

And:

phraseto_tsquery behaves much like plainto_tsquery, except that it
inserts the <-> (FOLLOWED BY) operator between surviving words instead
of the & (AND) operator. Also, stop words are not simply discarded,
but are accounted for by inserting <N> operators rather than <->
operators. This function is useful when searching for exact lexeme
sequences, since the FOLLOWED BY operators check lexeme order not just
the presence of all the lexemes.

Your query would work like this:
select id
     , ts_headline(content, phraseto_tsquery('english', 'good-morning')
                          , 'HighlightAll=true MaxFragments=100 FragmentDelimiter=$')
from   tbl
where  ts_content @@ phraseto_tsquery('english','good-morning');

phraseto_tsquery('english', 'good-morning') generates this tsquery:
'good-morn' <-> 'good' <-> 'morn'

Since "good-morning" is identified as asciihword (hyphenated ASCII word), the stemmed complete word is added before the components. The manual:

It is possible for the parser to produce overlapping tokens from the
same piece of text. As an example, a hyphenated word will be reported
both as the entire word and as each component: (followed by an example)

to_tsvector() basically does the same on the other end, so everything matches up. This allows for fine-grained options with hyphenated words. The above only finds "good-morning" with a hyphen (or variants stemming to the same). To find all strings with "good" followed by "morn" (or variants stemming to the same) use phraseto_tsquery('english','good morning') generating this tsquery: 'good' <-> 'morn'
OTOH, you can enforce exact matches by adding another filter like:
...
AND content ~* 'good-morning'  -- case insensitive regexp match

Or:
...
AND content ILIKE '%good-morning%'

Seems a bit redundant to the human eye, but this way you get fast full text index support and exact matches.
The latter is mostly equivalent, but different (fewer) characters have special meaning in the LIKE pattern and might need escaping. Related:

PostgreSQL: Regular Expression escape function
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Example to demonstrate the operator <N>:
phraseto_tsquery('english', 'Juliet and the Licks') generates this tsquery:
'juliet' <3> 'lick'

<3> meaning that lick must be the third lexeme after juliet.


Group by reference, count distinct numbers in each group and compare that to the total count of distinct numbers in the table:

SELECT
  Reference
FROM
  dbo.atable
GROUP BY
  Reference
HAVING
  COUNT(DISTINCT Number) = (SELECT COUNT(DISTINCT Number) FROM dbo.atable)
;