In case you’ve entered the programming and analytics, structured query language or SQL has been an analytics staple for years and it provides the backbone to many systems that then connect to pull data since the system already exists, says Joseph Zulick, manager at MRO Electric and Supply.
It’s nice to look at all the shiny new objects and say, “if I was going to create a new system I would build it using XYZ.” The problem is that when you have to merge in the old with the new and access data from many existing systems throughout a company and more importantly, throughout a plant, you would have to reconsider going with that shiny new object and certainly consider that shiny workhorse in the corner.q
Why SQL? Well, SQL has become an industry standard for many reasons, it’s stable, it’s low maintenance, it’s cross-compatible with many different software programs and many use SQL as their go-to for mapping data. But why?
Historical database systems before SQL used various platforms, one of the most common was Access. It was a Microsoft product, it was fairly common, so lots of people had it available, lots of people had written report software related to using the existing tables of Access, and there were a lot of programmers that knew the language.
Here comes the but, it was fairly unstable as files grew larger, it wasn’t self-maintaining, where it knew files were getting too large and it would require a compact and repair, if you opened an active file there was a pretty good chance you would corrupt the file while the other software was writing to these tables.
If you were just using the system for static data, it was fine but many people needed a more dynamic system.
Enter SQL. While SQL didn’t start as a system for every platform in the world, it was designed for server language which meant it played very nice with other programs. Since it was designed for servers it needed to be stable, since it doesn’t do well to have your servers going down regularly, just because files were getting too large.
Language was a big part of the SQL value since servers needed to communicate with many programs, it worked well to transcribe between languages and pass along the data.
Programmers were abundant since so many programmers were needing to learn the language if you were going to use the server on your system. This extensive network and dedicated community provide a stable support group and deep community with years of experience.
SQL was developed in 1988 but didn’t get its legs until later closer to 2005 when data pages were checksummed for better error resiliency, and optimistic concurrency support had been added for better performance. Permissions and access control were made more granular and the query processor handled concurrent execution of queries in a more efficient way. Partitions on tables and indexes were supported natively, so scaling out a database onto a cluster was easier. SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.
In 2008 it gained a lot of features
SQL Server 2008 R2 includes several new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, Report Builder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control Point), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers.
Why is it still on top? It’s the database that is used most by web developers and it’s free for non-commercial users. It is great for companies that are on a budget, want lots of functionality, a good range of interfaces, and need something reliable yet flexible enough to work with other databases. It is second only to Oracle in the database arena.
It’s also the second most common programming language. Next to Java, SQL is the most popular programming language. This common language means many IT professionals are familiar with the language and a lot of programmers are versed in utilising its benefits. This also means it’s easier for programmers to manipulate in-to and out-of, SQL.
It has longevity. Not a lot of databases or languages can brag about their 46-year history. This is an incredibly long time for any program to last and flourish. Think about how many things in our lives that we rely on, but never think about? For most of us, we don’t think about electricity, we just plug in our devices and they work. We don’t think about the coal plant, the nuclear generator, the wind turbine, or the water turbine that generates the current; in-fact I would say, we rarely think about the electricity except when the utility bill comes due. This is how SQL is in programming… it just works.
If you’re like many of us who have suffered through inadequate systems it is incredible to know that it’s reliable, without loss, corruption, and failures.
Is it simple? Yes! It’s a simple system with ease of use and sharing of the data and the analytics. It’s history as a Relational Database Management System (RDBMS), makes it optimised for databases and tables. Indexing the tables and searching them with queries is where SQL shines.
Why is SQL important when designing a web system? Speed! Understanding how SQL is used and applied can impact your speed and CPU usage if you are not indexing tables properly it will impact the burden on your system, memory, processing power and time as well as the response to web searches, etc.
It has Cache power! When you are accessing the database this also impacts processing speed and how fast the response time is perceived. This is where a system like SQL can do very well by indexing the tables which speeds up the processes and reduces the strain on your system.
All of the above doesn’t necessarily make the language the most popular, there are certainly more popular languages but when you need to access databases and act upon the data you can consider SQL to be standing on the shoulders of giants!
Query? How do I find the data to analyse it and then use it to solve problems? Yes, Query? But how? Query, That’s right, what’s right? Query. This is the computer version of who’s on first.
Utilisation by other programs. Many programmers find it easier to access data through SQL instead of writing a program that wastes time just to access what you can already obtain through SQL. It’s much easier to just access a data location that delivers the data and shares it in a format you need.
SQL comes in many forms also.
SQL is not the end-all of databases, other systems use plain text or CSV files that do things better than SQL with those types of files. But SQL is outstanding in processing and combining data tables for our back end system.
Remember that in many cases the beauty of SQL is that you don’t need to reinvent the wheel. The program and the data already exist are easily accessible. This is a bigger advantage than many programmers would admit. Too often the reason Programmers choose to use another way to obtain the information outside of SQL is more about wanting to own the program than it is to keep it simple stupid (KISS method).
Don’t look for a reason to add code or to find a different way to obtain the same solution. The lines of code to obtain simple solutions are readily available and easily accessible.
If we look to the future using SQL it’s hard to know every option out there but the stability of SQL guarantees its use for the years to come.
The author is Joseph Zulick is a manager at MRO Electric and Supply.
Comment on this article below or via Twitter: @IoTNow_OR @jcIoTnow
