Documentation on usage of brackets() in mysql queries I have an application where i am needed to write queries with multiple conditions and i am using several other keywords such as between, and, or etc

I have this two conditions

select * from countries where date between (x and y) and filter = filter and column choosen like '%col%';

and

select * from countries where date between (x and y) and filter = filter

I haven't actually run the queries but i want the date condition using between to be executed first and i thought the brackets would help.

What is the exact usage of brackets () in mysql and where is that documented?.

Putting parentheses in a BETWEEN leads to strangenesses:

date between (x and y) and filter1 = filter2

is treated as

(date between (x and y) and filter1) = filter2

Why?

BETWEEN expects 2 'arguments'; you have given it (1) (x and y), which returns true or false, which are numerically 1 or 0, and filter1.
The result of that (probably 0) is then compared:  0 = filter, which is likely to be false (0).

Recommendations:

Do not use parens that way in BETWEEN.  These are OK:

(date between x and y)

(date) between (x) and (y)  -- but this is clutter

When mixing AND and OR use parens to make it clear what you want to do.  AND will be done before OR, but too many SQL programmers fail know that, so be explicit:

a AND (b OR c)  -- Without parens, you get  (a AND b) OR c

x = 1 AND ( y = 2 OR z = 3 )

Don't clutter:  Rarely do you need (x) instead lf x.
When in doubt, add parens, even if it clutters.
When using function calls, no space before the (; there are cases where the parser decides whether it is a function call versus something else by using that spacing:  DATE(foo), not DATE (foo).

The documentation spells all of this out, but not necessarily in an obvious way.  Here are clues:

The BETWEEN syntax does not show the parens you asked about.
Any expression can have parens around it.
There is a "precedence" that says what happens when mixing AND and OR -- which is just like * and +.  (You do understand that 1+2*3 is 7, not 9 because * is evaluated before +?)

Brackets have the highest priority.

-- original
select * from countries where date between (x and y) and filter = filter and choosen like '%col%';
-- step 1
select * from countries where date between (x and y) and filter = filter and (choosen like '%col%');
-- step 2
select * from countries where (date between (x and y) and filter) = filter and (choosen like '%col%');
-- step 3
select * from countries where ((date between (x and y) and filter) = filter) and (choosen like '%col%');
-- finally
select * from countries where (((date between (x and y) and filter) = filter) and (choosen like '%col%'));

-- original
select * from countries where date between (x and y) and filter = filter
-- step 1
select * from countries where (date between (x and y) and filter) = filter
-- finally
select * from countries where ((date between (x and y) and filter) = filter)


How to fix this error Idk? how to fix it I am new in Oracle?

I tried to connect RMAN and it showed me this error...

please check your alert.log for your database instance
(your database instance must running).

if you use catalog database, it must be open (the target database does not need to be open).


cannot connect my oracle server to developer I get the following error: Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.  My services are below and my connection window.  I believe the installation of oracle 12c was a success.  Do you guys know what could be wrong, please.

enter image description here

First Logged in SQLPlus and Unlock the user

Run SQLPlus, connect as SYS user (use administrator's password you have entered during installation) . Suppose that here For your test connection unlock HR user and give him a new password:

C:\>sqlplus  sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 24 12:15:51 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user hr identified by new_password;

User altered.

SQL> alter user hr account unlock;

User altered.

For Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer is to create a database connection.Perform the following steps:

Open Oracle SQL Developer.
In the Connections navigator, right-click Connections and select New Connection.
Enter test for the Connection Name (or any other name that identifies your connection), hr for the Username and Password, specify your localhost for the Hostname and enter ORCL10(in your case, as it showing in your attached picture) for the SID. Click Test.
The status of the connection was tested successfully. The connection was not saved however. Click Save to save the connection, and then click Connect.

For further your ref Here , Here & Here


How to select records where NOW is between two dates in fields? I've searched all over for this, and I seem only to find how to get records where a single date is between two "outside" dates.

I want to find out how to select records where the current date is between the value in the startDate field and the value in the endDate field.

What I have so far (PHP):

$now = new DateTime();
$sql = "
    SELECT content, image, imageHeight, imageWidth, link, linkDescription
    FROM news
    WHERE appearanceDate < $now
      AND $now < termDate
    ORDER BY appearanceDate DESC
";

But it doesn't work.

You can use the NOW() function in mysql. Note, this assumes that termDate is the later date:

Select content, image, imageHeight, imageWidth, link, linkDescription
  From news
  Where appearanceDate < NOW()
    And termDate > NOW()
  Order By appearanceDate Desc;

You could use the MySQL NOW() function to get the current timestamp.


Is there a way to get the last NDF file READ/Access date / time? I have found a mysterious NDF file in my DB. Can't tell if it contains valid data or if the file is dead.

I'd like to remove it but I cannot tell if the file is in use by SQL Server. Windows Last Modified Date is 2 years ago, but for SQL files that is not reliable because SQL uses it's own drivers to access its files.

How can I tell what the last access time (either to read or to write) is for particular NDF file in a db?

Thanks!

Another option is to use sysinternals process utilities especially :

make sure you use the filter button to filter out unwanted things :-)

Process Explorer
Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.

or

Process Monitor
Monitor file system, Registry, process, thread and DLL activity in real-time.

Try deleting the file. If you are able to, SQL is not using it. A file that is part of an attached/online database cannot be deleted.


Restore SQL Server database Using VB.NET, I want to restore a SQL Server database from a .BAK file without the "log file".

I tried using:

Restore database dbName
from disk = '" & path & "'"

but it didn't work, exception that refers to

"not finding _log file"

more specifically:

Cannot open backup device 'D:\AlomohasebSQL.bak'.
Operating system error 2(Invalid path).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

From the more complete error message:

  Cannot open backup device 'D:\AlomohasebSQL.bak'. Operating system error 2 (Invalid path). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Do you know the exact SQL restore statement you are performing? No matter what front-end language you are using, it should be executing something like this against the SQL Server:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH (various options here)

In your case, I think the error message indicates that the backup file location ("D:\AlomohasebSQL.bak" in your error above) is incorrect. Obviously, make sure this is a valid drive, path and filename.

IMPORTANT NOTE: This path needs to be visible to the SQL Server, not to wherever you are running this app. (Thanks @ColinMackay for bringing this up). In a related note, check to see that the SQL Server service account has the proper permissions to read this file at that location.

There is a (smaller) possibility that it doesn't know where to place the database files after it restores them. SQL does have a "default" path, but this is sometimes out of date. If necessary you can specify the path to restore the files to:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH MOVE 'primarydatafilename' To 'D:\DB\data.mdf',
     MOVE 'logfilename' To 'D:\DB\log.ldf'

You'd obviously need to know the files that are part of that database. If you don't, just use the SSMS restore wizard. There are ways to find that via the RESTORE command, let me know if you need that.

In SQL, the LOG_TRANSACTION file is an undissolvable part of database - once you have defined that in your backup routine.

To get the restore without the LOG, you must define the backup as SIMPLE-FULL one, like below:

BUT, to get success on this, the database properties must also define that characteristics:


SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica We have an AlwaysOn environment, which includes a replica in our DR site that is setup with async commit and readable secondary = No.

When we were running on SQL Server 2014 SP2 we were able to run DBCC CHECKDB against the databases on our DR replica. But since upgrading to SQL Server 2016 we're unable to and our weekly integrity job is failing with the error.

'The target database is participating in an availability group and is currently not accessible for queries.
Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information,
see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Obviously something has changed in 2016 to prevent this, I just don't know what?

I can say that the error message you are getting is because of a bug in SQL Server 2016. Microsoft knows about it and has fixed it in CU3 for SQL Server 2016 RTM.
Note: The user error is reported but the CHECKDB should continue as is.
See FIX: Error 976 occurs when you run the DBCC CHECKDB command on an unreadable secondary replica of SQL Server 2016 Availability Group
Workaround
You can take snapshot of the databases and can run checkdb on it. In this case it would not fail.Database snapshot is enterprise feature I am sure you are not using basic availability group

Microsoft have released a fix for this in SQL 2016 CU3
https://support.microsoft.com/en-us/kb/3194923


Changing IP address of SQL Server 2008 R2 I would like to know if there are any dangers or relevant precautions before changing the IP address of a SQL Server 2008 R2 box.

We have built a virtual PC with Windows Server 2008 R2 and SQL Server 2008 R2. The purpose of this machine is run a job that restores a backup file (copied by a different server), manipulates it, backs it up again and then copies it out to other servers. We've run it a few times in our test/dev DMZ and are now considering deployment.

The simplest option would be to re-IP it. The server name would remain intact. My problem is a colleague has suggested this is unsafe (to re-ip a SQL Server). My question is

a) Is this true for the OS and SQL I'm using? (Win 2008 R2 & SQL 2008 R2?)

b) Is it true for any other combinations of OS and/or SQL?

c) Is there anything special we should do in preparation?

My research thus far indicates it will be fine but I trust the folks of DBA StackExchange more than those social MSDN people.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9844e0c1-b57e-4e0c-88cf-7cb51cc55e07/change-sql-server-ip-address

http://www.sqlservercentral.com/Forums/Topic1164450-391-1.aspx

Changing the IP Address for a machine running SQL Server is not a problem at all.  You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address.  But that's it.

If no users actually connect to the machine then there's nothing else to do.

If users did connect to the machine they'd need to refresh their DNS cache on their workstations by running "ipconfig /flushdns" or by simply restarting their computers.

Unless you've done something very unusual with the TCP listeners within SQL there's nothing else that needs to be done with SQL Server other than the quick restart.

Good day to all,

In my case I did an IP address change on a production server due to a migration to a better server (more memory, SSD, several cores, etc..). When the change was applied none of the users or myself were able to connect remotely; The way I fixed it was by adding an exception on the firewall for inbound/outbound connections under port 1433 and everything worked fine after that.


Is it a bad practice to always create a transaction? Is it a bad practice to always create a transaction?

For example, it is a good practice to create a transaction for nothing but one simple SELECT?

What is the cost of creating a transaction when it is not really necessary?

Even if you are using an isolation level like READ UNCOMMITTED, is it a bad practice?

Is a bad practice to create a transaction always?

It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly).

But wait there is more to understand first :
Everything in sql server is contained in a transaction.

When the session option IMPLICIT_TRANSACTIONS is OFF and you explicitly specify begin tran and commit/rollback then this is commonly known as an Explicit Transaction. Otherwise you get an autocommit transaction.

When  IMPLICIT_TRANSACTIONS is ON an Implicit transaction is automatically started when executing one of the statement types documented in the books online article (e.g. SELECT / UPDATE / CREATE) and it must be committed or rolled back explicitly. Executing a BEGIN TRAN in this mode would increment @@TRANCOUNT and start another "nested" transaction)

To switch which mode you're in, you'd use

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

select @@OPTIONS & 2

if above returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

how much is the cost of creating a transaction when is not really necessary?

Transactions are needed to take the database from one consistent state into another consistent state. Transactions have no cost as there is no alternative to transactions.
Refer: Using Row Versioning-based Isolation Levels

Even if you are using an isolation level read_uncomitted. Is a bad practice? because it shouldn't have problems with locking.

READ_UNCOMMITED isolation level will allow dirty reads by definition i.e. One transaction will be able to see uncommitted changes made by other transaction. What this isolation level does is, it relaxes the over head of locking - method of acquiring locks to protect Database concurrency.

You can use this on a connection/query level, so that it does not affect other queries.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Found an interesting article by Jeff Atwood describing Deadlocks due to Dining Philosophers Puzzle and describing  read committed snapshot isolation level.

EDIT:

Out of curiosity, I did some test measuring the impact on T-log with Perfmon counters like Log Bytes Flushed/Sec, Log Flush Waits/Sec (No. of commits per sec that are waiting on LOG flush to occur) as below graph :

sample code :

create table testTran (id int, Name varchar(8))
go

-- 19 sec
-- Autocommit transaction
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
---------------------------------------------------
-- 2 sec
-- Implicit transaction
SET IMPLICIT_TRANSACTIONS ON
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF

----------------------------------------------------
-- 2 sec
-- Explicit transaction
declare @i int
set @i = 0
BEGIN TRAN
WHILE @i < 100000
Begin
INSERT INTO testTran values (1,'Kin Shah')
set @i = @i+1
End
COMMIT TRAN

Autocommit Transactions: (Edited as highlighted by @TravisGan)

Insert took 19 secs.
Every Autocommit will Flush the T-log buffer to the disk due to autocomit (after @TravisGan highlighted, and I missed that to mention) .
The CHECKPOINT process will be completing fast as the amount of dirty log buffer required to be flushed will be less as it runs quiet often.

IMPLICIT & Explicit Transaction:

Insert took 2 secs.
For EXPLICIT transaction, the log buffers will be flushed only when they are full.
Contrary to Autocommit transaction, in EXPLICIT transaction, the CHECKPOINT process will take longer duration as it will have more log buffers to flush (remember that log buffers are flushed only when they are full).

There is a DMV sys.dm_tran_database_transactions that will return information about Transactions at database level.

Obviously, this is more sort of a simplistic test to show the impact. Other factors like disk subsystem, database auto growth settings, initial size of the database, other processes running on the same server\database, etc will have influence as well.

From the above tests, there is near to no difference between Implicit & Explicit transactions.

Thanks to @TravisGan for helping to add more to the answer.

The issue is whether a group of operations must be treated as a single action.  In other words all of the operations must be completed and committed successfully or none of the operations can be committed.
If you have a scenario that requires you to read preliminary data and then perform updates based on that data then the initial read should probably be part of the transaction.
Note: I am avoiding Select/Insert/Update on purpose.  The transaction scope may actually be at the application level and involve multiple database(s) operations.
Think of classics patterns such as Airplane Seat Reservation or Bank Balance Query/Withdrawal.
One must take a wider view of the problem to ensure the whole application yields reliable, consistent data.


What does horizontal scaling mean? In database context, I have come across horizontal scalability as one of the advantages of the NOSQL databases. What does the term mean?

CouchDB on Wikipedia
NoSQL on Wikipedia

How would it compare to vertical scaling?

Horizontal Scaling
Horizontal Scaling is essentially building out instead of up. You don't go and buy a bigger beefier server and move all of your load onto it, instead you buy 1+ additional servers and distribute your load across them.

Horizontal scaling is used when you have the ability to run multiple instances on servers simultaneously.  Typically it is much harder to go from 1 server to 2 servers then it is to go from 2 to 5, 10, 50, etc.

Once you've addressed the issues of running parallel instances, you can take great advantage of environments like Amazon EC2, Rackspace's Cloud Service, GoGrid, etc as you can bring instances up and down based on demand, reducing the need to pay for server power you aren't using just to cover those peak loads.

Relational Databases are one of the more difficult items to run full read/write in parallel.

I saw Damien Katz speaking about CouchDB at StackOverflow DevDays in Austin and one of his primary focuses for its creation was these parallel instances.  As this has been a focus of it since day one, it would be much more capable of taking advantage of horizontal scaling.

Vertical Scaling
Vertical Scaling is the opposite, build up instead of out. Go and buy the strongest piece of hardware you can afford and put your application, database, etc on it.

Real World
Of course, both of these have their advantages and disadvantages. Often times a combination of these two are used for a final solution.

You may have your primary database where everyone writes to and reads real time data on a large piece of hardware. Then have distributed read only copies of the database for heavier data analysis and reporting where being up to the minute doesn't matter as much.  Then the front end web application may be running on multiple web servers behind a load balancer.

Make it simpler to understand. NO SQL DB means no JOIN which means your number of columns stays the same that means all you care about scaling is the number of data i.e. the number of rows which means horizontal growth or scaling.


How to copy a table during replication in MySQL? I have two MySQL servers; db1 and db2. db1 is production and db2 is for reporting. I have setup replication where db1 is master and db2 is slave. This current setup works fine.

I was given a new requirement to not replicate a field from the USER table because it contains sensitive information.

Here are the steps I implemented:

I created a copy of USER, called USER_CLONE that does not have the field to be omitted
I set up a stored proc (UPDATE_USER_CLONE) to DELETE FROM USER_CLONE and INSERT INTO USER_CLONE SELECT  FROM USER
I added the following to my.cnf on db2:
  replicate-ignore-table = USER
  replicate-ignore-table = UPDATE_USER_CLONE

When replication starts, I get an error on the INSERT statement because USER does not exist on db2. I understand the error since replication will rerun all the db statements.

What is the right way to set this up so USER is ignored or has a field ignored, or USER_CLONE can be setup to replicate?

Thanks for any advice.

replicate-ignore-table and the related configuration options are problematic at best.  Not because they don't do what they're supposed to do -- because they do -- but rather because they have side effects that require a deeper understanding of MySQL replication.
The problem you have stems from the way the events are replicating.  MySQL can replicate table data changes using two different mechanisms, statement-based and row-based, based on the setting of the global configuration variable binlog_format.
STATEMENT -- the actual queries are replicated from server to server, with each server executing the query and modifying its data set.
ROW -- the actual queries are not sent, but instead, tightly-packed row images of the changes actually caused by each query are sent.
MIXED -- the server chooses on a query-by-query basis how to replicate the events.
Importantly, when a query is replicated as a statement event, triggers on the affected tables fire on the slave in order to make the same changes to other tables that would have been made on the master.  When a query replicates as a row event, triggers do not fire on the slave.  Instead, the master server sends over additional row images from the affected tables so that the additional impacted tables also get updated to match the way they were on the master.  The same thing is true when procedures are called -- either the individual statements in the procedure are replicated and have to be valid and execute on the slave (statement-based replication), or the rows changed by the procedure will be replicated (row-based replication).
The scenario you described cannot work unless your master server's binlog_format is set to ROW, because the queries running on the slave have to have access to the "user" table data.
Once that is set, if it were me, I would remove the replicate-ignore-table from the configuration, and instead provision the user table on the slave with the BLACKHOLE storage engine, which discards data sent to it, returning "success" on inserts and 0 rows found/affected on updates and deletes.
However, keep reading, because you are doing this the hard way.
The solution is much simpler: alter the table to move the confidential data to the far right side of the table, and then drop those columns from the slave's copy of the table.  No, really:

Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table.
You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:
Columns common to both versions of the table must be defined in the same order on the master and the slave. (This is true even if both tables have the same number of columns.)
Columns common to both versions of the table must be defined before any additional columns.
— http://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

I have linked to the documentation from MySQL 5.6 since that's the current GA version, but this feature is available as far back as 5.1.21.  When doing things this way, you would still be advised to switch your master to binlog_format = ROW.

You can't ignore particular field of a table, however you can ignore a particular table or database to get replicated.
You can refer these documentation links:

replicate_ignore_table

replicate_ignore_database

In your case you can ignore USER table to get replicated and allow USER_CLONE to get replicated as it has sensitive field ignored.


Is it possible to use a column value as the argument to a hypothetical-set aggregate function? Say I have a table like the one described below.

                                     Table "public.foo"
  Column   |            Type             |                    Modifiers
-----------+-----------------------------+-------------------------------------------------
 id        | integer                     | not null default nextval('foo_id_seq'::regclass)
 data      | text                        | not null
 score     | integer                     |

To calculate the percentile rank for a particular score, I can do something like this:

test=# select percent_rank(5) within group (order by score) as rank from foo;
        rank
--------------------
 0.0762917366163029
(1 row)

What I'd really like to do is have a select statement that shows the data, score, and rank for each row present. I tried the below query.

test=# select data, score, percent_rank(score) within group (order by score) as rank from foo group by data, score;

This produces a rank of 0 for every row. I'm assuming this is because the within group clause ends up being limited by group by clause, but I'm not enough of a SQL ninja to know for sure.

At any rate, is it possible to include the percentile rank of a row's score as part of a multi-row select?

If you include ID I would assume that you can join it back to itself on ID to get that information in one dataset.


Cant see SQL Server Agent in SSMS I have SSMS 2012 installed on my laptop, and SQL Server 2012 on my hosting, but when i try to schedule a sql job i didn't find "SQL Server Agent" in the left side list!!?

If you have Management Studio Express installed (any version prior to SQL Server 2012 SP1), it simply doesn't contain any facility for SQL Server Agent (no matter which edition you're trying to manage). You can fix this by getting the very latest version from here:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

You want the file SQLManagementStudio_x64_ENU.exe (or the one with x86 in the name if you have a really old 32-bit machine). This is the first version of Management Studio that has been offered fully functional and license-free.

Once you install this version of Management Studio, you will have access to SQL Server Agent when managing any instances of the SQL Server engine that support SQL Server Agent. Of course that won't help you create jobs on instances of SQL Server Express, since it doesn't have Agent at all.

As steoleary said, SQL Server 2012 Express doesn't support SQL Server Agents

Only the following SQL Server 2012 editions support SQL Server Agents: Enterprise, Business Intelligence, Standard and Web

See all the features by edition on MSDN: Features Supported by the Editions of SQL Server 2012


sp_send_dbmail with multiple reply_to addresses I got this error when I specified multiple email addresses in reply_to

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2014-08-19T12:54:35). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
  )

here is the sql snippets

EXEC msdb..sp_send_dbmail
@profile_name = 'Company',
@recipients = 'hi@sg.com',
@subject = 'PO delivery dates confirmation',
@body = 'test',
@body_format = 'HTML',
@importance = 'Normal',
@reply_to = '2@sg.com;3@sg.com'

I can't use distribution group because the reply_to is dynamic base on the query. what would be the work around for this?

@reply_to can only accept 1 email address. Its by design - see this connect.

The document reflects that as well.

  [ @reply_to= ] 'reply_to'
  Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP secuirty settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

If you have to dynamically change the reply_to it seems like your going to have to wrap this sp_send_dbmail within a stored procedure and assign the single reply_to (based on some query) to a variable and use that vs. hardcoding the entries.


Database architecture for big data store? My team has developed service software with a PHP framework, so we are flexible to change database engine: MySQL, PostgreSQL etc. We have already more than 30 customers who wait to start using this service. My uncertainties are following:

Which DB engine is the best for use in such situations?
Is there also MySQL ok. or better to use PostgreSQL?
We use AWS so we plan to have own machine for DB hosting, is this the right decision?

More information:

Database will grow up to 10 GB in year.
Lots of selects but also inserts
Storing many texts and numbers. Files and images are stored on other storage.
Need 24/7 availability

The specified data is quite common on any LAPP or LAMP scenario. I think both databases would handle this kind of loads easily. If you really need high availability, and you are on AWS; I would use RDS, with either PostgreSQL or MySQL, depending on which database you feel more comfortable with. All the necessary steps to configure and maintain the databases are taken care of for you.

If you want to prepare for High Availability, the streaming replication options of both MySQL and PostgreSQL are good alternatives. The intermediate software, that should monitor the master and slave(s) databases and decide when the master is 'dead' and a slave needs to be promoted is the most difficult part (and many times, becomes your SPOF).

Every DB has its pros and its cons. I use mostly PostgreSQL, but also MySQL for some applications that require it. If I have to make a choice, 90% of the times I'll go for PostgreSQL. However, there is not a "good one" or a "bad one". There's normally one that fits best your needs. Sometimes your SQL needs are not that complicated, but you want to use a DB that is known by your team, and maybe your team knows better MySQL. Sharding is easier (better integrated) using MySQL, for instance.

The volume of the data by itself is meaningless without the answers to these questions -

How much data are you going to process on a regular basis?
How many concurrent requests are you going to serve?

From SQL perspective, It is PostgreSQL heaven Vs. MySQHell.

If your queries differ from select * from t where x = ?, I would seriously consider if MySQL is capable of answering your needs.

Here are some features that exists in PosgreSQL but not in MySQL which make a huge difference:

Analytic functions
Table functions
Regex functions (The real deal and not just RLIKE)
Arrays
Hash Join
Hash Group


insufficient privileges while executing oracle stored procedure? I'm getting an insufficient privileges error while executing the following oracle stored procedure. I'm using Oracle Database 10g Express Edition.

CREATE OR REPLACE
PROCEDURE sp_update_acounts(
    accounts_file_dir  IN VARCHAR2,
    accounts_file_name IN VARCHAR2)
IS
BEGIN

EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_accounts_dir AS '''||accounts_file_dir||'''';

EXECUTE IMMEDIATE 'grant read, write on directory ext_accounts_dir to myuser';

EXECUTE IMMEDIATE 'drop table crm_account_stage';

EXECUTE IMMEDIATE 'CREATE TABLE crm_account_stage (entity_account_id NUMBER(19,0), crm_id VARCHAR2(255 CHAR)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY
ext_accounts_dir ACCESS PARAMETERS (FIELDS TERMINATED BY '','' ( entity_account_id CHAR(225), crm_id CHAR(225))) LOCATION ('''||accounts_file_name||''''||') )';

MERGE INTO ua_crm_accounts acc
USING (
  SELECT entity_account_id,
         crm_id
  FROM crm_account_stage) acc_stage
ON (acc_stage.entity_account_id = acc.pkey)
WHEN MATCHED THEN
  UPDATE SET acc.crm_id = acc_stage.crm_id;

END;

I'm using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure successfully. I have all the rights for the oracle user because I'm the admin. I have given all possible rights.

But when I execute the SP I'm getting error like:

Error starting at line 13 in command:
execute sp_update_acounts('C:\Users\surenr\Desktop\UA\Intitial-Conversion','acc_data.csv')
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "myuser.SP_UPDATE_ACOUNTS", line 7
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

Update:
I'm not trying to update user or password. But the error message says as i'm trying to modify user details. When I try the same code step by step outside the stored procedure, it's executing without any problem.

What could be the reason for this? How can I resolve the issue?

The pl/sql execution context does not include Role. So your risght have been delegated by a role. The Pl/SQL doesn't see it.
So, first, you can try to give the right to alter the user directly not via a role.
2nd, i would try this option on the package definition (auhtid) which is explained here

You need to add the follow line: AUTHID CURRENT_USER.

CREATE OR REPLACE
PROCEDURE sp_update_acounts(
    accounts_file_dir  IN VARCHAR2,
    accounts_file_name IN VARCHAR2)
AUTHID CURRENT_USER
IS
BEGIN

EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_accounts_dir AS '''||accounts_file_dir||'''';

EXECUTE IMMEDIATE 'grant read, write on directory ext_accounts_dir to myuser';

EXECUTE IMMEDIATE 'drop table crm_account_stage';

EXECUTE IMMEDIATE 'CREATE TABLE crm_account_stage (entity_account_id NUMBER(19,0), crm_id VARCHAR2(255 CHAR)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY
ext_accounts_dir ACCESS PARAMETERS (FIELDS TERMINATED BY '','' ( entity_account_id CHAR(225), crm_id CHAR(225))) LOCATION ('''||accounts_file_name||''''||') )';

MERGE INTO ua_crm_accounts acc
USING (
  SELECT entity_account_id,
         crm_id
  FROM crm_account_stage) acc_stage
ON (acc_stage.entity_account_id = acc.pkey)
WHEN MATCHED THEN
  UPDATE SET acc.crm_id = acc_stage.crm_id;

END;


What are Identity Columns? I was reviewing the commit-fest scheduled for 7/01 for PostgreSQL and I saw that Pg is likely going to get "identity columns" sometime soon.

I found some mention in information_schema.columns but nothing much

is_identity         yes_or_no         Applies to a feature not available in PostgreSQL
identity_generation character_data    Applies to a feature not available in PostgreSQL
identity_start      character_data    Applies to a feature not available in PostgreSQL
identity_increment  character_data    Applies to a feature not available in PostgreSQL
identity_maximum    character_data    Applies to a feature not available in PostgreSQL
identity_minimum    character_data    Applies to a feature not available in PostgreSQL
identity_cycle      yes_or_no         Applies to a feature not available in PostgreSQL

The Wikipedia Page doesn't say much either

  An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.

But, I don't see anything else on them. How do identity columns work? Do they provide any new functionality or is this just a standard method to create sequences? Any breakdown of the new feature and how it works?

This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21):

  4.15.11 Identity columns

  The columns of a base table BT can optionally include not more than one identity column. The declared type
  of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct
  type whose source type is an exact numeric type with scale 0 (zero). An identity column has a start value, an
  increment, a maximum value, a minimum value, and a cycle option.
  ...
  ...  The definition of an identity column
  may specify GENERATED ALWAYS or GENERATED BY DEFAULT.

It is a property of a column which basically says that the values for the column will be provided by the DBMS and not by the user and in some specific manner and restrictions (increasing, decreasing, having max/min values, cycling if the max/min value is reached).

Sequence generators (usually called just "sequences") are a related SQL standard feature: it's a mechanism that provides such values - and can be used for identity columns.

Note the subtle difference: a SEQUENCE is an object that can be used to provide values for one or more identity columns or even at will.

The various DBMS have so far implemented similar features in different ways and syntax (MySQL: AUTO_INCREMENT, SQL Server: IDENTITY (seed, increment), PostgreSQL: serial using SEQUENCE, Oracle: using triggers, etc) and only recently added sequence generators (SQL Server in version 2012 and Oracle in 12c).

Up to now Postgres has implemented sequence generators (which can be used to provide values for column, either with the special macros serial and bigserial or with nextval() function) but has not yet implemented the syntax for identity columns, as it is in the standard.

Defining identity columns (and the slight difference from serial columns) and various syntax (eg. GENERATED ALWAYS, NEXT VALUE FOR, etc) from the SQL standard is what this feature is about. Some changes / improvements may need to be done on the implementation of sequences as well, as identity columns will be using sequences.

If you follow the link identitity columns (from the page you saw), you'll find:

  identity columns

  From:     Peter Eisentraut
  To:   pgsql-hackers
  Subject:  identity columns
  Date:     2016-08-31 04:00:42
  Message-ID:  6adbacbf-73bc-dd1a-2033-63409180fd18@2ndquadrant.com

  Here is another attempt to implement identity columns.  This is a
  standard-conforming variant of PostgreSQL's serial columns.  It also
  fixes a few usability issues that serial columns have:

  need to set permissions on sequence in addition to table (*)
  CREATE TABLE / LIKE copies default but refers to same sequence
  cannot add/drop serialness with ALTER TABLE
  dropping default does not drop sequence
  slight weirdnesses because serial is some kind of special macro

  (*) Not actually implemented yet, because I wanted to make use of the
  NEXT VALUE FOR stuff I had previously posted, but I have more work to
  do there.

  ...

Update 2017, September: seems like the feature will be in Postgres 10, which is to be released in a few days/weeks:  What's New In Postgres 10: Identity Columns

Oracle have also implemented identity columns and sequences, in version 12c. The syntax is according to the standard, as far as I checked:
Identity Columns in Oracle Database 12c Release 1 (12.1)

  The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

SQL 2003 Summary

For a good review of the spec on this here is a column entitled, SQL:2003 Has Been Published from SIGMOD

  Identity Columns
  While sequence generators put SQL in charge of
  generating unique values, users are still burdened
  with tasks such as creating a sequence generator and
  invoking the NEXT VALUE FOR function at
  appropriate times. SQL:2003 provides another new
  feature, identity columns, that provides a more
  convenient mechanism by making it unnecessary for
  users to perform these additional tasks.
  Identity columns are columns designated with the
  special keyword IDENTITY, as shown below:

CREATE TABLE PARTS (
PARTNUM INTEGER GENERATED ALWAYS
AS IDENTITY (START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
NO CYCLE),
DESCRIPTION VARCHAR (100),
QUANTITY INTEGER )

  As can be seen from the above example, identity
  columns share the same attributes as sequence
  generators. This is because a sequence generator that
  inherits the identity column attributes gets associated
  conceptually with each identity column. Note that at
  most one column in a table can be designated as an
  identity column.
  Users do not need to specify a value for an
  identity column whenever a new row is inserted into
  a table containing that identity column. The value for
  such a column is generated automatically by invoking
  the NEXT VALUE FOR function implicitly under the
  covers. For example, the following INSERT
  statement:

INSERT INTO PARTS
(DESCRIPTION, QUANTITY)
VALUES ('WIDGET', 30)

  adds a new part named WIDGET to the PARTS
  table. The value for the PARTNUM column is
  generated automatically, following the same rules
  that are used for generating values of sequence
  generators. That is, the value of PARTNUM column
  for the first row would be the START WITH value
  specified for that column, and the values for
  subsequent rows would follow the formula we
  described previously for sequence generators.
  What we said above is true if the user had
  specified GENERATED ALWAYS for the identity
  column, which is the case for PARTNUM column in
  our example. SQL:2003 provides another option,
  GENERATED BY DEFAULT. If the user chooses
  this option, automatic generation takes place only
  when values are not provided in the VALUES clause.
  This feature is very useful for making copies of tables
  with identity columns.


Is the table InnoDB? For that the index statistics are gathered by "index dives" and are by definition imprecise.

EXPLAIN is not actually executing the query, but uses the estimates to plan it so it shows those imprecise numbers.

The important thing is that if it keeps numbers in the same order as reality (which, being a double in your case, it is), it works well enough for most cases.

Keeping exact numbers is too cumbersome and not even really possible in transactional environment.

The explain plan is simply showing you how many rows it had to sort through, to get your result.

If you were to remove your where clause and do a "select count (*) from [table_name]", then it would return a count of all rows in the table, and your explain plan would match the total.


How to fix postgres stuck in recovery mode? I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space available. The problem now, is that postgres seems not to start correctly. It's in this recovery mode, but it can't get out of it.

valter@web:~$ ps -ef | grep post
postgres 14580     1  0 Jan03 ?        00:01:16 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres 14581 14580  0 Jan03 ?        00:00:00 postgres: startup process   recovering 000000010000000A0000000D

Any idea what is going on here ?

valter@web:~$ sudo ls -ltha /var/lib/postgresql/9.3/main/pg_clog/
total 3,9M

drwx------ 15 postgres postgres 4,0K Jan  3 12:41 ..
drwx------  2 postgres postgres 4,0K Jan  3 12:34 .
-rw-------  1 postgres postgres  48K Jan  3 12:41 000E
-rw-------  1 postgres postgres 256K Jan  3 12:30 000D
-rw-------  1 postgres postgres 256K Nov 15 09:39 000C
-rw-------  1 postgres postgres 256K Okt 13 23:07 000B
-rw-------  1 postgres postgres 256K Sep 11 23:11 000A
-rw-------  1 postgres postgres 256K Aug  8 10:45 0009
-rw-------  1 postgres postgres 256K Jul  3  2015 0008
-rw-------  1 postgres postgres 256K Mai 26  2015 0007
-rw-------  1 postgres postgres 256K Apr 17  2015 0006
-rw-------  1 postgres postgres 256K Mär  9  2015 0005
-rw-------  1 postgres postgres 256K Jan 30  2015 0004
-rw-------  1 postgres postgres 256K Dez 19  2014 0003
-rw-------  1 postgres postgres 256K Nov  9  2014 0002
-rw-------  1 postgres postgres 256K Sep 29  2014 0001
-rw-------  1 postgres postgres 256K Aug 22  2014 0000

EDIT 1:

valter@web:~/9.3/main/pg_xlog$ ls -ltha
total 97M
drwx------ 15 postgres postgres 4.0K Jan  3 12:41 ..
-rw-------  1 postgres postgres  16M Jan  1 12:16 000000010000000A0000000D
-rw-------  1 postgres postgres  16M Dec 23 00:00 000000010000000A0000000C
drwx------  3 postgres postgres 4.0K Dec 21 23:45 .
-rw-------  1 postgres postgres  16M Dec 21 23:36 000000010000000A00000011
-rw-------  1 postgres postgres  16M Dec 21 19:00 000000010000000A00000010
-rw-------  1 postgres postgres  16M Dec 21 14:25 000000010000000A0000000F
-rw-------  1 postgres postgres  16M Dec 21 09:55 000000010000000A0000000E
drwx------  2 postgres postgres 4.0K Jul 10  2014 archive_status

Server log file:

    valter@web:~/9.3/main/pg_xlog$ tail -f /var/log/postgresql/postgresql-9.3-main.log
    ...
    2016-01-05 17:23:08 CET FATAL:  the database system is starting up
    2016-01-05 17:23:09 CET FATAL:  the database system is starting up
    2016-01-05 17:23:10 CET FATAL:  the database system is starting up
    2016-01-05 17:23:11 CET FATAL:  the database system is starting up
    ...

If you can re import your data, you can uninstall postgresql and reinstall it. And then re import your data. I had a similar issue and this worked for me.

for ubuntu : sudo apt-get --purge remove postgresql-YOUR VERSION


upgrading sql server 2000 database to sql server 2012 Seeking some advice about an oudated database system we're working with.... hope this is an appropriate place to post this question.

I work with a city government arts program.  In 2004, we contracted with a local database developer to develop a system that allows us to track student participation in our after school art classes.  It tracks each student's address, demographics, what classes they enroll in, daily attendance, paperwork/documentation, class progress,  etc.   It was built on SQL Server 2000.

Because of issues with the city's technology systems back then we were not able to install it right away.  When they finally got around to supporting us (I think around 2006 or 07), they gave us SQL Server 2005.  We ended up installing an instance of SQL Server 2000 on SQL Server 2005 which itself runs on Windows Server 2003.

As of a few years ago, we are no longer under the city's technology systems and manage our own systems through independent contracted consultants.  Last year, we terminated our contract with the original database developer because he wasn't following through on a number of issues we needed addressed over the years.  Last year, we also went through a major overhaul of all our servers and desktops.

We've upgraded all  our other servers but we still have one server left that's still running SQL server 2000 and Windows 2003 just  because of this one outdated db system.  We'd like to upgrade both SQL Server to 2012 and Windows Server  to 2008R2 on this last server.

I'm trying to budget for how many hours it would take to upgrade a SQL Server 2000 database so it will run on SQL Server 2012. I realize you can't really tell much without actually seeing what we're working with, but all I'm looking for now is a ballpark figure (i.e., is it in the 20-30 hour range? 50 hours? 100 hours?).

If there is any other info I can provide to help get at an estimate feel free to post a comment....

Thanks for any help!

You can easily restore your 2000 DB to any higher version of SQL Server. The issue you may face is feature deprecation. If you aren't using any deprecated features, the actual DB migration is a non-event for the most part.

You can also use Profiler to trace for deprecated calls.

I don't think there are much issues. Try restoring your old database to SQL Server 2012 using Management Studio. You don't need an extra budget allocation for this.


How do I ensure I always connect to Primary/Secondary node in a MongoDB ReplicaSet? I have a 3 node replica set running on 3 different servers. I have finished setting it up and everything works great. Now I want to give my users two connection strings:

Connect to this "xyz" for write
Connect to this "abc" for read only traffic

I am also trying to set up a CNAME for each of the connections, to be able to have the flexibility to change in the future.

Thanks and appreciate your time in advance.

Ref: https://docs.mongodb.com/manual/reference/connection-string/

If you are using replica set you do not use your mongod hostname in your connecton string. Rather use all mongos hostname and replica set name.

As an example:

  To describe a connection to a replica set named test, with the
  following mongod hosts:

  db1.example.net on port 27017 and db2.example.net on port 2500. You
  would use a connection string that resembles the following:

  mongodb://db1.example.net,db2.example.net:2500/?replicaSet=test

Writes will always go to primary and you do not have to do anything. Config and mongos will communicate among themselves will redirect to primary.

You can configure Write concern which describes the kind of assurances that the mongod and the driver provide to the application regarding the success and durability of the write operation. For a full explanation of write concern and write operations in general, see Write Concern.

Read Preference Options

Read preferences describe the behavior of read operations with regards to replica sets. These parameters allow you to specify read preferences on a per-connection basis in the connection string. The default value is primary, which sends all read operations to the replica set’s primary.

The read preference values are the following:

primary
primaryPreferred
secondary
secondaryPreferred
nearest

For descriptions of each value, see Read Preference Modes.

You can also use the following:

mongo-local "mongodb:/Host1,Host2,Host3/<database>?replicaSet=<replica_set_name>" --eval '<querry>'


Reliability of Scimore embedded database I currently use SQLite for some applications that need a light embedded database. These apps use multiple processes that access the same database. For concurrent write access this is cumbersome. We have done some work-arounds to overcome this to some degree, but clearly SQLite has it limits.

So, we looked around and found an interesting, but very unknown, database: Scimore. It's quite capable; it's embedded version uses an out-of-process technique that allows multi-concurrency read and write access. Besides this, it's free. It's almost too good to be true.

From our first test results it seems to be very good and fast, but then why is it so unknown?

Therefore my question: Do you know this database; if so what are your experiences regarding reliability?

Scimore website

NB: I'm only asking for real usage experiences; not opinions.

I've worked a few with it, and until now, it works like a charm, no concurrency problems, fast and depending on clients, lite as you want it to be.
I do not know if it would hace issues at electrical shutdowns while saving data
but i'm using it for technical data (Engineering data)

Cheers!


what is the password to user mongodb when mongodb is installed in ubuntu? When I installed mongodb in ubuntu, user mongodb and group mongodb are created in /etc/passwd and /etc/group. But I have no idea of the password to these user and group. I have to run mongod as root user, which is not suggested. Someone suggests to change the owner of mongodb data and log files to mongodb:mongodb. But I have no idea how to run mongod as mongod?
Any idea?

Launch Shell as User:

If all else fails, on Ubuntu 14.04 LTS running on AWS, try this...

me@local $ ssh -i aws.pem ubuntu@foo
ubuntu@foo $ sudo -u mongodb bash   # OR whatever shell (zsh)
mongodb@foo $ mongod --dbpath /data/db --fork

OF COURSE, you'll add a much larger set of flags to the command. Note also that this assumes you're authenticated as ubuntu on an AWS EC2 instance. The first portion of each line to the left of the $ is the user@hostname

Invoke Command as User:

YMMV but this should work too...

mongodb@foo $ sudo -u mongodb mongod --dbpath /data/db --fork

The MongoDB user might have been created to be able to access services. Should using root username not be recommended, may I recommend that you create an admin user on your mongodb database? See how to do that in the MongoDB documentation:

Manage Users and Roles

Since you are able to access the database using the root username, creating an admin account will allow you to modify the mongodb user's password.

I encountered this issue with SQL Server in the past. Modifying the service account was what was required.


MS SQL full datetime to only month and date i am using ms sql server, i have date filed with value '01-Jan-2016 12:12:03PM'
from this format i need something like 'January-2016'
how can i do this?
with this i need to count something by month.

MY code following

select
DATENAME(MONTH,p._DATE) AS AC_MONTH_,
COUNT(CASE WHEN p.PC=266 THEN 1 END) AS PRE_DATE,
COUNT(CASE WHEN p.PC=1325 THEN 1 END) PP_DATE
from true_all p
where  p._DATE>'01-Jun-2016'
group by DATENAME(MONTH,p._DATE)
order by DATENAME(MONTH,p._DATE) desc

For best performance, use date/time arithmetic to truncate a datetime column to year and month before using it for grouping. There are at least two distinct approaches to that.

Approach 1

The first is a well-known date/time truncation method applicable to various units, not just months. It consists in finding the difference between your datetime value and another, fixed, datetime value in the same same units that you want to perform the truncation on and adding that difference to the fixed datetime value. Very often a 0 is used as an easily memorable fixed value (it represents the date of 1900-01-01 00:00):

DATEADD(unit, DATEDIFF(unit, 0, yourDateTimeValue), 0)

You want to do the truncation on months – so, in your case it would be

DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)

The result will be the datetime value representing the midnight of the first of the same month as p._DATE. For 2016-11-16 14:32:58.547, for instance, you will get 2016-11-01 00:00:00.000.

Approach 2

Another method, primarily applicable to dates but easily adaptable for datetimes, is to take the date's day value (for instance, 16 for today), decrease it by one (15) and subtract that number of days from the date. As you can understand, that will also give you the first of the same month. In your case the expression would be:

 DATEADD(DAY, 1 - DAY(p._DATE), p._DATE)

That assumes p._DATE is the date type. If it is datetime, just cast the second occurrence to date:

 DATEADD(DAY, 1 - DAY(P._DATE), CAST(P._DATE AS date))

Usage

With both approaches the expression is supposed to be used as the grouping criterion. Using the first one as an example, your query could be rewritten like this:

SELECT
  AC_MONTH_ = DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)
FROM
  dbo.true_all AS p
WHERE
  p._DATE > '01-Jun-2016'
GROUP BY
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
ORDER BY
  AC_MONTH_ DESC
;

Notes on formatting

Of course, if you want the date values in the output to look like Month-YYYY, you will need to format them accordingly. Conventionally, formatting should be done at the presentation level, not in the database, so that the application could take advantage of the client-side locale settings.

If you insist on doing it in Transact-SQL, though, you can certainly have some options. Just make sure you apply the formatting to the already transformed value (the expression) rather than the source column. Use of the DATENAME function is one of the options:

  AC_MONTH_ = DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0))
            + '-'
            + DATENAME(YEAR,  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)),

In order to suppress repetition of the same long expression, you can use CROSS APPLY to calculate the month date once and reference it both in GROUP BY and in SELECT, like this:

SELECT
  AC_MONTH_ = DATENAME(MONTH, x.MONTH_DATE) + '-' + DATENAME(MONTH, x.MONTH_DATE),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)
FROM
  dbo.true_all AS p
  CROSS APPLY
  (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
  ) AS x (MONTH_DATE)
WHERE
  p._DATE > '01-Jun-2016'
GROUP BY
  x.MONTH_DATE
ORDER BY
  x.MONTH_DATE DESC
;

Note the ORDER BY x.MONTH_DATE DESC in this case. Now that the AC_MONTH_ column holds formatted values, it can no longer be used for consistent sorting. But the grouping criterion can, so the above query is using it in ORDER BY.

You can try following script

SELECT  DATENAME(MONTH, CAST('01-Jan-2016 12:12:03PM' AS DATE)) + '-'
        + CAST(YEAR(CAST('01-Jan-2016 12:12:03PM' AS DATE)) AS VARCHAR);


Create index view with self join I use SQL Server 2008 R2 and know that in Indexed View cant have self join.

I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between each record and parent record. can I simulate self join in this view?

EDIT

My table structure is :

 SAM.Detail (DetailID Int, ParentDetailID Int, Quantity Int, ...)

and my query is :

 Select A.DetailID,
       A.Quantity - SUM(B.Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail A
inner join SAM.Detail B ON B.ParentDetailID = A.DetailID
Group By A.DetailID, A.Quantity

If all you need is to speed up your query, you can create the following indexed view:

Select ParentDetailID ,
       SUM(Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail
Group By ParentDetailID

You can join it you Detail table, and your query might run faster.

I personally suggest you can look through what is left join , inner join and right join

You can read the details by searching the related keyworkds or go to thi following webpage(s)

http://www.w3schools.com/sql/sql_join_right.asp

https://stackoverflow.com/questions/2647150/outer-join-for-parent-child-chain

In this case, right join is more suitable if your child record must have parent ID and all parent ID is not null

The Child record having null parent ID or parent record have no child cannot be displayed under inner join .

Note that if the right join joining the self table continues forever , the tree structure simulating the structures assigning file folders like Windows or Mac OSX can be simulated.


How restore a specific database from backup using mongorestore command I created a backup of all my databases using mongodump command. Now I want to restore a specific database using mongorestore command.

How is this possible, I use this command: --db option then mongodb doesn't restore a specific database.

To restore a single database you need to provide the path to the dump directory as part of the mongorestore command line.

For example:

# Backup the training database
mongodump --db training

# Restore the training database to a new database called training2
mongorestore --db training2 dump/training

The --db option for mongodump specifies the source database to dump.

The --db option for mongorestore specifies the target database to restore into.

Maybe slightly unrelated, but based on the answers here i got the following

Using mongodump and mongorestore archive feature I comprised a oneliner:

mongodump --host H --port P --username U --password PWD --archive | mongorestore --username U1 --password PWD1 --archive

The above example will :

Dump data from host H with port P logged in with user U and password PWD
"Stream" that data into your local database with user U1 and password PWD1


SQL server permissions during installation I'm trying to find out when a SQL server installation grants permissions on the OS.

I know that the following permissions are giving during setup msdn article about permissions.

However what I don't know is how SQL server will react if the permissions are there, but granted through a group. My problem is that the OS group policies do not allow for several of the required permissions to be granted. And we are going to add the users to a group, that is then included in the group policy.

Will SQL server correctly detect that the required permissions are present, and skip adding the users individually? Or will the installation end on error because it can not grant the required privileges?

We're planning to test this soon, however due to time constraints I was hoping some-one here has experience with this issue, and can give me an answer.

Update: I'm quickly adding some information in the hopes that someone can explain the observed behavior to me...

We are able to install SQL server, despite group policies preventing nearly all the permissions mentioned in the before linked MSDN article. However if we then try and run an SSIS package, or certain server wide jobs, we receive the error that SQL Server lacks the required privileges.

Why was SQL Server able to install, without error even, despite not being able to add the sql server agent and service users to the User Right policies it needs according to the MSDN documentation?

Currently SQL Server is running, despite the user not having local permissions, and not having the Log on as a service permissions... Am I wrong in my understanding of why these permissions are required?

I will say up front that I do not know why your company would have policies that lock down the OS so much that a Microsoft product cannot be installed and provided the appropriate permissions during an installation. I would think they might be doing that backwards. I supported the military as a contractor for many years and that included secured networks, we did not lock down the OS until after all software was installed on the server. We at most wanted to ensure the software installed itself appropriately without any issue.

Outside of that, it is going to depend on what permissions you are not allowing to be set. If it is being restricted on what OS level permissions are set within the local group policy that are going to prevent the services from starting the installation will likely fail.

Shorting SQL Server services the documented permissions required to operate might affect your supportability with Microsoft. Just something to keep in mind.

Edit/Update

There is no in-depth doc that is going to go through what all the installation process does on a server. You can weed through the installation log under the default directory here: C:\program files\Microsoft SQL Server\110\Setup Bootstrap\Log. The 110 would be based on which version of SQL Server you are working with: 100 = SQL Server 2008/R2; 110 = SQL Server 2012; 120 = SQL Serve 2014.

I don't know anyone has gone through and determined the outcome of each little affect of an OS level permission not existing. All that is needed to understand is that permissions assigned according to documentation show what is required for a supported installation of SQL Server. You are basically going to experience sporadic issues based on what you need to do. Every permissions has a purpose, you just may not have reached or hit that purpose as of yet.

I'm trying to find out when a SQL server installation grants permissions on the OS.

During installation during Database Engine Configuration please make sure to add yourself as Administrator for SQL Server Administrator so that you have an access to Specific instance after installation.

During this step you can include user groups to access SQL Server Instance.

You can always add windows groups to SQL Server and allow access depending on their privileges.

As long as you have user added to SQL Server you will not get any error during installation.

Hope this helps you.


How to get rid of these Key Lookups? I have this query:

    SELECT TOP (20) table1.Vit_Codigo
    ,table1.Vit_Nome
    ,table1.Vit_Apelido
    ,table1.Vit_codigoStatus
    ,table1.Vit_Numero
    ,table1.Vit_Ano
    ,table1.Vit_CodigoTiposExames
    ,table3.Esp_Setor
    ,table2.TEs_Descricao
FROM table1
LEFT JOIN table2 ON tb_Vitima.Vit_CodigoTiposExames = table2.TEs_Codigo
LEFT JOIN table3 ON table3 .Esp_Codigo = table1.Vit_CodigoEspecialidade
WHERE (rtrim(ltrim(lower(tb_Vitima.Vit_Rg))) = 'n/a')
    AND (
        table1.Vit_codigoStatus IN (
            3
            ,4
            )
        )
ORDER BY table1.Vit_DataAberturaReal DESC

And these plans:

I made this index:

create index IX_SELECTTOP20_INCLUDE
    on table1
    ( Vit_Rg ,Vit_codigoStatus )
include (
Vit_Codigo , Vit_Nome , Vit_Apelido , Vit_Numero ,
               Vit_Ano , Vit_CodigoTiposExames )

...but it seems the code is using the old index.

So, Why is my index still using key lookup?

Neither index appears to be "covering". To avoid a key lookup, you must at least INCLUDE every column involved in your SELECT, WHERE, and ORDER BY.

The reason the older index is being used is that it's better for the query. That's partially because the index on Vit_DataAberturaReal prevents a sort operator in the plan - because the data is already sorted properly. It's also because the first column in the new index is useless in your query non-sargable. If you can remove the RTRIM, LTRIM, and LOWER, then the performance may be better.

If the index is only to support this query, you may consider adding WHERE Vit_codigoStatus IN (3,4) to your index definition.

Ideally, the WHERE columns would be part of the index itself - not in the INCLUDE. If I were to make an index specifically to support this query, it would probably be along the lines of:

CREATE INDEX [IX_SELECTTOP20_INCLUDE]
    ([Vit_DataAberturaReal])
INCLUDE(
    [Vit_Codigo], [Vit_Nome], [Vit_Apelido], [Vit_codigoStatus],
    [Vit_Numero], [Vit_Ano], [Vit_CodigoTiposExames],
    [Vit_CodigoEspecialidade], [Vit_Rg])
WHERE [Vit_Rg] = 'n\a' AND [Vit_codigoStatus] IN ( 3, 4 )

Note: this will not support the LTRIM(RTRIM(LOWER(...))).

Wow, I just took off the ltrim from the query, and now is at full speed. Just because of this, the query is now using paralelism:

But, Why is this happening? I'm really curious.

if I take RTRIM off, the query is still running slowly. it only works with LTRIM.


Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster SQL Server 2008 R2 multi-instance cluster (on VMware vSphere 5.1)
2 nodes, each running 2 instances (4 instances in total)
16GB RAM per node.

The only time the instances are on the same node is when I am patching the other node.

Brent Ozar's "maximum server memory" recommendation is to leave Windows 4GB or 10%, whichever is more.

Since this a cluster, how should I set the max memory on each node? Should I treat each as a standalone server? This would make sure memory on each node is not wasted. However, in a node failure, the max memory total for all 4 instances would exceed system memory of the single node. Will this cause any issues in the timeframe until we get the second node recovered? Do I need to lower the max memory setting on the 4 instances until the secondary node is recovered? Or is SQL Server smart enough to keep working (using page file if necessary).

You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event using a startup procedure. I blogged about this here, but for a different reason (failing over to a node with a different amount of memory):

https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

Basically, you just need to check if both instances are on the same node (and this will require a linked server to be set up in both directions), and adjust accordingly. A very quick and completely untested example based on my blog post and assuming there is only one instance on each node at a time presently (the question is a bit ambiguous if you have 2 total instances or 4):

CREATE PROCEDURE dbo.OptimizeInstanceMemory
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
     @thisNode      NVARCHAR(255) = CONVERT(NVARCHAR(255),
                                  SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
     @otherNode     NVARCHAR(255),
     @optimalMemory INT = 12288, -- 12 GB
     @sql           NVARCHAR(MAX);

  SET @sql = N'SELECT @OtherNode = CONVERT(NVARCHAR(255),
                        SERVERPROPERTY(N''ComputerNamePhysicalNetBIOS''));';

  EXEC [SERVER\INSTANCE].master..sp_executesql @sql,
    N'@OtherNode NVARCHAR(255) OUTPUT', @OtherNode OUTPUT;

  IF @thisNode = @otherNode
  BEGIN -- we're on the same node, let's make everyone happy
    SET @optimalMemory = 6144;
  END

  SET @sql = N'EXEC sp_configure N''max server memory'', @om;
    RECONFIGURE WITH OVERRIDE;';

  EXEC                   master..sp_executesql @sql, N'@om INT', @optimalMemory;
  EXEC [SERVER\INSTANCE].master..sp_executesql @sql, N'@om INT', @optimalMemory;
END
GO

EXEC [master].dbo.sp_procoption
  N'dbo.OptimizeInstanceMemory', 'startup', 'true';

Of course create it again on the other instance, swapping the linked server name used.

This gets a little more complex if you have to adjust depending on whether you are sharing the current node with 1, 2 or 3 other instances.

Note that this will cause other side effects such as clearing the plan cache (in the event when one of the instances didn't just restart or fail over, in which case the plan cache would be empty anyway), but these are arguably better than leaving both instances to assume they still have 12 GB of memory to play with - there will be a lot of thrashing if they're both heavily used.

You may also want to consider other options such as global maxdop, NUMA/CPU affinity etc. depending on how sensitive the system is to the amount of resources available.

I recommend setting the max server memory to the default value of 2147483647 which means SQL Server will dynamically manage memory.


How can I generate an Entity-Relationship Diagram and SQL DDL statements from natural language text? I am using LaTeX or another text-macro tool/library. How can I (1) use natural language text to describe relationships, then (2) generate an Entity-Relationship Diagram and (3) generate SQL DDL to create the database schema from it?

There are many ways to do this, but the most important thing to know is that there is a name for what you want to do: Object Role Modeling. It's a process for creating conceptual models using natural-language semantics.

This is a pretty good (if old) overview of the process.

The Wikipedia page for ORM has a pretty complete list of the available tools, many of which are free.

The most up-to-date solution is NORMA, since it is actively maintained by the ORM Foundation, but that's a plug-in that requires Visual Studio.

If you don't have Visual Studio, Infagon looks like it is still pretty actively developed and may be your best bet.

CaseTalk is also worth a look. Free to students and educators, but commercial otherwise.

Hey If it's any help i have used visio to create my ERD and revere engineer it to DB using it's Reverse engineering tool, which provides reverse engineering support to ms access, ms sql server, ...


How can I get all data from a given table since my last run? In querying a table in Oracle Database, is there any way to reliably and precisely get all rows that have changed (via inserts or updates) since the last time I checked?
My tables typically have a column update_date DATE that holds the date/time last updated, and I am currently using that.  I'm finding this isn't fully reliable, as some other stored procedure could insert data with this column set properly via SYSDATE, then commit the transaction a few seconds later when it completes.  If I ran a query after the DATE column was written but before the transaction was committed, my query will contain rows with later update_date values (by a second or two, usually) but I won't see the newest rows that have a short delay in being committed.
In other words, suppose in  my first run I SELECT all rows, and the latest row I get has an update_date of '2021-01-15 08:01:02'. Then my job keeps track of that value, and on my second run, the job now naively uses WHERE update_date > :latest_changed_date_from_last_run, but such a query won't include data from '2021-01-15 08:01:02' that was committed after my last run, nor even perhaps data from '2021-01-15 08:01:01' or '2021-01-15 08:01:00'--or really any past DATE--that was written before but committed after my previous run.
(I'm not having trouble storing or retrieving this "latest_changed_date_from_last_run" value, but there seems to be no way to use it to get all the data I need. I can only get most of it.)
So is there any way to ensure I can get all rows changed since my last run?  If there's no way to do it with a SELECT query, is there some other kind of Change-Data-Capture-lite technique I should be considering for this simple scenario?
(More context: This is in trying to use a Kafka Connect JdbcSourceConnector with an ordinary query, but I imagine this concept could be needed in any system that periodically checks for any updates/inserts in any table.)

I think you can base your query on the ORA_ROWSCN pseudocolumn.

If two transactions T1 and T2 modified the same row R, one after
another, and committed, a query on the ORA_ROWSCN of row R after the
commit of T1 will return a value lower than the value returned after
the commit of T2

For this use case, the table must be created with ROWDEPENDENCIES (cannot be changed after table creation)

With this feature, each row in the table has a
system change number (SCN) that represents a time greater than or
equal to the commit time of the last transaction that modified the
row.

If you can recreate the table with ROWDEPENDENCIES, then you would swap your latest_changed_date_from_last_run for latest_ora_rowscn_from_last_run and that should do the job.

You could set up a personal table, one DATE column, inserting one row after each time you query the target table.  Since your table column you are interested in is in DATE format, keep your personal table column the same data type; using TIMESTAMP will not help.
Example personal table, named last_look and target table named sales:
alter session set nls_date_format = 'YYYY-mm-dd hh24:mi:ss';
create table last_look (looked_last date default on null sysdate not null) tablespace users;

And now load some data and test.  Sorry for the length of this, but I wanted to check my answer!
22:30:43 MARK.STEWART> insert into last_look values(null);
1 row created.

22:31:05 MARK.STEWART> select * from last_look;

LOOKED_LAST
-------------------
2021-01-15 22:30:58

22:31:14 MARK.STEWART> create table sales (cust_id number, changed_date date default sysdate) tablespace users;

Table created.

22:31:52 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

no rows selected

22:32:31 MARK.STEWART> insert into last_look values(null);
1 row created.

22:32:41 MARK.STEWART> select * from last_look;

LOOKED_LAST
-------------------
2021-01-15 22:30:58
2021-01-15 22:32:41

22:32:44 MARK.STEWART> insert into sales (cust_id) values (33);
1 row created.

22:32:57 MARK.STEWART>  insert into sales (cust_id) values (34);
1 row created.

22:33:01 MARK.STEWART> commit;
Commit complete.

22:33:04 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01

22:33:15 MARK.STEWART> select * from sales;

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01

22:33:22 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01

22:33:31 MARK.STEWART> insert into sales (cust_id) values (38);
1 row created.

22:33:39 MARK.STEWART> commit;
Commit complete.

22:33:41 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01
        38 2021-01-15 22:33:39

22:33:49 MARK.STEWART> insert into last_look values(null);
1 row created.

22:33:57 MARK.STEWART> select * from last_look;

LOOKED_LAST
-------------------
2021-01-15 22:30:58
2021-01-15 22:32:41
2021-01-15 22:33:57

22:34:03 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);
no rows selected

22:34:07 MARK.STEWART> insert into sales (cust_id) values (39);
1 row created.

22:34:33 MARK.STEWART> commit;
Commit complete.

22:34:35 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);

   CUST_ID CHANGED_DATE
---------- -------------------
        39 2021-01-15 22:34:33

22:34:40 MARK.STEWART> insert into last_look values(null);
1 row created.

22:34:46 MARK.STEWART> select * from sales where changed_date > (select max(looked_last) from last_look);
no rows selected

22:34:50 MARK.STEWART> select * from sales;

   CUST_ID CHANGED_DATE
---------- -------------------
        33 2021-01-15 22:32:57
        34 2021-01-15 22:33:01
        38 2021-01-15 22:33:39
        39 2021-01-15 22:34:33


why would command text not execute at all? in my report i have::

<CommandText>SELECT

    column1,
        column2,
        'poop'
from mytable
</CommandText>

i am using sql server profiler to see exactly what statement is being set.

i have set only two filters:

databaseName

yet after running the report, no statement gets intercepted.

i suspect that because i am a beginner at SSRS, i am missing something crucial here.

for what reason would commandtext not be executed at all?

i did follow this question, to make sure that i am using sql profiler correctly, and indeed, i am: https://stackoverflow.com/questions/9107383/sql-server-profiler-capture-calls-to-your-databases-stored-procs-during-ssrs

another bit of important information is although the chart shows no data:

i actually am indeed showing data when i run the commandtext from ssms!

Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started.

  CREATE EVENT SESSION query_check ON SERVER
    ADD EVENT sqlserver.sql_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text))
    ADD TARGET package0.asynchronous_file_target(SET filename=N'D:\temp\monitor.xel',max_file_size=(5),max_rollover_files=(4))
    --,ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON);

You can start it like this:

ALTER EVENT SESSION query_check ON SERVER STATE=START;

Run your query a few times and then stop the event session:

ALTER EVENT SESSION query_check ON SERVER STATE=STOP;

Then you can query it like this:

SELECT
    fired_event = event_data.value('(/event/@name)[1]','nvarchar(25)'),
    fired_event_time = event_data.value('(/event/@timestamp)[1]','datetime2(0)'),
    event_database_id = event_data.value('(/event/action[@name=''database_id''])[1]','int'),
    event_sql_text = event_data.value('(/event/action[@name=''sql_text''])[1]','nvarchar(max)')
FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file(N'D:\temp\monitor*.xel',
                                            N'D:\temp\monitor*.xem',
                                            NULL, NULL)
    ) events

You can capture more data, you can choose what you'd like from here:

SELECT p.name AS package_name,
       o.name AS action_name,
       o.description
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects AS o
     ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'action'

So if you wanted to capture login information, you'd modify your ACTIONs from

ACTION (sqlserver.database_id, sqlserver.sql_text)),

to

ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.username)),

I'd actually recommend against filtering on the query text because it's expensive. Instead, I'd try to find the correct username. If you don't think this will work, then I'd filter based on the query text of the result XML file. In fact, only in SQL Server 2012 can you filter on the statement text anyway (it's called a predicate).

If you wanted to perhaps capture data from a specific username you'd add WHERE to each EVENT that you'd like to filter.

ADD EVENT sqlserver.sql_statement_starting(
    ACTION (sqlserver.database_id, sqlserver.sql_text)
    WHERE (sqlserver.username = N'{{ your username here }}')
)

This sounds like an issue with the report design, not the query; I would try building a new report from scratch, using the wizards and text output to verify, then add the graph to the report.


How to disable ldf file or limit its size Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again.
How can I permanently limit the filesize or perhaps even disable it entirely (since I don't think I'll ever use it).

You cannot disable/remove the LDF file. It has to exist, as it stores database transactions.
What you can do is:

Set the database recovery model to Simple

This should keep your LDF file from growing, and is set via the Options page of the Database Properties dialog.

For additional reducing, which I don't think is necessary, you can:

2 Set Auto Shrink to True

This is also done from the Options page of the Database Properties dialog:

3 Turn auto-grow off

This is done from the Files page of the Database Properties dialog:

Be aware that when a database is in the Simple recovery model, you will not be able to recover to a point in time

Or

Set the database recovery model to Full (I guess it's already set this way)
Create a full database backup
Start creating transaction log backups regularly. It's best to schedule a SQL Server job to do this

The latter scenario provides better disaster recovery as it enables recovery to a point in time
It's up to  you to decide how much data you can afford to lose

You can take a look at the answers I received and posted in this thread: SQL Server virtual log size

I'm not sure if it's possible to disable the log completely, but the way I solved it has kept the logs from growing out of control.


question regarding csv format? CSV format cannot be used for importing text files to a database unless the table for the data is already defined in the database.

is this true or false?

For MS SQL Server.
False: You can use the Import Data Wizard to map your columns in the CSV file to columns in the Table that the Wizard will create when you run it.


MySQL: Using DATE_SUB to subtract business days instead of all calendar days I am looking for a robust solution to subtract the number of business days from a date. I am using DATE_SUB however it subtracts all calendar days.

For example, if today is Monday and I subtract 2 days from today, I would like for the result to be Thursday last week and not Saturday.

You can use DAYOFWEEK to calculate the right number of days to subtract based on that. This also works for date_add. For the DAYOFWEEK index, 1 is equal to Sunday and 2 is equal to to Monday.

case
when DAYOFWEEK({date}) = 1 then date_sub({date}, Interval 2 Day)
when DAYOFWEEK({date}) = 2 then date_sub({date}, Interval 3 Day)
else date_sub({date}, Interval 1 Day)
end

http://www.w3resource.com/mysql/date-and-time-functions/mysql-dayofweek-function.php


Python script leaves the database in restoring mode I am attempting to use python to automate some of our database restores.  When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the same SQL query the database is left in the restoring mode.
Any help with what I'm doing wrong would be greatly appreciated.
import pyodbc

#Set these Variables
sourceserver = 'MySourceServerName'
destinationserver = 'MyDestinationServerName'
dbname = 'test'

sourceserverplusport = sourceserver + ',1433'
destinationserverplusport = destinationserver + ',1433'

def ExecSQL(serverplusport, dbnameIN, sql):
    try:
        connexecsql = pyodbc.connect('Driver={SQL Server};'
                                         'Server=' + serverplusport +';'
                                         'Database=' + dbnameIN + ';'
                                         'Trusted_Connection=yes;')

        #Set AutoCommit
        if connexecsql is not None:
           connexecsql.autocommit = True

        print(sql)
        cursor = connexecsql.cursor()
        cursor.execute(sql)
    finally:
        cursor.commit
        cursor.close
        connexecsql.commit
        connexecsql.close

sqlcommand = r"""IF @@SERVERNAME = 'MyDestinationServerName'
RESTORE DATABASE test FROM DISK = '\\MyBackupPath\test.BAK' WITH FILE = 1, MOVE N'test' to N'X:\Database\MDF\test.mdf',  MOVE N'test_log' to N'Y:\Database\LDF\test_log.ldf',  NOUNLOAD,  REPLACE, RECOVERY, STATS = 5;"""

ExecSQL(destinationserverplusport, "master", sqlcommand)

If I add one more ExecSQL command at the end of the script to run RESTORE DATABASE test WITH RECOVERY; at the end returns the error:

The database cannot be recovered because the log was not restored."

Including the REPLACE with the RECOVERY removes the error but leaves the database in restoring still.

Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.
Eg
cursor.execute(sql)
while cursor.nextset():
    pass
cursor.close()

Have you considered using https://dbatools.io/ instead?
It's FREE and highly regarded.
Check out https://docs.dbatools.io/#Restore-DbaDatabase.
No disrespect to Python intended.


Mass data upload from Excel - technology I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list.

Requirement:
There will be a web application which will allow user to upload Excel document in specified format(expecting 1 Million of rows) and it will then Save somewhere in DB and run some calculation against our data base values. After that will present user with some numbers/chart based on calculations.

My first question is MySQL DB good enough for this requirement over any other DB like Postgres?
Second question is how much time will it take to upload 1 million rows of data from Excel, considering there will be 6 columns?
Third, which technologies should I consider to make this application fast and efficient like InnoDB, etc.

Once again I have done my research but I could not reach into conclusions, and any DB expert feedback/help will be highly appreciated.

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user.  Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use:

COPY table FROM 'fpath' CSV HEADER DELIMITER ';'

You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well.

If you must use excel formats like .xlsx, why not use something like Pentaho and develop an application that can connect to any database. There is a java api for this one. Other ETL tools may do the trick too.

In these instances, upload speed is under 10 minutes. My last upload over a non-commercial network of 900000 rows lasted 20 minutes in Pentaho. A straight copy to in PostgreSQL is actually much faster.

As for speed, querying is your major problem. If you are just copying and querying, Postgres is fine. However, if you are running intensive queries, counts; aggregation, use an ETL tool like Pentaho that you can run from Java and connect with a connection pool like BoneCP for Postgres or another connection pooling software for other databases.

Most databases support connection pooling but have connection limits so look at your configuration settings.

I have used this script called File Big Dump, works really well for CSV/Excel files to be uploaded.

http://www.ozerov.de/bigdump/


Joining two tables with different fields through a link-table Im a complete novice to Access (2013).

Im doing some History research and thought it would be easier to use Access. I have created two table so far.

People, Details info about them
Events (historical).

Both have different fields.

Can I create a third table that collates all the info from the first two tables?

Therefore, I can 'organise' the dates to display in order. (may need to look at DOB and Event Date to merge under name field name to do this?)

I've uploaded the file for you to see if it helps. I tried to search for answers before posting but nothing along the lines im looking at was thrown up. Also have no coding experience, so this is completely new to me.

Thank you in advance

Link to Database File

People and Events are related thorugh a many-to-many relationship, because an event can have many people related to it, and one person can be related to several events.

This is represented in a SQL database by means of a link-table, which contains (at least) two columns (fields), one indicating the unique id representing the Event, the other one indicating the unique id representing the Person (People). Let's call those columns EventId and PeopleId. The table might contain more columns if they should provide some relevant extra information (let's say you want to know which role the person had with regard to this event, such as "was the organiser", "was an attendant", "was a sponsor", "was the inducer", "led the event", "was one of the lead roles", "just was a passer-by", "was the victim", ...).

The primary key of this table is actually a compound one, comprising both (EventID, and PeopleID). So your table should be defined in a fashion similar to the one depicted in the following image:

You can let Access know how this table is related to the other ones by means of the Relationships Diagram:

The corresponding SQL definition is:

CREATE TABLE [People_x_Events]
(
    [PeopleID] LONG NOT NULL REFERENCES [People] ([ID]),
    [EventID]  LONG NOT NULL REFERENCES [Events] ([ID]),
    PRIMARY KEY ([PeopleID], [EventID])
) ;

This directly answers your question about "collating" records and "merging" fields.  It is not exclusive to joanolo's answer, rather you could combine both techniques to provide a richer set of data.  You mentioned a "third table", but you really only need queries to combine your existing tables.  There are indeed times when you want additional tables if you need to store additional information or create necessary links as joanolo's answer details. Otherwise, extra tables for combining data can just lead to duplicate information which requires double the work to maintain and can develop discrepancies if not maintained properly (in other words, avoid copying data between tables).

A query, on the other hand, always draws form the current data and so is always up-to-date.  The following set of example queries

combine fields using string concatenation.  There are other ways to combine data.

There are some fields that maybe should not be combined, that should be left blank for certain records.  To leave a field blank for a certain query, add a field like this Null As [Field Name]

rename fields using the SQL AS keyword.
use SQL Unions to combine query results into one set of data which can be sorted altogether.

Saved query [Timeline Events]:

SELECT
    Events.[ID] As IDMixed,
    "Events" As IDType,
    Events.[Event Date] AS [Timeline Date],
    [Event Type] & "; " & [Notes] AS [Timeline Details]
FROM Events

Saved query [Timeline People Births]:

SELECT
    People.[ID] As IDMixed,
    "People" As IDType,
    People.DOB AS [Timeline Date],
    "Born: " & [First Name] & " " & [Surname] AS [Timeline Details]
FROM People
WHERE People.DOB Is Not Null

Save query [Timeline People Deaths]:

SELECT
    People.[ID] As IDMixed,
    "People" As IDType,
    People.DOD AS [Timeline Date],
    "Died: " & [First Name] & " " & [Surname] AS [Timeline Details]
FROM People
WHERE People.DOD Is Not Null

Altogether now:

SELECT  [IDMixed], [IDType], [Timeline Date], [Timeline Details]
FROM [Timeline Events]
UNION
SELECT [IDMixed], [IDType], [Timeline Date], [Timeline Details]
FROM [Timeline People Births]
UNION
SELECT [IDMixed], [IDType], [Timeline Date], [Timeline Details]
FROM [Timeline People Deaths]
ORDER BY [Timeline Date]

The reference to the source table requires not only the ID, but also something that indicates the source table.  To present a related record, for example to show the Event details when the user selects a timeline event, the usual technique (based on proper, normalized table design) is to create a bound subform which can automatically show related records from another table or query.  But this technique also works only when a foreign key (e.g. ID values) refers to a single table.  There is no built-in behavior to switch between multiple related tables as in this case with the combined data.  Eventually it will require more advanced Access programming techniques.  Here is an outline of how I would proceed, but if you have difficultly, you may need to explore possibilities beyond this question and answer, perhaps posting new questions on Stack Overflow since this goes beyond Database Admin and design.

Create a main form with the combined timeline query as the record source.

Along with other desired fields, add controls for [IDMixed] and [IDType] fields but probably make them hidden (i.e. Visible = False).

Create separate subforms for each table type: People and Events

A subform is really just a normal form, only that it is designed to be displayed in a subform control of another form.  You create and design it like any other form.  In other words, there is no special "subform" type.
Make sure that a control for the [ID] fields are on each form, even if you make them hidden.

Add both subforms to the main form.

Make them both hidden: Visible = False.
Position them so that they are on top of each other.
In the subform Property Sheet, set the Link Master Fields to IDMixed and the Link Child Fields to ID.

In the main form's OnCurrent event, add the following VBA code.  Note that this is only a template.  At the least, exact names of controls will need to be updated.
There are other more advanced techniques, perhaps more elegant or efficient, but they require more advanced programming techniques and understanding of Access intricacies.

Private Sub Form_OnCurrent()
    On Error Resume Next
    If Me!IDType = "Events" Then
        Me.EventsSubForm.Visible = True
        Me.PeopleSubForm.Visible = False
    ElseIf Me!IDType = "People" Then
        Me.EventsSubForm.Visible = False
        Me.PeopleSubForm.Visible = True
    End If
    If Err.Number <> 0 Then
        Me.EventsSubForm.Visible = False
        Me.PeopleSubForm.Visible = False
    End If
End Sub

Besides the general structure which these queries outline, the specific fields which you include is completely of your design and choice.  I assume you will want to include more types of timeline dates, but all you do is add another query and combine it by appending another UNION.

Notes about Union: It is not required to save the other queries separately--the SQL from the original queries could be included directly with the UNION statements, but saving them separately makes it easier to view, edit and verify each separate query individually.  It is technically not required that the columns of each query have the same names, only that they have the same number of columns and are of compatible data types.  The resultant query will take columns names from the first query in the union.