Study your Data

This is a very simple version of a Xmas tree using T-SQL, also is a good example to learn how recursive CTE works. So, if you are in front of the computer with little work to do, take a look at the code, maybe you can improve it further! /* Draw a simple xmas tree using recursion 🎄 Happy holidays! */ WITH XmasTree AS ( SELECT CAST (REPLICATE( ' ' , 16) + '^' AS VARCHAR(50)) AS t, 0 AS lvl UNION ALL SELECT CAST (REPLICATE( ' ' , 15 - lvl) + '/' + REPLICATE( '*' , 2 * lvl + 1) + '\' AS VARCHAR(50)) ,n.lvl + 1 FROM XmasTree n WHERE lvl < 16 ) SELECT t as [Happy Holidays] FROM XmasTree; And this is the final result: HAPPY HOLIDAYS!

When you are in charge of databases with tons of objects, making a small change over an object can be difficult to replicate if you have a lot of objects that depend on it. For this post, we will focus on how to update column changes on dependent views. For this type of task, there are 2 scenarios: You explicitly defined column names on the dependent views, or defined WITH SCHEMABINDING: For this case, you need to perform a find/replace task, this can be difficult to achieve since there are no native tools to do it (as far as I know). You can use third-party free tools like RedGate SQL Search or ApexSQL SQL Search , both having its pro and cons, so take a look at both tools and find out what it suits best your needs. You implicitly defined column names on dependant views: The famous SELECT * , even when this is not a best practice , it is easy to replicate changes for this case, we will show you how to do it. Setting up our example We will use WideWorldImporters

SQL Server engine does an excellent job to optimize query performance and it has different methods to achieve this goal, one of these methods is the data caching. Data caching consist of putting 8KB pages from storage into memory as they are needed, so the database engine can access the required data again in a much faster way. At a glance, the way SQL Engine caches the data is the following: When a request is made the engine checks if the data is already cached in memory (or buffer pool), if the data is already available, then it is returned. If the data is not available, then it is retrieved from storage and put into memory until the buffer pool is full. How long the data is stored in the buffer pool depends on various aspects: the memory allocated to SQL Server, size of your database, workload, type of queries executed, In-memory OLTP.. etc. When the buffer pool does not have enough space to allocate the desired memory, then a data spill occurs, it consists of additiona

Notebooks are a functionality available in Azure Data Studio, that allows you to create and share documents that may contain text, code, images, and query results. These documents are helpful to be able to share database insights and create runbooks that you can share easily. Are you new to notebooks? don't know what are the uses for it? want to know how to create your first notebook? then you can get started in ADS notebooks checking my article for MSSQLTips.com here . Once you have created your first notebooks and share them among your team, maybe you want to share it on your website or blog for public access. even when you can share the file for download, you can also embed it on the HTML code. On this post, I will show you how to do it. What do you need? We will use an online HTML converter, nbviewer , provided by Jypiter website, on that homepage, you just have to provide the link of your .ipynb file (my GitHub Notebook repository for this example). It looks

Azure Database is the PaaS solution for SQL Server databases, on a previous post we have discussed how to create one. On this post, I want to show you how you can secure your Azure SQL Database by creating users and segregating their permissions. When you connect to your Azure Database using SSMS (or another tool), you can see the management options are very limited compared to an On-Premises instance. If you want to create a login and database user, you must create them via T-SQL, on this post I will show you how to do it. Types of logins Azure SQL database support two types of logins: SQL Server login and Azure Active directory login. In order to create Azure AD logins, you must set up an AD administrator first using the Azure portal, you configure it on the server dashboard, then accessing the Active Directory Admin, as follows: Once you set up your AD Admin, you can connect to the Azure database using this account and you can then assign proper access to o

If you finally have given a try t o Azure Data Studio , and if you use it on a regular basis, maybe you want to customize it to suit your needs. Among the huge customization options it has, you can control the text size in form of zoom. To do change it, just use the following keyboard combinations: (Ctrl + = ) For Zoom in. (Ctrl + - ) For Zoom out. (Ctrl + 0) For Zoom reset.  You can see it in action: Also, as with everything on this tool, you can access this functionality from command palette (Ctrl + Shift + P) , and type "zoom" (you can fine-control the editor zoom from here): If you haven't tried it yet, you can download Azure Data Studio here .

A great experience! Thanks to all the atendess to my session about T-SQL, for being my first time as a speaker for a SQL Saturday it was good! As I promised, the presentation and session material is available at the following links: SQLSaturday #828 site: (Please evaluate my session if you attend) https://www.sqlsaturday.com/828/Sessions/Details.aspx?sid=87912 My personal GitHub: https://github.com/Epivaral/Scripts/tree/master/T-SQL%20Basics%20coding%20for%20performance Some pictures from the event: SQL Server local users group board!

If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog  is created on MSDB database, it contains logging entries for packages executed on that instance. If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow. A proper deletion process must be put in place, so you not get into situations like this one in your msdb database: If you are already on this situation, you can the following T-SQL Script to delete records by batches: DECLARE @ date_del datetime, @ batch_size int = 1000 , -- will delete on batches of 1000 records @ RowsAffected int =1 -- Time to keep in the history, in our case 1 month SET @ date_del = DATEADD(mm, -1 ,getdate()); SET NOCOUNT ON ; WHILE ( @ RowsAffected >0 ) BEGIN DELETE TOP( @ batch_size) FROM [dbo].[sysssislog] WHERE starttime < @ date_del; SET @ RowsAffected = @@ ROWC

I’m very thrilled to announce that I will be participating as speaker in this year’s SQL Saturday #828 event in Guatemala city! This will be my first time as a speaker on a SQLSaturday. Event will take place on February 16 at Universidad Francisco Marroquin, Calle Manuel F. Ayau (6 Calle final), zona 10, Guatemala Here are the details of the session I will be presenting ( at 3:15 PM CST in Dev Room ) T-SQL Basics: Coding for performance It is very common in the IT field for a developer to switch to database developer or administrator, even when the programming concepts are the same, the skillset required to code T-SQL is different. In this session, we will learn some basic tips to improve our code and improve database performance from early application planning stages to already deployed applications. We will also see some demos about: Compatibility level and deprecated features Filtering basics: SARGABLE arguments Covering indexes Indexed views Implicit conver

Graphic representation of the difference between 0 and NULL Image taken from 9gag.com According to database theory, a good RDBMS must implement a marker to indicate "Missing or inapplicable information". SQL Server implements the lack of value with NULL, that is datatype independent and indicates missing value, so the logical validations are different, this is better known as Three-Valued Logic , where any predicate can evaluate to True , False or Unknown. I see a common error, referring to null like "Null values" but the correct definition of null is "Lack of value" so you must refer to it as null , in singular form. On this post, we will learn how to work with null in SQL Server. Declaring and assigning NULL For working with null, SQL Server engine uses the reserved word NULL to refer to it. It is datatype independent so you just have to assign it to any variable or field, using the equal operator =, as you can see on this example: D

You need a lot of hard work for success! 2018 was a good year for me in a lot of areas of my life both personal and professional (the birth of my daughter, my MCSE certification and another MCSA as well, the start of the local PASS Local group, the growth and recognition of this blog, etc). Also, since August 2018, I started writing technical posts for other sites, and one of them is MSSQLTips.com . This is one of the best SQL Server related sites on the world, it contains a lot of technical articles in a "tip" format where you can learn new things or how to fix an issue in your environments. The new year 2019 has come and new opportunities to continue growing, to continue learning and improve your career. I also have started this year with good news, last week I was awarded "Rookie of the year 2018" by MSSQLTips. This award is won by community, peer and site leaders votes, so I am very honored that many people have voted for me, this is a great honor and a

You can maintain and deploy code easily Source control is a vital part of IT work, since it is used to track changes over software and documents, with two main benefits: having a centralized, up to date copy of all your applications and utilities, and to be able to rollback to a previous stable version in case of a failed deployment. This is perfectly applicable to SQL Server, where you want to have an updated and ready to deploy elsewhere copy of your database objects. There are a lot of source control applications and software, everyone has its pros and cons, but personally, I like to use Git Hub , since it is free to use and since it was recently acquired by Microsoft, support for other products is easier (SQL Server for this case). On this post, I will show you how to implement a source control for a database using GitHub and Azure Data Studio (ADS) . Setting up your machine First of all, you need to have Azure Data Studio installed and running on your machine, an