SQL Server Performance Blog where milliseconds matter

Hi guys, Are you ready for the first post of April? Today we will talk about a new function that permit to reverse what the SUM function does . I will do a quick post just to give to you an example!     The Unsum function Let's start this example from the SUM function. Suppose having a simple table with a column of type integer called value SELECT * FROM TAB_EXAMPLE Now we will use the SUM function to sum the values of the "value" colum. SELECT SUM (Value) AS TOTVAL INTO TOT_EXAMPLE * FROM TAB_EXAMPLE We will obtain a new table called TOT_EXAMPLE with one row and a total of 6. OK! Now we will use the UNSUM function. So, type this command: SELECT UNSUM (TOTVAL) FROM TAB_HEADER Now,  if you execute this statements will have back your single values!!! ooohhhh!!!! Wooow, Cool very very cool!!! If you get an error or do you need more infos you can take a look to this page     That's all fo

Hi Guys,   For this last post of March we will talk about an operator that we have seen in the execution plan . Perhaps it is less known than the others but it is worth knowing. Today we will talk about the Bitmap operator .  It is used to filter data and to improve performance . As usual, I will try to be as clear as possible. Enjoy the reading!   The Bitmap Operator As mentioned earlier today we will talk about the Bitmap operator. This operator is born with the aim of improving performance when we are dealing with a lot of data and the execution plan works in parallel . Let's see with an example how to generate an execution plan that contains the Bitmap operator. For the example we generate a couple of heap tables: Create table TabA (id int identity (1,1), Progr int ) Create table TabB (id int identity (1,1), Progr int ) Both tables have the same structure: a field ID of type integer and a field Progr of type integer. We will join TabA and TabB through the P

Hi Guys, During the last post we saw that data are not physically deleted from the table where you execute a delete statement. Data are marked as deleted only. It is a scheduled task called Ghost cleanup task that physically removes data from the table. We have also seen that we can disable this process via global traceflag number 611. So, if you enable flag 611, the records will no longer be automatically removed from the table. (In reality this is not true  for the heap tables where the traceflag 661 has no effect ) Sure, they are not of those configurations to try in production! But what would that entail? The positive thing is that in the presence of many cancellations the workload of the server should be less. The bad thing is that the disk space is not freed up and that as the tables are physically larger, the table scan should take longer. But let's see if that's true or not with an example!   What happens if you disable the Ghost cleanup task? Before running the examp

Hi Guys, Since the previous post received a lot of reviews, today we continue with the series " omg I deleted the wrong record! " Then let us immerse ourselves in the dense atmosphere of a server room. This time you have dared too much. You didn't do your nice "begin tran" command before doing that damn delete .. and you deleted the wrong row . In the previous post we saw that we can recover the data. But if you have just made the cancellation, do you have any more hope of recovering the data on the fly?   Enjoy the reading!   Deleted data or Ghost record? The first important fact is that records are not physically deleted when you execute a delete . In fact, for performance reasons when deleting data SQL Server only marks the data as ghost record . Therefore, the deleted row stays on the database. In the row header it will be written that that the row is now a ghost row. ...and if you think about it, this is a very useful mechanism because it prevents us fr

Hi Guys, Welcome to this midweek post! Today's topic is...   "How to recover deleted data or data from a dropped table… without backup!" Sometimes a carelessness or a mistake is enough, and in the end, it happens! ...yes, In no time at all you find yourself having deleted rows or entire tables from the database that you should not have deleted! And now? … What can we do? Enjoy the reading mates! How to recover deleted data or data from a dropped table If you've just deleted some data or deleted a table (panic aside) you need to know that there is good news . Each data change occurs within a transaction and then goes through the transaction log . Have you performed an update? ..or did you perform a deletion? Then these data changes are passed through the transaction log. Always! Even if you haven't explicitly opened a transaction, SQL Server has opened one (implicit) for you . At this point you will have already understood where we are going to look for the data d

Hi guys, Welcome this post mates. Today a super light post only to show how to use a function as a default value for a column of a SQL server Table. Simply and easy as usual! Enjoy the reading!   How to use a function as default for a column   A columns of a table in SQL Server can have a default value. Simply, if I insert a row using the insert statement and I do not specify the name of a field, in that case the default value will be used for that field. Let's see how a default is defined on a field. To add a default value you simply add the default and a value after the type definition of the field. CREATE TABLE ORDTES (ID INT IDENTITY (1,1), CODE VARCHAR (20), TOTAL_VALUE FLOAT DEFAULT 0 ) Obviously we can specify any value consistent with the type of the field. Now, if you do not specift the field total_value in the insert statement: INSERT INTO ORDTES (CODE) VALUES ( 'AAA' ) The default value will be used: Sometimes, however, our default is not a fixed value.