Request a topic or
contact an Arke consultant
404-812-3123
December 2009

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments

Archive

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2024

SQL Server DateTime

SQL Server DateTime get 3.3ms resolution at best, and an officially unspecified amount worse than that at worst, depending on hardware and bugs on the target system.

In practice, it’s not enough resolution to allow adding a DateTime to make a row unique.  Uniqueness is best achieved with either a GUID or a rowversion (“TimeStamp”, which is confusingly named in that it is just a sequentially incrementing rowversion and so has nothing to do with the clock); special cases like “this state should only happen once” should generally be done with unique constraints that accurately model whatever contributes to the state involved.

DateTime resolution is likely to actually be more like 10ms or 15ms due to Windows NT clock (See 15ms comment in http://v8.googlecode.com/svn/trunk/src/platform-win32.cc ; MSDN article at http://msdn.microsoft.com/en-us/magazine/cc163996.aspx )

SQL Server timing functions http://msdn.microsoft.com/en-us/library/ms186724.aspx notes:

“Higher-Precision System Date and Time Functions

SQL Server 2008 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.“

-David


Posted by David Eison on Monday, December 7, 2009 6:52 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Sql Server Recursive Joins

Sometimes a client wants to know why they shouldn’t just stick with Sql Server 2000.  There are many compelling reasons to use Sql Server 2005 or 2008 over 2000, but for today I’ll focus on just one: Recursive Joins.

One criticism of SQL Server years ago when I first started using it was that it didn’t have Oracle’s “connect by” feature, which let you do recursive joins, which basically let you do tree based queries in SQL.  You have a table with a “parentid” column, and with a recursive join you can walk up or down the tree and get all the descendents or all the parents of an object.

Addressing this need, SQL Server added some recursive join features in 2005 and 2008.  It can be hard to google because “sql server connect by” has hundreds of pages saying “can’t be done in sql server” and pointing to the “sql for smarties” nested sets solution (which uses triggers to maintain extra sorting rows to make querying trees possible).

“with” was added in 2005 as the syntax for working with “Common Table Expressions”.  See http://consultingblogs.emc.com/christianwade/archive/2004/11/09/234.aspx

The basic idea is you establish a base to work from with a simple select, then union in the children (or parents or whatever) of the base, and your subquery in the union can refer back to the base as whatever you named it in the ‘with’.

2008 takes it one step further and lets you store the hierarchy path in the data model with HierarchyID. http://msdn.microsoft.com/en-us/magazine/cc794278.aspx  This makes the data model a little less obvious to work with but looks like a good way to go if you’re developing a new data model.  I happen to be working with an existing tree table so I’m sticking to ‘with’ for now.

An example “with” query to show all of the not-deleted category tree in ASPDotNetStorefront  (intentionally including unpublished categories, but you could easily exclude them):


with c as ( 
  select cast('/'+name as varchar(1000)) as pth,categoryid,displayorder,name,0 as lvl 
   from dbo.Category c1 
   where c1.ParentCategoryID=0 and Deleted=0 
 union all 
  select cast(c.pth+'/'+c1.name) as varchar(1000)) as pth, c1.categoryid, c1.displayorder, c1.name, lvl+1 as lvl 
   from dbo.category c1 join c on c1.ParentCategoryID = c.CategoryID
   where c1.Deleted=0
)
select lvl, c.pth
 from c join dbo.category c1 on c.categoryid = c1.categoryid
 order by lvl,c.displayorder,c.pth;

Posted by David Eison on Thursday, December 3, 2009 1:11 PM
Permalink | Comments (0) | Post RSSRSS comment feed