What is the Best Database

If someone asks you "What is the best database", what is your answer. Answers might be different depending on your job role, whether you are a developer or a database administrator or client.

However, there is a ranking done for the database every month and the following is the latest ranking of databases. 

You can look at the rank of all the 359 databases here. According to this ranking, first six databases, Oracle, MySQL, Microsoft SQL Server, PostgreSQL, MongoDB, IBM BD2 are remaining the same compare to the last year.  If you look at the entire list you will find that Azure SQL Server Database is a major improvement. Azure SQL Server Database ranked 17 this year where it was 25th in last year.  

Let us look at the trending of these databases. 

You can see that MySQL and Oracle are running closers whereas PostgreSQL and MongoDB are running a close encounter. 

This does not say one database is better than others. This ranking based on various factors, such as Number of mentions of the system on websites, Frequency of technical discussions about the system, Number of job offers, in which the system is mentioned, Number of profiles in professional networks, in which the system is mentioned, and Relevance in social networks etc. You can look at the complete ranking parameters here.

SQL Server Migration Assistant (SSMA) v8.1

SQL Server Migration Assistant (SSMA) for Oracle, MySQL, SAP ASE (formerly SAP Sybase ASE), DB2, and Access allows users to convert a database schema to a Microsoft SQL Server schema, upload the schema, and then migrate data to the target SQL Server (see below for supported versions).

• SSMA for Access
• SSMA for DB2
• SSMA for MySQL
• SSMA for Oracle
• SSMA for SAP ASE

Magic Quadrant for Data Management Solutions for Analytics

Microsoft was leading in the Gartner quarter for while and Oracle was not promising in this. However, with the 2017 results, landscape has changed.



You can read the details at https://www.gartner.com/doc/reprints?id=1-3T5J67S&ct=170209&st=sb

DB-Engines Ranking

It is very common to compare the databases for marketing or for fun. http://db-engines.com/en/ranking has done the ranking for the database engine.

SQL Server has the major gain while Oracle has the major slip though it is still gaining the number one position. Following is the trend for the ranking of Database Engine. 

Which relational DBMS is best for your company?

There are lot of databases around you. What should I select from them. Lot of parameters you need to think. Database expert Craig S. Mullins examines the top relational database management system software to determine which best fits your organization.

Read more at http://searchdatamanagement.techtarget.com/feature/Which-relational-DBMS-is-best-for-your-company

The World According to DBAs

Database administrators (DBAs) are at the frontline of data management in most enterprises, identifying and often choosing to adopt new technologies. According to a new survey of DBAs, "While Hadoop and NoSQL are exciting new technologies," primarily large companies use them now and they "do not factor into many companies' plans over the next few years." Furthermore, traditional database management systems remain the foundation for information management infrastructure in most organizations, with Oracle and Microsoft SQL Server the dominant platforms for supporting mission-critical data. To examine the database infrastructure, understand the roles of DBAs today and how their roles are changing, Dell commissioned Unisphere Research, a division of Information Today, to survey database administrators and others charged with managing corporate data. There were 300 respondents from a wide range of companies representing a dozen industries. Two-thirds came from organizations with more than 1,000 employees. One-quarter of respondents' organizations run more than 500 databases. - See more at: http://www.cioinsight.com/it-strategy/big-data/slideshows/the-world-according-to-dbas.html#sthash.9u6JjSqn.dpuf

Before Triggers in SQL Server

There are lots of questions asking whether there are before triggers in SQL Server. There are nothing called Before Triggers in SQL Server.

What is the requirement for the Before Trigger?

Let us say, you want to verify some values other table before inserting it. In SQL Server, you can use INSTEAD OF TRIGGER.

CREATE TRIGGER tr_data_before ON Table_Data
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Tablle_Data2)
BEGIN
    INSERT INTO dbo.Table_Data
    SELECT *
    FROM INSERTED
END
END

However, in Oracle and DB2 there are Before Triggers.

MySQL's creator on why the future belongs to MariaDB

It's fair to say that MySQL creator Michael "Monty" Widenius is not a fan of Oracle. When the company announced in April 2009 that it was purchasing Sun, Widenius saw a bleak future ahead for the (still) wildly popular open source database, which Sun had snapped up in 2008.

The day the Sun purchase was announced, Widenius responded in the tried and true open source fashion — he forked MySQL, launching MariaDB, and took a swathe of MySQL developers with him.

"Many of the original MySQL core developers, including me, didn't believe that Oracle would be a good owner of MySQL and we wanted to ensure that the MySQL code base would be free forever," Widenius explains.

Read more http://www.computerworld.com.au/article/457551/dead_database_walking_mysql_creator_why_future_belongs_mariadb/

Ignore Duplicates in Oracle

When I wrote post about Ignore duplicates in SQL Server, I got the following comment from one of the LinkedIN group.

“

Also, Oracle has a great way to handle this in 10g and beyond.
By James Williams
@LinkedIN group SQL Server DBA

“

I thought looking into this with my very limited knowledge in Oracle.

I used Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 version.

First let me create the table with the unique constraints.

Let me insert three rows to the table.

Now the question is how oracle is managing duplicate inserts for unique constraints.

The above statement create an error logging table using the DBMS_ERRLOG package, and one of the inserts violates the check constraint on CITY, and that row can be seen in city_errors. If more than ten errors had occurred, the statement would have aborted, rolling back any insertions made:

Let’s say there is a another table of data (tempCity) and we want to populate CITY with tempCity.

If you simply insert tempCity into CITY entire transaction will be failed since Denver city already exists.

However, you can insert using following syntax.

So you can see only two rows will be inserted while other duplicate row is ignored.

by analyzing city_errors table you will be able to get which records were failed with the reason.

NOLOGGING In Oracle Databases

In Oracle there is a valuable feature where you can disable logging for only one table. In SQL Server maximum granularity you have is database level. So in case, you are doing lot of inserts or index rebuilding for only one table you have the option of disabling the logging for that table.

ALTER TABLE t1 NOLOGGING;

So above command will suspend the logging for table t1,

Only the following statements can make use of nologging:

Alter Table

• Move Partition


• Split Partition


• Add Partition


• Merge Partition


• Modify Partition

Alter Index

• Split Partition


• Rebuild


• Rebuild Partition

Create Table

Create Index

More info from following links

http://www.orafaq.com/wiki/Nologging_and_force_logging

http://www.adp-gmbh.ch/ora/misc/nologging.html

http://www.dba-oracle.com/t_nologging_append.htm

So I think it is good feature to have for SQL Server as well.

Oracle Unveils Migration Tool for Microsoft SQL Server to MySQL

Oracle is going after users of Microsoft's SQL Server with a new tool for migrating data from SQL Server to its own MySQL database, the vendor announced Wednesday.

The tool has been built into Oracle's MySQL Workbench administration console, and with it, applications written for SQL Server can be easily tweaked for MySQL, Oracle said in a statement.

Oracle is also releasing an improved version of the MySQL Installer for Windows environments, as well as a new MySQL Notifier for Windows tool that "helps developers and DBAs to easily monitor, start and stop their MySQL database instances, with the Microsoft SQL Server look and feel," Oracle said.

In addition, Oracle is offering a new plug-in that allows users with no experience on MySQL to work with MySQL data inside Microsoft Excel.

Oracle claims that MySQL users can experience up to 90% less total cost of ownership compared to running SQL Server 2012.

More at:

http://www.oracle.com/us/corporate/press/1715479

http://www.computerworld.com/s/article/9229646/Oracle_hopes_to_poach_Microsoft_SQL_Server_users_with_MySQL_migration_tool?taxonomyId=18

GROUP_CONCAT or LISTAGG

 

Well, this is not some thing SQL Server has. This is a basic function you get in ORACLE or in mySQL.

What it does.

Let us look at this data set. It has Color and ProductNumber where ProductNumber is the business unique key,

What I need is following output.

In  mySQL you can use simple function GROUP_CONCAT

in Oracle you have LISTAGG function

Source :http://oracleabc.com/b/archives/2100

In both cases it is one table scan which means cost is low,

Let’s move to SQL Server.

You don’t have a out-of-box function. However there is a workaround for this. Honestly I don’t know how this is working.

and the query plan is,

In case of SQL Server, you need to use DISTINCT and WHERE IS NOT NULL and more importantly there is two table scans, which means you might not use this for large tables. 

So has Microsoft SQL Server team has reserved this for future versions.