Happy Spatial Christmas!

Building Sky Map in SSMS =>

Christmas is pretty good time to exercise my newly acquired skills using Spatial Data.

For that event I decided to build a Christmas Tree in SSMS.



Here is the code to draw the Tree:

DECLARE @g TABLE (g GEOMETRY, ID INT IDENTITY(1,1)); -- Adjust Color INSERT INTO @g(g) SELECT TOP 29 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages; -- Build Christmas Tree INSERT INTO @g(g) VALUES (CAST('POLYGON((0 0,900 0,450 400, 0 0 ))' as geometry).STUnion(CAST('POLYGON((80 330,820 330,450 640,80 330 ))' as geometry)).STUnion(CAST('POLYGON((210 590,690 590,450 800, 210 590 ))' as geometry))); -- Adjust Color INSERT INTO @g(g) SELECT TOP 294 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages; -- Build a Star INSERT INTO @g(g) VALUES (CAST('POLYGON ((450 910, 465.716 861.631, 516.574 861.631, 475.429 831.738, 491.145 783.369, 450 813.262, 408.855 783.369, 424.571 831.738, 383.426 861.631, 434.284 861.631, 450 910))' as geometry)); -- Build Colored Balls INSERT INTO @g(g) SELECT TOP 2 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages; INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (80 290, 110 320, 140 290, 110 260, 80 290))' as geometry)); INSERT INTO @g(g) SELECT TOP 2 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages; INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (760 290, 790 320, 820 290, 790 260, 760 290))' as geometry)); INSERT INTO @g(g) SELECT TOP 3 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages; INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (210 550, 240 580, 270 550, 240 520, 210 550))' as geometry)); INSERT INTO @g(g) SELECT TOP 46 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages; INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (630 550, 660 580, 690 550, 660 520, 630 550))' as geometry)); SELECT g FROM @g ORDER BY ID; GO

In this exercise I've used following Spatial Data  and a method:
POLYGON - 2-Dimensional surface area ("Tree", "Star", and "color adjustment" area)
CIRCULARSTRING - Collection of circular arc segments (Ball circles)
CURVEPOLYGON - 2-Dimensional surface area defined by a ring ("Colored Balls")
.STUnion - Method for unionizing two geometry instances.

Drawing has been done in following steps:
1. Adjusted a color for the Tree. I draw 29 dummy triangles to get the 30st color in SSMS palette, which is kind of Green.
2. Built three POLYGON - triangles and unionize them.
3. Did another color adjustment to get goldish color for the Star.
4. Built a POLYGON in a shape of star.
5. Built four colored circles with color adjustments before each of them.
6. Run the script.
7. Switch to "Spatial Result" tab in SSMS.

That was easy. Isn't it?

Learning Every Day: Case Sensitive Replace

During some T-SQL programming I needed to add spaces before some characters within a string.
The problem was that characters were in upper and lover case and I had to preserve their cases.

Idea for the solution was found in "stackoverflow.com".

Here is how it can be done:

SELECT REPLACE('1C2c3' COLLATE Latin1_General_CS_AS, 'C', ' C');
SELECT REPLACE('4C5c6' COLLATE Latin1_General_CS_AS, 'c', ' c');

And here is the result:

--------
1 C2c3
--------
4C5 c6

The trick is in using "Latin1_General_CS_AS" case sensitive collation to do the replace.

You can also use that method for a search. For example two queries below will return different results:

SELECT CHARINDEX('C', '1C2c3' COLLATE Latin1_General_CS_AS);
SELECT CHARINDEX('c', '1C2c3' COLLATE Latin1_General_CS_AS);

New SQL Server 2012 Logical Functions

Want to present couple of new T-SQL Logical Functions implemented in SQL Server 2012.

The first function is "CHOOSE". It simply chooses one value from a provided list.
Here are couple of examples:
SELECT CHOOSE(3, 'ab', 'cd', 'ef', 'gh');
SELECT CHOOSE(5, 0, 1, 2, 3, '4');

First query will return third value "ef" and the second query will return number "4".

That means you can use any data type in the list as far as all items are of the same type or types can be converted by SQL Server.

It is pretty easy. What about the limits?
Books Online do not mention any limits. The biggest number of items I was be able to test with was 4426. That depends also on size of items and on the first parameter, which value has to be chosen.
In some cases I've got following error:
Msg 8631, Level 17, State 1, Server Bla-Bla-Bla, Line 1
Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

Here are automated testing results for SQL Express 2014: In Research batch script I was be able to execute "CHOOSE" function with 4981 items and item size upper than 60 bytes each (probably goes higher, but I've stopped the test).

However, in manual mode it shows even better results: Up to 4985 Items with item size 64Kb !!!
I'd consider it as a pretty high limit with query size as big as  300 Mb.

---

The second function is "IIF" !!!
It was more than 20 years available in MSAccess version of SQL and I really missed it.

For those who do not know what it is: It is just a shorter version of "CASE" clause, which chooses between only two choices.

IIF has three parameters: First parameter is a logical statement, second parameter is a value, which will be returned when the first parameter is "True". In case the first parameter is "False", third parameter's valuse will be returned.

"IIF" looks more convenient than "CASE", but it is about 1.5% slower on SQL 2014 and about 2% slower on SQL 2012.
Just compare two queries below. They produce the same results by marking Even and Odd numbers:

SELECT TOP 10 message_id,
    CASE message_id % 2 WHEN 0 THEN 'Even' ELSE 'Odd' END
FROM sys.messages;

SELECT TOP 10 message_id, 
    IIF(message_id % 2=0,'Even','Odd')
FROM sys.messages;

Unfortunately, do not think a lot of people will be using it because of it slowness.

Dedicated Admin Connection (DAC) in SQL Express

I've tried to do some exercises on my local VM with SQL Server Express edition, required Dedicated Admin Connection (DAC).

Faced the problem that I can not actually do it. After a short research found very easy solution.

1. Run SQL Server Configuration Manager. You can use an icon from windows menu or if it is not available you might use the following (or modified for your system/version) command:
C:\Windows\SysWOW64\mmc.exe /32 C:\Windows\SysWOW64\SQLServerManager12.msc

2. In Configuration Manager locate your SQL Server instance and choose properties by right click on it.



3. In Properties, choose "Startup Parameters" tab:


4. In the box specify a trace flag: "-T7806" and press Add.

5. Then hit OK and restart SQL Server Service.

Another way to do the same thing is would be editing Windows registry by RegEdit.exe
You just have to add  new key "SQLArgX" with "-T7806" in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\Parameters

That should look something like this:



 That also require SQL Server instance restart.

 
Also, in order to be able to establish DAC you have to enable it within your SQL Server configuration:

---

sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO 
sp_configure 'show advanced options', 0; 
GO
RECONFIGURE
GO

---

The last step of DAC is connection itself. You have to specify word "ADMIN:" before the server name:



You can have ONLY ONE Dedicated Admin Connection per server at any given moment.
That means: do not forget to close your connection after the exercise and leave ability to connect to your server open at any moment in the future. Learn to do the same in production too.