Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect

Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect
5 (100%) 2 vote[s]

Introduction

This tutorial includes information about SQL (DDL, DML) that I have gathered during my professional life. This is the minimum you need to know while working with databases. If there is a need to use complex SQL constructions, then usually I surf the MSDN library, which can be easily found on the internet. To my mind, it is very difficult to keep everything in your head and, by the way, there is no need for this. I recommend that you should know all the main constructions used in most relational databases such as Oracle, MySQL, and Firebird. Still, they may differ in data types. For example, to create objects (tables, constraints, indexes, etc.), you may simply use integrated development environment (IDE) to work with databases and there is no need in studying visual tools for a particular database type (MS SQL, Oracle, MySQL, Firebird, etc.). This is convenient because you can see the whole text, and you do not need to look through numerous tabs to create, for example, an index or a constraint. If you are constantly working with databases, creating, modifying, and especially rebuilding an object using scripts is much faster than in a visual mode. Besides, in my opinion, in the script mode (with due precision), it is easier to specify and control rules for naming objects. In addition, it is convenient to use scripts when you need to transfer database changes from a test database to a production database. Read More

SQL Data Discovery and Classification

SQL Data Discovery and Classification
5 (100%) 3 vote[s]

With the recent data protection laws and the implementation of GDPR last year, it is imperative for us to know the type of data our databases are storing. This will help us classify data based on the regulations and help us meet the data privacy standards. It is quite difficult for DBAs to know the type of data within a database. However, there is a new built-in tool within SQL Server Management Studio (SSMS) that can help us to easily discover the data within the database. With this tool, we will be able to classify the data and work on reporting any sensitive data.

Read More

Basic and Complex Uses of Not Equal Comparison Operator in T-SQL

Basic and Complex Uses of Not Equal Comparison Operator in T-SQL
3.7 (74.29%) 7 vote[s]

This article is focused on the T-SQL Not Equal comparison operator (<>) and its uses in basic to slightly complicated SQL scripting tasks.

The article also highlights the importance of understanding the correct use of Not Equal comparative operator with expressions.

The Not Equal comparative operator is a very common operator used in T-SQL, however, it is important to understand how to use it effectively in different scenarios.

Read More

T-SQL SET Operators Part 1: UNION and UNION ALL

T-SQL SET Operators Part 1: UNION and UNION ALL
3 (60%) 2 vote[s]

In SQL Server, we can combine the same type of data from multiple tables using SET operators. After combining multiple SQL statements, it returns one result set. Following is the list of T-SQL SET operators:

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. EXCEPT

To use SET operators, we must follow a number of rules:

  1. The result set of both queries must have the same number of columns.
  2. The data type of columns retrieved by the top and bottom queries must be the same.
  3. If we want to sort the final result set, the ORDER BY clause must be at the end of the query.
  4. The positional ordering of the columns returned by the top and bottom queries must be same. Read More

New Features in Visual Studio 2019

New Features in Visual Studio 2019
4.5 (90.48%) 21 vote[s]

Microsoft has recently released the preview version of Visual Studio 2019. Like all previous editions, the latest Visual Studio comes with lots of improvements and new features that are focused on faster execution, more productivity for developers and team collaboration.

Visual Studio 2019 Banner

If you haven’t downloaded it yet, you can do it here for free: Visual Studio 2019 preview. You can then easily install and run it alongside any other edition of Visual Studio – they won’t interfere with each other and this won’t require any considerable upgrades of your OS. You can also download Visual Studio 2019 RC (Release Candidate) from the official website.

Read More

Automatic gathering of SQL Server configuration by using PowerShell

Automatic gathering of SQL Server configuration by using PowerShell
3.5 (70%) 4 vote[s]

At present, the gathering of SQL Server configuration information is no longer a problem— the opportunities to add value through an update and to build a stable database environment exist permanently. Most of the third party tools provide the appropriate functionality in order to capture the software market. Through this guide, you’ll figure out the ways to capture few SQL Server configurations and manipulate the data by using PowerShell.

Moreover, you will get the answers to the following questions:

  • How to gather SQL Server configuration
  • How to automatically discover SQL instances
  • How to manually input the SQL Server instances to speed up the process
  • How to transform data by using PowerShell
  • And more…

Read More

SQL Server Database Tables Export and Import Methods

SQL Server Database Tables Export and Import Methods
4.1 (82.86%) 7 vote[s]

When working as a SQL Server database administrator or developer, you cannot live in your isolated SQL Server world without communicating with other data sources. For example, there is rarely a day when you won’t be requested to import data from an Excel, Access or CSV file into your SQL Server table. Or, conversely, export one of your SQL Server database tables to an external file in order for this table to be used in another database engine or to be analyzed externally by the corresponding team.

SQL Server provides us with a number of methods that can be used to export an existing SQL Server database table to an Excel or text file, and import data from an external data source to a new or existing SQL Server database table. In this article, we will gradually cover all these methods.

Read More

Using Trace Flag 3226 to Suppress Log Backup Logging

Using Trace Flag 3226 to Suppress Log Backup Logging
3 (60%) 1 vote[s]

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Read More

Column-level and row-level tracking in merge replication

Column-level and row-level tracking in merge replication
4.6 (91.11%) 9 vote[s]

In this article, let us review row-level and column-level tracking options in merge replication and how these are used in detecting conflicts during merge replication.

Merge Replication: Merge replication is used to replicate data in both ways i.e. from the publisher to the subscriber and from the subscriber to the publisher.

The initial snapshot of objects is taken and applied to subscribers. Incremental data changes and schema changes are tracked using triggers and applied to subscribers when the subscriber synchronizes with the publisher.

Read More

DBCC SHRINKFILE Overview

DBCC SHRINKFILE Overview
5 (100%) 4 vote[s]

Running DBCC Shrink commands is quite a controversial issue across the SQL Server community. In this article, we will review details about this command and provide a brief overview of its use and also warn you about the risks of running this command. As DBAs, a number of databases were handed over to from other teams or vendors, and it is not always we get to manage the databases which we created. As DBAs, whenever we are involved in migrations or new projects, we need to ensure that we carefully plan a smooth transition of the database to production and regular use. It is at this stage that we need to factor in the size of the database. Can you imagine, you set up a database application without considering the growth forecast for the first year or so. How about you create a SQL Server database with size so small that it needs to grow every other day raising capacity disk alerts in the middle of the night? It may sound silly, but in reality, the truth is this happens, and this sometimes may not be in your control.

Read More