T-SQL commands performance comparison – NOT IN vs NOT EXISTS vs LEFT JOIN vs EXCEPT September 22, 2017 by Ahmad Yaseen The T-SQL commands library, available in Microsoft SQL Server and updated in each version with new commands and enhancements to the existing commands, provides us with different ways to perform the same action. In addition to an ever evolving toolkit of commands, different developers will apply different techniques and approaches to the same problem sets and challenges Read more »
How to automatically maintain Full-Text indexes and catalogs September 21, 2017 by Jefferson Elias Introduction In a previous article entitled Hands on Full-Text Search in SQL Server, we had an overview on the Full-Text feature as it’s implemented in SQL Server. We saw how to create Full-Text indexes and that they were stored inside a container called a Full-Text catalog. We’ve also seen that, by design, this kind of index will generate a fragmentation. Read more »
SQL Server DB Migration – Cloning a database to another collation September 19, 2017 by Prashanth Jayaram Database migration is a vital task in any environment, complex or otherwise. Seamless migrations are the goal but the efforts required to ensure it are tremendous. Backing up and restoring the database is surely a preferred and a robust approach, but does it work well in all situations? How do we plan this when the source and the destination databases need different configurations? How do we make such a migration seamless? Read more »
SQL Server 2016 Maintenance Plan Enhancements September 18, 2017 by Ahmad Yaseen SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user. SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks. Read more »
How to replace hardcoded lookups using SQL Server Master Data Services September 14, 2017 by Sifiso W. Ndlovu Introduction A big part of the technical debt in my organization’s data warehouse (DW) and business intelligence (BI) environments relates to hardcoded lookup data. This is data required by the business to make sense of transactional data but was never planned for in the underlying source system and consequently get injected into DW and BI solutions. Inevitably, it is only a matter of time before DW and BI team lose track of the places wherein the hardcoded data reside thus making it difficult to maintain. Furthermore, due to lack of documentation or staff retention, anyone who subsequently takes over these DW/BI solutions can unknowingly create duplicate lookup data. In this article, I explain how we reduced such technical debt in my organization by moving most of the hardcoded lookups into SQL Server Master Data Services (MDS). Read more »
How to connect to (and query) Power BI and Azure using PowerShell September 13, 2017 by Craig Porteous Power BI “as a whole” is a bit of a black box. If you’re like me and used to using SQL Server & its components; SSRS, SSAS etc. you have access to installation directories, Event logs, trace logs, error logs, chocolate logs? You can see full instances & their contents in one go, whether that be databases, reports or cubes. It gives you the control over & responsibility for performance & maintenance. Read more »
SQL query performance tuning tips for non-production environments September 12, 2017 by Derik Hammer It is a common misconception that you need real production data, or production like data, to effectively tune queries in SQL Server. I am going to explain how you can compile the same execution plans as what your production environment would compile, so that you can tune them in a non-production environment, gaining these benefits. Read more »
How to Capture Database(s) usage stats using sp_spaceused and PowerShell September 11, 2017 by Prashanth Jayaram Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator. Read more »
Synchronizing SQL Server Instance Objects in an Availability Group September 8, 2017 by Derik Hammer Availability groups can no longer be considered new. The feature was released in SQL Server 2012 but I often notice that SQL Server Failover Cluster Instances (FCIs) are much better understood. For a very long time, when people thought about SQL Server they thought about the entire instance as a unit. Microsoft has been implementing new features and services which begin to segment the concept of an instance from the concept of a database. Read more »
Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461 September 8, 2017 by Samir Behara In today’s competitive world, technical certifications play an important role in advancing your career. It is a great way to validate and sharpen your technical skills, thus helping you in being more productive in your assignments. It also helps you to stand out from the crowd and get more visibility. A few months back I decided to appear the Microsoft Exam 70-461: Querying SQL Server 2012/2014 and was able to successfully pass the exam. Read more »
Using Many-to-Many Relationships in SQL Server Analysis Services (SSAS) 2016 September 7, 2017 by Thomas LeBlanc The Multidimensional Cube option of Analysis Services has handled many-to-many relationships with ease for many versions before 2016. The Tabular had a work around using DAX formulas until the release of SQL Server 2016. There are still some limitations to many-to many in Tabular but of course, there are some “tricks” to overcome the limitations. But, the many-to-many relationship will be in businesses data for many years to come. A solution has to be provided when it comes to Analysis Service databases. Read more »
How to drop a role in a SQL Server Database September 6, 2017 by Jefferson Elias Introduction Let’s say we have a database role that we don’t need anymore because we defined another security policy using another database role. What are the steps to follow in order to properly drop this database role? That’s the question this article will try to answer, covering as many cases as possible. In the following article, we will consider the simple steps we can follow in order to do this task using both SSMS and T-SQL. Then, we will focus on some facts that will lead us to the conclusion that, if we do it this way, it won’t work every time. We will list some situations where it could fail and define a test case situation in order to create a stored procedure that will do the job correctly, in all cases bymanaging these situations. Read more »
How to Index Foreign Key Columns in SQL Server September 5, 2017 by Ahmad Yaseen Before going through the main concern of this article, indexing the foreign key columns, let’s take a small trip back to review the SQL Server Indexes and Foreign Key concepts. Read more »
8 things to know about Azure Cosmos DB (formerly DocumentDB) September 4, 2017 by Minette Steynberg Introduction Azure Cosmos DB is a low-latency, high throughput, globally distributed, a multi-model database which can scale within minutes and offers 5 consistency options to let you decide how to deal with the CAP theorem. Azure Cosmos DB used to be known as Document DB, but since additional features were added it has now morphed into Azure Cosmos DB. The name was chosen to spark the innovation and imagination of developers around the globe. Read more »
Nested Loop Joins in SQL Server – Batch Sort and Implicit Sort September 1, 2017 by Neeraj Prasad Sharma 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 »
What’s new in SQL Server Management Studio 17.2; Authentication methods, scripting options and more August 31, 2017 by Ahmad Yaseen SQL Server Management Studio is an integrated graphical interface that is used to configure, manage, monitor and administrate the SQL Server instances hosted on the local machine, on a remote server or in the cloud. It provides us with editing, debugging and deploying environment for the T-SQL, XML, MDX and DMX languages. Read more »
Configure Multi-Factor Authentication in Azure SQL Database August 30, 2017 by Daniel Calbimonte Introduction The new SSMS 17.2 allows users to authenticate using Active Directory with Multi-Factor Authentication (MFA). This is a secure method for authentication where you have more than one method to validate your authentication. That way, you have a more secure authentication and prevent your Azure Portal or applications to be easily attacked by hackers or other malicious users. Read more »
Introduction to Azure SQL Data Warehouse August 29, 2017 by Minette Steynberg Introduction Azure SQL Data Warehouse is a new addition to the Azure Data Platform. When I first heard about it I wasn’t quite sure about what exactly it would be. As it turns out it is relational database for large amounts of database and really big queries as a service. This is essentially the equivalent of the APS (Analytics Platform System) in the cloud. Read more »
What is the difference between Clustered and Non-Clustered Indexes in SQL Server? August 28, 2017 by Ben Richardson Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow. Read more »
Hands on Full-Text Search in SQL Server August 25, 2017 by Jefferson Elias Introduction In most cases, we will use clustered and non-clustered indexes to help a query go faster, but these kinds of indexes have their own limitations and cannot be used for fast text lookup. For instance, a LIKE operator will lead SQL Server to scan the whole table in order to pick up values that meet the expression next to this operator. This means it won’t be fast in every case, even if an index is created for considered column. Read more »