Study your Data

If you work with SQL Server on a daily basis, it is very likely you have a lot of custom scripts you have to execute frequently, maybe you have stored them on a folder and you open them manually as you need them, or have saved them on a solution or project file, maybe you execute a custom .bat or PowerShell file to load them when you open SSMS... Every method has its pros and cons, and on this post, I will show you a new method to load your custom scripts on any open query window on SSMS via Snippets. What is a Snippet? According to Microsoft Documentation : Code snippets are small blocks of reusable code that can be inserted in a code file using a context menu command or a combination of hotkeys. They typically contain commonly-used code blocks such as try-finally or if-else blocks, but they can be used to insert entire classes or methods. In short words is custom code (T-SQL Scripts for us) that you use a lot and you want to have always available to use. In SSMS there ar

R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL. You can learn a little more about what you can do in the SQL Server blog . On this post, I will show you how to setup and configure it so you can start using it right away in your applications and reports. To install it You have to choose it from instance features on your SQL server setup window, you then choose the languages you want to work (R, Python) Note: if your computer does not have access to the internet, you will have to download two packages separately and then locate them in the setup window. Continue the setup as usual and finish it. After you run the setup, please make sure that SQL Server LauchPad service is running for the instance you have installed the Machine Learning Services. I recommend you to patch the instance with the latest update available in case you encounter any i

Transparent data encryption (TDE) helps you to secure your data at rest, this means the data files and related backups are encrypted, securing your data in case your media is stolen. This technology works by implementing real-time I/O encryption and decryption, so this implementation is transparent for your applications and users. However, this type of implementation could lead to some performance degradation since more resources must be allocated in order to perform the encrypt/decrypt operations. On this post we will compare how much longer take some of the most common DB operations, so in case you are planning to implement it on your database, you can have an idea on what to expect from different operations. Setup of our example For this example we will use the Wide World Importers sample database, and restore it 2 times, one decrypted, and the other one encrypted. Please note that the restored database is around 3.2 GB size, a relatively small one. To encrypt one of t

On a previous post , we discussed what is an extended event (XE) and how to create one. With the following T-SQL you can quickly check what are the currently running Extended Events sessions: SELECT ES.name, iif(RS.name IS NULL , 0, 1) AS running FROM sys.dm_xe_sessions RS RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name --WHERE es.name = '<YOUR XE SESSION NAME>' The column running will show 0 if the session is currently stopped and 1 if it is currently running. In the commented WHERE clause, you can filter for a specific session. Hope this helps you to monitor your XE sessions!

PowerBI is a powerful reporting solution for Business Intelligence for analytical reports, it is not meant to replace SSRS, the focus of this tool is to be able to visualize data interactively from different data sources in a collaborative way. On this post I will show you a quick way to embed your PowerBI reports into HTML code, that can help you to publish reports to websites. For this, you will need a PowerBI Pro subscription (you can try it by free for 60 days) Once you are logged to your PowerBI Pro account, select any published report you want to embed, as follows (for this example I will use a test report I have with imported dataset) Generating HTML code for the report Click on File and select Publish to web A message to create an embed code will appear, click on it A warning will also appear since your data will be public, click on publish And that is all, now you have your HTML code to share it wherever you want. What if you want to stop

This post is part of #tsql2sday event :) I know, in the world of the database administrators and developers, the triggers are the ugly duck of the database development. They are hard to track, hard to maintain, can have a weird transaction behavior if you are not careful and can lead to blocking your entire database if they are poorly programmed (yes, this is the prejudice). But as anything, if treated well and programmed carefully they can be handy in a lot of special situations, but read again... I put SPECIAL SITUATIONS, let me tell you 2 histories I have with triggers, a good one and a bad one: The Good Some years ago, more than I want to admit, I was working on a local bank,  there was a requirement from the banking regulation authority, they ask each bank to report any currency exchange operation in near real-time (a maximum of 30 minutes to report an operation, I cannot remember exactly, but around these times) and the implementation time was some kind of short.

One of the things I enjoy about my job is when I have to develop or review some T-SQL code, for my own projects or reviewing someones else code. In both cases, you often have to tune some queries or review them to see if there is room for some performance improvement. An Execution plan is the method SQL Server engine will choose to execute (or would use if is an estimated plan) any given query based on the object indexes and statistics, server load, parameters, tables involved, operators, conversions, to provide the optimal execution time and the minimum resource usage. For human analysis, these are stored on .sqlplan or .xml extensions so you can save and analyze them later or on another machine from where it was generated. For this type of analysis, you need to check execution plans, and it is important that you know how to generate and save them to be shared or for later analysis. On this post, we will discuss a wide range of the options you have to generate and save an executi

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

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

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