Query on Float DataType may return inconsistent result

Let us try following T-SQL script on SQL Server: declare @a float =300000000000000000000000000000000000select @a + 50 -@aselect @a -@a +50go Guess what is the result?  the first is 0. and the second result  is 50. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. When you…

20

Tertiary Collation and the performance impact on order clause

Today, One customer asked about collation SQL_Latin1_General_CP1_CI_AI with non unique nonclustered index we are getting a sort on the query plan.  The sample script is: CREATE TABLE TableWithASColumn(ID INT PRIMARY KEY, CharData VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS) CREATE NONCLUSTERED INDEX IX_CharData ON TableWithASColumn(CharData) CREATE TABLE TableWithAIColumn(ID INT PRIMARY KEY,CharData VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI) CREATE NONCLUSTERED INDEX IX_CharData ON…

0

How to make Like Case Sensitive?

Today, I got a question from customer: I have one column (Type: nVarchar) in a table which has data something like this: a1 aa aa2 AB I want to show all the rows which have all the lower case letters in the above mentioned column. The following query works: select * from test WHERE LTRIM(RTRIM(a))…

0

Unicode References

In this article, I recommend several Unicode articles/websites for reference. Note, the list is not yet completed,  I will add more entries and make better categorization My blog is a good site for collation issues in SQL Server. Sort it all out. Michael Kaplan’s random stuff of dubious value is a great source for learning…

1

Recommended presentation "The future of testing"

  I like to share a talk by James Whittaker, Software Architecture at Microsoft. The talk name is  “The Future of Testing”, which was presented at  “2008 Annual Google Test Automation Conference”, the link is  https://www.youtube.com/watch?v=Pug_5Tl2UxQ.  Here is some my thought after watching the video:    * Fix bumpers in car v.s. It is feature in…

0

Testing DBMS system: what should be the language?

   Yesterday, I had chat with a colleague about  testing database system.  He raised a ery interesting question: what should be your language?     Any test can be write in the ways of: Test(X), while X is the “thing” we want to test or verify. In additional, a test need 1) SUT: system under test, …

0

SQL Server and UTF-8 Encoding (1) -True or False

Today, I will start my series of articles about SQL Server and Unicode UTF-8 Encoding. In many times, I found when people ask me about UTF-8, they actually don’t understand UTF-8.  So today’s talk will be quite short. I just clarify some misunderstand.1.    SQL Server doesn’t support Unicode, do you mean UTF-8?     Sometime, people…

74

What I was doing these days?

In recent days,  I am mainly reading test books since I am a Tester, and I need to know more about testing.  The good news for people who are interesting in collation topic is that  I will have a SQL Server collation select tool available soon. I hope I can start to write UTF-8 related…

1

SQL Server’s Binary Collations

   Today, I will discuss SQL Server’s Binary collations.  In SQL Server, we have two kinds of binary collations: BIN collation, which has collation name ending with _BIN and BIN2 collation, which has collation name ending with _BIN2.  In SQL Server, a string value (either varchar or nvarchar value) is encoded and stored as a…

2