General database design

Different methods to attach SQL Server MDF files

February 7, 2020 by Nisarg Upadhyay

This article demonstrates different methods to attach SQL Server MDF files. First, let me explain about the database files.

Understanding the interaction between Set Theory and Set Operators in SQL Server

January 21, 2020 by Esat Erkec

In this article, we will describe the relation between the Set Theory and SQL Server Set Operations

How to detect and prevent unexpected growth of the TempDB database

January 13, 2020 by Ahmad Yaseen

In this article, we will discuss the best practices that should be followed in order to keep the TempDB database in a healthy state and prevent any unexpected growth of the database, in addition to the procedure that can be followed to detect this unexpected growth once occurred.

Using SQL CREATE INDEX to create clustered and non-clustered indexes

January 10, 2020 by Ben Richardson

The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A book index may have a list of topics discussed in a book in alphabetical order. Therefore, if you want to search for any specific topic, you simply go to the index, find the page number of the topic, and go to that specific page number. Database indexes are similar and come handy. Particularly, if you have a huge number of records in your database, indexes can speed up the query execution process. There are two major types of indexes in SQL Server: clustered indexes and non-clustered indexes.

Install and configure the AdventureWorks2016 sample database

January 9, 2020 by Nisarg Upadhyay

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.

Learn SQL: CREATE DATABASE & CREATE TABLE Operations

December 5, 2019 by Emil Drkusic

Welcome to the first article in the Learn SQL series. In this part, we’ll start with two essential commands in SQL: Create Database and Create Table. While both are pretty simple, they should be used first before you start working on anything with data (unless you use some template database).

SQL DDL: Getting started with SQL DDL commands in SQL Server

November 8, 2019 by Ben Richardson

This article explains SQL DDL commands in Microsoft SQL Server using a few simple examples.

SQL JOIN TABLES: Working with Queries in SQL Server

November 6, 2019 by Ben Richardson

In this article, you will see how to use different types of SQL JOIN tables queries to select data from two or more related tables.

Dynamic Data Masking In SQL Server

October 15, 2019 by Ben Richardson

Security has been one of the prime concerns of database developers since the inception of database management systems. Various data protection schemes have been introduced to provide secure access to sensitive data.

SQL Server ALTER TABLE ADD Column overview

August 27, 2019 by Dinesh Asanka

In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and adding and updating the column with a value later on larger tables.

Clustered Index vs. Heap in SQL Server

June 21, 2019 by Ed Pollack

Summary

There are few topics so widely misunderstood and that generates such frequent bad advice as that of the decision of how to index a table. Specifically, the decision to use a heap over a clustered index is one where misinformation spreads quite frequently.

Nested Triggers in SQL Server

March 27, 2019 by Ben Richardson

Nested Triggers in SQL Server are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc.

SQL Server trace flags guide; from -1 to 840

March 4, 2019 by Nikola Dimitrijevic

SQL Server trace flags are configuration handles that can be used to enable or disable a specific SQL Server characteristic or to change a specific SQL Server behavior. It is an advanced SQL Server mechanism that allows drilling down into a hidden and advanced SQL Server features to ensure more effective troubleshooting and debugging, advanced monitoring of SQL Server behavior and diagnosing of performance issues, or turning on and off various SQL Server features

What is Database Normalization in SQL Server?

November 27, 2018 by Prashanth Jayaram

In addition to specifically addressing database normalization in SQL Server, this article will also address the following questions:

Graph Database features in SQL Server 2019 – Part 1

November 21, 2018 by Rajendra Gupta

SQL Server 2017 introduced Graph database features where we can represent the complex relationship or hierarchical data. We can explore the following articles to get familiar with the concept of the Graph database.

Difference between Identity & Sequence in SQL Server

August 15, 2018 by Ben Richardson

In SQL Server, both the SEQUENCE object and IDENTITY property are used to generate a sequence of numeric values in an ascending order. However, there are several differences between the IDENTITY property and SEQUENCE object. In this article, we will look at these differences.

What’s new in SQL Server 2017

August 14, 2018 by Prashanth Jayaram

SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing lot about it and testing various features of SQL Server 2017, I’d like to walk you through some of its interesting features.

Query optimization techniques in SQL Server: Database Design and Architecture

July 13, 2018 by Ed Pollack

Description

One of the best ways to optimize performance in a database is to design it right the first time! Making design and architecture decisions based on facts and best practices will reduce technical debt and the number of fixes that you need to implement in the future.

Hash partitions in SQL Server

June 28, 2018 by Bhavesh Patel

In SQL Server, when talking about table partitions, basically, SQL Server doesn’t directly support hash partitions. It has an own logically built function using persisted computed columns for distributing data across horizontal partitions called a Hash partition.

Understanding benefits of Graph Databases over Relational Databases through Self-Joins in SQL Server

May 21, 2018 by Sifiso Ndlovu

Earlier this year, I published several articles on SQLShack with an aim of demonstrating tools available for visualising SQL Server 2017 graph databases. I was so caught up in the excitement of having SQL Server finally support graph databases that I forgot that some people still do not have a good grasp of how graph databases work let alone consider replacing their relational databases models in favour of graph. Although there are several ways that one can go about explaining the usefulness of graph databases over its relational counterpart, I have opted to focus on the benefits and strengths of graph databases by demonstrating the differences in which graph and relational databases deal with hierarchical datasets.

Logging SQL Server database errors

May 15, 2018 by Timothy Smith

We receive many database alerts with many of the alerts logging some of these same alerts or information to files or tables. What we’ve found over time is that the logging is now costing us quite a bit of resource. Our logging server (where both files and table logging are stored) has had a few outages related to conflicts from messages for other servers. We’ve considered scaling the alerting by environment type, but we’ve also considered that we may be logging too much information about our databases. In addition, since we receive so many alerts each day, it’s impossible for us to resolve them and assist with other issues that arise. What are some techniques that we can use to help us with the issue of too much logging information and too many alerts? Read more »

How to quickly generate a large number of dimension tables for reporting applications

January 19, 2018 by Ed Pollack

Description

When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI.

Creating this schema by hand is time-consuming and error-prone. Automating it can be a way to improve predictability, maintainability, and save a ton of time in the process!

How to enable and disable the Identity Cache in SQL Server 2017

December 20, 2017 by Sifiso Ndlovu

Every data warehouse developer is likely to appreciate the significance of having surrogate keys as part of derived fields in your facts and dimension tables. Surrogate keys make it easy to define constraints, create and maintain indexes, as well as define relationships between tables. This is where the Identity property in SQL Server becomes very useful because it allows us to automatically generate and increment our surrogate key values in data warehouse tables. Unfortunately, the generating and incrementing of surrogate keys in versions of SQL Server prior to SQL Server 2017 was at times challenging and inconsistent by causing huge gaps between identity values. In this article, we take a look at one improvement made in SQL Server 2017 to reduce the creation of gaps between identity values.

How to implement a graph database in SQL Server 2017

December 19, 2017 by Esat Erkec

Introduction

Graph database

A graph database is a type of database whose concept is based on nodes and edges.

Graph databases are based on graph theory (a graph is a diagram of points and lines connected to the points). Nodes represent data or entity and edges represent connections between nodes. Edges own properties that can be related to nodes. This capability allows us to show more complex and deep interactions between our data. Now, to explain this interaction we will show it in a simple diagram

How to return data use, index compression, and row information with PowerShell

December 6, 2017 by Timothy Smith

Background

We recently inherited a database environment where we’re facing significant data growth with limits on the sizes we can allow our databases to grow. Since we maintain multiple development, QA and production environments and these environments must be sized appropriately. We set a few standards about tables that exceed certain sizes – rows, data or both, or have certain growth patterns and our standards force compression at thresholds we set for our different environments (including using page, row, or clustered columnstore index compression) We’re looking for how we can get information about data and compression in tables and options we have so that we can quickly determine candidates that don’t currently match our best practices design we’ve set for our environments.