Posts

Showing posts from August, 2018

Format Query as JSON

Image
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 application

Performance Basics: Indexed views

Image
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 a

Announcing new Guatemala SQL Server user group

Image
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

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 publishe