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 »General database design
SQL Server system databases – the model database
August 9, 2017Introduction
This is my fourth article about SQL Server system databases. In previous articles of the series, I wrote about the tempdb database, the master database and the msdb database.
Read more »SQL Server system databases – the msdb database
July 14, 2017Introduction
This article is the third I am writing about Microsoft SQL system databases.
- The first article Configuration, operations and restrictions of the tempdb SQL Server system database was about the tempdb database
- The second article SQL Server system databases – the master database was about the master database.
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 »SQL Server system databases – the master database
July 6, 2017Introduction
There are at least 4 system databases in any SQL Server instance as shown by the following SQL Server Management Studio (SSMS) screen capture:
Read more »SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT
July 4, 2017In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.
In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.
Read more »SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT
June 30, 2017SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.
Read more »Configuration, operations and restrictions of the tempdb SQL Server system database
June 28, 2017Introduction
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 »SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation
June 21, 2017The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.
Read more »Understanding different SQL Server database states
June 16, 2017Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.
Read more »Parallel Nested Loop Joins – the inner side of Nested Loop Joins and Residual Predicates
May 22, 2017This article is the second part of the Nested Loop Join Series. In the first part, Introduction of Nested Loop Join, we gave a brief introduction to Native, Indexed and Temporary Index Nested Loop Joins along with CPU cost details.
Read more »Implementing Different Calendars in Reporting
May 11, 2017Introduction
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 »SQL DateTime data type considerations and limitations
May 11, 2017Introduction
In this article, we will explorethe SQL Date Time data type and its limitations.
Read more »Introduction to Nested Loop Joins in SQL Server
May 8, 2017A 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 »SQL Server 2016 enhancements – SQL Truncate Table and Table Partitioning
April 18, 2017The 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.
Read more »Implementing and Using Calendar Tables
March 24, 2017Introduction
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 »Designing a Calendar Table
March 22, 2017Introduction
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 »Availability Groups WITHOUT an Active Directory Domain in Google Cloud Platform
March 20, 2017Starting 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.
Read more »How to work with filegroups in SQL Server and migrate data between them
March 8, 2017As 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.
Read more »How to import flat files with a varying number of columns in SQL Server
February 22, 2017Ever 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.
Read more »When to Use SQL Temp Tables vs. Table Variables
February 21, 2017It 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.
Read more »How to Merge and Split CSV Files Using R in SQL Server 2016
February 21, 2017Introduction
From time to time, we may encounter the following scenarios when dealing with data processing:
Read more »Top 5 new features in SQL Server for developers
February 15, 2017Introduction:
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:
Read more »Are SQL Server database triggers evil?
January 25, 2017Introduction
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.
Read more »Best Practices for Configuring Newly Installed SQL Server Instances
January 20, 2017Often 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.
Read more »How to Split a Comma Separated Value (CSV) file into SQL Server Columns
December 29, 2016Receiving a comma delimited file is not new technology nor is it difficult to deal with in SQL Server. As a matter of fact, it is extremely easy. There are many cases as to why you would want to do this. For example, you have an external data source that needs to be imported into your database/table. There a couple ways to do this, however the quickest and easiest way is to use the native “import” feature within SQL Server Management Studio and you can even save it to an SSIS Package at the end of the process. The end result of using this method is that the external CSV file is loaded into a SQL Server table where columns are created and rows are populated.
Read more »