276
C
HAPTER
18
Getting and staying connected—or not
Server directly via ports is
OK
but I beg to disagree as I’m concerned with hard-coding
ports and network snooping.
Building any connection strategy is all about what works for your application archi-
tecture and security infrastructure. In some cases the just-in-time connection strategy
makes sense, but in others it incurs needless overhead. Sometimes the connection
pool helps improve performance, but at other times it just gets in the way.
Sometimes we as writers, consultants, and pundits make things a lot more compli-
cated than they have to be by overloading our readers and clients with a lot of unnec-
essary detail. Considering that most
SQL
Server implementations are
SQL
Server
Express, those that mold opinions need to remember to keep things simple whenever
possible without compromising security or performance. For the most part, the devel-
opers I work with want solutions, not options. I hope this chapter has provided some
of these solutions.
About the author
William (Bill) Vaughn is an industry-recognized author, mentor,
and subject-matter expert on Visual Studio,
SQL
Server, Report-
ing Services, and data access technologies. He’s worked in the
computer industry for over 37 years. In 2000, after 14 years at

) in Microsoft
SQL
Server 2005 and 2008, and
SQL
Server Query Analyzer in Microsoft
SQL
Server 2000, is one of my favorite fea-
tures of those applications. I love the idea that during the development of my data-
base, which contains more than 100,000 objects at the moment, I can use my utils
to easily perform everyday tasks such as searching for a specific object, showing the
object definition, or finding dependencies between database objects. If you spend
some time on writing your own utils to fit your needs, I can promise you won’t
regret it.
Custom keyboard shortcuts
Both Query Analyzer and
SSMS
provide the ability to call
T-SQL
code with custom
keyboard shortcuts. You can define the shortcuts using the main menus of those
applications (note that the way you define the shortcuts in both applications is
slightly different).
To define custom shortcuts in Query Analyzer:
1
On the Tools menu, click Customize... (see figure 1).
Figure 1 To define custom keyboard
shortcuts in Query Analyzer, in the Tools
menu, click Customize...
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

What makes this feature powerful is that you can use these shortcuts with the text
selected in the query editor window. You select the text, press the appropriate short-
cut, and then the code assigned to the shortcut is concatenated with the text you’ve
selected and the result of concatenation is executed. Let’s see some examples. By
default, the Alt-F1 shortcut is reserved for the
sp_help
system stored procedure. Open
Figure 2 Keyboard shortcuts defined in the Customize window in Query Analyzer
Figure 3 To define custom keyboard
shortcuts in SSMS, in the Tools menu,
click Options...
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
279
Creating your custom utility to use with keyboard shortcuts
a new query window in
SSMS
(or Query Analyzer), connect to any
SQL
Server 2005 or
2008 instance, and type the following:
'sys.objects'
Then select the text you’ve just written and press Alt-F1. This should display the result
of the
sp_help
procedure executed with
'sys.objects'
as a parameter (the result
should consist of the metadata of the sys.objects catalog view).
NOTE

280
C
HAPTER
19
Extending your productivity in SSMS and Query Analyzer
The prefix
sp_
isn’t accidental here. If you want the procedure to work with database
objects in every single database in your
SQL
Server instance, the best way is to create it
in the master database and name it with an
sp_
prefix so that you can easily call it no
matter what the current database of your session is (
SQL
Server will search the master
database for objects prefixed with sp_).
Listing 1 shows an example of what the procedure’s code can look like. You can
add some improvements. I’ll provide some suggestions later in this chapter.
USE master
GO
IF OBJECT_ID('dbo.sp_getcolumns','P') IS NOT NULL
DROP PROC dbo.sp_getcolumns
GO
CREATE PROC [dbo].[sp_getcolumns]
@object sysname,
@horizontal tinyint = 0
AS
SET NOCOUNT ON

GO
Listing 1 Creating sample utility
sp_getcolumns
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
281
Creating your custom utility to use with keyboard shortcuts
First of all, note that I use the
sys.all_columns
catalog view to retrieve the column
list (to retrieve the column list in
SQL
Server 2000, you should use the dbo.syscolumn
system table).
NOTE
Normally you should avoid the sp_ prefix for your stored procedures.
Use it only in development or for testing, and not in your produc-
tion databases. Also, it may be required to mark your newly created
procedure as a system object with an undocumented stored
procedure—
sp_MS_MarkSystemObject
. Otherwise your procedure may
not work properly with all databases within the
SQL
Server instance.
Remember that the
sp_MS_MarkSystemObject
system procedure is for
internal use only and isn’t supported by Microsoft; therefore, never
use it against objects in your production databases.

variable. I wanted the
result list to be comma separated; therefore, the comma is concatenated to every col-
umn/parameter name. The last comma is unnecessary, so I remove it, either by using
LEFT
function (for column/parameter list returned horizontally) or by simple
UPDATE
statement (for the list returned vertically).
Finally, all rows from the
@lines
table vari-
able are returned in the appropriate order.
Simple, isn’t it?
All you have to do after you create the
procedure is to assign a custom keyboard
shortcut to it. Then you can test the util. Go
to one of your databases, write the name of
one of your database objects (if you use the
fully qualified name of the object, put it in
single quotes), and press the appropriate
keys on your keyboard. The example is shown
in figure 5. A parameter list of the
sp_getcolumns
stored procedure is returned
(current database: master).
Some thoughts on how you can improve the procedure presented in this chapter:

Add an option to return the data types of the columns/parameters.
Figure 1 Sample use of the
sp_getcolumns
utility


Use the
NOLOCK
hint to avoid unnecessary locking on the system objects.
Some ideas for utilities to implement
Here are some ideas of other utilities that might be useful for you:

Searching for database objects by name

Searching for database objects depending on the given object

Scripting database objects

Selecting sample rows from a table
This is a call to action! Create your own utilities, assign them to the custom shortcuts,
and make your everyday work more efficient than ever before.
Summary
With custom keyboard shortcuts and your own stored procedures, you can immensely
improve your productivity. A skilled developer can avoid using the mouse in a graphi-
cal interface and just use a keyboard to execute
T-SQL
code. As a result, some
SQL
Server developers are considered magicians because they can do everything just by
quickly tapping the keyboard.
About the author
Pawel Potasinski is a database developer and consultant working
for Asseco Business Solutions S.A. corporation. He’s been working
with
SQL

This approach has a few problems; here are three of them:

The original developer could forget to contact another developer to make
the code change.

A lot more people have to make changes now; this will increase the chance of
mistakes.

All the other developers have to update and test their code to make sure it
works as expected.
As you can imagine, it’s much easier to change and test the code in one place. This
is the primary reason you need a tools database. Ideally, the tools database should
have loosely coupled code and data; it shouldn’t have data and code that depend
on another user-created database. To give you an example, the tools database
shouldn’t format dates based on a calendar table from the human resources data-
base; the calendar table should be stored in the tools database itself.
What belongs in the tools database?
The following are examples of what should go in a tools database:

ZIP
code and address tables

Auxiliary table of numbers

Maintenance procedures for the database server
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
284
C
HAPTER

CREATE TABLE Numbers(number int primary key not null)
GO
DECLARE @Loop int
SET @Loop = 0
SET NOCOUNT ON
WHILE @Loop <=250
BEGIN
INSERT Numbers(number) VALUES (@Loop)
SET @Loop = @Loop + 1
END
GO
Generating a calendar on the fly
Let’s look at how you can use a numbers table to create a result set of dates. When
working with dates and number tables, you’ll use the
DATEADD
function. The syntax for
DATEADD
function looks like this:
DATEADD

(

datepart

,

number,

date


SELECT DATEADD(mm,-1,'20090501')
(Result set)
2009-04-01 00:00:00.000
Now it’s time to use our numbers table to create some dates. The query in listing 2
will add one month multiplied by the number in the numbers table to today’s date
and return the next 100 months, beginning with today’s date.
SELECT DATEADD(mm,number,CONVERT(varchar(8),GETDATE(),112))
FROM dbo.Numbers
WHERE number < 100
ORDER BY number
The query creates the dates in the result set in listing 3.
2008-10-29 00:00:00.000
2008-11-29 00:00:00.000
2008-12-29 00:00:00.000
2009-01-29 00:00:00.000
....
....
2017-01-29 00:00:00.000
If you add a minus sign in front of number, it’ll go back in time. As shown in listing 4,
we can use the minus sign before a number to go back in time.
SELECT DATEADD(mm,-number,CONVERT(varchar(8),GETDATE(),112))
FROM dbo.Numbers
WHERE number < 100
ORDER BY number
The query creates the dates in the result set in listing 5.
Listing 2 Query to create dates from the numbers table
Listing 3 Abridged result set of dates created from the numbers table
Listing 4 Query to create dates in the past from the numbers table
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

. You want
to get all the characters in that string without duplicates. This is easily accomplished
with a numbers table. First, create the stored procedure in listing 8.
CREATE PROCEDURE SplitString
@StringToSplit varchar(1000),
@Delimiter varchar(10)
AS
SELECT DISTINCT SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number
➥
+ 1,
CHARINDEX(@Delimiter, @Delimiter + @StringToSplit + @Delimiter, number + 1)
➥
- number -1) As StringItem
FROM Numbers
WHERE number <= LEN(@Delimiter + @StringToSplit + @Delimiter) - 1
AND SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number, 1) =
➥
@Delimiter
ORDER BY StringItem
GO
Listing 5 Abridged result set of dates created in the past from the numbers table
Listing 6 Query to return the first and last day of every quarter from 2000 to 2024
Listing 7 Abridged result set of query in listing 6
Listing 8 Stored procedure to split delimited strings with the numbers table
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
287
Using an auxiliary table of numbers
Here’s how you’d call that stored procedure with a string delimited with commas:
EXEC SplitString 'Z,X,A,B,D,F,Z,Z,Z,Z,A,V,S,Q,L,B,B,B,B,B',','

CHARINDEX(@Delimiter, @Delimiter + @StringToSplit + @Delimiter, number + 1)
➥
- number -1) As StringItem
-- Use the numbers table to loop
FROM Numbers
-- Keep going until you arrive at the end of the string
WHERE number <= LEN(@Delimiter + @StringToSplit + @Delimiter) - 1
-- Return only positions between delimiters
AND SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number, 1) =
➥
@Delimiter
ORDER BY StringItem
Listing 9 Result set of unique characters in a string
Listing 10 Stored procedure with comments
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
288
C
HAPTER
20
Why every SQL developer needs a tools database
As you can see, I created a stored procedure and made it accept different delimiters;
this provides flexibility and also one codebase. I don’t need to have a stored proce-
dure for every delimiter that can possible be used.
Placing common code in the tools database
Common code is code that typically can be written and consumed only one way. Convert-
ing from Celsius to Fahrenheit, converting from miles to kilometers, and calculating
sales tax are some examples.
Let’s look at a sales tax calculation example; each state will have a current tax rate
and a previous tax rate in a table. The row where the EndDate is

IF @Date IS NULL
BEGIN
SELECT @TaxRate = TaxRate
FROM StateTaxRates
WHERE StateCode = @StateCode
AND EndDate IS NULL
END
ELSE
--Grab tax rate for a specific day
BEGIN
SELECT @TaxRate = TaxRate
FROM StateTaxRates
Listing 11 Table for state tax rates
Listing 12 User-defined function to calculate tax
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
289
Formatting
WHERE StateCode = @StateCode
AND @Date >= StartDate
AND @Date < EndDate
END
--Do the calculation by multiplying the tax with the amount
RETURN @Value * @TaxRate
END
GO
Four example calls and their results are shown in listing 13.
SELECT dbo.CalculateStateTax(100,'NJ',null)
(Result set)
8.0000

2008-10-29 2001-01-01
Listing 13 Four example calls and their results
Listing 14 Function to format a date
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
290
C
HAPTER
20
Why every SQL developer needs a tools database
Calling code from a different database
In order to call code that resides in a different database, you’ll need to use three-part
notation. Three-part notation looks like this:
DatabaseName.Schema.Object
A stored procedure named
SplitString
exists in the tools database within the dbo
schema. In order to execute this stored procedure from the temp database, your code
would look like this:
USE tempdb
GO
EXEC Tools.dbo.SplitString 'Z|X|A', '|'
GO
You need to have appropriate permissions in order to call objects in a database from
another database!
Summary
Hopefully this chapter has given you an idea of how a tools database can help you save
time and headaches. Next time when you have to make code changes in several places
to fix a bug or make a change request, consider a tools database: it’ll make your life
easier.

of deprecated features?
Among the possible reasons are the following two:

You can obtain greater longevity for an application if you’re an application
developer.

You can identify possible upgrade problems for your existing applications
before the next release of
SQL
Server hits the shops.
The deprecation feature consists of two components:

The
SQLServer:Deprecated

Features
object performance counter.

The
Deprecation
category of trace events, including the
Deprecation
Announcement
event class (indicates that a feature will be removed in a future
version of
SQL
Server) and the
Deprecation

Final