T-SQL

T-SQL

T-SQL is the extension of SQL functionality supported by Sybase ASE and Microsoft SQL Server.
Coding  Life

Coding Life

1625139960

SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial

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.

Timestamp:
00:00 Intro
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

#t-sql #sql

SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial
Karlee  Will

Karlee Will

1623900780

Fetch T-SQL With Passed Parameter Value Using Stored Procedure

This tutorial explains how to fetch T-SQL with passed parameter value using stored procedure.

Introduction

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.

Description

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

Fetch T-SQL With Passed Parameter Value Using Stored Procedure
Karlee  Will

Karlee Will

1623843480

T-SQL Stuff Command on Practical Examples

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.

The T-SQL STUFF Function

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

T-SQL Stuff Command on Practical Examples

The “insert if not exists” challenge: a solution

The Challenge

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:

  • check if the data exists already,
  • if not, insert it

The issue

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.

The solution, in principle

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.

A smarter solution

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] 
)

The first 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: post_id and 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.

The 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.

The INSERT statement will do exactly what it says: insert rows into the tags table, if any.

#azure sql #development #t-sql #transaction #solution

The “insert if not exists” challenge: a solution
Karlee  Will

Karlee Will

1623075120

SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries

In this article, we will explore SUBSTRING, PATINDEX and CHARINDEX string functions for SQL queries.

Introduction

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.

SUBSTRING function in SQL queries

The SUBSTRING() function extracts the substring from the specified string based on the specified location.

Syntax for SUBSTRING() function:

SUBSTRING(expression, starting_position, length)

  • Expression: In this argument, we specify a character, binary, text, ntext, or image expression
  • starting_position: It contains an integer or bigint expression. It defines the starting position from where we extract the substring. The first character in the string starts with the value 1
  • Length: It is a positive integer value that defines how many characters from the string, from the starting_position, we want to retrieve

#sql commands #string functions #t-sql #sql

SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries
Karlee  Will

Karlee Will

1623051608

Creating T-SQL Query Shortcuts in SQL Server Management Studio(SSMS)

Introduction

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.

Add T-SQL Query as Keyboard Shortcuts

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

Creating T-SQL Query Shortcuts in SQL Server Management Studio(SSMS)
Chesley  Wehner

Chesley Wehner

1622557200

SQL SELECT TOP statement overview and examples

In this article, we will learn how to use SQL SELECT TOP queries and we will also reinforce this learning using examples.

Introduction

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

SQL SELECT TOP statement overview and examples
Karlee  Will

Karlee Will

1622553300

Programmatically parsing Transact SQL (T-SQL) with the ScriptDom parser

.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

Programmatically parsing Transact SQL (T-SQL) with the ScriptDom parser
Chesley  Wehner

Chesley Wehner

1622549940

Solving the River Crossing problem with SQL Graph

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!

Governing Rules

Let’s present the main “rules” of this problem, as presented in the TED talk:

  1. There are 3 wildebeest and 3 lions, all located on one bank (for completeness, we have the West bank, and the East bank) of a river. They are trying to cross the other river bank.
  2. As it turns out, wildebeest are a natural prey for the lions. Therefore, there is a “rule” which states that if the number of wildebeest either equal, or outnumber the number of lions, on the same bank of the river, they are safe. In our context, we will call this a “safe” state.
  3. Conversely, If the number of wildebeest are lesser than the number of lions, they are unsafe. In our context, we will refer to this as an “unsafe” or “disallowed” state, as we don’t want the wildebeest to come to harm!
  4. Let’s also imagine, that these animals know how to use a raft, to cross the river However, the raft can only take 2 animals at any time, and obviously, the raft is not “autonomous”, and needs an animal or 2 animals on board to move from one bank of the river to the other.
  5. At any given point in time, rules 2 and 3 must continuously be applied, even when 2 animals are on the raft!

#azure sql #graph #t-sql #sql

Solving the River Crossing problem with SQL Graph
Chesley  Wehner

Chesley Wehner

1622542380

An Overview of User-defined SQL Server Types

In this article, we will explore User-defined SQL Server Types and their usage.

An Overview of System Data Types

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.

System Data Types

Here the data types are in different groups.

  • Exact Numeric: bit, Tinyint, Smallint, Int, Bigint, Numeric, Decimal, SmallMoney, Money.
  • Approximate Numeric: float, real
  • Data and Time: DateTime, Smalldatatime, date, time, Datetimeoffset, Datetime2
  • Character Strings: char, varchar, text
  • Unicode Character strings: Nchar, Nvarchar, Ntext
  • Binary strings: binary, Varbinary, image
  • Other Data types: sql_variant, timestamp, Uniqueidentifier, XML
  • CLR data types: hierarchyid
  • Spatial data types: geometry, geography

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

An Overview of User-defined SQL Server Types
Chesley  Wehner

Chesley Wehner

1622152200

Stairway to SQL essentials

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

Stairway to SQL essentials
Karlee  Will

Karlee Will

1622111640

Managing your Replication in SQL Server

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

Managing your Replication in SQL Server
Chesley  Wehner

Chesley Wehner

1621611360

Learn SQL: How to prevent SQL Injection attacks

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.

Data Model

Nothing had changed in our model since the last article, so we’ll use the same model we’ve used so far.

the data model

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

Learn SQL: How to prevent SQL Injection attacks
Chesley  Wehner

Chesley Wehner

1621550460

Learn SQL: Dynamic SQL

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.

Data Model and a Brief Introduction

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.

the data model

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

Learn SQL: Dynamic SQL