SQL Server Basic Availability group: one or zero listener for multiple AG In my testing of SQL Server 2019 Basic Availiiblity Group on Ubuntu 20.04, I successfully created AG1 for DB1 and AG2 for DB2 with only AG1 config'ed with listener IP. But when I use sqlcmd to test, I can connect to AG1's listener and use AG2's DB, do DDL and DML. Is it OK for this configuration? Only 1 listener for several AG.
Besides, I'm using pacemaker for cluster management, is it possible to config no listener for AG, just use pacemaker's IP resource to bind IP on master server?

The listener has to be unique. When you attempt to create the listener you will get an error message that is difficult to understand, but basically, the listener setup program detects that it is in use.
Multiple AGs on the same nodes can share a listener.


Migrate Postgres from VM to VM I have 2 VMs, both are identical in structural terms (came from the same snapshot, nothing installed, upgraded or removed).

One VM is "clean", never used.

The other VMs was heavily used and it has some data stored in the Postgres DB.

Is there a way to migrate the Postgres database from the heavily used VM to the clean VM just by copying the DB files from a specific folder?

For instance, let's say that Postgres (I'm assuming this part) writes all its files in the directory XYZ. If I copy all files from that XYZ directory to the clean VM, will Postgres work?

I'm not worried if that is the correct way to do that or not. This is just a PoC and I need to do it with the least effort possible and copying files around seems to be the best fit here.

Absolutely no! Do not try this. The normal way of doing this is a proper dump and restore and copy the dumped file from one VM to another VM.

pg_dump -d db -f db.sql

You might want to compress the file with xz or some other compressor before transferring it.

transfer db.sql to new VM after creating the database with createdb db (adding any other option you require)

then

psql -d db -f db.sql

or use pg_restore


mysqld can't start without any info mysqld can't start without any info, that is the only information I get is 'Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!'.

I edited the my.cnf file to enable logging on /var/log/mysql/mysql.log but it apparently doesn't want to log anything.

So I don't know anything. I know there are many similar questions, but in most cases people are able to tell something more from the log file(s).

BTW, it is happening after abnormal system shutdown.

Update 1: in my.cnf file I can see a line like this:
Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

When I open the file I can see this:
[mysqld_safe]
syslog

What does it mean? How can I see error log?

Update 2: when I do
mysqlcheck -u root -p --all-databases

I get "Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect"

Update 3: OK, now I know what syslog is:) It says: Can't start server : Bind on unix socket: No space left on device
So now I have a final question - on which device?

the answer is there MySQL can not write to the directory you are pointing inside my.cnf, and it dose not have the right permissions. to fix this problem make sure the user MySQL has owner privileges on the directory that my.cnf is pointing for example lets say the base_dir is located under /MySQL , data_dir is located under /MySQL/data and the socket is located under /tmp in this example you need to make sure /MySQL and /MySQL/data are owned by MySQL user, for /tmp since its a default directory for Linux user MySQL should have read,write and execute privileges. also you need to make sure the partition that contain the data_dir is not full "contain enough free space"


Mysql - when increase 'max_connections', what other paramters to adjust accordingly? I know some memory / cache / open file related params  should be adjusted accordingly.
But after searching for quite a while, didn't found a comprehensive guide.
Say, mysql 8 is in use, running on Linux, and trying to change max_connections to 400, the database is using innodb engine, could you give a list of other parameters, and their new values to adjust to, in common.

The main setting is innodb_buffer_pool_size.  It should be set to about 70% of available RAM if you have more than 4GB of RAM.
If you need more than the default 151 for max_connections it is worth looking into whether the client(s) are allowing too many connections.  It is better to reduce the load from that size.
A common problem is open_files_limit.  It comes from the OS's ulimit may be at a painfully low 1024.  That should be fixed in the OS.
There are no other "likely" settings to check on, especially with MySQL 8.0.
There are STATUS values that can help tuning busy systems.  See this for guidance in that help:  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


database schema for a product with multiple categories and hierarchical categories I need some opinion on whether I am going in the right direction with this.

I am trying to build a e-commerce site (with MySQL backend) where

a single product can belong to more one than category
categories are hierarchical in nature

For e.g.

Food > Fruit > Red > Cherry
     > Fruit > Yellow > Banana
     > Healthy > Cherry

Cherry is both Healthy and Red.
There is a hierarchy for categories

The way I am planning to implement this in the backend is:

Modified PreOrder traversal for storing hierarchy of categories. The tree would not hold products, but only categories.
ProductID - CategoryID: many-to-many table: where each product can be stored with multiple categories.

Is this the right way to go ?

One of my concerns in this approach is; even though new categories won't be added very frequently, but when we would have to add a new category, we will have to make a whole lot of updates in productID-cateogryID table since the category IDs would now be pointing to something different!

Is there a better way to deal with this ?

EDIT: Modified preorder traversal is better known as: Nested Set model

What you are proposing is a good solution for your requirement of M:N products to categories and hierarchical categories.

To avoid exposing yourself to numerous updates:
You need to do two things to ensure that you don't have a lot of updates in your intersection table.

First, you need to be sure that your categories have a stable, persistent primary key.

Second, you need to link food items to leaf categories.  Don't join Cherry to Red, Healthy, Fruit and Food - just join it to Red and Healthy.  Your nested sets take care of all of the secondary (and higher level) associations.


On which node first transaction will be executed in Always-on setup? I have a scenario where SQL Server 2012 - Always-on feature configured.The primary and secondary are synchronous for failover purposes. I understand that we will have node 1 (PRIMARY) and node 2 . Also I know during fail over node 2 becomes PRIMARY.

My question is, (synchronous commit setup)

When application send INSERT or any SQL transactions, which node first the execution happens? Is it on PRIMARY ?
When node 1 fails then node 2 becomes PRIMARY, but who will make node 1 up?  do I need to setup alerts? if node 1 dies then how HA and DR works?
What is difference between active-passive or active-active?

Where I can find detailed information on always-on architecture? I have read many article but none explains the internals.

1) From Overview of AlwaysOn Availability Groups (SQL Server)

  The primary replica sends transaction log records of each primary database to every secondary database. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database.

So, primary first, then secondary.

2) Are you asking how node 1 is brought back on line? That's what your operations people and DBAs are paid their huge salaries to do.

Alerts would be a good idea. Maybe even a very good idea.

If node 1 dies AlwaysOn will re-direct connects to node 2, transparently to the application. In-flight work may receive an error. New connections will be routed to node 2. This is the HA and DR in action. When node 1 comes back you can continue running on node 2 as primary and have node 1 as secondary, or organise a managed failback at a time of your choice. If node 2 dies while node 1 is still down, then you have a problem.

3) Active-passive has the secondary as "warm": it is ready to become the primary when required but cannot do any application work until then. Active-active has a "hot" secondary: it can process read-only transaction from the application, and be used for backups and such like. IIRC one license is needed for active-passive and two for active-active.


Distributed Availability groups & Listeners I'm currently doing my first setup with Distributed Availability groups. This is currently a POC setup but will normally be implemented soon. I have 4 nodes, 2 nodes located together and 2 other also together but with some distance between them.

I set up the Distributed AG with the automatic seeding and everything is fine so far. The database got synchronised to the secondary Availability group without any issues.

Now comes the actual question: I have created two listener for my AG's (Primary & Secondary), but I can't create a listener for my Distributed AG (Not much information on it). This poses the problem that my application will have to be Listener aware...

Is there a way to create a listener for the Distributed AG, through which the application will always be able to connect even if we failover the distributed availability group?

After a call with someone from Microsoft, I was told that there are no listeners on the Distributed availability level yet. So currently if we want to use DAG, you will have to change your DNS or application connection to connect to the secondary availability group.


MySQL - speed up execution of ALTER TABLE I have a MySQL DB with innoDB tables. the DB size is about 30GB. I am using ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; to change all the tables characters set and it takes forever!!
is there a way to speed up the process?

create a new table like your source table.
alter that new table the way you want.
insert your data into the new table.
create indexes etc. as needed on the new table.
rename your old table to something like ..._old or whatever.
rename your new table to the former name of the old one.
copy any missing rows from the _old table to the new one.
done :)


Perform join on multiple tables I have three tables right now in a postgres DB:

Artists
ID  Name

Shows
ID  Date    Venue   Price   Is_sold Ages    Time    Multi_day   Pit

Artists_shows
ID  Show_id Artist_id

A show can have one or many artists; eventually I'm going to produce JSON for all shows that looks like this:

[
    {
    "bands": [
        "Domino And The Derelicts",
        "Creeper",
        "Lesbros"
    ],
    "date": "2015-12-26T08:00:00.000Z",
    "venue": "Stork Club, Oakland",
    "time": null,
    "soldOut": false,
    "pit": false,
    "multiDay": false,
    "ages": "21+",
    "price": null
    }

...]

Right now I'm doing this pretty inefficiently.

First, I'm running a query: SELECT * FROM shows WHERE date >= ${date} ORDER BY date, where date is today's date.

Then, I'm taking the result and running a for loop through each individual show that results and running this query: SELECT artist.* FROM artist INNER JOIN artist_shows ON artist.id = artist_shows.artist_id AND artist_shows.show_id=${id}, where id is each id from the results of the first query on the Shows table.

I'm then stitching together the results of both queries to produce the resulting JSON.

Is there a way to perform a single JOIN operation on my tables so that I can avoid the loop that I'm running? I assume that there might be a way to merge my existing join with my first query so that I get a table with all of the artists and all of the show details in one table. The thing that is tripping me up is the fact that a show can have many artists associated with it.

You can only return single row results so grouping in the way you are envisioning is not all that natural. There is a way, however, to use aggregate functions to combine groups and get the exact JSON result you are looking for.

SELECT
  row_to_json(all_shows)
FROM (
  SELECT
    array_agg(bands) bands,
    shows.*
  FROM shows
  JOIN (
    SELECT show_id, array_agg(artists.name)
    FROM artist_shows
    JOIN artists ON (artist_shows.artist_id = artists.id)
    GROUP BY show_id
  ) bands ON bands.show_id = shows.id
  WHERE date >= now()
) all_shows

Here we are using a subquery (bands) to group all artists names based on the show id and then join the aggregate array into the show row. Lastly you can combine all rows and return a JSON representation of the data. If you don't care to have Postgres wrap everything up into JSON then you can just use what is in the initial FROM clause.


PostgreSQL / pgAdminIII: is it possible to send command line commands via the UI Using PostgreSQL / pgAdminIII, is it possible to send command line commands to PostgreSQL via the UI? Put another way: how can I access the command line via the pgAdminIII?

As I am coming up to speed on pgAdminIII, occasionally it seems like it would be useful. Ex: I search for a solution to an issue, but all I find is a solution that involves the command line, not the pgAdminIII UI.

Edit:

Maybe I am not understanding something more fundamental here, or maybe I am not using the proper terminology, but for example in this link Jay Taylor's answer seems to use the command line commands I am referring to:
https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql

In this link Bruno's answer uses them:
https://stackoverflow.com/questions/20775462/cant-grant-user-privileges-to-postgresql-database-for-a-rails-app

In this link there are several references to them:
https://jamie.curle.io/creating-a-read-only-user-in-postgres

Explicitly, from the last link above, how would I send something like this to PostgreSQL/the server from pgAdminIII (not that I actually want to):

 pg_dump -O -Fc  --format=t productiondb -U backup_user -h localhost

If by "command line" you mean the client program psql, then the answer is no. psql is just another client program like pgAdminIII.

If you have configured File -> Options -> Browswer -> Binary paths -> PG bin path in pgAdminIII, there is a shortcut to open psql:

pg_dump is another separate client application. You run it from the shell of your OS. You don't "send it to Postgres". But you can optionally call it from within pgAdmin. See Tools -> Backup...

All commands starting with the shell prompt $ in Jay Taylor's answer are also commands to run in the shell. Be sure to connect to the right DB.


How can i import 150GB database after deleting ibdata1,logfile in MYSQL? I have a 150GB database and my ibdata1 file size is 240GB so I want to shrink the ibdata1 file because I have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and then start the =MYSQL again and import the DB again.

If the above solution is correct how can I export and import such huge DBs ?   Also, there is partition created in the DB table.

And I am also not sure that I will not lose any data in during this process.

You need to mysqldump all the data (InnoDB and MyISAM) to a local text file and follow your steps.

I have discussed this topic before

Dec 06, 2012 : InnoDB insertion faster
Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Oct 29, 2010 : My Original Post in StackOverflow

You may be concerned about the amount of data to dump. If you have multiple database, perhaps doing a mysqldump of all that data into a single table may not be advisable.

You could perform a mysqldump for each database into separate files

Please read my other posts on how to do separate mysqldumps

Apr 17, 2011 :  How can I optimize a mysqldump of a large database?
Dec 07, 2013 : Is there a Successor or an Alternative to mk-parallel-dump?

UPDATE 2014-04-10 11:21 EDT

Given these facts

You have two databases (call them db1 and db2)
First Database is 140 GB
Second Database is 7 GB

Here are two courses of action

COURSE OF ACTION #1 : Dump Databases Separately

Here is a straightforward mysqldump approach to dump and restore

Dump Databases into Separate Files

USERPASS="-uroot -ppassword"
DBTODUMP=db1
DUMPFILE=${DBTODUMP}_dump.sql
echo "CREATE DATABASE IF NOT EXISTS ${DBTODUMP};" > ${DUMPFILE}
echo "USE ${DBTODUMP}" >> ${DUMPFILE}
mysqldump ${USERPASS} --routines --triggers ${DBTODUMP} >> ${DUMPFILE}
DBTODUMP=db2
DUMPFILE=${DBTODUMP}_dump.sql
echo "CREATE DATABASE IF NOT EXISTS ${DBTODUMP};" > ${DUMPFILE}
echo "USE ${DBTODUMP}" >> ${DUMPFILE}
mysqldump ${USERPASS} --routines --triggers ${DBTODUMP} >> ${DUMPFILE}

Restore Databases (after making new ibdata1 and logfiles)

USERPASS="-uroot -ppassword"
DBTODUMP=db1
DUMPFILE=${DBTODUMP}_dump.sql
mysql ${USERPASS} < ${DUMPFILE} &
DBTODUMP=db2
DUMPFILE=${DBTODUMP}_dump.sql
mysql ${USERPASS} < ${DUMPFILE} &
wait

COURSE OF ACTION #2 : Dump Tables Separately

Here is code to dump all the tables 5 at a time

USERPASS="-uroot -ppassword"
TABLESTODUMP=/tmp/TablesToDump.txt
DUMP_LOCATION=/some/path/you/choose
SQL="SELECT CONCAT(table_schema,':',table_name)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema IN ('db1','db2')"
mysql ${USERPASS} -ANe"${SQL}" > ${TABLESTODUMP}
COMMIT_COUNT=0
COMMIT_LIMIT=5
for DBTB in `cat ${TABLESTODUMP}`
do
    DB=`echo "${DBTB}" | sed 's/:/ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/:/ /g' | awk '{print $2}'`
    DUMPFILE=${DUMP_LOCATION}/${DB}_${TB}.sql
    echo "CREATE DATABASE IF NOT EXISTS ${DB};" > ${DUMPFILE}
    echo "USE ${DB}" > ${DUMPFILE}
    mysqldump ${USERPASS} ${DB} ${TB} >> ${DUMPFILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ] ; then wait ; fi

Restore Databases (after making new ibdata1 and logfiles)

DUMP_LOCATION=/some/path/you/choose
cd ${DUMP_LOCATION}
for DUMPFILE in `ls *.sql` ; do mysql < ${DUMPFILE} ; done

CAVEAT

Make sure DUMP_LOCATION has enough space


SqlLocalDB.msi and DBNETLIB / PowerShell SqlServer ServerConnections I'm trying to setup a development SQL server using SqlLocalDB.msi which installs a local version of SQL Server 2017.

I can connect to the SQL engine with SSMS (v14.0.17277.0) using MDAC (v10.17134.1).

But, when I open SQL Server Configuration Manager and click on SQL Server Network Configuration (32-bit), it says "There are no items to show in this view."

This appears to be a real problem because when a third-party app I have to use tries to open a connection to the database, it fails withthe following message:

But, the Server does exist.  Here's a screen shot from SSMS:

Another clue to what may be going on is that no ServerInstances are listed when when I open an administrator Power Shell and do the following:

PS C:\Windows\system32> import-module "sqlserver"
PS C:\Windows\system32> $smo = 'Microsoft.SqlServer.Management.Smo.'
>> $wmi = new-object ($smo + 'Wmi.ManagedComputer').
>>
>> # List the object properties, including the instance names.
>> $Wmi

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {}
ClientProtocols    : {np, sm, tcp}
ServerInstances    : {}
ServerAliases      : {}
Urn                : ManagedComputer[@Name='WINDEV1806EVAL']
Name               : WINDEV1806EVAL
Properties         : {}
UserData           :
State              : Existing

Does anyone know how to get SQL Server Express to work with DBNETLIB?

SQL Express LocalDb is not going to work with the old ODBC driver that comes with Windows.  Either download and use a newer ODBC driver, or install the service-based SQL Server Express Edition.


Update table where table name in select query itself I am having following select query

 (select   article.articleId ,'MUSIC' as type ,createddate
  FROM     MUSICarticle
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId, 'SPORTS' as type ,createddate
  FROM     SPORTSarticle
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId ,'DANCE' as type  ,createddate
  FROM     DANCE article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
  FROM     SINGING article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 order by 3 ASC limit 1

and having output

articleid |  type   |        createddate
----------+---------|-------------------------
    1     |  MUSIC  | 2017-04-28 12:53:55.649

Now have to update MUSIC table where id =1 .

You could use a stored procedure or anonymous PL block to build dynamic SQL. See below for an example.

  DISCLAIMER: I must point out that dynamic SQL is almost never the way to go - the database cannot cache the query plans efficiently and it is much slower than conventional queries. Were we on Stack Overflow here I'd be more inclined to suggest you use a supplementary language with a switch statement or something to accomplish this.

do language plpgsql $$
declare
    _sql text;
    _r record;
begin
    for _r in (
        select   article.articleId ,'MUSIC' as type ,createddate
        FROM     MTDDiscussion article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.articleId, 'SPORTS' as type ,createddate
        FROM     MTDOpinion article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.articleId ,'DANCE' as type  ,createddate
        FROM     MTDSurvey article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
        FROM     WikiTopic article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1
    ) loop
        _sql = E'update ' || _r.type::text || E' set COLUMNNAME = \'' || _r.createddate::text || E'\'::date where id = ' || _r."articleId"::text;
        execute _sql;
    end loop;
end
$$;


Usage of json to store multiple attributes Typically we have the table designed like this

UserId  FirstName LastName EmailId  MobileNumber Age Sex City

Where UserId is going to be the primary key

Is it good to have multiple columns combined into one single column?
i.e what if i have the above table structure changed to one below

UserId  EmailId UserDetails

Where the UserDetails will contain all the user details in a json format.

When we are querying, we can bring the data and build the user object from json string and get the desired results using Linq to Sql queries

What are the problems we might face if we follow this approach?

This way if you need to filter/order by any part of UserDetails, you cannot use index.

So you can fetch ALL rows to your app and do it there - that means:

reading everything in sql (slow disks)
moving it to app (slow network)
manage in app (lot of memory)

Or you can "walk" all rows in sql using LIKE or something. That will still mean reading entire table but at least it will give you only somehow relevant rows where there is nontrivial chance that it will really match after "deserializing" the JSON so you skip latter two of previous three points.

Linq2Sql is just that - it normally builds queries (hopefully quite efficient ones, but you can sure break that). You probably meant Linq to objects/collections.

Those stand if you need to search "all" (means big enough subset) users by Age/Name etc. In that case having the data in separate columns (and properly indexed) is really needed for anything reasonable (if you will never have more than few hundreds of users, you can probably do it any way and not notice anything, but if you get to tens of thousands or even millions, it will really start to matter).

But - if you know that even with millions of users you will always need to search only specific userId/emailId, or small set of them (say one specific school class in a table of all students of US) and never to it without those limits, then JSON won't hurt you and table will probably be smaller without those indexes you are not going to use.

Same if you never search by those at all, but that does not seem probable (even if you do not now, you probably will in the future - if management asks you for statistics of first names per city, you have a problem with JSON).


SQL Server 2016 - ColumnStore Clustered Index vs. Non-ColumnStore Clustered Index I just read the SQL 2016 Columnstore Index guide located here. We're going to have some rather large tables (hundreds of millions to billions of rows) in a SQL 2016 database that will be used for both OLTP and analytics.

These tables will be queried, primarily, one of two ways: 1) Users will retrieve rather small result sets based on specific discrete values in a Where clause (e.g., Where SubId = 'ABC'); 2) Users will retrieve larger results sets based on a date/time value ranges (e.g., Where ReadTime Between '2/1/2017' And '2/5/2017').

Since a columnstore index is better suited for scenerio #2 (I think), I'm considering making the clustered index non-columnstore (e.g., on SubId) for scenerio #1 and creating a non-clustered columnstore index (e.g., on ReadTime) for scenerio #2.

However, I'm not sure this is really any better than if I created a columnstore clustered index on ReadTime and a non-columnstore index on SubId.

I'm not sure how to make this decision.

Clustered Columnstore Indexes are designed to be used primarily with Data Warehouse, OLAP or reporting solutions.  They are not great at WHERE x = y type queries; they are excellent at WHERE x < 1 and x > 1000000 type queries where the query processor can perform a range-scan on the CCI.

Assuming your workload is OLTP-critical, I'd recommend, without a ton of work on my part, to create the table with a standard B-Tree clustered index, and consider a well-designed nonclustered columnstore index that will help with your point #2.

If the OLTP component of the workload is small, or not very critical, but the reporting component is critical, you could possibly create the table as a clustered columnstore index, then add a number of starndard b-tree nonclustered indexes to help support the OLTP component.


Mysql replication no error and not replicating I am getting no errors yet replication is not working. I wonder where I am going wrong?

My master status is

            File: mysql-bin.000002
        Position: 2006432
    Binlog_Do_DB: site3,site2
Binlog_Ignore_DB:

My Slave Status is

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 104.131.119.241
                  Master_User: drupal
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2006432
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 2006577
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: site3,site2
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2006432
              Relay_Log_Space: 2006879
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

USE site99;
INSERT INTO site2.mytable ...;

will not be replicated.  This is because the USE controls replication, not the db.tbl syntax.

If this does not provide the answer, please provide an example of the code that fails, plus mysqldumpbinlog against both the Master's binlog and the Slave's relay_log.  This should show whether or not the query made it that far.


MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */ Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form:

SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments).

I can't seem to find much information about what this statement means and whether I need to worry about it enough to try and track down where WordPress is creating this SQL.

These types of SELECT are always performed by mysqldumps.

If you look in the slow log for the start time of the query and the time of each of these SELECTs is the same time every day, it is definitely coming from a mysqldump you have crontab'd somewhere.

To eliminate this, you may need to run this

SET GLOBAL slow_query_log = 'OFF';

Then go run the mysqldumps, and then run

SET GLOBAL slow_query_log = 'ON';

This should totally eliminate this type of SELECT from landing needlessly in the slow log.


Simplest filesystem backup of Postgres database Situation: A backup system performs filesystem-level daily backups on a server containing a large but fairly static PostgreSQL 9.2 database. The filesystem backups are incremental and generally take <30 minutes.

My initial idea was to use the backup system hook scripts to put the database cluster into backup mode:

start-backup hook runs: psql -c "SELECT pg_start_backup('daily');"
backup copies postgres data files somewhere else, ignoring pg_xlog/
backup-finished hook runs: psql -c "SELECT pg_stop_backup();"

My understanding was that (1) would force a database checkpoint, meaning that you have a consistent filesystem in base/ & friends. Further WAL segments would be written during (2) to pg_xlog/ but would leave the base/ files alone. Then when pg_stop_backup() is called in (3) it applies the WAL logs from during the backup to base/ & friends. And if you restore the backup you'd end up with the database state at (1).

But the Postgresql docs describe something different:

  Standalone Hot Backups

  It is possible to use PostgreSQL's backup facilities to produce standalone hot backups. These are backups that cannot be used for point-in-time recovery, yet are typically much faster to backup and restore than pg_dump dumps.

  If more flexibility in copying the backup files is needed, a lower level process can be used for standalone hot backups as well. To prepare for low level standalone hot backups, set wal_level to archive, archive_mode to on, and set up an archive_command that performs archiving only when a switch file exists. For example:

archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'

  This command will perform archiving when /var/lib/pgsql/backup_in_progress exists, and otherwise silently return zero exit status (allowing PostgreSQL to recycle the unwanted WAL file).

  With this preparation, a backup can be taken using a script like the following:

touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/`

  The switch file /var/lib/pgsql/backup_in_progress is created first, enabling archiving of completed WAL files to occur. After the backup the switch file is removed. Archived WAL files are then added to the backup so that both base backup and all required WAL files are part of the same tar file. Please remember to add error handling to your backup scripts.

That's fine - I need to enable wal_level=archive to get pg_start_backup() to work anyway, and can easily create & remove a switch file. But getting the backup system to revisit the filesystem (for the final step above) isn't possible...

From what I can tell, the above approach from the Postgres documentation would get me a backup of the database state as at pg_stop_backup() (the latter part via replaying the archived WAL segments during startup).

But it's quite possible my understanding is wrong, and base/ does change during the backups, which means you need the archived WAL segments as well.

So, do I need to copy the WAL segments from the time between pg_start_backup() & pg_stop_backup() calls in order to get a usable backup?

Yes, base/ will likely be changed while the backup is occurring.  And yes, you need all of the WAL segments for the time between pg_start_backup() and pg_stop_backup().

Your existing filesystem backup tools do not provide you with the tools you need to back up a live database.  This is not unusual.  Rather than trying to fit a square peg into a round hole, why not just tell your filesystem backup tool to ignore the database, and then use a dedicated database backup tool to backup the database?  For example, use pg_basebackup in conjunction with WAL archiving, or pg_basebackup -X s as a standalone backup.  Of course, the files created as the result of this backup need to themselves backed up, but at that point they are "cold" files and your regular backup system will work.


How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job? I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window in the upgraded dev. server, It seems(as far as I know) that the only changes that I have to make to this script is to point it to the correct server, file path and get it a new @schedule_uid number but not sure how to assign that number.

This job was built for an SSIS package (as its pointing to in the file path)

Thanks for any advice

Script code is:

USE [msdb]
GO

/****** Object:  Job [eLFCopy]    Script Date: 12/19/2013 11:30:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/19/2013 11:30:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'eLFCopy',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'This job copies eLF files & folders from stage locations to active directories.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'BIO\WDDocManagement', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RunPackage]    Script Date: 12/19/2013 11:30:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunPackage',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'SSIS',
        @command=N'/FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP1.WDDocumentManagement";"Data Source=BRORPORAP1;Initial Catalog=WDDocumentManagement;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION RCSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklySchedule',
        @enabled=1,
        @freq_type=8,
        @freq_interval=2,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20110701,
        @active_end_date=99991231,
        @active_start_time=100,
        @active_end_time=235959,
        @schedule_uid=N'c406cae9-49b7-40dd-9438-cef44160f562'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The other option to Aaron's excellent advice is to just remove @schedule_uid, the procedure will create a new UID for the schedule.  That is how I moved all the jobs from our old server to our new one with out issue.


Order of SQL Server backup restore An general doubt about how restore in done in SQL server

While performing restore, will the MDF and NDF will be restored first or the LDF files?

I agree with the very wise folks in the comments, who noted that this doesn't matter.

And, in terms of what's actually going on in the files, I don't know. None of the files is usable until the restore process is done. So, again, doesn't matter.

Now, what might matter, is when the files are created, and when they reach full size. Especially if the devs were looking at storage during the process.

Just did a quick test to confirm that what I thought I knew, I actually knew. And, as I thought, my MDF and LDF files were both created when the restore started, and are created at full size (whatever size they were before they were backed up). [Technically, the files are created at size 0, then inflated (still empty) to the proper size, then filled; however, the time between creation and inflation will usually be infinitesimally smaller than the time between the inflation and the completion of the filling]

I don't have backups with an NDF file to confirm with, unfortunately. However, I would expect that to behave similarly (with the possible exception of restoring a pre-2008 backup to a 2008+ server, which allows for the full-text indexes to be rebuilt or reset, if appropriate).


Effective indexing and querying of overlapping date/time ranges I have a table with the following schema:

AccountId: int
StartDateTime: datetime2(0)
EndDateTime: datetime2(0)

I want to be able to query this for rows with a certain AccountId, and whose date range intersects with a specified date range. E.g:

SELECT *
FROM ... AS d
WHERE d.AccountId = @AccountId
    AND d.StartDateTime <= @EndDateTime
    AND d.EndDateTime >= @StartDateTime

This will be a very large table and I'll want to be able to query it as efficiently as possible. I've specified a single composite index of AccountId, StartDateTime, and EndDateTime.

Will this index, and the above query work effectively together? If not, what would be a better alternative? Thanks.

If the intervals within a particular account do not overlap with each other, you can replace your query with a TOP (1) query, to find the last start date before your desired end date. Do the same but opposite to get your earliest end date after your desired start date. These will form a small range you can use to check.

If they do overlap within a particular account, then you have one of the classically hard problems, and I'd recommend you read the material that Dejan Sarka has written at http://solidqblogs.azurewebsites.net/en/businessanalytics/interval-queries-in-sql-server-part-1/ (and its follow ups).

The problem is that an indexes on either startdate or enddate are less than ideal, because either one alone doesn't give a clue about whether the interval reaches your desired range. Another strategy is useful instead, such as indexing values within the range (lots of storage, and you have to consider the granularity), or come up with a creative solution such as interval trees or spatial analytics.


Cumulative data and dates - my joins aren't working So I want to get cumulative data, for every day since a start date until an end date (or today). I have constructed a query that will get the data from a table, but for some reason, some dates are missing. My left join isn't working and I am not getting the desired results. Can anybody point out my mistake?

(also, as a small detail, how do I only get the date and not the timestamp?)

Current result (note the missing dates):

          date          |   sum
------------------------+----------
 2012-09-01 00:00:00+02 |    8.000
 2012-09-03 00:00:00+02 |   29.300
 2012-09-07 00:00:00+02 |   31.300

Expected result (a missing date behaves as distance 0, so sum shouldn't increase):

          date          |   sum
------------------------+----------
 2012-09-01 00:00:00+02 |    8.000
 2012-09-02 00:00:00+02 |    8.000
 2012-09-03 00:00:00+02 |   29.300
 2012-09-04 00:00:00+02 |   29.300
 2012-09-05 00:00:00+02 |   29.300
 2012-09-06 00:00:00+02 |   29.300
 2012-09-07 00:00:00+02 |   31.300

Query:

WITH dates  as (SELECT min(date) as start_date,
                       max(date) as end_date
                from training_training
                where athlete_id = 1)

SELECT  distinct(d.date),
        sum(distance) OVER (ORDER BY d.date)
FROM
      (
       SELECT generate_series(start_date, end_date, interval '1 day') as date
       FROM dates
      ) d
LEFT JOIN training_training
     ON         d.date = training_training.date
     WHERE athlete_id  =  1
     AND   kind        in ('t', 'd', 'i', 'w')

GROUP BY d.date, distance
ORDER BY d.date

As inspired by a_horse_with_no_name, I moved the right part of the join to a subquery so the where clause doesn't interfere with the join condition

This works as intended:

WITH dates  as (SELECT min(date) as start_date,
                       max(date) as end_date
                from training_training
                where athlete_id = 1)

SELECT  distinct(d.date),
   sum(distance) OVER (ORDER BY d.date)
FROM
  (
    SELECT generate_series(start_date, end_date, interval '1 day') as date
    FROM dates
  ) d
LEFT JOIN
  (
    SELECT
      date,
      distance
    FROM training_training
    WHERE athlete_id = 1
          AND kind IN ('t', 'd', 'i', 'w')
  ) t
ON d.date = t.date

GROUP BY d.date, distance
ORDER BY d.date


Efficiently store large list structure in RocksDB so that the data can be retrieved in pages Description:

RocksDB is a key-value storage so we can simply serialise the list of objects & store the value corresponding to a key. This would be ok if the data in the list is small enough.

But if the list is large and ever increasing in size then we would need the data paginated. So in this case storing the entire serialised list data corresponding to a single key would not be a good idea; as there would be a performance issue since every time a new data is inserted into the list this very large value would need to be read & updated also during read time when showing list to user entire value would be retrieved while only a part of it was needed by the user.

Ex: Let’s say we want to store orders placed by user in rocksDB. Then we could store this order data in following way in RockDB   “u:1:li:o” : Serialised([O1{}, O2{},….On{}]). But if there are thousands of orders placed by user and we would like to retrieve the orders in form of pages (10 or 20 records at a time). So storing thousands of order in same key and retrieving entire data from that key & then giving required 10-20 records won’t be a good idea. Also adding new order by user to same key will affect the performance as described above.

So I am working to design schema for efficiently storing and retrieving such large lists in RocksDB.

If you can give your suggestions on schema design that would be great & very much helpful.

you need to split your "large list" into a set of nodes of a "Segment B-Tree" and then store each node one as a RocksDB record.

Each node will need to store it's Height in the "Segment B-Tree" and it's left child ID.

This have a number of benefit

This will let you find if one entry is inside the list without
    having to scan the whole list.
Scanning all the leaf node would be fast because leaf node have
contiguous ID
This let you easily fetch any subset of the list using offset.

See : https://www.sqlite.org/fts3.html for more detail.


Conversion failed when converting date or time from character string error? i have a problem with this error i don't know why but i try many thing and search about it bout i didn't find any thing to solve this problem ( i want to make a query to calculate the age for the client auto in a column i have the Civil number for him like (286063001795) and i have some wrong civil No in my data ) the query is working good with little of data when i use it with my table gave me that error

UPDATE [dbo].[tabl]
set dob = CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))

select *, datediff(Month, dob, getdate())/12 as age, (CIVILIDD / 100000),17000000 + (CIVILIDD / 100000)
from [tabl]

  Name_Arabic   nvarchar(255)   Checked
  CIVILIDD    bigint  Checked
  gender  nvarchar(255)   Checked
  NATIONALITY nvarchar(255)   Checked
  dob date    Checked

set dob = TRY_CONVERT(date, convert(varchar(10), 17000000 + (CIVILIDD / 100000)))


Very strange GRANT permission issue I am having a very strange issue here with a web app that I have seen working fine in the past.

The server is a simple LAMP stack running MySQL 5.6.30.

SQLSTATE[HY000] [1045] Access denied for user 'my_user'@'localhost' (using password: YES)

The reason why this issue is causing me problems is because the user my_user has all privileges.

Here is the output of the SHOW GRANTS query on my MySQL server:

mysql> SHOW GRANTS FOR 'my_user'@'localhost';
+---------------------------------------------------------------
| Grants for my_user@localhost
+---------------------------------------------------------------
| GRANT USAGE ON *.* TO 'my_user'@'localhost' IDENTIFIED BY PASSWORD '******'
| GRANT ALL PRIVILEGES ON `db1`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `db2`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `db3`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `db4`.* TO 'my_user'@'localhost'
| GRANT ALL PRIVILEGES ON `my_db`.* TO 'my_user'@'localhost'
+---------------------------------------------------------------
6 rows in set (0.00 sec)

The database called my_db is the Database in question for this web app.

Does anyone have any suggestions on how I should go about diagnosing this issue?

I have confirmed that I am indeed looking at the right server with the correct user and what should be the correct password.

I am also unable to log in to MySQL CLI with this user.

SOLUTION:
Somehow the password for that user got modified/blasted. By resetting the password I have regained access.


Usefulness of Foreign Key WITHOUT reference option Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE

I think this will avoid INSERT of a value which does not exist in the parent table. Is there any other advantage?
Does this FOREIGN KEY assist a better JOIN on the corresponding key?
Without FOREIGN KEY a query merely deals with an independent table. Will FOREIGN KEY slow down the mysql performance (even trivial) due to the link with another table? e.g. in SELECT and INSERT.

Having built databases since before there were efficient database engines, I would recommend you create FOREIGN KEY constraints where required.

Presence or absence of ON DELETE and ON UPDATE does not avoid the INSERT requirement on the parent table.  They will influence what happens when the parent key is changed or removed.  The parent record will be required if the child table's key is not nullable.
The foreign key will enforce referential integrity and this will ensure that joins will work.  Without the foreign key it will be possible to enter values in the child table which do not exist in the parent table.  Outer joins to the parent table will fail for those records.
Query speed will not slow down due to the FOREIGN KEY.  There will be a minor impact on INSERT as the parent table's index will need to be check for presence of the parent record.  This is not a reason to sacrifice referential integrity.  (The cost to continually verify it by other means will usually outweigh the costs of the constraint.)  The cost of cleaning up the referential integrity after the fact will greatly exceed the savings.

I avoid the ON DELETE and ON UPDATE actions in FOREIGN KEY definitions.  I haven't yet designed a database where they are necessary.  These two actions control what happens to the child records when the parent changes.  ON DELETE can be extremely destructive if a frequently referenced parent is unintentionally deleted.  ON UPDATE is only useful where the parent key can change.  Use of surrogate keys eliminates the need for ON UPDATE actions.

If the reverence to the parent table is always from the child it might be appropriate not to index the foreign key column in the child table.


Sargability of queries against a view with a case expression I have a view that looks similar to this:

CREATE VIEW Shipment.Shipment AS

    SELECT  CASE
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId,

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
GO

Then if I do a query like this:

SELECT * FROM Shipment.Shipment WHERE ShipmentId = 18140

I get a full index scan.  The reason why is that it is running the case expression for ShipmentId on all the rows in the table and comparing the result to my ShipmentId (18140).  The very definition of non-sargable.

I am using this view to normalize legacy data so I can write a new application on top of it.  I just want the case expression to run on output.

I am guessing that is not possible, but I thought I would ask before I pursue more extreme options.  So here is my question:

Is it possible to still have my ShipmentId output go through the case expression, but also have a sargable query when ShipmentId is used in the where clause?

Is it possible to still have my ShipmentId output go through the case
  expression, but also have a sargable query when ShipmentId is used in
  the where clause?

No.  SQL Server doesn't do that for you.

This is where you might use an inline Table-Valued Function, sometimes called a "parameterized view".  EG

CREATE function GetShipments ( @ShipmentId int )
returns table
AS return

    SELECT  CASE
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId,

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
    where  SHIPMENT_ID = @ShipmentID

Or add an extra column to your view.


I did an upgrade to percona mysql 5.6 and it solves a problem. Both type of queries runs equally. Fortunately - equally fast.


Where .dmp file is restored after import in oracle? I am new to oracle and I have abc.dmp file at c:\abc.dmp location. I want to import this abc.dmp file. I am using the following command.

c:\>imp system/mypassword file=c:\abc.dmp full=y

I am using Toad for Oracle 12.1 Version. Now after import has been done I don't know where this db has been restored.

I have also created a new user and used the following command.

c:\>imp system/mypassword file=c:\abc.dmp fromuser=system touser=testuser2014.

After this commend has been executed, I don't know where the db is restored.

Please help me.

Thanks in advance

Thanks. I have imported dmp file by following steps..

Command to open SQLPLUS

In Command Prompt:
C:> sqlplus / as sysdba

Clear Screen:

SQL> Clear Screen

1- First create a new user by

SQL> Create user TestUser1 identified by test; -- (test is password)

2- Grant Privilages to new created user TestUser

SQL> Grant dba, IMP_FULL_DATABASE, EXP_FULL_DATABASE to TestUser;

Optional:

You can check which users have the above three grants

SET lines 100 --May not work
COL privilege FOR a50 -- May not work
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2; -- May not work

3- Check TableSpace for this newly created user

SQL> select * from dba_users

Note: here you will see that the default DEFAULT_TABLESPACE for TESTUSER is USERS

4- Create a new TableSpace for this newly created user "TestUser"

SQL> CREATE TABLESPACE tbs_testuser1
    DATAFILE 'tbs_testuser1.dat'
        SIZE 256M REUSE
        AUTOEXTEND ON NEXT 256M MAXSIZE 2048M

    ONLINE;

5- Now Connect this newly created tablespace  'tbs_testuser1' to 'testuser1'.

SQL>  ALTER USER TESTUSER1 default tablespace tbs_testuser1 quota unlimited on tbs_testuser1;

6- Check whether your 'tbs_testuser1' tablespace is connected to user or not.

SQL> select * from dba_users

7- In command prompt:

c:\>imp system/password file=File Path.dmp log=LOGFileName.log fromuser = <UserName of User from which file is imported> touser=testuser1


The logical order of execution plan of the SQL query with more than one join I know the logical order of execution of the SQL query which is:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

What will happen if there are more than one join in the query for instance if we have a query like this:

SELECT *
FROM user_branch T1
INNER JOIN dimcustomer2 T2
   ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
   ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM

some example data:

customer_guarantee:    CUSTOMER_NUM      BRANCH_CODE
                      -------------------------------
                           A                X
                           B                X
                           C                Y
                           D                Z

 user_branch:           USER_ID          BRANCH_CODE
                      --------------------------------
                           U1               Y

 dimcustomer2:         CUSTOMER_NUM      BRANCH_CODE
                      --------------------------------
                           A                Y
                           B                Y
                           C                Y
                           D                Z

How will this execute? Which join will be executed first? And what if there are
different kind of joins in the query? What would be the order of executing joins in that case? Thanks in advance.

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join:
SELECT *
FROM user_branch T1
LEFT  JOIN dimcustomer2 T2
   ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
   ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM

Let us assume that some rows in T1 have no matches in T2. More specifically, let us assume these are the three tables:
    T1                         T2                           T3

BRANCH_CODE          BRANCH_CODE  CUSTOMER_NUM          CUSTOMER_NUM
-----------          -----------  ------------          ------------
11                   11           230                   120
12                   12           235                   170
13                   15           260                   230
14                                                      235
15                                                      245
                                                        250
                                                        260
                                                        270

There are two joins here and two possibilities in which order they are executed.
1. LEFT JOIN, then INNER JOIN
If the left join evaluates first, then its result will have nulls in the T2 columns where T1 rows had no match:

T1.BRANCH_CODE  T2.BARNCH_CODE  T2.CUSTOMER_NUM
--------------  --------------  ---------------
11              11              230
12              12              235
13              (null)          (null)
14              (null)          (null)
15              15              260

Joining that result further with T3 using an inner join on a condition that uses a T2 column will eliminate the non-matches – and, therefore, corresponding T1 rows, – because a null cannot satisfy the join's equals condition:

T1.BRANCH_CODE  T2.BARNCH_CODE  T2.CUSTOMER_NUM  T3.CUSTOMER_NUM
--------------  --------------  ---------------  ---------------
11              11              230              230
12              12              235              235
15              15              260              260

This way some of T1 rows will be excluded from the final result set.
2. INNER JOIN, then LEFT JOIN
Now if the inner join is executed first, then it will produce a result set containing the rows from T2 and T3 that match the inner join's condition:

T2.BARNCH_CODE  T2.CUSTOMER_NUM  T3.CUSTOMER_NUM
--------------  ---------------  ---------------
11              230              230
12              235              235
15              260              260

When this result set is then outer-joined to T1, T1 being on the outer side, you will get a final result containing all the rows from T1 and those from the T2-T3 inner join that match the outer join condition:

T1.BRANCH_CODE  T2.BARNCH_CODE  T2.CUSTOMER_NUM  T3.CUSTOMER_NUM
--------------  --------------  ---------------  ---------------
11              11              230              230
12              12              235              235
13              (null)          (null)           (null)
14              (null)          (null)           (null)
15              15              260              260

Therefore, this second interpretation would mean that all T1 rows should be present in the result.

Since these two interpretations give such different results, it is clear that only one can be true. Executing the query, you will see that actually it is the first one. That means that logically, the joins are executed in the order they are specified in the FROM clause.
Syntax variations
Note that the conclusion above applies to the most conventional join syntax, namely this:
FROM
  T1
  ... JOIN T2 ON ...
  ... JOIN T3 ON ...
  ...

Your example matches that pattern, so the conclusion applies to it as well. However, there are variations worth mentioning where our conclusion does not apply, or at least not as straightforwardly.
1. Nested JOIN syntax
Syntactically, a join may be specified inside another join, like this:
FROM
  T1
  JOIN
    T2
    JOIN T3 ON ..
  ON ...

In the above case, JOIN T2 is encountered before JOIN T3. However the former join's declaration is not complete at that point: its ON subclause is the one at the end and is logically evaluated only after the JOIN T3 ON ... part. So in this case, T2 is joined to T3 first, then the result of the join is joined to T1.
You could still argue that our conclusion stands here, although it is not as clear-cut in this situation. We concluded that the joins are evaluated in the order they are specified in the FROM clause. In this example, the first join we encounter as we parse the FROM clause, is not yet completely specified by the time the second one is.
2. Mixing comma joins and conventional joins
Prior to introduction of the explicit JOIN syntax, joins were specified like this:
FROM
  T1,
  T2,
  T3
WHERE
  <joining conditions, filter conditions>

This type of join, sometimes referred to as comma join, is still supported by most, if not all, platforms, including SQL Server.
Without a joining condition, a comma join is essentially a cross join. A joining condition makes it an inner join. You can see, though, that the joining condition in this case comes in an entirely different clause, the WHERE clause.
Now, SQL Server allows you to mix comma joins and conventional joins in the same FROM clause. When you have a mix of joins like this:
FROM
  T1,
  T2
  JOIN T3 ON ... ,
  T4 ...

SQL Server will evaluate each individual comma-separated item independently of the others before cross-joining them all together. So in the above case, the T2 JOIN T3 ON ... join will be evaluated before its result is cross-joined to T1 (to be further filtered down by any joining condition that might be found the WHERE clause). Our conclusion does not apply at all here. However, you can see that a very different syntax comes into play in this case.
I am discussing the mixed syntax in slightly more detail in my answer on Stack Overflow: The multi-part identifier could not be bound.


Syntax error when trying to import database from two PostgreSQL databases Following on from this prior question, I'm trying to export a database from one server and put it on another using phpPgAdmin. My process is like this:

select the database in server 1 and hit the export button
select "data and structure and select the option in the dropdown "copy"
download
create the a database of the same name in server 2 with the same name as the database from server 1
select that database, open up the SQL pane, and paste the SQL code I downloaded from server 1
Execute

That's when I get the error:

ERROR:  syntax error at or near "OWNED"
LINE 73: ALTER SEQUENCE address_customer_id_seq OWNED BY address.cust...

Line 73 in its entirety is:

 ALTER SEQUENCE address_customer_id_seq OWNED BY address.customer_id;

I've read here that it may be because I've have slightly different versions of PostgreSQL on my two servers. So I checked with the server tech, who said that, yes, there was that issue. So he upgraded server 2. I'm getting the same error however.

Any idea what could be going on?

You seem to have omitted the new version of the upgraded server, but I strongly suspect it's still on 8.1 since the ALTER SEQUENCE ... OWNED BY clause was added in 8.2 (Compare ALTER SEQUENCE docs on 8.2 to ALTER SEQUENCE docs on 8.1).

You really need to understand that 8.4 and 8.1 aren't slightly different versions, they're massively different. It's like saying that Windows Vista and Windows 7 are "slightly different" versions because they're Windows versions 6.0 and 6.1 respectively. Or that Mac OS X 10.4 and 10.7 are "slightly different"... when they're more incompatible than not.

Yes, I know the version numbering in PostgreSQL is stupid; it's a historical oddity we seem to be stuck with despite periodic attempts to change it. See the version policy document linked in the prior answer for more information about what the versioning means, and read the release notes for a better understanding.


Find total duration of each consecutive series of rows MySQL Version

The code will run in MySQL 5.5

Background

I have a table like the following one

CREATE TABLE t
( id INT NOT NULL AUTO_INCREMENT
, patient_id INT NOT NULL
, bed_id INT NOT NULL
, ward_id INT NOT NULL
, admitted DATETIME NOT NULL
, discharged DATETIME
, PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

This table is about patients in a hospital and it stores the beds where each patient spent some time while being hospitalized.

Each ward may have multiple beds and each patient may move to a different bed within the same ward.

Objective

What I want to do is to find how much time each patient spent in a specific ward without having moved to a different ward. I.e I want to find the total duration of the consecutive time he spent within the same ward.

Test case

-- Let's assume that ward_id = 1 corresponds to ICU (Intensive Care Unit)
INSERT INTO t
  (patient_id, bed_id, ward_id, admitted, discharged)
VALUES

-- Patient 1 is in ICU, changes some beds, then he is moved
-- out of ICU, back in and finally he is out.
(1, 1, 1, '2015-01-06 06:05:00', '2015-01-07 06:04:00'),
(1, 2, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(1, 1, 1, '2015-01-07 07:08:00', '2015-01-08 08:11:00'),
(1, 4, 2, '2015-01-08 08:11:00', '2015-01-08 09:11:00'),
(1, 1, 1, '2015-01-08 09:11:00', '2015-01-08 10:11:00'),
(1, 3, 1, '2015-01-08 10:11:00', '2015-01-08 11:11:00'),
(1, 1, 2, '2015-01-08 11:11:00', '2015-01-08 12:11:00'),

-- Patient 2 is out of ICU, he gets inserted in ICU,
-- changes some beds and he is back out
(2, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(2, 1, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(2, 3, 1, '2015-01-07 07:08:00', '2015-01-08 08:11:00'),
(2, 1, 2, '2015-01-08 08:11:00', '2015-01-08 09:11:00'),

-- Patient 3 is not inserted in ICU
(3, 1, 2, '2015-01-08 08:10:00', '2015-01-09 09:00:00'),
(3, 2, 2, '2015-01-09 09:00:00', '2015-01-10 10:01:00'),
(3, 3, 2, '2015-01-10 10:01:00', '2015-01-11 12:34:00'),
(3, 4, 2, '2015-01-11 12:34:00', NULL),

-- Patient 4 is out of ICU, he gets inserted in ICU without changing any beds
-- and goes back out.
(4, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(4, 2, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(4, 1, 2, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),

-- Patient 5 is out of ICU, he gets inserted in ICU without changing any beds
-- and he gets dismissed.
(5, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(5, 3, 2, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(5, 1, 1, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),

-- Patient 6 is inserted in ICU and he is still there
(6, 1, 1, '2015-01-11 12:34:00', NULL);

In the real table the rows are not consecutive but for each patient the discharge timestamp from one row == the admission timestamp of the next row.

SQLFiddle

http://sqlfiddle.com/#!2/b5fe5

Expected Result

I would like to write something like the following:

SELECT pid, ward_id, admitted, discharged
FROM  (....)
WHERE ward_id = 1;

(1, 1, '2015-01-06 06:05:00', '2015-01-08 08:11:00'),
(1, 1, '2015-01-08 09:11:00', '2015-01-09 11:11:00'),
(2, 1, '2015-01-07 06:04:00', '2015-01-08 08:11:00'),
(4, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(5, 1, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),
(6, 1, '2015-01-11 12:34:00', NULL);

Please, do take note that we can't group by patient_id. We must retrieve a separate record for each ICU visit.

To put it more plainly, if a patient spends time in ICU, then moves out of it and then returns back there, I need to retrieve the total time he spent in each ICU visit (i.e. two records)

Query 1, tested in SQLFiddle-1

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,          -- the first bed a patient uses
                                           -- can be omitted
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
  ( SELECT patient_id, bed_id, admitted, discharged
    FROM t
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT *
            FROM t AS prev
            WHERE prev.ward_id = @ward_id_to_check
              AND prev.patient_id = t.patient_id
              AND prev.discharged = t.admitted
          )
  ) AS st
JOIN
  ( SELECT patient_id, admitted, discharged
    FROM t
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT *
            FROM t AS next
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = t.patient_id
              AND next.admitted = t.discharged
          )
  ) AS en
    ON  st.patient_id = en.patient_id
    AND st.admitted <= en.admitted
GROUP BY
    st.patient_id,
    st.admitted ;

Query 2, which is the same as 1 but without the derived tables. This will probably have better execution plan, with proper indexes. Test in SQLFiddle-2:

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
    t AS st    -- starting period
  JOIN
    t AS en    -- ending period
      ON  en.ward_id = @ward_id_to_check
      AND st.patient_id = en.patient_id
      AND NOT EXISTS
          ( SELECT *
            FROM t AS next
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = en.patient_id
              AND next.admitted = en.discharged
          )
      AND st.admitted <= en.admitted
WHERE
      st.ward_id = @ward_id_to_check
  AND NOT EXISTS
      ( SELECT *
        FROM t AS prev
        WHERE prev.ward_id = @ward_id_to_check
          AND prev.patient_id = st.patient_id
          AND prev.discharged = st.admitted
      )
GROUP BY
    st.patient_id,
    st.admitted ;

Both queries assume that there is a unique constraint on (patient_id, admitted). If the server runs with strict ANSI settings, the bed_id should be added in the GROUP BY list.


Attempt to find Transitive Dependencies Table From this table example I am trying to understand both this dependencies. According to me partial dependencies have a primary composite key and transitive dont have.

I try to figure out Partial dependenceis but not sure, but i cant find the transitive one  Try to figure it out. Orange one are Primary key.

Functional dependencies are determined from the subject matter.  I'm inferring the FDs here from the names of the columns.

The key is Ord_NO and Prod_ID. I didn't include CUS_ID in the primary key, because it depends on   Ord_NO.

Name depends on CUS_ID which depends on Ord_NO.

This is a transitive dependency.

The only non key column which depends on the whole key is Qty_Ord.  All the rest are partial dependencies.

Edit:  I revised my answer to make the Column Names agree with the ones in the sample data.

The sample data is so small as to be anecdotal.  Deriving FDs from such a small sample is risky, at best.  You run the risk of seeing as patterns what are in reality mere coincidences.

Based on the Column names and an intuitive notion of how orders work, here are the FDs.

 Cus_ID -->  Name
 Ord_NO --> Ord_Date
 Ord_NO --> Cus_ID
 Prod_ID --> Description
 Ord_NO, Prod_ID --> Qty_Ord

While these are consistent with the sample data, they are NOT derived from it.  As I already said, that's risky with such a small sample.


SQL Server 2017: How does Batch Mode Memory Grant Feedback Work? Batch Mode Memory Grant Feedback is part of a family of features in the 2017 query processor that consists of:

Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback

So how does Batch Mode Memory Grant Feedback work?

What's Memory Grant Feedback All About?

Batch Mode Memory Grant Feedback attempts to right-size memory grants for queries, correcting for both over- and under-estimates.

When a query runs that requires a memory grant, the optimizer will ask for a grant of a size that it thinks will keep all row operations in memory.

Things that commonly require memory grants:

Sorts
Hash Joins

When too much memory is asked for and granted, concurrency may suffer as a result. Memory is a finite resource, and not everything can use all of it all the time. There's no such thing as unlimited access to such resources.

When too little memory is asked for, queries may spill to disk. Look, no one wants their queries spilling anywhere. Disk is dreadful.

How Does The Magic Work?

When a plan that requires a memory grant executes and is cached, actual memory needed to run the plan is recalculated, and the plan information is updated accordingly. Right now, it requires the presence of a ColumnStore index to achieve Batch execution mode.

What If The Magic Isn't So Magical?

This feature does have a terminating point in which it will fall back to the original memory grant. If queries run that need constant recalculating, our magical feature will give up. Eventually. As of this writing, I don't have all of the implementation details on when it will quit.

How Do I Know If It's Working?

You can use Extended Events:

You may also observe it during regular query tuning over multiple runs in the actual execution plan.

Can You Show Me An Example?

Of course! Here's a stored procedure. Under the right circumstances, it will ask for an incorrect memory grant.

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant (@OwnerUserId INT)
AS
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts_cx AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

The query plan has a warning on the select operator.

Sadness indeed! Our query asked for too much memory.

On second execution, the warning will disappear.

I've Heard About Some Sneaky Tricks...

Itzik Ben-Gan and Niko Neugebauer have both come up with workarounds to features and operators that require Batch Mode execution.

Create a filtered, non-clustered ColumnStore index with a WHERE clause that contains 0 rows
Join your table to a temp table with a Clustered ColumnStore index on it with no rows using a superfluous Left Join

Both methods are valid workaround to get this to work!

Creating an empty nonclustered ColumnStore index:

/*Itzik's Trizik*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_helper
    ON dbo.Posts
(
    Id,
    AcceptedAnswerId,
    AnswerCount,
    ClosedDate,
    CommentCount,
    CommunityOwnedDate,
    CreationDate,
    FavoriteCount,
    LastActivityDate,
    LastEditDate,
    LastEditorDisplayName,
    LastEditorUserId,
    OwnerUserId,
    ParentId,
    PostTypeId,
    Score,
    ViewCount,
    IsHot )
    WHERE ( Id = -2147483647 AND Id = 2147483647) -- eez impossible!

Running a different version of the proc against the table with the empty index:

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt1 (@OwnerUserId INT)
AS
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

Using the same value:

EXEC dbo.LargeUnusedGrant_alt1 @OwnerUserId = 8672;
GO

First run: sad memory grant

Second run: happy memory grant

Superfluous left join:

/*Niko's Triko*/
CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt2 (@OwnerUserId INT)
AS
BEGIN

    CREATE TABLE #t1 (Id INT, INDEX cx_whatever CLUSTERED COLUMNSTORE);

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    LEFT JOIN #t1 ON 1 = 1
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

EXEC dbo.LargeUnusedGrant_alt2 @OwnerUserId = 8672;
GO

First run: sad memory grant

Second run: happy memory grant


Database design. And get last modified time with each column in PostgreSQL My database/table each endpoint/scope  have multiple table,
e.g user:  user, user_information, user_role ...
I was wondering should I separate table like below, or not?

This is my first time to build a product not like before just make some onetime  work small website. and this it might add more feature in future version.

I'm not sure about is this overdesign?  Does separate table like this any benefit in the future ??
and cons I know is use more table join, and more difficult to me to maintain build the query.

any suggestion, share experience will be really appreciate.

For now I only can imagine I might want to know each column last modified time?

So if I want to know each column last modified time, is there any original build method in PostgreSQL? or I have to add for each column like email_last_modified_date, username_last_modified_date ...

endpoint/scope user

CREATE TABLE IF NOT EXISTS "user"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "status" integer NOT NULL,
    PRIMARY KEY ("id")
  );

CREATE TABLE IF NOT EXISTS "user_information"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "user_id" integer NOT NULL,
    "email" varchar(100) NOT NULL,
    "username" varchar(50),
    "password" varchar NOT NULL,
    "first_name" varchar(50),
    "last_name" varchar(50),
    "website" varchar,
    "description" varchar,
    "birth_date" timestamp without time zone,
    "country" varchar(50),
    "gender" integer,
    "file_type" integer,
    "file_name" varchar(50),
    "file_extension" varchar(50),
    "file_portrait" boolean,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("id") ON DELETE CASCADE ON UPDATE CASCADE
  );
CREATE TABLE IF NOT EXISTS "user_role"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "user_id" integer NOT NULL,
    "role" integer NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("id") ON DELETE CASCADE ON UPDATE CASCADE
  );

merge table ?

CREATE TABLE IF NOT EXISTS "user"(
    "id" SERIAL NOT NULL,
    "create_date" timestamp without time zone NOT NULL,
    "last_modified_date" timestamp without time zone,
    "last_modified_by_user_id" integer,
    "status" integer NOT NULL,

    "information_last_modified_date" timestamp without time zone,
    "information_last_modified_by_user_id" integer,
     .... user_information

    "role_last_modified_date" timestamp without time zone,
    "role_last_modified_by_user_id" integer,
    ... user_role
    PRIMARY KEY ("id")
  );

I was wondering should I separate table like below, or not?

Well, one immediately obvious downside of the table design you posted is that there are no constraints on user_information or user_role ensuring that you don't have more than one such row in each of these tables for each user. And even if you remember to add that, you will still have the problem that it will be difficult or impossible to enforce the rule "every user must have a role (or other critical attribute)".
Where it typically makes sense to break out metadata like this is when you have either many-to-one relationship of this metadata (e.g. a single user may have multiple roles), or you have a lot (i.e. many columns) of optional metadata that is unwieldy to cram into a single table.

For now I only can imagine I might want to know each column last modified time?
So if I want to know each column last modified time, is there any original build method in PostgreSQL? or I have to add for each column like email_last_modified_date, username_last_modified_date ...

You'll have to create individual columns (e.g. "email_last_modified_date") and have a trigger function enforce that the column is set correctly to achieve this. A more flexible and useful approach is usually constructing an audit table, with a trigger on the "user" table saving the changed columns for every UPDATE, or the contents of the whole row for every DELETE. This is where json, jsonb, or hstore types come in handy, if you'd like a single audit table capable of dealing with several tables, or capable of dealing with many columns from a single user table without needing to know the structure of the user table.


postgres row to column I have this pretty straightforward SQL query

SELECT count(date_mdy) AS unique_date_count,
  county,
  date_s
FROM (
  SELECT date_mdy,
    county,
    date_s
  FROM air_quality2
  GROUP BY county, date_s ,date_mdy
  ORDER BY county, date_s
) AS t1
GROUP BY county, date_s
ORDER BY date_s;

this returns

I want the county names to be the column headers. I know there is a crosstab function and I have seen the many posts on here where they use it but I cannot get it to work on this query.

As I said, simplify it using count(DISTINCT...)

SELECT county,
  date_s,
  count(DISTINCT date_mdy) AS unique_date_count
FROM air_quality2
GROUP BY county,
  date_s
ORDER BY 1,2;

Now wrap it in crosstab

SELECT *
FROM crosstab($$
    SELECT county,
      date_s::int,
      count(DISTINCT date_mdy) AS unique_date_count
    FROM air_quality2
    GROUP BY county,
      date_s
    ORDER BY 1,2;
  $$,
  $$ SELECT DISTINCT county FROM air_quality2; $$
)
AS ct(
  year               int,
  "Bergen County"    int,
  "Essex County"     int,
  "Hunterdon County" int,
  "Middlesex County" int,
  "Monmouth County"  int,
  "Morris County"    int,
  "Union County"     int
);

Update

forgot to put the row_name year in the definition.
You may have to cast date_s to int
You may have to cast count(DISTINCT...) to int, (or bigint and change the types).
You will have to make sure you gave all the counties in your sample data. Or, paste the results of SELECT DISTINCT county FROM air_quality2;.


Index and Heap table storage on disk I have a few questions on tables and Indexed tables and its storage, Here is my first image where i have taken an example with Northwind database and Table name is Orders. Orders Table is with 9 indexes and Orders1 table has no indexes which i had replicated with statement(select * into orders1 from orders).

I had checked the space of table Orders and Orders1 where i found the index size is of Orders Table is more than data size and in Orders1 Only i can find Data Size is less.
Question 1:Is index size in orders table is cumulative of 9 indexes? or Its a single clustered index(as shown in below Fig.2)
Question 2:Each index partition shown below in Fig.2 has 830 rows is that mean to have duplicate page data on disk or memory?
Question 3:Does Non-Clustered index occupy space on disk or only Clustered Index?
Question 4:How does fill factor effect on storage of these clustered and non-clustered indexes.
How does index occupy size on disk(any references links/books/blog)?

Answers are welcome and greatly appreciated.

Best Regads,
Siva

First, some definitions:

A clustered index is the data. When you see the size of a clustered index, that's the size of the table itself.
A heap is a table that does not contain a clustered index.
A non-clustered index contains the clustered index (or a row identifier if there is no clustered index), so that it can map back to the right row in the table, plus whatever column(s) you're indexing.

It's quite possible for the total size of non-clustered indexes to be larger than the table itself.

Let's answer your questions.

  Is index size in orders table is cumulative of 9 indexes? or Its a single clustered index(as shown in below Fig.2)

This is the cumulative total of all non-clustered indexes (keeping in mind that each of them contains a copy of the clustered index).

  Each index partition shown below in Fig.2 has 830 rows is that mean to have duplicate page data on disk or memory?

Maybe. SQL Server does not care if you create duplicate indexes, but just having the same number of rows does not make it a duplicate. Read more here about determining if an index is a duplicate.

The reason there are 830 rows in each index, is because the table has 830 rows, so each index contains the same number of rows, each of which maps back to the row in the clustered index (depending on which column(s) are indexed).

  Does Non-Clustered index occupy space on disk or only Clustered Index?

All indexes consume space. A clustered index is the data in the table, and each non-clustered index is a copy of whatever data that it is indexing.

  How does fill factor effect on storage of these clustered and non-clustered indexes.

This means that a percentage of space is left on each 8KB data page, to try reducing fragmentation. See link below about data pages.

  How does index occupy size on disk(any references links/books/blog)?

The index is a data page like any other page in the database. It has a header, a slot array, and data in the middle that points to other data pages, and eventually gets to the leaf node where the data sits. You can read more about data pages here. Once you understand that, you can read about index pages here.


How can I implement a quick backup and restore strategy for database script testing? I've got a copy of our 130GB production database restored on our development SQL Server 2008 R2 Standard Edition 64-bit.

I'm developing some test scripts, which need to de-duplicate a lot of data that has been created in error, so I've got a copy of the database in order to develop and test the de-duplication scripts.

The full restore just took 45 minutes and I want to be able to test my script, then quickly restore the database back to the initial state, then fix any issues with my script and re-test the improved database script.

Since I'm not on Enterprise Edition snapshot backups is not available to me.

What backup and restore strategy could I use to prevent this 45 minute bottleneck? If there is one available, please can you suggest a script for the backup and restore?

Since you are limited by Edition, I agree with @MartinSmith that you can use Dev edition (to create snapshot and revert when you have to rollback) since you are only testing and no one is connecting or using the data except you - provided you have enough space and memory. Also, I assume that 130GB is compressed backup !

Also, restoring a 130 GB database takes 45 mins .. you need to check if Instant file initialization is enabled or not (should be enabled). Also, you can play with BUFFERCOUNT and MAXTRANSFERSIZE parameters.


Does Active Data Guard include Golden Gate? I've been told that Active Data Guard incorporates Golden Gate as part of its technology stack.

Is Golden Gate usable directly from the Active Data Guard installation?

It's the other way around. Active Data Guard is included if you license GoldenGate on Oracle Database Enterprise Edition.

The GoldenGate product page on Oracle Technology Network states:

  The license for Oracle GoldenGate includes a full use license for
  Oracle Active Data Guard, and a full use license for XStream in the
  Oracle Database.


Rock-Paper-Scissors-Spock-Lizard statistics DB design, is there a better way to achieve this? I am working in a player versus computer web application which is a version of "Rock-Paper-Scissors-Spock-Lizard".

I should be able to provide some statistics like for example:

How many times the user has won, lost and draw?
How many times the computer has won, lost and draw?
How many times the user using the player Lizard has won, lost and draw?
How many times the computer using the player Lizard has won, lost and draw?

And basically the same goes to each player type.

I have made this small DB containing only two tables but I am not sure of it because I need to make a lot of COUNT queries to obtain what I am looking for.

Posibles values for result are:

0: user won
1: computer won
2: draw

And the player types are as follow:

1: Lizard
2: Paper
3: Rock
4: Scissors
5: Spock

So for example, to find "how many times the user has won, lost and draw" I need to run the following queries:

SELECT COUNT(*) FROM match WHERE result=0; // won
SELECT COUNT(*) FROM match WHERE result=1; // lost
SELECT COUNT(*) FROM match WHERE result=2; // draw

But if I want to find "how many times the user using the player Lizard has won, lost and draw" then I need to run the following queries:

SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=0; // won using Lizard
SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=1; // lost using Lizard
SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=2; // draw using Lizard

And I need to keep repeating the same queries all the time for get those types of statistics.

Is there any other approach or a better way to achieve this? I am using MySQL 5.6.

To increase efficiency, you could run the query and get all 3 results at once:

SELECT COUNT(*) AS cnt, result
FROM match
GROUP BY result

That should give you 3 rows in this situation, which you can deal with in the application layer however you want.

As by the example above, you would get something like the following:

Cnt | Result
10 | 0
15 | 1
25 | 2

I would personally say do the following with structure:

The Matches table holds one record per match, with references to each other table.

The Player table holds one record for each player (Whether they are a computer or user), with a reference to the PlayerType table

The PlayerTypes table holds the ID and the type of player (User, or computer etc)

The ResultTypes table holds a record for each possible result of a match, with the TypeName column being the text for that result (IE 'Won', 'Loss' etc)

I've not added any foreign keys etc - should be fairly easy to work out from the names though. http://sqlfiddle.com/#!9/865078

The below query would then give you your result:

SELECT COUNT(Matches.ID), ResultTypes.TypeName
FROM Matches
INNER JOIN ResultTypes ON Matches.ResultID = ResultTypes.TypeID
GROUP BY ResultTypes.TypeName