Database development

Aveek Das
Export Data Tier Applications in SQL Server Management Studio

An introduction to Data-Tier applications in SQL Server

April 29, 2020 by

In this article, I’m going to introduce the data-tier applications in SQL Server. As the official documentation from Microsoft says – “Data-tier applications in SQL Server are a logical entity that can be used to develop and manage most of the SQL Server objects like tables, views, stored procedures, functions etc. as a self-contained package“. Essentially, what that means is it is a component of SQL Server, using which we can develop, build, test and deploy databases for SQL Server just like we can do for any other web or desktop applications.

Read more »
Esat Erkec
What is pagination in SQL Server?

Pagination in SQL Server

April 14, 2020 by

Pagination is a process that is used to divide a large data into smaller discrete pages, and this process is also known as paging. Pagination is commonly used by web applications and can be seen on Google. When we search for something on Google, it shows the results on the separated page; this is the main idea of the pagination.

Read more »
Frank Solomon
Setting the target Directory

How to update the T-SQL Toolbox database

April 6, 2020 by

Introduction

In an earlier article, Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database, I described T-SQL Toolbox, a free, open-source SQL Server database that handles time zone, date, and time calculations in a clean and efficient way. Available here at the CodePlex Archive, and here at GitLab, T-SQL Toolbox relies on time zone and time zone adjustment data in its two tables for many of its own calculations. However, T-SQL Toolbox does not update that data. I built primitive C-Sharp and VB.net applications that extract the latest time zone and time zone adjustment data from the Windows registry, and I discussed those applications in that earlier SQL Shack article. In the article, I explained that we can build SQL Server UPDATE statements with this extracted data, and then update the T-SQL Toolbox tables. This article describes a better approach and a better solution.

Read more »
Aveek Das
Pivot Table Example

Dynamic Pivot Tables in SQL Server

April 2, 2020 by

In this article, I am going to explain how we can create a dynamic pivot table in SQL Server. Pivot tables are a piece of summarized information that is generated from a large underlying dataset. It is generally used to report on specific dimensions from the vast datasets. Essentially, the user can convert rows into columns. This gives the users the ability to transpose columns from a SQL Server table easily and create reports as per the requirements.

Read more »
Aveek Das
Default Masking

Understanding Dynamic Data Masking in SQL Server

April 2, 2020 by

In this article, I’m going to explain the Dynamic Data Masking feature in SQL Server. As it goes by the name, Dynamic Data Masking is one of the security features that was introduced in SQL Server 2016. In an ever-growing digital era, the security of ones’ data has become one of the most important and expensive deals. Everyone expects their data to be protected as their own asset and like to treat it with the highest efficiency possible.

Read more »
Aveek Das
Selecting Data

Introduction to Row-Level Security in SQL Server

March 31, 2020 by

In this article, I’m going to discuss Row-Level Security in SQL Server. RLS or Row-Level Security as the name suggests is a security mechanism that restricts the records from a SQL Server table based on the authorization context of the current user that is logged in. This means the records from the tables are displayed based on who the user is and to which records do the user has access to. This is usually done to allow specific users to have access to their data only without permission to view other users’ data.

Read more »
Esat Erkec
Listing temporary tables in tempdb database

How to drop temp tables in SQL Server

March 23, 2020 by

Temporary tables, also known as temp tables, are widely used by the database administrators and developers. However, it may be necessary to drop the temp table before creating it. It is a common practice to check whether the temporary table exists or not exists. So, we can eliminate the “There is already an object named ‘#temptablename’ in the database” error during the temporary table creation.

Read more »
Jignesh Raiyani
SQL Server Auto Shrink option

SQL Server Auto Shrink database property

March 5, 2020 by

In this article, we will discuss the SQL Server Auto Shrink database property, which allows SQL Server to automatically shrink the database files when the value is configured as True in the database option. Automatic shrink operation is done by the server auto shrink database feature, which is the main course of this article.

Read more »
Esat Erkec

Introduction to the sp_executesql stored procedure with examples

January 9, 2020 by

The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes. For example, when we want to determine the displayed columns in our reports, this procedure might be a solution option for us. In the simplest sense, this procedure takes a dynamically constructed SQL batch and other parameters, then execute it in the runtime and, finally, it returns the result.

Read more »
Nisarg Upadhyay
Database install script

Install and configure the AdventureWorks2016 sample database

January 9, 2020 by

This article explains the process of installing the AdventureWorks2016 and AdventureWorksDW2016 sample database on a stand-alone instance of SQL Server and Azure SQL Server. The sample databases were published by Microsoft to demonstrate how to design a database using SQL Server. Microsoft has also published another lightweight database named AdventureworksLT, which can be used as a sample database on Azure SQL Server.

Read more »
Esat Erkec

An overview of the STRING_AGG function in SQL

January 3, 2020 by

In this article, we will explore the STRING_AGG function in SQL and we will also reinforce our learning with various examples. STRING_AGG function can be counted as a new member of the SQL aggregation functions and it was announced in SQL Server 2017. STRING_AGG function gathers all expression from rows and then puts it together in these rows expression in order to obtain a string. Additionally, this function takes a separator parameter that allows separating the expressions to be concatenated.

Read more »