Primary key addition for a table which contatins no data is too slow We are using mysql. We are running 10 alter queries which contains 3 Primary key deletion and 4 column addition and 3 primary key addition. And this alter query is on three tables which don't contain any data. To run this query system took 15s. Usually system wont take this much for alter query. But for a table which contains no data I am not sure why it is taking this much of time.

I am not sure whether it is because of primary key addition and deletion or any mysql configuration is wrong or because of the machine.

While this migration alone system is slow. After and before the migration is system is behaving as normal.

Can anyone help me on this one?

Below are the queries. I was running three sets like this.

ALTER TABLE Table1 DROP PRIMARY KEY;

ALTER TABLE Table1 ADD Column3 INT(10) DEFAULT ‘1';

ALTER TABLE Table1 ADD PRIMARY KEY Table_PK(Column1, Column2 , Column3 );

You are running in Windows, correct?  Perhaps an older version of Windows and an older version of MySQL?  File operations are slow.  ALTER TABLE, especially when changing the PRIMARY KEY, requires creating 1-3 files, copying over some stuff (not much in your case), removing the old 1-3 files, then renaming the files.

ALTER on an empty table is not a priority.  However, some improvements were made in 5.6 on Windows.  Still, I suggest that Windows file management is the main culprit. Your code may have added up to 27 system calls for file manipulation.  15 seconds seems about right for Windows.

You could speed the process up by combining the statements:

ALTER TABLE Table1
    DROP PRIMARY KEY,
    ADD Column3 INT(10) DEFAULT '1',    -- (I fixed the syntax error here)
    ADD PRIMARY KEY Table_PK(Column1, Column2 , Column3 );

With few exceptions, you can combine multiple ALTERs for a single table into a single ALTER.  And, unless all can be run with ALGORITHM=INPLACE, it is faster to combine them.

Consider using INT UNSIGNED for Column3.  And/or some smaller type of INT, such as SMALLINT.


Performance of mysql equi-join observed in HDD and SSD I experimented performance of queries having equi-joins. I have simplified version of EXPLAINs of the queries.

Mysql version : 5.7.18

Engine : Innodb

Cache state : Mysql restarted before experiment so no buffer pool cache. Since, physical machine is restarted before every experiment there won't be OS cache.

Note : Primary key is nothing but an increasing INT.

CPUs: SSD machine is a Mac machine with 2.5 GHz Intel Core i7.
      HDD machine is a Mac machine with 2.3 GHz Intel Core i7

Case 1:

| id | select_type | table | partitions | type   | possible_keys | key     |  ref             | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+--------+----------+----+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY |  const,const     | 190864 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY |  c.id.           |      1 |    10.00 | Using where |

Case 2:

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+-------
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows   |Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+------+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY | 771     | const,const                 | 190864 |Using index
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY       | PRIMARY | 775     | const,const,c.id            |      1 |Using index
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+

I both cases it can be noted that:

the first table having 190864 rows ready to be joined to second table
the second table is about to join in 'eq_ref' order.

Interpretation:
 Each row from first table is going to find its pair in second table. Since there are 100k rows in first table , there will be 100k i/o to disk in random fashion (i.e., simply random i/o correct me if I were wrong). I have run this experiment with HDD and SSD.

Results (time taken):

Case 1 : 3.409s (SSD) and 26.651s (HDD)

Case 2 : 0.248s (SSD) and 1.364s (HDD)

It can be seen that there is a huge difference in performance. Is this because of that SSD are better at random I/Os? or simply SSDs are better at I/O itself?

I am excited to hear reliable comparisons between HDD and SSD, but first...

Here are some of the variables you have not accounted for.  (Or maybe you did, but failed to state.)

InnoDB?
One says "Using index", meaning that the processing happened only in the index's BTree ("Covering" index); the other apparently had to bounce between the secondary index and the data.  This could make a significant difference.
Table sizes?
Was the cache (buffer_pool) cold?  Please restart mysqld before each test.
Same CPU, since you want to focus on the I/O, not the CPU time.
If it was the same server, which drive was c on?
SHOW CREATE TABLE -- there could be subtle issues.
MySQL version.
Are the indexes aligned with the data?  For example:  AUTO_INCREMENT and TIMESTAMP might be virtually in lock-step.  But UUID is very random.

8x and 5.5x seem to be reasonable.


Oracle SecureFile - DBFS If I create a table that has a SecureFile column, does it mean that all the LOBs put into the column are automatically saved as files? In other words, DBFS and SecureFiles go hand in hand mandatorily or SecureFiles can exist without DBFS too?

No.

The words Secure Files should be interpreted that files can safely be stored in the database. This is often safer than storing them on the FileSystem with a reference from the database. If the file is in the database it is also protected by many of the database features. SecureFiles is the modern implementation of LOB's.
The new implementation has a great performance boost compared to the old inplementation.

dbfs is a fileystem that can be created inside the database and that can be mounted on Linux using dbfs_client. Regular operating system processes see it as a regular POSIX filesystem. The LOB's in a dbfs should be implemented as SecureFiles to have the best performance. The performance of dbfs is better than that of NFS, if using filesystem like logging. With normal logging dbfs has about the same performance as NFS, with the added possibility of replicating the data to standby databases.


is it safe to create mysql database by just creating its directory? context: I want a separate encrypted filesystem per database (to have a separate key) on MySQL 5.7 but if I mount it before  I execute CREATE DATABASE foo it fails with error database exists, and mouting the filesystem after is a bit risky to me.
So I noticed I can "create" a database in MySQL but just creating a folder inside the mysql datadir and creating tables after causes no trouble. I know this is dirty but this way it simplifies the integration with the creation of the filesystem underneath (as it appeared as the folder when lie the tables).
Does creating database this way is safe?

UNSAFE!
Use CREATE DATABASE.

mkdir probably works for ENGINE=MyISAM, but that old engine should not be used anymore.
InnoDB needs to know what is going on.
You noticed db.opt; but what if something else gets added in the future?
MySQL 8.0 has an internal "data dictionary" that would be totally confused by that mkdir.
And who knows what future versions will be like !

It is possible to execute a command on the mysql commandline tool.  This makes CREATE DATABASE a one-liner, albeit more verbose than a simple mkdir.
If you want a table or partition to be on a different physical drive, check out the limited capabilities of specifying the location for the DATA DIRECTORY:  https://dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html


How to audit a specific query in Oracle I want to audit a specific SQL query in Oracle. However I think we can audit only users or table level.
For example I want to audit 'audit statements', such as who run the "AUDIT CREATE SESSION by username by access" query.
Thanks for supports.

You are right in that audits of "audit" statements need to be made at the object level.
Using Traditional Auditing, you can use a query to generate a script to enable audit tracking for existing objects:
select 'audit audit on '||owner||'.'||object_name;' from dba_objects where owner='[owner]';

After that, or ideally when you first configure your database and before you install any schemas, you can set auditing of audit to happen by default for all future created objects with:
audit audit by default;

With Unified Auditing you can do something like this:
create audit policy audit_actions actions audit table, audit view, audit procedure, audit package, audit directory;
audit policy audit_actions;


Mysql warning: InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set Whenever my MySQL 5.7 starts I get the warning

InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

bar is a table I experimented with long time ago with import tablespace, but its long deleted and I don't see any files associated with it in the database directory.

drop table bar doesn't work.

How do I get ride of this warning ?

Why this warning pops up:

You discarded the tablespace and physically removed the .frm file from the data directory. But still, tablespace information is present in the InnoDB data dictionary table. (you can check innodb_sys_tables, innodb_sys_tablespace in information schema).

So whenever the server starts up, InnoDB tries to load all the table present in sys_tables, sys_tablespaces.  So it tries to open foo.bar table but the state is DISCARD. So that warning is issued.

How to remove the table/warning:

Create a fake bar.frm & bar.ibd inside the foo folder of data
directory.
Start the server and give the drop table foo.bar. It
should remove the table from the dictionary and removes the warning too.


PostgreSQL and query planner I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to make efficient queries? How is it using those statistics? Is there any better description of this whole process, than in the documentation?

I'm fully aware that my question might not be understandable, but I really don't know how to explain my problem easier. If you have questions, please ask.

The most detailed discussion of how statistics are used is the Row Estimation Examples section of the documentation.  Ultimately all information about how a query might be executed is turned into a serious of costs via the various Cost Constants.  So if a table is 1000 pages in size, and the statistics suggest 10% of it will be touched at random by a proposed query, that's 100 pages * 4.0 (random_page_cost) = 400 cost units for pulling in the data; then other constants are used to determine things like processing costs on the data in those pages.

The query optimizer tries various ways of obtaining and combining individual components:  different join types, different ways to access the table data, etc.  It iterates through the possible plans from those combinations, then picks the one that has the cheapest total cost to execute.

I wrote just over 50 pages on this subject for my book PostgreSQL 9.0 High Performance, which has the longest discussion of query execution available right now.  There's isn't too much there on how statistics are used beyond what's shown in the documentation though.  Most of it covers all of the various query plan node elements you might run into.


Vertical / Horizontal Scaling Disk Size MySQL or MariaDB I have been looking into this for a while and cannot find a suitable solution so would like to know if someone here can please help.

I am trying to set up database back-end that will assist with doing analytics. I need to stick with MySQL or MariaDB as this is what we currently use on all projects and queries.

My problem is that I have access to a few virtual machines. Most of them pretty decent, but they all have a range and capacity of 1-2TB. They cannot be increased in disk size as they have been provisioned and in use for other purposes as well.

As they are only used infrequently and sitting idle for large amounts of time I am trying to set it up as a cluster for a database.

Now, what I would like to know is, how do I enable these machines to be scaled such that I can use the hard drive space as a total.

I.e. I have Server 1 (1.5TB), Server 2 (1.0TB), Server 3 (1.8TB), Server 4 (1.5TB), Server 5 (1.2TB). I would like to shard/cluster them all together to add all the space together so that I can utilise the 7TB in total.

The reason for this is that the data being analysed is generally larger than what 1 of the servers can hold. I am not to worried about replication/consistency and systems being down (As it;s not business critical and any lost data can be recovered and re-imported easily). Although I am happy to use a Master/Slave arrangement if needed (In case something goes wrong).

I have look into the following and haven't found any one thing that answers my question...

MaxScale
Percona XtraDB Cluster
Galera Cluster

I have tried with the above packages, but most of the resources I can find tend to only talk about multi master or master slave systems. Or horizontal scaling for maximum read/write access.

I need a solution for vertically scaling by adding multiple machines together. Mostly for total disk space, less important for CPU, RAM etc.

What you're looking for is typically called shared-data architecture, where multiple database nodes access the same set of database files (a database) distributed across the nodes, and the database capacity approximates the sum of disk and CPU capacities of all nodes (more or less).

The MySQL cluster solutions you're referring to implement the shared-nothing architecture, where the database copies are replicated between nodes, and each database node works with its own "copy", so you're still limited by the disk and processor capacity of an individual node.

I don't think there exists a shared-data cluster solution for MySQL, as implementing it is a non-trivial endeavour -- just ask Oracle RAC folks.

If your workload allows, you might consider a cluster file system, such as CephFS or GlusterFS, which will present the sum of all servers' disk volumes as a single file system to a single database node. This will at least address your disk capacity requirement.


Transaction Log 16GB on 2MB database Possible Duplicate:
  Why is My SQL Server Transaction Log Growing or Running out of Space?

The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. I have begun backups of the transaction log and they occur every half an hour.

I have attempted to run DBCC SHRINKFILE (Log,size) but it says all virtual pages are in use.

Does this mean there is an uncommited transaction or something? The file has been this big for a while now. Am I able to somehow truncate the log?

Was the database in FULL recovery but no backups being taken previously? Paul Randall's article Running Out of Transaction Log Space covers the other likely scenarios.

The DMV query below will tell you why the log cannot be truncated. If you're unsure how to interpret the output, add the response to your question.

SELECT
    db.[name] AS [Database Name]
  , db.recovery_model_desc AS [Recovery Model]
  , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
  , ls.cntr_value AS [Log Size (KB)]
  , lu.cntr_value AS [Log Used (KB)]
  , CAST(CAST(lu.cntr_value AS FLOAT)
    / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM
    sys.databases AS db
INNER JOIN
    sys.dm_os_performance_counters AS lu
ON  db.name = lu.instance_name
INNER JOIN
    sys.dm_os_performance_counters AS ls
ON  db.name = ls.instance_name
WHERE
    lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
OPTION (RECOMPILE);


Adding another Secondary Log Shipping location I've seen a few variations of this question, but no clear cut answers to my situation.

I have an existing log shipping setup, and want to add a secondary server.  Here is how I understand what will happen, please correct me if I am wrong.

Restore backup from 12 hours ago to secondary, using no recovery.
Add the secondary database to log shipping which creates the copy and restore job on the secondary and the copy job copies ALL
the .trn files from shared backup location to secondary server. (no
matter if they are needed or not)
The restore job is able to look at the DB on the secondary and see what the last LSN applied is, then find the appropriate log
    backup to apply, then apply all subsequent log backups to bring the
    DB into a consistent state with the primary.

last questions is where I am not sure...  Do I need to manually apply all the log backups since the full backup myself, or will log shipping work as I described above and do it automatically?

Yes, you should apply the latest FULL and all the applicable Transaction log backups in sequence after that full, manually on the secondary with no recovery, prior to setting up the log shipping jobs and turning them on (using the wizard with no initialization).  When the log shipping wizard is used, it will, as you said, create the backup job on the primary and the copy and restore agent jobs on the secondary which looks for the next LSN to apply after the last one you applied manually when it starts applying the logs it needs for the log shipping process to begin.


How to get back the PostgreSQL database to a specific point in time? I was running a batch script in a PostgreSQL database which inserted values in different tables between 10:40 and 11:00. Later I came to know that the script was executed mistakenly. How can I get back my database to a state before 10:40 without stopping the server? I don't want the values which were inserted by batch during 10:40 to 11:00.

WAL has been configured.

I was struggling with this for the last three days. I have been trying with creating recovery.conf file with:

restore_command='path to my archivefile/%f %p'
recovery_target_time = '2014-10-15 10:40:00'
recovery_target_inclusive = true

Still I am not able to get my database backup to previous state.

If you know which rows were accidentally updated, you just don't know what the previous value of those rows were, then what you can do is:

Restore to a new non-production server from your base backup.
Recover that server to the point just before the error.
Write a query to select the values for the rows you know got mistakenly updated.
Turn that into a series of updates which can be run against the original production server.
Spin up yet another server which has been recovered up to current date to test those updates against to make sure they do the correct thing
Apply to production.

Of course if people or software have been relying on the bad values between when the error was made and now, then reversing the error will not reverse the decisions they made based on bad data.


Possible Sign That A Database Is Corrupt? I have a database that's size is 100GB.  When I run CHECKDB on it, it fails to due a lack of space for tempdb, which has over 1TB of space.  I can restart my SQL Server, flush out tempdb, the drive has 1TB of available space, and run it again, and it will use everything and fail due to a lack of space.

I suspect the database is corrupt because this seems like odd behavior, but is there a scenario where a 100GB database would use more than 1TB for running CHECKDB?

UPDATE

The table that didn't pass DBCC CHECKTABLE, I recreated its indexes and will see if it verifies faster.

Normally a few events of 823 and 824 are warnings that there is possible corruption on the database, also if you have backups of this database that are failing and or jobs on it that fail to complete.


If A and B are candidate keys, then AB is candidate key or not ? If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?

The union between A and be is a superkey.

(A candidate key is a superkey but a superkey could be not a candidate key)

Moreover, remember that a candidate key is a particular type of superkey, so you shouldn't worry about if it is "both" key and superkey.


How to import export.sql in sql developer? In SQL Developer, from the top menu choose Tools > Data Export. This launches the Data Export wizard.

So that is export. It creatres export.sql file. Now how to do import ? I am usgin SqlDeveloper 3.20.
Trygin to follow this tutorial , and have created new connection but I cannot find import.
Tools->Preferences->Database->Utilities->Import Method and than what ??
I have tried to  run script @C:\locationOfExportFile\export.sql
And I am getting eroors

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:

BTW, I have no idea what am I doing i just want to get that database to localhost. Other computer and my computer both have Oracle 11g Express database. Schema name is the same!

Open the file(s) it has created, and run them with F5 in SQL Developer. They should also work in SQL*Plus.

They'll create the tables/objects (if you have the Export DDL option checked), and then they'll INSERT the rows (if you have the Export Data option checked.)

Note that there are more efficient ways for moving objects/data around, especially when dealing with very large amounts of data - namely, Data Pump.

This particular feature is very helpful when wanting to do ad hoc data and object exports - especially when you, the developer, don't have access to run Data Pump.


Installing MySQL server 4.1 on Ubuntu 11.04 how do I need to install mySQL server 4.1 on Ubuntu 11.04 server for a legacy app to run (it breaks on mySQL 5).

sudo apt-get mysql-server-4.1

doesn't work - "package is missing, obsoleted or available from another source".

Background:

I'm trying to migrate from a Windows Server to Ubuntu Server. I have a MS Visual Foxpro app connecting to mySQL server 4.1 on Windows.

Just trying to replicate that on Ubuntu for now.

So far, I've got it to the point where the app is already up and running from a remote machine (on mySQL 5.1). However, I have tested and verified that some functions don't work well and it is because of the difference in mySQL versions.

Invariably, someone would suggest that I tinker with the code. I can't do that yet as I didn't develop the app and am still negotiating for the source code.

In the meantime, my goal is just to get this thing to run in Ubuntu with as little hassle as possible and that requires mySQL server 4.1.

Thanks.

If you can run 6.06 in a VM on top of 11.04 server, it looks like MySQL 4.1 can be installed on that


MySQL database naming convention? Is there any convention for database names in MySQL/MariaDB? I've searched Google and this Stack Exchange and haven't been able to find anything. Not even a "Nope, there's no official convention." Is there a standard, or is this something that's decided on a case-by-case basis?

Thank you.

Database names are completely up to the person creating them.  Having said that, it makes sense to use some kind of sane naming convention.

Don't use reserved keywords, don't put spaces in the name, etc.  Essentially, follow the MySQL Documentation for identifiers, and add a little common sense that applies to your environment.


How to configure mongodb replica set with redundant networks I need to set up a mongodb cluster with redundant private networks connecting all the nodes.  Mongo's documentation states:

  ... you should place each mongod instance on a separate host server serviced by redundant power circuits and redundant network paths.

https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

I am able to make mongod listen on multiple nics using the --bind_ip parameter, which I set to:

--bind_ip localhost,10.1.1.101,10.1.2.101

But then when doing rs.initiate()/rs.reconfig()/rs.add() I see no way to specify multiple IP addresses for the members[n].host fields.  I tried multiple values separated with a comma:

> rs.initiate({ _id : 'rs', version : 1, members : [{ _id : 0, host : '10.1.1.101:27107,10.1.2.101:27017' } ] })
{
    "ok" : 0,
    "errmsg" : "FailedToParse: More than one ':' detected. If this is an ipv6 address, it needs to be surrounded by '[' and ']'; 10.1.1.101:27107,10.1.2.101:27017 for member:{ _id: 0.0, host: \"10.1.1.101:27107,10.1.2.101:27017\" }",
    "code" : 93,
    "codeName" : "InvalidReplicaSetConfig"
}

I tried making host an array:

> rs.initiate({ _id : 'rs', version : 1, members : [{ _id : 0, host : [ '10.1.1.101:27107', '10.1.2.101:27017'] } ] })
{
    "ok" : 0,
    "errmsg" : "TypeMismatch: \"host\" had the wrong type. Expected string, found array for member:{ _id: 0.0, host: [ \"10.1.1.101:27107\", \"10.1.2.101:27017\" ] }",
    "code" : 93,
    "codeName" : "InvalidReplicaSetConfig"
}

I tried specifying host multiple times, which mongo accepted, but it only used the last host specified.

> rs.initiate({ _id : 'rs0', version : 1, members : [{ _id : 0, host : '10.1.1.101:27107', host: '10.1.2.101:27017' } ] })
{ "ok" : 1 }

All this is just getting the first node set up.  I had similar issues when adding nodes 2 and 3 with rs.add() and rs.reconfig().

Any help would be much appreciated.

Yes, you can have mongod at multiple networks, but when you configure replica set, you use only addresses of one network. Like network 10.1.1.x addresses. To clients you can give list of addresses from 10.1.2.x network, if that is preferred.

Those addresses what you configure with --bindip are addresses where mongod answers connection attempts.


Mysql Server version: 5.0.77-log Source distribution I have a master(M)-slave(S) replication on MySQL. During a failover test, the master was brought down and the slave was made the master by doing STOP SLAVE; RESET MASTER;. Since there are only 2 servers (master and slave) I didn't have any other slave to change master to. On the master server the MySQL data files (ibdata files and bin logs) were deleted by mistake. Can someone please tell me how to bring the master up to speed and change it back to being the master server?
Thanks.

STOP SLAVE; RESET MASTER; is not the right way to do a failover. You can stop a slave if you want, but RESET MASTER will delete your own binary logs. You may be looking for RESET SLAVE; CHANGE MASTER TO MASTER_HOST=''; (RESET SLAVE ALL; since 5.5). The actual failover is not performed by mysql, but by an external entity, be it a proxy or a connector.

As you have resetted the master data and the slave binary logs, you need to perform a clone of the slave (current master) with its binary logs coordinates (with mysqldump or Percona XtraBackup) and set the replication in the other direction.

If you intend to do manual failovers, a master-master replication with one of the nodes in read-only mode is suggested. You also have software like MHA that can simplify the process.


Moving a SQL Server TempDB of AlwaysOn members We have two SQL Server instances on Azure Windows virtual machines. Always on availability group is configured between them. We need to move TempDB on both instances, is there a risk that something can get corrupted regrading data and availability group listener or we can simply execute instructions from MSDN instructins found here: http://msdn.microsoft.com/en-us/library/ms345408(v=sql.110).aspx

Thanks.

is there a risk that something can get corrupted regrading data and availability group listener

No, tempdb is recreated when SQL server restarts. If your tempdb is large, Make sure to enable instant file initialization for data files only.

The instructions are fine and follow the comment that I put in.(will add it in answer later)


Optimizing PostgreSQL for transient data I have several tables with 100-300 columns of integer types each, that hold highly volatile data. The datasets are keyed by one or two primary keys, and when refresh occurs, the whole dataset is deleted and new data is inserted in one transaction. Dataset size is usually a few hundred rows, but can be up to several thousand rows in extreme cases. Refresh occurs once per second, and dataset updates for different keys are usually disjointed, so dropping and recreating the table is not feasible.

How do I tune Postgres to handle such load? I can use the latest and greatest version if that makes any difference.

Depending on how many different data sets there are, one option would be to partition the tables per-dataset.

When a dataset is updated, BEGIN a new transaction, TRUNCATE the table, COPY the new data into it, and COMMIT. PostgreSQL has an optimisation where COPYing into a table that's been TRUNCATEd in the same transaction does much less I/O if you're using wal_level = minimal (the default).

If you cannot partition and truncate (say, if you're dealing with tens or hundreds of thousands of data sets, where there'd just be too many tables) you'll instead want to crank autovacuum up to run as much as it can, make sure you have good indexes on anything you delete based on, and be prepared for somewhat ordinary performance.

If you don't need crash safety - you don't mind your tables being empty after a system crash - you can also create your tables as UNLOGGED, which will save you a huge amount of I/O cost.

If you don't mind having to restore the whole setup from a backup after a system crash you can go a step further and also setfsync=off, which basically says to PostgreSQL "don't bother with crash safety, I have good backups and I don't care if my data is permanently and totally unrecoverable after a crash, and I'm happy to re-initdb before I can use my database again".

I wrote some more about this in a similar thread on Stack Overflow about optimising PostgreSQL for fast testing; that mentions host OS tuning, separating WAL onto a different disk if you're not using unlogged tables, checkpointer adjustments, etc.

There's also some info in the Pg docs for fast data loading and non-durable settings.


Worse performance on a new server We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the new server is slightly worse than the old, slower server.

In testing, our ASP.NET application runs 10 - 20% slower. Running individual expensive queries with STATISTICS IO and STATISTICS TIME shows 10 - 20% greater elapsed time on the new server. SQL Query Profile shows higher CPU usage on expensive queries.

Task Manager on the new server shows sqlserver.exe is consuming 22 GB of RAM, but the CPU values always stay very low.

I've updated all statistics, rebuilt or reorganized indexes, etc. Execution plans should be stored on the new server at this point, given the amount of testing I've done. If there are any missing indexes (I don't think there are) they affect the old and new servers equally. New has a restored backup of the same data on the old.

I'd expected that the performance on the new server would be better, but of more concern is load. If the old server is performing better even under load, then what will happen when this new, slightly worse server has to take that load?

What else could I be missing here?

EDIT: MAXDOP set to 6.

Old server has OS, databases, and tempdb's on the same physical drives (RAID 10). Total of 4 15k 3 Gb/s 3.5 inch SAS.
New server has three drive sets: OS on RAID 1, database on RAID 10, tempdb on RAID 5. Total of 9 15K 6 Gb/s 2.5 Inch SAS.

Old server has 1 x Intel Xeon E5620 2.40 GHz Quad-Core 8 Threads (w H/T).
New server has 2 x Intel Xeon E5-2640 2.5 GHz Six -Core 12 Threads (w H/T).

EDIT 2: Here's the final analysis:

The power plan was on balanced, not high performance. Switched that over.

Tempdb was on a RAID 5, not RAID 10. Added another HD to create two physically distinct RAID 10 configs, one for tempdb and one for everything else.

Excluded SQL-related files (mdf, ldf, ndf, bak) from virus scanning.

Rebuilt all indexes following the move to the new server. They were very fragmented -- possibly as a result of backup, copy, restore?

And I realized that the processor jump was not that big. The queries aren't going to execute that much faster, but with more processors, more cores, more RAM, we'll be more scalable.

Raid 5 is slower than raid 10, especially for write-heavy workloads.  As such, it is not generally recommended for SQL server and certainly not for tempdb.  This alone could easily explain the performance difference.

My recommendation would be to move tempdb to raid 10.


How to AUTOINCREMENT against another column rather than the whole table in sqlite? I'm using a table to track messages sent in several different channels.

I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'll use 'id' instead.

Here's an example of the end result that I'm looking for:

┌───────┬────────────┬────┬───────────────────┐
│ rowid │ channel_id │ id │       text        │
├───────┼────────────┼────┼───────────────────┤
│     1 │          1 │  1 │ Hello!            │
│     2 │          1 │  2 │ Message text      │
│     3 │          1 │  3 │ Beep boop         │
│     4 │          2 │  1 │ Different channel │
│     5 │          2 │  2 │ So id has reset   │
└───────┴────────────┴────┴───────────────────┘

I should then be able to INSERT INTO messages(channel, text) and have id fill itself.

Normally I'd use id INTEGER NOT NULL PRIMARY KEY or id INTEGER NOT NULL AUTOINCREMENT. However, I believe that this increments the id per-table, not per-channel.

Is there a way that I can make the id increment only such that it creates unique combinations with another column?

I'd just create a view on top of the table.

The view uses the analytic function row_number() to provide what you needed.  You'll need a column to sort on.  That could be your AUTOINCREMENT column.

https://www.sqlite.org/windowfunctions.html

select channel_id, text
  ,row_number() over (partition by channel_id order by identity_column) as id
from unnamed_table


Parsing, Planning and Optimizing Prepared Statements PostgreSQL (2nd Edition) by Douglas and Douglas notes:

  Use server-side procedures (triggers and functions) to perform common operations. A server-side procedure is parsed, planned and optimized the first you use it, not every time you use it.

When using a prepared statement, is it parsed, planned and optimized only on the first time it's used (similar to triggers and functions per the quoted text), or each time it's used?

The PostgreSQL doc for PREPARE is clear about the advantages of prepared statements in terms of performance:

  PREPARE creates a prepared statement. A prepared statement is a
  server-side object that can be used to optimize performance. When the
  PREPARE statement is executed, the specified statement is parsed,
  analyzed, and rewritten. When an EXECUTE command is subsequently
  issued, the prepared statement is planned and executed. This division
  of labor avoids repetitive parse analysis work, while allowing the
  execution plan to depend on the specific parameter values supplied.


Error text: "Key ... is not present in table" even though the key is there? Using Postgresql 9.5 on Ubuntu 16.04.  Importing large .csv files into a new database using COPY FROM.  The database is created by the same script that attempts to import the .csv files, so I know the tables are initially empty.  The script successfully imports around 45,000 rows into the first table.

The problem:
Partway through importing the 2nd table's data, I get errors like this one:

ERROR:  insert or update on table "inactive" violates foreign key constraint "inactive_drug_code_fkey"
DETAIL:  Key (drug_code)=(47543) is not present in table "qrym_drug_product".

But when I check the 1st table, I see that that key exists:

select * from dpd.qrym_drug_product where drug_code=47543;
  id   | drug_code | product_categorization | class | ...
-------+-----------+------------------------+-------+----
 17970 |     47543 |                        | Human | ...
(1 row)

Why would PostgreSQL complain about that key not being present in the table, when I can see it does exist?

I'm not using table inheritance (I saw the caveat on [1]).

Here is the first table:

\d dpd.qrym_drug_product;
                                       Table "dpd.qrym_drug_product"
           Column           |  Type   |                             Modifiers
----------------------------+---------+--------------------------------------------------------------------
 id                         | bigint  | not null default nextval('dpd.qrym_drug_product_id_seq'::regclass)
 drug_code                  | integer | not null
 product_categorization     | text    |
 class                      | text    |
 drug_identification_number | text    |
 ai_group_no                | text    |
Indexes:
    "qrym_drug_product_pkey" PRIMARY KEY, btree (id)
    "qrym_drug_product_drug_code_key" UNIQUE CONSTRAINT, btree (drug_code)
Referenced by:
    TABLE "dpd.inactive" CONSTRAINT "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT

Here is the definition for the 2nd table that bombs partway through the COPY FROM:

\d dpd.inactive;
                                       Table "dpd.inactive"
           Column           |  Type   |                         Modifiers
----------------------------+---------+-----------------------------------------------------------
 id                         | bigint  | not null default nextval('dpd.inactive_id_seq'::regclass)
 drug_code                  | integer | not null
 drug_identification_number | text    |
 brand_name                 | text    |
 history_date               | date    |
Indexes:
    "inactive_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT

Someone named "RhodiumToad" in the #postgresql channel on IRC pointed out the problem.  Because I didn't explicitly state the column name, the 2nd table is referencing the primary key column "id" from the first table.  Instead, I wanted it to reference the drug_code column.

So where I had previously defined the 2nd table as:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product,
    ...

I had to fix it up to be this:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product (drug_code),
    ...


What happens when all secondary read only nodes are powered off? As per my understanding when all the secondary read only replicas are shut down, the primary replica is the one that will take all the read write requests and read-only requests as well.

But when I was validating this scenario yesterday, my primary instance didn't take read-only requests from my application server; however, it did take write requests.

My question here is: Will the primary take read-only requests whenever read-only secondaries are not available?

This is an Always On 3-node environment. The configuration properties are:

As shown, all nodes are configured to allow all connections when they are primary.

For all read requests, we are using the listener name and application intent= read only. For all write requests we use the listener name. We are not using the node name explicitly anywhere. I just tested in my acceptance environment with my application.

Make sure you include the primary in the read_only_routing_list

ALTER AVAILABILITY GROUP  aggroup
MODIFY REPLICA ON 'node1'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= ('node2','node3','node1')
)
);

Do the same for the other 2 nodes. and you can test using this query

SELECT    AVGSrc.replica_server_name AS SourceReplica
        , AVGRepl.replica_server_name AS ReadOnlyReplica
        , AVGRepl.read_only_routing_url AS RoutingURL
        , AVGRL.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists AVGRL
INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
ORDER BY SourceReplica


Searching through lots of applications Google search has instant search feature. While you are typing, it finds the pages. Apple.com search box. StackExchange ask question part finds similar questions while you are typing question title.

I am building a collection of web applications in same system. I will add a general search functionality to search through storage of all web applications. But thinking that tables will have records of thousands of users, and that searching will be done for all applications, that can cost too much server power.

I consider caching, though it is like creating all database tables in a folder or memory with same information. I don't see any benefit from this approach.

How are businesses are building this kind of feature in products professionally? Is there any special technique, or can caching really solve it?

P.S. I am using PHP with MySQL.

If you're searching documents, a text engine like solr could be your friend.  Combine that with ajax for real-time document discovery.


Best way to store units in database I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the MS_Description field of Extended Properties. I am wondering if there is a better way to store this information. I suppose it is fine for documentation purposes, but it would be difficult to make robust unit-conversion calculations based on this data. At this point I am not prepared to make an invasive change, but if I get the chance to do so, what is the recommended Best Practice in this regard? Options, off the top of my head, might include:

Change column name to included units (e.g., "TotalVolumeInGallons". This would make the information a little more readily available, but it still seems weak to me.)
Add a separate "Units" column to correspond to every "Amount" column (this column could be nvarchar OR it could be a foreign key to a separate Units table which might make it easier to calculate unit conversions. On the other hand, adding so many columns could fairly double the size of my database -- with terribly redundant data.)
Create a new field in Extended Properties dedicated specifically for units. (Unfortunately, I don't think this can be a foreign key to a Units table.)
Is there another idea that I'm overlooking?

UPDATE: After reading @Todd Everett's answer, a possible solution occurred to me, so I'm going to go ahead and answer my own question. (See below)

Since you mention hundreds of columns I would consider an EAV design.  While Joe Celko warns against this, I think it may be applicable in your use case.  It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string.  It will work even better if all the amounts are whole numbers, but can work also if some are decimal.  Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns:  Conventions of Thought.  This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that.  One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure.  Here is an example:

UOM Conversion

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2.  There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps.  So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32.  The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth.  I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.


How do I load just the data from SQL Server 2005 backup into a 2008 R2 database? I created a 2008 native database and I have the structures in place but need to load data from a 2005 backup.

Is your new structure different of the structure in SQL Server 2005? You may restore the backup entirely, there is no need to create first the structure and then load the data.


MySQL datetime to just date Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way to change the column to store just the YY-MM-DD?

If yes, would this corrupt any of the data?

If no, would the solution lie in creating a completely new column?

From the manual here, you can simply use the DATE() function.

 DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

It is designed for precisely your use case. Although the quote is from the 5.7 manual, it works right back to at least 5.0. You don't have to worry about corrupting your data.

If you are using 5.7, you can use a generated column:

  The syntax is:

  TYPE   [ GENERATED ALWAYS ]   AS   (  )   [
  VIRTUAL|STORED ] [ UNIQUE [KEY] ]   [ [PRIMARY] KEY ]   [ NOT NULL ]
  [ COMMENT  ]

MyDate DATE AS (DATE(My_Date_Time_Field))  -- or similar, not tested

This avoids throwing away data which might be useful later and nicely tidies your SQL syntax.

However, if you're using an earlier version of MySQL, you can always generate this DATE() part on the fly, which given the relatively simple syntax, shouldn't be too problematic.


Sql Rank for records contain multiple combinations I am trying to filter the records which contains multiple combinations and removing the records which contains a single combination. Database DB2

Query Used:

select *
from
    (select b.Store,b.order,
            rownumber() over (partition by b.store order by b.order) AS RNK
     from Library.Table1 b
    ) tmp
where tmp.RNK > 1
order by tmp.Store

Current output:

Store Order   RNK

Store1  Order1  1
Store2  Order1  1
Store2  order2  2
Store2  order3  3
Store3  Order1  1
Store3  order2  2
Store3  order3  3
Store4  Order1  1

Expected output:

Store Order   RNK

Store2  Order1  1
Store2  order2  2
Store2  order3  3
Store3  Order1  1
Store3  order2  2
Store3  order3  3

I'm not familiar DB2, but if it allows using count as analytic function you can do

select *
from
    (select b.Store,b.order,
            rownumber() over (partition by b.store order by b.order) AS RNK ,
            count(*) over (partition by b.store) cnt
     from Library.Table1 b
    ) tmp
where tmp.cnt > 1
order by tmp.Store

If not, the query below should give you the same result (maybe not the best solution from performance perspective ) :

select tmp.*
from
    (select b.Store,b.order,
            rownumber() over (partition by b.store order by b.order) AS RNK

     from Library.Table1 b
    ) tmp
    INNER JOIN
    (
        SELECT store
        FROM Library.Table1
        GROUP BY store
        HAVING (COUNT(*) >1)
    )tmp2 ON (tmp2.store = tmp.store)

order by tmp.Store


Many-many relation with specific type Sorry if this question has already been asked, but I didn't find it performing a search.

I have 2 tables (table_a and table_b), linked by a many/many relation.
The relation can be of two types, type_a and type_b.

I was wondering what was better between:

Setting a type column in the relation table and during queries filter on this field
Creating 2 relations table, one for each type and apply no filter during queries

I can see advantage in both solutions, but I can't tell which one is generally used to solve this kind of problem

Adapted from here; see that for rationale.

For separated tables, each should look similar to this:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

For the single table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    type ENUM('foo', 'bar') NOT NULL,  -- or TINYINT UNSIGNED  ***
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X  ***
    INDEX      (y_id, type, x_id)       -- When starting with Y
) ENGINE=InnoDB;

A third option is to put both types in a single table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    type ENUM('foo', 'bar') NOT NULL,  -- or TINYINT UNSIGNED
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, type, y_id),      -- When starting with X  ***
    INDEX      (y_id, type, x_id)       -- When starting with Y
) ENGINE=InnoDB;

(*** indicates the only deviation(s) from previous version.)

Which is "best"?  I don't know.  And if you add FOREIGN KEYs, things get messier.


MS SQL - export tables to format compatible with PostgreSQL We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing.

MS SQL Server has an export function but the only one that looks like it would work would be to export to Excel files. I'd rather use a SQL script or function that can export to a file and import those files into a PostgreSQL server for testing. I've searched a number of sites for a SQL script but I can't find one that will work.

What would be a good approach to accomplish this?

Please refer to below thread. SSIS would help you in transferring data from SQL server to postgres
Micrsoft forum thread for similar issue

I have written article on how to transfer data from SQL server to postgres but this is applicable to SQL server 2008 and onwards you can read it though

How to transfer records from sql server to postgres


MySQL - What exactly does smallint(2) permit? Sorry for the really simple question, but I can't figure it out.

When I set up my schema, I set up my primary key to use a field of type smallint(2). The thought behind it was that the field should only ever consist of 2-digit numbers, 1-99.

We've broken the upper limit of that and now I'm seeing 3-digit numbers being added to this field. I'm now speculating that smallint(2) and varchar(2) do not function the same, but since I don't understand what's going on with this table I'm getting a little concerned.

What values does a field such as smallint(2) allow?

Thanks!

As explained in the MySQL documentation here, the (n) suffix for numeric types is the (default) display width for numbers in that column. In your case the (2) sets the default display width to 2 digits. It does not limit numbers in that column to two digits (as you have discovered), it's just a formatting hint for display purposes.


Single AND operator and multiple OR operators I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3.
Every SELECT statement should return 1 record.

View my sample table here:

How do I do it in SQL? I tried this SQL statement:

SELECT record_id
FROM tbl_people
WHERE category_id=1 OR category=id=2 OR category_id=3
OR category_id=4 OR category_id=5 OR category_id=6
AND user_id = 1;

...But it does not work.

That's because in SQLite, the AND operator has a higher precedence than OR (see the Operators section on this SQLite documentation page)

This means that SQLite first evaluates the category_id=6 AND user_id = 1 expression and then ORs its result with all the other category_id conditions.

Thus, your query returns all records where category_id is 1,2,3,4, or 5, or where category_id is 6 and user_id is 1.

You should add parentheses to force the ORs to be evaluated first:

SELECT record_id
FROM tbl_people
WHERE
(
    category_id=1 OR
    category_id=2 OR
    category_id=3
)
AND user_id = 1;

Alternatively, you could use the SQL IN operator to make this more concise:

SELECT record_id
FROM tbl_people
WHERE category_id IN (1, 2, 3)
AND user_id = 1;

Also, note that you have a typo: in your WHERE clause there's a category=id, which should probably be category_id.

Update: Here's a link to try out the query: http://www.sqlfiddle.com/#!7/d7ad3/1


Horizontal and Vertical Fragmentation on Distributed Database My Architecture:

I need to build a database with SQL Server, where the data from each server is distributed to the other server.

How can I achieve this kind of architecture?

How do I maintain consistency between the tables location on insert, and when selecting?

How can I select all the data of both servers with the same code?

Both instances need to accept writes. The idea is to work like DDB so one server knows that the other has the data and requests it.

The typical method employed where you need to replicate the data to both servers is to use a NEWSEQUENTIALID() as the default value for the primary key columns.

From the Microsoft Documentation for NEWSEQUENTIALID():

  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

SQL Server uses the UuidCreateSequential function to generate globally unique identifiers.  This function guarantees that no duplicates will be generated when used in a machine with a manufacturer-assigned ethernet MAC address.  If you are using virtual machines with locally generated MAC addresses where those MAC addresses are not guaranteed to be unique there is a slight possibility that the values will not be unique.

An example CREATE TABLE statement for your location table:

CREATE TABLE dbo.Locations
(
    LocationID uniqueidentifier NOT NULL
        CONSTRAINT Locations_PK
        PRIMARY KEY CLUSTERED
        CONSTRAINT Locations_PK_DF
        DEFAULT NEWSEQUENTIALID()
        ROWGUIDCOL
    , LocationName varchar(48) NOT NULL
) ON [PRIMARY];

Inserting location values looks like:

INSERT INTO dbo.Locations (LocationName)
VALUES ('Cydonia')
    , ('Utopia Planitia');

The inserted row now looks something like:

SELECT l.LocationID
    , l.LocationName
FROM dbo.Locations l;

╔══════════════════════════════════════╦═════════════════╗
║              LocationID              ║  LocationName   ║
╠══════════════════════════════════════╬═════════════════╣
║ A6044A1D-E286-E911-9C54-005056807260 ║ Cydonia         ║
║ A7044A1D-E286-E911-9C54-005056807260 ║ Utopia Planitia ║
╚══════════════════════════════════════╩═════════════════╝

I've created the same table structure on a second server; here we're inserting different rows on that server:

INSERT INTO dbo.Locations (LocationName)
VALUES ('Copernicus Crater')
    , ('Mare Imbrium');

SELECT l.LocationID
    , l.LocationName
FROM dbo.Locations l;

╔══════════════════════════════════════╦═══════════════════╗
║              LocationID              ║   LocationName    ║
╠══════════════════════════════════════╬═══════════════════╣
║ A27E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Copernicus Crater ║
║ A37E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Mare Imbrium      ║
╚══════════════════════════════════════╩═══════════════════╝

As you can see in the two sets of output shown above, each row has a unique LocationID value.  The values aren't generated in random order, they are generated sequentially, which is important for insert performance since it will limit fragmentation via page splits.  Be aware, however, those sequential starting points will change whenever the server hosting SQL Server is restarted.

Merge replication, which is a feature included in all versions of SQL Server from Standard to Enterprise (not SQL Server Express), automatically replicates rows between both servers.  The ROWGUIDCOL property included in the CREATE TABLE statement marks the LocationID column for use by Merge Replication.  Directions for setting up Merge Replication are beyond the scope of this answer.

Once Merge Replication completes, both servers will have this in their dbo.Locations table:

╔══════════════════════════════════════╦═══════════════════╗
║              LocationID              ║  LocationName     ║
╠══════════════════════════════════════╬═══════════════════╣
║ A27E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Copernicus Crater ║
║ A37E42F8-E286-E911-94B4-9CB6D0087DC0 ║ Mare Imbrium      ║
║ A6044A1D-E286-E911-9C54-005056807260 ║ Cydonia           ║
║ A7044A1D-E286-E911-9C54-005056807260 ║ Utopia Planitia   ║
╚══════════════════════════════════════╩═══════════════════╝


Rows to EAV structure with row identifier Would you help me with this, please?

Imagine any SQL table or query results like…

+-----------+----------+-----+
| FirstName | LastName | Age |
+-----------+----------+-----+
| Peter     | Whatever |  31 |
| George    | McDonald |  55 |
| ...                        |
+-----------+----------+-----+

And I want to transform each row to EAV structure (column name, column value) and add a unique row identifier, like…

+--------------------------------------+-----------+----------+
|                 GUID                 |  ColName  | ColValue |
+--------------------------------------+-----------+----------+
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | FirstName | Peter    |
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | LastName  | Whatever |
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | Age       | 31       |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | FirstName | George   |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | LastName  | McDonald |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | Age       | 55       |
| ...                                                         |
+--------------------------------------+-----------+----------+

Preferably using FOR XML PATH clause (more tables with a different structure to transform like this).

Script for creating test table goes here:

DECLARE @MyTable TABLE (
    FirstName NVARCHAR(100)
    ,LastName NVARCHAR(100)
    ,Age INT)
INSERT INTO @MyTable (FirstName,LastName,Age)
VALUES(N'Peter',N'Whatever',31),(N'George',N'McDonald',55);

I know the solution without GUID so the row-identifier is the point of my question:

SELECT T2.N.value('local-name(.)', 'nvarchar(128)') as ColName,
       T2.N.value('text()[1]', 'nvarchar(max)') as ColValue
FROM (SELECT *
      FROM @MyTable
      FOR XML PATH(''), TYPE) as T1(X)
CROSS APPLY T1.X.nodes('/*') as T2(N)

You do need to include a GUID higher in the query.

This looks like it might do the job:

SELECT N.value('../@GUID', 'nvarchar(max)') as c1,
       N.value('local-name(.)', 'nvarchar(max)') as ColName,
       T2.N.value('text()[1]', 'nvarchar(max)') as ColValue
FROM (SELECT NEWID() as "@GUID", *
      FROM @MyTable
      FOR XML PATH('T'), TYPE) as T1(X)
CROSS APPLY T1.X.nodes('/T/*') as T2(N)


Difference in architecture between SQL Server 2005 and SQL Server 2012 I've been asked to identify the architecture differences between Microsoft SQL Server 2005 and 2012. I've been pointed out to http://technet.microsoft.com/en-us/library/bb545450.aspx but I haven't seen anything talking about the architecture.

So what are the differences? I'm specially interested on security-related differences.

There are a number of them, a lot of articles on the topic if you have google at it.

Some of them:

Default Schema for Windows Groups

User Defined Server Roles
Enhancments to Auditing
User Contained Databases, authentication without logins.
TDE - Transparent Data Encryption
Hashing Functions - improved.


Migrating data in SQL Server from source to target with schema change at target We have a strange scenario.

We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2.

The source database table has 5 columns.  The target database contains an extra column which is not nullable and has a DEFAULT value defined.

We need to push data for the 5 columns from source to target.  If we use any tool like red-gate or dbcompare, it will compare source with destination and provide a query to insert the new data on to the target, however running the query fails as it is not inserting anything into the newly introduced column which is not nullable.

Any idea on how to handle this?

Did you

Copied all the data to SQL2008
Updated the schema on SQL2008 according to the upgrade script?

Copy data shouln't be the problem here, schema compare neither ;)

For the scheme compare from sql 2005 to 2008 you may use the free Open DbDiff from http://opendbiff.codeplex.com and apply the resulting script to sql 2008.

Otherwise use the new version of the SQL Server Data Tools http://msdn.microsoft.com/en-us/data/tools.aspx that allows SQL 2005 to 2008/2012/Azure migration and scheme update out of Visual Studio 2010+


Comparing duplicates and choosing the duplicate with most recent date in a join Assume i have table A:

id:    name:    timestamp:
1      Bob      2018-05-31 04:46:37
2      Alice    2019-01-31 07:13:56
1      Bob      2019-01-15 18:23:74

And i want to join with another table B:

id:    name:    Preference:
1      Bob      Car
2      Alice    Bike

ID alone isnt a primary key. however ID + timestamp IS an unique key. However, how would i make to only join the 1 part of the duplicate, that has a the newest date? So that i would get a joined table like:

id:    name:    Preference:    Timestamp:
1      Bob      Car            2019-01-15 18:23:34
2      Alice    Bike           2019-01-31 07:13:56

By if i have duplicates, then compare the date between the duplicates and choose the newst

You could use a LATERAL join or CROSS APPLY (both added in version 12c) or window ranking functions in a subquery which should work in older versions as well:

WITH cte AS
(
    SELECT
        b.id, b.name, b.preference,
        a.timestamp,         -- keep only columns you need
        ROW_NUMBER() OVER (PARTITION BY b.id
                           ORDER BY a.timestamp DESC)
          AS rn
    FROM b
         LEFT JOIN a
         ON b.id = a.id
)
SELECT cte.*           -- keep only columns you need
FROM cte
WHERE rn = 1
-- ORDER BY b.id
 ;


Is SYSUTCDATETIME a safe primary key? I have a serially modified table of processed events.
A utc time for when it was processed and an eventId.

Is it safe to use SYSUTCDATETIME() as my primary key? I can easily use a surrogate key but it felt funny.

( Please note that the original wording of the Question was: "Is SYSUTCDATETIME a safe clustered index?" )
What exactly do you mean by "safe"? A Clustered Index doesn't need to be unique, so it is "safe" in the sense that it won't break anything. But it can't be a Primary Key since it can't be guaranteed to be unique. Just try the following and you will see that the value coming back from the function is the same across all rows:
SELECT SYSUTCDATETIME(), * FROM sys.objects;

And so the question is then: what exactly would you gain by using SYSUTCDATETIME() instead of an IDENTITY? I assume you are using a DATETIME2 column, which is 8 bytes, yet you could most likely get away wit a 4 byte INT for the IDENTITY column.
So, I would go with an INT populated via IDENTITY as it is:

smaller (4 bytes instead of 8; and that is 8 bytes in the ideal case of having unique values, but when there are duplicates, a "uniquifier" value is added to any duplicate rows that is an extra 4 bytes)
guaranteed unique (not only better for a Clustered Index, but eligible to be a PK).
they would essentially be in the same order anyway (although it is possible that occasionally they will not match in order)

UPDATE
No, I personally would not trust a value, even with such high resolution as DATETIME2, to be a Primary Key since it is still possible for two operations to happen at the same microsecond. AND, you cannot guarantee that at no point in the future will someone or some process insert more than 1 record in a single statement.
UPDATE 2
@ypercubeᵀᴹ brings up a good point in a comment below (a comment that will soon magically disappear now that I mention it here ;-):

you could use the datetime column for the Clustered index and the identity column for the PK. (That's sometimes good, if all or almost all your queries depend on ordering by the datetime. So the occasional not matching of the two orders won't hurt performance.)

Yes, this is sometimes a good option, but it is not without potential consequence due to the Clustered Index key(s) getting copied into Non-Clustered Indexes:

If there is only one Non-Clustered Index on this table, then the space taken up by the index will be mostly the same either way. But because the Clustered Index keys have to ultimately be unique (at least internally) as they are the RowID for the Non-Clustered Indexes, for all rows with duplicate DATETIME2 values there will be that extra 4 byte "uniquifier" added. And it will be added twice: once to the Clustered Index, and once to the Non-Clustered Index for the IDENTITY PK.

If there are several Non-Clustered Indexes, then the impact on space used will be:
(4 bytes minimum + 4 bytes for any duplicated Clustered Index key values)
* number_of_indexes
* rows_in_table (less if filtered indexes are used)
These numbers might seem small to some, but if we are working with hundreds of millions of rows, they do add up. And for those who mistakenly believe that "disk is cheap" please consider that a) Enterprise storage is not cheap, and b) disk operations such as index maintenance, etc are also not cheap (less of an issue with SSD, but still). For a more detailed analysis regarding the down-stream effects of data modeling decisions, please see the following article I published on SQL Server Central: Disk Is Cheap! ORLY? (that site requires free registration to view content).

This is not to say "don't do it", but more so "do it only if the benefit outweighs the cost".
UPDATE 3
For the sake of completeness, I should mention that one issue with using DATETIME2 values in the hopes of separating individual INSERT and/or UPDATE statements is that they are not as granular as they appear to be. This is an issue between "resolution" and "precision". The "precision" of DATETIME2 is 7 decimal places. But that doesn't mean that the most granular time value represented is incremented at the next value as it happens. This is just like with DATETIME values that are precise to the millisecond, you will never get a value that has anything but a 0, 3, or 7 in the millisecond position:
SELECT CONVERT(DATETIME, '2016-04-14 20:30:40.121'), -- 2016-04-14 20:30:40.120
       CONVERT(DATETIME, '2016-04-14 20:30:40.122'), -- 2016-04-14 20:30:40.123
       CONVERT(DATETIME, '2016-04-14 20:30:40.128'); -- 2016-04-14 20:30:40.127

Likewise with DATETIME2 values, there is a moment when a call to SYSUTCDATETIME() is completely accurate. But because the value does not get refreshed at the next microsecond, the reported value will stay constant for a moment until the next time it is refreshed. This is why the simple test that @Paul posted in a comment on the Question gets PK violations, even though the two INSERT statements are arguably running at least 1 microsecond apart.
Please see the following Stack Overflow answer for more details, including a link to a project on CodeProject.com that has code that can be used in SQLCLR to overcome this limitation:
SQL Server CLR Integration not calling system time as expected


How to get first value from list of value I have data as follows

 Id   Col1   Col2
 ================
 101  101    abc
 101  102    def
 102  102    ght
 103  101    mgj
 103  102    dkf
 103  103    dfj

I need output as follows

 Id   Col1   Col2
 ================
 101  101    abc
 102  102    ght
 103  101    mgj

(by default, it should take  101 (col1), if not there 102(col1), or else 103(col1))

Any Idea?

You can use the DISTINCT ON of SELECT as in the documentation

SELECT DISTINCT ON (id)
  id, col1, col2
FROM TABLE
ORDER BY id, col1, col2

The difference between SELECT DISTINCT <columns> and SELECT DISTINCT ON (<columns>) <columns> is that the first gives you unique rows across all selected columns .. the second gives you one unique row per column set defined within the parenthesis, but allows for additional columns to be returned.

To specify WHICH additional data is returned, you need to ORDER BY..  DISTINCT will then return the first row for each DISTINCT ON set of columns.


Do I need ORDER BY in a window, if I already have a properly ordered query? In this query..

SELECT *
FROM foo
ORDER BY x;

Does a window function over foo need a specific ORDER BY clause or does the window share the ordering of the query?

Question inspired by @Erwin, and @Ypercube's comments on my own answer

Yes

You can actually see that without going to the spec.

CREATE TABLE foo AS
  SELECT trunc(random()*10) AS x
  FROM generate_series(1,100);

with

SELECT row_number() OVER (), x
FROM foo
ORDER BY x;

 row_number | x
------------+---
         95 | 0
         93 | 0
         75 | 0
         74 | 0
         21 | 0
         55 | 0
         97 | 0
         54 | 0
         26 | 0
          5 | 0

Versus the window function over an ordered partition

SELECT row_number() OVER (ORDER BY x), x
FROM foo;

 row_number | x
------------+---
          1 | 0
          2 | 0
          3 | 0
          4 | 0
          5 | 0
          6 | 0


Hard delete vs soft delete performance I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. Other areas of the system depend on this, so the table on its own doesn't really explain the scenario. However, the question I have is whether I should keep the table as is and delete, when the pending action is done, or whether I should add a flag column in and update.

Note, a record may be deleted in the same second it is inserted. I'm hoping to support up to 100 per second, but would not like to have that as the limit.

I am using SQL Server 2014 Enterprise Edition.

Here's the table's definition (all indexes are based on select queries using this table):

CREATE TABLE [dbo].[OpenRounds](
    [OpenRoundId] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [GameActivityId] [bigint] NOT NULL,
    [VendorId] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [UserBonusId] [bigint] NULL,
    [VendorRoundId] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GamesOpenRounds] PRIMARY KEY CLUSTERED
(
    [OpenRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsUserIdUserBonusId] ON [dbo].[OpenRounds]
(
    [UserId] ASC,
    [UserBonusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsUserIdVendorIdVendorRoundId] ON [dbo].[OpenRounds]
(
    [UserId] ASC,
    [VendorId] ASC,
    [VendorRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsVendorIdVendorRoundId] ON [dbo].[OpenRounds]
(
    [VendorId] ASC,
    [VendorRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

If you are designing your system with the intention of supporting thousands of transactions per second, you probably want to design a queuing system that does not rely on inserts.  Inserts are costly, and depending on the design of the queue table may result in the last page of the queue table being a "hot spot" that limits the rate of throughput for the system.

The following design allows for up to 100,000 concurrent transactions through the Rounds table.

CREATE TABLE dbo.OpenRounds
(
    OpenRoundID BIGINT NOT NULL
        CONSTRAINT PK_GamesOpenRounds
        PRIMARY KEY CLUSTERED
    , UserID INT NULL
    , GameActivityID BIGINT NULL
    , VendorID INT NULL
    , RoundDate DATETIME NULL
    , UserBonusID BIGINT NULL
    , VendorRoundID NVARCHAR(50) NULL
    , ReferenceCount INT NOT NULL
        CONSTRAINT DF_OpenRounds_ReferenceCount
        DEFAULT ((0))
);

INSERT INTO dbo.OpenRounds (OpenRoundID)
SELECT TOP(100000) /* top 100,000 -> we're creating 100,000 slots */
    rn = ROW_NUMBER() OVER (ORDER BY o1.object_id)
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

GO
CREATE SEQUENCE dbo.RoundSlotSequence
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CYCLE
CACHE 10000;
GO

The sequence created above is designed to roll-over once it reaches 100,000.  This coincides with the number of rows we created in the dbo.OpenRounds table.

To push messages into the OpenRounds table, you can use the following proc:

CREATE PROCEDURE dbo.PushRound
(
    @UserID INT
    , @GameActivityID BIGINT
    , @VendorID INT
    , @RoundDate DATETIME
    , @UserBonusID BIGINT
    , @VendorRoundID NVARCHAR(50)
)
AS
BEGIN
    DECLARE @SlotID INT;
    DECLARE @SequenceID INT;
    DECLARE @TryCount INT = 0;
    SELECT @SequenceID = NEXT VALUE FOR dbo.RoundSlotSequence;
    WHILE @SlotID IS NULL AND @TryCount < 50
    BEGIN
        UPDATE dbo.OpenRounds WITH (ROWLOCK)
        SET ReferenceCount = ReferenceCount + 1
            , @SlotID = OpenRoundID
            , UserID = @UserID
            , GameActivityID = @GameActivityID
            , VendorID = @VendorID
            , RoundDate = @RoundDate
            , UserBonusID = @UserBonusID
            , VendorRoundID = @VendorRoundID
        WHERE ReferenceCount = 0
            AND OpenRoundID = @SequenceID;
        /* If @SlotID IS NULL the slot was not available
            - wait 5 milliseconds before checking again
            to see if the slot is open
        */
        IF @SlotID IS NULL WAITFOR DELAY '00:00:00.005';
        SET @TryCount += 1;
    END
    IF @SlotID IS NULL
        RETURN 1
    ELSE
        RETURN @SlotID
END;
GO

This procedure can be used to obtain the next message from the OpenRounds table.  Typically this would be ran inside a loop that continuously looks for rows to process:

CREATE PROCEDURE dbo.PopRound
(
    @UserID INT OUTPUT
    , @GameActivityID BIGINT OUTPUT
    , @VendorID INT OUTPUT
    , @RoundDate DATETIME OUTPUT
    , @UserBonusID BIGINT OUTPUT
    , @VendorRoundID NVARCHAR(50) OUTPUT
    , @MaxRetries INT = 2000 /* 10 seconds
                default maximum wait time */
)
AS
BEGIN
    DECLARE @SlotID INT;
    DECLARE @TryCount INT = 0;
    WHILE @SlotID IS NULL AND @TryCount < @MaxRetries
    BEGIN
        UPDATE dbo.OpenRounds WITH (ROWLOCK)
        SET ReferenceCount = ReferenceCount - 1
            , @SlotID = OpenRoundID
            , @UserID = UserID
            , @GameActivityID = GameActivityID
            , @VendorID = VendorID
            , @RoundDate = RoundDate
            , @UserBonusID = UserBonusID
            , @VendorRoundID = VendorRoundID
        WHERE ReferenceCount > 0;
        IF @SlotID IS NULL WAITFOR DELAY '00:00:00.005';
        SET @TryCount += 1;
    END
END;
GO

This design is loosely based upon concepts I saw at the Exadat.co.uk Super-scaling SQL Server site, by Chris Adkin.  His site provides exceptional material and guidance around pushing SQL Server to its limits, including very in-depth details about the hardware and how SQL Server interacts with it.  I am not associated in any way with Chris, or his website.


Script to Output Tables to CSV Showing Heading Only Once I am trying to write a script that will output a table to a CSV.  The tricky part has been to get the output to only show the header once.  The script below does not show the header at all.  The other option we have used is is setting the pagesize extremely high.  That has also been very problematic.

set colsep |
set pagesize 0
set trimspool on
set linesize 10000

spool table1.csv
SELECT host_name from v$instance;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "CURRENT TIME" from dual;
prompt ================================================
prompt    TABLE 1
prompt    SELECT * FROM tbl1;
prompt ================================================
SELECT * FROM tbl1;
spool off

For clarity:
I do not have direct access to the database.  I will be passing the script to a DBA to run against the database, so PL/SQL or anything else more advanced than a very basic script is probably not an option.  Also because of this, I want the column headers to show ONCE and only once whether there are 10 or 10 million records in the table.  This will give me reasonable descriptions of data that I need and minimize the cleanup before analyzing the data.

You could run two queries - the first pulls the list of field names in tbl1 from user_tables and then the second query then pulls the actual data from tbl1? Spool all to the same file. Don't have a running instance of Oracle to test, but that may be a solution to your issue? Removes the need for PL/SQL - although I fail to see the difference between running a shell script and a simple PL/SQL one.


Joining in query decomposition I have a question about query decomposition and localization, as in here:

When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table?
Why ASG and EMP are joined first, then the "result" is joined with PROJ?

Can all the 3 tables not be joined parallelly:

Would the following also be possible (just switching the order of PROJ and EMP):

The problem as shown is transforming relational calculus, of which SQL is a variant, into relational algebra, which consists of the original operators Codd defined on relations.  I will assume that the EMP, ASG, and PROJ represent employees, projects, and the assignment of employees to projects.  The query, as stated in relational calculus, is asking for the names of employees who aren't J. Doe and who are assigned to the CAD/CAM project for a duration of 12 or 24 months.  The operator in question, natural join, is associative - which means the ability to group the operands in any way without changing the result.  So it does not matter the order in which the EMP, ASG, and PROG are joined.  This is because natural join is really just the product of EMP, ASG, and PROG, then restricted by EMP not J. Doe, ASG DUR 12 or 24, PROG CAD/CAM, EMP.ENO = ASG.ENO, and ASG.PNO = PROJ.PNO, then only the ENAME column projected out.  When you think of it this way, you realize that to first get the product of the rows in EMP, ASG, and PROJ it makes no difference the order you multiply them.  This corresponds to integer algebra, where it makes no difference in what order you multiple 3, 6, and 2.  Thinking of it this way also shows why you can't do the join "in parallel."  You can't take the product of ASG and PROJ, and the product of ASG and EMP, and then... what?  No more than you could take the product of 3 and 6, which is 18, and 6 and 2, which is 12, and then... what? to get what is the right answer - 36.

This is a great question as it shows the job of the optimizer when parsing relational calculus is to first build a corresponding tree of equivalent operators in relational algebra.  There are many ways to build the tree that yield the same result, and as Marco pointed out the optimizer's job is to figure out which of the ways will cost the least.  Part of that cost estimation is determining which access methods (ie indexes) are available to execute each relational operator and the cost of each.  In this way, access methods can be added and removed without impacting the formulation of the query.  This was a huge benefit over the navigation systems which pre-date the SQL DBMS'.  A great reference on this is Fabian Pascal's book SQL and Relational Basics which, while written over 20 years ago still provides the best explanation I have found of these concepts.  It is unfortunately out of print now but you may be able to purchase an old copy.  You can however purchase his Practical Database Foundation Series on his website.  I would also highly recommend any of the writings of Chris Date, Hugh Darwen, and/or David McGoveran on the topic.


Security for Application Developers doing PL/SQL work in Oracle How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need few restrictions. However, there seems to be a big gaping hole in the architecture for programmers doing development with a front end application and PL/SQL in multiple schema. Here are some of my options with their downsides:

Make each programmer do development in their own schema. The DBA will grant object level privileges to programmers needing them. Any package development must be done by a DBA. The major downside is that programmers will use the database like a bit bucket to the detriment of database performance. I want the programmers to develop in the database, but this method would greatly discourage it.
Give each programmer the username/password for the dozen or so schema they need to do development in. Grant these application schema permission to create procedures, tables, etc. Some of the disadvantages with this approach are that programmers have to maintain multiple logins and are seldom logged in as themselves. Cross schema development is also difficult.
Grant programmers proxy authentication privileges on each schema they need to do development for.  This keeps them logged in as themselves without having to grant them privileges other than the proxy privilege.  Disadvantages include programmers having to maintain separate connections for each schema they proxy for, cross schema development is more cumbersome as connections have to be constantly changes, and packages using public database links with passed authentication won't compile inside proxy connections.
Give each programmer DBA privileges. – The downside here is security. No schema programmer can be kept out of any schema and any programmer can impersonate any other programmer (DBA).

There seems to be a missing option to grant each programmer SELECT/INSERT/CREATE/etc. privileges on the schema they need to do development in. They login as themselves to do their work using one connection. New objects in the schema they have access to are immediately available.

Am I missing something? How do you handle application programmers that do PL/SQL development?

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server.  Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.

In the case of our critical systems (SCT Banner, for tracking classes & students, and Oracle Financials), there were also 'test' and 'seed' servers.  Test was for user acceptance testing before stuff migrated from dev to prod;  'seed' was stock install of the software, so should we find a bug, we could verify if it was something we had introduced or came from SCT or Oracle's software.


FROM keyword not found where expected (Oracle) ORA-00923 I'm getting the error in the following query:

SELECT DISTINCT UNIQUE_ID as uid, CONFIDENCE_IS_SAME FROM
    (
    (SELECT UNIQUE_ID, CONFIDENCE_IS_SAME,  FIRST_NAME, LAST_NAME, POSTAL_CODE
    FROM DANIEL.UNIQUE_PHYSICIAN  WHERE
    DANIEL.UNIQUE_PHYSICIAN.FIRST_NAME = ''
    AND
    DANIEL.UNIQUE_PHYSICIAN.LAST_NAME = ''
    AND
    DANIEL.UNIQUE_PHYSICIAN.IS_ROOT_PHYS = 0
    AND
    DANIEL.UNIQUE_PHYSICIAN.POSTAL_CODE = '')

INNER JOIN

(SELECT
MAX(CONFIDENCE_IS_SAME) OVER (PARTITION BY ROOT_ID) max_conf
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE DANIEL.UNIQUE_PHYSICIAN.FIRST_NAME = '' AND
DANIEL.UNIQUE_PHYSICIAN.LAST_NAME = '' AND DANIEL.UNIQUE_PHYSICIAN.IS_ROOT_PHYS = 0
AND DANIEL.UNIQUE_PHYSICIAN.POSTAL_CODE = '')

ON CONFIDENCE_IS_SAME = max_conf);

You need an alias for both derived tables. And the outer pair around the from clause is useless.

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM ( --<< only one opening parenthesis
  SELECT unique_id,
         confidence_is_same,
         first_name,
         last_name,
         postal_code
  FROM daniel.unique_physician
  WHERE daniel.unique_physician.first_name = ''
  AND   daniel.unique_physician.last_name = ''
  AND   daniel.unique_physician.is_root_phys = 0
  AND   daniel.unique_physician.postal_code = ''
) t1 ---<< the alias for the derived table is missing
  INNER JOIN (
    SELECT max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
    FROM daniel.unique_physician
    WHERE daniel.unique_physician.first_name = ''
    AND   daniel.unique_physician.last_name = ''
    AND   daniel.unique_physician.is_root_phys = 0
    AND   daniel.unique_physician.postal_code = ''
  ) t2 ON t1.confidence_is_same = t2.max_conf

But the join isn't needed in the first place. Your query can be simplified to:

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
  SELECT unique_id,
         confidence_is_same,
         max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
  FROM daniel.unique_physician unq
  WHERE unq.first_name = ''
  AND   unq.last_name = ''
  AND   unq.is_root_phys = 0
  AND   unq.postal_code = ''
) t1
where confidence_is_same = max_conf;

You don't need to select first_name, last_name and postal_code in the inner select as you don't use them in the outer select. This can make the query potentially more efficient.

Additionally, the condition unq.last_name = '' won't do what you think it does. Oracle does not have an "empty string". A string with length zero ('') will be stored as NULL, so what you really want is probably:

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
  SELECT unique_id,
         confidence_is_same,
         max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
  FROM daniel.unique_physician unq
  WHERE unq.first_name is null
  AND   unq.last_name is null
  AND   unq.is_root_phys = 0
  AND   unq.postal_code is null
) t1
where confidence_is_same = max_conf;


SQL Server - Restore only primary Filegroup but bring the database online I'm trying to find out if it's possible to bring a SQL Server 2016 Enterprise database online by restoring only the primary Filegroup. My scenario is, we have a fairly large OLTP database, of around 2TB. About 25% of that size is a single 'history' table, which as the name suggests, just tracks activity.
Each day, this database (in full recovery mode) is backed up and restored to several other environments where it is used for various, typically none-critical purposes. This restore process is slow due to the database size, and the 'history' table is obviously a significant contributer.
Although this 'history' table needs to exist in these other environments (it is referenced by numerous stored procedures), it doesn't need to contain any data. We just need it to exist but it could be blank, or ideally, just contain a few thousand of the most recent rows (not essential).
My first thought in achieving this was to:

Move the 'history' table to a secondary filegroup (the database has a single, primary filegroup currently)
Perform a backup of only the primary filegroup (this would probably be an extra 'copy_only' backup).
Restore this backup to the other environments.

The problem is, after step 3, I can bring the database online, however, I obviously can't reference the 'history' table, since it belongs to an FG that hasn't been restored. If I do, I get an error as expected:
The query processor is unable to produce a plan for the table or view 'History' because the table resides in a filegroup that is not online.
So my questions are:

Is what I'm trying to achieve even possible using this (or a similar) approach?
After step 3, is there a way by which I could 'drop' the secondary filegroup and recreate a blank copy of the 'history' table in the primary filegroup? This would allow the application/stored procedures that rely on its presence (but not it's data) to function. This doesn't appear possible without restoring the secondary filegroup first.
Is there just a flat-out better approach that I've not considered?

Note - The 'history' table is not referenced by any FKs, so there are no data integrity issues caused by not having the data there.
I'm aware that there are alternative ways of 'synchronising' multiple databases rather than a backup/restore (replication being one). Unfortunately, that is somewhat out of my control so for now, I'd like to focus on the backup/restore approach. Of course, I'm happy to hear suggestions.
UPDATE - Just to be clear, the rest of the data in the database is needed in each environment, but minus the 'history' table.

I know you mentioned it's a little bit out of your control, but if you don't need the data at all and just the schema itself, doing a backup and restore on a 2 TB database is going to be the least efficient way to accomplish your goal, by far, among probably 10 different options. (You should familiarize yourself with the alternatives and bring this to the attention of whoever's control it is in.)
Some of those alternative options are Replication (as you mentioned), SSIS, SQL Agent Job that keeps the other instances up to date, scripting out the database, or using a DACPAC to deploy just the schema, which would be my choice for you as it's the simplest / makes the most sense. (Also if your limitation in using a different features is because you're not allowed to setup anything on the server, DACPAC is an already enabled feature, and the process is simpler than backups since you just use SSMS to generate the file for you, similar to if you were generating scripts. No need to install or setup anything.)
If your schema doesn't change often, you can even dedicate one empty copy of the database as your "model" which always remains data-less and can easily be used to generate new databases off of (even if you still stick to the backup and restore methodology, since it'll be a tiny database). Or even if it does change often, you can still do the backup and restore to just this one model database, and then truncate the data or use a DACPAC there to generate the other instances so that way you don't need to wait on a 2 TB restore more than once.


How can I resolve a database trigger's name with built-in functions? I have a database trigger that I use to prevent me from creating certain procedures in user databases.

It appears in sys.triggers, with an object_id, but I can't use the object_id function to find it.

SELECT OBJECT_ID(t.name, t.type) AS object_id, *
FROM   sys.triggers AS t;

Likewise, I can find it in sys.dm_exec_trigger_stats. I can't get object_name to resolve, but object_definition does.

SELECT OBJECT_NAME(dets.object_id, dets.database_id) AS object_name,
       OBJECT_DEFINITION(dets.object_id) AS object_definition,
       *
FROM   sys.dm_exec_trigger_stats AS dets;

Is there a function that will accept a database level trigger's object id, and return its name?

Database- and server-level triggers are not scoped as "objects" per se (this is why you cannot create them under a schema, and why they don't show up in sys.objects).

You can see that these objects have certain restrictions on them, for example in the OBJECTPROPERTY() docs:

  This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

And similarly in the OBJECTPROPERTYEX() docs:

  OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

The OBJECT_ID() docs are a little more explicit:

  Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'.

The OBJECT_NAME() docs are less explicit, but they mention the same restriction implicitly (emphasis mine):

  Returns the database object name for schema-scoped objects.

For the first query, not sure why you need to get the name via the function, since the name column in sys.triggers already gives you that answer. For the second query, you could just join to sys.triggers:

SELECT tr.*, ts.*
FROM sys.dm_exec_trigger_stats AS ts
LEFT OUTER JOIN sys.triggers AS tr
ON ts.[object_id] = tr.[object_id];

You could create your own function, of course, but I don't know of any built-in functions that do this correlation for you (and I recommend staying away from built-in metadata functions generally anyway).

DDL triggers are kind of a special animal. So if you're worried about also having to join to sys.procedures, sys.views, etc., don't.


my oracle db won't open please help This is what happens when I try to query as DBA:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 10 22:28:00 2014

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dba_users;

select count(*) from dba_users
                     *
ERROR at line 1:   ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database open;

alter database open
*
ERROR at line 1:   ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [824], [95], [98], [], [], [], [],
> [], [], []

please help to restore my db...

ORA-600 errors are usually bad news. ORA-600 on OraFAQ.
If this is an environment you or anyone else cares about I recommend:
1) Opening an Oracle service request
2) Find your organizations DBA and engage them immediately
3) Dig those backups out because you may have a significant corruption of RDBMS binaries or data.

Best of luck!


MySQL function to return breadcrumb-like string from hierarchical structure Show create table:

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `a_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Select:

+----+---------+-----------+
| id | name    | parent_id |
+----+---------+-----------+
|  1 | World   |         1 |
|  2 | Europe  |         1 |
|  3 | USA     |         1 |
|  4 | France  |         2 |
|  5 | Germany |         2 |
+----+---------+-----------+

Question:

how do I best define a function which, passed id 4 as an argument, returns:

"World > Europe > France"

This hierarchical "nesting" by "parent_id" could be arbitrarily deep, not just 3 levels deep.

delimiter //
create function derp(IN myId int) returns varchar(255) begin
    select concat(t1.name, ' > ', t2.name, ' > ' t3.name) from a t1 join a t2 on t1.parent_id=t2.id join a t3 on t2.parent_id = t3.id where t1.id=myId;
end//

Not tested, just off the top of my head.  For reference http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html


CREATE PLUGGABLE DATABASE REFRESH MODE MANUAL from remote fails with ORA-00922 I'm doing some test with remote PDB cloning. Both local and remote instance are

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

The DB LINK is working, and the remote PDB exists

SELECT COUNT(*) FROM V$PDBS@REMOTE
WHERE NAME = 'REMOTEPDB';

But doing this on sqldeveloper

CREATE PLUGGABLE DATABASE mypdb FROM REMOTEPDB@REMOTE
  REFRESH MODE MANUAL;

Gave me this error:

Error starting at line : 1 in command -
CREATE PLUGGABLE DATABASE mypdb FROM REMOTEPDB@REMOTE
  REFRESH MODE MANUAL
Error report -
ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:
*Action:

create_pdb_clone::=

12.1 syntax:

12.2 syntax:

pdb_refresh_mode is available starting with version 12.2, it is not available in your version.