SQL DROP TABLE Statement and Various Use Cases

Total: 1 Average: 5

The SQL DROP TABLE statement serves to drop the table from the database. It removes the table and its data and indexes associated with it. The statement is irreversible. Thus, you can restore the table only by restoring the backup.

This article covers the following cases:

  1. Delete one or multiple tables.
  2. Delete the table used in a View.
  3. Delete the table having a foreign key.
  4. Delete the table having clustered and non-clustered indexes.
  5. Delete the table from the replication setup.
Read More

Fundamentals of Managing Datafiles in SQL Server

Total: 1 Average: 5

Introduction

Datafiles are physical objects that constitute the most important part of the database system since they contain actual data. You can think of a database as a collection of data files. An instance gives you the means of mounting and accessing such files.

Here, managing datafiles is understanding how to monitor and resize existing datafiles and how to add or remove the data files from a database.

Read More

Use Cases for SQL Server MERGE Statement: Syncing Online and History Tables

Total: 1 Average: 5

INTRODUCTION

The SQL Server MERGE statement is an incredibly useful tool for carrying out DML operations based on comparing two tables or two data sets. Usage of this statement is actually like performing multiple operations in a single statement.

This article will explore three use cases that border on ensuring data between an online table and a history table in sync.

Read More

Five Different Methods To Start, Stop, And Restart SQL Server Services

Total: 1 Average: 5

When we install the SQL Server, it also installs the following services:

  1. SQL Server database engine service to manage and access data in SQL Server.
  2. SQL Server Integration Service for the ETL process – we install it when including Integration services during setup.
  3. SQL Server Reporting Service to manage the SQL Server reports.
  4. The SQL Server Agent Service. Note that it is not available in the SQL Server Express edition.

In this article, we are going to learn the following methods to manage SQL Server services:

  1. Start, stop, and restart SQL Server services using the SQL Server configuration manager.
  2. Start, stop, and restart SQL Server services using Services MMC (Microsoft Management Console).
  3. Start, stop, and restart SQL Server services using the PowerShell script.
  4. Start, stop, and restart SQL Server services in Ubuntu Linux.
  5. Start, stop, and restart SQL Server services using the Windows server failover cluster manager.

Let us examine all these methods.

Read More

Basics of SQL Server Management Studio (SSMS) – Part 1

Total: 1 Average: 5

This article aims to share the basics of SQL Server Management Studio, commonly known as SSMS, and some useful tips on working with it. Also, it highlights the importance of SSMS as database development and administration tool.

About SQL Server Management Studio

It is always good to clarify the SSMS definition(s) first. Let’s review some of the definitions from the database perspective.

Read More

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: 0 Average: 0

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

CodingSight - Make Awesome Lists Yourself, or GitHub as Notebook

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