[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Query Syntax

52K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

In this article, we will see how to use the RANK analytical function in an Oracle database and how it helps us in an analysis.
0
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

In this article, we will see how to use Order by Clause in Oracle with simple examples.
0
SQL Server, like all other major RDBMS, supports ANSI SQL, the standard SQL language. SQL Server also contains T-SQL and its implementation. SSMS- the SQL Server Management Studio known as Enterprise Manager earlier is SQL Server’s main interface tool supporting 32-bit and 64-bit environments.
0
An introductory discussion about how to use Partition by Clause in Oracle.
0
Generating sequential numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more. Here we will show how to accomplish this in several ways with either little or no code.
0
Generating random numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more work. Here we will show how to accomplish this with a few lines of code.
0
Restore Stored Procedure in SQL Server
In this article, we will discuss how to deal with a situation wherein you face an issue with a mandatory stored procedure in your critical database.
0
CA Premier Accounting Package
Copying and pasting of files will continue, at least for now. The front end MS Access 2016 and SQL Server 2016 back-end can be zipped together and emailed to clients like any other file without problems, I have thoroughly tested the detailed method below and can confirm that it works very well.
0
CA Premier Accounting Package
Migrating data tables from MS Access to SQL Server is not for faint-hearted people, you have to draw all your programming knowledge to a higher assumed expertise level. Be prepared to sort out programming issues at a higher level.
0
The process of ML is comparable to data processing. Both systems search through information to appear for patterns. However, rather than extracting information for human comprehension as just in case of knowledge mining, ML uses that information to discover trends in data and alter program actions.
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

This tutorial is a great guide for newbies in SQL Server who never backup the database and for people with some experience, because you will learn some tips that you may not know.
1
SQL Backup Repair
Know how to repair corrupted backup files using SysTools SQL backup Recovery, Also understand the reasons behind .bak file corruption in SQL Server.
0
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
0
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and consists of using a SQL Recovery Software.
2
LVL 55

Expert Comment

by:Mark Wills
Comment Utility
Good Article,

Even as a SQL Server "expert", always good to know about tools - especially when it can make life much easier.

Stellar Phoenix SQL Database Repair looks the goods.

Thanks for sharing :)

Cheers,
Mark Wills
3
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
0
Attach SQL Database
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
1

Expert Comment

by:Rejin James
Comment Utility
Thanks for such an informative article! It was of great help to me. Had already tried the 1st two methods which you had mentioned so I directly skipped to trying the software you recommended. Initially the software took about 35 minutes to scan my file, but the best part is that it showed all the records of my database. That moment was a huge sigh of relief to me since I was able to see the crucial data after spending almost 2 days repairing the .mdf file. Now that I have recovered my data, I can finally sleep in peace. Stellar SQL Database Repair is definitely a brilliant software!
0
MT DV MySQL
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
0
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, this article addresses the 'improvement'...
0
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began appearing in 2006-7 and as of MySQL version 8.0 and MariaDB version 10.2.2. 


Common Table Expression ( the WITH RECURSIVE … clause )

Recursive SQL is achieved by using Common Table Expression or CTE which is a temporary named resultset, derived from a simple query and defined within the execution scope of SELECT, INSERT, UPDATE or DELETE statements. CTE is often used to improve readability of a SQL code by simplifying and/or prise the code into more readable sections. One “feature” of CTE is the ability to reference recursively the named resultset, hence recursive SQL. 


With recursive SQL queries you can achieve things you would not have imagined being possible with this type of SQL and at the speed it executes. You can solve many types of business problems and even rewrite some complex SQL/application logic down to a simple recursiveSQL-call to the database. 

Some example of useful usage of recursive CTE is that you can use it to find gaps in data, create organization charts and create test data.

What I like most of using recursive SQL query is the ability to produce large amount of test-data in amount of seconds or few minutes, depending on complexity of the data and the available database/systems resources. Using recursive CTE we can produce many hundreds, thousands or millions of records in very short period of time which is then only limited to available database memory and other database/system resources. Recursive query produces test data way faster compared to other test-data procedures that I have seen and experienced.
Also, it has been proven that recursive queries outperforms other queries that take days to execute on huge amount of data by running in several minutes.


The word recursive says it all. You have a query that repeatedly calls itself with some starting point and that which is EXTREMELY IMPORTANT an ending point (a fail-safe exit as I call it). If you don't have a fail-safe exit or your recursive formula goes beyond it you are in deep trouble. Your query will go into aninfinite loop resulting in very high CPU and very high LOG utilization which will lead to memory and/or storage exhaustion. If your query goes haywire you must think very fast and stop it. If you are unable to do so then alert your DBA immediately so he/she can prevent the database system of choking by killing the runnaway thread. 


Examples

Let's look at some simple examples that shows how easy and effective it can be. 


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mydates
                                        where level < 100000
                                        )
                                        SELECT nextdate from mydates
                                        );


Here we produce 100k of  random dates. The level is our counter and fail-safe exit to safely exit the recursive query. The  line ( no 2 ) is our starting point and the line (no 4-5 ) is the recursive call with the ending point in the where clause ( no 6) . Then the last lines ( no 8 - 9 ) is the call to execute the recursive query and retrieve the data. 

The above query executed in 0.84 sec and outputted 100k of rows. Note that even though you put a LIMIT 10   on the query  as shown below it will first produce the 100k rows and then limit the result to the first 10 rows in the same amount of time or 0.84 sec


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mydates
                                        where level < 100000
                                        )
                                        SELECT nextdate from mydates
                                        ) LIMIT 10;


You can also create a table and by that physically store the data by surrounding the CTE query like this. 


create table somedates as (
                                        with RECURSIVE mynumbers (level,nextdate) as (
                                        select 1 level, FROM_UNIXTIME(RAND()*2147483647) nextdate from DUAL
                                        union all 
                                        select level+1, FROM_UNIXTIME(RAND()*2147483647) nextdate
                                        from mynumbers
                                        where level < 100000
                                        )
                                        SELECT nextdate from mynumbers
                                        );

This query ran on my laptop in 2.93 seconds storing 100k rows of dates in a table called somedates.


Another simple example, a “gaps in data” solution,  which produces last 1000 dates to current date and compares it to the order table to find all dates that don't have any orders showing the newest date first.

For this example I created 1 million rows with random orderdates using recursive SQL in 32.95 seconds. Created index on (orderdate,orderid) on the the table in 9.12 seconds, deleted thousands of random rows to create a gap and ran the below query


with RECURSIVE mydates (level,nextdate) as (
                                        select 1 level, ADDDATE(CURRENT_DATE,INTERVAL -1000 DAY) nextdate from DUAL
                                        union all 
                                        select level+1, ADDDATE(nextdate,INTERVAL 1 DAY) nextdate
                                        from mydates
                                        where level < 1000
                                        )
                                        SELECT md.nextdate from mydates md left join myorders od on md.nextdate = od.order_date
                                        where od.order_date is null order by md.nextdate desc;


This query ran on my test data and looked for gaps in the last 1000 days from current date and returned 68 dates in 0.05 seconds


Conclusion

Here I have talked briefly about recursive queries and showed how useful and powerful it can be to solve problems in very short and effective way. However, when writing the recursive query be VERY careful NOT to forget the fail-safe exit and make sure you double check twice that the calculations used in the fail-safe exit produces a stop signal. 

0
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
7
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
3

Expert Comment

by:Hari Shankar
Comment Utility
Great Article. Thank u Vitor
1
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this solution, while refreshing my recursive CTE skills.
0
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
0

Expert Comment

by:Gregorio Méndez
Comment Utility
Mr. Strauss, what a good tip, thanks a lot for your contribution
1
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
2

Query Syntax

52K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.