Category Archives: TSQL

Advent of Code – Day 2 – Using Cross apply

For this post i worked on second puzzle in the Adventofcode series. This wasn’t as challenging as the first one, but still very doable with TSQL.

Part 1 of the puzzle  was as below:

The elves are running low on wrapping paper, and so they need to submit an order for more. They have a list of the dimensions (length l, width w, and height h) of each present, and only want to order exactly as much as they need.

Fortunately, every present is a box (a perfect right rectangular prism), which makes calculating the required wrapping paper for each gift a little easier: find the surface area of the box, which is 2*l*w + 2*w*h + 2*h*l. The elves also need a little extra paper for each present: the area of the smallest side.

For example:

  • A present with dimensions 2x3x4 requires 2*6 + 2*12 + 2*8 = 52 square feet of wrapping paper plus 6 square feet of slack, for a total of 58square feet.
  • A present with dimensions 1x1x10 requires 2*1 + 2*10 + 2*10 = 42square feet of wrapping paper plus 1 square foot of slack, for a total of 43 square feet.

All numbers in the elves’ list are in feet. How many total square feet of wrapping paper should they order?

Solution: The input was about 1000 lines long, in the format of axbxc, with a , b and c being numbers for each of the three dimensions. It was too long to stick into an insert statement, so I put it into a text file and imported that text file into a table. I then parsed the string to get the 3 numbers out as length,breadth and width, different fields in the same table. Don’t think that part is really necessary as it is simple TSQL. For  getting the answer to the puzzle, I used below query:

SELECT SUM( 2*(length*breadth)
+ 2*(Breadth*Width)
+ 2*(Width*Length)
+ (IIF(Length*Breadth > Breadth*Width, IIF(BREADTH*width > Width*Length, WIDTH*Length, Breadth*Width),IIF(LengTH*Breadth > Width*Length, Width*Length, Length*Breadth))
)) FROM [dbo].[listofcodes]

 

For part 2: 

The elves are also running low on ribbon. Ribbon is all the same width, so they only have to worry about the length they need to order, which they would again like to be exact.

The ribbon required to wrap a present is the shortest distance around its sides, or the smallest perimeter of any one face. Each present also requires a bow made out of ribbon as well; the feet of ribbon required for the perfect bow is equal to the cubic feet of volume of the present. Don’t ask how they tie the bow, though; they’ll never tell.

For example:

  • A present with dimensions 2x3x4 requires 2+2+3+3 = 10 feet of ribbon to wrap the present plus 2*3*4 = 24 feet of ribbon for the bow, for a total of 34 feet.
  • A present with dimensions 1x1x10 requires 1+1+1+1 = 4 feet of ribbon to wrap the present plus 1*1*10 = 10 feet of ribbon for the bow, for a total of 14 feet.

How many total feet of ribbon should they order?

Solution: This needed a table valued UDF that returns the sum of the least two values given 3 values. Again, simple TSQL which I don’t think is worth a cut and paste. The final solution was as below. I learnt something from this. I always assumed that a table valued UDF would return something (null if there was no value) – but there was a bug in mine which caused it to return nothing for some rows. When the cross apply returned fewer rows than main table it led me to investigate and figure this out – This was an interesting new discovery. But otherwise, the solution was simple.

SELECT SUM((2*minvalue)+(length*breadth*width))
FROM [dbo].[listofcodes] l CROSS APPLY dbo.[udf_summingsides](length,l.breadth,l.width) f

On to the next one…am not sure I’d be blogging every one of these since several of them are seriously procedural in solution. But when I find one that is TSQL Friendly will get to it again. Thank you for reading.

Advent of Code – Day 1 – Parsing a variable

I have been following the series of posts written by my friend and SQL MCM Wayne Sheffield on some TSQL puzzles – these puzzles are from a guy named Eric Wastl and called ‘Advent of Code‘.
Your challenge basically after you sign up is to solve a series of puzzles arranged in the form of a christmas tree – each puzzle has two parts to  it, the second part will be revealed after the first part is successfully solved. While the fundamental puzzle is the same, each person will be presented with a different data set for solving it, so no two people get the same answer. You can paste your answer in and verify it for correctness. These puzzles are not TSQL/SQL Server specific, but TSQL lends itself beautifully well to solving them.

I took up the challenge and came up with my own answers – Day 1 as below:

Part 1 of puzzle: 

Santa is trying to deliver presents in a large apartment building, but he can’t find the right floor – the directions he got are a little confusing. He starts on the ground floor (floor 0) and then follows the instructions one character at a time.

An opening parenthesis, (, means he should go up one floor, and a closing parenthesis, ), means he should go down one floor.

The apartment building is very tall, and the basement is very deep; he will never find the top or bottom floors.

For example:

  • (()) and ()() both result in floor 0.
  • ((( and (()(()( both result in floor 3.
  • ))((((( also results in floor 3.
  • ()) and ))( both result in floor -1 (the first basement level).
  • ))) and )())()) both result in floor -3.

To what floor do the instructions take Santa? I am supplied with the string of braces as my data.

Solution :  

DECLARE @leftbrace CHAR(1), @rightbrace CHAR(1)
DECLARE @string VARCHAR(MAX)
SET @string = '()()(()()()(()()((()((()))((()((((()()((((()))()((((())(((((((()(((((((((()(((())(()()(()((()()(()(())(()((((()((()()()((((())((((((()(()(((()())(()((((()))())(())(()(()()))))))))((((((((((((()())()())())(())))(((()()()((((()(((()(()(()()(()(()()(()(((((((())(())(())())))((()())()((((()()((()))(((()()()())))(())))((((())(((()())(())(()))(()((((()())))())((()(())(((()((((()((()(())())))((()))()()(()(()))))((((((((()())((((()()((((()(()())(((((()(()())()))())(((()))()(()(()(()((((()(())(()))(((((()()(()()()(()(((())())(((()()(()()))(((()()(((())())(()(())())()()(())()()()((()(((()(())((()()((())()))((()()))((()()())((((()(()()(()(((()))()(()))))((()(((()()()))(()(((())()(()((()())(()(()()(()())(())()(((()(()())()((((()((()))))())()))((()()()()(())()())()()()((((()))))(()(((()()(((((((())()))()((((()((())()(()())(())()))(()(()())(((((((())))(((()))())))))()))())((())(()()((())()())()))))()((()()())(())((())((((()())())()()()(((()))())))()()))())(()()()(()((((((()()))())()))()(((()(((())((((()()()(()))())()()))))())()))())((())()())(((((())())((())())))(((())(((())(((((()(((((())(()(()())())(()(())(()))(()((((()))())()))))())))((()(()))))())))(((((())()))())()))))()))))(((()))()))))((()))((()((()(()(())()())))(()()()(())()))()((((())))))))(())(()((()()))(()))(()))(()((()))))))()()((((()()))()())()))))))()()()))(()((())(()))((()()()())()(((()((((())())))()((((()(()))))))())))()()())()))(()))))(()())()))))))((())))))))())()))()((())())))(()((()))()))(())))))(()))()())()()))((()(()))()()()()))))())()()))())(())()()))()))((()))))()()(()())))))()()()))((((()))()))))(()(())))(()())))((())())(()))()))))()())))()())()())))))))))()()))))())))((())((()))))())))(((()())))))))(()))()()))(()))()))))()())))))())((((()())))))))())))()()))))))))()))()))))()))))))(())))))))))())))))))))))))))())())((())))))))))()))((())))()))))))))())()(()))))))())))))()()()())()(()()()(()())(()))()()()(()())))())())))()))))())))))))()()()()())(())())()())()))))(()()()()()))))()))())())))((()())()())))()))()))))(()())))()))))))))(((()))()()))))))))))))))))))))(()))(()((()))())))())(()))(()(()(())))))()(()))()))()()))))))))))))()((()())(())())()(())))))())()())((()()))))(()()))))())()(())()))))))))))))))))))))()))(()(()())))))))()()((()))()))))))((())))()))))))))((()))())()()))())()()))((()))())))))))))))(()())()))(())((()(()()))(()())(())))()())(()(())()()))))()))()(()))))))(()))))))))))(()))())))))))))())))))())))(())))))()))))(())())))))))))()(()))))()())))())(()))()())))))))))))))())()()))))()))))))())))))()))))(())(()()()()((())()))())(()))((())()))())())(())(()()))))()))(())()()((())(())))(())))()))())))))))))()(((((())())))(())()))))(())))((()))()(((((((()))))()()))(())))))()(()))))(()()))()))())))))))(()())()))))))))())))(()))())()))(())()((())())()())())(()(()))))()))))))((()())(())()()(()())))()()))(())(())(()))())))()))(()))()()))((((()))))()))((()()()))))()))()))())))(()))()))))(())))()))())()(()))()())))())))))))())))())))()()))))))(()))())())))()))()()())())))))))))))))())))()))(()()))))())))())()(())))())))))))))))))))))()()())())))))()()()((()(()))()()(())()())()))()))))()()()))))))((()))))))))()(()(()((((((()()((()())))))))))))()))())))))((())())(()))())))())))))())()()())(())))())))()())())(())))))))()()(())))()))())))())())())()))))))))()))(()()()())())())))(())())))))))()()())()))))())))())()(())())))))))()())()))(()()(())())))()(()((()()((()()(((((())(()())()))(())()))(())))(())))))))()))()))((()))()))()))))))))()))))))))((()()())(()))(((()))(())))()))((())(((())))()())))())))))((())))))(())())((((((())())()(()))()(()((()())))((())()(()(()))))(())(()()())(())))())((()(((())())))(((()())())))())()(())())((((()()))))())((()))()()()()(())(((((((()()()((()))())(()())))(())())((((()()(()))))()((())))((())()))()(((()))())))()))((()(()))(())(()((((())((((()()(()()))(((())(()))))((((()(()))(())))))((()))(()))((()(((()(()))(()(()((()(())(()(()(()(()()((()))())(((())(()(()))))(()))()()))(())))(())()(((())(()))()((((()()))))())(()))))((())()((((()(((()))())())(((()))()())((())(())())(())()(())()(()()((((((()()))))()()(((()()))))()())()(((()(()))(()(()())(()(()))))(((((()(((())())))))(((((()((()()((())())((((((()(())(()()((()()()()()()()(()()))()(((()))()))(((((((())(((()((()())()((((())(((()(())))()((()(()()()((())((()())()))()))())))())((((((()))(()(()()()))(()((()(()(()))()((()(((()()()((())(((((())()(()))())())((()(())))(()(()())(())((())())())(((()()()(())))))())(()))))))()))))))())((()()()))((()((((((()))(((()((((()()()(((()))())()(()()(((()((()()()()())()()))()()()(()(())((()))))(()))())))))))()(()()(((((())()(()(((((()((()(()()())(()((((((((()((((((())()((((()()()((()((()((((((()))((())))))))())()))((()(()))()(()()(()((())((()()((((((((((((()())(()()()))((((()((((((())(()))())(()()((()()))()(((((((()((()()((((((()(((())))((())))((((((((()()(((((((())(((((()())(((())((())()((((()(((((((()(()(((()((((((()(((()(((((((((((()()((()()(()))((()()(((()(((())))((((())()(()(((())()(()(((())(((((((((((()))())))((((((())((()()((((()())())((((()()))((())(((((()(()()(()()()((())(()((()()((((()(((((()((()(()((((()())((((((()(((((()()(()(()((((())))(())(())(())((((()(()()((((()((((()()((()((((((())))(((((()))))()))(()((((((((()(((())())(((())))(()(()((())(((()((()()(((((()((()()(((())()(()))(((((((())(()(((((()))((()((()((()))(())())((((()((((())()(()))(((()(((((((((((((((())(((((((((()))(((()(()()()()((((((()((())()((((((((()(())(((((((((((()(()((())()((()()(()(()()((((()()((())(()((()()(()()((((()(((((((())))((((())(())()(((()()((()()((((()((()(((()((())(((()()()((((()((((()()(()(()((((((((())(()(((((())(()())(((((((()())()(()((((()((())(()()())((((()()(((()((((())(())(()()(((((((((()()))()(((())(()(()((((((())(()()())(()))()()(((()(((()((())(()(((((((()(()(()((()(((((()(()((()(()((((((()((((()()((((()(((()((())(()(()((()()((((()()(())()(())(((())(()((((((((()())(((((((((()(())()((((())))()))()()(((((()()((((((())(()()(((()(()(((((((()(()(((((((())(())((((()((()(())))((((()()())(()))((()())((((()(((((()(()(())(()(()()())(((((()(((((()((((()()((((((((()()))(()((((((())((((())()(()(((()()()(((()(()(())(())(((((()(())())((((())(())(()(((()(((((())((((())())((()(((((((()(((())(()(()))(((((((((()((()((()()(()((((())(((()((())((((())(()(((()(((()(()((((()(((())(()(((()(()()(()(()((()()(()())(())())((()(()(((()(((()(((()()(((((((((()(((((((((()()(((()(((()())((((()(()(((()()()((())((((((((((())(()(((()((((()())((((()((()))(((()()()(((((()(((((((())((()())(()((((())((((((((())(()((()((((((((((()()((()((()()))(((()())()())()(((()())()()(()(()(((((((())()))(())()))())()()((())()((()((((()((()((())(((((()((((((()(())))(()))())(((()))((()()(()(((()))((((())()(((()))))()(()(())()(((((())(()(()(())(())()((()()()((((()(())((()())(()(()))(()(()(()()(())()()(()((())()((()))))()))((()(()()()()((()())(()))())()(()(((((((((())())((()((()((((((())()((((())(((())((()(()()()((())(()((())(((()((((()()((()(()(((((())()))()((((((()))((())(((()()))(((())(())()))(((((((())(())())()(())(((((()))()((()))()(()()((()()()()()())(((((((';
SET @leftbrace = '('
SET @rightbrace = ')'
SELECT  (LEN(@string) - LEN(REPLACE(@string, @rightbrace, ''))) - (LEN(@string) - LEN(REPLACE(@string, @leftbrace, '')))

Easy peasy right? My answer was 280. Now harder..onto part 2

Part 2 : Now, given the same instructions, find the position of the first character that causes him to enter the basement (floor -1). The first character in the instructions has position 1, the second character has position 2, and so on.

For example:

  • ) causes him to enter the basement at character position 1.
  • ()()) causes him to enter the basement at character position 5.

What is the position of the character that causes Santa to first enter the basement?

This gets much  harder..well  sort of..it is easy to do character by character, the RBAR way, although you are parsing a variable here and that might be excusable given how TSQL works  generally with this..but a better way perhaps would be to parse the string using a recursive CTE – as below. My answer was 1797. I had  fun doing this as  i had not parsed a variable this way and learnt how. I hope you do too..and do remember, your set of data is different , so don’t make the same mistake I  did (cut and paste from here and wonder why the site is  not accepting your answer!!).

Happy Coding Fun and for the next part, next week!

 

DECLARE @start INT, @end INT, @pos INT
DECLARE @leftbrace CHAR(1)
SET @leftbrace = '(';
SET @string = '()()(()()()(()()((()((()))((()((((()()((((()))()((((())(((((((()(((((((((()(((())(()()(()((()()(()(())(()((((()((()()()((((())((((((()(()(((()())(()((((()))())(())(()(()()))))))))((((((((((((()())()())())(())))(((()()()((((()(((()(()(()()(()(()()(()(((((((())(())(())())))((()())()((((()()((()))(((()()()())))(())))((((())(((()())(())(()))(()((((()())))())((()(())(((()((((()((()(())())))((()))()()(()(()))))((((((((()())((((()()((((()(()())(((((()(()())()))())(((()))()(()(()(()((((()(())(()))(((((()()(()()()(()(((())())(((()()(()()))(((()()(((())())(()(())())()()(())()()()((()(((()(())((()()((())()))((()()))((()()())((((()(()()(()(((()))()(()))))((()(((()()()))(()(((())()(()((()())(()(()()(()())(())()(((()(()())()((((()((()))))())()))((()()()()(())()())()()()((((()))))(()(((()()(((((((())()))()((((()((())()(()())(())()))(()(()())(((((((())))(((()))())))))()))())((())(()()((())()())()))))()((()()())(())((())((((()())())()()()(((()))())))()()))())(()()()(()((((((()()))())()))()(((()(((())((((()()()(()))())()()))))())()))())((())()())(((((())())((())())))(((())(((())(((((()(((((())(()(()())())(()(())(()))(()((((()))())()))))())))((()(()))))())))(((((())()))())()))))()))))(((()))()))))((()))((()((()(()(())()())))(()()()(())()))()((((())))))))(())(()((()()))(()))(()))(()((()))))))()()((((()()))()())()))))))()()()))(()((())(()))((()()()())()(((()((((())())))()((((()(()))))))())))()()())()))(()))))(()())()))))))((())))))))())()))()((())())))(()((()))()))(())))))(()))()())()()))((()(()))()()()()))))())()()))())(())()()))()))((()))))()()(()())))))()()()))((((()))()))))(()(())))(()())))((())())(()))()))))()())))()())()())))))))))()()))))())))((())((()))))())))(((()())))))))(()))()()))(()))()))))()())))))())((((()())))))))())))()()))))))))()))()))))()))))))(())))))))))())))))))))))))))())())((())))))))))()))((())))()))))))))())()(()))))))())))))()()()())()(()()()(()())(()))()()()(()())))())())))()))))())))))))()()()()())(())())()())()))))(()()()()()))))()))())())))((()())()())))()))()))))(()())))()))))))))(((()))()()))))))))))))))))))))(()))(()((()))())))())(()))(()(()(())))))()(()))()))()()))))))))))))()((()())(())())()(())))))())()())((()()))))(()()))))())()(())()))))))))))))))))))))()))(()(()())))))))()()((()))()))))))((())))()))))))))((()))())()()))())()()))((()))())))))))))))(()())()))(())((()(()()))(()())(())))()())(()(())()()))))()))()(()))))))(()))))))))))(()))())))))))))())))))())))(())))))()))))(())())))))))))()(()))))()())))())(()))()())))))))))))))())()()))))()))))))())))))()))))(())(()()()()((())()))())(()))((())()))())())(())(()()))))()))(())()()((())(())))(())))()))())))))))))()(((((())())))(())()))))(())))((()))()(((((((()))))()()))(())))))()(()))))(()()))()))())))))))(()())()))))))))())))(()))())()))(())()((())())()())())(()(()))))()))))))((()())(())()()(()())))()()))(())(())(()))())))()))(()))()()))((((()))))()))((()()()))))()))()))())))(()))()))))(())))()))())()(()))()())))())))))))())))())))()()))))))(()))())())))()))()()())())))))))))))))())))()))(()()))))())))())()(())))())))))))))))))))))()()())())))))()()()((()(()))()()(())()())()))()))))()()()))))))((()))))))))()(()(()((((((()()((()())))))))))))()))())))))((())())(()))())))())))))())()()())(())))())))()())())(())))))))()()(())))()))())))())())())()))))))))()))(()()()())())())))(())())))))))()()())()))))())))())()(())())))))))()())()))(()()(())())))()(()((()()((()()(((((())(()())()))(())()))(())))(())))))))()))()))((()))()))()))))))))()))))))))((()()())(()))(((()))(())))()))((())(((())))()())))())))))((())))))(())())((((((())())()(()))()(()((()())))((())()(()(()))))(())(()()())(())))())((()(((())())))(((()())())))())()(())())((((()()))))())((()))()()()()(())(((((((()()()((()))())(()())))(())())((((()()(()))))()((())))((())()))()(((()))())))()))((()(()))(())(()((((())((((()()(()()))(((())(()))))((((()(()))(())))))((()))(()))((()(((()(()))(()(()((()(())(()(()(()(()()((()))())(((())(()(()))))(()))()()))(())))(())()(((())(()))()((((()()))))())(()))))((())()((((()(((()))())())(((()))()())((())(())())(())()(())()(()()((((((()()))))()()(((()()))))()())()(((()(()))(()(()())(()(()))))(((((()(((())())))))(((((()((()()((())())((((((()(())(()()((()()()()()()()(()()))()(((()))()))(((((((())(((()((()())()((((())(((()(())))()((()(()()()((())((()())()))()))())))())((((((()))(()(()()()))(()((()(()(()))()((()(((()()()((())(((((())()(()))())())((()(())))(()(()())(())((())())())(((()()()(())))))())(()))))))()))))))())((()()()))((()((((((()))(((()((((()()()(((()))())()(()()(((()((()()()()())()()))()()()(()(())((()))))(()))())))))))()(()()(((((())()(()(((((()((()(()()())(()((((((((()((((((())()((((()()()((()((()((((((()))((())))))))())()))((()(()))()(()()(()((())((()()((((((((((((()())(()()()))((((()((((((())(()))())(()()((()()))()(((((((()((()()((((((()(((())))((())))((((((((()()(((((((())(((((()())(((())((())()((((()(((((((()(()(((()((((((()(((()(((((((((((()()((()()(()))((()()(((()(((())))((((())()(()(((())()(()(((())(((((((((((()))())))((((((())((()()((((()())())((((()()))((())(((((()(()()(()()()((())(()((()()((((()(((((()((()(()((((()())((((((()(((((()()(()(()((((())))(())(())(())((((()(()()((((()((((()()((()((((((())))(((((()))))()))(()((((((((()(((())())(((())))(()(()((())(((()((()()(((((()((()()(((())()(()))(((((((())(()(((((()))((()((()((()))(())())((((()((((())()(()))(((()(((((((((((((((())(((((((((()))(((()(()()()()((((((()((())()((((((((()(())(((((((((((()(()((())()((()()(()(()()((((()()((())(()((()()(()()((((()(((((((())))((((())(())()(((()()((()()((((()((()(((()((())(((()()()((((()((((()()(()(()((((((((())(()(((((())(()())(((((((()())()(()((((()((())(()()())((((()()(((()((((())(())(()()(((((((((()()))()(((())(()(()((((((())(()()())(()))()()(((()(((()((())(()(((((((()(()(()((()(((((()(()((()(()((((((()((((()()((((()(((()((())(()(()((()()((((()()(())()(())(((())(()((((((((()())(((((((((()(())()((((())))()))()()(((((()()((((((())(()()(((()(()(((((((()(()(((((((())(())((((()((()(())))((((()()())(()))((()())((((()(((((()(()(())(()(()()())(((((()(((((()((((()()((((((((()()))(()((((((())((((())()(()(((()()()(((()(()(())(())(((((()(())())((((())(())(()(((()(((((())((((())())((()(((((((()(((())(()(()))(((((((((()((()((()()(()((((())(((()((())((((())(()(((()(((()(()((((()(((())(()(((()(()()(()(()((()()(()())(())())((()(()(((()(((()(((()()(((((((((()(((((((((()()(((()(((()())((((()(()(((()()()((())((((((((((())(()(((()((((()())((((()((()))(((()()()(((((()(((((((())((()())(()((((())((((((((())(()((()((((((((((()()((()((()()))(((()())()())()(((()())()()(()(()(((((((())()))(())()))())()()((())()((()((((()((()((())(((((()((((((()(())))(()))())(((()))((()()(()(((()))((((())()(((()))))()(()(())()(((((())(()(()(())(())()((()()()((((()(())((()())(()(()))(()(()(()()(())()()(()((())()((()))))()))((()(()()()()((()())(()))())()(()(((((((((())())((()((()((((((())()((((())(((())((()(()()()((())(()((())(((()((((()()((()(()(((((())()))()((((((()))((())(((()()))(((())(())()))(((((((())(())())()(())(((((()))()((()))()(()()((()()()()()())(((((((';
SET @start = 1
SET @pos = 0;
WITH a AS
(SELECT [start] = 1,
 [end] = LEN(@string),
 [pos] = IIF(SUBSTRING(@string, @start, 1) = @leftbrace, @pos + 1, @pos - 1)
 UNION ALL
 SELECT [start] = [start] + 1,
 [end] = LEN(@string),
 [pos] = IIF(SUBSTRING(@string, [start]+1, 1) = @leftbrace, [pos] + 1, [pos] - 1)
 FROM a
 WHERE [start] <= [end])
SELECT TOP(1) [start] FROM a WHERE pos = -1 OPTION (MAXRECURSION 0)

Comparing data in two identical tables

Continuing with the recipes in SQL Server 2012 T-SQL recipes book – I was drawn to this puzzle that asked for how you would compare data in two identical tables using a single query. Now, if they didn’t specify the means I would readily point them to Red Gate’s SQL Data Compare – a nifty great tool that does this kind of stuff and gives an awesome report on differences. But as we all know, not all companies have tools. And, if you are presented  this question at an interview – that would probably not be an acceptable answer.
My answer to this problem is different from what is in the book – mainly because I wanted a generic query that I could use on any table. The book  deals with grouping on a field-to-field basis which is table specific and would come in handy if the situation demands that.
My solution is as below – I took two tables in Adventureworks, Password and Passwordcopy which is an identical version of Password. I made some changes to the latter as below(updated two records, added one record and changed two more via  management studio).

TABLECOMPARE1

Now I ran query as below to give me differences.

TABLECOMPARE2

It gave me the differences I was looking for. I can run the first part of the query before union to see what of these came from first table and second part to see what is in the second. Of course, it is not SQL Data Compare – it does not tell me what the differences are but it is a simple easy way to get a look.

TSQL Puzzle

Today’s TSQL post is an answer to a puzzle posted by Adam Machanic. I learnt something new via this puzzle today. The puzzle is as below – what would this query return?

SELECT * FROM(VALUES(1),(2)) AS x(i) WHERE EXISTS (SELECT MAX(i) FROM (VALUES(1)) AS y(i) WHERE y.i=x.i)

Upon looking at it, I thought I would get 1 row (1) – ecause the second where condition was looking for matches to 1. But I got two rows, 1 and 2. So I broke up the query as below to dig further:

SELECT * FROM

(VALUES(1),(2)) AS x(i) WHERE

EXISTS (SELECT MAX(i) FROM (VALUES(1)) AS y(i) WHERE y.i=x.i)

–this returns two rows , 1 and 2

SELECT * FROM (VALUES(1),(2)) AS x(i)
–this also returns 1 and 2

SELECT MAX(i) FROM (VALUES(1)) AS y(i)
–this returns 1

SELECT MAX(i) FROM (VALUES(2)) AS y(i)
–this returns 2

SELECT * FROM (VALUES(1),(2)) AS x(i) WHERE

EXISTS (SELECT MAX(i) FROM (VALUES(1)) AS y(i) WHERE 1=2)

–Bingo, I got my answer!! Even if exists clause is null it still returns two rows , 1 and 2.
This was a very interesting discovery and one that I will always remember when using MAX in and embedded select. Thanks, Adam.

 

TSQL-Paging through a resultset

I have been out of touch with latest features in TSQL for a while now. That mostly happened because my dba gigs in the past few years have not involved a lot of programming. To come upto speed with TSQL, am working with a book TSQL recipes written by my good friends Wayne Sheffield(blog|twitter), Jason Brimhall(blog|twitter) and some others. Am going to pick one ‘recipe’ from the book for each blog post and write of what I learnt from it.  Today’s recipe is a simple problem –

‘You wish to present a result set to an application user n rows at a time, how will you do it’.

This lead me to research two keywords in TSQL – OFFSET and FETCH NEXT.These are used together. What Offset does is to tell the
resultsets to skip first <x> rows, and Fetch Next <y> rows grabs the y rows after the offset. So suppose my requirement at a more refined level is to return 100 products at a time to the application, ordered by product id – I would go for a stored procedure  as below:

CREATE PROCEDURE Paging100Rows

@cycle INTEGER

AS

DECLARE @offsetrows INTEGER, @fetchrows INTEGER

SET @offsetrows = @cycle

SET @fetchrows = 100

SELECT ProductID,[Name] FROM [Production].[Product]

ORDER BY [ProductID]

OFFSET @offsetrows ROWS FETCH NEXT @fetchrows ROWS ONLY;

So if I wanted the first 100 rows, I would call it as below..for the next 100 rows I’d pass 100 and the next 100 rows 200 and so on.

paging100rows

What to look out for:

1 The sort order must be very specific and related to the rows you want returned.
2 Any change in isolation level will impact results with dirty reads/non repeatable reads etc. (as it does with any select statement of any kind)

Related links:
https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

 

 

 

What it took to stump the optimizer

Sometime ago I was working with a Microsoft support technician on a ticket related to some database issues on SCOM. The technician wanted to run a stored procedure to clear out some of the backlog we were seeing with a batch job.

When he ran the procedure (via Query Analyser) – we encountered an error as below. Basically an error that is wee bit rare, the optimizer telling us that it is stumped and the query is too big for it to process. The technician moved on to other ways of resolving the issue as fixing this problem involved touching some procedures that came with the product and was beyond the scope of our call. But after we were done, I was intrigued to find out what caused the optimizer to give up.scomoptimiser

I went to Line 177 of the specified stored procedure and found a statement that looked pretty small on the outside:

DELETE dbo.[CategoryOverride]

FROM dbo.[CategoryOverride] CO

JOIN #BaseManagedEntitiesToPurge BMETP

ON CO.[InstanceContext] = BMETP.[BaseManagedEntityId]

The table CategoryOverride had about 300 rows..and  on checking what went into the temp table – it wasn’t all that large either, only around 100 rows. Looking at the estimated plan from the optimizer gave me nothing – a perfectly ordinary plan with an equijoin on a clustered index. So what was causing the problem?

I looked to see if the table CategoryOverride had any triggers on it. None. Then I went on to looking at keys. The table had one foreign key defined on it.

scomo1

I decided to script this key and see what it showed me.

ALTER TABLE [dbo].[CategoryOverride] WITH CHECK ADD CONSTRAINT [FK_CategoryOverride_ManagementPack] FOREIGN KEY([ManagementPackId])

REFERENCES [dbo].[ManagementPack] ([ManagementPackId])

ON DELETE CASCADE

GO

There was my culprit – an ON DELETE CASCADE. So many other tables is this touching? A quick look at the dependancies on this table showed me no less than 14. For one record to be deleted – there were dependancies on 14 tables. I picked one record on the main table and counted how many dependant records were there to clean up. It was about 6000. So the delete statement was far from a benign straightforward delete – it had a huge amount of data to clean up and the optimizer could not generate a plan across 14 tables some of them having more cascade deletes on them.

Moral of the story – never assume the simplicity of a statement based on looking at it, or what the query optimizer tells you. The truth may be much deeper. If I had the choice of fixing it I would remove the cascade delete and go on to cleaning up data step-by-step in each of the dependant tables.

Finding users who are Sysadmins

We had a requirement on a server supporting a vendor application to monitor users who are given sysadmin rights. This was because vendor personnel would need these privileges every now and then – and it was our job to monitor when they are active and see that they are disabled on time. To achieve this objective I was asked to write a stored procedure based job that would get a list of people who were SA’s and send an email to the DBA team manager with the list. Below is the procedure I wrote to achieve this objective.

/*********************************************************************************************
Authored by: Malathi Mahadevan 2/27/2013

Tested on : SQL Server 2005 and 2008 only

Version 1.00
**********************************************************************************************/

CREATE PROCEDURE usp_getsanames
AS
DECLARE @tableHTML  NVARCHAR(MAX)
Declare @T table(saname varchar(100),sarole varchar(500),saenabled smallint)
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO @T(saname,sarole,saenabled)
— Insert statements for procedure here
SELECT  mem.name,mem.type_desc,mem.is_disabled
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS rol ON rol.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals AS mem ON mem.principal_id = srm.member_principal_id
WHERE rol.name = ‘sysadmin’
and mem.type_desc <> ‘SQL_LOGIN’

SET @tableHTML =    N'<H1>SYSADMIN ROLE REPORT</H1>’ +
N'<table border=”1″>’ +
N'<tr><th>Name</th><th>Role</th><th>Status</th>’
+
CAST ((
SELECT  td = CAST(([saname]) AS nvarchar(100)),”,
td = CAST((sarole) as nvarchar(15)),”,
td = CAST((CASE  [saenabled]
WHEN 0 THEN ‘ENABLED’
WHEN 1 THEN ‘Disabled’
END) AS NVARCHAR(50))
FROM @T
–ORDER BY [name]
FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX) ) +    N'</table>’

IF @@ROWCOUNT > 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@recipients =
‘dbamanager@mycompany.com’,
@subject =
‘SYSADMIN ROLE MEMBERS -VENDOR’,    @importance = ‘High’,    @body =   @tableHTML,    @body_format = ‘HTML’
end
ELSE
BEGIN
SELECT @TABLEHTML =  N'<H1>SYSADMIN ROLE REPORT</H1>’ +
N'<table border=”1″>’ +
N'<tr><th>No records found</th>’
END

END

T-SQL Tuesday #025 – Invitation to Share Your Tricks

This is my first response to a TSQL Tuesday.

There are lot of things in TSQL that can be done to make DBA’s life easier. We have a large collection of scripts. These are some I have used over the years at multiple jobs – there are several more but I don’t particularly want to write a very long post. Also a disclaimer that I did not write all of them.  I wrote some,  I tweaked some I found on sites like SQLServerCentral.com that have come in very handy. I apologize ahead for not offering credit since I have not kept track of the authors.

–Find the last server start date(2005)
SELECT crdate AS Datetime_Started FROM sysdatabases WHERE name = ‘tempdb’

1 –Find Blank passwords
SELECT name FROM sys.syslogins WHERE pwdcompare(””, password) = 1

2 –Find members of a role (for example, public)
SELECT a.[name] + ‘ ‘ + v.[name] + ‘ ON ‘ + QuoteName(oo.[name]) + ‘.’ + QuoteName(o.[name]) + ‘ TO ‘ + QuoteName(u.[name])
FROM dbo.sysprotects AS p JOIN master.dbo.spt_values AS a
ON (a.number = p.protecttype AND ‘T’ = a.type) JOIN master.dbo.spt_values
AS v ON (v.number = p.action AND ‘T’ = v.type) JOIN dbo.sysobjects AS o ON (o.id = p.id)
JOIN dbo.sysusers AS oo ON (oo.uid = o.uid) JOIN dbo.sysusers AS u ON (u.uid = p.uid) WHERE ‘public’ = u.name

3 –Find number of times a stored procedure was used since last reboot
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = ‘proc’
group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by max(cp.usecounts)

4 –Stick in anything to be changed across all dbs (just used sp_dboption with read only here)
SET NOCOUNT ON

— Get the name of all databases
DECLARE UserDatabases CURSOR FOR

SELECT name FROM master..sysdatabases
where name not in (‘master’,’tempdb’, ‘model’, ‘msdb’, ‘distribution’)

— Open Cursor
OPEN UserDatabases

— Define variables needed
DECLARE @DBase NVARCHAR(128)
DECLARE @COMMAND NVARCHAR(128)

— Get First database
FETCH NEXT FROM USerDatabases INTO @DBase

— Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN

set @command =’master..sp_dboption @dbname=”’ + @dbase +
”’,@optname=”Read ONLY”, @optvalue=”True”’

— Print command to be processed
print @command

— Process Command
exec (@command)

— Get next database
FETCH NEXT FROM UserDatabases INTO @DBase

END

— Close and Deallocate Cursor
CLOSE AllDatabases
DEALLOCATE AllDatabases

5 —-My favorite, lists job calender

USE MSDB

DECLARE

@JOB_ID VARCHAR(200),

@SCHED_ID VARCHAR(200),

@FREQ_TYPE INT,

@FREQ_INTERVAL INT,

@FREQ_SUBDAY_TYPE INT,

@FREQ_SUBDAY_INTERVAL INT,

@FREQ_RELATIVE_INTERVAL INT,

@FREQ_RECURRENCE_FACTOR INT,

@ACTIVE_START_DATE INT,

@SCHEDULE VARCHAR(1000),

@SCHEDULE_DAY VARCHAR(200),

@START_TIME VARCHAR(10),

@END_TIME VARCHAR(10)

CREATE TABLE #SCHEDULES

(JOB_ID VARCHAR(200),

SCHED_ID VARCHAR(200),

JOB_NAME SYSNAME,

[STATUS] INT,

SCHEDULED INT NULL,

schedule VARCHAR(1000) NULL,

FREQ_TYPE INT NULL,

FREQ_INTERVAL INT NULL,

FREQ_SUBDAY_TYPE INT NULL,

FREQ_SUBDAY_INTERVAL INT NULL,

FREQ_RELATIVE_INTERVAL INT NULL,

FREQ_RECURRENCE_FACTOR INT NULL,

ACTIVE_START_DATE INT NULL,

ACTIVE_END_DATE INT NULL,

ACTIVE_START_TIME INT NULL,

ACTIVE_END_TIME INT NULL,

DATE_CREATED DATETIME NULL)

INSERT INTO #SCHEDULES (

job_id,

sched_id ,

job_name ,

[status] ,

Scheduled ,

schedule ,

freq_type,

freq_interval,

freq_subday_type,

freq_subday_interval,

freq_relative_interval,

freq_recurrence_factor,

active_start_date,

active_end_date,

active_start_time,

active_end_time,

date_created)

SELECT

j.job_id,

sched.schedule_id,

j.name ,

j.enabled,

sched.enabled,

NULL,

sched.freq_type,

sched.freq_interval,

sched.freq_subday_type,

sched.freq_subday_interval,

sched.freq_relative_interval,

sched.freq_recurrence_factor,

sched.active_start_date,

sched.active_end_date,

sched.active_start_time,

sched.active_end_time,

j.date_created

FROM sysjobs j

inner join

sysjobschedules s

ON j.job_id=s.job_id

INNER JOIN dbo.sysschedules sched

ON s.schedule_id = sched.schedule_id

WHILE 1=1

BEGIN

SET @SCHEDULE = ”

IF (SELECT COUNT(*) FROM #SCHEDULES WHERE scheduled=1 and schedule is null) = 0

BREAK

ELSE

BEGIN

SELECT

@job_id=job_id,

@sched_id=sched_id,

@freq_type=freq_type,

@Freq_Interval=freq_interval,

@freq_subday_type=freq_subday_type,

@freq_subday_interval=freq_subday_interval,

@freq_relative_interval=freq_relative_interval,

@freq_recurrence_factor=freq_recurrence_factor,

@active_start_date = active_start_date,

@start_time =

CASE

WHEN

LEFT(active_start_time, 2) IN (22, 23) AND len(active_start_time) = 6

THEN

convert(varchar(2), left(active_start_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M’

WHEN

left(active_start_time, 2) = (12) AND len(active_start_time) = 6

THEN

cast(LEFT(active_start_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_start_time, 2) BETWEEN 13 AND 24 AND len(active_start_time) = 6

THEN

convert(varchar(2), left(active_start_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_start_time, 2) IN (10, 11) AND len(active_start_time) = 6

THEN

cast(LEFT(active_start_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ‘ A.M.’

WHEN

active_start_time = 0

THEN

’12:00 A.M.’

WHEN

LEN(active_start_time) = 4

THEN

’12:’ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 3

THEN

’12:0′ + convert(varchar(2), left(active_start_time, 1) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 2

THEN

’12:00:’ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_start_time) = 1

THEN

’12:00:0′ + convert(varchar(2), left(active_start_time, 2) ) + ‘ A.M.’

ELSE

cast(LEFT(active_start_time,1) as char(1))

+ ‘:’ + SUBSTRING(CAST(active_start_time AS CHAR),2, 2) + ‘ A.M.’

END,

@END_TIME= CASE

WHEN

left(active_end_time, 2) IN (22, 23) AND len(active_end_time) = 6

THEN

convert(varchar(2), left(active_end_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M’

WHEN

left(active_end_time, 2) = (12) AND len(active_end_time) = 6

THEN

cast(LEFT(active_end_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_end_time, 2) BETWEEN 13 AND 24 AND len(active_end_time) = 6

THEN

convert(varchar(2), left(active_end_time, 2) – 12)

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ P.M.’

WHEN

left(active_end_time, 2) IN (10, 11) AND len(active_end_time) = 6

THEN

cast(LEFT(active_end_time,2) as char(2))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ‘ A.M.’

WHEN

active_end_time = 0

THEN

’12:00 A.M.’

WHEN

LEN(active_end_time) = 4

THEN

’12:’ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 3

THEN

’12:0′ + convert(varchar(2), left(active_end_time, 1) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 2

THEN

’12:00:’ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

WHEN

LEN(active_end_time) = 1

THEN

’12:00:0′ + convert(varchar(2), left(active_end_time, 2) ) + ‘ A.M.’

ELSE

cast(LEFT(active_end_time,1) as char(1))

+ ‘:’ + SUBSTRING(CAST(active_end_time AS CHAR),2, 2) + ‘ A.M.’

END

FROM #SCHEDULES

WHERE schedule is null

AND scheduled=1

IF EXISTS(SELECT @freq_type WHERE @freq_type in (1,64))

BEGIN

SELECT @SCHEDULE = CASE @freq_type

WHEN 1 THEN ‘Occurs Once, On ‘+cast(@active_start_date as varchar(8))+’, At ‘+@start_time

WHEN 64 THEN ‘Occurs When SQL Server Agent Starts’

END

END

ELSE

BEGIN

IF @freq_type=4

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_interval as varchar(10))+’ Day(s)’

END

IF @freq_type=8

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_recurrence_factor as varchar(3))+’ Week(s)’

SELECT @schedule_day=”

IF (SELECT (convert(int,(@freq_interval/1)) % 2)) = 1

select @schedule_day = @schedule_day+’Sun’

IF (SELECT (convert(int,(@freq_interval/2)) % 2)) = 1

select @schedule_day = @schedule_day+’Mon’

IF (SELECT (convert(int,(@freq_interval/4)) % 2)) = 1

select @schedule_day = @schedule_day+’Tue’

IF (SELECT (convert(int,(@freq_interval/8)) % 2)) = 1

select @schedule_day = @schedule_day+’Wed’

IF (SELECT (convert(int,(@freq_interval/16)) % 2)) = 1

select @schedule_day = @schedule_day+’Thu’

IF (SELECT (convert(int,(@freq_interval/32)) % 2)) = 1

select @schedule_day = @schedule_day+’Fri’

IF (SELECT (convert(int,(@freq_interval/64)) % 2)) = 1

select @schedule_day = @schedule_day+’Sat’

SELECT @SCHEDULE = @SCHEDULE+’, On ‘+@schedule_day

END

IF @freq_type=16

BEGIN

SELECT @SCHEDULE = ‘Occurs Every ‘+cast(@freq_recurrence_factor as varchar(3))+’ Month(s) on Day ‘+cast(@freq_interval as varchar(3))+’ of that Month’

END

IF @freq_type=32

BEGIN

SELECT @SCHEDULE = CASE @freq_relative_interval

WHEN 1 THEN ‘First’

WHEN 2 THEN ‘Second’

WHEN 4 THEN ‘Third’

WHEN 8 THEN ‘Fourth’

WHEN 16 THEN ‘Last’

ELSE ‘Not Applicable’

END

SELECT @SCHEDULE =

CASE @freq_interval

WHEN 1 THEN ‘Occurs Every ‘+@SCHEDULE+’ Sunday of the Month’

WHEN 2 THEN ‘Occurs Every ‘+@SCHEDULE+’ Monday of the Month’

WHEN 3 THEN ‘Occurs Every ‘+@SCHEDULE+’ Tueday of the Month’

WHEN 4 THEN ‘Occurs Every ‘+@SCHEDULE+’ Wednesday of the Month’

WHEN 5 THEN ‘Occurs Every ‘+@SCHEDULE+’ Thursday of the Month’

WHEN 6 THEN ‘Occurs Every ‘+@SCHEDULE+’ Friday of the Month’

WHEN 7 THEN ‘Occurs Every ‘+@SCHEDULE+’ Saturday of the Month’

WHEN 8 THEN ‘Occurs Every ‘+@SCHEDULE+’ Day of the Month’

WHEN 9 THEN ‘Occurs Every ‘+@SCHEDULE+’ Weekday of the Month’

WHEN 10 THEN ‘Occurs Every ‘+@SCHEDULE+’ Weekend Day of the Month’

END

END

SELECT @SCHEDULE =

CASE @freq_subday_type

WHEN 1 THEN @SCHEDULE+’, At ‘+@start_time

WHEN 2 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Second(s) Between ‘+@start_time+’ and ‘+@END_TIME

WHEN 4 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Minute(s) Between ‘+@start_time+’ and ‘+@END_TIME

WHEN 8 THEN @SCHEDULE+’, every ‘+cast(@freq_subday_interval as varchar(3))+’ Hour(s) Between ‘+@start_time+’ and ‘+@END_TIME

END

END

END

UPDATE #SCHEDULES

SET schedule=@SCHEDULE

WHERE job_id=@job_id

AND sched_id=@sched_Id

END

SELECT job_name ,

[status] = CASE STATUS

WHEN 1 THEN ‘ENABLED’

WHEN 0 THEN ‘DISABLED’

ELSE ‘ ‘

END,

scheduled= case scheduled

when 1 then ‘Yes’

when 0 then ‘No’

else ‘ ‘

end,

schedule as ‘Frequency’ ,

convert(datetime, convert(varchar,active_start_date, 101)) AS schedule_start_date,

convert(datetime, convert(varchar,active_end_date, 101)) AS schedule_end_date,

date_created

FROM #schedules

WHERE scheduled=1

ORDER BY job_name

DROP TABLE #schedules

I hope you all find something useful. And thank you to Allen White for hosting.