A database
A SQL Server database can be thought of, in and of itself, as a data dictionary. It is self-describing, in that objects can be scripted into Data Definition Language aka DDL scripts to view all attributes, quickly and easily.
Read more »
Database design
Why is my CTE so slow?
December 22, 2016Introduction
Have you ever written up a complex query using Common Table Expressions (CTEs) only to be disappointed by the performance? Have you been blaming the CTE? This article looks at that problem to show that it is a little deeper than a particular syntax choice and offers some tips on how to improve performance.
Read more »
Top 5 New SQL Server 2016 DMVs for DBAs
December 20, 2016Since its initial release that was part of SQL Server 2005, Dynamic Management Views (DMV) changed – mostly for the better – the role of database administration (DBA) within SQL Server-based environments. They improved the efficiency of DBAs regarding database server monitoring, issue diagnoses, and subsequent performance optimisation. As a result of this positive reception, it has become customary that some of the highly anticipated items in every release of SQL Server includes discovering what new DMVs will be added. In this article I take you through my favourite top 5 DMVs to come out of SQL Server 2016.
Read more »
Microsoft SQL Server database deployment process
December 14, 2016Introduction
Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.
Read more »
SQL Server consolidation – Hosting multiple databases on a single SQL Server instance
December 2, 2016Introduction
Consolidating SQL Server databases and instances is a practice which, under certain criteria, allows organizations to benefit as they can reduce licensing costs, and if properly designed, hardware costs.
Read more »
Concept and basics of Temporal tables in SQL Server 2016
November 24, 2016In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:
Read more »
Understanding the SQL Server Proportional fill algorithm
November 22, 2016When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent.
Read more »
SQL Server Partitioned Views
November 18, 2016SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily. A common example for tables partitioning is archiving old data to slow disk drives and use the fast ones to store the frequently accessed data. Table partitioning improves query performance by excluding the partitions that are not needed in the result set. But table partitioning is available only in the Enterprise SQL Server Edition, which is not easy to upgrade to for most of small and medium companies due to its expensive license cost.
Read more »
How to check if a date is a holiday in SQL server
November 1, 2016Introduction
There are plenty of scripts to compute the date of various holidays given the year. Let’s look at the problem in reverse: Given a date, determine if it is a given holiday or not.
Read more »
How to track the history of data changes using SQL Server 2016 System-Versioned Temporal Tables
September 23, 2016SQL Server 2016 introduces a new type of table that is designed to keep the full history of data changes, where row validity is managed by the system. This new table type is called a System-Versioned Temporal Table. In earlier SQL Server versions, user tables would enable you to hold only the most recent copy of the row, without being able to query the value before the UPDATE or DELETE operations. Using a Temporal Table, you will be able to query the recent state of the row as usual, in addition to the ability to query the full history of that row, which is fully managed by the SQL Server Engine, as you can’t define the rows validity period explicitly.
Read more »
Database checkpoints – Enhancements in SQL Server 2016
September 21, 2016When a new row is inserted or an existing one is updated in your database, the SQL Server Database Engine will keep that change in the buffer pool in the memory first, without applying each change directly to the database files for IO performance reasons. These data pages located in the buffer pool and not reflected yet to the database files are called Dirty Pages. The SQL Server Database Engine uses a special kind of processes to write these dirty pages to the data and log database files periodically. These processes are called Checkpoints. The Checkpoint creates a mark that is used by the SQL Server Database Engine to redo any transaction that is committed, written to the database transaction log file without reflecting the data changes to the data files due to an unexpected shutdown or crash. Also, this recovery point that is created by the Checkpoint will be used to roll back any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. In this way the SQL Server Engine will guarantee the database consistency. The time that is taken by the SQL Server Database Engine to redo and undo the transactions is called the Recovery Time. All information about the Checkpoints will be written to the database boot page to identify till which point the database files are synchronized with the buffer pool when the system wakes up after crash or shutdown.
Read more »
Archiving SQL Server data using Partitions
August 29, 2016The Partition feature was introduced in the SQL Server 2005. This article is to cover how partitioning can be useful when it comes to archiving of SQL Server data in a database. Please note that this article does not cover how partitioning works and its configurations in detail.
Read more »
Optimize NULL values storage consumption using SQL Server Sparse Columns
August 29, 2016SQL Server 2008 introduces a new column attribute that is used to reduce the storage consumed by NULL values in the database tables. This feature is known as Sparse Columns. Sparse Columns work effectively in the case of the columns with high proportion of NULL values, as SQL Server will not consume any space storing the NULL values at all, which helps in optimizing the SQL storage usage.
Read more »
Ready, SET, go – How does SQL Server handle recursive CTE’s
August 19, 2016First of all, a quick recap on what a recursive query is.
Recursive queries are useful when building hierarchies, traverse datasets and generate arbitrary rowsets etc. The recursive part (simply) means joining a rowset with itself an arbitrary number of times.
A recursive query is defined by an anchor set (the base rowset of the recursion) and a recursive part (the operation that should be done over the previous rowset).
Read more »
Link a SQL Server to an Oracle database
August 15, 2016There are some cases in the wild where we want to get back data from an external database without additional coding in an application and just write a T-SQL query that will do all the job for us. That’s why Microsoft provides the « linked server » feature.
Read more »
Use of hierarchyid in SQL Server
July 29, 2016I attended a TDWI conference in May 2016 in Chicago. Here I got a hint about the datatype hierarchyid in SQL Server which could optimize and eliminate the good old parent/child hierarchy.
Read more »
The new SQL Server 2016 sample database
July 22, 2016Background
We have all learned to love and hate the trusty Bike shop database. Almost every demo or presentation pertaining to SQL Server we do, we use the AdventureWorks sample database. Almost every code sample on books online references AdventureWorks for illustrations and practical explanations of a feature. When SQL Server 2005 was released Microsoft replaced the old Pubs & Northwind Sample databases with a more complete and more feature rich database called AdventureWorks. Ever since we have been using this new sample database for almost everything we want to test. Microsoft has now given us an even better sample database called WorldWideImporters, which utilises almost every SQL Server feature I can think of from Temporal Tables to In-Memory table.
Read more »
Efficient creation and parsing of delimited strings
July 5, 2016Description
Converting a delimited string into a dataset or transforming it into useful data can be an extremely useful tool when working with complex inputs or user-provided data. There are many methods available to accomplish this task; here we will discuss many of them, comparing performance, accuracy, and availability!
Read more »
Temporal tables – SQL Server
July 4, 2016What is a temporal table?
Temporal tables give us the possibility to retrieve data from any point in the past and for every data change (Update, Delete, Merge). With temporal table users can recover data from accidental changes (update/delete) as well as audit all changes to data, etc. Temporary table is a new feature in SQL Server available from SQL Server 2016 CTP2 version. Read more »
What’s new in SQL Server 2016 summary
June 22, 2016SQL Server 2016 became publically available on the 1st of June 2016. Some might feel that a new SQL Server release every 2 years might be too frequent with many organization still lagging behind by running SQL Server 2012 or earlier.
Read more »
The system msdb database, introduction and tips
June 14, 2016Introduction
In a previous article, we talked about the tempdb system database. In this article, we will talk about the msdb database.
Read more »
How to migrate users to a partially contained database in SQL Server
June 13, 2016Introduction
Microsoft introduced the Contained Database feature in SQL Server 2012. In this article we will take a look at how to migrate SQL Server Logins to a partially contained database in SQL Server to remove dependencies from the Instance of SQL Server where Contained Database resides.
Read more »
SQL Server 2014 contained databases
June 1, 2016SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.
Read more »
The benefits, costs, and documentation of database constraints
April 7, 2016Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them?
Read more »
SQL Server indexed views
March 17, 2016SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.
Read more »