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 »
Data conversion is one of the most fundamental tasks of any programming language. Data received from different sources is often not in the right format. For example, if you receive an XML file where age is in the string format and you want to calculate an average age for the people in the file you will need to convert age into an integer.
To make the conversion process simple, the TRY_PARSE and TRY_CONVERT functions were introduced in SQL Server 2012. Before TRY_PARSE and TRY_CONVERT, SQL Server only had the PARSE and CONVERT functions.
Read more »
One of the paramount ways to guard data within a database is to utilize database encryption. However, no one encryption solution is perfect for all databases. Which encryption solution you select totally depends on the requirements of your application. Note that more powerful encryption for larger amounts of data requires a healthy amount of CPU. So, be prepared in the event that that introduction of encryption increases the system load.
Read more »
Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.
Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.
This article will take a detailed look at sequence objects.
Read more »
This collection of content will provide an overview of SQL formatter tools, with product page links for each of the tools. If you noticed a SQL formatter tool we missed, please let us know in the comments below.
Read more »
Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how can we store images in SQL Server?
We could create an application in .NET or Java, but if we do not have experience in those programming languages, we could use SQL Server tools to do it.
In this new article, we will learn the following tips that will help us to work with images including how to:
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 »
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 »
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
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 »
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 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 »
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 »
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 »
This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.
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 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 »
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 »
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 »
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 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 »
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 »
This article will provide an overview covering programmatically moving databases on the Azure Portal while avoiding common problems with users and logins.
Read more »
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 »
Context
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 »© 2022 Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy
