Manage MDF Files in SQL Server 2019

Total: 1 Average: 5

An SQL Server database contains primary data files, secondary data files (optional), and transaction log files.

The primary and secondary datafiles contain tables, database objects, schema, and data.

The file extension of the primary database file is *.mdf, and the file extension of the secondary data file is *.ndf.

The transaction log files store all the changes made by the transactions (insert, update, and delete). If the SQL Server restarts unexpectedly or crashes, the database engine rolls the incomplete transaction back before the point of failure using the Transaction log file.

The extension of the transaction log file is *.ldf. You might want to refer to this article to understand the Transaction Log Files architecture.

In this article, I am going to explain how we can manage the database files (MDF files) in SQL Server 2019.

Read More

Make Awesome Lists Yourself, or GitHub as Notebook

Total: 1 Average: 5

This material was originally posted on Habr.com in Russian. The author permitted it to be translated and published on Codingsight.

Perhaps, everyone has somewhere a file to keep certain exciting and useful things, like, links to articles, books, repositories, manuals, etc. You might use bookmarks in a browser or tabs, still open and waiting for you. Over time, it all grows excessively, links become broken, and materials get obsolete.

But what if we could share these insightful gems with the community? Upload a file to GitHub? Then, your findings would be useful for someone else. Besides, it would be easier to keep the file up-to-date together, getting the list updated via the good old pull requests.

This is what we have the Awesome Lists project for.

Read More

Everything You Need to Know About SQL CTE in One Spot

Total: 1 Average: 5

The first time Karl heard of SQL Server CTE was when he was looking for something to make his SQL code easier for the eye. It’s kind of a headache when you look at it. Anton, his concerned colleague, asked him about CTE. Karl thought Anton was referring to his headache. Maybe he heard it all wrong, so he answered, “Of course not.” The funny thing is, he was referring to Chronic Traumatic Encephalopathy, also a CTE – a neurodegenerative disease caused by repeated head injuries. But based on Karl’s response, Anton knew for sure that his colleague was clueless about what he was saying.

What a crazy way to introduce CTEs! So, before you get into the same boat, let’s clarify, what is SQL CTE or Common Table Expressions in the SQL world?

You can read the basics here. Meanwhile, we’ll learn a bit more about what happened in this unusual story.

Read More

Your Ultimate Guide to SQL Join: CROSS JOIN – Part 3

Total: 1 Average: 5

CROSS JOIN is in the spotlight. This article finishes our small series of SQL JOIN-related publications. If you missed the previous two articles, refer to them as follows:  

SQL Server CROSS JOIN is the simplest of all joins. It implements a combination of 2 tables without a join condition. If you have 5 rows in one table and 3 rows in another, you get 15 combinations. Another definition is a Cartesian Product.

Now, why would you want to combine tables without a join condition? Hang on a bit because we are getting there. First, let’s refer to the syntax.

Read More

Add Columns to an Existing Table in SQL Server Database

Total: 1 Average: 5

Introduction

A table is a two-dimensional logical structure and the fundamental means of storing data in relational database management systems. Its ‘row and column’ format is very much like the organization of the spreadsheet. 

Each new record introduced to a table is a row (also called a record or tuple), while rows are grouped into a finite set of columns (also called fields or attributes). Each column has a name and data type, and it serves as a reference for displaying result sets when querying data from relational database systems.

In this article, we explore the current options letting us add new columns to an existing table in SQL Server database. The use case may arise on many occasions. Particularly, it happens when there are updates for an application, and they demand adding new columns.

Read More

How to Install Ubuntu 18.04 and SQL Server 2019 on Linux

Total: 1 Average: 5

The previous article explained the creation of a virtual machine using Oracle VM Virtual machine. Now, we will learn how to install Ubuntu 18.04 and SQL Server 2019 on Linux.

Install Ubuntu Linux

First, we need an operating system ISO file. Once the file is downloaded, we mount it on a virtual disk drive:

Launch Oracle VM Virtual Machine Manager and right-click on LinuxSQL01. Then click Settings.

Read More

Handling the NULL Values Effectively with the SQL COALESCE Function for Beginners

Total: 1 Average: 5

This article aims to help beginners understand the basics of the T-SQL COALESCE function and its application for handling NULL values. Additionally, the readers will get hands-on experience by implementing some simple examples of this function.

We’ll also highlight the importance of T-SQL functions in resolving database analytical problems.

Read More

Сreating a Virtual Machine With Oracle VM Virtual Box

Total: 1 Average: 5

Microsoft announced cross-platform support of SQL Server – the SQL Server 2017 and SQL Server 2019 work on Microsoft Windows and Linux. We can install SQL Server 2017 and higher on Red Hat Enterprise Linux, Ubuntu, and SUSE Enterprise Server. We can also set up high availability and disaster recovery mechanism for SQL Server on Linux like SQL Server Log shipping and AlwaysOn Availability Group.

Read More