Database design

Esat Erkec

How to implement a graph database in SQL Server 2017

December 19, 2017 by

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

Read more »
Prashanth Jayaram

An introduction to a SQL Server 2017 graph database

December 4, 2017 by

As a database technologist always keen to know and understand the latest innovations happening around the cutting edge or next-generation technologies, and after working with traditional relational database systems and NoSQL databases, I feel that the graph database has a significant role to play in the growth of an organization. Not only are traditional database systems generally inefficient in displaying complex hierarchical data, but even NoSQL lags a little. We usually see a degradation in performance with the number of levels of relationship and database size. Also, depending on the relationship, the number of joins may increase as well.

Read more »
Ayman Elnory

How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs

December 1, 2017 by

Quite often developers put the responsibility of the server performance on the shoulders of DBAs. But to be honest, as a developer, it is your responsibility to write code which is efficient and optimal. However, when writing code, the developer often lacks the knowledge and/or experience about SQL Server to meet this requirement.

In this series, I will be discussing various aspects of query designing, keeping performance and scalability in mind. We will be addressing the most common concepts which we often see that negatively impact SQL code written by developers.

Read more »
Vin Cannarelli

5 tips for working with SQL Server databases to support SharePoint

November 23, 2017 by

The SharePoint primer

If you are reading this, odds are you already work with SharePoint. So, isn’t reading a primer a waste of your time? Not really. As with all things tech, and more to the point Microsoft’s enterprise technology offerings, complete mastery is elusive. For instance, your SharePoint deployment should support sharing, collaboration, and content management depending on your organizational needs. But are you getting the most out of your deployment? And by the way, in how many ways can users collaborate?

Read more »
Bojan Petrovic

How to configure database mail in SQL Server

November 22, 2017 by

Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database.

The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:

  • create a Database Mail account,
  • create a Database Mail profile,
  • and configure those two to work together
Read more »
Ahmad Yaseen

SQL Server Data Type Conversion Methods and performance comparison

October 3, 2017 by

When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the Data Type. SQL Server provides us with a big library of system data types that define all types of data that can be used with SQL Server, from which we can choose the data type that is suitable for the data we will store in that object. You can also define your own customized user defined data type using T-SQL script. SQL Server data types can be categorized into seven main categories:

Read more »
Neeraj Prasad Sharma

Nested Loop Joins in SQL Server – Batch Sort and Implicit Sort

September 1, 2017 by

In SQL Server, it`s not always required to fully understand the internal structure, especially for performance and optimization, if the database design is good, because SQL Server is a very powerful Relational Database and, as such, it has many inbuilt optimization processes which assure a response to the users as fast as possible. But it is always beneficial for the SQL Server developers and administrators to understand the internal structure of the SQL Server so that they can understand and fix the problems that slowed the response of the Database.

Read more »
Sifiso W. Ndlovu

How to replace ASCII special characters in SQL Server

August 7, 2017 by

One of the important steps in an ETL process involves the transformation of source data. This could involve looking up foreign keys, converting values from one data type into another, or simply conducting data clean-ups by removing trailing and leading spaces. One aspect of transforming source data that could get complicated relates to the removal of ASCII special characters such as new line characters and the horizontal tab. In this article, we take a look at some of the issues you are likely to encounter when cleaning up source data that contains ASCII special characters and we also look at the user-defined function that could be applied to successfully remove such characters.

Read more »
Vitor Montalvão

SQL Server system databases – the msdb database

July 14, 2017 by

Introduction

This article is the third I am writing about Microsoft SQL system databases.

In this article, I will focus only on the msdb database, one of the four system databases that exist in any MSSQL instance:

Read more »
Vitor Montalvão

Configuration, operations and restrictions of the tempdb SQL Server system database

June 28, 2017 by

Introduction

tempdb is one of the 4 system databases that exists in all SQL Server instances. The other databases are master, model and msdb. In case of using Replication, a fifth system database named distribution will also exist. You can find all existing system databases in SQL Server Management Studio (SSMS) under the Databases / System Databases folder:

Read more »
Ahmad Yaseen

Compression and decompression functions in SQL Server 2016

June 2, 2017 by

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

Read more »
Ed Pollack

Implementing Different Calendars in Reporting

May 11, 2017 by

Introduction

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. Calendars themselves can vary and provide differing insight into a business, data, and finance. They also assist in taking the intrinsically messy Gregorian calendar and making it easier to work with.

Read more »
Neeraj Prasad Sharma

Introduction to Nested Loop Joins in SQL Server

May 8, 2017 by

A relational database system uses SQL as the language for querying and maintaining databases. To see the data of two or more tables together, we need to join the tables; the joining can be further categorized into INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN. All these types of joins that we use are actually based on the requirement of the users.

Read more »
Ed Pollack

Implementing and Using Calendar Tables

March 24, 2017 by

Introduction

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes. In my previous article, you could learn about designing of a calendar table.

Read more »
Ed Pollack

Designing a Calendar Table

March 22, 2017 by

Introduction

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes.

Read more »
Page 1 of 41234