Joining two subqueries with union all and join leads to suboptimal execution plan I've faced the similar problem on my production environment, but I managed to reproduce this behavior on Northwind DB.

Consider the following query:

USE NORTHWND;
DECLARE @ids TABLE ( Id NCHAR(50) );
INSERT  INTO @ids
VALUES  ( N'AROUT' ),
        ( N'ALFKI' );

SELECT  *
FROM    ( SELECT    c.CustomerID
          FROM      dbo.Customers c
          WHERE     c.CustomerID IN ( SELECT    Id
                                      FROM      @ids )
          UNION ALL
          SELECT    '0'
        ) t1
JOIN    ( SELECT    o.CustomerID
          FROM      dbo.Orders o
          --LEFT JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
          UNION ALL
          SELECT    '0'
        ) t2 ON t2.CustomerID = t1.CustomerID
OPTION  ( RECOMPILE );

It has pretty good query plan:

Now when I uncomment line with LEFT JOIN, the plan becomes not as good (seems like query processor can't push the CustomerID filter to the data access operators):

When I add FORCESEEK hint on dbo.Orders, query processor can't produce a query plan.
On the other hand, when I remove one of the UNION ALL, the query plan becomes as good as the first one.

Is that expected behavior? Why does the query processor not put filter by CustomerID before the join operator?

I think that SQL Server simply does not have the appropriate optimization rules to yield the seek-into-Orders query that you are looking for in the case of a UNION ALL on both sides of the query. Such a query plan is theoretically possible, but the query optimizer is not capable of producing it for your query.

In order to reach this conclusion, I compared the full original query (including the LEFT JOIN) to an alternate formulation of the query that yields a much lower estimated cost (0.042 vs. 0.085). So SQL Server would be likely to choose this lower cost alternative if it were able to explore this plan shape for your query.

The original query plan

Here is the original plan, with an estimated cost of 0.085.

The alternate query plan

This query is semantically equivalent to the original query but has an estimated cost of 0.042, about half the cost. It is formed by promoting the second UNION ALL up to the top-level of the query. This requires us to reference t1 (the set of customers) twice, but even so yields a plan with half the cost by allowing seeks into the Orders and [Order Details] tables.

The alternate query

Here is the full query you can use to play around with this approach:

DECLARE @ids TABLE ( Id NCHAR(50) );
INSERT  INTO @ids
VALUES  ( N'AROUT' ),
        ( N'ALFKI' );

-- Define the original t1 as a CTE
WITH customers AS (
    SELECT    c.CustomerID
    FROM      dbo.Customers c
    WHERE     c.CustomerID IN ( SELECT    Id
                                FROM      @ids )
    UNION ALL
    SELECT    '0'
)
-- Join t1 to the top half of the original UNION ALL
SELECT  *
FROM Customers t1
JOIN ( SELECT    o.CustomerID
          FROM      dbo.Orders o
          LEFT JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
        ) t2 ON t2.CustomerID = t1.CustomerID
-- And then join it again to the bottom half of the original UNION ALL
UNION ALL
SELECT  *
FROM Customers t1
JOIN ( SELECT    '0' AS CustomerId ) t2 ON t2.CustomerID = t1.CustomerID
OPTION  ( RECOMPILE);

Takeaways / caveats

In general, I have found that Concatenation operators are good at getting in the way of the query optimizer and preventing it from yielding an optimal plan. A couple examples include this Connect issue where UNION ALL prevents optimized bitmap filters as well as repeated observations that Concatenation operators, particularly if nested, fool the SQL 2012 and earlier Cardinality Estimator and may yield sub-optimal plans due to poor cardinality estimates. Obviously UNION ALL is a very useful tool, but it's worth being aware that it does occasionally limit the query optimizer's ability to optimize your query.
Actually splitting the query into two separate units in this way might or might not make sense on your real use case, but it could be worth a try. (This particular query benefits from the fact that one of the two chunks can be simplified since it contains only the dummy SELECT '0'.)
The data sets involved on Northwind are so small that it's difficult to draw conclusions, so testing with your real world data set will be important.


Insert/ Update Scalability (more records, continually longer lookups --- solution?) posted this before but never received a clear answer.

I'm working on an ETL project and am basically feeding JSON records of "tickets" into a MySQL database. (actually come to think of it, may be migrating to SQL server, but I'm not sure that will be a huge difference).

These JSON records are the "current data" for each ticket.

So it will say, for example:

Ticket_Id: 1
Ticket_Subject: Hi
Ticket_Issue: Greeting
Ticket_Status: Open

In the future I might get a record like:

Ticket_Id: 1
Ticket_Subject: Hi
Ticket_Issue: Greeting
Ticket_Status: Closed

These update records are stored in the order in which they are made ... so it's pretty easy to sweep through each day, parse the JSON, and perform an Insert/ Update into a SQL database/ table with Ticket records.

Here's the issue. There's currently 100k tickets in the database. In 6 months there may be 500k tickets.

The ETL process is already fairly slow, and I need to work on optimizing each step. For now, I want to focus on the SQL Insert/ Update.

Currently it's lookup against 100k rows ... soon it will be looking up against 500k ... eventually 2 million ... you get the idea. This is NOT scalable currently.

Luckily, I know that these tickets become "archived"/ uneditable after 30 days of inactivity. They become "closed" permanently and will never be changed or updated again.

Thus, there may be a logical solution that somehow transfers, locks, moves, partitions, ignores .... I simply don't know ... these "closed/ archived" tickets ... so I only have to perform daily lookups against 30k active tickets any given day. Not a million.

This seems like an extremely common issue (virtually ANY large insert/ update conundrum) .... but I have not found many simple ideas online.

I'm wondering what is a simple, maintainable solution to this problem. Even if the "simplest" is sadly two different tables or what have you. I have no problem doing a "union" select statements --- in actuality the 'select' query times are already super fast and not a problem. The problem is the insert/ update/ lookup/ write times.

Any ideas?

Parse the JSON when the record in inserted, not later.  (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.)

When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL.  Be conservative.  Don't extract many fields.  Leave the JSON as is in case you need the details later.  It would even be good to compress it and store it into a BLOB -- to save space, hence decrease I/O.

What queries will you be doing on the data?  Studying them is critical to the design of a Data Warehouse.

Properly indexed (etc), a table that grows by a factor of 10 may be only 10% slower.  Don't panic, but do look at the queries.  I have seen billion-row tables that hum along nicely.

It sounds like you are inserting only about 2 rows per minute; is that correct?  100 per second is when it starts to get challenging.

Let's worry about the "archiving" after we have covered some of the issues I mentioned.


How to access another Connect server table in my Local server in SQLyog 7.01? In same server name using same database

select * from area;(Working Good)

Same Servername but Different Database

select * from mysql.event(Working Good)

Now i am connection TWO Different server i want to access one Remote server database table into local server database table in my sql using SQL YOG 7.01
Examples my server like this

root@192.168.12.35
root@192.168.12.89

If what you are asking is how to access a remote MySQL table from another MySQL server, that can be achieved with the FEDERATED engine (disabled by default in mysql 5.5) os with the CONNECT engine (only available on MariaDB 10).

Here you have the documentation to create such tables in both cases:

Creating a FEDERATED table using CONNECTION. You need to enable the FEDERATED engine beforehand
Connect table types - MySQL/MariaDB table

I hope you are not thinking about using it for nothing too serious, as the performance is usually not too good.


Recursion on deletion trigger that calls a function I have the following schema:

CREATE FUNCTION myfunc() Returns trigger as $myfunc$
    DECLARE
        someVal integer;
    BEGIN
        SELECT Count(*) into someVal
        FROM MyTable
        WHERE someAttribute = OLD.someAttribute;
        IF someVal = 100 then

            -- Recursion, don't do this
            -- DELETE FROM MyTable
            -- WHERE someAttribute = OLD.someAttribute;

            -- delete from MyTable2
            DELETE FROM MyTable2
            WHERE someAttribute = OLD.someAttribute;
        END IF;
        RETURN OLD;
    END;
$myfunc$ LANGUAGE plpgsql;

I have oversimplified what I did in my code, so let me clarify it.

I am registering this function with the following code:

CREATE TRIGGER myfunc BEFORE DELETE ON MyTable
FOR EACH ROW EXECUTE PROCEDURE myfunc();

So, when someone deletes a value in my relationship and if some condition occurs, I want to delete other values as well. Think about this: I have a hundred 10's in my database. If the user deletes one of them, it will be 99 and I want to delete all 99 of them because less than 100 attributes are no use to me (don't say why I have said I have oversimplified this).

So, If I delete from the same table inside the IF condition, I am encountering infinite loop, and the program stops when the stack overflows. What should I do? I need to delete those values from MyTable and MyTable2.

Add the following at the beginning of your function:

IF pg_trigger_depth() > 1 THEN
   RETURN OLD;
END IF;

Then recursion will be cut short after the first iteration.

To avoid the error that you get when the same row is deleted twice in the same statement, explicitly exclude it:

DELETE ...
WHERE ...
  AND OLD.ctid <> ctid


Strange error in trace file for NSAn standby process One of the Oracle databases I help manage is the primary in a standby configuration, with two standby databases. I started finding repeated entries in the alert log on the primary for a failure to resolve the TNS connect identifier for log_archive_dest_3 and referencing the trace file for NSA3. That trace file had an error message I'd never seen before, and I can't find any information on it for the life of me: "LOG_ARCHIVE_DEST_2 depends upon me, but my destination index 2 does not match its destination index 1." This showed up several times in the trace file.

I was able to verify that all standbys are up-to-date with the primary and are responsive to tnsping. Can anyone shed some light on this error?

At first glance, in MOS there's a related known bug with that message in 11.2.0.1 on Solaris (Bug 10331312)  and 11.2.0.2 on Linux x86-64 (Bug 14016842 , this one refilled for review) both referenced by Doc Id 1308804.1.

However it doesn't provides any source of cause. As workaround they recommend to disable the log_archive_trace parameter to = 0 (default), as those messages can be ignored.


Creating a User to access multiple databases Is it possible in SQL server to create a User without a Login that can access multiple databases?

I am creating a user setting up a service broker queue and the receiver of the message needs to be able to collect data from several databases.

I'm not certain about the service broker que but it is possible to setup a user without a login.  This user will not be able to authenticate but you can still assign the user rights.  The examples below are from the microsoft article for createing users http://msdn.microsoft.com/en-us/library/ms173463.aspx

D. Creating and using a user without a login

The following example creates a database user CustomApp that does not map to a SQL Server login. The example then grants a user adventure-works\tengiz0 permission to impersonate the CustomApp user.

USE AdventureWorks2012 ;
CREATE USER CustomApp WITHOUT LOGIN ;
GRANT IMPERSONATE ON USER::CustomApp TO [adventure-works\tengiz0] ;
GO

To use the CustomApp credentials, the user adventure-works\tengiz0 executes the following statement.

EXECUTE AS USER = 'CustomApp' ;
GO

To revert back to the adventure-works\tengiz0 credentials, the user executes the following statement.

REVERT ;
GO


DB2 Express on Windows 7 - Disable security? I'm trying to install DB2 Express-C on my PC (Windows 7 pro), but no matter how I've installed or configured it, I can't grant the ability to create databases to my windows domain account. No matter what I try, I get the errorSQL1092N my-windows-id does not have the authority to perform the requested command when I try to create a database.

My PC is centrally administered by my company, and I can't (permanently) create local acccounts on the PC. My domain account is in the local administrators group, and I've added my account to the DB2ADMNS group created during the DB2 install, but DB2 doesn't seem to be able to see that. I've spent about 12 hours going through the DB2 instructions and trying different things, but no matter what I've tried, DB2 won't allow my domain account to create databases.

I'm the only user of the PC and I'm not really concerned about security. I just need to get DB2 working in order to run some software that requires it. Is it possible to open up DB2 security and allow anyone to do anything? I tried changing the FED_NOAUTH config setting, but that didn't seem to have the desired effect.

The short answer to this problem is that you need to set the DB2 registry variable DB2_GRP_LOOKUP to local.

As a local administrator on your PC (i.e. not using your domain account), start a DB2 Command Window and issue the following:

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

The reason this occurs has to do with how DB2 enumerates which groups a user belongs to upon successful authentication.  In a Windows Domain environment, the default group enumeration behavior is to ask the system that authenticated the user:  If a user is authenticated by the local machine, DB2 asks the local machine what groups belongs to; if the user is authenticated by the domain, DB2 will ask the domain what groups the user belongs to - and it will not ask the local machine.

So, when you connect to DB2 using your domain ID, DB2 asks the domain controller what groups you belong to, so it does not see that your ID belongs to the Administrators group on your local PC.

When you set DB2_GRP_LOOKUP=local, this modifies this behavior:  It tells DB2 to always enumerate groups from the local machine, regardless of whether the user was authenticated by the domain or the local machine.

After doing this, DB2 will see that your Domain ID belongs to the local Administrators group, and you'll end up with full SYSADM privileges (and the ability to create a database).


Table design for ticket sales I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like eventId, name, price etc.) except for a serial number, which must be unique for each ticket.

What are the best practices for this situation? Should I treat each ticket as a record in a Ticket table, or should I have a TicketBatch table where I store the ticket information along with the amount available and implement the serial number logic somewhere else?

My concern is that each event can potentially have thousands of tickets for sale, so the one-ticket-per-row solution may not scale well and have slow queries over time. On the other hand, if I go the TicketBatch route, I may have problems keeping track of which tickets (identified by their serial number) were sold.

I can see no strong reason to avoid having a Ticket table that stores each serial number on a separate row, assuming that makes most sense for the application that will use this database. You should still normalise the overall design to avoid repeated data in this table.

"Thousands of rows" is pretty tiny for a relational database: you should easily be able to store all the tickets for thousands of events (~millions of rows) without any performance problems, provided you have clustered and indexed the table appropriately. This of course depends on your server hardware, and on how many events you expect to be servicing.

I assume events are short lived, and the record of tickets sold would not usually be needed after the event has happened. If so, you could easily have a TicketHistory table, and archive off rows to there once the event is over so the main table remains small and performant.


Query difference with where condition I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this:

select A.id from A left join B on B.id_A = A.id where B.id_A is null;

And it works perfectly well. However now I need to do the same query with an additional conditional for the matching, meaning I need to find the ids of all rows in a table A without a matching row on table B whose column c has the value x. If I try something like:

select A.id from A left join B on B.id_A = A.id where B.id_A is null and B.c = x;

It obviously gives me an empty result set.

So far, the only way I figured to do that is using a subquery for B with a "not exists" clause:

select A.id from A where not exists (select id from B where B.id_A = A.id and B.c = x);

Any ideas on how to do this with a join without using subqueries?

SELECT A.id
  FROM A
  LEFT JOIN B ON B.id_A = A.id AND B.c = x
 WHERE B.id_A is null;

Moving the test of B.c into the join condition and out of the where clause causes it to be used to only eliminate non-matching B-rows from consideration for the left join rather than eliminating rows from the result.


MYSQL will not start I am getting the errors below when trying to start MYSQL.
I've deleted both the ./ib_logfile0 and ./ib_logfile1 files and still no success.  I'm assuming is has something to do with not being able to write to the temp file but I don't know the location of that directory.

140527 15:56:04 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140527 15:56:04 [Note] Plugin 'FEDERATED' is disabled.
140527 15:56:04 InnoDB: The InnoDB memory heap is disabled
140527 15:56:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140527 15:56:04 InnoDB: Compressed tables use zlib 1.2.3.4
/usr/sbin/mysqld: Can't create/write to file '/tmp/ibmZXLyt' (Errcode: 13)
140527 15:56:04  InnoDB: Error: unable to create temporary file; errno: 13
140527 15:56:04 [ERROR] Plugin 'InnoDB' init function returned error.
140527 15:56:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
/usr/sbin/mysqld: File '/var/log/mysql_slow.log' not found (Errcode: 13)
140527 15:56:04 [ERROR] Could not use /var/log/mysql_slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
140527 15:56:04 [ERROR] Unknown/unsupported storage engine: InnoDB
140527 15:56:04 [ERROR] Aborting

Errcode: 13 means Access denied.  Assuming MySQL is trying to create a file in /tmp, which is 777 I guess this is incorrect configuration of either selinux or apparmor (on Debian systems).

Try to disable selinux

    echo 0 >/selinux/enforce

If it doesn't help, stop apparmor (if you run Debian or Ubuntu)

    # /etc/init.d/apparmor stop
    # /etc/init.d/apparmor teardown


In Oracle, how to globally enable parallel execution? In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?

Simple answer: Yes. Unfortunately it is not quite a simple as that, as if you want to enable parallelism, then you are obviously concerned about performance, and it is extremely tunable.

Start with alter system set paralllel_automatic_tuning=true scope=spfile; then restart. This will have Oracle do what it thinks is best. There are many other parameters to set (e.g. parallel_execution_message_size) and you will need to experiment to find the best. Be careful with this; it is easy when you start out with parallel query to get carried away in benchmarking then discover in the "real world" a few queries fly but the overall throughput of the system actually decreases as some sessions are starved of CPU or the system is forced into swap. The documentation warns:

  When concurrent users have too many
  query server processes, memory
  contention (paging), I/O contention,
  or excessive context switching can
  occur. This contention can reduce
  system throughput to a level lower
  than if parallel execution were not
  used.

So I strongly advise putting some limits in place, e.g. with resource consumer groups.

Also for any table you can do ALTER TABLE table_name PARALLEL (DEGREE x); where x is a number, 4 or 8 might be good places to start.


sp_spaceused reveals 0 rows but 72 KB of reserved space When I run sp_spaceused dummybizo I get the following results:

name        rows    reserved    data    index_size  unused
dummybizo   0       72 KB       8 KB    8 KB        56 KB

I am expecting the following results:

name        rows    reserved    data    index_size  unused
dummybizo   0       0 KB        0 KB    0 KB        0 KB

What is causing the reserved memory?

I have already tried to force a ghost record clean up with alter table dummybizo rebuild; but it doesn't change the results. I've also tried truncate table dummybizo but it doesn't do anything to these stats. I also note that select * from dummybizo with (nolock) doesn't reveal any hidden records.

What should I try next? I would like to set up the table such that the stats read 0   0 KB   0 KB   0 KB   0 KB

Likely, there is an IAM page and extent reserved for this table. TRUNCATE TABLE will remove these. When all data is just deleted, a bare minimum structure will still exist for the table.

Have a demo!

--Setup
DROP TABLE IF EXISTS dbo.welcometable
CREATE TABLE dbo.welcometable
(ID INT)

EXEC sp_spaceused 'dbo.welcometable'
--all 0s

-----------------------------------------------------
--Let's see what spaceused looks like when there's data
INSERT dbo.welcometable
VALUES (1)

EXEC sp_spaceused 'dbo.welcometable'
--72KB reserved, 8KB data, 8KB index, 56KB unused

-----------------------------------------------------
--Now what if we delete?
DELETE FROM dbo.welcometable

EXEC sp_spaceused 'dbo.welcometable'
--72KB reserved, 8KB data, 8KB index, 56KB unused

-----------------------------------------------------
--What if we truncate?
TRUNCATE TABLE dbo.welcometable

EXEC sp_spaceused 'dbo.welcometable'
--all 0s
--success!

If you want to actually see the pages and where they are, use this:

SELECT page_type_desc, allocated_page_page_id, is_allocated
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.welcometable'),NULL,NULL,'DETAILED')

This shows one IAM page and one reserved extent for me, even when all rows have been deleted.


Functioning of Oracle's Server result Cache I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] :

  PL/SQL function code can include a request to cache its results. Upon invocation of
  this function, the system checks the cache. If the cache contains the result from a
  previous function call with the same parameter values, then the system returns the
  cached result to the invoker and does not reexecute the function body. If the cache
  does not contain the result, then the system executes the function body and adds the
  result (for these parameter values) to the cache before returning control to the invoker

My doubt:

After a PL/SQL function is executed, its result set is stored in cache.
Then a DDL statement changes the table/index/anything that the function uses...

If I run the same PL/SQL function now, will I get the same result or updated result?

cached results are invalidated and regenerated when transactions occur
  against the underlying data

I am fairly confident the same would be true for DDL changes but did you mean DML? The long and short is that Oracle is not going to allow an inconsistent result.


Change recovery mode and enforce a transaction log backup? I am going to do a special data deletion operation - once and during a maintenance with no user access (Sql 2008 r2).

The result is a large deletion of millions of blob data from a table by re-creating the whole table.

Reasons:

Remove no more required rows including the (blob) data
Improve performance
Reduce data file size (Regarding 3.: shrinking is not yet a part of the maintenance procedure, maybe later on...)

I plan to change the recovery model to bulk_logged to minimize logging and back to full after the maintenance.

The main operation performed is a SELECT INTO statement followed by creation of (non + clustered) indexes and constraints, defaults + truncating the original table + renaming of the new table.

I have read the suggestions at Considerations for Switching from the Full or Bulk-Logged Recovery Model.

There we are advised to run a Log Backup before and after the process. Unfortunately the regular (15 Min interval) log backup is done by third party software (backupexec). I have no control about it. When I tried to do a log backup using T-Sql I failed because there is

  no device present

I checked sys.backup_devices => nothing.

I want to do it by script because I have to do it on several different servers and don't want to do it in a user interface.

So, questions:

Would you agree to the procedure in general?
How can I enforce the Log Backup?
Is the log backup enough or is a log shrink required as well at any time?

Yes in general that is a good approach.
As Shanky commented if you take a manual backup then the chain within BackupExec will be broken.

  If you have the ability to kick all the users on the server and can wait for the transaction log to be backed up by BackupExec then you will find it easier to start the work without involving another team.  Otherwise you will want to get the backup team to take the log backups before and after you have shrunk the data.

Only Shrink the database if you really have to reclaim the disk space otherwise leave it as it is. Brent Ozar has a lovely rant on shrinking, especially when scheduling it here and below is an excerpt from Paul S. Randal blog. The long and short of it is that it takes processing power to grow and shrink a log, this can be an unnecessary burden on an SQL Server.

  Shrinking the log is necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly’s excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be part of any regular maintenance you perform.


Does this design violate a normal form I came across this answer on Stack Overflow. The question itself isn't really relevant. The answer outlined a database design that I will paraphrase here:

Consider the relations Organization, Account, and Invoice. The Organization key is OrgName. The Account key is OrgName, AccountID. The Invoice key is OrgName, AccountID, and InvoiceNumber. As suggested by the keys, an organization has many accounts and an account has many invoices. (Note that AccountIDs can be reused across organizations and InvoiceNumbers can be reused across Organizations and Accounts - hence the need to have two and three attribute keys. Clarified further, AccountID and InvoiceNumber are not individually unique within their respective tables.)

It strikes me that if you need to move an account to another organization, that would necessitate an update not only in the Account relation, but also in the Invoice relation. This "redundancy" leads me to believe that a normal form is violated in this design, though I've been unable to identify which one.

Thus my questions is: Is a normal form violated in this design? If so, which one and why?

Normalisation talks about the atomicity of attributes within a relation and the dependency of attributes on candidate keys. Since there is no mention of non-key attributes in the question it is not possible to comment on the normalisation or otherwise of this design.

That said, I would like to comment on the semantics of this design with regards to the business requirement to move accounts between organisations. In this design you cannot move an account to another organisation. Primary keys, by definition, distinguish one item from another. The account's key includes the organisation id. Therefore an account for one organisation is a different thing to an account with a different organisation, even if the two accounts have the same values in all attributes.

By extension, moving an account would not cause Invoice to be updated. Rather, new rows would be created in Invoice, with values copied from the existing (Organisation, Account, Invoice) rows.

The old Invoice rows could be deleted or not, it doesn't matter to the argument. Although an insert followed by a delete looks to an outside observer like an update, we are talking about primary keys here, which define the existence and distinguishability of objects. If I were to replace your Ford by a Ferrari while your eyes were closed would you, on opening them, think "Wow, my car has spontaneously morphed!" No, one thing has gone and another, functionally similar thing arrived.

If the problem domain requires accounts to be movable yet still recognised as one continuing object you could

A) key the account off account id solely, and have organisation id as a foreign key called "current owner"

B) introduce a new table called "account transfer" with attributes
    From owner
    From account
    To owner
    To account
    Start date
    End date


Does an ad hoc, full backup, using Oracle RMAN, flag files, so they are not backed up by the regular backup? If I run a simple RMAN full backup, ad hoc, outside of the regular backup scheduled, does that flag files so when the regular backup runs, that regular backup won't get some of the log files?

For example,

RMAN> Backup Database;

Overview of Backup Optimization

  When you enable backup optimization, the BACKUP command skips backing
  up files when the identical file has been backed up to the specified
  device type.

  ...

  RMAN uses backup optimization when the following conditions are true:

  The CONFIGURE BACKUP OPTIMIZATION ON command has been run to enable backup optimization.
  You run BACKUP DATABASE, BACKUP ARCHIVELOG with ALL or LIKE options, or BACKUP BACKUPSET ALL, BACKUP RECOVERY AREA, BACKUP RECOVERY FILES,
  or BACKUP DATAFILECOPY.
  Only one type of channel is allocated, do not mix disk and SBT channels in the same backup command.

With backup optimization, the database will not backup the same, identical file more than once to the same device type.

Other than that, the files will be backed up by the regular backup as well.


SQL Server Corruption & Replication I have a situation where storage was ripped out from under SQL Server.  After running BBCC CheckDB I have received an "Extent (1:269886) in database ID 34 is allocated by more than one allocation object" message.

The research I have done basically tells me recover from backup.  However, this table is a subscriber in transactional replication.  Replication is set up such that I have added individual articles in the past in order to initialize in stages.  Am I correct that I can do the following:

Remove the article from replication
Add the article back
Let the agent job take a new snapshot
Let that snapshot apply

Do I need to truncate or drop the damaged table first?

SQL  Server 2008R2 on Windows Server 2008

Your steps are correct and you do not need to truncate or drop as you will be generating new snapshot for that one article.

Few things to check:

When you drop article, also drop subscription.
Make sure immediate_sync property of your publication is set to
false so you do not end up with a full snapshot (of all articles).

These two threads has details about where things might go wrong.

Add article to transactional publication without generating new snapshot
http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html


What are your use-cases for HandlerSocket? There's a bit of hype around: HandlerSocket

It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in.

My question is, what production use-cases make sense to bypasses the libmysqld layer of MySQL to utilize the HandlerSocket plug-in? Are you deploying it into production?

I find it interesting that Percona says that HandlerSocket is not that popular. In fact, it has bugs when writing operations interferes with open HANDLER structures.

What I also find appalling is that the concept of the HandlerSocket (known back then as HANDLER goes way back to MySQL 4.0.3 for both MyISAM and InnoDB. There were only provisions for reading at that time.

The basic usage of HANDLER is also in the book MySQL Reference Manual : Documentation From the Source, Section 6.4.2 Pages 512,513 (I have the book right here next to me)

Question remains : What purpose does the HandlerSocket serve ???

Imagine Facebook engineers trying to read a value in a MyISAM table (Facebook hates MyISAM, BTW) that is heavily trafficked. We all know that MyISAM performs a full table lock for any DML or SELECT performed. What you wanted to do is adjust a single column in a specfic row in that table (via SQL) but you need to know that data right now before SELECT queries lock it down. Do you want to wade through the humongous number of DB Connections hitting the one table you need just to change a single value? You could bypass these waiting SELECTs with no locking using the HANDLER syntax. You can do the same to InnoDB tables and bypass MVCC (MultiVersioning Concurrecy Control) and row-level locks as well.

Only in a high-traffic, high-read environment would you need to takes such a risk, especially if finding out data RIGHT NOW was the highest priority.

If you really want to know what production environments actually use it, I would write Percona directly in the blogs begging for that answer. Since I have never seen coding examples with the HANDLER (today HandlerSocket) do things other than reading, it would only serve the purposes of big MySQL installations like Facebook.

CAVEAT

When you look at the syntax for opening tables, opening indexes and traversing rows, it resembles some programming languages I coded in back in the late 1980's.

I am referring to the languages DBase, Clipper, FoxPro and Visual FoxPro. DBase was the grandfather of all these products. Now, before you start laughing and falling out of your chair, DBASE STILL EXISTS AND SO DOES VISUAL FOXPRO !!! I personally know one person that still codes in FoxPro and Visual FoxPro !!! OK, now you can fall out of your chair, crying in the fetal position. Still don't believe me ??? How about some sample code ???

Enough ranting on old data handling programming languages that resembles hierglyphics !!!


How to avoid multiple joins on the same table (it has 200 columns to hold IDs. Another table has a mapping of IDs to values) I have a table Checklist with the following columns

ChkId, Q1_ID, Comment1, Checked1, Q2_ID, Comment2, Checked2, ...
Q200_ID...

I have a table Questions with the following columns

Q_ID, Prompt

How do I replace Checklist.Q#_ID with the matching Questions.Prompt without joining them up to 200 times? Is there an elegant solution?

Take a look at my answer here to understand why your original design is a very bad idea from a relational database point of view.

Basically, you have "squashed" two entities into one! Your scenario is very similar to that question! You need to normalise your data - this will greatly assist your query formulation and speed!

It appears that there are two entities here (IMHO) a checklist entity and a question entity.

Checklists are identified by a checklist_id (chk_id) and questions are uniquely identifed by a checklist_id and a question_id. Perhaps "quiz" might be a better name? You could have history quizzes, sports quizzes &c. with different chk_id's.

So, in order to tackle the underlying problem, you should create a question table (see SQLFiddle here):

CREATE TABLE question
(
  chk_id INTEGER NOT NULL,
  q_id INTEGER NOT NULL,
  q_comment VARCHAR (255) NOT NULL,
  q_checked BIT NOT NULL,
  CONSTRAINT question_pk PRIMARY KEY (chk_id, q_id)
);

You could implement my strategy in VIEWs if you can't change the underlying table structure - that will be a bit clumsy, but still better than your current scenario!

Sample checklist table:

A sample of the first 3 questions extracted from your checklist table and sample data:

CREATE TABLE checklist -- you have this already!
(
  chk_id INTEGER PRIMARY KEY,
  q1_id  INTEGER NOT NULL,
  q1_comment VARCHAR (255) NOT NULL, -- or whatever size suits.
  q1_checked BIT NOT NULL,

  q2_id INTEGER NOT NULL,
  q2_comment VARCHAR (255),
  q2_checked BIT NOT NULL,

  q3_id INTEGER NOT NULL,
  q3_comment VARCHAR (255),
  q3_checked BIT NOT NULL

  -- &c. 197 times.
);

INSERT INTO checklist
VALUES
(233, 1, 'Good question!', 1, 2, 'Crap', 0, 3, 'Better', 1),
(234, 1, 'An OK question', 0,  2, 'Bleuch...', 1,  3, 'Brilliant!!', 0);

If the "comment" field is the question itself - i.e. "prompt", substitute text of your choice - my comments are opinions about the question... not really sure. Easily modifiable!

You will then need to construct a series of 200 INSERTs like this:

INSERT INTO question
SELECT
  chk_id,
  q1_id,
  q1_comment,
  q1_checked
FROM checklist;

You will have to do this 200 times (q2... q200). Obviously, this will require some work (either manual typing or scripting) to get your data out of your original entity, but it will be worth it in the long run!

Here are the three question entries (by two checklists - history or sport):

SELECT * FROM question;

Result:

chk_id  q_id          q_comment     q_checked
   233     1     Good question!          true
   233     2               Crap         false
   233     3             Better          true
   234     1     An OK question         false
   234     2          Bleuch...          true
   234     3        Brilliant!!         false

Your tables will be much more performant and much easier to manipulate!


Sub-query executes 1 time for a parent query or more? If i apply a static data in where clause is it faster then applying a sub-query. Example:

(The query returns 5 records as a result set)

SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = '2012-08-21';

is faster then :

SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = (SELECT STARTDATE FROM TEST2 LIMIT 1);

or they are same in any case?

Sub-query executes each time a comparison perform by query with each record or only for 1 time?

Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove:

mysql> SELECT COUNT(*) FROM sample_data;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sample_data WHERE id = (SELECT SLEEP(1) );
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (8.00 sec)

Note that the query takes 8 seconds, thus the SLEEP(1) is executed 8 times, once for each row in sample_data.

MySQL 5.6 has implemented subquery materialization which, in theory, will prevent this from happening in many cases.  MariaDB also has a greatly improved optimizer that should prevent this.

In most cases, it is preferable to avoid subqueries in MySQL.


Select next row only if different from current row I have a table called times that looks like so:

id  start_date  breakfast   lunch       snack       dinner
1   2016-10-10  06:30:00    01:30:00    05:00:00    08:00:00
2   2016-10-17  06:30:00    01:30:00    05:00:00    08:00:00
3   2016-10-24  06:30:00    01:30:00    04:45:00    08:00:00
4   2016-10-31  06:30:00    01:30:00    04:45:00    08:00:00
5   2016-11-06  06:00:00    01:00:00    03:30:00    08:00:00
6   2016-11-14  06:10:00    01:00:00    03:30:00    08:00:00
7   2016-11-21  06:20:00    01:00:00    03:30:00    08:00:00
8   2016-11-28  06:25:00    01:00:00    03:30:00    08:00:00

This represents 4 times during the day. So starting on 2016-10-10, the times are 6:30am, 1:30pm, 5pm, 8pm; and starting on 2016-10-24, the snack time changes to 4:45 and all other times remain the same.

Some of the rows are duplicates, with the exception of the start_date, which represents the beginning of the week in this context.

I want to show when the next time change is, ie, select the next row when the time for breakfast, lunch, snack or dinner are different than today's.

I tried using group by like so, but it returns the row with ID 2, when I want it to return the row with ID 3:

SELECT * FROM times
WHERE start_date > '2016-10-16'
order by start_date asc
group by breakfast,lunch,snack,dinner
limit 1

One more clarification, start_date is a DATE type column, the other 4 are TIME type columns. Id is just an int.

You need to join the table to itself. One instance of the table to find the values of the "current" row and the second to look for the first row that the values differ.

Since the date parameter may not exist in the table, we also need a subquery (derived table, named c) to find the previous row that holds the values for the "current" date:

SELECT
        s.*
FROM
        ( SELECT start_date, breakfast, lunch, snack, dinner
          FROM times
          WHERE start_date <= DATE '2016-10-16'
          ORDER BY start_date DESC
          LIMIT 1
        ) AS c        -- "current"
    JOIN
        times AS s    -- "search"
    ON
            s.start_date > c.start_date
        AND
            (s.breakfast, s.lunch, s.snack, s.dinner)
            <>
            (c.breakfast, c.lunch, c.snack, c.dinner)
ORDER BY
        s.start_date ASC
    LIMIT 1 ;

Tested at SQLfiddle.

This part:

        (s.breakfast, s.lunch, s.snack, s.dinner)
        <>
        (c.breakfast, c.lunch, c.snack, c.dinner)

is just a compact way to write:

        (  s.breakfast <> c.breakfast
        OR s.lunch     <> c.lunch
        OR s.snack     <> c.snack
        OR s.dinner    <> c.dinner
        )

I assumed that the four time columns are not nullable. If they are nullable, the above expression would need to be more complicated.


What are the most relevant similarities between T-SQL and MDX? I have used T-SQL (CTEs, windowed functions, recursive CTEs, large data, table-valued functions, APPLY, PIVOT, stored procedures, etc.), but have never queried a Microsoft SSAS cube.

Are there similar concepts in MDX for the following T-SQL concepts?

PIVOT
UNPIVOT
Windowed functions
Recursive Common Table Expressions

Are there any pitfalls for a T-SQL user using MDX?

I am hoping there are some common analogies between T-SQL and MDX, that will help someone good at T-SQL transition to MDX.

It would also be great if there was a guide for common mistakes that are made when coming from T-SQL into MDX.

General notes

First of all, T-SQL and MDX are 2 completely different beasts. SQL is intended to query tabular relational data, while MDX is intended to query multidimensional data.

I'll start with addressing your question about common mistakes. In my opinion the most common mistake is to try and apply SQL knowledge to MDX. You really need to get into the multidimensional mindset and forget about thinking in relational structures.

For example, you don't query columns and rows but you slice dimensions and measures. See for example my answer here.

Pretty much the only thing in MDX that resembles SQL is the fact that you have a SELECT and a FROM and a WHERE clause but MDX doesn't necessarily return rows and columns but returns "axes", of which you can have more than 2.

Addressing some of your points will probably show you why SQL concepts don't map to MDX concepts:

Pivot and unpivot

You pretty much don't do this in MDX. If you look at the MDX select syntax you'll see an example like this (returning in this case a 2 dimensional result):

SELECT
    [Date].[Calendar].[First8Months2003] ON COLUMNS,
    [Product].[Category].Children ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

Say, you want to pivot that resultset, you just swap the axes around:

SELECT
    [Product].[Category].Children ON COLUMNS,
    [Date].[Calendar].[First8Months2003] ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

Bear in mind that the COLUMNS and ROWS axes are just shorthand names, you could also do:

SELECT
    [Product].[Category].Children ON 0,
    [Date].[Calendar].[First8Months2003] ON 1
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

and add a third axis and a fourth axis if you wish to do so (and if your client supports rendering that), so the result isn't a table but rather a multidimensional cellset.

Obviously, pivot and unpivot wouldn't keep up when you have more than 2 axes in your result set.

Recursive CTE's

Since MDX doesn't return "tables", there is no such thing as table expressions. You can have recursion in your calculated measures if that is something you need.

When you encounter the WITH keyword, it's defining Query-Scoped Calculated Members.

Window functions

Window functions also have no use in MDX, since they are used to calculate things that are already calculated in your cube and calculated measures. The OVER clause is pretty much the dimension members you put on your axes.

Take for example a running total. In SQL you would write something along the lines of

SELECT SUM(internet_sales) OVER (ORDER BY date) AS running_total
FROM sales;

However, in MDX you would have a calendar dimension and a sales amount measure, and your MDX would look something like:

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({NULL:[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

Conclusion

As you can see, trying to solve MDX problems by applying T-SQL principles, you will likely run into problems.

If you want to learn MDX, you need to learn MDX as if it were a completely new language and not a new syntax, because that's what it is, a different language and not a SQL dialect.


Powershell and dbo.sysjobsteps.Command field being truncated - not a length issue On the accepted answer to Export SQL Agent jobs with steps and scripts, there is a comment:

  Command is unlikely to get pulled into Excel very nicely. – Aaron Bertrand ♦ Feb 2 '15 at 21:27

This seems very true.

I am trying to pull the dbo.sysjobsteps.Command field into a csv file using Powershell Export-Csv.  The data seems to be truncated as of the first space found in the Command field (has nothing to do with a length limitation).

Do you know why?

Using the script Powershell export query to csv, I was able to run it under Powershell and successfully export the job commands without encountering any truncation.  Are you doing something different?  Here is the script I ran:

$server = "."
$database = "msdb"
$query = "SELECT
    j.Name
    , j.[Description]
    , js.Step_ID
    , js.Step_Name
    , js.Database_Name
    , js.Command
    --, j.*, js.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID"

$extractFile = "C:\Scripts\TestCsv.csv"

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | Export-Csv $extractFile


In what direction SQL Server WHERE clause conditions are checked? I have this query:

SELECT RCodeID as 'ListItemID',
       RCode as 'ListItemName'
FROM RCode
WHERE RCode not like '%,%'
      AND ISNUMERIC(RCode) = 1
      AND CAST(RCode as int) >= CAST(@From as int)
      AND CAST(RCode as int) <= CAST(@To as int)
ORDER BY ListItemName;

So in what order (Right to left/left to right), these conditions are checked by the SQL Server?

Because I am getting those rows which have a , comma too, but I want to exclude them before ISNUMERIC(Rcode) is executed.

Data in the table:

+---------+--------+
| RCodeID | RCode  |
+---------+--------+
| 1       | 342    |
+---------+--------+
| 2       | 232,23 |
+---------+--------+
| 3       | 323xs  |
+---------+--------+

Input: @From=300 and @To=400

Expected Output:

+---------+--------+
| RCodeID | RCode  |
+---------+--------+
| 1       | 342    |
+---------+--------+

UPDATE

I moved the code with RCode not like '%,%' AND ISNUMERIC(RCode) = 1 in a view. The view is showing the correct data. But when I use that view in above query, the cast error for comma-seperated values is coming. If view is filtering that data then why those rows are coming?

I don't think there are any guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query using available information and each invocation of the query may change depending on what the optimizer thinks is the best plan right now.

If you MUST exclude some rows before evaluating other rows, I think you'll have to materialize the first result set (possibly in a temp table) and then run additional queries against that materialized result set.

To get around your CASTing error, you could use TRY_CAST

DROP TABLE IF EXISTS Table1
GO
CREATE TABLE Table1 (
    RcodeID INT
    ,RCode VARCHAR(6)
    );

INSERT INTO Table1 (RcodeID,RCode)
VALUES (1,'342'),(2,'232,23'),(3,'323xs');

DECLARE @FROM INT = 300
DECLARE @TO INT = 400

SELECT RCodeID AS 'ListItemID'
    ,RCode AS 'ListItemName'
FROM table1
WHERE RCode NOT LIKE '%,%'
    AND ISNUMERIC(RCode) = 1
    AND TRY_CAST(RCode as INT) >= CAST(@From AS INT)
    AND TRY_CAST(RCode as INT) <= CAST(@To AS INT)
ORDER BY ListItemName;

| ListItemID | ListItemName |
|------------|--------------|
| 1          | 342          |


Transaction has set autocommit = 0, but still the update happens before I commit I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of statements:

set autocommit = 0;
START TRANSACTION;
use database1;
update `table1` set tkey = '3' .....;
update `table1` set tkey = '4' .......;
update `table1` set tkey = '5' .......;
update `table1` set tkey = '6' .......;
ALTER TABLE `table2` ADD COLUMN `.....` bool NOT NULL; -- This fails
COMMIT;

In above transaction the alter against table2 fails, but still the updates to table 1 have happened, how can I avoid that? I want a rollback even if one statement fails.

In MySQL most DDL statements, including ALTER TABLE, implicitly issue a COMMIT before executing. It's described in the manual.

One way to do what you want is reorder your statements, placing updates after the alter.


Are these good indexing guidelines? I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure of their justification.

It is better to create an index on a narrow datatype than it is on a wide datatype (e.g. INT over DATETIME).
It is better to create an index on multiple columns than it is on a single column.
It is better to create an index on a column that is never (or rarely) updated than it is to index frequently changing columns.

Are these good guidelines? Since I'm not entirely sure why I follow these guidelines, can you help explain what is the justification for each and when would they not apply?

Basically, SQL Server will automatically create an index for your primary key.

Other than that, I would recommend to create indices on your foreign key columns - if you have a foreign key relationship, that's most likely because you need to JOIN two tables together, and your JOIN operations will benefit significantly if an index is present on the foreign key column.

From there on - observe your system, check your system performance, gather server traces to see what queries cause the biggest CPU and/or I/O load on your system. Armed with that information, design additional indices - but sparingly! An "over-indexed" system is often worse than one without any indices!

If you see an index that would make sense and that would help one or multiple of your "heavy" queries, add that index, and observe again.

Repeat this "observe - gather information - analyse information" cycle - basically forever...

See Kimberly Tripp's excellent Indexes: just because you can, doesn't mean you should! blog post on the dangers of over-indexing


Performance of single vs multiple column unique constraint I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique constraint, I restrict the uniqueness), which one is better for lookups?

Consider this table where a unique alphanumeric code is allotted to each student of the class.

CREATE TABLE sc_table (
   name text NOT NULL,
   code text NOT NULL,
   class_id integer NOT NULL,
   CONSTRAINT class_fk FOREIGN KEY (class_id) REFERENCES class (id),
   CONSTRAINT sc_uniq UNIQUE (code)
);

Currently the code is unique across the entire table. However the specification says that it is sufficient for the code to be unique across the class only. For my design requirements there's no restriction either way.

However if I change the constraint to be unique for a given class only, how would it affect lookup by code?

Or, in other words, which of the following combination of constraint & lookup is the best speed wise:

-- 1. unique across entire table, lookup by value
CONSTRAINT sc_uniq UNIQUE (code)
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 2. unique across entire table, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code)
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'

-- 3. unique per class, lookup by value
CONSTRAINT sc_uniq UNIQUE (code, class_id)
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 4. unique per class, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code, class_id)
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'

Question: My understanding is that 2 is better than 1 & 4 is better than 3. But which one's better between 1-vs-3 & 2-vs-4?

Update : Adding output of explain analyze. 3 is bad because there's no index for the lookup. 2 seems to be the best but the table is too small to conclude that.

-- 1
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.041..0.044 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"Total runtime: 0.096 ms"

-- 2
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.024..0.026 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"  Filter: (class_id = 1)"
"Total runtime: 0.056 ms"

-- 3
"Bitmap Heap Scan on sc_table2  (cost=4.18..12.64 rows=4 width=72) (actual time=0.052..0.053 rows=1 loops=1)"
"  Recheck Cond: (code = 'code1'::text)"
"  ->  Bitmap Index Scan on sc_uniq2  (cost=0.00..4.18 rows=4 width=0) (actual time=0.039..0.039 rows=1 loops=1)"
"        Index Cond: (code = 'code1'::text)"
"Total runtime: 0.121 ms"

-- 4
"Index Scan using sc_uniq2 on sc_table2  (cost=0.15..8.17 rows=1 width=72) (actual time=0.036..0.039 rows=1 loops=1)"
"  Index Cond: ((code = 'code1'::text) AND (class_id = 1))"
"Total runtime: 0.093 ms"

Your combinations in order of typical performance:

1. > 2. > 4. ( > 3.)

3. is invalid. If rows are only unique per (code, class_id), the lookup by code alone can return multiple rows and is different from the rest.

2. is pointless. If code is unique, there is no point in adding another predicate on class_id - except to verify that a given code actually belongs to a given class_id (and get no row otherwise).

Only 1. and 4. make sense and I would go with 1., of course. Unless you have additional requirements for the values of code, it's much more efficient to have one unique column. You could also make it the PK. Queries are simpler (one predicate instead of two), the (automatically created) unique index is potentially smaller (the most important factor here!), the lookup is typically slightly faster.

UPDATEs are also potentially more expensive for 2., where more columns trigger index updates. An UPDATE changing only code_id is cheaper for 1..

Your test result for 1. is counter-intuitive, maybe an artifact of your specific setup. Maybe you didn't prewarm the cache? Or some other random factor. It's pretty obvious from the EXPLAIN output: the only difference between 1. and 2. is the additional Filter: (class_id = 1) step. Nothing to gain here, you can only lose (even if very little in this case). 2. is typically a bit slower than 1. And 4. is also typically a bit slower than 1.


Getting last modification date of a PostgreSQL database table I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in several minute after I update my table. Is it correct behaviour? Does PostgreSQL store table modifications in some cache and then flush it to the hard drive?

So, how do I get the correct last modification date of a table (let's assume that auto vacuum modifications are ok too)?

I use PostgreSQL 9.2 under Linux Centos 6.2 x64.

There is no reliable, authorative record of the last modified time of a table. Using the relfilenode is wrong for a lot of reasons:

Writes are initially recorded to the write-head log (WAL), then lazily to the heap (the table files). Once the record is in WAL, Pg doesn't rush to write it to the heap, and it might not even get written until the next system checkpoint;
Larger tables have multiple forks, you'd have to check all the forks and pick the newest timestamp;
A simple SELECT can generate write activity to the underlying table due to hint-bit setting;
autovaccum and other maintenance that doesn't change the user visible data still modifies the relation files;
some operations, like vaccum full, will replace the relfilenode. It might not be where you expect if you're trying to look at it concurrently without taking an appropriate lock.

A few options

If you don't need reliability, you can potentially use the information in pg_stat_database and pg_stat_all_tables. These can give you the time of the last stats reset, and activity stats since the last stats reset. It doesn't tell you when the most recent activity was, only that it was since the last stats reset, and there's no information about what happened before that stats reset. So it's limited, but it's already there.

One option for doing it reliably is to use a trigger to update a table containing the last-modified times for each table. Be aware that doing so will serialize all writes to the table, destroying concurrency. It will also add a fair bit of overhead to every transaction. I don't recommend it.

A slightly less awful alternative is to use LISTEN and NOTIFY. Have an external daemon process connect to PostgreSQL and LISTEN for events. Use ON INSERT OR UPDATE OR DELETE triggers to send NOTIFYs when a table changes, with the table oid as the notify payload. These get sent when the transaction commits. Your daemon can accumulate change notifications and lazily write them back to a table in the database. If the system crashes, you lose your record of most recent modifications, but that's ok, you just treat all tables as just-modified if you're starting up after a crash.

To avoid the worst of the concurrency issues you could instead log the change timestamps using a before insert or update or delete or truncate on tablename for each statement execute trigger, generalized to take the relation oid as a parameter.  This would insert a (relation_oid, timestamp) pair into a change-logging table. You then have a helper process on a separate connection, or called periodically by your app, aggregate that table for the latest info, merge it into a summary table of most recent changes, and truncate the log table. The only advantage of this over the listen/notify approach is that it doesn't lose information on crash - but it's even less efficient, too.

Another approach might be to write a C extension function that uses (eg) ProcessUtility_hook, ExecutorRun_hook, etc to trap table changes and lazily update stats. I haven't looked to see how practical this would be; take a look at the various _hook options in the sources.

The best way would be to patch the statistics code to record this information and submit a patch to PostgreSQL for inclusion in core. Don't just start by writing code; raise your idea on -hackers once you've thought about it enough to have a well defined way to do it (i.e. start by reading the code, don't just post asking "how do I ..."). It might be nice to add last-updated times to pg_stat_..., but you'd have to convince the community it was worth the overhead or provide a way to make it optionally tracked - and you'd have to write the code to keep the stats and submit a patch, because only somebody who wants this feature is going to bother with that.

How I'd do it

If I had to do this, and didn't have the time to write a patch to do it properly, I'd probably use the listen/notify approach outlined above.

Update for PostgreSQL 9.5 commit timestamps

Update: PostgreSQL 9.5 has commit timestamps. If you have them enabled in postgresql.conf (and did so in the past too), you can check the commit timestamp for the row with the greatest xmin to approximate the last modified time. It's only an approximation because if the most recent rows have been deleted they won't be counted.

Also, commit timestamp records are only kept for a limited time. So if you want to tell when a table that isn't modified much is modified, the answer will effectively be "dunno, a while ago".


Is it worth time converting Access query to T-SQL query Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions.

I had a database built in Access entirely, and had to make it so that multiple users could use it at the same time from different locations. Below are the things I have tried:

I split it and used linked tables to multiple front-ends. This would have worked fine on a local share, however, this had to work for users outside of the network. I tried a VPN which ended up being way too slow using it with a regular Windows share.
After some research I found out that you could link SQL Server tables to Access, and that would have better performance than using a Windows share. Which it was much faster, especially when you access the tables from outside the network via VPN. There were some things that still did not meet performance needs though.
I then found pass-through queries, that are run on the server instead of the client, and don't require the whole table to be requested. So then I thought "Perfect! I'll convert the queries to pass-through queries and unicorns will run free again on the Earth" (not really, but I thought simply changing the query to a pass-through one would work), nope. Turns out you also need to convert the query from the Access SQL language to T-SQL. I have no previous experience with either language.

Is it worth the effort of converting 30 queries from Access to T-SQL? I have looked around online and most things I find I don't quite understand to the point where I know what to change. Any help at all would be extremely appreciated.

To me 30 queries does not sound like to much work in order to obtain the level of performance increase you will see having SQL Server do the work instead of Access. Especially if the data is going to expand to more users and is a more mission critical type thing.

Something you might consider is running your Access database through the SQL Server Migration Assistant for Access tool. This tool will actually move your queries to SQL Server. You can find more information about the tool here: Access to SQL Server Migration: How To Use SSMA


Schema for multiple identical queue tables I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition).  Messages are received and distributed via TCP/IP endpoints and the typical installation has between 100 and 500 such endpoints.  Each endpoint has its own queue to enforce strict FIFO processing of its messages.  Dynamically creating these queues in MSMQ was no problem.  Dynamically creating individual queue tables in SQL Server isn't really a problem, either, except for the fact that I want the enqueue and dequeue operations to be implemented as stored procedures because the messages are structured and stored in a non-trivial master-detail arrangement.  The separate queue tables would each require a separate pair of enqueue/dequeue stored procedures unless I use dynamic SQL to specify the specific queue table within the stored procedure.

Per Erland Sommarskog's very helpful article on dynamic SQL, the creation of multiple identical queue tables is something of an anti-pattern, but I believe my situation warrants it because storing pointers to all messages in a single queue table would result in significant complexity and fragmentation when maintaining a FIFO ordering on a per-endpoint basis.

Should I just bite the bullet on the dynamic SQL approach to the enqueue/dequeue stored procedures or am I overlooking a simpler solution to the multiple queues problem?

Answer originally left in comments by the question author

After reading up on partitioned tables, it seems that I could store multiple independent ring buffers in one since the partitions can be stored in separate filegroups, but I doubt I'm going to be able to sell the requirement for Enterprise Edition.

I am going to try for a poor man's partitioned table by creating pre-allocated queue slots in a single table using a clustered index on QueueId and SlotId.  As long as my queues don't grow beyond the number of allocated slots, I should be able to treat them as consecutive ring buffers for multiple logical queues.


Restoring MySQL 8.0 server from cold backup files Recently, my database server running MySQL 8.0 crashed due to power outage. I have access to a cold backup (i.e., all physical files) of the server before the crash occurred. I do not have any other backups (e.g., mysqldump) of the server.

Now, I am trying to restore the server from the backup files that I have. The manual for this procedure suggests that I can directly copy all .ibd files as long as I can set up identical table structures (https://dev.mysql.com/doc/refman/8.0/en/innodb-migration.html).

Meanwhile, I am unable to find a way to create tables that are identical to those in the crashed system (I vaguely remember that when I did this years ago, I can restore table schema using .frm files but they do not seem to exist anymore in MySQL 8.0).

Any suggestions would be greatly appreciated. Thank you.

Looks like you're reading the wrong page. Restoring from a file system ("raw") backup doesn't require you to "set up identical table structures"; simply copy all MySQL files from the backup to their original location, then start MySQL.


Resources for understanding SQL Server locking and concurrency? As demonstrated by a recent question of mine locking and concurrency are HARD.

Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that would, properly learned, enable better navigation of all the inherent pitfalls in this area?

I'm thinking of all kinds of resources--tutorials, blogs, manual pages, PASS sessions, or anything.

Best book on the subject, and the most complete resource, is Kalen Delaney's SQL Server 2008 Internals. You really can't do any better. Another good book on the subject is Chris Bolton's SQL Server 2008 Internals and Troubleshooting. I don't think it's as complete as Kalen's book, but it does cover things pretty well, especially around locking and concurrency.


mysql: ERROR 1040 (HY000): Too many connections and can't restart because of Error: 28 (No space left on device) My app crashes because there are too many connections open and mysql does not make me log in as root showing the same problem.
After restarting mysql, it does not restart because of no space on disk

here there is a full guide on how I solved the issue:
I tried to log in as root with the command:
sudo mysql -u root -p

But it did not work for me, mysql kept giving the error:
ERROR 1040 (HY000): Too many connections

After some searches I tried
service mysqld restart --max-connections=500

This way stopped mysqld, which wasn't able to restart because at the same time my disk was full and receiving:
 Error: 28 (No space left on device)

Then, in order to inspect on ubuntu where my space was occupied I used the command:
du -h --max-depth=1 | sort -hr

That let me know all the space was occupied by binlog files of mysql.
I couldn't remove files by hand, it is not recommended procedure.
But I did not have anything to remove and I was not able to start mysql to purge the binlog files.
Then I run the following command which gave me enough space to restart mysql:
apt-get clean
service mysql restart || systemctl restart mysqld

At this point I was able to login to mysql and purge the binlog files with:
PURGE BINARY LOGS BEFORE '2021-03-03 22:46:26';

But this is not a long term solution.
To avoid the problem in the future I did:
SET GLOBAL binlog_expire_logs_seconds = 432000; // 5 days of binlog files

I am going to improve this answer when I solve the maximum connection issue for a long term solution.


Log Shipping: two secondary DBs on the same instance - is this possible? This is a bit insane, I'll explain intention later:

Scenario:
+ instance_1: one database (LS_source) configured as primary for log shipping
+ instance_2: two databases (dest_1, dest_2) as secondaries for LS_source (yes, this works!)

Problem:
There is one restore job on the secondary instance. Therefore both secondaries will be restored at the same point in time. We'd like to setup two restore jobs to have different schedules for dest_1 and dest_2.

Closer look: Restore executes sqllogship.exe -Restore secondary_id

Core of the problem: Both secondary databases have the same secondary_id, so sqllogship.exe will always restore both...

Any ideas how to get two restore jobs? Can the secondary_id be changed?

Thank you for your help!

Now the idea behind this strange scenario:
We'd like to use instance_2 for load balancing read only access (secondary is standby). While trn are restored, user cannot access it for ~90 seconds. This is not acceptable, 5 seconds would be ok.
If we got two secondaries restored at different times we could switch from one to another after restore completed. Both databases must be on the same instance, so we can have one database snapshot pointing to the active secondary => no need to change connection strings for all clients.
Transactional replication is no choice, impact is to high when replication stalls. AlwaysOn is not available on Standard Edition...

It is not possible.

Ref:

About Log Shipping

  SQL Server Log shipping allows you to automatically send transaction
  log backups from a primary database on a primary server instance to
  one or more secondary databases on separate secondary server
  instances.

  Provides a disaster-recovery solution for a single primary database
  and one or more secondary databases, each on a separate instance of
  SQL Server.

  The log can be shipped to multiple secondary server instances. In such
  cases, operations 2 and 3 are duplicated for each secondary server
  instance.


Sql server 2008 filestream maximum number of rows We are using filestreams in Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) and Windows server 2008 to store millions of files. Since there are millions of files all of them are grouped into 100 filegroups and inserted sequntially one after another filegroup. Now we have around 15K files in each filegroup and expected to grow to 20K soon. so we want to know is there any maximum limit of rows that a filegroup can have to give optimum performance or is there any maximum on the operating system side for optimum performace as we are storing or going to store around <20K files in single folder?
Any suggestion to the right resources will also be very helpful.
I have a link to msdn blog which states that

4.Check if FILESTREAM directory containers do not have more than 300,000 files individually, as NTFS performance degradation may be an
issue especially when generation of 8.3 filenames is enabled.

Msdn Blog Link

Thanks.

Actually you can put as many files as you want in a file group.

  Basically file group feature included in sql server so that you can
  archive your organization data properly and can query the data
  efficiently. Its also depend on the hard disk space on server and
  configuration that which raid model you use for data storage.


Should we explicitly lock MyISAM tables or is it implicit....? Does MyISAM tables automatically lock a table for each select or insert statement ....

I mean if I query the database as select * from tablename  then does the MySQL engine first lock it for read automatically and unlock it after showing results or do we have to first mention lock tablename read explicitly and then read the data....??

I have this doubt cause we have a database with some lack of rows and around some 50- 100 rows gets inserted or updated each second. We never used lock statements while inserting or updating and we are still not having any concurrency problems.

MyISAM only locks for INSERT, UPDATE, and DELETE (a.k.a. DML)

These issue full table locks each time (See MyISAM Documentation on Locking granularity).

SELECTs get blocked by those statement.

The exception is an INSERT with  concurrent_insert=2 defined.

Performing an explicit lock is unnecessary, although you are free to do so.

You may need to check you application for failed connections or DML failures.


Compare row value when records are not successive I have a table wich holds the records for all the products purchases made. What I'm trying to do is to get a list of all the purchases made that contain one specific product to get a price variation history. I also want to display an icon showing if the price increased, decreased or is the same value as the previous record.

I almost made it work, however, the way I found to do it is based on the unique ID of each rows. The problem with this approach is that they are not sequential, so I can't rely on this method. What I need to compare is the date, but I'm finding it difficult to do.

First of all, here is my table structures:

**tb_purchase**
id | value_purchase | value_delivery | date
1  | 80.90          | 0.00           | 2019-07-01
2  | 24.90          | 5.00           | 2019-07-03
3  | 18.90          | 5.00           | 2019-07-03
[...continue]

**tb_purchase_product**
id | id_purchase | id_product | id_provider | price | quantity | total
1  | 27          | 33         | 2           | 16.90 | 1        | 16.90
2  | 27          | 87         | 2           | 34.90 | 1        | 34.90
3  | 28          | 64         | 2           | 19.90 | 1        | 19.90
4  | 29          | 33         | 3           | 15.80 | 3        | 47.40
5  | 30          | 48         | 3           | 18.90 | 1        | 18.90
6  | 31          | 33         | 1           | 15.80 | 1        | 15.80
7  | 32          | 33         | 3           | 17.90 | 2        | 35.80
[...continue]

**tb_provider**
id | name
1  | 'Mega Food'
2  | 'Best Market'
3  | 'Amazing Sales Store'
[...continue]

This is the query I did:

SELECT
    a.price,
    DATE_FORMAT(b.date, '%d/%m/%Y') as 'date',
    c.name as 'provider',

    CASE
        WHEN a.price > d.price THEN 1
        WHEN a.price < d.price THEN -1
    ELSE 0 END AS 'variation'

FROM
    tb_purchase_product a,
    tb_purchase b,
    tb_provider c,
    tb_purchase_product d

WHERE a.id_product = :id_product -- {ex.: 33}
AND a.id_purchase = b.id
AND b.id_provider = c.id
AND b.date BETWEEN :date_initial AND :date_final
AND d.id = a.id + 1

ORDER BY b.date DESC

The expected result for id_product = 33 is as follow, but I'm getting inconsistent results since it's comparing the value from the previous id and not previous record of the id_product. I'm also having trouble to fix this since I don't know how to mach the date when it's the first smaller date.

price | date       | provider              | variation
15.80 | 05/07/2019 | 'Amazing Sales Store' | -1
17.90 | 04/07/2019 | 'Mega Food'           | 1
15.80 | 03/07/2019 | 'Amazing Sales Store' | -1
16.90 | 02/07/2019 | 'Best Market'         | 0

  Note: :id_product, :date_initial and :date_final are passed via php array argument when running the application online.

As already suggested by Rick James in the comments, if you are working with MySQL 8+, you can make this much easier by using the LAG() analytic function to return the previous row's price. You can also get the variation using the SIGN() function instead of the CASE expression, as demonstrated in the following query:

SELECT
  pprod.price,
  DATE_FORMAT(purch.date, '%d/%m/%Y')                                   AS date,
  prov.name                                                             AS provider,
  SIGN(pprod.price
     - LAG(pprod.price, 1, pprod.price) OVER (ORDER BY purch.date ASC)) AS variation
FROM
  tb_purchase_product AS pprod
  INNER JOIN tb_purchase AS purch ON pprod.id_purchase = purch.id
  INNER JOIN tb_provider AS prov ON purch.id_provider = prov.id
WHERE
  pprod.id_product = :id_product
  AND purch.date BETWEEN :date_initial AND :date_final
ORDER BY
  purch.date DESC
;

In this query I have also rewritten the joins using the JOIN syntax. Using the explicit JOIN syntax separates joining conditions from filter conditions more clearly, which, in my estimation, makes the code more understandable and easier to maintain (but your mileage may vary).

And with the use of the LAG() function there is no need to join a second instance of tb_purchase_product, which is another obvious difference of this query from the original.

One important note regarding this query is that, as written, it works for a single id_product, which should be all right based on your filter conditions. But if you ever need to run it for multiple products simultaneously, you need to introduce partitioning to the LAG function, like this:

LAG(pprod.price, 1, pprod.price) OVER (PARTITION BY pprod.id_product
                                       ORDER BY purch.date ASC)

The PARTITION BY subclause ensures that the previous pprod.price value is for the same product as the current row.


Database Mail not sending I'm trying to send email using the DatabaseMail on SQL Server 2008, I can see my emails setting in the

msdb.dbo.sysmail_unsentitems

But they just sit there, I get no error messages.
I've checked I have rights to use the DatabaseMail by using the following TSQL:

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';

And I have also run

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

which shows the length as the number of emails I've tried to send but the Status is still showing as Inactive.

Am I missing something else here?

Had similar issue, for me it got resolved by installing .NET 3.5 and 4.5


T-SQL replace NULL values in result I'm at loss with this. What would be the simples way to achieve this?

I have "Customers" table like this:

USER_ID| NAME    | J_ID   |K_ID    |Y_ID
-----------------------------------------
    1  |  CUST1  |  A     |  1     |  AB
    2  |  CUST2  |  B     |  2     |  NULL
    3  |  CUST3  |  C     |  3     |  EF
    4  |  CUST4  |  NULL  |  NULL  |  GH
    5  |  CUST5  |  E     |  5     |  IJ

Then "R_ID" table like this:

   R_ID  | Y_ID
------------------
    1A9  |  AB
    2B8  |  CD
    3C7  |  EF
    4D6  |  GH
    5E5  |  IJ

Then "JK_ID" table like this:

   R_ID  | J_ID | K_ID
-----------------------
    1A9  |  A  |  1
    2B8  |  B  |  2
    3C7  |  C  |  3
    4D6  |  D  |  4
    5E5  |  E  |  5

This is what I would like to get:

USER_ID| NAME    | J_ID   |K_ID    |Y_ID
-----------------------------------------
    1  |  CUST1  |  A     |  1     |  AB
    2  |  CUST2  |  B     |  2     |  CD
    3  |  CUST3  |  C     |  3     |  EF
    4  |  CUST4  |  D     |  4     |  GH
    5  |  CUST5  |  E     |  5     |  IJ

So I need to get the missing values from R_ID or JK_ID tables by joining those. But what is the simplest way to get that result, so that the nulls are replaced by the values from joined tables?

One way to accomplish this is to use LEFT OUTER JOIN along with COALESCE. To get the missing values in the J_ID and K_ID column you can join to R_ID and JK_ID. To get the missing values in the Y_ID column you can join to JK_ID and R_ID.

Something close to the following should work:

SELECT
  cus.USER_ID
, cus.NAME
, COALESCE(cus.J_ID, r_to_jk.J_ID) AS J_ID
, COALESCE(cus.K_ID, r_to_jk.K_ID) AS K_ID
, COALESCE(cus.Y_ID, r_to_y.Y_ID) AS J_ID
FROM Customers cus
LEFT OUTER JOIN R_ID y_to_r ON cus.Y_ID = y_to_r.Y_ID
LEFT OUTER JOIN JK_ID r_to_jk ON y_to_r.R_ID = r_to_jk.R_ID
LEFT OUTER JOIN JK_ID jk_to_r ON cus.J_ID = jk_to_r.J_ID AND cus.K_ID = jk_to_r.K_ID
LEFT OUTER JOIN R_ID r_to_y ON jk_to_r.R_ID = r_to_y.R_ID;


Execution Plan in Microsoft SQL Server I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine.

Question 1: If I execute following queries from Hibernate, will database create a separate execution plan for each query? In which case SQL Server update/change stored procedure Execution Plan?

EXECUTE myProcedure 'vicky.thakor', 'Routine Check Up'

and

EXECUTE myProcedure 'chirag.thakor', 'Routine Check Up'

Question 2: This is bit of programming question but you guys may explain me bit of it. In Java there is something called PreparedStatement. PreparedStatement is similar to pre-compiled query.

PreparedStatement generates query like following and put value in place of ?.

SELECT * FROM user_master WHERE user_name = ?

But database will receive the final query like

SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

then how database help to get best performance when using PreparedStatement.

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything.

Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached.

When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc query.

The point is: as long as the actual SQL statement is IDENTICAL (down to the last comma or space), then a possibly pre-existing, cached execution plan is reused and the whole step of determining an execution plan can be skipped. If the SQL statement text varies - even by a single space - the whole process of analyzing and coming up with an execution plan for that query is run again.

I don't know about how Java handles it's prepared statements, but in C# using ADO.NET, when you run a query something like

SELECT * FROM user_master WHERE user_name = @userName

and you supply a value for @userName (e.g. 'vicky.thakor'), then the SQL Server will NOT received what you seem to think it will receive - the parameter is NOT replaced in the query text - instead, SQL Server will execute this as

EXEC sp_executesql @stmt, N'@userName', @userName = 'vicki.thakor'

SQL Server will receive a parametrized query and a list of parameters and their values.

Therefore, if you call this same query again, with a different value for @userName, the actual SQL statement text is identical and the cached execution plan is reused - only the value of the parameter has changed.


ER relation with unique key I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs:

A student does not have to be a member of any team.
A student could be a member of at most one team.
member has a unique member ID.

I did my ER Diagram:

                                                  member-id (key attribute)
                                                            |
--------                          -                    -----------
- team -------------------- -  - mem -   ------------- - student -
--------                          -                    -----------

As I said before, student does not have to be a member of any team. In this case, my diagram works fine? I asked it because of the member_id is a unique key.

For now, I see two possible problems in your model:

Member of a team can exist without a reference to Student.

1.1. If you delete a Member with ON DELETE CASCADE foreign key option in Student it will remove your student.

1.2. If you delete a Student, there might be a Member, that not referenced to any Student.
If you would like to place Member properties to Student table, then you will have to set them to NULL each time, then you need to unreference Student and Member. Also it will lead to table Student will loose it's independence. If you would not do that, this proplem will never happen.

I might suggest something like this instead:

Where: AI = AUTO_INCREMENT, NN = NOT NULL, UN = UNSIGNED.

Student can not exist in two teams, because there might be only one record in table Member for each Student record.
Student can be a member if record in the Member table exists for the Student table record. However, he might have no record in the Member, but still have a record in the Student table. Means, that he is not member of any team.

How to achieve that?

Make a primary key of the Member table to be a foreign key, that referes to Student table's primary key. PRIMARY KEYs are UNIQUE by default and can not have duplicate values. So there wouldn't be two records in the Member table for one record of table Student.
Make a foreign key for the table Member, that referes to the Team table's primary key. Only member can have a team.
As there is only one record for each Student can be placed in Member table, each student will have to be only in one team.
If you delete Member record, Student associated record will no longer be a member of any team, but still be a student.

Test script (assuming MySQL RDBMS):

DROP SCHEMA IF EXISTS `test`;
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `test`;

CREATE TABLE IF NOT EXISTS `Team` (
  `IDTeam` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `etc` VARCHAR(45) NULL,
  PRIMARY KEY (`IDTeam`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Student` (
  `IDStudent` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`IDStudent`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Member` (
  `IDMember` INT UNSIGNED NOT NULL,
  `TeamID` INT UNSIGNED NOT NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`IDMember`),

  INDEX `fk_Member_Team` (`TeamID` ASC),

  CONSTRAINT `fk_Member_Student`
    FOREIGN KEY (`IDMember`)
    REFERENCES `Student` (`IDStudent`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `fk_Member_Team`
    FOREIGN KEY (`TeamID`)
    REFERENCES `Team` (`IDTeam`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


What does the vertical pipe slash operator do? I'm wondering what pipe slash |/ does in,

SET equal_area_radius = |/area/pi();

The statement works and it definitely changes the value:

SELECT |/125.555/pi(); --  returns: 6.32181918120139
SELECT 125.555/pi();   --  returns: 39.9653977598058

What does the pipe-slash operator |/ do?

The |/ operator calculates the square root

postgres>  select |/16,  sqrt(16);
 ?column? | sqrt
----------+------
        4 |    4
(1 row)

So |/125.555/pi() is equivalent to sqrt(125.555/pi())


I can't create relationship in my database sql server I have designed a  database for my website. It has 3 tables: user, topic and comment.

A user has many topics (or articles)
A topic (or article) has many comments
A user has many comments

I created the relationships for them  after I set the insert and and update specification as cascade.

I want that

if I delete a user then the topics of this user and the user's comments will be deleted
If I delete a topic then all comments of this topic will be delete by cascade

My diagram:

The issue is with creating two CONSTRAINT in Comment table with ON DELETE CASCADE one for UserId and other for TopicId, which can be fixed by INSTEAD OF DELETE Trigger. Just create all the tables and add all foreign key constraint as shown.

You can add your columns as indicated with other columns and Id column can be changed from bigInt to Int.

CREATE TABLE [dbo].[User]
(
UserId BIGINT NOT NULL,
UserName VARCHAR(10),
--Other columns
CONSTRAINT PkUserId PRIMARY KEY CLUSTERED (UserId ASC)
);
GO
CREATE TABLE [dbo].[Topic]
(
TopicId BIGINT NOT NULL,
UserId BIGINT NOT NULL,
TopicData VARCHAR(20)
--Other columns
CONSTRAINT PkpostId PRIMARY KEY CLUSTERED(TopicId ASC)
);
ALTER TABLE [dbo].[Topic] WITH CHECK
ADD CONSTRAINT [FkTopicUserId] FOREIGN KEY(UserId)
REFERENCES [dbo].[User]([UserId]);
GO
CREATE TABLE [dbo].[Comment]
(
CommentId BIGINT NOT NULL,
TopicId BIGINT NOT NULL,
UserId BIGINT NOT NULL,
CommentData VARCHAR(20),
--Other columns
CONSTRAINT PkCommentId PRIMARY KEY CLUSTERED(CommentId ASC)
);

ALTER TABLE [dbo].[Comment] WITH CHECK
ADD CONSTRAINT [FkCommentTopic] FOREIGN KEY(TopicId)
REFERENCES [dbo].[Topic](TopicId);
GO
ALTER TABLE [dbo].[Comment] WITH CHECK
ADD CONSTRAINT [FkCommentUser] FOREIGN KEY(UserId)
REFERENCES [dbo].[User](UserId);

Now for Multiple delete cascade you can use INSTEAD OF DELETE Triggers as

CREATE TRIGGER [DELETE_User]
   ON [dbo].[User]
   INSTEAD OF DELETE
AS
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [dbo].[Topic] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM  [dbo].[Comment] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM dbo.[User] WHERE UserId IN (SELECT UserId FROM DELETED)
END
GO
CREATE TRIGGER [DELETE_Topic]
   ON [dbo].[Topic]
   INSTEAD OF DELETE
AS
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [dbo].[Topic] WHERE UserId IN (SELECT UserId FROM DELETED)
 DELETE FROM  [dbo].[Comment] WHERE UserId IN (SELECT UserId FROM DELETED)
END
GO

Let's check with some data on these the above design.

INSERT INTO [dbo].[User]
VALUES (1,'user1'),(2,'user2');
GO
INSERT INTO [dbo].[Topic]
VALUES (1,1,'POST1'), (2,1,'POST2'),(3,2,'POST3');
GO
INSERT INTO [dbo].[Comment]
VALUES  (1,1,'COmment1'), (2,1,'Comment2'),
(3,1,'Comment3'), (4,2,'Comment4'), (5,3,'Comment4');

Now check with cascade delete.

SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];
GO
DELETE FROM [dbo].[Topic] WHERE TopicId =1;
GO
SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];
GO
DELETE FROM [dbo].[User] WHERE UserId = 2;
GO
SELECT UserId,UserName FROM [dbo].[User];
SELECT TopicId, UserId FROM [dbo].[Topic];
SELECT CommentId,TopicId FROM [dbo].[Comment];


Is there any way to retrieve "Messages" in SQL Server through JDBC? By "Messages" I mean the Messages tab in the following figure, which includes "Warning", "n rows affected" and in the figure below, the execution time. In JDBC, ResultSet class is used to retrieve the "Results". Is there any way to get all the information in the "Message" tab through JDBC?

You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that.

In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop:

Statement stmt = ...;
stmt.execute("some_procedure");

SQLWarning warning = stmt.getWarnings();

while (warning != null)
{
   System.out.println(warning.getMessage());
   warning = warning.getNextWarning();
}

I have seen some messages also being returned on the Connection instance rather than the Statement. If you don't see anything, try the above code with your Connection instance.