THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

How I lay out my T-SQL source code

I have a Visual Studio solution which mostly consists of unit and automated tests. All tests are developed in C#, using NUnit and our own library for database tests.
All my T-SQL code is also included in the same VS solution. Because Windows search frequently fails to find files, I am using VS search, which always finds all the right files as long as they are included in my solution. This also allows me to find both a module's source code and the corresponding unit tests, which is very convenient.

Like most other developers I know, I am using Subversion, which is a wonderful tool. For better integration between VS and Subversion I am using an excellent plugin, VisualSVN, and I am very happy with it.

My T-SQL modules are stored in separate files; when I build, I invoke an Iron Python script which combines all individual objects into a single SQL script, which can build my database from scratch and insert some static data, such as countries and currencies. Also I utilize C++ preprocessor, so that I can use macros:

#define YyyyMmDd(A) (YEAR(A)*10000 MONTH(A)*100 DAY(A))
 

These macros are as convenient to use as scalar UDFs, and as perfromant as inline ones. In my source code, I write this:

SELECT YyyyMmDd(SaleDate)  

and this line expands into this in the script:

SELECT  YEAR(SaleDate) * 10000 MONTH(SaleDate) * 100 DAY(SaleDate) ) AS SaleDateAsInt 

 without any runtime performance penalty whatsoever. This is great for reusing code. I wish scalar UDFs were supplemented by something like this.

This single SQL script is also checked into Subversion, and other solutions list it as their dependencies, and use it to automatically create a test database from scratch.

One more thing: the expected results for my unit tests are not hardcoded in my tests, they are stored separately in XML files. This dramatically simplifies the maintenance of my unit tests. These XML files are also included in the solution, so that I can search in them using VS search.

How do you store your source code?

Published Thursday, June 24, 2010 11:26 AM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

FindTim said:

I have used similar methods of storing my source code, but not to the sophistication that you use.  At one employer, I stored SQL objects in individual and combined files in a version control system.  Each statement needed a sanity check before it to ensure it would be a valid statement.  A C# program was built that combined a tree of changes into a single SQL file which became the deployment code.

I like your use of the C++ preprocessor to define Macros.  I have been wanting something like that and knew of the C++ tool, but had not thought enough outside the box to figure out a way to use it in my projects.  Thanks for the tip.

I have found that it is really helpful to step away from SSMS to write code.  It is too easy to make changes and loose them.  With that said; as I have seen in the last 10 - 15 projects I have been involved in, the biggest hurdle is "how do I get my existing database under control" and the second biggest is - "once I decide on how to control this, how do I get the rest of the people that have access to change objects on boarder with the protocol & procedures".

I appreciate the gentle prodding on this topic Alex.  If you call yourself a professional in the realm of software or databases, this should be a no-brainer to answer.

July 2, 2010 6:38 PM
 

Alexander Kuznetsov said:

FindTim,

Thank you for your kind words.

How do you perform sanity checks? Do you create objects in the context of a transaction, then roll them back?

July 7, 2010 9:28 AM
 

FindTim said:

if object_id(N'objectname') is not null

begin

--create the object

end

go

alter object

or

if it is data manipulation then ensuring the value you are manipulating is not already in place.

Regarding the C++ preprocessor.  I had thought at 1 point of using sqlcmd variables in my code for the marcos in place of your defines.  So in version control much like your suggestion you would see $(MACRONAME) and then just using sqlcmd to deploy with the appropriate variables.  But because the values are only known at run time, now there is another file that needs to be stored in version control.  e.g. the sqlcmd deployment commands.

I am curious if you have explored this option at all.

Thanks,

FindTim

July 9, 2010 12:32 PM
 

Alexander Kuznetsov said:

No I did not try out other approaches. The C++ preprocessor was already built in into VS, and it worked perfectly well for me, so I did no see the need to try out anything else.

July 9, 2010 12:59 PM
 

Jay Deesfield said:

Handy function you've made using the C++ preprocessor. I did not know that was possible!

July 12, 2010 11:09 AM
 

Rand E. Gerald said:

Where do we report errors we find in the book?

On page 285 it reads "to convert a strong to an integer."  It should read "to convert a string to an integer."  Just the type of error a spell checker will not find.

July 12, 2010 11:53 AM
 

Alexander Kuznetsov said:

Rand,

For now, I forwarded your post to Tony Davis, the editor of the book. I will also post his suggestions on "Where do we report errors we find in the book"

July 12, 2010 12:51 PM
 

Alexander Kuznetsov said:

Rand,

Please post further comments in this forum:

http://www.simple-talk.com/community/forums/589/ShowForum.aspx

Thanks,

AK

July 26, 2010 10:14 AM
 

Wilfred van Dijk said:

I use an almost identical solution by using the PPWizard tool from Dennis Bareis (http://www.dennisbareis.com). This is based on REXX scripting and allows me to re-use SQL code. I'll put some examples on my website in September

August 27, 2010 3:56 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement