Learn how to a build a cloud-first strategyRegister Now

x

SQL

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as well as instant password recovery. 
0
Restore individual SQL databases with ease
LVL 1
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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

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

Expert Comment

by:Hari Shankar
Comment Utility
Great Article. Thank u Vitor
1
Windocks web UI
Windocks is an independent port of Docker's open source to Windows. This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
0
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
Visualize your virtual and backup environments
LVL 1
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
0
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return different results based on different supplied parameters.
0
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
2
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives basic information about SQL injections
4
Load multiple files in SSIS
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even we can use SSIS to download files from FTP server.
0
By Mark Wills

Formatting dates and times has aways presented a few challenges in T-SQL. Seems most other languages support a FORMAT command.

Well, time to rejoice because (finally) MS SQL 2012 (and more recent) now supports the FORMAT function. One little "gotcha" is you need .Net Framework because it leverages CLR. Which also means that it cannot be remoted (which is nothing new for CLR runtime).

So, with that out of the way, and for a large majority of T-SQL requirements, it is like any other system function. Probably easiest to show you :)

First, some dummy data. Create an order table with date and price columns, then populate :-
CREATE TABLE EE_tbl_Orders (CUSTOMER varchar(20),ORDER_DATE datetime, ORDER_PRICE decimal(10,4));


INSERT INTO EE_tbl_Orders (CUSTOMER, ORDER_DATE, ORDER_PRICE) VALUES ('Fred','2015-08-20 20:30:40',1234.5678);

Open in new window


The structre of the command is FORMAT ( value, format_str [], culture ] ) and can be found at MSDN Now, [culture] is optional and I tend to leave it out so it can default to the way my server / systems are set up.

So, with our Order Table, let us experiment with our new command...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), Order_Price
FROM EE_tbl_Orders


-- Results

-- Fred      2015 Aug 20      1234.5678

Open in new window


Now, we can also do something with that price as well (and note the rounding)...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), FORMAT(Order_Price,'C')
FROM EE_tbl_Orders
 
-- Results
-- Fred      2015 Aug 20      $1,234.57

Open in new window

8
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Darn handy, especially for those that are stuck supporting 2008R2 databases.  Voted Yes.
0
By Mark Wills

Not so long ago, there was a fairly tricky question on Experts Exchange to do with unstringing a field, and some very good answers. Not wanting to be left out of the "fray", I submitted one of my old tricks. Despite the code being around for a while in various guises, my posting was met with intrigue and various superlatives. Naturally, I felt rather EElated at the response, though, cannot take all the credit.

So, I thought I would share some of those tricks that center around the use of a rather special table in MS SQL Server known as spt_values in the master database.

First, what is spt_values ?

It has been around since the beginning, it is not a system table, but is very much a part of SQL. It is rather simple, it is Microsoft's very own "look up" table for all those system type things (determined by the TYPE column) where you need a name. More importantly, it is probably always resident in memory.

Let's have a quick peek, and please scroll through the list after you have run the first select command, and you will see quite a variety of "names" which are actually held as numbers within the MS systems...

select * from master..spt_values

-- Now, the ones we are interested in are :

select number from master..spt_values where type = 'p' 

-- which gives us all the numbers from 0 to 2047 inclusive. 

Open in new window


So, what can we do with a list of numbers ?

Well, in this article, we can unstring a delimited field, and we can …
15
 
LVL 41

Expert Comment

by:Sharath
Comment Utility
Mark, nice article.

Why don't you provide examples on splitting the string, displaying vertically etc. here?

As you have already answered such questions, you can add links to those questions for anyone's reference whoever want to check the magic of spt_values.

-Sharath
0
 
LVL 60

Expert Comment

by:Kevin Cross
Comment Utility
Here is a nice follow-up tip from chapmandew on how spt_values trick from Mark can be utilized even when a higher than 2048 is needed.

http://sqlservernation.com/blogs/tipweek/archive/2010/06/27/quickly-generate-a-number-sequence-in-sql-server.aspx

Again this isn't always the optimal method over say a persisted numbers table, but boy have I had fun with this so just wanted to give kudos to Mark again despite the fact I already relinquished my vote above! :)
0

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005.


The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.

 

Recently, I was involved in a discussion as to what some of these views really do, and thought I might share some of that discussion in a relatively light hearted way. Considerably more light hearted than the aforementioned discussion...


Now, despite being able to find a lot of discussions about these views, these two in particular can cause confusion. Mainly due to the terminology used in naming these views. Notably, the words "index" and "usage" are a little ambiguous. You would think that those names imply what one would reasonably expect to get if using them, and, when you might use them. Think again.


At first glance they give information which doesn't quite make sense when compared side by side. One view says "this" many reads and the other says "that". One view has an index entry for a table and the other doesn't. Both have entries for tables that don't have indexes. So, what is going on ? Why are they so different ? Which happens to be the wrong question, the fact that the information doesn't match is a very important differentiator. Question should be "How can I take advantage of those difference".


The differences in the information from those views actually tell us important pieces of the index puzzle. Collectively, that information is very telling about your index designs (which will be a different post).


So, lets have a look at those views :


SYS.DM_DB_INDEX_USAGE_STATS only reports on indexes that have been used at least once since the server was last restarted. Here the word "used" really is ambiguous, because it is updated once each time the query plan is executed, not every time an indexed is actually used.


SYS.DM_DB_INDEX_OPERATIONAL_STATS reports on all indexes regardless of whether they have been used and is updated every time the storage engine executes a specific operation on the index during the execution of query plans. One could reasonably argue that this view is really the usage, because it does reflect every time an index is actually used.


Now there is a third very interesting DMV sys.dm_db_index_physical_stats, and can help identify index fragmentation. But that will be a new article.


Because operational stats more accurately reflects actual usage, it is easy to get these two confused. The way I remember how to use them is by their name (duh), but, with a small twist, as in "is my index useful" then usage, "is my index operating efficiently" then operational.


To demonstrate the differences, let's create a couple of test tables :


But first, to use the queries below it has to be in sequence, so open a new query window and paste the code. Highlight the code until you get to the next "go" and then run the queries (keyboard short cut F5).


When looking at the results, you will need to scroll right to see the columns being referred to (namely singleton_lookup_count and range_scan_count in the "operational" view). So please browse and scroll when we look at the results from the DMV's.


9
by Mark Wills

PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively easy to do, other times it can be a challenge (and that is why we are here).

Let's have a quick look at the PIVOT function...

SELECT
          <display_column_list>

FROM

          (SELECT <source_columns> as Column_Source
                         ,<column_to_be_aggregated> as Column_Value
                         ,<column_with_new_column_names> as Column_List
           FROM <datasource> ) as DataSource

PIVOT
          (<aggregate_function>(Column_Value)  FOR  Column_List  IN
          ([<new_column_1_heading>],[<new_column_2_heading>],...,[<new_column_N_heading>]) ) PivotTable

ORDER BY <column_number_or_name>;

That looks pretty straight forward, except for one or two small details:

1) First up, we need to know the <display_column_list>
    easy enough, just do a Select * instead and problem solved (except that it does control display sequence)

2) Secondly, we need to know and hard code the new column headings as in :
     ([<new_column_1_heading>],[<new_column_2_heading>],...,[<new_column_N_heading>])

And that last point is often the big challenge. Not so bad if we are doing something static like "months in a year", just list out those months in sequence, and make sure you can cast the…
32
 
LVL 51

Author Comment

by:Mark Wills
Comment Utility
Yeah, should be...

Would add in another definition for the totals (similar to @columns).

But then we can no longer simply cheat with "select * from" we would have to add in that new computed column, and probably a parameter to decide when / if to use.

If you are feeling adventurous, there is a slightly more involved Article that should be able to do that for you : http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html

Cheers,
Mark
0
 

Expert Comment

by:Wil999
Comment Utility
Thanks voor the quick reply Mark, i will look in to the article!
0

SQL

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.