Sql implementations

Sql implementations

• 1.
  June 26, 2017www.snipe.co.in 1 Prepared : Snipe Team
• 2.
  June 26, 20172 SQL standard, implementations, Comparison
• 3.
  June 26, 20173 Agenda •Database Introduction •Different SQL standard and implementations - MySql - SQL Server - Oracle DB - PostgreSQL • Comparison •Statistics
• 4.
  June 26, 20174 Database introduction •A database is structured collection of data. •Databases may be stored on a computer and examined using a program •These programs are called database management systems (DMS) •Relational database: a database structured to recognize relations between stored items of information •An object-relational database (ORD), or object-relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language.
• 5.
  June 26, 20175 MySql Mysql • Is a Relational Database Management System • MySQL is written in C and C++ • Its SQL parser is written in yacc • The first version of MySQL appeared on 23 May 1995 • MySQL is offered under two different editions: the open source MySQL Community Server and the proprietary Enterprise Server
• 6.
  June 26, 20176 MySql Limitations •MySQL does not currently comply with the full SQL standard for some of the implemented functionality •Up until MySQL 5.7, triggers are limited to one per action / timing •No triggers can be defined on views •MySQL database's inbuilt functions like UNIX_TIMESTAMP() will return 0 after 03:14:07 UTC on 19 January 2038
• 7.
  June 26, 20177 Mysql Server • Is a relational database management system developed by Microsoft •As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications which may run either on the same computer or on another computer across a network (including the Internet). •SQL Server uses as its primary query languages T-SQL and ANSI SQL •Azure SQL Database is the cloud-based version of Microsoft SQL Server, presented as a platform as a service offering on Microsoft Azure
• 8.
  June 26, 20178 Mysql Server • •Data storage is a database, which is a collection of tables with typed columns •Microsoft SQL Server also allows user-defined composite types (UDTs) to be defined and used •SQL Server buffers pages in RAM to minimize disc I/O
• 9.
  June 26, 20179 Oracle DB • Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system produced and marketed by Oracle Corporation •An Oracle database system—identified by an alphanumeric system identifier or SID—comprises at least one instance of the application, along with data storage •The Oracle DBMS can store and execute stored procedures and functions within itself •The Oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files
• 10.
  June 26, 201710 Oracle DB • The partitioning feature was introduced in Oracle 8 •This allows the partitioning of tables based on different set of keys. •Specific partitions can then be easily added or dropped to help manage large data sets. •Oracle database management tracks its computer data storage with the help of information stored in the SYSTEM tablespace •The SYSTEM tablespace contains the data dictionary—and often (by default) indexesand clusters
• 11.
  June 26, 201711 PostgreSql • is an object-relational database management system (ORDBMS) with an emphasis on extensibility and on standards-compliance • PostgreSQL implements the majority of the SQL:2011 standard • handles complex SQL queries using many indexing methods that are not available in other databases •PostgreSQL is cross-platform and runs on many operating systems including Linux, FreeBSD, Solaris, and Microsoft Windows • It is free and open-source software, released under the terms of the PostgreSQL License, a permissive free-software license
• 12.
  June 26, 201712 PostgreSql • PostgreSQL manages its internal security on a per-role basis • The sepgsql extension (provided with PostgreSQL as of version 9.1) provides an additional layer of security by integrating with SELinux • Current version 9.4 released on 2015-06-12 (JSONB data type, ALTER SYSTEM statement for changing config values, refresh materialized views without blocking reads)
• 13.
  June 26, 201713 Comparison Feature MS SQL Server Oracle Mysql PostgreSQL License Commercial software Commercial software Free/open source Free/open source Server-side scripts Transact-SQL & .NET lang. PL/SQL ---- User defined functions OS support Only windows All except BSD All All Maintainer/ developed by Microsoft Oracle corporation Oracle Corporation PostgreSQL Global Development Group Interface GUI, SQL API, GUI, SQL GUI, SQL API, GUI, SQL
• 14.
  June 26, 201714 Comparison Feature MS SQL Server Oracle Mysql PostgreSQL ACID properties Supports Supports Supports Supports Auto-increment columns Yes No Yes Yes (SERIAL data type) Automatic conversion of code pages No Yes No No Sql variant Fast standard Fast standard Slow standard Fast standard
• 15.
  June 26, 201715 Statistics According to CD Times magazine dated July 1, 2004 Top Deployed Databases poll shows following databases in use: SQL Server with 78%, Oracle - 55%, MySQL - 33% and PostgreSQL - 8%.
• 16.
  June 26, 201716 Statistics In 2009, in a survey, total votes received are 1,697. SQL Server – 1,121 – 64%,Oracle – 432 – 25%, MySQL – 144 – 8% ,Other – 64 – 4%. Other includes PostgreSQL with 12 votes
• 17.
  June 26, 201717 Statistics