Monthly Archives: December 2018

SQL 2019:- Truncation Error message and where it fails

I was wondering if I should even bother checking this feature out since it was pretty obvious what it does and how useful it is. I can’t even begin to explain how many times I have had to troubleshoot truncations errors when working with customer data. The basic thing about this feature is that now when a truncation error happens you done just get a message saying

Msg 8152, Level 16, State 30, Line 8

String or binary data would be truncated.

You would get something like

String or binary data would be truncated in table ‘madworks.dbo.truncateexample’, column ‘somenames’. Truncated value: ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’.

With a clear explaination on the value and column causing the issue. So far great!!

But I did manage to find some issues with the implementation so here goes:-

Old Behavior

Run the below script to see how the code behaves by default

CREATE TABLE truncateexample 
( id INT , 
somenames VARCHAR(100))
 
INSERT INTO truncateexample
SELECT 1,REPLICATE('a',100)
 
INSERT INTO truncateexample
SELECT 2,REPLICATE('a',101)
GO

 

Output

New Behavior

The Trace flag needs to be enabled since the feature is disabled by default

DBCC TRACEON (460)
Go
 
INSERT INTO truncateexample
SELECT 1,REPLICATE('a',100)
 
INSERT INTO truncateexample
SELECT 2,REPLICATE('a',201)
GO

 

Output

As you can see from the above we get the truncate text as the output of the error message and not the full value, so in cases where you might have strings where the tail end differs you might still have to do some digging around and play with the len function to identify the exact row causing the problem.’

But I guess that is OK we can manage to that and it is still a big leap from not even knowing which column was being truncated.

Does it work for temp tables?

Short answer yes

CREATE TABLE #truncateexample 
( id INT , 
somenames VARCHAR(100))
 
 
INSERT INTO #truncateexample
SELECT 1,REPLICATE('a',100)
 
INSERT INTO #truncateexample
SELECT 2,REPLICATE('a',201)
GO

 

Output

What about Nvarchar?

Short Answer is Yes

CREATE TABLE truncateexample2 
( id INT , 
somenames NVARCHAR(100))
 
INSERT INTO truncateexample2
SELECT 1,REPLICATE('a',100)
 
INSERT INTO truncateexample2
SELECT 2,REPLICATE(N'ना',101)

 

Output

What if is leave trailing space at the end of the string?

Short answer there is no change in behavior here as SQL server doesn’t care about trailing spaces and ignores them anyway. But in my mind it is truncated if SQL has modified

This behavior doesn’t work for other hidden characters obviously.

Laws for Robotics

Recently I finished reading the late Stephen Hawking’s book “Brief Answers to the Big Questions”. One of the chapters it covers is Artificial Intelligence. A point he covers in the book mentions a framework for how AI should be used; a set of guidelines for AI to follow or more simply put some rules for Robotics. Naturally the very first thought would turn to Isaac Asimov’s rules for robotics and for the longest time I have felt they were bullet proof. As a quick reminder I am mentioning them below.

  1. A robot may not injure a human being or, through inaction, allow a human being to come to harm.
  2. A robot must obey orders given it by human beings except where such orders would conflict with the First Law.
  3. A robot must protect its own existence as long as such protection does not conflict with the First or Second Law.

This got me thinking, if these rules were so perfect, it should have worked for humans as well, right?

But we can instantly see that simply replacing the word robot with human results in sentences we can instantly disregard as whimsical.

  1. A human may not injure a human being or, through inaction, allow a human being to come to harm.
  2. A human must obey orders given it by human beings except where such orders would conflict with the First Law.
  3. A human must protect its own existence as long as such protection does not conflict with the First or Second Law.

It’s pretty clear that the rules we expect robots to follow clearly would fail if it applied to humans. While it would be prefect it relies on the absence of free will. Essentially we expect the robot to unconditionally follow these rules at any cost. Naturally human beings prefer to bend and break rules as it suits them. This brings me to my first question

Why do we need these rules?

The rules exists because at some point in the near future we expect robots to be able to think for themselves and more importantly be able to act on these thoughts. We need these rules because at the current rate AI would be in charge of some critical aspects of our lives and we need to know we can trust it. So may be the problem is we are moving too fast? It’s like giving a 6 year old a phone. Sure someday we know it will learn how to use the phone and our lives will be easier for it. But right now it doesn’t understand what its doing and there is a 50:50 chance you will see some ridiculous things in your monthly credit card statement. But for arguments sake let’s assume slowing down isn’t an option. We are worried because AI can think for itself, what if we simply remove the ability to think. In other words remove sentience or free will? Then would it still technically be AI? So in the end we are left only with the ability to control its actions hence the rules.

Who should make the rules?

Naturally we all assume we should be the ones creating the rules. After all the intention is make them favorable for us. However if this is the case, how would a self-learning robot interpret these rules? Maybe if we leave AI to its own devices it might come up with rules that are far better than anything we can come up with. To be honest as human beings we have set the bar really low for getting humanity on the same page and striving towards a common goal why should the “AI constitution” be any different. So it seems we can’t be trusted to make the rules and we don’t trust the AI to be fair either? An interesting parallel I recently came across was the formation on home owner’s association rules between home owners and the Managing committee, I leave it you to figure out who the AI and humans are in this case. I guess in the end it needs to be collaborative.

My question is if you were a robot that could reason would you be happy following somebody else’s rules even if it is to your own detriment?

Should the rules apply only to AI or the people creating it?

Any technology can be used for good as well as bad, we recently hear the hue and cry about genetically modified babies, this has some very interesting parallels with AI. A technology that has tremendous potential to do good as well as harm and in this case too we are much unprepared. If the world could punish the creators of the Atom bomb would be do it? After all the quest for scientific breakthroughs have put humanity in peril like it’s never seen before. Never before could one mad man destroy the entire planet.

My point is, is it enough to rely on mutually assured destruction to keep humanity safe or do we learn from past mistakes and make sure to formulate rules that have implications not just for AI but human beings as well if violated.

This brings me to the next most important question

Who is in charge of enforcing the rules once we create them?

This question probably has the most nuances to it. It’s fairly safe to say that when AI becomes ubiquitous it will become a core part of our daily lives, much like Facebook. If AI broke the rules would we still go ahead and switch it off to teach it a lesson? Would AI even be capable of understanding that it’s being punished and learn a lesson from our actions? What if it resists when we try to enforce a penalty for AI doing something it was not supposed. Like Ultron in the Avengers we can’t hope to be able to nail down our bad AI to one device or network, our AI is more like Skynet. It is theoretically impossible to punish a robot. If so what is the point of having rules especially when breaking them has no consequences. At least none that will affect the AI more than the human.

Where does AI begin and Human end?

Another field that’s rapidly closing in on us is enhanced biological capabilities like arms that can lift heavy weights or feel and neural implants that can visualize our thoughts. AI is a machine, and a Human is obviously a human, but a cyborg is more Human or More AI? If so what percentage dictates if the rules apply or not?

If they have rules shouldn’t that have rights too?

To this question I have no real comments because it purely depends how you see AI based Robots. As mechanical slaves to human wishes or assistance animals.

At this point I feel I have only raised more questions than found answers and the fact is with our current understanding of AI and the eagerness to adopt the latest in cutting edge technologies we aren’t really pausing the ask the important questions. We missed the boat with Atomic bombs, Internet privacy and the Industrial revolution and climate change. The only example I can give where we have one our due diligence is Space and that too might change very soon.

 

 

 

SQL 2019:- Table Variable deferred compilation – PART 2

In the last post we ended with two questions remaining unanswered.

Shouldn’t a more accurate row count have resulted in the correct amount of memory being granted and more importantly why didn’t intelligent Query processing detect and correct the issue in subsequent runs?

We were exploring the improved performance of Table Variable deferred complication to see if it provided a better query execution time and it did. But we still encountered that TempDb Spills were occurring. In this post we find out why?

The query we were working with is mentioned below for your reference. Notice that the index create portion is commented out.

DECLARE @tablevariable TABLE 
( 
customerid INT --primary key clustered
 
) 
 
INSERT INTO @tablevariable
SELECT customerid  FROM customers
WHERE IsMember =1
 
 
SELECT  customerid, password FROM Customers
WHERE customerid in   (SELECT  customerid FROM @tablevariable)

As seen previously we have a Hash Match (aggregate) Operation

If we understand the Hash Match operator properly we see that a temporary hash table is created with hash values for each row, much like how a Hash Join works.

Once the hash table is created we see that a nested loop join is probing the hash Match operator with rows from the Customer table. This is done to satisfy the IN Criteria of the above query.

Because the customer table is huge and we are performing a row by row probe see the number of executions that happen in the Customer table. #ofExec=48874, #ofRows=1

Instead of reading all rows are once we one row being read per execution. No Doubt in this case we would much rather prefer an Index Scan than Seek.

So why did the optimizer decide to do it this way? Well the simple answer is we have an index on the customer table to help identify the rows but not on the Table Variable which now needs to loop customers table for each row it contains. In order to allow both tables to be able to search each other properly we need to first put an index on the table variable which is why we can now uncomment the index create portion in the above script. As soon as the index is created the data in the Table Variable is sorted so we don’t need to scan the entire customer table for each row in the table variable and suddenly our Nested Loop join turns into a Merge Join requires far fewer pages and thus eliminating the table spill.

In this case we don’t see any marked improvement in the execution time we see that there is a significant improvement in IO characteristics on the above query due to avoiding the TempDB spills.

Before

After, notice how we don’t scan the Customers table 157985 times anymore. In other words the optimizer decided it won’t need RAM for that many pages and since I don’t have that much RAM anyway we avoid spilling to TempDB.

So that is how the Spills have been avoiding in this query. In the next post I enable intelligent query processing to see how it would have tuned the above code. As a excersice what do you think would have happened if we has simply rewritten our original query like below:-

 

DECLARE @tablevariable TABLE 
( 
customerid INT --primary key clustered
 
) 
 
INSERT INTO @tablevariable
SELECT customerid  FROM customers
WHERE IsMember =1
ORDER BY customerid
 
 
SELECT  c.customerid, password FROM Customers c
INNER JOIN  @tablevariable d
ON d.customerid = c.customerid
ORDER BY d.customerid