Author Archives: diligentdba

My name is Mala, I am a DBA on SQL Server for the past 15 years..and an active member of the PASS community. Aside from my profession i love to read, write, paint,travel and play practical jokes.

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

 

 

 

Keep the Quorum…

As a follow up to the earlier post I had on harassment issues at PASS Summit – writing this to clarify more on the same. The community seems divided on if or not issues should be reported, if or not those who do not report are guilty, if or not those who say ‘I  will report’ are wrong, on and on. There are no easy answers to any of this, and a healthy debate is a good thing, for sure. But what happens in most cases is that people with strong stances either way tend to back off, thinking they are ‘wrong’ or ‘don’t belong’ and it leads to a sort of a stalemate, allowing such things to go on as is. Below are some of my experiences and humble opinions on all of this.
1 Should all issues be reported?
The answer is umm..no. There are lot of issues  with making a blanket statement like that.Any of us who has been a victim – not necessarily at PASS but anywhere knows the issues around making a shaming incident public.You are already suffering shame, and need privacy. And then you have people expressing how they feel about it, you have people taking stances that it did not exactly happen like you said, you have gossip, cliques, on and on. It is not a pleasant experience and the victim has a right to opt out of it if he/she chooses to. In this case it can also be that victim really does not trust the body of people the issue is being reported to. In a close community that kind of opinion is not uncommon.

2 Is  it then necessary even to have a structure to report issues?
Yes, it absolutely is. And again, that is the problem with polarized stances. If you don’t report an issue, chances are pretty high that person is going to try it again – on someone else, at someplace else. All criminals get to be that way because they got away with something,somewhere. And again, you may be from a country or a culture where such incidents are really rare. Where if someone engages in butt pinching the woman in question will just turn around and put him in place before he could bat an eyelid. I have known women like that from Germany and Sweden, and  even from my own home country, India. There are women who will turn around and make a sarcastic joke, or pinch him back. The problem is that not all people are that way, nor do all of us have presence of mind to act in that way. Some of us feel incredibly violated, shamed. And not all incidents have to  be as mild as butt pinching. It can be a lot worse. A lot of us come from cultures  where people will stand by and watch abuse happen, even enjoy it and egg the perpetrator on. A lot of us have been saved by many a unknown hero who saw it was wrong.
A structure to report the incident to, to ban the person from conferences, report him to police or similar can and absolutely should  exist. Men or women who want to be guardians or make themselves available as guardians are AWESOME PEOPLE, they should not be shamed or let down for what they offer to do.

3 What if I see something that looks like harassment and want to report it?

Check with the victim as  much as possible. And if that is not possible I hate to say it but I would probably report it. But asking the victim helps – and understand what he/she would prefer to do.

4 What if someone confides in me and asks me not to report it?

I would not report it. Respecting someone’s need for privacy is important. I would definitely ask if he/she did anything to let the perpetrator know he was behaving badly. And if it is ok to report it anonymously. I would not in any condition disclose the victim’s name to anyone else or shame him/her for not reporting.

Let us keep the awareness that taking unfair advantage of anyone is wrong, no matter how your own culture chooses to call it. The fine print on that stops if I say I have been taken unfair advantage of. It is wrong to say things like ‘in my culture that would have been a non issue’, or ‘you are too sensitive’, or that ‘You should have reported it’, or ‘You should not have reported it’. All of those thing are opinions , and opinions are necessary to make our process better. But we do have to accept that abuse happens, and work towards bettering trust and related issues – not telling victims how to feel and how to act all the time.

We have to keep the quorum that abuse is wrong, and the victim gets the last word on what that is. We only get to define how to fix things and make it better.

Thank You.

Feeling safe…

Members of PASS recently received an email from president Thomas LaRock reminding us of the importance of proper etiquette and behavior at the summit. The email was prompted by a few unfortunate incidents of harassment, atleast one of which was highlighted by board member Wendy Pastrick as a personal experience. These posts were followed by a lot of discussion on twitter on how much to regulate, what to regulate, do you report or do you not, on and on. There are no simple answers to any of those questions.

The summit is a conference with as much as 30-40 percent of people from overseas – it is enormously hard to enforce very specific standards for behavior. Not to mention that USA is a country with the highest number of freedoms – so there will not be any norm that is totally acceptable to every person. I got a few emails/requests from friends overseas – particularly in Asia and Asia-Pacific region (for the record I was their Regional Mentor for many years)  asking for clarity on what constitutes appropriate touch, among other things. A lot of them were just very confused in this regard. I will choose to share what has   worked for me personally –
1 Is it ok to hug? If yes, whom?
Generally speaking, yes. I happen to be rather good with body language and I can usually tell when someone is open to it. I am good with my own too  and  have said more than once that I don’t feel particularly huggy that day. Intuition and body language are good things to keep handy – see if the person looks friendly first, that simple.Even after a decade of conferences there are people I’d rather not talk to – they are not friendly enough for me, I take brush-offs badly even if they don’t intend it, I have enough people I know,and I don’t think anyone is that important to force a conversation.  Most trouble ends there.For a hug, just ask. And if it is not your thing it is totally fine to not do it. A courtesy hug is usually one-armed with palm below neck  for a brief second or two. It is not a chest-to-chest embrace that lasts a long time. And if you don’t feel it is right for you, just put out your hand for a handshake, most people are absolutely fine with that.

2 What is an inappropriate touch?
Somebody touching you very intentionally, on parts of the body where it is not comfortable. Our bodies have built in wisdom – if someone is violating your boundaries you will know – you will feel flushed,embarassed and violated. If you are not body-wise – check with a good friend if how you feel is appropriate given the situation.

3 When do I report it – do I have to report it?
It is a difficult answer,  and like many things in the SQL world – It depends. Reporting has some advantages because the person  will not be able to continue the same behavior in public at the conference atleast.Also to remember that you can request anonymity – although chances are high that the perpetrator is going to know it was you. But there are personal cons to this too – depending on how you look at it. It is embarrassing to tell someone you got touched. It is just the way we are, and it is embarrassing for anyone,  male or female. A lot of us can deal with it in our own way – I know of one girlfriend who slapped the man’s rear with a lot of force in return(it was effective!), some use odd humor to make the point, some have a frank talk with whoever is doing it. If you are a dark skinned person like me and  if it is known you complained it is highly unlikely to make you any more popular, even assuming you are popular. And if you are a foreigner you really don’t want to cause any  waves of that nature before leaving. It is all understandable. I would recommend though, to deal with it the best you can and not ignore it – as ignoring it is usually an invitation to the perpetrator that it is encouraged.For those who believe complaining is the route , there are enough valiant men in the community who can shoulder your cause – just approach any of them.

3 People seem to behave in all sorts of ways – sitting on each other’s laps, petting hair, kissing cheeks….how do I know what is the actual norm here?
This was  actually a real question posed to me by a friend from overseas this morning – it was accompanied by some pictures of individuals at the summit. The answer to that is just that it is impossible to regulate how groups of people who are in consent with each other behave. If it is personally embarrassing, step away. I have done that multiple times. Where I am from, sitting on someone’s lap is reserved for romantic relationships and almost never done in public. I have no judgements against those who do it but it is too personally embarrassing for me and not something I can think my way out of. In short, ignore them and don’t base any of your own behavior on other people’s that you find difficult to accept as natural.

4 I am a man..is it my job to come to the rescue of any woman if I see her in trouble?
It is old fashioned for a man to jump to a woman’s rescue  and many men simply cannot resist it. But physically lashing out against the perpetrator can have many consequences including landing you in jail, if you are not careful. If someone is asking for help, go to their rescue and hold the person via conversation or other means until help arrives. Usually there may be more than one man willing to assist with this if it happens at a community event or party. And no, it is not anyone’s job to protect anyone else, in theory. But it is everyone’s job to watch out and take care of each other.

There are a few more tips I have used  that have made my  summit experience positive :

1 Always attend parties with someone you know – preferably  at least one person who will not be totally drunk and can be relied upon to return with you.
2 Be vigilant and use intuition whenever you are in  physical contact with anyone.
3 When in doubt – ask. I have had many experiences at the summit but one that will always be clear and absolute about is that there are a LOT of kind, helpful people. So if complaining is too hard atleast talk to someone about your bad experience. Ask that they not complain if that is not what you want. (Some may not agree to this but there are many who will).
4 Use tips provided by  John Morehouse on how to navigate crowds.
5 The summit is a  very safe place and a great event to attend, don’t let this discussion lead you into thinking otherwise.

Happy Thanksgiving to all!

 

 

 

Dedicated Admin Connection (DAC)

What is DAC?
DAC is short for Dedicated Administrator connection to SQL Server – it is used for troubleshoooting by those who are SAs in for exceptional situations where a regular login will not meet the needs.

When is DAC typically needed?
In situations that require serious troubleshooting by an administrator.

How do I connect to SQL Server using DAC?
I decided to figure this out the hard way instead of googling for solutions. I only knew that i had to use admin:mylogin on ssms (I picked the ssms option over SQLCMD which is also another way to try) and decided to try it. I clicked on object explorer, prefixed my login with ‘Admin:’ and connected. After all, all connections go via Object Explorer right? No…I was faced with error as below –

dac6

I was using the wrong method to connect – the right way is to use ‘Database Engine’ query icon, the one to the right of ‘New Query’ on SSMS toolbar. I tried again using that method (it gives you the same login window as object explorer did). Below was the next error.

dac5
Now I realized that I had RDP-ed into the machine and did not have remote admin connections enabled. So the next step  was enabling those connections, as below. This configuration is  apparently needed on clustered servers also – and may not be needed if you are on a local machine.

EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE
GO

Now I was done and connected fine.

What can I do with DAC?
I was able to do a sp_who2, kill a runaway process, and run some dmv queries. I was not able to do a backup/restore.  This link from MSDN has a list of diagnostic and troubleshooting queries that can be run (various DBCC commands and querying DMVs). It is important to remember that it not the best option to run regular maintenence – only DBCC commands that relate to troubleshooting specific and rare issues.

What is the database I connect to with DAC?
It is the default database associated with the login. You can access any database after connecting with the use statement. To connect directly to the master you can use the sqlcmd line sqlcmd –A –d master (it assumes your windows login has access to master as SA).

What are some other miscellaneous facts to know regarding DAC?
It has limited resources, so should always be used for lightweight operations.
It cannot make use of parellelism.
The port number that DAC listens on is written to the error log during start up and is 1434 by default.
You can have only one DAC connection open at a time.(Even if the
other admin is a different person).

There is a great post by my friend and SQL Server MVP Kendra Little’s blog on the same subject – which  is also a worthy read. She also gives us an interesting DMV based query to find out if there are other DAC users if you are not allowed to get in.

Knowing about DAC might save your bacon in unexpected situations.I consider enabling it to be added to my list of standards for configuring a server.

TSQL Tuesday #72 – Data Modeling gone wrong

SqlTuesday

My first TSQL Tuesday post on the new blog is an invite by my friend and sql sister Mickey Stuewe (b|t). It is regarding data modeling practices that are well avoided and how problems rising as a result of bad data models can be fixed.

I have several experiences to recount in this regard as my first ever job was with doing data modeling and design. I will pick one of them for this post. Several years ago, I was working for a startup company as a junior DBA. DBAs also had their say in how data models were designed, although we didn’t always get the final word on it. Since it was a new company working off its own software we had some freedom to design things as we wanted to. The issue we had on had was to do with some code tables. Or rather, a code table. We had tables for products, customers and invoices. Now, we had an issue with some other aspects of the business – we needed a few lookup tables. We had a product type, a customer type and an invoice type. In addition to that, we needed payment type, address type and a whole of other ‘types’. The structure of these were more or less similar – a code and a description, in most cases. In a truly 3rd normal design – each of these would have been their own table with a foreign key relationship into the table it was used in. Small tables, no doubt, but specifically intended to store the lookup data in question.The senior DBA to whom I was reporting to had an opinion that a lot of small tables were adding up to the schema getting large and therefore difficult to manage, and that the queries would get too ‘deep’ over time. So he made a call that we would have a generic ‘code table’. A simple denormalized table with three fields – a code, a code type, and a code description. All of our codes – product type, customer type, invoice type, address type – everything would go in that one table, with the code type telling us what it was, and a code description giving some additional information.Programmers were thrilled with the idea, since it meant they had only one table to work with for most of their queries. Initially, this code table only contained about a 100 or so records and seemed like a workable idea. But the business started to grow, and along with it the table too. We started running into issues as below:

1 Almost all the queries in the application were referring to this one table – leading to it getting hit too many times.
2 The queries got increasingly cryptic when they tried to refer to one specific range of records – for example, an address type was indicated by a code A, and their query would say ‘code_type = ‘A”..another query would have code type = ‘I’ and so on..when the programmer left nobody really knew what these letters meant. And yes, there was no third table to decode the meaning.
3 The business wanted to manage the data, and there was no easy way to give them an interface. The data was a jumble of many different things and could not be managed with one screen. The programming effort needed to give them multiple screens was huge.
4 Some of the ‘codes’ started needing additional attributes – phone number type, for example, was initially just cell phone or residential.Then, some customers started asking for a second residential line, so you had to say residential 1 or 2, or cell phone 1 or 2.
5 There were no foreign key relationships defined – so the dependency was on code to enforce the relationship. Code had bugs, and that resulted in bad data getting in very often.

In short, it got so very complicated that the CIO had to approve a project to normalize the whole thing. The rewrite involved creating several smaller tables, putting in the right foreign keys and relationships, and creating interfaces for the business to manage the data. The big lesson that came out of it was that the size of the schema or fewer tables do not really mean a simpler/easier to manager design, in fact it can be the exact opposite.

Good database design is the backbone of a well designed and well performing  application – always try to learn what goes into it. A great article by SQL Server MVP Louis Davidson describes several issues like this and is worth reading.

From #23 to #403 – 8 years of SQL Saturday

“Many of us are more capable than some of us, but none of us is as capable as all of us.” Tom Wilson

SQL Saturday 403 marks 8 contiguous years of SQL Saturdays in Louisville – which I have had the pleasure of running. I have learnt many, many lessons during these 8 years – lessons on how to run an event on a budget and unpredictable funding, logistics around finding restaurants/caterers, what audience looks for,  what vendors look for, when is the right time and season for an event, who are the right speakers to pick, on and on. But the most important lesson among them all is one of team spirit. Good teams are self managing, where members enjoy what they do,thrive in each other’s company and do it for the love over anything else. I am fortunate to have been blessed with such a team to run our SQL Saturday.

A SQL Saturday team needs to have many skills – we need people who are good at marketing and selling the event to vendors and attendees, we need people who can order food/suggest restaurants/caterers etc, we need people who can do registration and structured work, and we need those invaluable hands who can do just about anything as needed. My team has been with me for 7 years – in the course of which we have lost and gained several people. I lost the person who did our marketing last year, which was one reason why our funding as well as attendance suffered. I was looking for someone with this talent to come on board – when I heard of John Morehouse moving to our town. I had heard of John’s passion for community and the great show they put up at Omaha sql Saturday and invited him onto our team immediately. Along with John we also had Chris Yates – who has been a twitter friend and community enthusiast for a while. Between John and Chris our marketing rocked the attendance and funding like nothing else before.  Some of the highlights of our event this year are as below –

1 We had 254 who registered – about 220 showed up. The registration is the highest to date.
2 Our funding was also highest to date and possibly the highest one has for events of our size.
3 Our signage as always was done very professionally by long time volunteer Deana Ritter. The flutter sign in  particular has become a cheerful landmark of our yearly events and one that is unfailingly appreciated by many people. Deana and her assistant volunteer Bill Murray get on the streets as early as 5 30 AM to put out signs near the highway for attendees coming in to the event.
4 Our sign ins and registration was ably handled by long time volunteer Karen Schuler. Karen is a cheerful presence for every attendee who walks in the door (directing those without speedpass to ‘table of shame’ :)). Speedpass was hugely successful this year with only around 25 people needing printouts at the venue.
5 Our lunch was catered by Mark’s Feed Store – it was hot bbq lunch and greatly appreciated by attendees. Volunteers James King and Bill Murray worked very hard to get arrangements in place to ensure smooth serving, no spillage or damage to carpet in the venue, as well as a cost effective arrangement to suit our budget.
6 All our swag was delivered to long time volunteer Dave Ingram, who carefully brought in every single package without a murmur (and there were many!!).
7 Our snacks, sodas, ice etc were ably taken care of by speaker cum volunteer Kenney Snell.
8 Our event cameraman was TJ Crivits – who has  captured the event in many memorable shots. Personally I see myself going through these when am old and still thinking of what fun it was doing all this. TJ also did a great job with getting the lunch leftovers carted away efficiently to a local soup kitchen.
9 Last but not the least is our volunteer cum speaker cum chapter lead Dave Fackler – whose many mailings helped immensely with publicity to the event. Dave also manned the table for PASS at the event.

During the 8 years of doing SQL Saturdays – we have had great events, good events and events which are kinda okay. We’ve had food deliveries that were missed, key volunteers who could not make it at the very last minute because of personal or work issues, venues that threatened to cancel on us because of weather (one day before, and yes that was our last and will be our last winter event, ever!) , vendors who backed out on sending funds..on and  on. This event was one that did not have any such issue , and was a complete, total success. I am proud and happy to close it out and look forward to event #9, and then soon, a whole decade of SQL Saturdays!!

Links related to our event:
1 Why we speak and volunteer for SQL Saturdays – by Dave Fackler.
2 SQL Sat 403 – Recap by Chris Yates
and one of the best geeky examples of using Powershell to tweet event info –
3 SQL Saturday Speaker Marketing with Powershell – by John Morehouse