Recovery model from Full to simple and Shrink transact log result on poor performance I'm new mantaining databases, I'm more like programmer but you know in some jobs you must be multitools because theres no specific dba.

My boss told me to change the recovery model from Full to simple because there was no specific routine to back up log and store the backups and the result was a 1.5 gb transaction log with no other initial backups (or maybe missing, the guy how do the project left) besides they do not care about the possible dataloss on daily basis. So I followed the command.

We schedule a windows and I do the job on SSMS and do the following steps:

Make a full copy to database with -> Tasks Copy Databases.
Make a full and transact log copy on Database.
Go to database options and change Recovery model from full to simple.
Shrink the transaction log ->Tasks > Shrink > Files

In the Shrink file dialog the currently allocate space field was the 1.5gb but were about 1 gb the Avaliable free space so I left 600MB to the log file

Reboot...

So after this the information was able to query but the performances is bad as hell a stored procedure that take 0.6 sec to execute before the changes now take up to 12 secs to run.

Moreover I repeat this in database copies (from the copy that I do before changes) and realized that instead of shrink the space in disk it's growing exactly the space trying to shrink.

After this I try to see if was fragmentation and indeed there about the 40% of tables about up to 50% in avg_fragmentation_in_percent

SELECT
    OBJECT_NAME(object_id) AS tblName
    ,dbo.index_name(object_id, index_id) AS ixName
    ,avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE
    avg_fragmentation_in_percent > 20
    AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
ORDER BY
    avg_fragmentation_in_percent DESC

So I try to reorganize the index on the tables. Some go down to 20% but must still on ~ 45%

Finnaly I realised that the CPU was overheating more and more until reach 100% of CPU usage.

Here I used this query to make the what querys are cosuming more resources

SELECT TOP 50
     [Average CPU used] = total_worker_time / qs.execution_count,
     [Total CPU used] = total_worker_time,
     [Execution count] = qs.execution_count,
     [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
     (CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset END -
    qs.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    ,DatabaseName = DB_NAME(qt.dbid)
FROM
    sys.dm_exec_query_stats qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
    [Average CPU used] DESC;

Can anyone tell me what's happend?

Seen this happen a bunch.  Your first mistake was to shrink the transaction log.  When you set the recovery model to Simple the log is automatically truncated after a full backup of the Database.

What you have now is a combination of Index Fragmentation and Statistics needing to be rebuilt.  I would high recommend Ola Hallengren's free utility that can do both for you.

The following works for me after creating the procedures:

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL'

The second thing that happened is most likely your query cache got purged and it now has to build new statistics and has cause parameter sniffing on your queries.  Rebuilding your statistics should help.


Does MongoDB support two 2-way synchronization We are looking at moving our data from IBM Domino to another backend.  One of the things Domino does really well is replicate (or synchronize as they call it) between a Server and Clients.  That means that Users can take their mailbox, or any other application DB, with them and work offline and when they have access to the server the "master" on the Server synchs with the "slave".

Does MongoDB support two 2-way synchronization? Assuming that it does, does it also support selective replication, where the data that is pushed out to the Client can be controlled at a field level.

Thx in advance for any insight.

No, MongoDB does not support two-way synchronization. It has master-slave replication, where all writes must occur on the master node before being replicated to the slave nodes. You can read about all the replication capabilities of MongoDB in their documentation.

Couchbase does support Master/Master replication, through it's XDCR (Cross Datacenter Replication) function. http://docs.couchbase.com/admin/admin/XDCR/xdcr-intro.html

Full disclosure - I work for Couchbase.


Troubleshooting "Can't connect ..." from MySQL slave to master Although I can connect from slave to master via a command-line mysql client, my slave can't connect to the master as part of its replication task. SHOW SLAVE STATUS cites a "Permission denied" error:

Last_IO_Error: error connecting to master 'replicant@dbmaster:5000' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on 'dbmaster' (13 "Permission denied")

I've checked the user permissions as well as a command-line client connection. What else can I check?
The user has SELECT and REPLICATION SLAVE permissions on the master:
mysql -h dbmaster -P 5000 -u replicant "-p$PASS" --ssl -e 'SHOW GRANTS'
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+
# | Grants for replicant@192.168.187.101                                                                                |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+
# | GRANT SELECT, REPLICATION SLAVE ON *.* TO `replicant`@`192.168.187.101` IDENTIFIED BY PASSWORD '[HASH-REDACTED]' REQUIRE SSL |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+

And I've set up the slave to use the same username, password, host, port, ssl:
cat <<-EOF | mysql
STOP SLAVE;
CHANGE MASTER TO MASTER_SSL=1, MASTER_DELAY=259200,
MASTER_HOST='dbmaster', MASTER_PORT=5000,
MASTER_USER='replicant', MASTER_PASSWORD='$PASS',
MASTER_LOG_FILE='mysql-bin.041117', MASTER_LOG_POS=733093730;
START SLAVE;
EOF

The server is running Mariadb 10.3.

cat <<-EOF | mysql    Line OK
STOP SLAVE; Line OK
CHANGE MASTER 'AnyName4Slave' TO MASTER_HOST = '',MASTER_PORT=3306, MASTER_USER = 'user', MASTER_PASSWORD = 'password' ,MASTER_LOG_FILE='mysql-bin.041117', MASTER_LOG_POS=733093730;
START SLAVE 'AnyName4Slave';
EOF


How do I get full details of each person when joining tables I know this has to have a simple solution and I am not seeing it.

I have a table, person, which contains all information about each person such as first name, last name, age, address.

I have a second table called consumer_client which contains a row containing person id for each consumer and client like this:

id | consumer_person_id | client_person_id

What I want is to fetch both full names when I join person to client_consumer like this:

id | Joe Mama | Mike Smith

I cannot seem to get that with a simple JOIN syntax like

SELECT * FROM person JOIN client_consumer WHERE person.person_id = cc.person_id

as it always returns just the consumer_person_id and client_person_id along with other information in person. I want it replaced with the full name instead of person_id.

I think you are looking at this the wrong way around. You should be selecting from the client_consumer table and joining to the person table. This way the query is easily read and understood.

SELECT
    cc.id,
    consumer.full_name as consumer_full_name,
    client.full_name as client_full_name
FROM client_consumer cc
JOIN person consumer ON
    consumer.person_id = cc.consumer_person_id
JOIN person client ON
    client.person_id = cc.client_person_id

What you are asking to do is pretty straightforward, and I see some good answers already, but here is the simplest version. You need to join the consumer_client table twice to the person table. So

SELECT p1.*, p2.*
FROM consumer_client cc
LEFT JOIN person p1 ON p1.person_id = cc.consumer_person_id
LEFT JOIN person p2 ON p2.person_id = cc.client_person_id

Although you'll probably want to alias the columns in the select statement, since you'll get duplicate columns with a straight up SELECT *


Permission denied for relation I have ran the following SQL in psql:

CREATE USER bspu LOGIN;

CREATE DATABASE bsp OWNER bspu;

GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu;

\c bsp

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  api_key VARCHAR(100) NOT NULL,
  api_secret VARCHAR(100) NOT NULL,
  auth_token VARCHAR(128) NOT NULL
);

When I login as bspu, and try to query the users table, I get the error:

permission denied for relation users

I tried running:

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO bspu;

But it doesn't help. What am I doing wrong? Why does database owner NOT have permissions to query its own database?

EDIT:
I upgraded bspu to superuser for now, so I can continue work. Any further guidance appreciated.

DEFAULT PRIVILEGES do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not specify the role when running ALTER DEFAULT PRIVILEGES, it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.
Also, since you are using a serial column, which creates a SEQUENCE, you'll want to set default privileges for sequences as well.
Run this on the user you create objects with, before you run the CREATE command:
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON TABLES TO bspu;
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON SEQUENCES TO bspu;

A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES ignoring  the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.
For existing objects you may also be interested in this "batch" form of the GRANT command:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bspu;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO bspu;

More under this related question on SO:

Grant all on a specific schema in the db to a group role in PostgreSQL

This is working for me after some research. psql (9.6.12)

-- login to postgres database server as postgres user.
ssh <user>>@hostname.<domain>>.com
sudo su postgres
id
psql
postgres=# create user sentry_read with login password 'sentry_read';
postgres=# du+
postgres=# \l+
-- **very important: make sure we should connect required database before we grant to user.**
postgres=# \c sentry
postgres=# \dt
sentry=# \dp SENTRY_VERSION
sentry=# select * from "SENTRY_VERSION";
sentry=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO sentry_read;
postgres=# \q

[user@hostname ~]$ psql -U sentry_read -d sentry -h hostname.<domain>>.com -W
sentry=> select * from "SENTRY_VERSION";


Merging two large tables physically in SQL Server 2014 I have a requirement to merge two large tables with redundant data.

Table A and Table B both have more than a billion records, most of the data including columns are redundant except 5 additional columns in Table B.

I would like to merge them physically and delete table B by comparing the data between both table and load the data that doesn't exist in Table A but exist in Table B to Table A.

Ultimate goal is to get rid of Table B and load all data and columns to Table A and then partition table A.

Did anyone had experience doing this? How can I compare the data between two large tables? Can we use Informatica or any native SQL tools? Is it a good approach to merge the tables and partition Table A or first partition Table A and them merge the tables?

Please let me know if you need any details.

going to take a while on 3 billion records but this is a query

select tableB.*
from tableB
left join tableA
on tableA.ID = tableB.ID
where tableA.ID is null


Instant File Initialization | Lock Pages in Memory Issues I have a server that I'm coming onto performance tune and for the life of me I can'g get these rights enabled.  So I ran

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami
nofault\administrator

Then

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami /priv

PRIVILEGES INFORMATION
----------------------

Privilege Name                  Description                               State
=============================== ========================================= ========
SeLockMemoryPrivilege           Lock pages in memory                      Disabled
SeIncreaseQuotaPrivilege        Adjust memory quotas for a process        Enabled
SeSecurityPrivilege             Manage auditing and security log          Disabled
SeTakeOwnershipPrivilege        Take ownership of files or other objects  Disabled
SeLoadDriverPrivilege           Load and unload device drivers            Disabled
SeSystemProfilePrivilege        Profile system performance                Disabled
SeSystemtimePrivilege           Change the system time                    Disabled
SeProfileSingleProcessPrivilege Profile single process                    Disabled
SeIncreaseBasePriorityPrivilege Increase scheduling priority              Disabled
SeCreatePagefilePrivilege       Create a pagefile                         Disabled
SeBackupPrivilege               Back up files and directories             Disabled
SeRestorePrivilege              Restore files and directories             Disabled
SeShutdownPrivilege             Shut down the system                      Enabled
SeDebugPrivilege                Debug programs                            Enabled
SeSystemEnvironmentPrivilege    Modify firmware environment values        Disabled
SeChangeNotifyPrivilege         Bypass traverse checking                  Enabled
SeRemoteShutdownPrivilege       Force shutdown from a remote system       Disabled
SeUndockPrivilege               Remove computer from docking station      Disabled
SeManageVolumePrivilege         Perform volume maintenance tasks          Disabled
SeImpersonatePrivilege          Impersonate a client after authentication Enabled
SeCreateGlobalPrivilege         Create global objects                     Enabled
SeIncreaseWorkingSetPrivilege   Increase a process working set            Disabled
SeTimeZonePrivilege             Change the time zone                      Disabled
SeCreateSymbolicLinkPrivilege   Create symbolic links                     Disabled

No prob I go Local Security > Local Policies > User Rights Assignment grant the LockPages in Memory to Network Administrator and perfom volume maintenance tasks.  No Good.

I tried giving the privileges to everyone ( I know bad Idea but just to test )  still no good.  And yes I restarted SQL server after each try.  Has anyone had any similiar issues with this, real cheap performance gain and I usually have no problem with this.

Thanks

Try setting the services to run as domain/machine account, and give the permissions to that account

You can absolutely assign lock pages in a VM just dont give 100% of the VMs memory to SQL server. For e.g. for a server with 128 gigs of memory, Assign 4 to OS, 124 To SQL and enable lock pages you should be fine.


Trigger Performance : Is Using Trigger is a good way? We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, around 100 columns and 10K records and increasing rapidly on daily basis. Around 200 users work with this application and updates also occur very frequently.
I know handling it via code will require very frequent connections to database.
I want to know which is a better approach for doing this. Is handling it through code will provide a better performance than using SQL Server trigger for that.

Personally, I would use triggers for auditing.

If they fail, then the main DML gets rolled back
(same) The main update requires the DML to succeed
A table will typically have several DML paths
You may not be able to use code
You will have direct table update that bypass code at some point

Ok, so we'll use triggers. Some notes

Ensure they work for multiple rows: no CURSORs or loops
Use them only for auditing: nothing else (no, say, sending emails)
Ensure you use TRY/CATCH in the triggers: this massively improves the behaviour on rollbacks in triggers
CASCADE foreign keys will cause child table triggers to fire: Beware nesting and recursion and don't use CASCADEs if possible

This last list also addresses sonme points in @NevilleK's answer. Triggers are one tool to use: use wisely and when appropriate and correctly.

600 updates per hour is trivial, not a problem at all for triggers (subject to cursors, loops, sending email and other stupid stuff)

In general, I'd recommend against using triggers for any heavy lifting - it's not a question of whether the performance is "better" or "worse" - it's that performance becomes unpredictable and issus are hard to debug.

Most performance problems follow a hockey stick curve - performance declines roughly linearly for a while until you hit a bottleneck, and then performance declines exponentially. The trick with scaling your system is to work out where your inflection point is, and what you need to do to move it further away if necessary (e.g. add more RAM, tune your queries, introduce off-line processes etc.).

In my experience, triggers are hard for developers to work with - they are effectively a side effect of the intended action (e.g. they intend to update a record in your table, and as a side effect, the trigger writes and audit entry). I've seen several cases where a change to the "normal" SQL caused the trigger to become much slower as a result of an unintended consequence of the side effect. For instance, a developer who was tuning a slow query changed an index which caused the trigger associated with that table to run MUCH more slowly.

So, I'd recommend against using triggers unless your audit requirements are literally just copying data from one table to another.


How to structure a MongoDB database to allow for multiple users to use the same account with restrictions based on roles? Ok, firstly I'm very new to MongoDB, and newish to DB development/management as a whole. I've done a unit using Oracle through uni, but apart from just manipulating fields through the phpmyadmin interface for wordpress plugins/sites, that's about it.

I want to model a (fairly complex?) user system. Here's an excerpt from the brief relating to it.

Users should be able to log in with their email and password, create a new account, invite other users and set different access rights. This way multiple users can share the same account with different levels of authority (ready-only, editing rights, etc.)

The backend needs a section where the business owner can set global restrictions on areas/sections of the app for different groups of users.

I've done a bit of research into structuring a MongoDB database and understand there should be a balance between embedding data into documents, and referencing other documents so that in the end you require fewer queries than if you embedded everything or referenced everything.

Here are my initial thoughts.

Side note - I don't know how to properly document a MongoDB schema so sorry if this doesn't actually make sense. I'll try explain it as best I can.

The diamond connections are for embedded objects inside documents, while the straight connections show references. (i.e AccountUsers are embedded in an Account while a Role is referenced by a User and AccountUser). I should probably update this to show directions and relation. Anyway...

I believe this setup allows both Users on an application level, and users on an account level to have separate roles that can be retrieved later. Global restrictions could be set by the business owner for all users of a certain role, and the admin of an account can set account wide restrictions based on setting a user's account role.

The roles themselves are arrays of objects containing a boolean and a reference to a SitePermission. The idea being that the SitePermissions will be setup by the dev team, with one SitePermission for each section of the app, from which the business owner or account owners can create their own roles.

Does this setup seem like a good way to go about creating a database that suits the brief? Like I said, I'm really new to MongoDB so I could have this whole thing completely wrong by treating it similarly to an RDBMS like Oracle. I basically just need to know if I'm doing this right? Thanks!

When working on an authorization requirement for any application, there are three essential elements that are part of the setup:

•   The resources that need to be secured/authorized
•   A list of roles and users that are part of these roles
•   A mapping between the resources and the roles that define who can access what


Write differences between varchar and nvarchar Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that.

My question is are there any differences in how SQL Server writes to varchar columns vs. nvarchar columns? We have a number of backend procedures that I'm concerned about.

Edit:
Not sure if this helps, but the columns don't have indexes, f/k, or constraints on them.

You need to be sure that you prefix Unicode string literals with an N prefix. For example these will work differently if the underlying data type is NVARCHAR:

CREATE TABLE dbo.t(c NVARCHAR(32));

INSERT dbo.t(c) SELECT 'រៀន';
INSERT dbo.t(c) SELECT 'នរៀ';
INSERT dbo.t(c) SELECT N'រៀន';

SELECT c FROM dbo.t;

SELECT c FROM dbo.t WHERE c = 'រៀន';
SELECT c FROM dbo.t WHERE c = N'រៀន';

Results:

c
----
??? -- not stored correctly
??? -- not stored correctly
រៀន -- stored correctly!

c
----
???
??? -- probably not expected, however all Unicode characters have been changed to ?

c
----
រៀន

For those on mobile devices or decrepit browsers that show box characters instead of actual Unicode characters, this is what it looks like:

If you use NVarchar over Varchar and you have no requirement to support MULTI-LINQUAL,  you increase storage for DB, Backups (local and offsite).  Modern Databases should support both and any Conversion hits should be considered in the design.


Grant users privilege to use QUERYTRACEON 9481 Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120.

In my situation, "degraded" means going from a few seconds to several minutes or more. Performance wise, 9481 works extremely well; however, regular users get a nice error instead of query results (running SSRS reports):

User 'xxxxx' does not have permission to run DBCC TRACEON.

The documentation indicates that users must be members of the sysadmin role to run DBCC TRACEON.

Are there any workarounds to allow non-sysadmins to run queries that use this hint?

I'd rather not drop the compatibility level back down to 110 because the majority of our queries are acceptable or working better.

You can create a plan guide as sysadmin for the query, which would then allow the user to run the pre-existing plan.  Otherwise, rewriting the query is about all you can do.

Using QUERYTRACEON in plan guides

Instead of using OPTION (QUERYTRACEON 9481), here is a proposed solution/workaround .
It allows a non-sa user to change the Cardinality Estimator for the specific statement, query, or procedure call (for the current session).

Solution:

Wonderfully explained by Kimberly Tripp in her post on sqlskills.com:
  "Setting CE TraceFlags on a query-by-query (or session) basis"

Create a Stored Procedure on msdb database which allows setting of a desired Trace Flag without sysadmin permissions.
(of course, sysadmin takes care of setting-up a list of allowed Trace Flag values).
Wrap any problematic statement (dynamic sql, ad-hoc query, or procedure call) with a call to this Stored Procedure and change the session trace flag for execution.
This allows users with lower permissions to change the Cardinality Estimator for execution of the problematic statement.

Example usage:

EXEC msdb.dbo.msdbSetTraceFlag 9481, 1;
GO

Problematic STATEMENT or PROCEDURE

EXEC msdb.dbo.msdbSetTraceFlag 9481, 0;  -- don't remember to turn it back off!
GO

Stored Procedure code:

USE msdb;
GO

CREATE PROCEDURE msdbSetTraceFlag
    (@TraceFlag int,
     @OnOff bit = 0)
WITH EXECUTE AS OWNER
AS
DECLARE @OnOffStr char(1) = @OnOff;
-- Sysadmins can add supported trace flags and then use this
-- from their applications
IF @TraceFlag NOT IN (
              9481 -- LegacyCE if database is compat mode 120 or higher
            , 2312 -- NewCE if database compat mode 110 or lower
                     )
     BEGIN
         RAISERROR('The Trace Flag supplied is not supported. Please contact your system administrator to determine inclusion of this trace flag: %i.', 16, 1, @TraceFlag);
         RETURN
     END
ELSE
     BEGIN
         DECLARE @ExecStr nvarchar(100);
         IF @OnOff = 1
             SELECT @ExecStr = N'DBCC TRACEON(' + CONVERT(nvarchar(4), @TraceFlag) + N')';
         ELSE
             SELECT @ExecStr = N'DBCC TRACEOFF(' + CONVERT(nvarchar(4), @TraceFlag) + N')';
         -- SELECT (@ExecStr)
         EXEC(@ExecStr)
         -- RAISERROR (N'TraceFlag: %i has been set to:%s (1 = ON, 0 = OFF).', 10, 1, @TraceFlag, @OnOffStr);
     END;
GO

GRANT EXECUTE ON msdbSetTraceFlag TO PUBLIC --or to a specific set of users;
GO

Note: This Stored procedure is created in msdb, and not on master, because of "trustworthy" prerequisite, which is default for msdb.


Multiple update of records in the same table I have an update query e.g.:

UPDATE table SET name = ? WHERE id = ?

I have all the relevant ids collected. I am looping over the ids and executing the update query for each one. In pseudo code:

for (id..ids)  {
   stmt->executeUpdate(query, nameForId(id), id);
}

I was wondering is there a smarter/more efficient way to do this query update without looping? For instance, is there a way to create a big string once, like:

UPDATE table set a=? where id=? AND a=? where id=? ...etc.

Philᵀᴹ: Insert all of the data pairs into a temporary staging table, then run a single update. Will be lots quicker.

Kondybas: Create table pairs(id,name) from collected data and then:

UPDATE table
JOIN pairs ON table.id=pairs.id
SET table.name=pairs.name

Wiki answer generated from comments on the question.

Yes - you can concatenate your id's into a comma delimited string and then issue the following command:

update table set a=? where id in (?,?,?,?)


Proper way to store a value that could be multiple different types I have an Answers table and a Questions table.

The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what its intended answer value type should be.

It will be important to parse these Answer values at one point or another since the numbers, at least, will need to be compared.

For a little more context, the questions and potential answers (typically a data type allowed for a textbox type input) are supplied by some users in a survey of sorts. The answers are then supplied by other specified users.

A couple options I've considered are:

A. XML or string that gets parsed differently depending on the intended type (which is kept track of in the question)

B. Three separate tables that reference (or are referenced by) the Answer table and are joined to based on the intended type. In this case, I'm not sure of the best way to set up the constraints to ensure each question has only one answer, or if that should be left to the application.

C. Three separate columns on the Answer table that can be retrieved based on the intended type.

I'd be happy just to get some input on the pros and cons of these approaches, or alternate approaches I hadn't considered.

Based on what you have said I would use the following general schema:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
)
CREATE TABLE [dbo].[PollOption]
(
    [PollOptionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollQuestionId] INT NOT NULL,  -- Link to the question here because options aren't shared across questions
    [OptionText] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL  -- Remove this if you don't need to hide options

    CONSTRAINT [FK_PollOption_PollQuestionId_to_PollQuestion_PollQuestionId] FOREIGN KEY ([PollQuestionId]) REFERENCES [dbo].[PollQuestion]([PollQuestionId])
)
CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

You don't really care if the answer is a number, date, word, etc. because the data is an answer to a question not something you need to operate on directly.  Furthermore the data only has meaning in context to the question. As such an nvarchar is the most versatile human readable mechanism for storing the data.

The question and the potential answers would be gathered from the first user and inserted into the PollQuestion and PollOption tables.  The second user who answers the questions would select from a list of answers (true/false = list of 2).  You can also expand the PollQuestion table to include the creator's user id if appropriate in order to track the questions they create.

On your UI the answer the user selects can be tied to the PollOptionId value.  Together with the PollQuestionId you can verify that the answer is valid for the question quickly.  Their response if valid would be entered in the PollResponse table.

There are a couple potential problems depending on the details of your use case. If the first user wants to use a math question, and you don't want to offer multiple possible answers.  Another situation is if the options the initial user provides aren't the only options the second user can choose.  You could rework this schema as follows to support these additional use cases.

CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NULL,
    [PollQuestionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [AlternateResponse] NVARCHAR(50) NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

I would also probably add a check constraint to make sure that either an option is provided or an alternate response, but not both (option and alternate response), depending on your needs.

Edit: Communicating datatype for AlternateResponse.

In a perfect world we could use the concept of generics to handle various datatypes for the AlternateReponse.  Alas we don't live in a perfect world.  The best compromise I can think of is to specify what the AlternateResponse datatype should be in the PollQuestion table, and store the AlternateReponse in the database as an nvarchar. Below is the updated question schema, and the new datatype table:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [QuestionDataTypeId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
    -- Insert FK here for QuestionDataTypeId
)
CREATE TABLE [dbo].[QuestionDataType]
(
    [QuestionDataTypeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Description] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
)

You can list all available data types for question creators by selecting from this QuestionDataType table.  Your UI can reference the QuestionDataTypeId to select the proper format for the alternate response field.  You aren't limited to TSQL data types, so "Phone Number" can be a data type and you will get appropriate formatting/masking on the UI.  Also if required you can cast your data to the appropriate types via a simple case statement in order to do any kind of processing (select, validation, etc.) on the alternate answers.

I think the problem is given too much thought or there are some additional constraints in why certain answers might be more accaptable then others.
Currently there seems to be no evidence that the Answer would have to be processed in any way by the DB but just as a log field.

I would go with an NVARCHAR(MAX) and then just let the frontend deal with storing/retrieving the content.
Possibly a IS_CORRECT bit field where the frontend could store if the Answer is correct.


SELECT + INSERT or INSERT alone I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second.

The first time a device sends the data the server script should register (INSERT) that device id in the device table and add the data to the data table.

My question is, what would be faster?

To do a SELECT EXISTS query to determine whether an INSERT is required on the device table. If SELECT EXISTS returns false then execute an INSERT, otherwise do nothing.
To execute always an INSERT statement. Considering the device id is primary key on the table, if the device already exists it would return an error, otherwise it would insert it.

Only the first time a device sends data would require an actual INSERT, after that no INSERT would ever be required.

The PostgreSQL version is 9.4.

The table of devices is defined as follows:

CREATE TABLE common.tag
(
  customer integer,
  hostname character varying(150),
  description text,
  model integer,
  configprofile integer,
  id character varying(150) NOT NULL,
  host integer,
  CONSTRAINT tag_pk PRIMARY KEY (id),
  CONSTRAINT tag_fk_client FOREIGN KEY (customer)
      REFERENCES common.customer (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_configprofile FOREIGN KEY (configprofile)
      REFERENCES common.configprofile (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tag_fk_tagmodel FOREIGN KEY (model)
      REFERENCES common.tagmodel (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

In Postgres 9.5, the most elegant, simple, safe and fast solution is the new UPSERT implementation:

INSERT INTO device (device_id) VALUES (1234567)
ON CONFLICT (device_id) DO NOTHING;

-- now we have either inserted the device or it was there already
INSERT INTO data (device_id, data)
VALUES (1234567, 'some data);

Obviously, there must be a some kind of unique index or exclusion constraint on (device_id).
Details in the manual.

For older versions or for auto-generated serial IDs:

Is SELECT or INSERT in a function prone to race conditions?

In my opinion Select exists should be good in terms of performance. As select is faster than insert in terms of performance.Select only reads data and return the result, The insert query must read that data, insert in the table, then it check for primary key and found guilty of duplicate, then it revert the changes and raise an error.


Postgres to Postgres DBLink with AWS/RDS I am trying to use dblink functionality for Postgres 9.5.2 DB to Postgres 9.52 on AWS/RDS.

On the server:

CREATE SERVER myremotedb FOREIGN DATA WRAPPER
    dblink_fdw OPTIONS (host 'myremotedb.123456.eu-west-1.rds.amazonaws.com', port '5432', dbname 'my_db');

CREATE USER MAPPING FOR foouser SERVER myremotedb OPTIONS (user 'foouser', password '1234');

GRANT USAGE ON FOREIGN SERVER myremotedb TO foouser;

On the client:

SELECT dblink_connect('myremotedb', 'host=myremotedb.123456.eu-west-1.rds.amazonaws.com , port=5432 , user=foouser , password=1234 , dbname=my_db');

select * from dblink('myremotedb', 'SELECT * FROM my_data') AS T(id integer, name text);

but what I get is:

ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.

Any idea how can I start a connection?

You need to configure the remote server so that it demands a password when logging on as foouser to mwu.  Right now it is using one of the other authentication methods.

Change the "trust" to "md5" on pg_hba.conf


Join 2 tables and get the latest record I have 2 tables table1 contains the columns of id, status,created_by,created_date,name and table2 is a log table which contains id, updated_by,updated_date. Table2 contains multiple records of same id. I'm trying to get the resultant query which support to get the selected columns from both tables by join operation. but when I tried i'm getting the duplicate records associated with that id. I want only one latest record for that id. My query...

select  t.created_by,t.created_date,tl.updated_by,tl.updated_date
    from  test_name t
    join (
        SELECT  updated_by,updated_date,id
            from  test_log
            order by  updated_date desc
            limit  1
         ) tl  ON t.id=tl.id
    where  state = 'active';

You could return the sequential number of a row within a partition (in this case id) of a result set, starting at 1 (AND seqnum = 1) for the first row in each partition.

See below

SELECT t.created_by,
       t.created_date,
       tl.updated_by,
       tl.updated_date
FROM   test_name t
      INNER JOIN (SELECT tl.*,
            Row_number()
              OVER (
                partition BY id
                ORDER BY updated_date DESC ) AS seqnum
             FROM   test_log tl) tl
         ON t.id = tl.id
            AND seqnum = 1
WHERE  state = 'active';

If you want to get the latest records from log for each name then this can't be performed by queries only in an easy way (at least within MySQL dialect). The simplest way is to create the copy of the log table log_last but with UNIQUE index on the id and INSERT ON DUPLICATE KEY UPDATE syntax. Each record written into the log should be written into log_last too. IODKU ensure that old data will be overwritten by new ones and when you SELECT * FROM log_last you'll get all and only latest log lines for each id.


With Postgres, can I make a rule that will not recurse? The rule is already in place, and I'm hoping to fix the rule itself rather than change the whole process on how this works due to some bureaucracy I'd rather not deal with. Or in other words, I know there are other ways to achieve my goal, but I specifically want to know if it can be done somehow with a rule.
The current rule is that when an update occurs, another update fires to concatenate a few fields together. That update performed by the rule seems to call the rule again and generates an "infinite recursion" error.
Rule example:
ON UPDATE TO people DO
UPDATE people
SET "fullname" = concat(new."firstName", ' ', new."lastName")
WHERE people.id = new.id

Is there any way to tell the update that the rule does to not then call the rule again? Or to flag the rule with some sort of runOnce? It's my understanding rules alter the query, so I can't do something simple like AND people.fullname != new.fullname

If fullname column is never altered manually (i.e. it must match firstname and lastname values always) then simply use generated column. It will be (re)calculated automatically during insert/update:
ALTER TABLE people
    DROP COLUMN fullName,
    ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;

DEMO

If you truly want it to only run once for that particular record, you could make fullname a nullable field and change the rule to:
ON UPDATE TO people DO
UPDATE people
SET "fullname" = concat(new."firstName", ' ', new."lastName")
WHERE people.id = new.id
    AND fullname IS NULL


What's the (Big O) computational complexity of a PostgreSQL recursive common table expression? For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to determine the paths from a starting node.

The screenshot above shows the the input data, the recursive CTE result, and a visualization of the source data.

Recursive CTE are iterative over the preceding result -- right? (as suggested in the accepted answer here) -- so would the time complexity be something like O(log n)?

OK, I'm going to propose a solution.  Using the excellent article "A Gentle Introduction to Algorithm Complexity Analysis" as a guide, I believe the worst-case complexity of the example in my question (above) is as follows.

Given this recursive CTE:

WITH RECURSIVE nodes(id, src, path, tgt) AS (
    -- Anchor member:
    SELECT id, s, concat(s, '->', t), t,
    array[id] as all_parent_ids
    FROM tree WHERE s = 'a'
UNION ALL
    -- Recursive member:
    SELECT t.id, t.s, concat(path, '->', t), t.t, all_parent_ids||t.id FROM tree t
    JOIN nodes n ON n.tgt = t.s
    AND t.id <> ALL (all_parent_ids)
)
-- Invocation:
SELECT path FROM nodes;

At the Anchor member (query definition), the algorithm selects each row from the table; therefore, at this step the maximum number of iterations (i) and the maximum size (n) is the number of rows in table; i < n, if a starting point within the table is specified.
The Recursive member selects each row from the table, starting from the position specified in the anchor member, so the maximum number of iterations here once again is: i ≤ n.

So, with the recursive CTE above I believe that the overall complexity is Θ(n).

(From your companion blog)
The query returns these 13 rows:

 a->b
 a->b->a
 a->b->c
 a->b->d
 a->b->c->e
 a->b->d->e
 a->b->c->h
 a->b->d->e->f
 a->b->c->e->f
 a->b->c->e->f->g
 a->b->d->e->f->g
 a->b->d->e->f->g->h
 a->b->c->e->f->g->h

The execution plan ( aka explain plan / query plan):

CTE Scan on nodes  (cost=322.86..356.54 rows=1684 width=32) (actual time=0.027..0.120 rows=13 loops=1)
   CTE nodes
     ->  Recursive Union  (cost=4.18..322.86 rows=1684 width=132) (actual time=0.025..0.110 rows=13 loops=1)
           ->  Bitmap Heap Scan on tree  (cost=4.18..12.65 rows=4 width=132) (actual time=0.024..0.024 rows=1 loops=1)
                 Recheck Cond: (s = 'a'::text)
                 Heap Blocks: exact=1
                 ->  Bitmap Index Scan on tree_s_t_key  (cost=0.00..4.18 rows=4 width=0) (actual time=0.005..0.005 rows=1 loops=1)
                       Index Cond: (s = 'a'::text)
           ->  Hash Join  (cost=1.30..27.65 rows=168 width=132) (actual time=0.009..0.012 rows=2 loops=6)
                 Hash Cond: (t.s = n.tgt)
                 Join Filter: (t.id <> ALL (n.all_parent_ids))
                 Rows Removed by Join Filter: 0
                 ->  Seq Scan on tree t  (cost=0.00..18.50 rows=850 width=68) (actual time=0.001..0.002 rows=10 loops=6)
                 ->  Hash  (cost=0.80..0.80 rows=40 width=96) (actual time=0.001..0.001 rows=2 loops=6)
                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                       ->  WorkTable Scan on nodes n  (cost=0.00..0.80 rows=40 width=96) (actual time=0.000..0.001 rows=2 loops=6)

  Execution plans are read innermost-indent to outermost, from bottom to
  top. Aligned indented operations belong to a parent operation located
  above them, that has less indentation.

  There are 2 sets of brackets after the operations, the first is an
  estimated cost so I will ignore that and talk about the actual costs.

The "Anchor" part of the CTE corresponds to the Bitmap Index Scan ( and Bitmap Heap Scan after it which is really the second stage of the same scan) and it shows actual rows = 1 and loops = 1 so we can simplify and assume that whole operation would be O(1)
The "Recursive" part of the CTE corresponds to the Hash Join. A hash is created on a WorkTable, an in-memory structure that it has reserved 9kB of memory for, and uses a hash function with 1024 possible buckets - which it deems enough for the query. How does initializing the WorkTable contribute to the complexity? Should it be ignored because it's an implementation detail or is it significant?
The WorkTable has 2 rows and is looped over 6 times, but each time it is scanned does the WorkTable have the same contents? How could it since the array is accumulating and arrows are being concatenated to the previous iteration's s & t? It must be modified or rebuilt on each loop. So is that 6 rebuilds of 2 rows plus 6 scans of 2 rows? There are implementation details that are not surfaced by the execution plan so hard to say without profiling the Postgres C source code.
That WorkTable is used as the Hash to probe the other table in the hash-join. That other table is tree and there is a Sequential Scan (Seq Scan) on tree that contains 10 rows, and it loops 6 times, 10 * 6 = 60. So yes i <= N as stated in your answer, but I don't think it is right to say the complexity is the same as i, it is i * n because each iteration is a sequential scan over the full tree table, not just one row.
The final query results (the CTE Scan in the very first line of the plan) says there are 13 rows and 1 loop. That 13 consists of the 1 anchor row plus the 2 * 6 = 12 rows returned by the Hash Join. Does that count as another 13? I would say yes because the step takes more than zero time; the Recursive Union finishes at 0.110 and the CTE Scan at 0.120.

So adding it all up

1 (anchor)
? ( Initialize WorkTable, contributes some small unknown time complexity)
12 ( worktable scan 2 rows * 6 loops)
12 ( hash 2 rows * 6 loops)
60 ( sequential scan of 10 rows of `tree` table * 6 loops)
12 ( hash join of 2 rows * 6 loops -  is this double or triple counting?)
13 ( Final CTE Scan)

= 110

If N = 10 (rows) then this is more like O(n^2)

Quadratic complexity, similar to a nested-loop operation, which is a reasonable comparison. A hash join is not a nested-loop join, it's an optimisation, but in the context of recursion it's not just doing it once.

I notice at the end of your blog you mention

  while embedding complex graphs in a RDB (arguably) is not a good idea,
  given that recursive CTE appear to be an efficient algorithmic
  approach to determining paths in trees/graphs, that is not one of the
  reasons

I would have to disagree. Recursive CTE's are not efficient, they are effectively explicit loops and loops are usually bad news in SQL. There are some situations where they can't be avoided but should be if possible. I would agree about using cypher/gremlin/ some other graph specific language or graph database for this use case though. It's not so much they have better algorithms for this but rather that have some good tricks for pre-calculating and caching paths, ancestors and descendants & storing redundant data structures at graph-build-time in order to speed up run-time queries.


How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records? I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this

CREATE TABLE "SOMEDATA" (
    "ID" INTEGER NOT NULL,
    "ITEMNAME" VARCHAR2 NOT NULL,
    "LINKLEFT" VARCHAR2 DEFAULT NULL,
    "LINKRIGHT" VARCHAR2 DEFAULT NULL,
    "CREATE_TIMESTAMP" TIMESTAMP NOT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL
)

The purpose of this table is to store timestamped records which may link to other records, similar to a hierarchy. My challenge is build a query that at any given specific timestamp, and for a given itemname, recursively fetch the latest versions of the rows that chain to the right of the item. E.g. in the following data:

+----+----------+----------+-----------+---------------------+------------+----------+--+
| Id | Itemname | Linkleft | Linkright |  create_timestamp   |   value1   |  value2  |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+
|  1 | item1    | (null)   | item2     | 23/08/2014 00:00:00 | Test       | Data     |  |
|  2 | item2    | item1    | item3     | 23/08/2014 00:01:25 | Some       | More     |  |
|  3 | item3    | item2    | (null)    | 23/08/2014 00:05:00 | Even       | MoreData |  |
|  4 | item2    | item1    | item3     | 24/08/2014 00:00:00 | Changed    | Data     |  |
|  5 | item1    | (null)   | item2     | 25/08/2014 12:37:02 | New        | Better   |  |
|  6 | item3    | item2    | item4     | 27/08/2014 00:00:00 | Lipsum     | Lopsum   |  |
|  7 | item4    | item3    | (null)    | 27/08/2014 00:01:00 | Additional | Stuff    |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+

For the query ITEMNAME='item1' and CREATE_TIMESTAMP=23/08/2014 14:58:59, I need to return records #1,2 and 3. This is because these are the latest linked records that link to the latest record for 'item1' at that particular point in time. However, for the query ITEMNAME='item1' and CREATE_TIMESTAMP=01/01/2015 12:00:00, I need to return records #5,4,6 and 7, because at that time, these records were the latest versions.

I've been playing about with Oracle's CONNECT BY PRIOR to build something like

SELECT * FROM "SOMEDATA" START WITH "ITEMNAME"="item1" CONNECT BY PRIOR "LINKRIGHT"="RIC"

This obviously chains the records together, but pays no attention to timestamps and returns all values for rows which match.

It seems that hierarchical records is a nicely solved problem, but not in the case where you want to also store the historical values for records in the hierarchy and query for any time. Is this sort of thing possible in this table structure, or am I heading down the wrong track entirely?

how about:

with preresult as (
SELECT id,itemname,
   linkleft,
   linkright,
   create_timestamp,
   value1,
   value2,
   ROW_NUMBER ()
      OVER (PARTITION BY itemname ORDER BY create_timestamp DESC)
      rownumber
   FROM somedata
   WHERE create_timestamp < :timestamp)
select * from preresult where rownumber < 2

and enter as :timestamp the desired date and time

hth,
macfly.


How long will a temporary MEMORY table persist if I don't drop it (MySQL) I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary table within the procedure...

BEGIN;

/* generates the temporary table of ID's */
CALL fetch_inheritance_groups('abc123',0);

/* uses the results of the stored procedure in the WHERE */
SELECT a.User_ID
FROM usr_relationships r
INNER JOIN usr_accts a ON a.User_ID = r.User_ID
WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)
GROUP BY r.User_ID;

COMMIT;

When calling the procedure, the first value is the top ID of the branch I want, and the second is the tier which the procedure uses during recursions. Prior to the recursive loop it checks if tier = 0 and if it is it runs:

DROP TEMPORARY TABLE IF EXISTS id_list;
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;

So my question is: If I don't DROP the temporary MEMORY table at the end of the procedure, or within my transaction, how long will that table persist in memory?  Is it automatically dropped once the session ends, or will it remain in memory as long as the connection is open?

**N.B. The obvious answer might be to drop the temp table prior to the commit statement, but lets assume for a moment that I can't do that.*

EDIT: To be a little more precise, what if persistent connections are employed, will the table persist through multiple requests?  So far it seems that it will and that we would need to explicitly remove the temp table to free up that resource.

UPDATE: Based on the advice from the commenters, I've found a way of adjusting my stored procedure so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the stored procedure and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

CALL fetch_inheritance_groups('abc123','0',@IDS);

... then within the stored procedure, I added a second IF tier = 0 at the very end with the following:

IF tier = 0
    THEN
    SELECT GROUP_CONCAT(DISTINCT iid SEPARATOR ',') FROM id_list INTO inherited_set;
    DROP TEMPORARY TABLE IF EXISTS id_list;
END IF;

So the result of the stored procedure is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)

... is now ...

WHERE r.Group_ID = 'abc123' OR FIND_IN_SET(r.Group_ID,@IDS)

Voila!  Thanks to the commentors for your input, and for giving me the reason I needed to try a little harder :)

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.
According to the Book

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.
It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session --  it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.
I hope this answers your question.
UPDATE 2014-01-31 11:26 EST
In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.

CREATE TEMPORARY TABLE  IF NOT EXISTS temp (Months VARCHAR(50),Sequence INT)
AS (
SELECT
CONCAT(MONTHNAME(m1),' ',YEAR(m1)) AS Months,CONVERT(m1,DATE) AS Sequence
FROM
(
SELECT
('2014-01-01' - INTERVAL DAYOFMONTH('2014-01-01')-1 DAY)
+INTERVAL m MONTH AS m1
FROM
(
SELECT @rownum:=@rownum+1 AS m FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
(SELECT @rownum:=-1) t0
) d1
) d2
WHERE m1<= '2015-07-30'
ORDER BY m1
) ;

SELECT t.Months,A.OpenCount,A.CloseCount FROM Temp T
 LEFT JOIN ( SELECT  CONCAT(MONTHNAME(e.dtcdate),' ',YEAR(e.dtcdate)) AS Months,
 ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr
 JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr='open' AND csr.dtcdate >='2014-01-01' AND csr.dtcdate <='2015-07-30' AND csr.ddlArea=e.ddlArea ) AS OpenCount
 ,
 ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr
 JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr='Close' AND csr.dtcdate >='2014-01-01' AND csr.dtcdate <='2015-07-30' AND csr.ddlArea=e.ddlArea ) AS CloseCount

 FROM csrcrn_frmempengagreqs e
 INNER JOIN master_detail m ON e.ddlcsstatus=m.masterDetailId
  WHERE  e.dtcdate >='2014-01-01' AND e.dtcdate <='2015-07-30'
 GROUP BY MONTH(e.dtcdate) ORDER BY e.dtcdate
 ) A ON CONVERT(A.Months,CHAR(20))=CONVERT(T.Months,CHAR(20))
       ORDER BY T.Sequence;
       DROP TEMPORARY TABLE  IF EXISTS temp;

/* given query give result successfully... when put this query in USP then showing error plz help me..proc is given below */

DELIMITER $$

DROP PROCEDURE IF EXISTS `usp_GetEngMonthlyChart_Test`$$

CREATE DEFINER=`root`@`%` PROCEDURE `usp_GetEngMonthlyChart_Test`(IN DateFrom DATE,IN DateTo DATE)
BEGIN
      -- SET @strWhere= CONCAT(' AND CSR.dtcInductionDate BETWEEN ''',CONVERT(DateFrom,DATE),''' AND ','''',CONVERT(DateTo,DATE),'''');

    SET @strSql=CONCAT('

    CREATE TEMPORARY TABLE  IF NOT EXISTS temp (Months VARCHAR(50),Sequence INT)
    AS (
    SELECT
    CONCAT(MONTHNAME(m1),'' '',YEAR(m1)) AS Months,CONVERT(m1,DATE) AS Sequence
    FROM
    (
    SELECT
    (''',DateFrom,''' - INTERVAL DAYOFMONTH(''',DateFrom,''')-1 DAY)
    +INTERVAL m MONTH AS m1
    FROM
    (
    SELECT @rownum:=@rownum+1 AS m FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
    (SELECT @rownum:=-1) t0
    ) d1
    ) d2
    WHERE m1<= ''',DateTo,'''
    ORDER BY m1
    )' );

         SET @strSql=CONCAT(@strSql,'; GO SELECT t.Months,A.OpenCount,A.CloseCount FROM Temp T
     LEFT JOIN ( SELECT  CONCAT(MONTHNAME(e.dtcdate),'' '',YEAR(e.dtcdate)) AS Months,
     ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr
     JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr=''open'' AND csr.dtcdate >=''',DateFrom,
     ''' AND csr.dtcdate <=''',DateTo,''' AND csr.ddlArea=e.ddlArea ) AS OpenCount
     ,
     ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr
     JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr=''Close'' AND csr.dtcdate >=''',DateFrom,
     ''' AND csr.dtcdate <=''',DateTo,''' AND csr.ddlArea=e.ddlArea ) AS CloseCount

     FROM csrcrn_frmempengagreqs e
     INNER JOIN master_detail m ON e.ddlcsstatus=m.masterDetailId
      WHERE  e.dtcdate >=''',DateFrom,''' AND e.dtcdate <=''',DateTo,'''
     GROUP BY MONTH(e.dtcdate) ORDER BY e.dtcdate
     ) A ON CONVERT(A.Months,CHAR(20))=CONVERT(T.Months,CHAR(20))
           ORDER BY T.Sequence;
           DROP TEMPORARY TABLE  IF EXISTS temp;');

    SELECT @strSql;
    PREPARE stmt FROM @strSql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END$$

DELIMITER ;

CALL usp_GetEngMonthlyChart_Test('2014-01-01','2015-07-30')


Problem of SUM of the SUM When I execute my query I have always the same problem :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|Total
------------------------------------------------------------
  2016       |    7        |  0     | 0.40   |  0      0.40
  2016       |    7        |  0.50  | 0      |  0      0.50
  2016       |    7        |  0     | 0      |  0.30   0.30

How I can combine to have a single line :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|
-------------------------------------------------------
  2016       |    7        |  0.50  | 0.40   |  0.30

This is my query :

SELECT
   MONTH(created_at) as month_created,
   YEAR(created_at) as year_created,
   1 - SUM(price)/SUM(order_total) as Total,
   CASE loc_id WHEN 1 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_1',
   CASE loc_id WHEN 2 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_2',
   CASE loc_id WHEN 3 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_3'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
  GROUP BY year_created, month_created
  ORDER BY year_created asc, month_created desc

Problem is that the SUM(order) can be 0 in the CASE statement. Create a sub-query that delivers the price and order totals per loc_id and then use this to do the calculation if the sum of order is not 0:

SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order'

Then the SQL look something like (check if this is possible in vertica):

SELECT year_created, month_created, 1 - total
       1 - CASE loc_id_1_order WHEN 0 THEN 0 ELSE loc_id_1_price/loc_id_1_order END as loc_id_1,
       1 - CASE loc_id_2_order WHEN 0 THEN 0 ELSE loc_id_2_price/loc_id_2_order END as loc_id_2,
       1 - CASE loc_id_3_order WHEN 0 THEN 0 ELSE loc_id_3_price/loc_id_3_order END as loc_id_3
FROM
  (SELECT
     MONTH(created_at) as month_created,
     YEAR(created_at) as year_created,
     SUM(price)/SUM(order_total) as Total,
     SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
     SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order',
     SUM(CASE loc_id WHEN 2 THEN price ELSE 0 END) AS 'loc_id_2_price',
     SUM(CASE loc_id WHEN 2 THEN order ELSE 0 END) AS 'loc_id_2_order',
     SUM(CASE loc_id WHEN 3 THEN price ELSE 0 END) AS 'loc_id_3_price',
     SUM(CASE loc_id WHEN 3 THEN order ELSE 0 END) AS 'loc_id_3_order'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
   GROUP BY year_created, month_created)
ORDER BY year_created asc, month_created desc

Try this:

select distinct year_created, month_created,
  max(loc_id_1) over (partition by year_created, month_created) as loc_id_1,
  max(loc_id_2) over (partition by year_created, month_created) as loc_id_2,
  max(loc_id_3) over (partition by year_created, month_created) as loc_id_3,
  sum(loc_id_1) over (partition by year_created, month_created) + sum(loc_id_2) over (partition by year_created, month_created) + sum(loc_id_3) over (partition by year_created, month_created) as total
from (
  -- your query
) z;

Or in general, read up on analytic queries.


Normalizing team leader Each user is on exactly one team. A team has exactly one user in the team_lead role. How can we normalize this in the database? Examples in pseudo-SQL:

Strategy 1:

table user:
id       int  pk
team_id  int  fk references team(id)
is_lead  int  //1 or 0

table team
id            int  pk

Problem: Multiple users on one team could be marked as the team lead

Strategy 2:

table user:
id       int  pk
team_id  int  fk references team(id)

table team
id            int  pk
team_lead_id  int  fk references user(id)

Problem: There is no guarantee the referenced team lead is actually a member of that team

Is there any way to normalize this in the database design, or do we have to use constraints or enforce on the front-end?

There's a (standard SQL) way of getting the guarantees you want, but I'm afraid mySQL 5.7 is not able to handle it. My example has been tested on PostgreSQL.

You can define your users table this way:

-- Schema for everything
CREATE SCHEMA teams ;

-- Users
CREATE TABLE teams.users
(
  user_id integer NOT NULL PRIMARY KEY,
  team_id integer,
  CONSTRAINT users_team_id_user_id_key UNIQUE (user_id, team_id)
);

Then, have your teams have be defined in the following fashion:

-- Teams have one team_lead
CREATE TABLE teams.teams
(
  team_id integer NOT NULL PRIMARY KEY,
  team_lead_id integer NOT NULL,

  -- This constraint ensures that 'team_lead_id' is a 'user_id', and
  -- this user is actually a member of the team
  CONSTRAINT teams_team_lead_id_fkey FOREIGN KEY (team_lead_id, team_id)
      REFERENCES teams.users (user_id, team_id) MATCH FULL
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED
);

By using a composite foreign key constraint you actually make sure that the user you are checking for, is actually a member of the team.

After this second table has been defined, you can now add the foreign key constraint users.team_id -> teams.team_id:

-- The 'team_id' on users is a
ALTER TABLE teams.users
  ADD CONSTRAINT users_team_id_fkey FOREIGN KEY (team_id)
      REFERENCES teams.teams (team_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED ;

Note that both tables are referrrig to each other. You have circular references.

Being this the case: the constraints must be checked at the end of a transaction, not just at the end of a statement. In the case of PostgreSQL, they need to be defined DEFERRABLE and INITIALLY DEFERRED (this is what mySQL cannot handle). You need to enter data for the first team (table teams) and its team leader (table users) all in one transaction, or, otherwise, there's no way to satisfy both FOREIGN KEY constraints:

Let's assume these are 'user 1' and 'team 5':

BEGIN TRANSACTION ;
INSERT INTO teams.users (user_id, team_id)      VALUES (1, 5) ;
INSERT INTO teams.teams (team_id, team_lead_id) VALUES (5, 1) ;
COMMIT TRANSACTION ;

Unfortunately, mySQL 5.7 documentation about foreign key constraints states:

  [...] InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

MariaDB documentation about FKs doesn't have mentions about deferrability.

It's not at all complicated. You already must have a cross reference/intersection table for the m-m User-Team relationships:

create table TeamPlayers(
  TeamID   int  not null references Teams( ID ),
  PlayerID int  not null referneces Users( ID ),
  constraint PK_TeamPlayers primary key( TeamID, PlayerID ),
  constraint UQ_TeamPlayers_Player unique( Playerid )
);

The FK constraint is typical of such tables. It assures the same player cannot be listed more than once for the same team.

The Unique constraint assures the same player cannot be listed more than once across all teams.

You would think that the Unique constraint renders the PK superfluous. Not really.

Though it can be placed in a separate table, the team leader is an attribute of the team so I'll show it as part of the Teams table:

create table Teams(
  ID    int  auto_generate primary key,
  ...
  Lead  int,
  ...,
  constraint FK_TeamsLeader( ID, Lead )
    references TeamPlayers( TeamID, PlayerID )
);

By referencing the FK to the intersection table, we guarantee that the team leader is a user already defined as a player on the team.

So you guarantee that each user is on exactly one team and a team has exactly one user in the team_lead role and that user is one that is playing on the team..


For recent versions of SQL-Server that support CTEs (and not the obsolete 2000):

Using a CTE and copying a trick of @Paul White in this answer (to get TOP in the recursive part of the CTE): How to recursively find gaps where 90 days passed, between rows

WITH CTE AS
(
    -- Anchor:
    -- Start with the earliest date in the table
    SELECT TOP (1)
        T.id, T.update_time, T.price
    FROM tableX AS T
    ORDER BY
        T.update_time

    UNION ALL

    -- Recursive part
    SELECT
        SQ1.id, SQ1.update_time, SQ1.price
    FROM
    (
        -- Recursively find the earliest date that is
        -- more than 5 minutes after the "current" date
        -- and set the new date as "current".
        -- ROW_NUMBER + rn = 1 is a trick to get
        -- TOP in the recursive part of the CTE
        SELECT
            T.id, T.update_time, T.price,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.update_time)
        FROM CTE
        JOIN tableX AS T
            ON T.update_time >= DATEADD(minute, 5, CTE.update_time)
    ) AS SQ1
    WHERE
        SQ1.rn = 1
)
SELECT id, update_time, price
FROM CTE ;

A cursor solution, that works in older versions (I don't have a 2000 instance to test and the following will probably need adjustments). Modified from another answer, in the same question How to recursively find gaps where 90 days passed, between rows:

(first, some needed tables and variables):

-- a table to hold the results
DECLARE @cd TABLE
(   id INT NOT NULL,                        -- adjust types
    update_time DATETIME PRIMARY KEY,       -- according to
    price DECIMAL(10,2) NOT NULL            -- your columns
);

-- some variables
DECLARE
    @id INT,
    @update_time DATETIME,
    @price DECIMAL(10,2),

    @diff INT,
    @previous_update_time DATETIME = '1900-01-01 00:00:00' ;

The actual cursor:

-- declare the cursor
DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT id, update_time, price
      FROM tableX
      ORDER BY update_time ;

-- using the cursor to fill the @cd table
OPEN c ;

FETCH NEXT FROM c INTO @id, @update_time, @price ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @diff = DATEDIFF(minute, @previous_update_time, @update_time) ;

    IF @diff >= 5
      BEGIN
        INSERT @cd (id, update_time, price)
            VALUES (@id, @update_time, @price) ;

        SET @previous_update_time = @update_time ;
      END ;

    FETCH NEXT FROM c INTO @id, @update_time, @price ;
END

CLOSE c;
DEALLOCATE c;

And getting the results:

-- get the results
SELECT id, update_time, price
    FROM @cd
    ORDER BY update_time ;

Tested at SQLfiddle (in 2008 version).

I am not much sure on SQL Server, but this works with PostgreSQL.

Using Recursive CTE to start with the day, more comments are inline.

    create table amn1
    (
    id int,
    upd timestamp,
    price float);
     insert into amn1 values
    (5 ,'2015-07-17 09:02:00.000', 65.5),
    (5 ,'2015-07-17 09:03:00.000', 65.5),
    (5 ,'2015-07-17 09:05:00.000', 65.5),
    (5 ,'2015-07-17 09:06:00.000', 66),
    (5 ,'2015-07-17 09:07:00.000', 66),
    (5 ,'2015-07-17 09:08:00.000', 66),
    (5 ,'2015-07-17 09:16:00.000', 66.5),
    (5 ,'2015-07-17 09:17:00.000', 66.3),
    (5 ,'2015-07-17 09:18:00.000', 66.25),
    (5 ,'2015-07-17 09:19:00.000', 66.3),
    (5 ,'2015-07-17 09:20:00.000', 67.3),
    (5 ,'2015-07-17 09:21:00.000', 67.5);

    WITH RECURSIVE dtls (ID, upd, price) AS (
        SELECT
            amn1.ID,
            amn1.upd,
            price
        FROM
            (
                SELECT ID, MIN (upd) AS upd
                FROM amn1
                WHERE upd >= now() - INTERVAL '1 day'--To Start from today
                GROUP BY ID) T
        INNER JOIN amn1 ON (amn1. ID = T . ID AND amn1.upd = T .upd)--To get today's values
        UNION ALL
            (
                SELECT amn1. ID, amn1.upd, amn1.price
                FROM
                    amn1
                INNER JOIN dtls ON (amn1. ID = dtls. ID AND amn1.upd >= (dtls.upd + INTERVAL '5 mins'))--Find next earliest updated value
                ORDER BY amn1.upd
                LIMIT 1--Limit with one
            )
    )
    SELECT * FROM dtls


SQL Server Transactional Replication delay I am running into strange situation currently. I have a transactional replication configured between 2 servers. When I update a record in one article it gets replicated after 30 mins. I am unable to figure out why.

Log reader and distributor agent is setup to run continuously. There is no replication latency/errors found in replication monitor. But still it happens.

A tracer token is very fast. It just shows 5 secs as overall latency. The article I am updating is added into multiple publication and my environment is SQL Server 2012. No blocking. SQL Server is running fine. I don't see any resource intensive queries running on the server.

I would appreciate if any one could help me figure out this issue.

If the tracer token goes through in about 5 seconds and your updated data is taking 30 minutes, then the data has not been committed on the publisher yet. The tracer token would not have gone through if there was an un-replicated row ahead of it. Run a trace or an XE session on the publisher to see what is going on there.

In such scenario (been there done that), I'd suggest that you use tracer token to figure out whether the delay is caused by Log Reader Agent or Distribution Agent.
Here is the link https://msdn.microsoft.com/en-us/library/ms151178.aspx

Very generic guideline is:

Check the log size of the publishing database, if it is too big, log reader agent may take long time to process it.
Check whether there is any blocking on the publisher side. When you
update a record in an article, does that update really get
committed? i.e. the t-log is written to the log file?
Check whether your distributor is busy and esp. whether it has a long list of transaction commands that need to be distributed?

This article is a good example of using tracer token to debug latency issue
https://blogs.msdn.microsoft.com/repltalk/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens/

Hope this helps.


Where can I get this nice phpMyadmin features for mongodb In phpmyadmin

Say I browse a table, or do anything.

I will be told the actual sql command that I should run to execute the query.

Say I want to do a query and then I want to do something else similar. I can just run some query. Copy the SQL command, modify it, and move on. This is obviously less error prone than writing queries from scratch, see that it doesn't work, and not knowing what's wrong.

In rockmongo I don't have that feature.

Say I want to search for certain record. Then I want to modify the search with additional search. I can't do that.

So what other software to access mongodb that give that feature?

Another related feature is a feature to run any sql command I wish in phpmyadmin.

I do not have that feature in mongodb.

Technically you don't have this feature in MySQL either - PHPMyAdmin is a third part tool just like rockmongo, but basically rockmongo does not have the features you want.  I am sure there are features people would like in PHPMyAdmin too that are not there yet.  When that happens you basically have 3 options:

Request the feature from the people running the project
Write it yourself (if the current tool is open source you could fork and extend etc.)
Use another tool that does have the feature you want

The effectiveness of 1 and 2 will depend on the feature you want, how easy it is to implement, what your skills are, how active the project is etc.

For number 3, there are other options for a GUI interface to MongoDB.  MongoVUE seems to have the view option you want, for example - however it is Windows based:

http://www.mongovue.com/features/

There are others too, MongoHub, PHPMoAdmin etc. - you can find them listed here:

http://www.mongodb.org/display/DOCS/Admin+UIs

There is a comparison of several of the UIs (and more in the comments on this blog post also):

http://timgourley.com/2010/03/16/tuesday-night-tech-mongodb-ui-edition.html

Finally, you should also consider MongoHQ and MongoLab as potential solutions, I know that HQ at least will let you add your own databases, and they both provide excellent admin interfaces for MongoDB.

I think I should clarify Adam's answer.

Mongovue support the exact feature I want. It's called Learnshell. I'll update my answer to include other things I tried.


How to use OR operator in CASE THEN statement I have a situation like this

select col1, col2
from table1
where col1 = case when (booleanexp1) then a OR col1 = b
                  when (booleanexp1) then c OR col1 = d end

I mean I need to get where col1 = a or col1 = b when booleanexp1 is TRUE,
but I get a syntax error? How is the right way to perform something like this?

Assuming two booleanexp1s is a misprint:

select col1, col2
from table1
where ((booleanexp1) AND (col1 IN (a, b)))
   or ((booleanexp2) AND (col1 IN (c, d)))

PS. not (booleanexp1) is one possible option of (booleanexp2)...

select col1, col2
 from table1
where col1 = case
               when (booleanexp1)  OR col1 = b  then a
               when (booleanexp1)  OR col1 = d  then c end

This part is also a boolean expression


Inserting empty string on Windows MySQL doesn't use default value Why inserting '' on a column with default integer value on MYSQL/WINDOWS produces
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

The query is:

insert into test2 (id,test) values('','');

If I use this query on MYSQL/LINUX, it runs just fine:

mysql>  insert into test2 (id,test) values('','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql>

id is autoincrement
test column is INT and has default value 0

Thanks.

SOLUTION:
on my windows machine i disable "strict" mode
in my.ini file, i removed STRICT_TRANS_TABLES from sql-mode=

PROBLEM

You need to insert nothing ?

SOLUTION

Then, insert nothing at all ... literally !!!

One of the following will work

INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);

SAMPLE DATA

use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2
    -> (id int not null auto_increment primary key,
    -> test int default 0);
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql>

QUERIES EXECUTED

If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT #1 : I did this in Windows 8.1 from the command line

mysql> select * from information_schema.global_variables
    -> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
| VERSION                 | 5.6.15                       |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)

mysql>

CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.

loading empty values by a date file will insert the data with empty strings instead of NULL.
I solved my case loading the data into tekst fields and subsequently ran an update:
update table <> set col1=NULL where col1='';


Database Engine Tuning Advisor - The minimum storage space required exceeds the default I created a test database with one table

CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NOT NULL
) ON [PRIMARY]

I have a sql script that consist of one line.

INSERT INTO TestTable ([TimeStamp]) VALUES(CURRENT_TIMESTAMP);

When I run the script in SSMS it works. But when I use it as a workload in DTA the the Analysis is Aborted with this error

  The minimum storage space required for the selected physical design
  structures exceeds the default storage space selected by Database
  Engine Tuning Advisor. Either keep fewer physical design structures,
  or increase the default storage space to be larger than at least 3
  MB.Use one of the following methods to increase storage space: (1) If
  you are using the graphical user interface, enter the required value
  for Define max. space for recommendations (MB) in the Advanced Options
  of the Tuning Options tabbed page; (2) If you are using dta.exe,
  specify the maximum space value for the -B argument; (3) If you are
  using an XML input file, specify the maximum space value for the
   element under

Using Windows 10 (latest update), SQL Server 2016 SP1.

Why do I get this error?

You have to go to Tuning Options and then Click Advanced Options. On the popup, define the max. space for recommendations.

https://www.sqlservercentral.com/Forums/Topic1383773-391-1.aspx

The fix for this issue was first released in Cumulative Update 10. For more information about how to obtain this cumulative update package for SQL Server 2016, click the following article number to view the article in the Microsoft Knowledge Base:

2783135 Cumulative update package 10 for SQL Server 2016


Restore Backup Fails - File 'Db' cannot be restored to '{...}.mdf'. Use WITH MOVE to identify a valid location for the file I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer.

RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak'

Any thoughts on what I can do to correct this?  It's looking for the mdf, so can I assume that the backup file I have is an incremental backup and not the full backup and that's why it's looking for the mdf?

I am not sure if 1) they have given all the data (the entire db) or 2) I have the right data - and I can do the restore with the right T-SQL commands - I just don't know how.

Help is appreciated.

Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 2
File 'Db' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 2
File 'DbDev_log' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 2
The operating system returned the error '21(failed to retrieve text for this error. Reason: 15105)' while attempting 'GetVolumeInformation' on 'D:\'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

RESTORE FileListOnly FROM DISK = N'C:\Backup\Backup.bak' shows me the following...

LogicalName PhysicalName    Type    FileGroupName   Size    MaxSize FileId  CreateLSN   DropLSN UniqueId    ReadOnlyLSN ReadWriteLSN    BackupSizeInBytes   SourceBlockSize FileGroupId LogGroupGUID    DifferentialBaseLSN DifferentialBaseGUID    IsReadOnly  IsPresent   TDEThumbprint
Db  D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf  D   PRIMARY 29622272    35184372080640  1   0   0   02925462-83CC-4222-8966-53229FA25B1C    0   0   29032448    512 1   NULL    269000000146900238  735C7F0E-F63D-4AA1-AC48-505A084AC00B    0   1   NULL
Db_log  D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF  L   NULL    40239104    2199023255552   2   0   0   71038B04-CBC9-4E4A-93AD-4E1268859CB2    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL
DbDev_filestream    D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db  S   PRIMARY_FILESTREAM  4691012 0   65537   18000000011800001   0   627C9AAA-97BD-4991-9C8C-90C400226A9F    0   0   4784128 512 2   NULL    269000000146900238  735C7F0E-F63D-4AA1-AC48-505A084AC00B    0   1   NULL

Take another look at the output from your first command. There is an error messag in it that points you to the fix.

Look for this section:

Use WITH MOVE to identify a valid location for the file.

Basically, the file path on your machine doesn't match the original machine. The MOVE option will let you fix that.

Here is an example of the RESTORE command using the MOVE option:

USE [master]
RESTORE DATABASE [AdventureWorks2008R2]
FROM  DISK = N'C:\SQL Backups\AdventureWorks2008R2.bak'
WITH  FILE = 1,
MOVE N'AdventureWorks2008R2_Data' TO N'C:\MyNewDataLocation\AdventureWorks2008R2_Data.mdf',
MOVE N'AdventureWorks2008R2_Log' TO N'C:\MyNewLogLocation\AdventureWorks2008R2_Log.ldf',
MOVE N'FileStreamDocuments2008R2' TO N'C:\MyNewFileStreamLocation\Documents2008R2',
NOUNLOAD,  REPLACE,  STATS = 1
GO

Hope this helps!

it can also be a problem with access rights. make sure the user is in the sysadmin role.


After upgrade to SQL Server 2019 some stored procedures fail We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150).
We have two different stored procedures that started failing after the upgrade, with error messages like this:
Msg 8632, Level 17, State 2, Procedure BuildSelfSaleStats, Line 14 [Batch Start Line 4]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Whats really strange is that this particular stored procedure doesnt take any arguments, and when we simply execute the body of the SQL code in SSMS, it works fine(!?).
What might cause some SQL code that works fine when executed in SSMS, to suddenly start failing when its wrapped in a stored procedure?
We managed to isolate the error to a date formatting function we had:
This works when run in SSMS:
select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
output:
(No column name)
2019-12

But when called in testproc, it fails:
CREATE PROCEDURE testproc
AS
BEGIN
    SET NOCOUNT ON;
    select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
END

exec testproc

Msg 8632, Level 17, State 2, Procedure testproc, Line 9 [Batch Start Line 11]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

This looks to be a problem with UDF inlining. As @ScottHodgin suggested, the first step is to install the latest CU to see if the problem persists. If that doesn't resolve the problem, your options are:

Turn off TSQL_SCALAR_UDF_INLINING for the database:
 USE YourDatabase;
 ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Add INLINE OFF to the problem function header. This will allow the benefits of scalar inlining for other functions.

Refactor the problem query as to avoid the error.

I just called sp_recompile for all functions and it worked ok.
I faced this same problem when i moved from SQL Server 2014 to 2019. After altering the database option "Compatibility Level" to 150 (SQL 2019) the errors started to come up.


SELECT MIN(id) id,
       ParentId,
       ProductId,
       SUM(Quantity) Quantity
FROM [source_table_name]
GROUP BY ParentId,
         ProductId

I'm sleeping I guess.
SELECT ParentId, ProductId, SUM(Quantity) T FROM [dbo].[InternalOrdersDetail]
where ParentId=19297
group by ParentId, ProductId

SUM(Quantity) in select does the job. Facepalm.


timestamp change on Oracle dbf files I have an oracle database with no asm.  If I shut down the database, move some of the dbf files to a location where I cannot preserve the timestamp on the files, then move them back... Will the database start up without any problem?  I will not change anything in the contents of the dbf files nor the permissions.. only the OS timestamp will change.  is this ok?  Thanks!

Oracle does not care about the OS timestamp on data files.  When it opens files it looks at the scn in all of the file headers and compares them to what is recorded in the control file.  If all scn match, the database opens.  If there is a mismatch between the scn of a file and that in the control file, it will throw an appropriate error.  But none of this has to do with the OS time stamp on the file.

... if you want to be sure, you could also archive the files to the temporary location instead of moving them,
eg. on Linux, just use

cd /path/to/DBFiles
tar czf /path/to/tempLocation/databasefiles.tar.gz DBfile1 DBFile2 ... DBFileN

After doing your maintenance on their original storage, re-extract them with

cd /path/to/DBFiles
tar xzf /path/to/tempLocation/databasefiles.tar.gz

The tar utility will preserve all metadata of files and directories, including timestamps.
The simple gzip compression (as set with the "z" option to tar) will use less space on tempLocation, too.


How to select column names by data type How would I get the names of all the columns in a table that are of a specific type such as datetime?

Even better -- How can I do the same thing but with multiple tables joined together and then list the column name along with the table it comes from?

Query sys.columns and sys.types like this:

select object_name(c.object_id) TableName, *
FROM sys.columns c
join sys.types t ON c.system_type_id = t.system_type_id
where t.name = 'datetime'

You can do this way:

   SELECT COLUMN_NAME FROM user_tab_columns WHERE
     table_name = 'MYTABLE' AND DATA_TYPE = 'DATE'

   SELECT TABLE_NAME, COLUMN_NAME FROM user_tab_columns
     WHERE  DATA_TYPE = 'DATE'


Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe? My application has a Product and a Purchased table.

The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the purchase table maintains a historical record of the product details.

Purchased.name and Purchased.description fields are taking up too much space. On average, 1 of 30 strings in these fields are unique, and 29 of 30 are duplicates.

I would like to avoid creating a HistoricalProduct table, as many facets of Product change on a regular basis, and that table would grow quite large, too.

Is there a way to get PostgreSQL to manage deduplication of these variable character fields automatically for me?

PostgreSQL has functions for regular expressions.
PostgreSQL string functions
So you can write triggers (function + dml event) to be done what you need. I don't believe it has some optimizer that compress strings across the records. Or maybe I didn't understand what you mean in the question.


Blocked Process Report -two updates causing a long block I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the database and found some potential culprits: 17 seconds worth of blocking.

Below is such log ( there are many similar ):

<blocked-process-report>
 <blocked-process>
  <process id="process308898748" taskpriority="0" logused="0" waitresource="OBJECT: 99:774293818:0 " waittime="17146" ownerId="66317995993" transactionname="UPDATE" lasttranstarted="2015-06-15T12:59:05.817" XDES="0x3bc9cd970" lockMode="IX" schedulerid="7" kpid="11204" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.817" lastbatchcompleted="2015-06-15T12:59:05.817" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG10" hostpid="6832" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995993" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000075c8f5236facd1f18bca0258f9d4babad99091d6"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3111573   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 99:774293818:0 " waittime="17182" spid="278" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-15T12:59:05.780" lastbatchcompleted="2015-06-15T12:59:05.780" clientapp=".Net SqlClient Data Provider" hostname="WORKFLOWG12" hostpid="9944" loginname="WorkflowStateUpdaterSP" isolationlevel="read committed (2)" xactid="66317995589" currentdb="99" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="16" sqlhandle="0x02000000cb1bb914fe051a308bd33cb2b873948749c2a96d"/>
    <frame line="1" sqlhandle="0x0200000001c6452aee248fd8f9f2be0e27c7038cfa4e334f"/>
   </executionStack>
   <inputbuf>
UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = 3807096   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

As you can see it appears that two updates are blocking each other and I'm struggling to see why.

Here are the database specifics that might help you help me:

TABLE: SP_ScheduleEvent
------
|ScheduleEventID| PK| bigint| not null|
...
|DateLastProcessed| | datetime| not null|
...

INDEX:
ScheduleEventID, "use page locks when accessing the index is set to" FALSE ( row locks is on )

Typically I have many processes running on different machines that would trigger this UPDATE, I run the update from a DBDataContext within a .NET application ( Linq to SQL) like so:

string sqlUpdateString = "UPDATE [ScheduDB].[dbo].[SP_ScheduleEvent] set DateLastProcessed = GETDATE() where ScheduleEventID = " + scheduleEvent.ScheduleEventID;
db.ExecuteCommand(sqlUpdateString);

Thanks

The Blocked process report (which you have) can be a little confusing at first. There are a few important sections

Blocking-Process
This is the process(SP, query etc) that is causing the blocking. The process is using a resource that is required by the Blocked-Process

Blocked-Process
This is the process(SP, query etc) that is blocked and is waiting on a a resource that is in use by the Blocking-Process.

waitresource
This details the resource that the process is waiting on. Taking your example of
waitresource="OBJECT: 99:774293818:0 "

The 99 refers to the Database ID of your data and the 774293818 refers to the table. Using the ID 774293818 and systables you should be able to find the table that is causing the blocking.

I would also set up an Extended Events Trace to capture the parameters of the offending blocking process and review the execution plans.

A good starting point is https://support.microsoft.com/en-us/kb/224453

If you have access to the db itself, you can run your application and take a look in the running requests (sys.dm_exec_request). There you'll find what query currently runs. You can also take a look at sys.dm_tran_locks which will give you a good overview of the requested locks on which ressource.

Another idea is to run a profiler using the tsql replay template on the database table and go throw the results. This would help you, I think.

If you need further information. Pls let me know.


Access to tables in SQL Server I would like to know if there is a table where accesses to tables are saved, per user or per system.

I'm using both SQL Server 2005 and 2008.

The closest match to what you're looking for seems to be the SQL Server Audit feature, which was added in SQL Server 2008 as a replacement/successor for SQL Trace.

I recommend following the link to read up on it, since its setup/use is too big a topic to describe here in any meaningful way.

Besides SQL Server Audit, there are some alternatives.

You can open SQL server Profiler to trace, but it will affect system
performance.
Use 3rd party tool (e.g Quest LogReader) to read
transaction log files.