Using SQL Server 2008 Semi-Structured features for Performance Tuning and Application Design (1)

Before I talk with the examples of semi-structured property management system, I like to define the semi-structured data: ·         Group: A set of ordered, open, heterogeneous properties. Every group has a name and every property in the group. ·         Object: An instance of a group.  ·         Property: A scalar property, a collection property, or a…

1

Sparse Column Storage

In SQL Server 2008, we implemented a set of features, including Sparse Column, Column Set + Wide Table, Filtered Index and HierarchyID, to support Semi-Structure data management.   Today, I will talk about how Sparse Column will save disk space.  When a table has no sparse column defined, and no data compression defined, the null value…

1

SQL Server 2008 (Katmai)’s new collations

  In SQL Server 2008 release,  a number of new collations were added.  You can get the list of 79 new collations (only _CI_AS was listed) by using         select * from fn_helpcollations() where name like ‘%_100_ci_as’ These new collations are aligned with the linguistic sorting of Windows Server 2008 (they use very similar algorithm). …

1

Conversion from string to datetime2, time, date is timezone un-awared

In SQL Server 2008, An instance of DateTimeOffset type can represent: 1)     A local time  + a time zone offset 2)     A UTC time + a time zone offset 3)     A local time and a UTC time. When converting such an instance to other date and time types, such as datetime type, we will use the…

0

Why should we bother about Surrogate characer?

Today, someone ask following questions:  1. We know that the Surrogate Characters have two bytes when counting, so when we design this character in DB table, we should set double size of your original type, shouldn’t we? 2. When comparing string, if we can’t use Binary collating sequence, then we will got different result from…

1

The resolution of SYSTEMDATE function

In SQL Server 2008, the datetime2, time, and datetimeoffset types can have a optional fractional second part. This decision have several benefit: If user does not care about the fractional second, they can use datetime2/time/datetimeoffset(0), which can save storage size, also and have a better display result (converting to string will not have factional second as well)….

0

Colon(:) and dot (.) in factional second is not the same

Colon and Dot are two symbol to separate the second and factional second in a given time string.  If we create following queries: select CAST(‘1999-12-21 01:01:01.12’ as datetime) ———————– 1999-12-21 01:01:01.120   select CAST(‘1999-12-21 01:01:01:12’ as datetime) ———————– 1999-12-21 01:01:01.013 As you can see, we have different result for colon and dot separtated string.  In…

0

Idea about implementing a time internal type in SQL Serve 2008

    In SQL Server 2008, we implement several new date and time types which follow the ISO SQL standard.  The one ISO SQL type which is missing in SQL Server 2008 is the time internal type. Today, I will present some idea about implementing a time internal types in SQL Server 2008.     A time interval is…

0

My first blog

Hello, guys    Welcome to my blog.  I am a tester in SQL Server Engine Team.  I tested the SQL Server ‘s collation, date and times types, functions.  In addition, I involved developping Sparse Column feature during SQL Server Katmai Release.  I will move my testing focus to XML.  I like to share some idea/throught to…

0