All about SQL, tutorials, lists, cheat sheets, code samples

ROW_NUMBER() is one of the windows functions in the SQL server, used to the current number of rows in the result set based on an ORDER BY statement and sometimes based on an ORDER BY within a particular group. In SQL Server, the ROW_NUMBER() is a window function. In other words, it is also called an analytical function. This function is essential for various data breakdowns and interpretation. In this, it allocates a sequential number [...]

Scopes in SQL are different from languages like C#, C, Java, etc. If we talk about a particular language, say C#, then each statement in a block has a different scope. Typically, a variable is unrestricted and plays between when announced and the ending of the batch, function, or procedure that it is defined in. It is clear, but it is uncomplicated to overlook what comprises a batch. The scope of a variable is the [...]

The WAITFOR statement is used to stop the execution of the current transaction/Stored procedure until delay time or specified time. How to Use WAITFOR? It can be used with different combinations like DELAY, TIME, or till for service brokers to receive messages. If multiple WAITFOR statements are defined on the identical server, multiple lines can be connected up waiting for these statements to execute. SQL Server observes the number of WAITFOR statement lines and erratically [...]

WHILE keyword loops statement inside it, until condition specified by it satisfies. There are often chances for WHILE loop to go into an infinite loop, so we have to make sure to update necessary variables if any inside while block. Its syntax is very simple, as listed BELOW. Keyword CONTINUE is used if want to skip the rest of the statements in the WHILE loop and go directly to the condition evaluation part. BREAK keyword [...]

When it comes to decision-making, there are always conditions on which decisions are formed. In other words, there are If-Else. From Assembly languages to modern languages, it-else is and was a very common part of them. In real life also there are if-else when it comes to decision making if we take the example of the mindset of employees when it comes to getting a raise. He thinks, "if I got a good raise this [...]

In a closed world logic assumption, the outcome is always deterministic. It's either true or false. Let's say, for example, a question "Is 1 is less than 4?" always has the answer YES. If we ask the question "Is 1 is greater than 4?" the answer is always NO. Procedural languages are based on this principle, the outcome is always deterministic there. But we live in an open world. There is always the possibility of [...]

Take a look at the outline of a session by mva, conducted by Graeme Malcolm and Geoff Allix. All the examples shown in this article use the database AdventureWorksLT. T-SQL or Transact-SQL is a collection of programming parameters that insert various components to the Structured Query Language (SQL). Some of the components are transaction control, exception and error handling, and much more. All templates interact with SQL Server by transmitting T-SQL messages to the server. [...]

SQL Server has 5 system databases: resource master model msdb tempdb You can see these databases under "System Databases" in the management studio. Resource It's a read-only database containing all system objects. It is often referred to as a "resource" database, its actual name is "mssqlsystemresource". You don't see the "resource" database in SSMS's object explorer window, but system objects persisted in the resource database logically appear in every database on the server. In order [...]

Data is the soul of every organization. It is a necessity for practically every industry today. Yet, creating a robust data warehouse demands enormous computing ability to manage production and expeditious retrieval of data and conclusions. In extension, the company’s IT units demand to obtain new skills and foundation know-how to build and manage their BI systems. This article is all about how to replicate MySQL to Redshift. Let's take a look at it in [...]

This post gives a precise, out-and-out idea of analytic functions and their different choices through a range of easy yet concept-building cases. The post is designed for SQL coders, who might not be utilizing analytic functions due to a lack of understanding of its mysterious syntax or difficulty with its philosophy of action. This post presumes knowledge of essential Oracle SQL, sub-query, join, and group functions. Based on that knowledge, it creates the idea of [...]