SQL Server Performance Blog where milliseconds matter

Hi Guys, Welcome back! I hope you and your family are fine. Today, after some posts about spatial data type and Machine learning, we will have a light post in which we will talk about the SQL server management studio. But not only...   Introduction What is the SQL server management studio or briefly SMSS? Well the SSMS is simply a graphical interface to access to the SQL Server database Engine. For example with the SSMS you can execute every T-SQL command or set options. But remember:   Everything you can do with the SSMS you can do sending command directly to the database engine . Remember also the is always recommended to use the last SSMS . This is because at each version bugs are fixed and new functionality are inserted. This month Microsoft released the version 18.5 so what better opportunity to show you how to install this new version? Are you Ready? How to install the last SSMS version This is easy. First download the last SMSS version fr...

Hi Guys! Today we talk about spatial data type.  We will see how to draw a graph directly from SQL Server Management Studio Are you ready to see new datatype called Spatial data type? Yes? OK! So, i will try to explain as simply as possible.   Introduction Spatial data type are datatypes used for conserving spatial data which allows us to render graphical data. They are used for representing the location, size, and shape of an object. Every object such as lake or a car could be rapresented. Spatial data may also include attributes which can be given more information of the entity that is being represented. That there are two types of spatial data: Geometry or Geography . Today we will examine Geometry spatial data type.   How to use Spatial geometry data type We can define a spatial data type like any other data type. For example we can create a table where a column (GEOM) is defined as geometry. CREATE TABLE [dbo].[SPATIAL_TABLE]( [ID] [in...

Hi Guys, I wish you an happy Easter! Today we will do another little step into the Machine Learning . Some weeks ago infact we talk about how to install Machine Learning services in SQL Server . I suggest to read the post you can find here: From SQLServer to ML, a first little step into machine learning This is because today we will see how to read data from SQL database with the R language . Read data in R language If you remember, in the previuos post we have enabled the execution of external scripts. Infact we will use external scripts in order to read data from SQL Server. The procedure sp_execute_external_script have some parameters that are: The language in this case you must specify "R" Command sent to R Runtime The input_data_1query data result Schema of the table returned For the example below we will read data from our ORDTES table: USE MASTER EXECUTE sp_execute_external_script @language = N'R' , @script ...

Hi Guys, Welcome back! Today we talk about security! Lately infact also SQL Server is used to execute malware and so exploiting their compute power to mine cryptocurrency! Are you ready to know if your SQL Server is safe? Introduction Yeah, this time attackers have sniffed the businnes: use Server in order to mine cryptocurrency such as Vollar and Monero! But How they do it? Anatomy of an attack! How they do it? Usually attackers gain the access to the targeted SQL Server databases by brute force method. (And so use wear password for the system administrator user is not a good idea...) Once attackers achieved the access with the sa user they apply changes to the database in order to allow command execution . Then simply download malware binaries and open multiple backdoor. Then remove traces of the malware activity and finally execute malware that execute the cryptomining procedure. The whole process told in this way seems simple enough and in fact it is! So fir...

Hi Guys, Welcome back! After the last post about  How to read an XML file from SQL Server ,today we have only some brief notes about triggers! We talk about the for triggers and after triggers .  So, are you ready? Introduction I think we all know what is a SQL Server Trigger. A trigger is simply a procedure triggered after the execution of a Insert, Update or Delete T-SQL Command . When you write a trigger you could specify the option FOR / AFTER or INSTEAD OF. What is the difference? If you specify FOR/AFTER parameter trigger is fired when " all operations specified in the triggering SQL statement have executed successfully ". While if you specify the ISTEAD OF parameter the trigger is fired in place of the T-SQL statement. But, what is the difference between the clause FOR and the clause AFTER? For Triggers and After Triggers In order to do an example let's take our famous ORDTES table. Now define the triggers below: CREATE TRIGGER [dbo].[TR_ORDT...