Drawing a Hyperbola in SSMS

<= Drawing a Parabola in SSMS

The last and most common shape of motion in the space is Hyperbola.

Small objects from outer space change their orbits insignificantly under heavy gravitation of bigger bodies.

Here is the formula I've used to draw hyperbolas:

Where:
X and Y - Hyperbola's coordinates;
A and B - imaginary central point;
L - slop.


Here is a classic hyperbola script for SQL Server:

DECLARE @MP VARCHAR(MAX)=''; DECLARE @l DECIMAL(8,4) = 1;    -- Slope DECLARE @bx DECIMAL(8,4) = 0;    -- X-Center point DECLARE @by DECIMAL(8,4) = 0;    -- Y-Center point DECLARE @s DECIMAL(8,4) = 10;    -- X-Span of Hyperbola DECLARE @Step DECIMAL(8,4) = 0.1;    -- Step DECLARE @k DECIMAL(8,4) = 0; DECLARE @cx DECIMAL(8,4) = -@s;    -- X-Current point DECLARE @x1 DECIMAL(19,9); DECLARE @y1 DECIMAL(19,9); DECLARE @x2 DECIMAL(19,9) = @cx + @bx; DECLARE @y2 DECIMAL(19,9) = @by + @k*@x2 + @l / (@x2 - @bx); WHILE @cx < @s SELECT     @cx += @Step,     @x1 = @x2, @y1 = @y2,     @x2 = @cx + @bx,     @y2 = CASE @cx WHEN 0 THEN @y1                 ELSE @by + @k*@x2 + @l / (@x2 - @bx) END,     @MP = @MP + CASE WHEN @x2 = @bx                   OR (@x1 <= @bx and @x2 >= @bx ) THEN '' ELSE '('         + CAST(@x1 as VARCHAR) +  ' '         + CAST(@y1 as VARCHAR) + ','         + CAST(@x2 as VARCHAR) +  ' '         + CAST(@y2 as VARCHAR) + '),' END ; SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

Here is the result of that script in SSMS:

By adjusting variables you can get variety of hyperbola shapes:

DECLARE @l DECIMAL(8,4) = -2;    -- Slope DECLARE @bx DECIMAL(8,4) = 12;    -- X-Center point DECLARE @by DECIMAL(8,4) = 3;    -- Y-Center point DECLARE @s DECIMAL(8,4) = 25;    -- X-Span of Hyperbola DECLARE @Step DECIMAL(8,4) = 0.1;    -- Step DECLARE @k DECIMAL(8,4) = 0.5;

Drawing a Parabola in SSMS

<= Drawing an Ellipse in SSMS

Drawing a Hyperbola in SSMS =>

Some asteroids are first and last time visitors in our Solar System.
They travel between stars in Milky Way galaxy and sometimes come so close to our Sun that completely change their orbit.
Usually their orbit is changed in shape of hyperbola, but sometimes they are going by parabola shaped orbit.

The formula I've used is not a conventional one, but it served my needs pretty well:

Where:
X and Y - parabola's coordinates;
A and B - coordinates of parabola's vertex;
L -  parabola's slop;
n - parabola's power;


So, it is a time will draw a Parabola in SQL Server management studio.

Here is a script to draw classical shaped parabola:

DECLARE @MP VARCHAR(MAX)=''; DECLARE @l DECIMAL(8,4) = 1;    -- Slope DECLARE @bx DECIMAL(8,4) = 0;    -- X-bottom point DECLARE @by DECIMAL(8,4) = 0;    -- Y-bottom point DECLARE @p DECIMAL(8,4) = 2;    -- Parabola's Power DECLARE @Step DECIMAL(8,4) = 0.01;    -- Step DECLARE @s DECIMAL(8,4) = 5;    -- X-Span of Parabola DECLARE @cx DECIMAL(8,4) = -@s;    -- X-Current point DECLARE @x1 DECIMAL(19,4); DECLARE @y1 DECIMAL(19,4); DECLARE @x2 DECIMAL(19,4) = @cx + @bx; DECLARE @y2 DECIMAL(19,4) = @by + @l * POWER(ABS(@x2 - @bx),@p); WHILE @cx < @s SELECT     @cx += @Step,     @x1 = @x2, @y1 = @y2,     @x2 = @cx + @bx,     @y2 = @by + @l * POWER(ABS(@x2 - @bx),@p),     @MP = @MP + '('         + CAST(@x1 as VARCHAR) +  ' '         + CAST(@y1 as VARCHAR) + ','         + CAST(@x2 as VARCHAR) +  ' '         + CAST(@y2 as VARCHAR) + '),'; SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

As the result I've got that beautiful picture:

By changing slope, parabola power and the coordinates of the bottom point you can get very different parabola shapes.

Here is one example of parameter set:

DECLARE @l DECIMAL(8,4) = -0.5;    -- Slope DECLARE @bx DECIMAL(8,4) = 7;    -- X-bottom point DECLARE @by DECIMAL(8,4) = 3;    -- Y-bottom point DECLARE @p DECIMAL(8,4) = 1.5;    -- Parabola's Power

In this case parabola's bottom point has coordinates (7,3), it has a negative slope and even its power not a whole number.

Try to reshape it by changing variables.

Drawing an Ellipse in SSMS

<= Building Sky Map in SSMS

Drawing a Parabola in SSMS =>

I was looking at orbits of planets, comets and asteroids and questioned myself if I can reproduce their tracks in SQL Server.

The first try is drawing Ellipse:

DECLARE @MP VARCHAR(MAX)=''; DECLARE @theta DECIMAL(8,4) = 0;-- angle that will be increased each loop DECLARE @h DECIMAL(8,4) = 0;    -- x coordinate of center DECLARE @k DECIMAL(8,4) = 0;    -- y coordinate of center DECLARE @step DECIMAL(8,4) = 2 * PI()/100;  -- amount to add to theta each time (degrees) DECLARE @a DECIMAL(8,4) = 10;    -- major axis DECLARE @b DECIMAL(8,4) = 5;    -- minor axis DECLARE @x1 DECIMAL(8,4), @y1 DECIMAL(8,4); DECLARE @x2 DECIMAL(8,4) = @h + @a * COS(@theta); DECLARE @y2 DECIMAL(8,4) = @k + @b * SIN(@theta); WHILE @theta <= 2 * PI() SELECT     @x1 = @x2, @y1 = @y2,     @x2 = @h + @a * COS(@theta),     @y2 = @k + @b * SIN(@theta),     @theta += @step,     @MP = @MP + '('         + CAST(@x1 as VARCHAR) +  ' '         + CAST(@y1 as VARCHAR) + ','         + CAST(@x2 as VARCHAR) +  ' '         + CAST(@y2 as VARCHAR) + '),'; SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

In this script you can change following parameters:
- Coordinates of the center of an ellipse - @h & @k;
- Lengths of Major and Minor axes' - @a & @b;
- Size of one step incremental angle - @step. That parameter also controls number of steps.

With a step size - 1/100 of a circle and axis' sizes 10 and 5 I've got that pretty Ellipse:

Catch 22 or creating SQL dependency circle.

That is a kind of a SQL joke. Part of the fun being a DBA.

First case of SQL dependency circle I will demonstrate on two tables interdependent on each other.

Here is a script to create these simple tables and build dependency relationships to each other:

USE TestDB; GO CREATE TABLE tbl_Catch_1(     ID INT IDENTITY(1,1) PRIMARY KEY,     Catch_2 INT ); GO CREATE TABLE tbl_Catch_2(     ID INT IDENTITY(1,1) PRIMARY KEY,     Catch_1 INT ); GO ALTER TABLE tbl_Catch_1 ADD CONSTRAINT FK_Catch_1 FOREIGN KEY (Catch_2) REFERENCES tbl_Catch_2(ID); GO ALTER TABLE tbl_Catch_2 ADD CONSTRAINT FK_Catch_2 FOREIGN KEY (Catch_1) REFERENCES tbl_Catch_1(ID); GO

Now, if you try to delete any of these tables you will get an error kind of:

Msg 3726, Level 16, State 1, Line 78 Could not drop object 'tbl_Catch_1' because it is referenced by a FOREIGN KEY constraint. Msg 3726, Level 16, State 1, Line 80 Could not drop object 'tbl_Catch_2' because it is referenced by a FOREIGN KEY constraint.

It is very simple to resolve that issue by dropping one of the relationships first:

ALTER TABLE tbl_Catch_1 DROP CONSTRAINT FK_Catch_1; GO DROP TABLE tbl_Catch_2; GO DROP TABLE tbl_Catch_1; GO

The second SQL interdependency example will be little bit more complicated:

Here I create an interdependency between table an a function:

USE TestDB; GO CREATE TABLE tbl_Catch_3(ID INT IDENTITY(1,1)); GO CREATE FUNCTION dbo.fn_Catch_3() RETURNS INT WITH SCHEMABINDING AS BEGIN   RETURN (SELECT MAX(ID) + 1 FROM dbo.tbl_Catch_3); END; GO ALTER TABLE tbl_Catch_3 ADD Next_ID as dbo.fn_Catch_3(); GO

Try to see if table and function are functioning correctly:

BEGIN     INSERT INTO tbl_Catch_3 DEFAULT VALUES;     SELECT * FROM tbl_Catch_3     WHERE ID = (SELECT MAX(ID) FROM tbl_Catch_3); END GO 5 GO

Everything looks fine:

Now, if we try to delete the table or the function we get following messages:

Msg 3729, Level 16, State 1, Line 117 Cannot DROP FUNCTION 'dbo.fn_Catch_3' because it is being referenced by object 'tbl_Catch_3'. Msg 3729, Level 16, State 1, Line 119 Cannot DROP TABLE 'tbl_Catch_3' because it is being referenced by object 'fn_Catch_3'.

The easiest way to resolve that issue is to go backwards and delete computed column first:

ALTER TABLE tbl_Catch_3 DROP COLUMN Next_ID; GO DROP FUNCTION dbo.fn_Catch_3; GO DROP TABLE tbl_Catch_1; GO

I'm pretty sure there are many other ways to have SQL dependency circle, but these two are only I have faced.

Collation on a single column

You specify collation when you install SQL Server.

What is "COLLATION"? - That is the way how SQL Server interprets ASCII symbols and character codes. It is applicably not only for "English", but for any supported Unicode languages.

Most of the time the default collation always works and nobody even look at it. All new databases will have the same collation and all CHAR/NCHAR/VARCHAR/NCHAR fields.

However, sometimes you have to pay attention to your collation.
There might be a case when you attach database from another server with different collation.
Sometimes your business rules dictate that values have to be case sensitive and should be sorted in certain order. Then you have to change your collation.

At first, how do you know what is default collation on your SQL Server?
Run following command:

SELECT SERVERPROPERTY(N'Collation');

My SQL Express returns "SQL_Latin1_General_CP1_CI_AS" - the regular default value.

If you want, you can change collation for any database.
Following script demonstrates different results of the same query with different database collation and two different ways to retrieve default collation information for a database:

USE [TestDB]; GO  SELECT collation_name FROM sys.databases WHERE Name = 'TestDB'; GO SELECT 'A','A' UNION  SELECT 'a','a' UNION SELECT 'B','B' UNION SELECT 'b','b'; GO  ALTER DATABASE [TestDB] COLLATE SQL_Latin1_General_CP1_CS_AS; GO SELECT DATABASEPROPERTYEX('TestDB', 'Collation') as collation_name; GO SELECT 'A','A' UNION  SELECT 'a','a' UNION SELECT 'B','B' UNION SELECT 'b','b'; GO

 When we have default case insensitive collation UNION statement collapses upper and lover case values, but when we switch database to case sensitive collation query returned all four rows.

Here is a demonstration how collation effects a table:

USE [TestDB]; GO  CREATE TABLE tbl_test_Collation(     A CHAR(1),     B CHAR(1) ); GO  INSERT INTO tbl_test_Collation(A, B)  SELECT 'A','A' UNION  SELECT 'a','a' UNION SELECT 'B','B' UNION SELECT 'b','b'; GO SELECT Name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('tbl_test_Collation'); GO

As you can see, both fields in the table have default collation for the database:

The beauty of SQL Server collation is in ability to set individual collation on every column:

ALTER TABLE tbl_test_Collation   ALTER COLUMN A CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS; GO ALTER TABLE tbl_test_Collation   ALTER COLUMN B CHAR(1) COLLATE Latin1_General_BIN; GO SELECT Name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('tbl_test_Collation');  GO

As a result we have two columns in a table with different collation:

Now will play around with that table. As you noticed, values in column "A" and "B" are identical:

SELECT * FROM tbl_test_Collation WHERE A = 'a'; GO  SELECT * FROM tbl_test_Collation WHERE B = 'a'; GO

We can see that first query, when we filtered by case insensitive column, returned two records and filter on case sensitive column returned only one record.

Ordering, using different collation, is even more interesting:

SELECT * FROM tbl_test_Collation ORDER BY A;  GO SELECT * FROM tbl_test_Collation ORDER BY B;  GO

First and the second queries have completely different order:

      

Ordering by case insensitive column sorts in alphabetical order. Ordering by "Binary sort" collation sorts by characters' ASCII codes.

Sometimes we need to JOIN, Link or concatenate character fields with different collation.
Here are couple of examples which will generate errors:

SELECT A + B FROM tbl_test_Collation; GOSELECT * FROM tbl_test_Collation WHERE A = B;GO

Here is the way to fix these queries:

SELECT A + B COLLATE SQL_Latin1_General_CP1_CI_AS   FROM tbl_test_Collation;GO SELECT * FROM tbl_test_Collation  WHERE A COLLATE Latin1_General_BIN = B;GO

However, be very careful joining columns, which do not have the same collation.
For example two queries below looks exactly the same, but return different sets of records:

SELECT * FROM tbl_test_Collation as a INNER JOIN tbl_test_Collation as b     ON a.A COLLATE Latin1_General_BIN = b.B; GOSELECT * FROM tbl_test_Collation as a INNER JOIN tbl_test_Collation as b     ON a.A = b.B COLLATE SQL_Latin1_General_CP1_CI_AS;GO

To prevent discrepancy between queries you can use "DATABASE_DEFAULT" option instead of specifying particular collation:

SELECT * FROM tbl_test_Collation as a INNER JOIN tbl_test_Collation as b     ON a.A COLLATE DATABASE_DEFAULT = b.B COLLATE DATABASE_DEFAULT;GO

I want to finish this post by the simple query to retrieve list of all available collations.
In SQL Server 2014 is almost 4K collations. To see only applicable to your language you can use following script

SELECT name, [description] FROM sys.fn_helpcollations() WHERE name like 'Greek%';

Usage of NOLOCK hint and READ UNCOMMITTED.

I've been hit again by wide usage of NOLOCK hint in a client's code.

To prove for the client and for myself necessity of using that hint I've made an experiment, which want to post here.

At first, will go to TestDB and create three tables: tbl_Test with test data, tbl_Counts - there will be inserted counts of records from tbl_Test table, tbl_Flag - communication table to transfer a signal between two SSMS windows.

As a result of that script table tbl_Test has to be populated by 10,000 "Empty" records.

-- # Script 1 - Window 1 - Preparation use TestDB GO -- Drop Test Table if exists IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = 'tbl_Test')        DROP TABLE tbl_Test; GO -- Drop Test Table if exists IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = 'tbl_Counts')        DROP TABLE tbl_Counts; GO -- Drop Test Table if exists IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = 'tbl_Flag')        DROP TABLE tbl_Flag; GO -- Create Test Table CREATE TABLE tbl_Test(        ID INT IDENTITY (1,1) CONSTRAINT PK_Test PRIMARY KEY,        TestText VARCHAR(100) NULL, ); GO CREATE TABLE tbl_Counts (Counted INT) GO CREATE TABLE tbl_Flag(ID INT IDENTITY(1,1)) GO -- Fill Test records INSERT INTO tbl_Test(TestText) SELECT TOP 10000 NULL FROM sys.messages; GO

After table tbl_Test has been populated I run the second script.
That script is waiting for a signal to collect the data and collects it until another signal.

Data collection is a simple counting of records from the tbl_Test table.
If the number of records won't match 10,000 it will be reported to tbl_Counts table.

-- # Script 2 - Window 1 - Collecting the data SET NOCOUNT ON GO DECLARE @i INT = 0; WHILE @i = 0  SELECT @i = COUNT(*) FROM tbl_Flag; WHILE (SELECT COUNT(*) FROM tbl_Flag) > 0 BEGIN        SELECT @i = COUNT(*) FROM tbl_Test (NOLOCK);        IF @i != 10000               INSERT INTO tbl_Counts(Counted) VALUES(@i); END GO SELECT Counted, 10000 - Counted as Diff FROM tbl_Counts; GO

While the second script is running I open another SSMS tab an start third script.
That script inserts one record into tbl_Flag table to activate data collection and then just does an update of tbl_Test. After update is finished it sends signal back to stop collecting the data.

-- # Script 3 - Window 2 - Changing test data INSERT INTO tbl_Flag DEFAULT VALUES; GO UPDATE tbl_FF_Test SET TestText = REPLICATE('A',100); GO TRUNCATE TABLE tbl_Flag; GO

After that script has finished I switched to the first tab and got my expected results:

That shows that during "UPDATE" operation my second query captured some situations when number of records is not equal 10,000 records.

That is easy proof how NOLOCK hint can produce incorrect results.

Microsoft recommends to use "READ UNCOMMITTED" transaction isolation level instead of NOLOCK.
Look how how it performs.
At first, run script #1 again to recreate our tables.

Then instead of script #2 run it's modification: #4

-- # Script 4 - Window 1 - Collecting the data SET NOCOUNT ON GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO DECLARE @i INT = 0; WHILE @i = 0  SELECT @i = COUNT(*) FROM tbl_Flag; WHILE (SELECT COUNT(*) FROM tbl_Flag) > 0 BEGIN        SELECT @i = COUNT(*) FROM tbl_Test;        IF @i != 10000               INSERT INTO tbl_Counts(Counted) VALUES(@i); END GO SELECT Counted, 10000 - Counted as Diff FROM tbl_Counts; GO

Then in another SSMS tab run script #3 and go back for the results. They are the same.
There are several captured records which do not match count 10,000.
That means "READ UNCOMMITTED" works in the same manner as NOLOCK hint and may generate misleading data.

Now will talk about the difference.
You can not use "READ UNCOMMITTED" within a function, but you can use NOLOCK hint.

CREATE FUNCTION dbo.fn_Test() RETURNS INT AS BEGIN   RETURN (SELECT COUNT(*) FROM tbl_Test (NOLOCK)); END

You have to apply "NOLOCK" hint individually to every table you want.
"READ UNCOMMITTED" will be applied to the whole statement until isolation level will be changed or until closing of connection.

Now is the most important question: Do you have to use "NOLOCK" at all? Is it "silver bullet" or is it evil?

SQL developers often use that hint to avoid unnecessary locking  of tables/pages/records during heavy transactional activity to avoid possible deadlocks.
However, as you could see from my examples you can possibly get inaccurate data as a result.
If the result is not important to you then it is OK, but if that result is used in following calculations or important financial reporting then I'd say it is not OK.

I'll try to define some ONLY cases when it would be OK to use "NOLOCK" hint:
1. For development/troubleshooting purposes, when results are not very important;
2. In extremely big and heavy transactional systems with thousands of requests per second SOME user reporting queries can be hinted with "NOLOCK". For instance: it is not big deal if Amazon store you get a duplicate or missing item in a suggestion items line.
3. For a query, which does clustered index seek or scan for historical data, which can't be changed or deleted during the request.


Generally speaking, if you have "NOLOCK" in your code means that you have big problem with performance and you are at risk of having even bigger problem of having incorrect data.