This article is a supplement to the previously published article, An overview of SQL String Functions . This time, we will describe another bunch of SQL string functions used in SQL Server.
Read more »
How to detect and prevent unexpected growth of the TempDB database
January 13, 2020In this article, we will discuss the best practices that should be followed in order to keep the TempDB database in a healthy state and prevent any unexpected growth of the database, in addition to the procedure that can be followed to detect this unexpected growth once occurred.
Read more »
Learn SQL: SELECT statement
January 13, 2020The SELECT statement is probably the most important SQL command. It’s used to return results from our database(s) and no matter how easy that could sound, it could be really very complex. In this article, we’ll give an intro to the SELECT statement and we’ll cover more complex stuff in the upcoming articles.
Read more »
Linking relational databases with OLAP cubes
January 10, 2020One of the most asked questions is how to link an OLAP cube with a relational database, or in another way how to join the result of an MDX query with a table stored in a relational database.
Read more »
Using SQL CREATE INDEX to create clustered and non-clustered indexes
January 10, 2020The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A book index may have a list of topics discussed in a book in alphabetical order. Therefore, if you want to search for any specific topic, you simply go to the index, find the page number of the topic, and go to that specific page number. Database indexes are similar and come handy. Particularly, if you have a huge number of records in your database, indexes can speed up the query execution process. There are two major types of indexes in SQL Server: clustered indexes and non-clustered indexes.
Read more »
An overview of sp_getapplock and sp_releaseapplock stored procedures
January 9, 2020This article explains about the sp_getapplock and sp_releaselock stored procedures and their usage with example.
Read more »
SQL Server Linux installation for Amazon EC2 instance with Red Hat OS
January 9, 2020This article installs SQL Server on Linux on the Amazon EC2 instance with Red Hat OS prepared in the earlier article.
Read more »
Introduction to the sp_executesql stored procedure with examples
January 9, 2020The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes. For example, when we want to determine the displayed columns in our reports, this procedure might be a solution option for us. In the simplest sense, this procedure takes a dynamically constructed SQL batch and other parameters, then execute it in the runtime and, finally, it returns the result.
Read more »
Install and configure the AdventureWorks2016 sample database
January 9, 2020This article explains the process of installing the AdventureWorks2016 and AdventureWorksDW2016 sample database on a stand-alone instance of SQL Server and Azure SQL Server. The sample databases were published by Microsoft to demonstrate how to design a database using SQL Server. Microsoft has also published another lightweight database named AdventureworksLT, which can be used as a sample database on Azure SQL Server.
Read more »
SQL Unit Testing Data Warehouse Extracts with tSQLt
January 7, 2020This article talks about basic concepts of SQL unit testing from a Data Warehouse point of view using tSQLt, a highly productive and acclaimed SQL unit testing framework.
Read more »
Learn SQL: Foreign Key
January 7, 2020In the previous article, we talked about the primary key (PK). In this one, we’ll check what is the foreign key (FK) and how it’s related to the primary key. This is the final step, after creating tables, inserting data, and defining primary keys, to cover all basics related to the database theory. There is much, much more than this, but if you understand these concepts, everything else is just expanding your knowledge around these basics.
Read more »
Encrypting passwords with Python Scripts in SQL Notebooks of Azure Data Studio
January 6, 2020This article explores the Python scripts for encrypting and decrypting passwords in SQL Server using Azure Data Studio SQL Notebooks.
Read more »
Best author award in 2019
January 3, 20202019 was a great year for the SQL Shack community, and 2020 is shaping up to be just as exciting. Thanks to our writers – SQL professionals and community members, we continue to spread the knowledge around the SQL community.
Read more »
An overview of the STRING_AGG function in SQL
January 3, 2020In this article, we will explore the STRING_AGG function in SQL and we will also reinforce our learning with various examples. STRING_AGG function can be counted as a new member of the SQL aggregation functions and it was announced in SQL Server 2017. STRING_AGG function gathers all expression from rows and then puts it together in these rows expression in order to obtain a string. Additionally, this function takes a separator parameter that allows separating the expressions to be concatenated.
Read more »
How to fix the “Agent XPs disabled” error
December 27, 2019This article explains how we can fix SQL Server error “Agent XPs Disabled”. Before we dive into troubleshooting and methods for fixing the error, let me explain about Microsoft SQL Server Agent and the Agent XPs configuration parameter.
Read more »
Rollback SQL: Rolling back transactions via the ROLLBACK SQL query
December 26, 2019The rollback SQL statement is used to manually rollback transactions in MS SQL Server.
Read more »
Use Python SQL scripts in SQL Notebooks of Azure Data Studio
December 25, 2019This article explores the Python SQL scripts in SQL Notebook of Azure Data Studio.
Read more »
SQL Injection: Introduction and prevention methods in SQL Server
December 25, 2019A SQL injection attack is one of the most commonly used hacking techniques. It allows hacks to access information from a database that is otherwise not publically accessible.
Read more »
An overview of SQL String Functions
December 25, 2019In this article, we will try to give a brief overview of the SQL string functions used in SQL Server and we will provide some examples.
Read more »
Using sp_executesql stored procedure for executing dynamic SQL queries
December 24, 2019The sp_executesql stored procedure is used to execute dynamic SQL queries in SQL Server. A dynamic SQL query is a query in string format. There are several scenarios where you have an SQL query in the form of a string.
Read more »
Understanding the SQL EXCEPT statement with examples
December 23, 2019The SQL EXCEPT statement is one of the most commonly used statements to filter records when two SELECT statements are being used to select records.
The SQL EXCEPT statement returns those records from the left SELECT query, that are not present in the results returned by the SELECT query on the right side of the EXCEPT statement.
Read more »
Overview of SQL Server Clustered indexes
December 23, 2019This article targets the beginners and gives an introduction of the clustered index in SQL Server.
Read more »
Learn SQL: Primary Key
December 20, 2019If you’ve already worked with databases, then you could hardly miss the term – Primary Key (PK). And if you’re reading this series and use it to learn about databases, well, this article should give you a good overview of what the PK really is. Still, if you’re a database expert, maybe you’ll find something new or just refresh your knowledge. So, sit back, relax, and let’s dive into PKs.
Read more »
Replace a SQL While loop and a cursor with ranking functions in SQL Server for better query performance
December 19, 2019SQL While loop and cursor are the most common approach to repeat a statement on condition-based or determined limits. Loop and cursor can be utilized in a circumstance to deal with row-based processing in T-SQL. We generally observe moderate execution of old made procedures, which are composed of using loop and cursors. Those procedures take time, especially when the number of iteration count is big for the execution.
Read more »
Send an alert when the database state changes to SQL Server single-user mode
December 18, 2019This article explains the SQL Server single-user mode and how to configure an automated email alert when the state of the user database changes to the single-user mode. To send an alert, we will create a DDL trigger that executes when the user runs the ALTER DATABASE query. It collects the pieces of information using EVENTDATA() function, saves it in a temporary table, and sends an HTML formatted email.
Read more »