Top 25 interview questions and answers about SQL Server indexes June 25, 2018 by Ahmad Yaseen Q1: What is the difference between a Heap table and the Clustered table? How could we identify if the table is heap? A Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index. Read more »
Database Filegroup(s) and Piecemeal restores in SQL Server June 22, 2018 by Prashanth Jayaram So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process. Read more »
SQL Server security considerations with open source tools June 21, 2018 by Timothy Smith As our company has grown, we’ve recently added developers to our team who want to use open source tools (open source languages and libraries). In the past, we built and used our own custom libraries, but our new developers to prefer to use open source libraries or add new languages that require new libraries. We’re concerned that the use of open source libraries may not be secure and may introduce new inputs and outputs in our system that we don’t fully understand. What should we consider when we think about allowing open source software, tools or languages in our environment from the standpoint of security? Read more »
Contribute, contribute, contribute! June 20, 2018 by Craig Porteous A good friend of mine and co-speaker at SQLGrillen this year, Paul Broadwith (@Pauby), said this during a practice run of our presentation, not in a Steve Ballmer way thankfully. Although we were talking about open source projects at the time, the concept applies to blogging, organising and speaking at user groups or conferences, everything Community related really. Read more »
Query optimization techniques in SQL Server: tips and tricks June 19, 2018 by Ed Pollack Description Fixing bad queries and resolving performance problems can involve hours (or days) of research and testing. Sometimes we can quickly cut that time by identifying common design patterns that are indicative of poorly performing TSQL. Read more »
How to automatically create KPIs in SQL Server Reporting Services June 18, 2018 by Esat Erkec Key Performance Indicator (KPI) A Key Performance Indicator aka KPI is a metric which objectively measures the numeric equivalent of goals or targets of a company, team or an organization. Any individual or a community which has a numerical target can measure success of their goal with key performance indicators. In all business roles and levels, if you manage a team you most likely have to set at least one key performance indicator and try influence this metric to show a positive result or trend. KPIs should be both logical and achievable. If not, team motivation and morale may decrease as a result of loss of self-confidence. For this reason, we have to be careful and logical when we set key performance indicators. At the same time, we have to check KPIs at regular intervals to determine if they remain valid. We can illustrate a typical KPI life cycle below. Read more »
How to implement error handling in SQL Server June 15, 2018 by Bojan Petrovic Error handling overview Error handling in SQL Server give us control over Transact-SQL code. For example when things go wrong we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc. Read more »
SQL Server Database Backup and Restore reports June 14, 2018 by Prashanth Jayaram In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file. In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management. Read more »
How to handle the SQL Server WRITELOG wait type June 13, 2018 by Nikola Dimitrijevic The WRITELOG wait type is one of those wait types that can often be seen quite frequently on SQL Server, and that can cause a lot of headaches for DBAs. The WRITELOG wait time represents the time that accumulates while waiting for the content of the transaction log cache to be flushed to the physical disk that stores the transaction log file. To understand better the WTITELOG wait type, there are some basics of SQL Server mechanism for storing the data in the transaction log file is to be explained first Read more »
Top 50 collection of articles on SQL Server interview questions June 12, 2018 by SQLShack This is the list of top 50 articles containing the most common SQL server interview questions, ordered by Alexa rankings (at the time of posting). Enjoy your interview preparation and good luck with your next interview! Read more »
How to “debug” a Linked Server from SQL Server to an Oracle Database instance June 11, 2018 by Jefferson Elias Introduction Intended audience This document is intended for database administrators who would like to be prepared or who experience performance issues with one or more Linked Server(s) to an Oracle Database instance using the OraOLEDb Linked Server provider. Read more »
Partial stored procedures in SQL Server June 8, 2018 by Gerald Britton Introduction SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility. Read more »
The Difference between CROSS APPLY and OUTER APPLY in SQL Server June 6, 2018 by Ben Richardson SQL Server supports table valued functions, what are functions that return data in the form of tables. JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function. APPLY operators are used for this purpose. Read more »
SQL Server performance counters (Batch Requests/sec or Transactions/sec): what to monitor and why June 5, 2018 by Nikola Dimitrijevic When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two Read more »
How to Sync Azure SQL databases and on-premises databases with SQL Data Sync June 4, 2018 by Marko Zivkovic SQL Data Sync allows you to synchronize data across multiple Azure SQL databases and on-premises SQL Server databases. In this article, how to synchronize Azure SQL database with on-premises SQL Server database will be shown. Read more »
Data science, data understanding and preparation – basic work with datasets June 4, 2018 by Dejan Sarka In my previous four articles, I worked on a single variable of a dataset. I have shown example code in T-SQL, R, and Python languages. I always used the same dataset. Therefore, you might have gotten the impression that in R and in Python, you can operate on a dataset the same way like you operate on an SQL Server table. However, there is a big difference between an SQL Server table and Python or R data frame. Read more »
What is SQL Data Sync June 1, 2018 by Marko Zivkovic SQL Data Sync is a service that allows synchronizing data across multiple Azure SQL databases and on-premises SQL Server databases. In this article, a base concept of how the SQL Data Sync service works will be explained as well as what the requirements and limitations are when want to create data synchronization by using SQL Data Sync Read more »
Tail-Log Backup and Restore in SQL Server May 31, 2018 by Prashanth Jayaram A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence. Read more »
Maintaining SQL Server indexes May 31, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of SQL Server tables and indexes, the guidelines that you can follow in order to design a proper index, the list of operations that can be performed on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes, the different types of SQL Server indexes (above and beyond Clustered and Non-clustered indexes classification), how to tune the performance of the inefficient queries using different types of SQL Server Indexes and finally, how to gather statistical information about index structure and the index usage. In this article, the last article in this series, we will discuss how to benefit from the previously gathered index information in maintaining SQL Server indexes. Read more »
Query optimization techniques in SQL Server: the basics May 30, 2018 by Ed Pollack Description Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem! Read more »
