This video is about Window Functions in SQL which is also referred to as Analytic Function in some of the RDBMS. SQL Window Functions covered in this video are RANK, DENSE RANK, ROW NUMBER, LEAD, LAG. Also, we see how to use SQL Aggregate functions like MIN, MAX, SUM, COUNT, AVG as window function.
This video is focused on teaching how to write SQL Queries using different window functions or analytic functions. We go through the syntax of using rank, dense_rank, row_number, lead, lag and max functions as window function.
Over clause is explained in detail in this video. Over clause is used in SQL when we need to use window function. Inside Over clause, we also use Partition By clause and also Order by clause.
Partition By clause is used to specify the column based on which different windows needs to be created.
The window function you learn in this video is applicable to any RDBMS since these functions are commonly used across most of the popular RDBMS such as Oracle, MySQL, PostgreSQL, Microsoft SQL Server etc.
01:33 Understanding Aggregate function
03:16 Syntax to write SQL Query using Window Function
06:33 ROW_NUMBER() Window Function in SQL
11:57 RANK() Window Function in SQL
15:43 DENSE_RANK() Window Function in SQL
17:10 Difference between RANK, DENSE RANK and ROW NUMBER in SQL
17:59 LEAD() and LAG() Window Function in SQL
🔔 Subscribe: https://www.youtube.com/c/techTFQ/featured
Using “sys.objects,” we can get a list of tables and stored Procedures created in the Database. SQL Server now supports table-valued parameters, which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API. Using SYSOBJECTS and SYSDEPENDS, we can get all the tables, stored procedures, and other database object-related information.
You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects. It contains a row for each user-defined, schema-scoped object that is created within a database. sys.objects does not show DDL triggers because they are not schema-scoped. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. It was not possible to pass a table variable as a parameter to a stored procedure in old versions of SQL Server.
#sql server #sql #t-sql
The main idea around the SQL Server function called STUFF is concatenating multiple columns into a single column with more flexibility than the CONCAT function would provide. Besides, STUFF can be combined with other techniques for some interesting effects.
In this article, we’ll explore the possibilities that the STUFF command provides for SQL Database specialists.
Let’s first take a look at the official Microsoft definition of the STUFF function:
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Thus, when you are using the STUFF command, you are rearranging the column data.
#sql server #sql functions #t-sql #sql
Recently I found a quite common request on StackOverflow. Generalizing the problem, it can be described as the requirement of insert some data into a table only if that data is not there already.
Many developers will solve it by trying to execute two steps:
This approach has a flaw, whatever the database you are using and no matter the database if relational or not. The problem, in fact, lies in the algorithm itself.
The moment you have two actions, where one depends on another, you need to make sure that the data used by both doesn’t change in the meantime because of some other action done by someone else. As you can easily understand, in fact, if you have done the first action, you really need to be sure that in-scope data doesn’t change before you can do the second action, otherwise the result may be wrong or inconsistent.
You need a transaction, that will provide the needed isolation so that interference won’t happen.
As the chosen algorithm is comprised of two separate steps, you must create a transaction big enough to keep both steps under the same umbrella, so that the two physical separate steps will logically work like one. This behavior is also known as atomicity: the two steps are indivisible. Everything works and changes will be persisted, or nothing works and changes will be undone.
While this will work perfectly from a functional perspective, if you are in a highly concurrent system and you need to have a very scalable solution where an extreme number of executions can be done in parallel, you really want to have such protection around your transaction – the isolation – for the shortest time possible, as for as long as you are using that data in your algorithm, other may have to wait for accessing it, to avoid interfering with your activity.
How to solve this problem elegantly and without having such big transactions? One option is use what is defined as “Optimistic Concurrency”. This approach uses a resource version token – for example, an ETag – to make sure that data didn’t change between the first and the second step. If data was changed, you simply restart from step 1 and loop continuously until you manage to complete the algorithm or you reach the maximum number of attempts allowed.
Now, that’s a very basic approach. It works, but I think we can do better. Much better.
What if, for example, we can do both steps in just one command? No need for big transaction, no need for less-then-optimal loops.
With Azure SQL, doing that is easy: you can
INSERT a row into a table using the result of a
SELECT on that table. Does it start to ring a bell?
By using an
INSERT``...``SELECT command, we can achieve exactly what is needed. One command, without any explicit transaction. Let’s say we have a table, named tags, that stores all the tags associated with a blogs post. A tag can be used in different posts, but only once per post. The table would look like the following:
create table [dbo].[tags] ( [post_id] int not null, [tag] nvarchar(50) not null, constraint pk__tags primary key clustered ([post_id], [tag]) )
Using such table as example, an
INSERT``...``SELECT to implement the insert-if-not-exists logic would look like:
insert into [dbo].[tags] ([post_id], [tag]) select * from ( values (10, 'tag123') -- sample value ) as s([post_id], [tag]) where not exists ( select * from [dbo].[tags] t with (updlock) where s.[post_id] = t.[post_id] and s.[tag] = t.[tag] )
SELECT will create a virtual table with the data we want to insert. One or more rows can be created with that technique (it works very nicely up to a few hundred rows. If you need more rows then JSON, Table Valued Parameters or Bulk Insert are a better choice). The virtual table will be called
s and will have two columns:
tag. Data types will be automatically inferred; if you want to have some specific data type, you can always
CAST the value to make sure the data type you want will be used. The
UPDLOCK is a hint to tell Azure SQL that we are reading with the goal to update the row. By allowing the engine to know that, the internal mechanism of lock conversion can be optimized to guarantee the best concurrency and consistency.
WHERE clause will make sure only those rows that’s doesn’t already exists in the target table –
tags – will be returned from the virtual table and passed to the
INSERT statement will do exactly what it says: insert rows into the
tags table, if any.
#azure sql #development #t-sql #transaction #solution
In this article, we will explore SUBSTRING, PATINDEX and CHARINDEX string functions for SQL queries.
While working with the string data, we perform various calculations, analytics, search, replace strings using SQL queries. SQL Server provides many useful functions such as ASCII, CHAR, CHARINDEX, CONCAT, CONCAT_WS, REPLACE, STRING_AGG, UNICODE, UPPER for this purpose. In this article, we explore SUBSTRING, PATINDEX, and CHARINDEX using examples.
The SUBSTRING() function extracts the substring from the specified string based on the specified location.
Syntax for SUBSTRING() function:
SUBSTRING(expression, starting_position, length)
#sql commands #string functions #t-sql #sql
Using keyboard shortcuts, it saves time, isn’t it?. SQL Server Management Studio is also not an exception !!!. We can add our own custom keyboard shortcuts, that too for T-SQL query. Really cool !!! How many of you know about this feature???. Do not worry if you did not know this before. Now you are in the right place. In this article we will discuss about adding custom keyboard shortcuts for T-SQL Statements.
Support when we are working in SQL Server and one T-SQL Statement is frequently used almost everyday, instead of keeping it as a .sql file, we can add it as a shortcut key. So whenever required just press those keys.
#sql server #query shortcuts #sql #ssms #t-sql
In this episode of Data Exposed, Kate Smith walks us through the process of how to create Elastic Jobs for Azure SQL using T-SQL.
#azure sql #aft-sql #t-sql #sql
In this article, we will learn how to use SQL SELECT TOP queries and we will also reinforce this learning using examples.
The TOP clause allows us to limit the result set of the queries according to the number of rows or the percentage of rows. In general, the TOP and ORDER BY construction are used together. Otherwise, the TOP clause will return the N number of rows in an uncertain order. For this reason, it is the best practice to use the TOP clause with an ORDER BY to obtain a certain sorted result.
The syntax of the TOP clause is as follows:
SELECT TOP (expression) [PERCENT] [WITH TIES] FROM table_name
#sql commands #t-sql #sql
.NET Developers are perhaps familiar with libraries like Roslyn and CodeDOM, which allow in-depth analysis of code (amongst many other things). Java developers use tools like SpotBugs for code analysis. As a SQL developer, perhaps you might have wondered if there are any equivalent functionality for Azure SQL and SQL Server code? Well, wonder no more!
Over the years, I have leveraged the very useful TransactSql ScriptDom .NET library, to programmatically parse Transact-SQL (T-SQL) statements, expressions, and scripts. This library accurately produces an Abstract Syntax Tree (AST) representation of the T-SQL code. Once you have such an AST, you can “walk” the tree to analyze it for various code patterns. As an example, you can use it to detect in a robust way if you have any DML statement other than SELECT – which may be useful to detect some forms of SQL injection. You can even change the AST if needed and produce a modified T-SQL script with the re-written tree. The possibilities are endless!
#azure sql #devops #modernsql #t-sql #sql #dotnet
Are you are familiar with the classic “river crossing” problem? If not, an easy 4 minutes introduction using Wildebeest and Lions as the actors in the problem, is presented in this TED-Ed talk. If you observe carefully, the video briefly mentions using an enumeration approach to list all the possible transitions, and for each transition, the end state. It so happens that this is the classic algorithmic approach as well to solve such problems – to create a graph of all possible states (represented by vertices / nodes in the graph) and the transitions (represented by edges) which result into those states. If you search online, there are plenty of references showing you how to use Breadth-First-Search (BFS) to find the shortest path to solve the problem. David Kopec’s Classic Computer Science Problems books also present implementations of this problem in Python and other languages.
Let’s see if we can solve this classic problem, using T-SQL and the graph capabilities in Azure SQL / SQL Server!
Let’s present the main “rules” of this problem, as presented in the TED talk:
#azure sql #graph #t-sql #sql
In this article, we will explore User-defined SQL Server Types and their usage.
SQL Server supports various data types for storing different kinds of data. These data types store characters, numeric, decimal, string, binary, CLR and Spatial data types. Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.
Here the data types are in different groups.
You can explore my earlier article An Overview of SQL Server Datatypes for understanding these data types and their ranges.
#sql commands #t-sql #sql #sql server
SQL Essentials stairway series helps readers understand the most common database language SQL uses for data munging and data wrangling. SQL Essentials articles is a collection of SQL standards and best practices and help you take you in step closer to learn database query.
Information Technology is unimaginable without the relational database model. The very backbone of IT is data, and SQL is one of the best ways available out there, to manage it. By choosing this stairway series “SQL essentials for beginners”, you have taken your first step in understanding the industry from a data perspective and your career journey is going to be nothing short of exciting. This introduction to SQL is aimed at giving you that right launch.
This article introduces the basic and important features of SQL in SQL Server. This guide demonstrates the working of SQL starts from creating tables, defining relationships, and writing Transact-SQL (T-SQL) commands, and so on.
As is the tradition, we will take a few glimpses at the history of SQL, look at what makes SQL so powerful, and introduce ourselves to the different types of commands and data types.
Ready to dive-deep into SQL essentials articles? Let’s go!
#sql commands #t-sql #sql
Replication is one of the oldest technologies on MS SQL Server, loved by every database administrator. It is a great and reliable technology for bringing data closer to users, especially for distributed reporting. Thanks to it, the database availability increases across multiple SQL Servers and regions.
Replication was introduced in SQL 2005. Can you believe it’s that old? Though newer managed SQL platforms on the cloud are introduced regularly, I believe that SQL replication will remain here. If it was a bug or insect, I would think of it as a cockroach. It’s hard to squash!
If you are one of those belonging to a small population of administrators who never managed a database, there is official Microsoft documentation on the topic. However, note that is it pretty long, comprehensive and will rob you of some time off from holiday or planned binge-watching TV series. Also, Codingsight offers the SQL Server Database Replication setup and configuration guide.
But before you get your hands dirty with the technical stuff, and I know you’re eager too, it’s important to plan for it.
The replication requirement may change with regards to location when you deploy to SQL Servers running on the cloud. But once the SQL replication is running like a well-oiled machine and replicating production data, you need to plan how you manage it.
In this post, I will share some tips and T-SQL scripts for you to use when you need to check many SQL Agent jobs are created after the replication configuration.
#sql server #replication #sql agent jobs #t-sql #sql
In the previous articles we’ve talked about SQL injection and dynamic SQL but we lacked an answer on how to prevent SQL injection attacks. Today we’ll do exactly that and show you one possible approach to how to do it. We’ll also mention other possible threats and approaches you could take.
Nothing had changed in our model since the last article, so we’ll use the same model we’ve used so far.
In this article, we won’t focus so much on data, but rather on the code we could use to prevent SQL injection attacks. This code could be easily modified to fit your needs, or you could add some checks in case we missed some of them.
#sql commands #t-sql #sql #sql injection
Dynamic SQL is a programming technique you can use to build SQL statements as textual strings and execute them later. This technique could prove to be useful in some cases and therefore it’s good to know we have it as an option. In today’s article, we’ll show how to create and execute dynamic SQL statements.
The data model we’ll use in this article is the same one we’re using throughout this series. Nothing changed since the last article, so we won’t lose time explaining it.
In the previous article, Learn SQL: SQL Injection, we’ve talked about SQL injection and showed a few examples of how dynamic SQL can be used, but still, we missed explaining what this technique is. In this article, we’ll fix that.
#sql commands #t-sql #dynamic sql #sql