Category: SQL Server

In-depth Exploration of Row Level Security

Kenneth Igiri
In-depth Exploration of Row Level Security
5 (100%) 2 votes

Introduction

Organizations are becoming more and more concerned about how to reduce the cost of licensing database solutions using consolidation. Some consolidation can be achieved in SQL Server simply by taking advantage of the existing one-to-many relationship between instances and databases. However, there are cases where the solution demands that data is consolidated into one table. In such a case, there may be concerns about how to restrict access to the data.

Row Level Security was introduced in SQL Server 2016 as a solution to scenarios similar to the above. It allows you to restrict access to rows in a table based on conditions defined in an inline Table Valued Function called a Predicate Function. When a Predicate Function is applied to a user table containing consolidated data, the system can be configured to return different data sets to different users depending on their roles which in turn depends on their job descriptions or departments for example.

Read More

Automatic Deletion of Forgotten Transactions in MS SQL Server

Evgeniy Gribkov
Automatic Deletion of Forgotten Transactions in MS SQL Server
4 (80%) 4 votes

Introduction

It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).

In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.

Read More

Advanced SQL: Insert output of the parameterized table-valued function in SQL table

Nisarg Upadhyay
Advanced SQL: Insert output of the parameterized table-valued function in SQL table
3 (60%) 3 votes

In this article, I am going to demonstrate the following:

  1. How to insert the output of a table-valued function in a SQL table.
  2. How to insert the output of a table-valued function which is created on the remote database server.

What is “Insert into” statement

In RDBMS, “Insert into” is one of the basic SQL statements. It is used to insert new records in a SQL table. Using the statement, we can perform the following tasks:

  • Insert new records in a table (Basic Insert).
  • Insert values of a specific column in a table.
  • Insert the output generated by a stored procedure in a SQL table. Read More

SQL Server Bulk Insert – Part 1

Esat Erkeç
SQL Server Bulk Insert – Part 1
4.1 (82.86%) 7 votes

According to Wikipedia, ”A Bulk insert is a process or method provided by a database management system to load multiple rows of data into a database table.”  If we adjust this explanation in accordance with the BULK INSERT statement, bulk insert allows importing external data files into SQL Server. Read More

Introduction to Row-Level Security in SQL Server

Ben Richardson
Introduction to Row-Level Security in SQL Server
3.5 (70%) 2 votes

Problem

Prior to SQL Server 2016, table-level security was the default lowest level of security for a database. In other words, a user could be restricted to access a table as a whole. However, in some cases we need users to have access to a table, but not to specific rows within the table. Prior to SQL Server 2016, this required custom stored procedures to be written for the provision of such fine-grained security. However, such stored procedures are prone to SQL injection and other security caveats.

Read More

SQL Server CRUD Operations – Part 1

Esat Erkeç
SQL Server CRUD Operations – Part 1
4.7 (93.33%) 3 votes

In the database programming, there are four basic and essential operations: create, read, update, and delete. These operations can be defined as the first step in database programming and are called CRUD operations. CRUD is an acronym of the CREATE, READ, UPDATE and DELETE words.  The CRUD term was once mentioned by James Martin in his book ‘Managing the Database Environment’ and since then this term has become popular. In this article, we will explore the CRUD operation in terms of SQL Server because the operation syntax can differ from other relational and NoSQL databases. Read More

Dealing with NULLs in SQL Server

Kenneth Igiri
Dealing with NULLs in SQL Server
3.3 (66.67%) 3 votes

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More

Basics of Running T-SQL Statements from Command Line using SQLCMD

Haroon Ashraf
Basics of Running T-SQL Statements from Command Line using SQLCMD
3.6 (72%) 10 votes

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

Read More

Essentials and usage of NOLOCK hint in SQL Server

Esat Erkeç
Essentials and usage of NOLOCK hint in SQL Server
4.4 (88.57%) 7 votes

The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this locking mechanism can lead to performance problems such as high concurrent process pressures. So you can experience the deadlock (Deadlock is a concurrency problem in which two transactions want to access the same data simultaneously) problems in your database. In this article, we will focus on how to avoid lock issues with the help of NOLOCK hint. First, let’s learn the main essentials and details of dirty read methodology because the NOLOCK hint can cause dirty reading.

Read More