Wednesday, August 22, 2018

Format Query as JSON

JSON (JavaScript Object Notation) is a file format used to transmit data from various applications, very similar to XML, it also used to stored NoSQL unstructured data, and because of this versatility, Many REST applications and web services use it.

Since this is a standard file format, you should be able to generate data in JSON format from SQL Server.

Fortunately, since SQL Server 2016, there is a native way to do it, in this post I will show you how to do it.

Generate a simple JSON file


The most basic syntax to generate a JSON file is this:


SELECT <field list>
FROM <YourObject>
FOR JSON AUTO 

Using this simple test table with 10 records as an example:


SELECT TOP 10
 *
FROM [dbo].[MyTestTable]



If we use JSON AUTO on this sample data we will have the following output:


SELECT TOP 10
 *
FROM [dbo].[MyTestTable]
FOR JSON AUTO

Query executed
Part of the JSON AUTO output


Using dot syntax


For most of real-world applications, the JSON AUTO will not give you the control you could need over your file format, for having more control over it, you must use the JSON PATH option, along with the ROOT option as follows:


SELECT TOP 10
 id,
 dataVarchar,
 dataNumeric,
 dataInt,
 dataDate
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')


This will generate the following output:

JSON PATH output


And if we want to group similar items (for example for queries with joins), just rename the fields as element.field as in this example:


SELECT TOP 10
 id,
 dataVarchar as [group1.D1],
 dataNumeric as [group1.D2],
 dataInt as [group2.E1],
 dataDate as [group2.E2]
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')

Will generate the following output:

JSON PATH with grouped items

Of course, if you have SQL Server Operations Studio you can do it from the IDE:



If you want to learn more about the FOR JSON option, please read Microsoft official documentation here

Saturday, August 18, 2018

Performance Basics: Indexed views

Views help our query writing by simplifying writing the same sentences and/or aggregations over and over again, but it has a drawback, the views just store our query definition, but the performance is not improved by using them.

Since SQL Server 2008, the option to create an index over a view was introduced, of course, there are some limitations, but if your view can use them, the performance improvement could be great!

I will show you how to create a simple index over a view

Base query


I have created a test table called Aggregate_tbl1 with over 12,000,000 rows of random "money" data and random texts as well, as you can see in the following images:






with this table, let us proceed to make a simple aggregation query and create it inside a view:


CREATE VIEW [dbo].[Aggregate_tbl1_VI]
AS
SELECT 
 SUM(ValueMoney) AS total, 
 [Description] AS descr
FROM dbo.Aggregate_tbl1
GROUP BY [Description]

GO

If we execute this view with a single where and check the execution plan we will see something like this (no indexes):



we can see that an index scan and some computes are performed, so what happens if we just add a simple index in the base table over description and include the valueMoney field?

If we execute the query again, we can see some improvement, and index seek is performed now, but still, there is an aggregation operation costing 7% of the total operation and please take a look on how many rows are read (22397) to compute the total









Creating the view and the related index


We proceed to create an index over the view we created previously, of course, we have to drop it first, and then recreate it with the SCHEMABINDING option, and since we are using a GROUP BY, we also must include the COUNT_BIG(*) sentence:


DROP VIEW [dbo].[Aggregate_tbl1_VI]
GO

CREATE VIEW [dbo].[Aggregate_tbl1_VI]
WITH SCHEMABINDING
AS
SELECT
 COUNT_BIG(*) as [count_rows], 
 SUM(ValueMoney) AS total, 
 [Description] AS descr
FROM dbo.Aggregate_tbl1
GROUP BY [Description]

GO

Now we proceed to create an index over the view (as any other index)
Just note that the index must be a unique clustered one, that is because a group by is used in our example, to guarantee the uniqueness of the rows of the view:

CREATE UNIQUE CLUSTERED INDEX IX_IV_Aggregate_tbl1_VI_DESCR  
    ON [dbo].[Aggregate_tbl1_VI] (descr)
GO  

Now, let us proceed to execute the query again and see how it behaves:



We can see that now the newly created index is executed, and the aggregation operation lowered its cost, and from reading 22397 rows it decreased to 1 row, as we can see in the details:



With this simple example, we could see that we had a big improvement on the performance of the query, but as I always tell you, test any change on a dev server first!

If you need more information about the special requirements to create an index over a view, please visit the Microsoft official documentation here


Thursday, August 16, 2018

Announcing new Guatemala SQL Server user group



I am very happy to announce that after a long time struggling and filling all the requirements, we were able to create the SQL Server local user group for Guatemala.

The purpose of creating this group was to empower the local database administrators and developers by creating a community where all of us can benefit from the knowledge or personal experiences from any particular topic, this will become us better professionals and why not? have better jobs and salaries in the future.

We want to focus our community as a roundtable so any of the members (old and new) could be an assistant or speaker so we will encourage the participation of all the members.


What are the contact details?


You can access the main site at gtssug.pass.org and we encourage to register now!
The meetings and related content will be published in there.

Also follow us on social Media for news and database related content.

Twitter: @gtssug
Facebook: facebook.com/groups/gtssug

Who is leading this group?


This group was initiated by great professionals I have the privilege to met (and me!).
these are Carlos Robles, Christian Araujo, Carlos Lopez, and myself. You can learn more about us here.

Please note that we only are the coordinators, in fact, anybody can participate as a volunteer or speaker!


Where will be the meetings?


We want to thank Atos Guatemala for providing us a place to have our meetings, you can learn more about this awesome company here

Atos is located in a convenient place at Distrito moda in Ciudad Cayala, so there are no excuses for not assist to any meeting of your interest!

Paseo Cayalá Oficina 401,
Edificio H Distrito Moda,
Zona 16, Guatemala


And remember, please register here to receive the latest news and related material.
We want you to be part of this rising community!


Thursday, August 9, 2018

Determine SQL Server Network Protocol Information using T-SQL and DMVs

I have recently started as an author for MSSQLTips.com website, this is an excellent site where you can start writing if you don't want to maintain a blog, and you acquire a lot of visibility right away since they have a lot of traffic to their website.

On my first post with them, I explain how to Determine SQL Server Network Protocol Information using T-SQL and DMVs

You are able to read the instance configuration registry information from SQL Server itself using DMV  

sys.dm_server_registry

and I also share a few examples on how you can obtain network information easily.

Take a look on the full post and let me know what do you think:

https://www.mssqltips.com/sqlservertip/5626/determine-sql-server-network-protocol-information-using-tsql-and-dmvs/ 


From now I will mix my posts between this blog and as an author for that website, but i will share all posts there so you will keep track of everything!

Use the MSSQLTips.com label to check all the tips I have published.