Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links




Post Categories

July 2010 Blog Posts

Joe Celko's Puzzles and Answers - The Restaurant seat assignment Problem

This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant. I've come up with a solution that need only 125 bytes of storage. All other solutions covered in Mr Celko's book has at least 1,000 bytes of storage. Here is my solution, complete with all procedures to assign and release seats, together with views to display current status of each and one seat. -- Setup sample data CREATE TABLE    dbo.Restaurant                 (                     Seats BINARY(125) NOT NULL                 ) -- Initialize an empty restaurant INSERT  dbo.Restaurant          (             Seats         ) SELECT  0x GO -- Create procedure for handling seat assignment CREATE...

posted @ Saturday, July 31, 2010 1:20 AM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 Celko Puzzle ]

More on XML and encoding

Today, let's examine encoding with SQL Server and XML datatype. DECLARE @Inf XML SET     @Inf =  '<?xml version="1.0" encoding="utf-16"?>                  <root>                     <names>                          <name>test</name>                      </names>                      <names>                          <name>test1</name>                      </names>                  </root>                 ' SELECT  x.value('name[1]', 'VARCHAR(10)') AS Name FROM    @Inf.nodes('/root/names') AS t(x) If you try to run the code above, you will get an error message like this Msg 9402, Level 16, State 1, Line 3 XML parsing: line 1, character 39, unable to switch the encoding Why is that? If you change the encoding to UTF-8, the code works. The solution is to know that UTF-16 works like UNICODE, and how do we denote UNICODE strings in SQL Server? Yes, by prefixing the string with N. So this code works with UTF-16 encoding...

posted @ Tuesday, July 06, 2010 9:40 AM | Feedback (2) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

String concatenation and entitization

This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog. Consider this sample data DECLARE @Sample TABLE         (             ID INT,             Data VARCHAR(100)         )   INSERT  @Sample VALUES  (1, 'Peso & Performance SQL'),         (1, 'MVP'),         (2, 'Need help <? /> -- '),         (2, 'With XML string concatenation ?') The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out. So here is the final query. SELECT      i.ID,             STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '') FROM        (                 SELECT      ID                 FROM        @Sample                 GROUP...

posted @ Sunday, July 04, 2010 11:59 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Proper Relational Division With Sets

I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data. With this information at my hand, I started to investigate what really is needed to get this Relational Division to work properly with two sets; Dividend and Divisor. Some of you know me well, and know I am not satisfied with just solving the problem. There have to be some tweaks, and I did that too with this solution. Not only is it only touching the Dividend table once and...

posted @ Friday, July 02, 2010 12:48 AM | Feedback (36) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Powered by:
Powered By Subtext Powered By ASP.NET