July 2010 Blog Posts
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...
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...
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...
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...