Logical query processing phases

This is one of the most challenging concepts that I had to learn when I started working with SQL.

I am not ashamed to confess that it took me some time to assimilate the way SQL Server processes its code and this is because SQL server does not process statement from top to bottom (like most programming languages)

I am an Accidental DBA, so I was expecting SQL Server to process the code as other programming languages does.

The “keyed-in order"

This is the order that you are supposed to type the query clauses. Please see below.

Logical Query Processing Phases

Here is where the fun begins and this is because the logical query processing order is different. It starts with the FROM clause.

Here is the same query based on “logical query processing order”:

1. Each phase operates on one or more tables as inputs and returns a virtual table as output.

2. The output table of one phase is the input to the next phase.

3. The very last step returns the final query result.

If you are a visual learner like myself, please take look at Itzik Ben-Gan’s flow chart of Logical Query Processing.

Examples:

A common mistake is try to use an ALIAS on the WHERE clause, this is going to throw an error because the SELECT clause is evaluated after a WHERE clause, so the column alias is not known to the WHERE clause.

On the other hand, we can use ALIAS on the ORDER BY clause since it is evaluated after the SELECT clause.

Using a little bit of everything based on the following rules:

• GROUP BY is processed before the SELECT (we cannot use alias here)

• HAVING is processed before the SELECT(we cannot use alias here, but we can use COUNT)

• ORDER BY is processed after the SELECT (we can use alias here)

Thanks for reading. Enjoy!

Inserción masiva en SQL usando datos XML (herramienta EN .NET)

En este post, veremos:

• ¿Cómo hacer una insercion masiva en SQL Server usando el tipo de datos XML?

• ¿Cómo automatizamos el proceso?

• Crea un SP automáticamente para manejar la inserción de datos.

• Transformar una DataTable a XML.

Antecedentes

A veces, es necesario insertar registros de forma masiva en la base de datos, este proceso es muy simple cuando tenemos acceso directo a la base de datos, pero ¿qué sucede cuando tenemos que extraer los datos de un Servicio Web, un Servicio ASP o una RESTful API?

Aquí es cuando comienzo a ver todo tipo de cosas creativas, como insertar uno por uno cada registro que se extrajo de la llamada, crear un script sobre la marcha, etc.

El Argumento

• Tenemos un servicio web que devuelve un DataSet (.NET)

• El esquema de la tabla de datos podría cambiar en cualquier momento sin previo aviso.

• Tenemos que extraer los datos de varios servidores web.

¿Cómo hacer una insercion masiva en SQL Server usando el tipo de datos XML?

Insertemos algunos datos basados en el siguiente XML:

XQuery en SQL Server es un lenguaje estático. Por lo tanto, para reducir el tipo estático y garantizar que exista, como máximo, un valor, se debe usar el predicado posicional [1] para indicar explícitamente que la expresión devuelve un solo valor.

TIP: Las mayúscula y minúsculas en XML son muy importantes (distingue entre mayúsculas y minúsculas). Las etiquetas de apertura y cierre deben coincidir exactamente. Por ejemplo, <ROOT>, <Root> y <root> son tres etiquetas diferentes.

Leamos el XML e insertemos algunos datos.

Voila, tenemos datos listos para ser consultados, fue muy simple ¿no? Bueno, esto es verdad si tenemos que crear el script solo una vez y dicho script no tiene más de 10 columnas, pero ¿qué sucede cuando tiene varios archivos XML con diferentes esquemas, muchos datos y cada uno va a una tabla diferente? Sí, que será una molestia crear el script, verificar cada tipo de datos y crear todas las tablas.

No te preocupes, te tengo una solución!

El problema

• Tenemos que extraer algunos datos de varios servidores, pero no tenemos acceso a la base de datos.

• El equipo de IT encontró una solución muy inteligente, por lo que en lugar de permitir el acceso a la base de datos, crearon un servicio web que regresa un DataSet con una sola tabla. ¡¡Esos de IT son geniales!! :P

• El equipo de IT proporcionó una imagen del conjunto de datos, esto es lo que debemos obtener cuando llamemos el servicio web, consulte a continuación.

La solución

Lo sé… le dije que tenía una solución, pero aun asi tienes que trabajar un poco para que esto funcione. Lo único que tienes que hacer es crear la tabla de destino basada en la imagen, sí la que vas a usar para almacenar los datos, luego .NET se encargará del resto.

Vamos a crear la tabla de destino.

¿Ahora que?

Tenemos que crear la consulta para insertar los datos, pero esta vez vamos a dejar que .NET haga el trabajo por nosotros, así que he creado un pequeño programa donde puede pasar el nombre de la tabla y el regresara un procedimiento almacenado que corresponde con el esquema de la tabla. Consulte a continuación.

Stored Procedure:

Tenga en cuenta que el xPath es estático “NewDataSet/XMLData”.

.NET Código Fuente.

Esto ejecuta una consulta en mi base de datos:

Esto hace el trabajo:

¿Qué pasa con los datos?

Bueno, los datos no son XML, por lo que tenemos que convertir el DataTable a XML, tengo una función que se encarga de esto. Por favor ver más abajo.

Aquí hay una muestra del XML después de la conversión:

Tenemos los datos, ¿ahora qué?

Tenemos que insertar los datos en SQL ... Sí, tengo otra función que se encarga de esto, tiene que pasar el DataTable y el nombre del SP. Por favor ver más abajo.

Sí, hemos terminado, ahora podemos obtener datos del servicio web y hacer una inserción masiva enSQL.

Esta solución funciona muy bien para archivos de tamaño pequeño / mediano, por lo que cualquier cosa de menos de 2GB se insertará muy rápido. Todo lo que supere los 2GB no funcionará con seguridad, en estas circunstancias, SQLXML Bulk Loader es la mejor solución. Trataré de cubrir esto en mis próximas publicaciones.

Extra

Vamos a hacer algunas pruebas con el programa final, vamos a insertar 300K filas de forma aleatoria en la base de datos.

La inserción de datos tomó un promedio de 6 segundos, por lo que no es tan malo.

Revisemos los datos.

Conclusión

Bienvenido al mundo XML, esta es una opción valiosa cuando desea insertar datos de forma masiva desde un servicio web, un servicio ASP o una API RESTful.

¡Diviértanse aprendiendo!

Bulk insert into SQL using XML data (.NET application Tool)

In this post, we will see:

• How do we insert bulk records into SQL Server using XML Data Type?

• How do we automate the process?

• Create an SP automatically to handle the data insertion.

• Transform a DataTable to XML.

Background

Sometimes, there is a need to insert bulk records into the database, this process is very simple when we have direct access to the database, but what happens when we have to extract the data from a Web Service, an ASP service or RESTful API?

Here is when I start to see all sorts of creative stuff, like hitting the database for each record that was extracted from the call, create a script on the fly, etc.

Scenario

• We have a web service that returns a DataSet (.NET)

• The schema of the DataTable could change any time without notice.

• We have to extract the data from several web servers.

How do we insert bulk records into SQL Server using XML Data Type?

Let’s insert some data based on the following XML:

XQuery in SQL Server is a statically typed language. So in order to reduce the static type and guarantee that there is indeed at most one value, you should use the positional predicate [1] to explicitly indicate that the path expression returns a singleton.

Tip: Uppercase and lowercase matter in XML (case sensitive). Opening and closing tags must match exactly. For example, <ROOT>, <Root>, and <root> are three different tags.

Let’s read the XML and insert some data.

Voila, we have data ready to be queried, it was very simple and straight forward right? Well, this is true if you have to create the script only once and it has no more than 10 columns, but what happens when you have multiple XML files with different schema, lots of data and each one goes to a different table? Yes, I know it will be a hassle to create the script, verify each data type and create all the tables.

Don’t worry, I have you covered!

The problem

• We have to extract some data from several servers, but we don’t have access to the database.

• IT Team found a clever a solution, so instead of allow access to the database, they created a Web Service that retrieves a DataSet with one single table. IT Team rules!! :P

• IT Team provided an image of the DataSet, this is what we should get when we call the Web Service, please see below.

The Solution

I know … I told you have I have you covered, but you still have to work a little bit to get this running. The only thing you have to do is create the target table based on the image, yes the one that you are going to use to store the data, then .NET will take care of the rest.

Let’s create a target table.

Now what?

We have to create the query to insert the data, but this time we are going to let .NET to do the work for us, so I’ve create an small program where you can pass the table name and the result is Stored Procedure based on the table schema. Please see below.

Stored Procedure:

Please observe that the xPath is hard-coded to “NewDataSet/XMLData”.

.NET Source Code.

This run a query against my database:

This get the job done:

What about the data?

Well the data is not XML, so we have to convert the DataTable to XML, I have a function that takes cares of this. Please see below.

Here is a sample of the XML after conversion:

We have the data, now what?

We have to insert the data into SQL… Yes I have another function that takes care of this too, you have to pass the DataTable and the SP name. Please see below.

Yes, we are done, now we can get data from the Web Service and do a bulk insert into SQL.

This solution work well enough for small / medium sized files, so anything under 2GB will be inserted very fast. Anything over 2GB will crash for sure, in these circumstances, SQLXML Bulk Loader should the best solution. I will try to cover this in my next posts.

Bonus

Let’s run some test with final program, we are going to insert 300K random rows into the database.

Data insertion took an average of 6 seconds, so it is not that bad.

Let’s review the data.

Conclusion

Welcome to XML world, this a valuable option when you want to bulk insert data from a Web Service, an ASP service or RESTful API.

Thanks for reading. Enjoy!