That is why choosing a tech stack may take more time than you expect.

In this article, I will help you choose the most effective tech stack for web development.

I will explain what technologies are available for selection, their advantages and disadvantages, and inform you on what technologies are suitable for different projects. I will also provide valuable recommendations on how to choose the right technology stack.

What Is a Technology Stack?

The term “technological stack” refers to a complex combination of programming languages, software, and a range of frameworks used to develop an app. It should be chosen on the planning stage of the web development process.

The architecture of any web application consists of two sides: client side (frontend) and server side (backend). The client side is the visualized data that users can see on their displays. It includes the following components:

• programming language, responsible for the interactive part of the web application,
• a documentation markup language that displays the website’s content in a browser,
• style sheet language used for describing the document’s presentation,
• UI frameworks.

The server side is not visible to the user. It provides data for the client side. The development of the server side involves the use of the following technologies:

• databases,
• backend programming language,
• frameworks,
• web server (serverless architecture also can be an option),
• cloud infrastructures and services.

So, how can you choose wisely from all the available options?

Things to Consider When Choosing a Technology Stack for Web Development

As I explained in the introduction, choosing a tech stack is crucial for the project you’re working on. The time and cost required, as well as the overall quality of the final product, are directly affected by your choice. To choose wisely, consider the following recommendations.

Choose Familiar Technologies

Let’s say you want to create a simple web app with only a couple of functions. If you used a new but completely unfamiliar technology, you would inevitably waste way too much time fixing bugs and solving small issues—which you wouldn’t have faced if you had used simpler and more familiar technology.

In short, in such a scenario, a simple and understandable task turns into a nightmare and you no longer feel confident about your project.

So, if your software engineers are great at PHP development, there is no sense in using Node.js. Although you shouldn’t use outdated technologies—since they may lead to poor performance and technical support issues—you should still choose technologies that your development team knows inside and out, especially if you want to launch your web app as quickly as possible.

Follow the Trends

If you are not limited in terms of budget and can hire any team from the web development company, you can choose almost any technology stack.

Technology trends are constantly changing. Top languages ​​replace each other every 5-7 years, and frameworks every 2-3 years. It’s a good tactic to choose a modern web development stack, as it will help you avoid many problems in the future. According to Statista, the most commonly used programming languages in 2021 were JavaScript (64%), HTML/CSS (56%), and Python (48%).

It’s good to make sure that a technology of your choice is supported by a large community of developers that have an amazing career in software development. This way, in case of difficulties, you will quickly find specialists to help you fix them.

Overall, you should trust your developers. Ask them about the pros and cons of technologies that are potentially suitable for the development of your web application. The technical expertise of specialists will help minimize the risks in the future.

Consider the Specifics of Your Project

Choose a tech stack considering the size and the purpose of your web application. For example, for small, single-page websites, the Node.js and React.js stack will do the job. Web apps of medium size (like shopping websites, for example) require more complex tech stacks, several levels of programming languages, and several frameworks.

As for large projects, you will need a stack capable of working with significant amounts of data and maintaining the required level of web app’s performance.

Moreover, keep in mind the evolution of your app. Your web app will definitely require technical support and updates in the future. So, when choosing a tech stack, think about your plans for the app’s future development: Maybe you want to make it available on other devices? Or want to add some additional functions?

Ensure High Security Level

The security of the users’ personal data is of primary importance. If your service includes an online payment system, you should prefer technologies that provide maximum security and inaccessibility to cyber attacks when forming a stack.

Remember Your Deadlines

If your project has strict deadlines, it will be a wise decision to resort to ready-made solutions of the technology stack. This will decrease the overall web application development time.

LAMP, MERN, and MEAN are among the most sought-after models. They will work for various medium-to-large-scale web applications. Nevertheless, it doesn’t mean that they will definitely be suitable for any project—your personal project may require a more creative approach.

Choosing Front-End Tech Stack

Let’s now take a look at how you can optimally choose a tech stack for the front end, in terms of frameworks as well as programming languages.

Framework

The most common frontend frameworks for web development are React.js, Angular.js, and Vue.js.

React.js is a free and open-source front-end JavaScript library for building user interfaces. It was created by Facebook and has gathered a large community around itself in less than a decade. It’s used for developing simple web applications. It comes in handy when developing complex front-end logic.

This UI library allows for fast and low-cost development. That is why it’s more popular than, for example, Angular. Moreover, since this is a very common technology, it won’t be a difficult task to find a qualified React programmer.

This is a very popular UI library that is unlikely to go out of use in the next 10 years, due to the fact that it is supported by Facebook and is used by millions of developers.

AngularJS is the biggest competitor to React. This framework is perfect for complex, advanced web apps. If you want to build a web app on Angular, you will need to thoroughly plan the development before it even starts.

Development on Angular means higher-quality results, but it also requires more time and money. Plus, it’s very difficult to learn this framework—which makes it harder to find a developer who works with Angular.

It will be a win-win idea to choose Angular if you need a complex web app with a simple interface and high-quality code.

As for Vue.js, it is an open-source JavaScript framework, which can be easily integrated into projects using other JavaScript libraries. Compared to React and Angular, this is a relatively new technology. That is why it doesn’t have too much support and there are no guarantees that it will stay relevant for many years.

Despite all this, as practice shows, Vue.js is a very good low-budget, simple technology—meaning, you won’t need a skilled engineer. This is a great choice if you need to develop a small web application or MVP as quickly as possible and have a limited budget and small team.

Programming Language

When developing the client side of a web application, you need to decide on the main development language. Here we have two obvious options, JavaScript and TypeScript.

JavaScript is commonly used for creating dynamic and interactive web pages. It will allow you to create a beautiful and intuitive user interface. JS also enables complex animations that contribute a lot to the creation of a great user experience.

JavaScript is extremely popular and will probably keep its position for many years. Over 97% of websites are built with JavaScript on the client side. All major web browsers have a dedicated JavaScript engine.

TypeScript was created by Microsoft. If you are planning to develop your app using Angular, it’s better to choose TypeScript. However, when working with React and Vue.js, TypeScript is a bad choice: They just don’t integrate well, which may be a problem for developers that don’t have enough experience.

TypeScript allows you to save time on development and make your app simpler and more convenient for your clients. It is also supported by all modern frameworks.

Choosing Backend Technology Stack

And now it’s time to see your options regarding the backend technology stack.

Programming Language

There are many programming languages that can help you build the logic of your app. Let’s look at the three most popular of them:

Ruby is an interpreted, high-level programming language that supports multiple programming paradigms. It allows for programming productivity and simplicity.

PHP is a general-purpose scripting language. Currently, it’s supported by the vast majority of hosting providers, being one of the leading languages used to build dynamic websites.

Finally, Python is a high-level programming language, focused on improving the developer’s productivity, code readability, and quality. It works for both small and large-scale projects.

So, Ruby will work for those who need a simple programming language; PHP is great for building dynamic websites; Python is the best choice for large-scale projects.

Database

Although it’s possible to create a custom web application without a database, such an app will have limited functionality. The database is required if your app asks for contact details from the users. The most popular databases are the following:

PostgreSQL. It’s used for financial and scientific projects and can boast a powerful SQL engine, being able to effortlessly process large amounts of data.

MySQL. It can ensure high performance and scalability and is universal for most types of apps.

Redis. It’s a good choice when super-fast response time is needed. The high speed of this database is very useful for real-time apps.

The Technology Stack Is the Core of Your App

The technology stack has a very strong influence on the future of any web application. It affects the cost of development, development time, scalability and many other elements. Put simply, the technology stack is the core of your app.

Your choice of technologies should ultimately depend on your purpose, what you want to achieve.

Therefore, in order to choose the best tech stack for a web app, it’s necessary to familiarize yourself with the advantages and disadvantages of all available technologies, and then consult with experienced developers.

The post How To Choose a Technology Stack For Web Apps in 2022 appeared first on Simple Programmer.

The major features that are attracting an increasing number of developers toward NoSQL databases include:

• Capability to manage large volumes of data
• Performance
• Straightforward scalability
• Agile development
• Fail-over safety
• Great CPU and memory skills
• Faster time to market
• No database breakage

The two governing technologies in this arena are HBase and MongoDB. With the differences between the two being important, software architects are finding it difficult to select the right one for their projects.

Before we explore this further by comparing the two leading technologies, let’s take a glance at their individual capabilities.

What Is Apache HBase?

According to Wikipedia’s definition, “HBase is an open-source, non-relational distributed database modeled after Google’s Bigtable and written in Java.”

Apache HBase is best utilized when there is a necessity for random and real-time read/write approach to Big Data databases, and massive amounts of structured data. It provides Bigtable-like skills on top of HDFS and Hadoop—compression, in-memory operation, Bloom filters etc.

HBase is a scalable, column-dependent database for structured data that enables effective and precise management of enormous sets of data that are distributed among various servers.

As it is created in Java, it provides support for different APIs. It is mainly constructed to manage real-time queries in big tables that have many rows and columns and perform across a cluster of hardware. It works on a four-dimensional data model with utmost scalability and fault tolerance.

Key Features

• Linear scalability and modularity
• Aid in exporting metrics through Hadoop metrics subsystem
• Automated failover support and sharding of tables
• Effortlessly usable Java API for clients
• Regularity in reads and writes
• Data storage as key or values
• Best fit for range-based scan

There are many companies and organizations using HBase, such as Adobe, Hubspot, Vinted, JVM Stack, Flurry, Tumblr, Pinterest, Celer Technologies, Yahoo!, etc. For more information, consider HBase: The Definitive Guide: Random Access to Your Planet-Size Data.

What Is MongoDB?

As Wikipedia informs us, “MongoDB is a source-available, cross-platform, document-oriented database program. Classified as a NoSQL database program, MongoDB uses JSON-like documents with optional schemas.”

In more detail, MongoDB is a general purpose, document-based, distributed database created for modern-day application developers and for the cloud era. It is utilized by many developers to empower the world’s most advanced products and build highly scalable APIs.

It has the capacity to serve multiple Fortune 500 and global 500 organizations across different industry segments, like healthcare, education, eCommerce, and finance.

Written in C++, Go, Python, and JavaScript, it is quite productive, scalable, high-performance, and encompasses a single server deployment to complicated infrastructures. As a substitute to tables and rows, it includes documents and collections. It is considered ideal for real-time analytics and high-speed logging.

Key Features of MongoDB

• Decreased I/O overload and dynamic schema for easy data structures
• Replication, support for several storage engines
• Schema-less database, quicker query handling via indexes
• Horizontal scaling, distributed storage and superior availability
• Elasticity, real-time view of data, nested object structure
• Indexable array attributes, on-desk encryption in enterprise version

Among the many organizations using MongoDB, it’s worth mentioning Facebook, Adobe, Foursquare, Google, SAP, Shutterfly, Cisco, Forbes, eBay, and Paypal. For more information on MongoDB, consider reading MongoDB: The Definitive Guide: Powerful and Scalable Data Storage.

A Comprehensive Evaluation of MongoDB vs HBase

As we tend to compare the two databases, here are certain similarities that are observed in both:

• Possesses master-slave replication
• Supports sharding partitioning method
• Does not support foreign keys
• Durable, concurrent with in-memory capabilities

Here is a detailed comparison between the two:

Unique Options for Unique Demands

To each their own! Though there are distinctions between HBase and MongoDB—the two prominent NoSQL databases—both have their share of fame and trustworthiness. Organizations must assess their own factors in detail before selecting which one to take.

HBase is best fit to key-value workloads where there is a high volume of random read/write for organizations who have HDFS as their storage layer, especially because it provides very fast reads/writes.

If there is a broader range of application areas, MongoDB is the right choice since it offers an enriched model for tracking user behavior easily. HBase is scalable and performant for a chosen subset while MongoDB can be used for a wider variety of applications.

It is up to the user to select if they need performance-driven implementation or effective support for a variety of applications. In the world governed by Big Data and BI solutions, it is a difficult choice to make, especially when it is like choosing the better out of the best. Hopefully this post has made your decision simpler and easier!

The post HBase Vs MongoDB – A Thorough Comparison Between NoSQL Databases appeared first on Simple Programmer.

I take most websites for granted. I can see and navigate most websites without much struggle because I’ve grown up using the web and don’t struggle with my sight or hearing. Unfortunately, not everyone can say the same.

The post Simple Ways to Make Your Website ADA Compliant appeared first on Simple Programmer.

“RED 5, STANDING BY”

After millions of movie fans around the world anticipated the opening of Star Wars: The Rise of Skywalker, the third film of the third Star Wars trilogy, there’s one very important fact to remember: It all started with an overlooked security flaw.

You may remember when George Lucas first took us to “a galaxy far, far away,” an unprotected thermal exhaust port on the Death Star (the Empire’s planet-killing satellite) made Luke Skywalker a hero and put him on the path to Jedi knighthood.

Following Obi-wan Kenobi’s advice to “use the Force,” Luke fired the shot—into a hole in the Death Star’s surface just two meters across — about the size of a womp rat — that started the chain reaction that destroyed the Death Star and saved the Rebel Alliance from annihilation.

A simple action that ended in disaster (for the bad guys, of course!) should be a lesson to administrators, IT heads, and frontline database users everywhere. All it takes is one small gap, one unguarded vulnerability, to destroy everything you have worked to build. Awareness, scrutiny, and attention to detail will make your little world safe and secure— no matter how hard your enemies will try.

What Experts are Saying About Network Security

According to one expert, the first step in addressing the problem is rethinking how you see data.

“People need to understand that data doesn’t exist the way they think it exists,” says Andrés Arrieta, Director of Consumer Privacy Engineering for the Electronic Freedom Frontier (EFF).

Drawing on his career as a telecom engineer, Arrieta joined the San Francisco-based non-profit that has sought to enhance transparency and protect consumer privacy and free speech rights in the internet marketplace for nearly 30 years. Arrieta works to raise awareness of the structural failures that lead to privacy breaches and create tools to protect against them.

“When people access a data file, they think they get it from a database, and when they’re done with it, they put it back in the database and there it stays until they take it out again,” he says. “But that’s not how it works.”

Arrieta explains that databases don’t work in isolation. They are linked to other databases, which all communicate with each other. So while a particular file may have its “home” in one database, it can also “exist,” in a general sense, in every other database connected to its home.

The Drawer Example

To understand this phenomenon, imagine a room full of filing cabinets, numbered 1 through 100. The file you need is in the top drawer of Filing Cabinet 33. But when you open the top drawer of Filing Cabinet 33, you don’t just see the contents of that drawer or the contents of every drawer in Filing Cabinet 33; you see the contents of all of the drawers of all of the filing cabinets. That, Arrieta explains, is how database networking works.

What many people don’t understand – even many IT security experts – is to secure the files in the top drawer of Filing Cabinet 33, you have to secure every drawer in every filing cabinet in the room. That is, every access point in the database must be secure, or the entire network is at risk.

Arrieta describes a scenario he’s seen many times, where two networks are linked together, and one contains very sensitive private data, and the other does not. While the “sensitive” network may have high-level security at its entry points, the non-sensitive network does not. And even when the IT team is aware that the non-sensitive network is attached to the sensitive network, they may not be aware that the security of the one is tied to the security of the other.

“A hospital, for example, may have several networks,” Arrieta says. “One is for patient data, with medical history, test results, and private information. Another might be the hospital’s AC system and IoT controls. So the network that controls temperature and airflow around the hospital and links with beds and other equipment to monitor patient care is connected to the one that handles all of the private data. And the controls network maybe doesn’t have the same level of security.”

The lesson, Arrieta says, is that every network, and every network of networks, is only as secure as its least secure component.

The Dark Side of Networks

This scenario played out in real life, in the worst health care data breach of 2019. American Medical Collections Agency (“AMCA”) was a billing company that contracted with hospitals, clinics, doctors’ offices, and laboratories all over the United States. Between August 2018 and March 2019, it was the target of a hacking attack that struck not only AMCA, but many of its clients’ databases as well. In all, about 25 million patient files were breached from at least 21 companies, driving AMCA into bankruptcy and triggering congressional inquiries.

While it’s not clear where exactly the initial breach occurred – that is, at AMCA itself or through one of its client companies – Arrieta says the AMCA data breach case shows the real-world consequences of a security failure at one database affecting many, many others.

Don’t Take Your Safety For Granted

It serves as an important learning opportunity for security administrators everywhere. If you rely on the belief that “your” network is safe, you may find yourself becoming a victim anyway. A recent report issued the National Cyber Security Alliance, based on a Zogby Analytics survey of 1008 small businesses with up to 500 employees, found that after suffering a data breach 10% went out of business, 25% had to file for bankruptcy and 37% experienced a financial loss.

If your network is connected to any other network, your network is as vulnerable as the other network, and every network that network is connected to.

To maximize your protection, there are simple steps you can take:

• frequently reassess your security system
• regularly monitor its effectiveness
• update your hardware, firmware, and software to make sure it’s operating at its peak
• follow best-practices in authentication for database access, including randomizing usernames and passwords
• run systematic training of all staff to ensure compliance with security protocols and practices

Moreover, as Arrieta and the AMCA example tell us, you have to be ready to ask your collaborators and partners to do the same. Because your business’s security depends on their vigilance as much as yours, and the Dark Side is always waiting to exploit a weakness (even though in this example, it was the Light Side that did the exploiting!)

The post What Star Wars Can Teach Us About Network Security appeared first on Simple Programmer.

Developers depend on data and use it every day. Data drives the business and thus ultimately drives software. Understanding how it is stored will help you build better software. Developers don’t have to know everything, but they should know the basics.

Most databases are built using what is called the relational model. The relational model refers to the underlying theory of how to logically organize data within a database. Relational models focus on reducing data duplication and increasing reliability.

Let’s take a look at what the relational model is, why developers should care, and how it can work for them.

Getting Relational

The relational model features tables that hold data. The “relational” part of the name comes from how the records in the model are linked together.

Each table has relationships with other tables, usually to show ownership in some way. For example, an invoice has a relationship to a customer and to a set of products.

Let’s take a look at an analogy to help illustrate how relational databases work. Think of the plates and silverware in your kitchen. You have a place for your plates, bowls, and cups. You have your silverware divided with knives, spoons, and forks each in their assigned locations.

Then it’s time to have company over for dinner. You go to the cupboard and grab a plate. You take out a spoon, a knife, and a fork. You join them together to create a place setting. Then you do the same thing for each person and fill up the dining room table.

Relational models work the same way. You have various related entities stored in separate tables. The relations between them allow you to join records together in order to retrieve the complete “place setting,” so to speak. Going back to the invoice example, you can create the invoice by joining together the customer and the products they purchased.

The relational model gives you the tool to build a rich model of data for your application, based on the entities that support your business.

Why Developers Need to Know the Relational Model

You might be sitting there reading this post and saying “Yeah, so what?” While you may not be an expert in data modeling, you need to understand how databases work. It helps you to be a better developer.

In today’s DevOps world, you may need to become a “full-stack” developer, learning and using more skills than just developing application code. Some companies, especially startups, require developers who know how to write code and build databases.

Building a good relational model results in reducing the space required to hold data. Your data model also weighs heavily on the performance of your application. Poorly constructed data models lead to major performance issues for your application. Don’t let a lack of understanding cause major problems down the road.

When you know the relational model well and use it effectively, it helps your application to run more smoothly. You can also become better at building database queries that your code uses to retrieve data from the database.

Learning these skills will make you a better developer and a more marketable one. So let’s practice building an effective relational model.

Building a Model

Here, we’re going to build a relational model for invoices. Invoices are a good place to start because they are both simple and good at illustrating the definitive concepts in creating a good model.

At its heart, a relational model is all about reducing duplication of data. You keep one record that is the source of truth for an entity. Then you reference that record where you need it without writing the same information all over again. Let’s see how that works.

First, you create a table for the customers who will receive the invoice. You’ll need to know what fields you care about in relation to the customer. We’ll keep it simple and just have a name, email address, and phone number.

The Id field is necessary to uniquely identify each record. That way, if there are two John Smiths in the database, the computer will know which one you need when you request it.

You can’t have an invoice without products, so let’s create that table next.

The interesting part of modeling data with relational tables is building relationships between tables. However, we can’t simply add a relationship directly between customers and products. Why not?

A customer can buy many different products from you. The customer may buy a couple of products one day and then come back the next day and buy more products. Also, a single product can be bought by multiple customers.

In relational modeling, this is referred to as a many-to-many relationship. We need a table in between that will tell us how customers relate to products over time. That new table is the invoice table.

We also want to reduce the amount of space necessary to store the invoices. We don’t want to replicate the name, email address, and phone number of the customer every time they buy a product.

We can do this by using the power of referential integrity, which is the principle that an id can be used to refer to one and only one entity in a database. That id can then be stored in different places by itself and used to refer to a record without replicating the data within that record.

We’ll see what that looks like in a minute. Before that, let’s create our invoice table.

Here, we see referential integrity and reduction in data size all in one. For each invoice record, we have a CustomerId that holds a single customer’s identifier. We have the same for a product. Now, when we need to pull back an invoice for a customer, we can join the invoice record with the customer and product records to put together a complete picture.

This is similar to our kitchen example, in which we joined together a plate, knife, fork, and spoon to create a complete place setting. The entire model now looks like this:

Let’s say we have a customer named John Smith with an id of “1.” We have a product named “chainsaw” with an id of “5.” The invoice record will have its own id, a “1” in the CustomerId field, and a “5” in the ProductId field, along with the date of the transaction.

When we need to see an invoice, we can go to the customer table, look for the record with an id of “1,” and find John Smith’s information. The data can then be passed to the user interface for display.

Build Your Relationships

The invoice example is simple but illustrates the power of the relational model. Several smaller tables linked together are easier to understand. Data is not replicated unnecessarily. Data integrity is maintained with ease.

To help with your first model, here are some tips:

• Model what makes sense. Don’t over complicate your model.
• Think in terms of “entities” within your application. Users, products, and invoices are entities in the above example. Each entity should become a table.
• Store only the attributes that are necessary for the business to function. Large database tables will take up too much space and be hard to manage over time.

Data is the heart of software. In today’s world, software is the heart of business. Learn how to use the relational model effectively and every business will value your skills.

Now go out and build your next model!

The post The Relational Model for the Busy Developer appeared first on Simple Programmer.

It’s an exciting time to be a SQL Server developer for many reasons that are outside the scope of this article, but to concisely summarize them: SQL Server has not only become the number one RDBMS—it has also become more than an RDBMS.

With these impressive new editions of SQL Server in mind, you may find yourself wondering how to extract the most value from the features that you anticipate to be the most impactful to your overall productivity, particularly if you lack experience. If, on the other hand, you are not attending every webinar and skimming every article over new features, then don’t worry too much; plenty of companies are still using SQL Server 2008 R2, particularly those in heavily regulated industries like finance and healthcare.

I would caution anyone against focusing on any of SQL Server’s new features and functionality without first becoming proficient in (if not mastering) the essential skills.

This article explains why metadata is valuable, what metadata is, and then walks through two practical problems that are solved with Transact-SQL (T-SQL) code that references metadata. From querying catalog views to using metadata dynamically, you should walk away with knowledge that will make your SQL Server development skills significantly more valuable by reducing the amount of time and effort it takes to familiarize yourself with data and solve problems independently.

Regardless of your industry, company, or even current version of SQL Server that you are using, these three common skills that you can learn are highly transferable—even across relatively large jumps in software versions (e.g., from SQL Server 2008 R2 to 2014).

Three Essential SQL Server Skills for Developers

SQL is the first and most obvious skill that you need to be competent in. One of the primary reasons for learning this scripting language (besides the fact that it’s fun) is how transferable it is—even across other RDBMSs. Of course, I am talking about American National Standards Institute (ANSI) Standard SQL (SQL) syntax, not necessarily T-SQL, which is Microsoft’s dialect of SQL. Personally, I have also found that it is easier to learn new elements of SQL/T-SQL syntax than to adjust to new features on a graphical user interface. For the purposes of this article, I’ll focus on T-SQL based on the assumption that anyone reading this piece is some variation of a SQL Server developer.

PowerShell is the second skill. PowerShell is another scripting language that allows users to automate a variety of useful tasks, which often involve running SQL Server Reporting Services reports, scheduling jobs, and basically doing a lot of database administrator (DBA) work. What makes PowerShell even more attractive, however, is the fact that it is a replacement for the Windows DOS batch language (i.e., the batch language that you use in command prompt) that uses .NET objects and methods. Yet another reason for its value is the fact that, unlike T-SQL, PowerShell can automate tasks that span the Windows and SQL Server environments.

Besides these two rich scripting languages, there’s a third skill that would greatly benefit any SQL Server user who is well-versed in it, which is the use of metadata. Technically, understanding SQL Server metadata (for the purposes of this article, all references of “metadata” will imply “SQL Server” unless explicitly specified) is a subject to study and an opportunity to exercise and apply skills (i.e., memorizing relationships and learning T-SQL)—not really a skill in itself. For this reason, whenever I refer to “the use of metadata,” I mean, “how well a developer applies knowledge of metadata in T-SQL.”

I would argue, however, that metadata is also one of the most overlooked and underestimated topics within the developer community (while learning T-SQL is clearly not). Many introductory SQL Server or T-SQL books do not even discuss it until later chapters, if at all, and even then, in little detail.

Familiarizing yourself with SQL Server metadata is a considerably more valuable skill than most instructors seem to think, particularly for beginners, because it is a practical means of applying knowledge in theoretical concepts within the SQL language, database design, and both physical and logical processing.

Even for more experienced developers and DBAs, SQL Server metadata can be extremely valuable, because its utility scales with your creativity and competence in other areas of database design and programming. Throughout the article, I will provide examples of T-SQL scripts that increase in complexity and demonstrate how familiarizing yourself with metadata can prove invaluable when trying to solve problems.

Before I dive into the examples, however, I should make a couple of important general points. Microsoft’s website, commonly referred to as the “Books Online” (BOL), is the single greatest resource that I can recommend on this topic. In fact, you should view this page to familiarize yourself with the various types of metadata and this page on how you should access the metadata (i.e., use catalog views).

Basic Metadata Queries

The simplicity and flexibility of querying object catalog views enables even users with minimal SQL knowledge to explore objects and relationships in a database remarkably well. Allow me to demonstrate why metadata is useful to developers with a quick example.

For those interested in following along, please note that I am using SQL Server 2016 Express Edition and the AdventureWorks2014 sample database (both are completely free).

Pretend that you are a new employee of the fictional company, Adventure Works Cycles. After looking at a few tables, you notice that a column called “BusinessEntityId” appears quite a bit. Wouldn’t it be nice to have a query display every column with that name in the database? Understanding the basics about SQL Server metadata makes that easy.

Since you are aware of [sys].[all_objects], [sys].[schemas], and [sys].[all_columns], you can write a simple query to achieve that single view of BusinessEntityId.

Here is the result set:

Metadata is for more than just writing basic ad hoc queries. Consider the opportunities to make incredibly complex queries to answer extremely difficult or time-consuming questions. For example, how many duplicate indexes exist in a given database? What types of indexes are they?

Familiarity with metadata, especially via catalog views and dynamic management views (DMVs), is extraordinarily valuable regardless of your current T-SQL skill level. It is a fun and accessible mechanism for honing your knowledge of both the T-SQL language and the company’s master data that scales with your rising competency in database programming.

Now follow the comments in the remaining queries that demonstrate how exploring the master data with metadata (combined with a bit of business knowledge) can help you independently answer questions.

There’s a lot that you can learn independently about a company’s data if you know your way around SQL Server metadata.

Advanced Application of Metadata

But what about the more advanced application of metadata? What if you are an experienced developer who has worked at a company for years? Why should you learn about SQL Server metadata? Well, a more complicated example may convince you.

In one of Grant Fritchey’s presentations at a PASS local user group event, he described 10 tips on how to improve SQL Server performance. One of them was to look for nested views and rewrite them (by joining to tables, ideally). By “nested,” I mean the views are created referencing other views in their definition. The more levels of nesting within a given view definition, the greater the performance will decline.

The obvious solution is to not write nested views, but even that is no excuse for avoiding their hypothetical existence, because by doing so, you are handicapping your performance tuning proficiency and relying on the assumption that it will not become a problem in the future. Moreover, if you are investigating database performance problems and are unsure about whether or not nested views plague your database, then it may be worth your time to at least take a look to verify whether or not this issue is one that you must untangle or not.

But how could you go about doing this? Besides manually right-clicking every view in the object explorer and reviewing the definitions, why not create a metadata stored procedure that utilizes dynamic SQL to give you the answer?

I wrote two stored procedures, which are referenced later in the article, that help get you started on solving this problem. There happens to be a system function called “sys.dm_sql_referenced_entities,” which accepts two input parameters: a qualified view name (i.e., “schema.view” or “[schema].[view]”) and a “referencing class.”

For the purposes of this article, just know that we are interested only in database objects, which means that we need to use the string “object” in the second parameter. In case you were wondering, you can view triggers’ references if you use a different referencing class. For more information, see this link.

Now that I’ve mentioned “dynamic sql,” I should address two categories of possible concern: security and performance.

Dynamic SQL Costs: Security and Performance

Dynamic SQL is basically “SQL that writes SQL.” While it is incredibly useful in stored procedures, it comes with some costs. Before elaborating on these costs, however, I must state that they are negligible compared to the long-term effects that nested views could have on a database.

I am well aware that SQL-injection is a serious security risk that becomes possible when developers write dynamic SQL. Fortunately for me, the “parent” stored procedure does not accept user input and is not intended for use in any customer-facing applications. More specifically, the dynamic SQL does not accept user input from the front end of an application to obtain values for its parameters.

If, on the other hand, your concern is the performance of dynamic SQL, then I have two responses for you:

First and foremost, the purpose of this “nested views” exercise is to increase the overall performance of a database by solving a potentially serious problem, and one that should occur only very infrequently (that is, unless you have a team of developers who continue to nest views on a regular basis, in which case, you have a much bigger problem).

Since the problem (in theory) occurs infrequently, then you should desire to run the code only infrequently, which means the poor performance of the code will only be of concern the few times you run it. In other words, you’re completely missing the context of the problem if you are focused on the performance of these procedures at the expense of the performance of the entire database, so don’t think too critically about the performance of this code (but feel free to tune it more if you can).

Secondly, you may also be concerned that performance suffers because of the very non-relational nature of dynamic SQL. I strongly agree with the notion that anyone writing SQL should strive to do so relationally (i.e., by writing in a manner compliant with the principles of set theory), whenever possible. Unfortunately, there is no alternative approach to solving this problem that complies with the relational model any more than this one. If you disagree, or have found any way to improve my code by making it more relational, then please contact me immediately. I should also mention that I have written an entire article on this subject.

To quickly summarize the criticisms: Security risks and performance concerns are negligible compared to the long-term and cumulative, performance-degrading effects that nested views may have on a growing database. The code itself may not be optimized for scalability and performance, but it will, if used properly, help you ensure that your databases are.

Making Your Metadata Dynamic

So, is dynamic SQL worth these risks? The best answer I can give you is that it depends on the value of the problem that you are trying to solve. Dynamic SQL is an additional tool in the SQL developer’s tool belt that dramatically increases the number of ways to solve problems. The first step in automating this nested view scavenger hunt is to write a dynamic SQL statement using sys.dm_sql_referenced_entities (I’ll use “referenced entities” for the sake of brevity) to return the names of all referenced views and the frequency of references:

[dbo].[CountObjectReferences]

[dbo].[FindNestedViews_v3]

Between dynamic SQL and cursors, there are some features of T-SQL that are simply unavoidable parts of this solution. As far as I’m aware, the only way to make this idea work is by using dynamic SQL to execute the referenced entities system function.

Additionally, the only way to run dynamic SQL multiple times is by using a cursor (unless you want to try something with extended stored procedures, but that is outside the scope of this article). Besides dynamic SQL and cursors, you are left with a few important design decisions.

Once you have a stored procedure that executes the dynamic SQL statement that passes in the database, schema, and view names, you might want to slow down and think about the design—specifically, by answering the design question: “Do I want to break this into another stored procedure and call it, or encapsulate all logic inside one giant stored procedure?”

The fact that I’ve contained the dynamic SQL inside a separate stored procedure, rather than include it as the first part of one huge stored procedure, was a deliberate design decision on my part. At the time, I thought it would be easier to read and maintain. Moreover, I wanted to ensure that the execution plan for the dynamic SQL was consistent (one of the advantages of stored procedures is preventing the optimizer from occasionally generating different execution plans). I also found that it was easier to write and test.

Deciding how to store the qualified views, pass them to the [dbo].[CountObjectReferences] stored procedure, store the results of the cursor, and then display the final output is one of the more difficult parts of this problem. We can use table variables, temporary tables, user-defined tables, or views.

How ironic would it be if you used a nested view in this stored procedure? Technically, it would be ironic only if the database you wrote the stored procedure in did not have any nested views except the one in the procedure. Now that is irony!

I opted for temporary tables because I am not as familiar with table variables; I don’t want to maintain a user-defined table as part of this process, and there’s no security concern preventing me from accessing the data directly (thus ruling out views). The ability to add indexes later and easily change the scope of the temporary tables between local and global are also appealing characteristics that affected my initial decision.

I did not clarify from the start whether I wanted a more detailed result set—which provides the user with as much relevant metadata as possible—or include the bare minimum amount of data in exchange for increased performance, maintainability, and simplicity.

The latter turned out to be my preference after reflecting on the original problem and thinking that I want to be able to run these stored procedures on an ad hoc basis and I only need a simple result set in order to find the nested views. Basically, you want to return the smallest amount of information possible to answer your question. In our case, that means returning all of the view names that contain other views and, ideally, how many levels of nested views exist between the original view and the table.

Before moving on, I must point out that I knew using a cursor would limit how scalable this approach would be. On the other hand, nesting views in a database isn’t exactly a scalable approach to database design either, so please keep that in mind too.

Key Points for Further Consideration

These stored procedures would not have been possible if I had not known about [sys].[views] or the referenced entities function. In fact, I originally joined [sys].[all_objects] on [sys].[schemas] and [sys].[all_columns], which performed worse than the version referenced in this article. It is also important to point out the security concerns behind metadata privileges and dynamic SQL.

Since security policies vary depending on the size of an organization and by its industry, whenever taking a job that involves SQL Server development, use these factors to keep your expectations within the same ballpark as the DBA(s) you will be working with. For more information on SQL Server metadata security, check out this article by Kalen Delaney. In fact, I would also suggest reading more from Delaney on the subject of SQL Server metadata.

Secondly, metadata access requires approval from your DBA. While there is a small security risk in allowing any users access to system metadata, it’s really a matter of how much your DBA or company trusts developers. Unless you work in a highly regulated industry, it’s unlikely that this will be a problem for you.

Exploring Other Uses for Metadata

When using the term metadata, I have been specifically focused on system metadata. I should also point out the usefulness of DMVs, since they are heavily used and relied upon among DBAs, and suggest that any developer should be familiar with all of the above information.

What I’ve found most challenging is finding the correct DMV or system metadata quickly—a problem that would certainly diminish as I take my own advice from the previous paragraph. On that note, I encourage anyone who experiences the same problem to use my first example and modify it based on what you are looking for (i.e., modify it to look for DMVs or system views of interest based on keyword searches).

With additional practice, metadata and DMVs will become incredibly valuable to you by maximizing your ability to solve problems in SQL Server without any assistance from third-party software. Even better still, much of your code that relies on SQL Server metadata will still function in Microsoft Azure, making the application of metadata an even more transferable skill.

Considering the chaos of rising and falling technologies, transferable skills are becoming increasingly harder to identify and rely on, which makes developers’ lives (at times) unnecessarily difficult. Thus, the value of SQL Server’s metadata is a testament to Microsoft’s dedication to user empowerment, which is as sure an indication as any that they are creating products with you, the developer, in mind.

The post Microsoft SQL Server Metadata For Developers appeared first on Simple Programmer.

In this article, I will describe why NoSQL databases were created in the first place, what problems they solve, and why suddenly we need to have so many different databases.

If you are new to NoSQL, you may be especially interested in the last part of the article where I list what NoSQL databases you should learn first to get a 360-degree view of this area.

Why do we suddenly need a new database?

According to some predictions, in 2018 humanity as a whole will generate 50,000 GB of data per second. This is a tremendous amount of data, and storing and processing it is a serious engineering challenge. What is even scarier is this number is constantly growing.

Relational databases are ill-equipped to work with these amounts of data. They are designed to run a single machine and if you need to handle more requests, you have only one option: Buy a bigger computer with more memory and a better CPU. Unfortunately, there is a limit to how many requests a single machine can handle, and we need a different database technology that can run on multiple machines.

Now, some of you may scoff, and say that there are two widespread techniques of how you can use multiple machines if you use a relational database: replication and sharding. But they are not sufficient methods to handle challenges that we are facing.

Read replication is a technique in which every update to your database is propagated to other hosts that can handle only read requests. In this case, all changes are applied by a single host, called the leader, while all other hosts, called read replicas, maintain a copy of the data. A user can read from any machine, but can change data only through the leader host. This is a useful and very popular technique, but it allows only handling of more read requests and doesn’t solve the problem of handling the required amount of incoming data.

Sharding is another popular technique where you have many relational database instances, and every instance accepts reads and writes for a part of your data. If you store information about a customer in your database, with sharding, one machine may process all requests for customers whose names start with A, another will store all the data for customers whose names start with B, and so on.

While sharding allows you to write more data, managing a sharded database can be a nightmare. You have to balance data across machines and scale your cluster up and down when necessary. While it may look simple in theory, implementing it correctly is a major challenge.

Can we have a better relational database?

I hope that by now, you see that relational databases can’t handle the amount of data that we generate, but you may still be left wondering why someone can’t build a better relational database that can run on multiple machines. You might think that the technology is simply not there yet and we will soon enjoy a distributed relational database.

Unfortunately, though, this will never happen because it is mathematically impossible, and we can do nothing about it.

To understand why this is the case, it’s important to talk about the so-called CAP theorem. The CAP theorem, which was proven in 1999, states that any distributed database running on multiple machines can have these three properties:

• Consistency – If you write data into a system, you should be able to read it back immediately afterward. If our system is consistent, after you write new data, you cannot read old, overwritten data.
• Availability – A distributed system can always serve incoming requests and return a non-error reply.
• Partition tolerance – A database will continue to reply to read and write requests even if some hosts cannot temporarily communicate with each other. This temporary hiccup is called a network partition and it can be caused by a number of factors, from actual problems with an underlying network from a slow host to physical damage to network equipment.

All these features are obviously useful and we would like to have all three. No one in their right mind would want to get rid of, say, availability without getting anything in return. Unfortunately, the CAP theorem also states that we cannot achieve all three properties in one system.

It may be a bit tricky to understand, but here is how you can think about it. First, if we want to have a distributed database, it should support “partition tolerance.” It’s not negotiable. Partitions happen all the time, and our database should work despite them.

Now let’s see why we can’t have both consistency and availability at the same time. Imagine we have a simple database that runs on two machines: A and B. Any user of this database can write to any machine and then the copy is propagated to a second host.

Now imagine that temporarily, these machines can’t communicate with each other and machine B cannot send to and receive data from machine A. If during this time, machine B receives a read request from a client, it has two options:

• Return its local data even if this is not the latest data. In this case, it will select availability (return some data that may be stale).
• Return an error. In this case, it will select consistency; the client won’t see stale data, but they also won’t get any data at all.

Relational databases try to implement both “consistency” and “availability” properties, and hence they can’t work in a distributed environment. If one would try to implement all features of a relational database, in a distributed system it would be either impractical (enormous latencies even for common operations) or simply impossible.

NoSQL databases, on the other hand, prioritize scalability and performance. They usually don’t have such “basic” features such as joins and transactions and do have a different, maybe even limiting data model. All this allows for storing more data and processing more requests than ever before.

How does NoSQL combine consistency and availability in one database?

At this point, you may be under the impression that if you select a NoSQL database, it either always returns some stale data or returns errors if any minor hiccup occurs. In practice, availability and consistency are not binary options. There is a broad spectrum of options that you can choose from.

Relational databases do not have these parameters, but NoSQL databases give you this control to select how your query should be executed. In one way or another, they allow you to specify two parameters when you perform a read or write operation with a NoSQL database:

• W – how many machines in a cluster should acknowledge that they have stored your data when you perform a write. The more machines you write data to, the easier it is to read the latest data with the next read, but the more time will it take.
• R – from how many machines you want to read data. In a distributed system, it may take some time for data to propagate to all machines in the cluster, so some hosts can have the latest data, while some can still lag behind. The more machines you read data from, the higher your chances of reading the latest data.

Let’s get more practical. If you have five machines in your cluster and you decide to write data to only one machine and then read data from one random machine, you have an 80 percent chance that you will get stale data. On the other hand, you will use a minimum amount of resources and if you can temporary tolerate stale data, you can choose this option. In this case, the W parameter is equal to 1 and R is equal to 1 as well.

On the other hand, if you write data to all five machines in a NoSQL database, at once you can read data from any machine and it is guaranteed that you will get the latest data every single time. It will take longer to perform the same operation with more machines, but if it is important for you, you can do this. In this case, W=5 and R=5.

What are the minimal numbers of reads and writes we need to perform to have a consistent database? Here is a simple formula: R + W ≥ N + 1, where N is the number of machines in the cluster. It means that if we have five hosts, we can select either R=2 and W=4, R=3 and W=3, or R=4 and W=2. In this case, no matter what machines you write to, you will always read from at least one machine that has the latest data.

Other databases such as DynamoDB have other constraints and allow only consistent writes. Every data item is stored on three hosts, and when you write any data, it is written to two machines out of three. But if you read data, you can select one of the following:

• Strongly consistent read that will read data from two machines out of three and should always return the latest written data.
• Eventually consistent read that will pick one machine at random and read data from it. This, however, can temporarily return stale data.

Why are there so many NoSQL databases?

If you been following software development news, you’ve heard about many different NoSQL databases such as MongoDB, DynamoDB, Cassandra, Redis, and many more. You may now wonder why we need so many different NoSQL databases. The reason is that different NoSQL databases focus on solving different problems and that’s why we have such a large number of competitors. The taxonomy of NoSQL databases contains four main categories:

Document-oriented Databases

These databases allow storing of complex nested documents, while most relational databases support only one-dimensional rows. It can be useful in many cases, for example, if you want to store information about a user in your system and allow every user to have multiple addresses. Using a document-oriented database, you can simply store a complex object with an array of addresses in it, while a relational database forces you to create two tables: one for user information and another one for addresses.

Document-oriented databases allow you to bridge the gap between the object model and a data model of a database. Some relational databases such as PostgreSQL now also support document-oriented storage, but most relational databases still don’t have this feature.

Key-value Databases

Key-value databases usually implement the simplest NoSQL model. At their core, they provide you with a distributed hash table that allows you to write data for a specified key and read back data using this key.

Key-value databases are easily scalable and have a much lower latency than other databases.

Graph Databases

Many domains, such as social networks or information about movies and actors, can be represented as a graph. While you can represent a graph using a relational database, it is going to be hard and cumbersome. If you need to graph data, you can use specialized graph databases that can store information about a graph in a distributed cluster and allow you to implement graph algorithms efficiently.

Column-store Databases

The main difference between column-store databases and other types of databases is how they store data on disk. Relational databases create a file per table and store values for each row sequentially. Columnar databases create a file for each column in your tables.

This structure allows you to aggregate data and to run specific queries more efficiently, but you need to make sure that your data fit the constraints of these databases and they can be used efficiently.

Which one should we pick?

Picking a database is usually an agonizing endeavor, and with so many choices, it may seem like an impossible task. The good news is that you don’t need to pick just one.

Using another modern pattern called “microservices” instead of developing a single monolithic application that implements all features and has access to all data in your system, you can split that application into a set of independent services. Every service solves a narrowly defined task, and it should use only its own database that fits the task of this microservice perfectly.

How are we supposed to learn all of it?

With so many databases around, you may wonder how you are going to learn them all. The good news is that you don’t need to. There are just a few major NoSQL database types, and if you understand how they work, you will be able to learn other NoSQL databases much faster. Also, some NoSQL databases are used more often than others, so it’s better to concentrate your efforts on the most popular solutions.

Here is a list of the most commonly used NoSQL databases that I think you should take a look at:

• MongoDB – This seems to be the most popular NoSQL database on the market. If a company is not using a relational database as its main data store, it is probably using MongoDB. It is a versatile document store with good tooling support. It used to have a bad reputation in its early days because it was losing data in some cases, but it has become more stable and reliable since then. You can take a look at this course about MongoDB if you want to learn more.
• DynamoDB – If you are using Amazon Web Services (AWS), you should learn more about DynamoDB. It’s a rock-solid, scalable database that provides low latency, a rich feature set, and integration with many other AWS services. The best part is that you don’t need to deploy it yourself. In a few clicks, you can set up a scalable DynamoDB cluster capable of processing thousands of requests. If you are interested in it, you can take a look at this course.
• Neo4j – This is the most widespread graph database. It’s a scalable and stable solution that you can use if you want to embrace the graph data model. If you want to know more, you can start with this course.
• Redis – While other databases described here can be used to store the main data of your application, Redis is commonly used to implement a cache and store auxiliary data. In many cases, you may end up using one of the aforementioned databases in tandem with Redis. To learn more, you can take a look at this course.

Get Ready for 2018 with NoSQL

NoSQL databases is a vast and quickly developing field. They allow you to store and process more data than ever before, but it comes at a price.

These databases don’t have the same features that relational databases have and it may be hard to wrap your head around how to use them. However, once you’ve understood this, you will be able to use a scalable and distributed database that can handle a tremendous amount of read and write requests, which will be extremely important as we continue to produce more and more data.

The post Guide to NoSQL for Software Developers appeared first on Simple Programmer.

Stack Overflow conducts a yearly survey that asks silly questions about if you use tab versus spaces and such, but it also goes further to explore gender demographics in the software world and programming language popularity.

Stack Overflow makes the results of these yearly surveys publicly available; anyone can use the data for their own purposes, be it to back up a hypothesis or to make a visualization.

I decided to do something with the data for a project and the first issue I noticed was the file format. The data are presented in CSV format. Although CSV is one of the most common formats, together with Microsoft Excel spreadsheets, they provide almost zero flexibility to programmers. It’s tough to work with CSV files if you want to transform them into something readable.

That’s why I decided to take the files and transfer them into a database. Using SQL (a query language for T-SQL-based database systems), the data from the survey can be queried in a simple manner, allowing us to apply filters on the data set, make transformations on the fly, and remove the need of sharing files.

And that is why after around four hours of finding the correct tools, setting up the database, filtering, formatting, and lastly uploading the data, I was like this at about 4 a.m.:

Follow the White Rabbit.

I’m going to explain how I finally made it work, so you don’t have to go through the same frustrating process. I’ll go through the steps that I took to move the files from my desktop to the database using MySQL database as the target, since it’s one of the most common database systems.

Importing CSV into MySQL

I made a quick sketch in my mind:

• Download files
• Set up the database
• Find a way to turn CSV into SQL

The first two steps were trivial: The files were easily searchable on Google and free for download. I set up the database so that I had access to the files and the database, which they would soon call home.

I had been using Sequel Pro to access the database, but since it’s available only outside of the Mac ecosystem, I had to use an alternative to import CSV into MySQL.

The internet suggested Workbench has such abilities. I have never been very fond of Workbench (it crashes too often), but I gave it a go. Fifteen minutes later, 56 rows were inserted. The processing speed is terrible and with this pace, the uploading would have taken ages.

After that, I tried HeidiSQL and additional online tools that promise to import CSV into SQL, but they all failed one after the other due to the size of the files.

The above-mentioned programs suggest they can take a CSV file and put it in the database; however, they are horribly slow. Workbench is known for its slow speed while performing those actions.

My savior answers to the name of csvkit. It can generate a script for the database to create the schema of the database. I built a CREATE_TABLE.sql file using the following command:

Csvkit expects the files to be under UTF-8 format, which avoids the errors that can happen with some other formats. I translated the files to this format quickly by executing the following command on PowerShell:

Now it was time for some formatting.

I went through the CREATE_TABLE.sql files to do some curation. This process involved reading through the generated code to check if there were any obvious errors, and also to make the name of the table suit my programming style.

In addition, I had to merge a few columns to make them more readable while in the database.

Some examples of issues I needed to fix in the curation process were:

• The 2015 file had a two-row header
• Because the table columns were taken from the questions, I needed to shorten them, since MySQL supports a maximum column name of 64 characters
• The 2012 file referenced 2011 columns
• Column names were underscore separated

All fixes were manual and focused around reordering the contents of the original CSV files so that csvkit could process them properly.

After I finished editing, I executed the scripts toward the database and then I had a schema I could work with!

During the edit, I had to choose between writing a script to take care of the edits or incorporating them manually. I was pretty sure that it would have taken me longer to automate the process, because I was tired and not focused. Sure, the manual approach normally takes longer, but it’s less risky than writing a script because it’s a repetitive process. Besides, let’s face it—I would never need that script file again.

I went with the dummy manual approach, with fewer risks—yes, sometimes manual work is faster.

The survey result files before 2015 are quite messy (don’t expect much until further curation is done). They have order errors, the questions go over columns, and so on, so the files are difficult to understand unless manual work is done to fix them.

However, if you have been paying attention to Stack Overflow and then you spend around an hour going through the surveys, it is clear how the results affected their course of action. I would like to say “kudos” to Stack Overflow for listening to the community over the years.

Upload Time

Now that I had the schemas, also known as tables, it was time to insert the CSV. Luckily, MySQL has the ability to load data from files, so I used the following command to do the trick:

During the insertion, I ran into a lot of column size issues. When defining a schema, you must provide the maximum string size that can be inserted. But during the schema generation, the upload outputted 1037277 warnings. My assumption was that there was a calculation error from Workbench, because that number exceeded the total number of rows inserted.

I had neither the time nor the will to spend on those warnings, so I moved on. I was sure that all rows were inserted by checking the sums, and if some were truncated, I just had to accept it for the time being.

Below are some details about the warnings I got for each upload:

2011 (921KB) Records:2814 Deleted:0 Skipped:0 Warnings:39298 6.031 sec

2012 (2,469KB) Records:6244 Deleted:0 Skipped:0 Warnings:38787 17.234 sec

2013 (7,746KB) Records:9743 Deleted:0 Skipped:0 Warnings:58596 57.250 sec

2014 (5,986KB) Records:7644 Deleted:0 Skipped:0 Warnings:67869 42.734 sec

2015 (~27MB) Records:26086 Deleted:0 Skipped:0 Warnings:275917 204.156 sec

2016 (~68MB) Records:56030 Deleted:0 Skipped:0 Warnings:541168 498.578 sec

2017 (~91MB) Records:51392 Deleted:0 Skipped:0 Warnings:1037277 666.641 sec

As you can see, the number of warnings was incredibly high for every set. I manually checked a few database entry rows per set, just to make sure they were not all nonsense and could not see any truncation or other issues with the text.

There is no guaranteed way to check if all the data are correct, especially in this magnitude, unless you start a whole operation and a new article just to validate that the origin and the destination have the same content.

Recap of the Process

The whole process took around four hours. No regrets here; it was actually quite enjoyable.

For developers, you gain a lot of knowledge by doing tasks that fall outside your normal everyday work. There is no software architecture involved or design patterns. You have to search for the best tool for the job and not give up when you encounter obstacles that arise.

These might be tasks that you would hate your boss for assigning to you, but they are fun to take on during your own time. And now, if anyone asks, you know how to put CSV into MySQL.

If you’re looking for all of the data assets I mentioned, you can find them on DataCircle or check out Stack Overflow Survey Data Raw for the raw data itself.

The post Adding Stack Overflow Survey Results into MySQL appeared first on Simple Programmer.

This course was definitely a difficult one to produce.  There is so much to cover in Hibernate, and there was no way I could cover it all.

Here is the course description:

In the world of Java one of the most popular and widely used frameworks is Hibernate.

Hibernate is an ORM or Object Relational Mapper that allows developers to map Java objects to relational database tables. It is a valuable tool that all Java developers should know how to use.The problem is, learning Hibernate can be difficult. Hibernate is a very large framework and there are many important concepts to understand to use it effectively.

Are you a developer who…

• has been wanting to learn how to use Hibernate
• struggles to use the application
• has never really understood Hibernate

This course is designed to give you the knowledge you need to feel confident about how Hibernate works and how to use it.

What this course offers:

This course is designed to make getting started with Hibernate as easy as possible and focus on the most important things you need to know. John starts off this course by teaching you a bit about Hibernate and how it works.

Then you will see how to get it setup with a real MySQL database installation. After that you will learn the very basics of mapping, which is one of the most important things to understand about Hibernate.

John will show you how to create a basic mapping for a Java object to a relational table and explain to you how the mapping works. He also covers some of the complex mapping situations like mapping collections and different relational mappings like one-to-many and many-to-one.

Once you’ve learned how to map you objects, you’ll want to know how to query them, so he’ll show you how to do this using Hibernate’s built-in HQL and using a more object-oriented approach with the Criteria API.

Finally, he wraps things up by taking a brief tour of some of the more advanced features like caching and interceptors.

The post Introduction To Hibernate appeared first on Simple Programmer.

How CROSS APPLY works

\n\nThe basic idea behind CROSS APPLY is to allow you to join two sets of data together.\n\nIf you understand how INNER JOIN works, you already understand CROSS APPLY.\n\nThe only difference is CROSS APPLY also allows you to join in a set of data in which that set of data is created or dependent on each row in the first set.\n\nSo basically what that means is that for a normal join you would, for example, join two tables that shared a common key.\n\nI could join my customer table to my orders table to see all the orders for a particular customer like so:\n\n

\n\nNotice how the join is operating on a key that exists independently in each table.\n\nWe could rewrite this to be exactly the same using the CROSS APPLY syntax instead like so:\n\n

\n\nWe can prove these results sets are exactly the same by using EXCEPT to make sure there are no rows in one set that aren’t in the other and then flipping it, like so:\n\n

\n\nJust run this exact query again swapping the SQL above the EXCEPT with the SQL below and make sure it has no results as well.  If both of those queries have no results, then you know the results from each query are the same since EXCEPT will show any results that are in the top query but not in the bottom one.\n\n

So when is CROSS APPLY useful?

\n\nRemember how I said it can do more than a simple join?  Joins are restricted to only joining two sets of data that could be queries independently of each other.\n\nWhat if I said give me the three most recent orders for each customer?\n\nTake a minute and think about how you would write that query.  Go ahead and try to do it.  I’ll wait.\n\nThere are a few ways to do it without using CROSS APPLY, but none of them are really very easy or perform very well.\n\nUsing CROSS APPLY it is simple though:\n\n

\n\nSo CROSS APPLY is useful whenever you have some data that you would want to be able to join against, but are forced to do some kind of sub-query instead because the data you are trying to join is not going to map well against a single key.\n\nThe other instance in which CROSS APPLY will be useful is when you are  doing a sub-select that has more than one value you would like to use in your final query.\n\nFor example if you were sub-selecting from an Order Details table to match up order ids that had a Quantity greater than 5, that sub-select would need to return exactly one column in order for you to use it in your where clause.  If you wanted to use other columns from the sub-select, you would have to do another sub-select for each of these columns.\n\nIf you first try to rewrite the sub-select as a JOIN and find that you can’t, you may be able to write it as a CROSS APPLY.\n\n

How to know when to use CROSS APPLY

\n\nThere isn’t a good solid rule you can use to identify when you should use a CROSS APPLY but having the knowledge of CROSS APPLY and how it works can help you when you are trying to tune queries and you are having a difficult time constructing one.  It is another option you can try.\n\nHere are some general guidelines of times when you might want to use CROSS APPLY:\n\n

\n
• A query where the result set you want to JOIN against is in some way related to the data in the first set.  (Example: one column in the first table tells you how many rows in the 2nd table to get)

\n

• A query where you are doing a sub-query, but need more than one value from the sub-query

\n

• Anywhere you are using a Common Table Expression (CTE) could possibly be rewritten as a CROSS APPLY

\n

• A query that has a large set of data it is joining against and then filtering out.  You can change it to a CROSS APPLY that does the filter in the CROSS APPLY statement.

\n

• Any time you are trying to join against a table function (this is actually what CROSS APPLY was created for.)

\n

The post Add APPLY to Your TSQL Tool Belt appeared first on Simple Programmer.