Is it time to put your database on a diet? This presentation describes how to use the
features of SQL Server combined with maintenance to keep your
database lean. Slides and examples are now available.
If you have the SQL PASS DVD's It's AD-200.
SQL Job Scripter
Ever have to script out the SQL Agent jobs from your server?
After doing it by and too many times I ended up writing a small
utility to do the scripting. I recently had to pull it out
of the closet again so I decided to turn it into an open source
project on CodePlex. You'll find it here:
T-SQL code is the
foundation of many applications. This presentation
demonstrates practical methods for figuring out where to focus
efforts and some techniques that have been successful in
speeding T-SQL code.
slides and examples as a zip or just the
Technet: Episode 37
Andy is going to be on this live podcast from Microsoft. He'll
be talking about database development, the Loadfest, SQL Azure,
Denali and other things SQL Server. Call in with your
question at 9 AM PST, 12 PM EST on June 15th
Watch this one hour episode
of DNR TV featuring Andy Novick Discussing Database Development
and Testing using Visual Studio with Carl Franklin. It's
the material from my VS 2010 Data Dude presentation.
A 24 minute Webcast about everything new in SQL
Server 2008 R2. It includes demos of Utility Control
Points, PowerPiviot, and Data-tier Applications as well as
discussions of the new editions, Datacenter and Parallel
DataWarehouse, and new components Master Data Management and
This article summarizes my experience using Solid
State Disk boards with SQL Server. The performance can be
impressive for the right loads.
SQL Azure is Microsoft's relational database offering in the
cloud. While it's similar to SQL Server 2008 there are
enough differences that it's important to understand what you
can and can not do with SQL Azure. In particularly, how do
you get data up and down from the cloud with Sync Framework 2.0.
Database development has been a hodgepodge of
ad hoc techniques from the beginning. The Visual Studio Database
Professional Edition, a.k.a The Data Dude, gave us a new tool
for developing and testing databases. Visual Studio 2010 is the
third iteration of Data Dude technology and database development
is no longer an exclusive product. Its now included in every
Visual Studio edition from Professional on up. This presentation
introduces database development with Visual Studio.
Intro to SSIS Presentation
Join me for an introduction to SQL Server
Integration Services. You'll find the slides and example SSIS
- Extension Methods
- Nullable types
- IF operator
- Anonymous Types (Var)
- Implicit Typing
- Object and Array Initializors
- XML support
While some of these features stand on
own, many of them are part of the language to
facilitate LINQ. We'll take a look at LINQ-to-Objects,
LINQ-to-XML, and LINQ-to-SQL.
Understanding and Optimizing ADO.Net 2.0's SQL Statements
When you use ADO.Net 2.0 you're often choosing to let it write
SQL on your behalf. This presentation shows the SQL
statements that get written by ADO.Net. It also shows the
dramatic performance difference that can be achieved by using
techniques such as batching and SQLBulkCopy. SQL and VB.Net
examples are included. Updated 9/29/2007
This article presents a stored procedure for
convenient reporting of the space consumed by each table.
It's uses SQL Server's sp_spaceused to compatibility but
includes the schema so that like named tables can be
This is an SQL Server enhancement request that suggests a new
type of UDF to improve the performance of many scalar UDFs. An
extract of Chapter 11 of Transact-SQL User-Defined Functions and
a sample script are included.
This full day
presentation is a Microsoft Mini-Code Camp about the programming
aspects of SQL Server 2005 and how they have changed since SQL
Server 2000. I joined
Adam Machanic to create and make the presentation.
Slides and examples are available. Two upcoming presentations:
April 4th and Central Mass .Net and April 6th
Revised 9/24/05 * Programming
SQL Server 2005 (Yukon) with .Net
SQL Server 2005 (code name Yukon) opens up the opportunity to run
.Net code right inside SQL Server engine on a par with T-SQL. This
gives the programmer an expanded choice of programming language.
This presentation shows you how to write C# or VB.Net routines and
execute them in SQL Server. Joining that presentation is this one:
Creating User-Defined Types with SQL Server 2005
Code samples are included.
launched on January 4th, 2005. The new name reflects a
change from previous versions which were titled the T-SQL UDF of
the Week. UDFs will still be around, but won't have the
near exclusive coverage that they did in the first two volumes.
Instead you'll see expanded coverage of stored procedures, DTS
scripts, and .Net SQLCLR programming in C# and VB.Net.
a VSS Label Every Day with A SQL Agent Job
When discipline is in short supply it may be
better to turn to automation to eliminate problems. That's
just what happened when this program and SQL Agent Job were
created. The program creates a label in Visual SourceSafe
every day. The SQL Agent Job is used to make sure it
happens every day.
Distributing the Smart Client Application
This presentation discusses the available options for
distributing the smart client application such as XCOPY
deployment, no-touch, deployment, setup projects (MSI), and the
Updater Application Block (UAB). The options are discuss in
light of the practical considerations that to into making the
Implementing CRUD Operations with Stored Procedures: Part I and
These two articles describe how to create stored
procedures to implement the Create,
Read, Update, and Delete (CRUD) operations in SQL Server. It
shows the issues involved and then concentrates on supporting features of
SQL Server, such as timestamps, computed columns, identity columns, and defaults.
InfoPath is an exciting new application
that's part of the soon to be released Office 2003 product from
Microsoft. This article shows how to connect an InfoPath
form to a SQL Server database. The benefit of
connecting the two packages is the ability to create a user
interface for SQL Server very quickly. The user also gets
the ability to work offline and to use the features of Office,
such as spell checking.
Reading and writing to the disk is the heart of what any database management system does, SQL Server included. Input/Output (I/O) performance can make or break an application. This article discusses the diagnostic tools that can be used to examine SQL Server's I/O statistics so that you can make fact-based judgments about disk configurations. It also builds a solution that makes it easy to gather I/O statistics at peak use times every day.
Scripting Traces for Performance Monitoring on SQL Server
The SQL Profiler is a great tool for monitoring and analyzing SQL
Server Performance. I use it to watch the detailed actions of a stored
procedure, trigger or user-defined function (UDF). It can also be used to monitor aggregate performance of an
entire SQL Server instance, a single database, or to isolate performance
problems. When you are interested in overall performance, using SQL
Profiler over time, by that I mean every day, improves your knowledge of
your system and its performance characteristics and provides the
information you need to spot trends and changes of behavior. This
article shows how to create traces using T-SQL scripts and how to use a
trace script to monitor your system ever day during peak usage hours.
This article discusses the reasons that SQL Server decides to recompile a stored
procedure and demonstrates techniques that can be used to minimize
recompilation. If your system makes extensive use of stored procedures,
minimizing recompilation can give you a performance boost.
Discusses the reasons that SQL Server
recompiles a stored procedure and demonstrates techniques to
minimize recompilation. If your system uses stored procedures,
minimizing recompilation can give you a nice boost in
SP3 and xp_cmdshell Problem All SQL server 2000 instances should be
upgraded to SP3. But upgrading causes a problem with SP's
that use xp_cmdshell. The article
shows the problem and how to fix it.
UDFs for Precise Paging A typical data driven web site has plenty of pages with tables
of data made from database content. This article discusses using
the Inline Table Valued User-Defined Function to retrieve the minimal
amount of data for each page.
Securing SQL Server
for Web Applications Discusses the issues faced when using SQL Server in a
web application. It covers the vulnerabilities, the attacks, goals
for security, written security policies, securing the Windows server,
network topology, and security logs.
Complying with IT's
Security Requirements Whether your application is a software package, built
in-house, or you're an Application Service provider, the end users
rely on their IT department to enforce corporate security requirements.
This article is about how to work with the IT department to implement
security in a web application. It covers working on the
requirements check list to insure compliance with Infrastructure
requirements and application behavior requirements.
Beyond the Upsizing Wizard
Microsoft provides the Upsizing Wizard
for converting Access databases to SQL Server. But it's only
part of the conversion process. This article takes you through
the process of using the Upsizing Wizard and they through all
the additional steps it takes to complete the conversion.
don't need a consultant. Sometimes all you need is the answer
to a couple of questions. At SQL Consulting, Inc, an
experiencednote consultant will give you his best efforts free for
at least 15 minutes and often much longer. You may
want to discuss your problem with a consultant at
I do custom training for corporate clients on
specific SQL Server specialties, particularly writing faster
procedures and faster queries. I can extend this into other topics
such as partitioning, managing storage, using DMV's and more.
by Andrew Novick
The first and only book that goes into depth about SQL Server's user-defined functions.
is the New England based consulting company
Novick. Over the last 24 years I've been managing projects,
consulting, writing, teaching, and programming to create software
applications for both operations and analysis. The most important thing that
I can do for my clients is to understand what drives their business. Only by
understanding their problem or what they're trying to achieve can an
effective solution be found.
Much of this site is devoted to technology.
I've specialized in the Microsoft Windows environment and the
Microsoft tool set. I usually work with SQL Server, Visual Basic,
XML, ASP and now C# and .Net. There is a variety of material about my
work including articles about SQL Server, IIS, XML, VB.Net, Visual
Basic, ASP.Net, and security. This work has been for the Accounting,
Financial Services, Retail, Transportation, Manufacturing,
Telecommunications, and Real Estate industries.
Novick Software offers customized training courses in these subjects:
SQL Server Database Administration
SQL Server Application Development including T-SQL, SQL-XML, DTS, SQL-DMO,
performance, and other SQL Server topics.
Analysis and Design Using Object and Structured Methodologies
C#, VB (All versions), and ASP.Net
Courses can be tailored to fit your company's specific training needs.
The location can be on-site, off-site, or on-line. Some of these courses
are offered in conjunction with training partners such as