SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.Read more »
How to implement array-like functionality in SQL ServerJanuary 16, 2018
I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.
I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.
Some developers asked me the same thing. Where are the arrays in SQL Server?Read more »
How to use sargable expressions in T-SQL queries; performance advantages and examplesDecember 22, 2017
One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario
- Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
- The query executed successfully and does not exhibit any symptoms of performance problems
- The developer team release this table and query into production
- One day you take a telephone from your colleague and he says my report is very slow
- Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it
What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statementsNovember 29, 2017
SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.Read more »
ETL optimization using SQL TRY FunctionsOctober 24, 2017
An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL TRY built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.Read more »
Monitoring SQL Server database status changes using T-SQL and PowerShell scriptsOctober 17, 2017
Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.Read more »
How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQLOctober 2, 2017
In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.Read more »
How to create a linked server to an Azure SQL databaseSeptember 27, 2017
Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL.
This article will explain how to create and configure a linked server to retrieve data from an Azure SQL database. Also, we will explain how to solve some common problems/issues during the process of creating a linked server to an Azure SQL database.Read more »
T-SQL commands performance comparison – NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPTSeptember 22, 2017
This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.
Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQLAugust 15, 2017
Database availability is critical to every enterprise and conversely, unavailability directly can create a severe negative impact to the business in today’s world. As database administrators, it is important that we ensure we take all possible steps to minimize data loss. While it is naïve to think that our databases are invincible because of all such precautions, we can always aim to bring the databases back into operation as quickly as possible by meeting the RPO and RTO. This way, the business is unaffected, and we also meet our SLAs.Read more »
SQL Server universal comparison quantified predicates (ANY, ALL, SOME)July 27, 2017
SQL server provides us with comparison operators to modify subqueries. This article will start with the definition of universal quantification, with a quick brush up with real life logical examples.Read more »
How to analyze SQL Server database performance using T-SQLJuly 10, 2017
The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.Read more »
How to create, configure and drop a SQL Server linked server using Transact-SQLJuly 5, 2017
Linked servers allow getting data from a different SQL Server instance using single T-SQL statement.
This article will explain how to create, configure and drop a SQL Server linked server using system stored procedures.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_ABORTJuly 4, 2017
In 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 ARITHABORTJune 30, 2017
SQL 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 »
How to calculate work days and hours in SQL ServerJune 26, 2017
Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.Read more »
How to copy an Azure SQL database using the Azure Portal, Cloud Shell and T-SQLJune 23, 2017
This article will provide an overview covering programmatically moving databases on the Azure Portal while avoiding common problems with users and logins.Read more »
Understanding the Impact of NOLOCK and WITH NOLOCK Table Hints in SQL ServerJune 6, 2017
Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.Read more »
A T-SQL design pattern for logging process executionMay 25, 2017
Back in my days at school, I followed a course entitled “Object-Oriented Software Engineering” where I learned some “design patterns” like Singleton and Factory. If you are not familiar with this expression, here is a definition of a design pattern from Wikipedia:Read more »
T-SQL as an asset to set-based programming approachApril 27, 2017
This article is the third and last one of a series of articles that aims to introduce set-based programming approach inside SQL Server, so using Transact SQL. In first article “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve compared procedural and set-based approaches and came to the conclusion that the second one can facilitate developer or DBA’s life. In second article “From mathematics to SQL Server, a fast introduction to set theory”, we’ve made the parallel between mathematical definition of set theory and its implementation in SQL Server. Now, we’ll discuss about some other features from T-SQL that can’t be left aside when considering sets.Read more »
T-SQL for DBAs – Three ways of using T-SQL for quick data analysisApril 26, 2017
As a database administrator, sometimes you need to identify details about a problem as quickly as possible and being able to build and analyze data for analysis will help you solve the problem. From getting information about the latest backups, to saving information about waits or indexes and comparing that to other captured metrics, we will run into issues where being able to get, store and analyze data are important for decisions to solve urgent problems.Read more »
SQL Server Code Review Checklist for DevelopersApril 26, 2017
In a software development life cycle, Code Review plays an integral role in improving the product quality. Having a Code Review Checklist is indispensable since it ensures that the best practices are followed and reviews are performed consistently. It is essential for developers to be aware of the coding guidelines while working on their code changes. Catching a bug early in the process is inexpensive and easier to resolve, than compared with a bug caught later in the game. Having all the common mistakes added to the checklist document is a great way to create awareness and ensure good code quality over a period of time.Read more »
From mathematics to SQL Server, a fast introduction to set theoryApril 25, 2017
In the previous article of this series “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve seen from a simple example that we could find actual benefit from learning set-based approach when writing T-SQL code.
In this article, we will carry on in this way by having a look at what a set is and what we can do with it in a mathematical point of view and how it’s implemented and provided to us in SQL Server. We will also have a look at more “realistic” examples using Microsoft’s AdventureWorks database.Read more »
An introduction to set-based vs procedural programming approaches in T-SQLApril 24, 2017
Most IT professionals started their studies or careers by learning programming languages like Basic, Cobol, C, Pascal, Java and so on. Those languages produce results using a sequence of operations or procedures. For this reason, this approach is called procedural programming.Read more »
SQL Server lock issues when using a DDL (including SELECT INTO) clause in long running transactionsApril 12, 2017
This article explains lock issues in SQL Server with DDL statements including SELECT INTO clause.
Use of explicit transaction is common in SQL Server development. Sometimes a developer might inadvertently specify a Data Definition Language clause within a long running explicit transaction, similar to the structure below, within a SQL Server object such as a stored procedure.Read more »