The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Best Practices

With something as management oriented as version control, it makes sense to begin with a discussion of what are commonly known in management circles as best practices. Best practices are techniques and approaches to accomplishing tasks that are better than others. Ostensibly, they are the best ways of doing things. All disciplinesespecially engineering disciplineshave best practices. Most types of skilled work have techniques that work better than others. I'll talk about a few that I think you should follow regarding source code management.

Store Objects in Scripts

Although you could conceivably store the Transact-SQL source code for the objects you create exclusively in the SQL Server databases in which they reside, this isn't a very good idea. Why? Because without tools like the dt_% procedures, you have no way of version controlling them. That is, you have no way of managing changes to them, of rolling back to a former version (without restoring an entire database backup), and no way of checking differences between versions. So, first and foremost, store the source to your objects in script files.

Maintain Separate Scripts

Store each object in its own script file. This keeps the granularity of the system high, and helps you avoid blocking other developers from making changes to unrelated code. You can edit a procedure or other type of script without worrying about keeping other people from working.

Don't Use Unicode

Save each script file in ANSI (non-Unicode) format. Not all version control systems can read Unicode (the current version of VSS can't), and even though this is the default format of the scripting facility in Enterprise Manager, you should not use it if you want to remain compatible with the majority of text file-based tools out there. For example, even though you can check a Unicode file into VSS, it is treated as a binary file because VSS doesn't recognize the Unicode text format. This means that you can't check for differences between versions of the filea severe limitation.

Use Labels to Denote Versions

Most version control systems have a facility that allows you to label or tag a version of your source code so that you can later reference it as a coherent group. Use these facilities to denote software or application versions. It'll save you trouble down the road. Obviously, the various source members in an application change at different rates. Their internal version numbers will differ. However, if you assign a versionwide label to the files that make up a given release of the software, you can retrieve and compile that release as often as you need to without having to synchronize the various internal revision numbers manually.

Use Keywords to Sign Your Files

A common feature of version control systems is a facility that allows you to embed special keywords or tags in your source files that can be expanded into version-related info when you check in the files. In VSS, these are known as keywords, and they allow you to record such useful info as the person who last changed a file, the date and time of the last modification, the internal version number of the file, and many other useful tidbits, in the source files themselves.

VSS keywords are enclosed in a pair of $ symbols. For example, to embed the author of a given source member in that file, include the $Author $ keyword. When the file is checked in, $Author $ will be translated into the VSS user name of the last person to change the file.

Typically, you embed these keyword tags in comments so that they don't disturb your code. A good place to put these comments is at the top of your script files. Here's an example of a Transact-SQL comment block that I often use (Listing 4-1):

Listing 4-1 An example comment block.

/*
Object: sp_usage
Description: Provides usage
 information for stored 
procedures and descriptions of
 other types of objects
Usage: sp_usage @objectname=
'ObjectName', @desc='Description of object'
[, @parameters='param1,param2...']
[, @example='Example of usage']
[, @workfile='File name of script']
[, @author='Object author']
[, @email='Author email']
[, @version='Version number or info']
[, @revision='Revision number or info']
[, @datecreated='Date created']
[, @datelastchanged='Date last changed']
Returns: (None)
$Workfile: sp_usage.sql $
$Author: Khen $. Email: khen@khen.com
$Revision: 7 $
Example: sp_usage @objectname='sp_who',
 @desc='Returns a list of currently running jobs', 
@parameters=[@loginname]
Created: 1992-04-03. $Modtime: 4/07/00 8:38p $.
*/

Note the text that VSS has inserted into each keyword tag. In the case of $Workfile $, VSS has inserted "sp_usage.sql" into the tag. In the case of $Author $, "khen" has been inserted.

CAVEAT

VSS keywords are case sensitive. If you use VSS and decide to embed these keywords in your Transact-SQL source files, be sure to enter them in the correct case. If you've enabled keyword expansion, but notice that some of the keywords you've entered aren't being expanded properly, check the case of the errant keywords.

You enable VSS keyword expansion by file extension using the VSS Administrator program. To turn on keyword expansion for a particular type of file, go to the General tab in the Tools|Options dialog in the VSS Administrator program. In the Expand keywords in files of type entry box, enter the file masks of the files in which you want keyword expansion to occur (e.g., *.SQL).

Table 4-1 lists the supported VSS keywords and what they signify.

Don't Encrypt Unless Absolutely Necessary

When you distribute SQL Server-based applications to customers and other third parties, you may be tempted to encrypt the source to your stored procedures, functions, and similar objects. Obviously this protects your code from prying eyes and keeps people from making changes to your code without your knowledge.

That said, unless you have real concerns about confidential or proprietary information being stolen, I recommend against encrypting your SQL Server objects. To me, encrypting SQL Server objects is usually more trouble than it's worth. There are a number of disadvantages to encrypting the source code to SQL Server objects. Let's discuss a few of them.

One, encrypted objects cannot be scripted, even by Enterprise Manager. That is, once a procedure or function is encrypted, you cannot retrieve its source from SQL Server. The well-known but undocumented methods of decoding encrypted source in earlier versions of SQL Server no longer work, and other methods one might discover are not supported by Microsoft. To make matters worse, if you attempt to script an encrypted object via Enterprise Manager using the default options, your new script will have a DROP statement for the object, but not a CREATE. Instead, all you'll see is a helpful comment informing you that scripting encrypted objects isn't supported (whereas, obviously, dropping them is). If you run this script, your object will be lost. It will be dropped, but not recreated.

Two, encrypted objects cannot be published as part of a SQL Server replication. If your customers set up replication operations to keep multiple servers in synch, they'll run into problems if you encrypt your code.

Three, you can't check encrypted source code for version info (such as that inserted by a source code management system). Because customers can load backups that may reinstall an older version of your code over a newer one, it's extremely handy to be able to check the code for version info on the customer's server. If your code is encrypted, you can't easily do this. If it's not, and if you've included version information in the source code, you should be able to easily determine the exact version of an object the customer is using.

Listing 4-2 shows a procedure that you can use to list the version information in your SQL Server objects. Basically, it scans a database's syscomments table for the keyword tags supported by VSS and produces a columnar report of the objects with these embedded keywords. Running this procedure can give you a quick bird's-eye view of the version info for all the Transact-SQL source code in a database.

Listing 4-2 A procedure to list VSS version information in stored procedures.

USE master
GO
IF OBJECT_ID('dbo.sp_GGShowVersion') IS NOT NULL
DROP PROC dbo.sp_GGShowVersion
GO
CREATE PROC dbo.sp_GGShowVersion
 @Mask varchar(30)='%', @ObjType varchar(2)='%'
/*
GGVersion: 2.0.1
Object: sp_GGShowVersion
Description: Shows version, revision and 
other info for procedures, views,
triggers, and functions
Usage: sp_GGShowVersion @Mask, @ObjType -- 
@Mask is an object name mask
(supports wildcards)
indicating which objects to list
@ObjType is an object type mask
(supports wildcards)
indicating which object types to list
Supported object types include:
P   Procedures
V   Views
TR  Triggers
FN  Functions
Returns: (none)
$Workfile: sp_ggshowversion.SQL $
$Author: Khen $. Email: khen@khen.com
$Revision: 1 $
Example: sp_GGShowVersion
Created: 2000-04-03. $Modtime: 4/29/00 2:49p $.
*/
AS
DECLARE @GGVersion varchar(30),
 @Revision varchar(30), @author varchar(30),
@Date varchar(30), @Modtime varchar(30)
SELECT @GGVersion='GGVersion: ',
@Revision='$'+'Revision: ',@Date='$'+'Date:
',@Modtime='$'+'Modtime: ',@Author='$'+'Author: '
SELECT DISTINCT Object=SUBSTRING(o.name,1,30),
Type=CASE o.Type
WHEN 'P' THEN 'Procedure'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trigger'
WHEN 'FN' THEN 'Function'
ELSE o.Type
END,
Version=CASE
WHEN CHARINDEX(@GGVersion,c.text)<>0 THEN
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX
(@GGVersion,c.text)+LEN(@GGVersion),10)),
1,ISNULL(NULLIF(CHARINDEX(CHAR(13),
LTRIM(SUBSTRING(c.text,CHARINDEX
(@GGVersion,c.text)+LEN(@GGVersion),10)))-1,-1),1))
ELSE NULL
END,
Revision=CONVERT(int,
CASE
WHEN CHARINDEX(@Revision,c.text)<>0 THEN
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX
(@Revision,c.text)+LEN(@Revision),10))
,1,ISNULL(NULLIF(CHARINDEX('
',LTRIM(SUBSTRING(c.text,CHARINDEX
(@Revision,c.text)+LEN(@Revision),10)))-1,-1),1))
ELSE '0'
END),
Created=o.crdate,
Owner=SUBSTRING(USER_NAME(uid),1,10),
'Last Modified By'=
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX
(@Author,c.text)+LEN(@Author),10)),1,ISNULL(NULLIF(CHARINDEX('
$',LTRIM(SUBSTRING(c.text,CHARINDEX
(@Author,c.text)+LEN(@Author),10)))-1,-1),1)),
'Last Checked In'=CASE WHEN CHARINDEX
(@Date,c.text)<>0 THEN
SUBSTRING(LTRIM(SUBSTRING(c.text,
CHARINDEX(@Date,c.text)+
LEN(@Date),15)),1,ISNULL(NULLIF(CHARINDEX('
$',LTRIM(SUBSTRING(c.text,CHARINDEX
(@Date,c.text)+LEN(@Date),20)))-1,-1),1)) ELSE NULL END,
'Last
Modified'=SUBSTRING(LTRIM(SUBSTRING
(c.text,CHARINDEX(@Modtime,c.text)+
LEN(@Modtime),20)),1,ISNULL(NULLIF(CHARINDEX('
$',LTRIM(SUBSTRING(c.text,CHARINDEX
(@Modtime,c.text)+LEN(@Modtime),20)))-1,-1),1))
FROM dbo.syscomments c RIGHT 
OUTER JOIN dbo.sysobjects o ON c.id=o.id
WHERE o.name LIKE @Mask
AND (o.type LIKE @ObjType AND o.TYPE in ('P','V','FN','TR'))
AND (c.text LIKE '%'+@Revision+'%' OR c.text IS NULL)
AND (c.colid=(SELECT MIN(c1.colid) FROM
 syscomments c1 WHERE c1.id=c.id) OR
c.text IS NULL)
ORDER BY Object
GO
GRANT ALL ON dbo.sp_GGShowversion TO public
GO
EXEC dbo.sp_GGShowVersion

(Results abridged)

Object                Type     Version  Revision Created
------------------------- --------- -------- -------- -----
sp_created      Procedure NULL   2    2000-04-08 00:19:51.680
sp_GGShowVersion      Procedure 2.0.1  1   2000-04-29 15:30:56.197
sp_hexstring          Procedure NULL     1   2000-04-08 15:12:21.610
sp_object_script_comments Procedure NULL   1   2000-04-29 12:59:08.250
sp_usage              Procedure NULL     6   2000-04-07 20:37:54.930

This procedure lists info for the VSS tags I use most, but could be modified to list any tag. Note the inclusion of the custom tag "GGVersion." You can use this tag to link Transact-SQL source with a particular version of your application. I've formatted GGVersion using the traditional layout of the Windows four-part VERSIONINFO ProductInfo fieldthe fourth part being supplied by VSS's $Revision $ keyword.