Database design

Discovering database specific information using built-in functions and dynamic management views (DMVs)

May 15, 2017 by Gerald Britton

Introduction

In the last two articles on dynamic management views in SQL Server, Discovering SQL server instance information using system views and Discovering more SQL Server information using the built-in dynamic management views (DMVs), we used DMVs to discover a fair bit of information about the SQL Server instance we’re connected to. In this article, we’ll begin diving in to database specifics. There is a lot of territory to cover! We’ll also use several of the built-in functions that come with SQL Server.

Implementing Different Calendars in Reporting

May 11, 2017 by Ed Pollack

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.

SQL DateTime data type considerations and limitations

May 11, 2017 by Jefferson Elias

Introduction

In this article, we will explorethe SQL Date Time data type and its limitations.

Introduction to Nested Loop Joins in SQL Server

May 8, 2017 by Neeraj Prasad Sharma

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.

SQL Server 2016 enhancements – SQL Truncate Table and Table Partitioning

April 18, 2017 by Prashanth Jayaram

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the SQL truncate command partitioning enhancements in SQL 2016

One of the biggest challenges for a DBA is to identify the right candidate for table partitioning, as it requires expertise in design and implementation.

Discovering more SQL Server information using the built-in dynamic management views (DMVs)

April 6, 2017 by Gerald Britton

Introduction

This is the second article in a continuing series on the many system tables, views, procedures and functions available in SQL Server. In the first part of this series, Discovering SQL server instance information using system views, you learned how to discover many attributes of a SQL Server instance you have been given access to. In this part, we will continue the journey and see what else we can find.

How to optimize the dimension security performance using partitioning in SSAS Multidimensional

March 27, 2017 by Koen Verbeeck

Introduction

In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query are read instead of all the data.

Implementing and Using Calendar Tables

March 24, 2017 by Ed Pollack

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.

Designing a Calendar Table

March 22, 2017 by Ed Pollack

Introduction

Availability Groups WITHOUT an Active Directory Domain in Google Cloud Platform

March 20, 2017 by Kaloyan Kosev

Starting with SQL Server 2016 and Windows Server 2016 there have been numerous cloud related enhancements, most of them are tightly related to Microsoft’s Azure like the ‘Cloud Witness’ but there are others that can be implemented within other public or private clouds.

How to work with filegroups in SQL Server and migrate data between them

March 8, 2017 by Kaloyan Kosev

As you may already have figured out, the default settings in SQL Server are not always the best. Such is the case when you are working with new user databases; usually you get a single data (*.mdf) and transaction log (*.ldf) file. The data file resides within the PRIMARY file group; the only one we have so far, and it will store all of our databases objects, system objects, user tables, user stored procedures and all other objects. In some cases this default configuration may be good enough for you, but let us cover why would you prefer a different configuration in your production environment.

Temporal Table applications in SQL Data Warehouse environments

March 7, 2017 by Hans Michiels

Today the subject of investigation is the Temporal Table, which is a new feature in SQL Server 2016. My focus will slightly be on how to use it in Data Warehouse environments, but there is some general information passing by as I write.

Benefits of Partitioning an SSAS Multidimensional Cube

February 28, 2017 by Koen Verbeeck

Introduction

In the article How to partition an SSAS Cube in Analysis Services Multidimensional, we explained how you can partition your measure groups in an SSAS cube. In this article, we’ll look at the expected benefits of the partition strategy. Time to reap the benefits of our hard work.

How to partition an SSAS Cube in Analysis Services Multidimensional

February 28, 2017 by Koen Verbeeck

Partitioning is the concept where you divide your data from one logical unit into separate physical chunks. This can have several advantages, such as improved performance or easier maintenance. You can for example partition a table in a SQL Server database, but you can also partition your measure groups inside an Analysis Services (SSAS) Multidimensional cube. In this article, we’ll discuss how you can set-up partitioning. For more information about the expected benefits, take a look at Benefits of Partitioning your SSAS Multidimensional Cube.

Discovering SQL server instance information using system views

February 28, 2017 by Gerald Britton

Introduction

Out of the box, SQL Server comes with a substantial and – release by release – ever-growing set of system tables, views, stored procedures and functions. There’s a good chance you’ve never directly used more than a handful of them. That’s certainly the case with me!

This is the first article in a series designed to explore this world that lives just below the surface of our everyday interactions with SQL Server through the same objects we create to enable the applications we write and support.

SQL Server Common Table Expressions (CTE)

February 23, 2017 by Syed Shanu

What is a Common Table Expression

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

In this article, we will see in detail about how to create and use CTEs from our SQL Server.

How to import flat files with a varying number of columns in SQL Server

February 22, 2017 by Brian Bønk Rueløkke

Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production?

Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files.

So I’ve come up with an alternative solution that I would like to share with you.

When to Use SQL Temp Tables vs. Table Variables

February 21, 2017 by Aamir Syed

It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.

How to Merge and Split CSV Files Using R in SQL Server 2016

February 21, 2017 by Jeffrey Yao

Introduction

From time to time, we may encounter the following scenarios when dealing with data processing:

$C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\14.png$

How to query data in a System-Versioned Temporal Tables in SQL Server

February 20, 2017 by Marko Zivkovic

This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size.

Top 5 new features in SQL Server for developers

February 15, 2017 by Mustafa EL-Masry

Introduction:

Microsoft SQL Server 2012 introduces many features that help database administrators, database developers, and BI developers.

In this article, I will cover some of the new features for database developers in these main points:

Introducing SQL Server Incremental Statistics for Partitioned Tables

February 3, 2017 by Simon Liew

If you are maintaining a very large database, you might be well aware of the pain to perform update statistics on a very large table.

This article introduces incremental statistics which is available from SQL Server 2014 highly simplifies statistics management on very large partitioned tables.

Options for Partitioned Tables and Indexes in SQL Server

January 31, 2017 by Gerald Britton

Introduction

I work for a large, multinational financial institution. Like most companies in this field, ours is conservative and subject to much regulatory oversight. The first has meant that we’re slow to adopt new technologies. We need to be really, really, really sure things won’t break, customer accounts won’t vanish or lose their balances and that the regulators will not raise any red flags. Everything we do is subject to the scrutiny of our internal auditors, even something that seems simple, like rerunning a production job that failed because the host compute was down. I really mean everything!

Are SQL Server database triggers evil?

January 25, 2017 by Daniel Calbimonte

Introduction

There is a lot of talk about how bad triggers are, how you should never use them, etc. I wanted to spend some time reviewing fact vs fiction and do an objective analysis of SQL Server database triggers (both DDL and DML), warts and all. We will review alternatives and compare them with triggers to determine advantages vs disadvantages of each approach.

Best Practices for Configuring Newly Installed SQL Server Instances

January 20, 2017 by Musab Umair

Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects in mind many of us have to go through the configuration of the newly installed or pre-installed but un-configured instances. Configuring SQL Server newly instances are not an easy task and a DBA might want to configure an Instance in many ways.