Select Statement  «Prev  Next»

Lesson 6 Choosing your database
Objective Choose Which Database will you use for your Project?

Which Database will you use for your Project?

In this lesson, you will learn what you need to do to get your database online and working and what information you will need to provide to your database administrator (DBA) to ensure that appropriate connections to your system will exist.

Choose your Database

You will likely experience one of two extremes in your database decision. Either you will have the decision mandated to you by your company's database policies and standards, or you will have free reign to select the tools you want to work with. Of course, if the first case is true, simply use the database engine that has been selected for you.
The second case is where things get interesting. First, you will need to determine what software you actually have access to. Keep in mind that Microsoft Access is part of Microsoft Office, so it is likely that, if you have Microsoft Office on your system, you can either use the existing installation of Access or install it from the distribution CD. If you have a choice, Access is a likely candidate for experimentation, and one that is easy to implement and use in the capacity that serves this course.
Of final consideration is your plan for moving forward. If you have a database system in place, or if you are looking to use your soon-to-be SQL talents on a company project, then by all means use that database system for this course. It will be easier to relate to something that you have a vested interest in learning.

IBM DB2

IBM is a long-term front-runner in the RDBMS arena, from the mainframe world with the MVS family of operating systems, to z/OS, and later to UNIX and Windows. Version IBM DB2 9.7 can run on the following operating systems (Linux, UNIX, and Windows). The IBM DB2 9.7 keeps the absolute record in transaction processing speed and comes in a variety of editions, from Advanced Server Enterprise to a free DB2 Express-C edition. DB2 in its version 9.7 is still only compliant with the ANSI/ISO SQL 92 Entry standard and supports some of the more advanced features from other standards organizations such as the Open Geospatial Consortium, JDBC, X/Open XA, as well as bits and pieces of the SQL:2008 Standard. In addition to its own built-in procedural extension language, SQL PL, it also provides support for Oracle's PL/SQL, Java, and even Microsoft's .NET family languages for creating stored procedures.

SQL Server - 2016

When users want data, they want it as fast as you can give it to them. Microsoft SQL Server 2016 includes several options for enabling faster queries. Memory-optimized tables now support even faster online transaction processing (OLTP) workloads, with better throughput as a result of new parallelized operations. For analytic workloads, you can take advantage of updateable, clustered columnstore indexes on memory-optimized tables to achieve queries that are up to one hundred times faster. Not only is the database engine better and faster in SQL Server 2016, but enhancements to the Analysis Services engine also deliver faster performance for both multidimensional and tabular models. The faster you can deliver data to your users, the faster they can use that data to make better decisions for your organization. The next lesson will help you set up whichever database you have chosen.

In-Memory OLTP enhancements

Introduced in SQL Server 2014, In-Memory OLTP helps speed up transactional workloads with high concurrency and too many latches by moving data from disk-based tables to memory-optimized tables and by natively compiling stored procedures. In-memory OLTP can also help improve the performance of data warehouse staging by using nondurable, memory-optimized tables as staging tables. Although there were many good reasons to use memory-optimized tables in the first release of In-Memory OLTP, several limitations restricted the number of use cases for which In-memory OLTP was suitable. In this section, we describe the many enhancements that make it easier to put memory-optimized tables to good use.

SQL Queries