Novick Software
SQL Server Consulting • Design • Programming • Tuning

  andy novick is a sql server mvp

 

 

sp_storage a proc to manage disk space in SQL Server

This procedure makes managing space on a SQL Server much simpler by reporting usage at the Drive, Database and File basis.

 


The Biggest Loser: Database Edition

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: https://sqljobscripter.codeplex.com/



Writing Faster T-SQL Stored Procedures and Functions: Lessons Learned

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. (updated 10/20/12 slides and examples as a zip or just the updated slides.
 



Talk 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


Looking at SQL Server in 2011
Andy is interviewed about what's going to happen in 2011 with SQL Server.  l


Dot-Net-Rocks TV with Andy Novick

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.


What's new in SQL Server 2008 R2 and what it means to you!

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 StreamInsight.

A transcript is available here.

 

SQL Server Loadfest R2

The 2010 Loadfest went great.  We had 50 people on hand installing SQL Server 2008 R2

Slides are available on the Loadfest Page

sql server loadfest r2 - Andy Novick discussing the install
 


MSSQLTips for 2010:

SQL Azure videos on SQL Share


SQL Server Performance with Solid State Disks (SSD)

This article summarizes my experience using Solid State Disk boards with SQL Server.  The performance can be impressive for the right loads.


SQL Azure Presentation
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.


Tip: Windows Azure - Set the SQL Server instance with dsinit

Developing and Testing SQL Server Databases with Visual Studio 2010 Presentation

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. It’s 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 package here.
 


MSSQLTips Articles

I've written a few articles for MSSQLTips:


   Video's on SQL Share

I've created a series of short videos about User-Defined functions in SQL Server



Partitioning Presentation

Partitioning Tables, Views and Indexed Views. Presentation on various forms of partitioning including sliding windows for data warehouses and  hash partitioning for OLTP databases


Defending SQL Server from SQL Injection Attack - Presentation

SQL Injection attacks have been hitting web sites for a decade and show no signs of letting up.  Learn defensive techniques to protect SQL Server.


2009 Calendar is Available

Check out the circled dates or download and print your own.  


Big Data: Working with Terabytes in SQL Server
A presentation on how to handle terabyte databases.  Partitioning, filegroups, and read-only files all play a big part.


Visual Basic 2008 New Language Features Leading up to LINQ

We'll take a look at these features:

 - Extension Methods
 - Nullable types
 - IF operator
 - Anonymous Types (Var)
 - Implicit Typing
 - Object and Array Initializors
 - XML support
 - LINQ

While some of these features stand on their 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


dba_spaceused

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 distinguished.


Proposed Enhancement to Solve the UDF Performance Problem

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.


User Defined Functions in SQL Server 2005
This presentation brings you up-to-date on UDFs.  Examples of both T-SQL and SQLCLR functions are supplied along with the sample database.
Updated with SQL Pass Version


CSLA: What and Why

Presentation on CSLA, the business object architecture that I use.


SQL Server 2005 Service Broker

Presentation and example script about the new transactacted asynchronous messaging and queuing subsystem that's part of SQL Server 2005.


SQL Server Programming: From 2000 to 2005

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 NEVB.com


Book review: SQL Server 2005 for Developers


.Net Developer Utility Roundup
As part of the NEVB Developer Utility Roundup this presentation introduces some of the utilities that I use for my .Net work. Links to find the utilities are collected on the Developer Utility page.


A Note on SQL Aggregates
This article shows a comment on the User Defined Aggregate article by Joe Celko and an additional aggregate, bit-wise OR, implemented in SQL.


Presentation: An Application for Executing Ad Hoc SQL in a Regulated Environment What do you do when you have bad data in a production environment.  You fix it, right? This presentation is about an application that audits ad hoc SQL statements and schem changes as they are executed on production systems.


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.


Take a first look at a SQL Server 2005 (Yukon) User Defined Product Aggregate (udagg) This article implements a Product aggregate in VB.Net.


The Coding-in-SQL Newsletter 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.

The latest issue: A SQLCLR Stored Procedure that Writes to a File


Inserting 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.


Achieving Programmer Productivity using a Reusable Library and Code Generation Find out the good news about how much programmer productivity can really be achieved with .Net.  I was surprised by the numbers and I wrote the code.

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 choice.


Isolated Storage in .Net  This presentation and .Net example describes isolated storage and describes what it's good for.


Presentation: Building on the .Net Framework: Creating Professional WinForms Applications The .Net framework gives us a great starting point for building Smart Client applications.  This presentation is about what you have to build on top of .Net to create an environment for delivering supportable, maintainable, flexible business applications.


Download: .Net SQL XML Demo Utility in C#

This is a .Net WinForms port of the utility from Appendix C of SQL Server 2000 XML DistilledIt's written in C# and delivered as source code.



Implementing CRUD Operations with Stored Procedures: Part I and Part II 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.


NewsletterT-SQL User-Defined Function of the Week

This free newsletter has changed.  It's now the Coding-in-SQL newsletter.  Subscriptions remain in place but you'll find a revised focus.  More...


Book Review: ASP.Net Upgrader's Guide  by Boehm and Murach:  Targeted information for the experienced ASP.Net programmer.


Book Review: Windows Forms Programming in Visual Basic .Net by Chris Cells and Justin Ghetland  Goes beyond the basics.


Book Review: Expert One-on-One: Visual Basic.Net Business Objects by Rockford Lhokota The CSLA framework.


Book Review: Visual Basic.Net Power Coding


Book Review: Building Solutions with Microsoft's .Net Compact Framework by Dan Fox and Jon Box. 


Presentation: Generating .Net Business and Data Layers for an n-Tier Application Code generation for an n-tier business application is discussed in the context of a code generation tool: DeKlarit.  The paradigm used by this tool is explained and we take a look at a few examples including creating a Business Component model, creating the database, and generating the C# or VB.Net code to implement it.  A sample of generated code is also available.


Tip (C#): Creating a COM object by ProgID in C#


Creating a SQL Server User Inferface with InfoPath

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.


Examining SQL Server's I/O Statistics

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.


TIP (VB): Background programs Hog the CPU


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. 


Minimizing SQL Server Stored Procedure Recompiles

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.


Identifying Stored Procedure Recompilation Problems in SQL Server 2000 SQL Server recompiles stored procedures when necessary.  This article describes the conditions that will cause a stored procedure recompile and shows you how to monitor the recompilation process.   You can also see a video showing how to use Windows Performance Monitor to identify aggregate recompilation statistics and one about how to use the SQL Profiler to see the recompilation details.


Minimizing SQL Server Stored Procedure Recompiles

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 performance.


Find Out What They're Doing with fn_get_sql

SQL Server 2000 Service Pack 3 includes a new system UDF, fn_get_sql.  This article show you how it works and demonstrates how to use it as an aid in diagnosing a blocking situation.  You can also see a video of the demonstration scripts in action.


Indexed Views Basics in SQL Server See how to create and use indexed views in SQL Server for improved performance.


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.


Using Inline 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.


Protecting Your IIS Server and Web Application Did you get hit by Code Red or Nimbda?  This article is all about how to recover from attack and protect your system in the future.


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.


Sometimes you 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 SQLConsulting.com 

Testimonial from a recent project...

 

Is your application slow?

Do you think your database is to blame?

Want someone to help?

 

Is your SQL Server under attack? Do you need to implement Security Best Practices such as encryption, protect from SQL Injection and to close security vulnerabilities?

 

Migrating from SQL Server 2000 to 2005, 2008, or 2008 R2 and need help or mentoring or coding?

 

Something else a problem?  Give me a call or drop me an e-mail!


Custom Training

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.

 


Transact-SQL
User-Defined Functions

by Andrew Novick

 

The first and only book that goes into depth about SQL Server's user-defined functions.

Code Download and Links


Andrew Novick,Novick Software SQL Server Consulting, Programming, Training, Design, and Project Managment Novick Software is the New England based consulting company of
Andrew 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.

 


 

Custom Training
 

Novick Software offers customized training courses in these subjects:

  • Visual Basic.Net

  • 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 SysEd.

 


Contact Information

Andrew Novick: anovick@novicksoftware.com
(978) 440-8126
(National Consulting from a Boston base)
 
General Information: info@novicksoftware.com
Webmaster:
webmaster@novicksoftware.com


SQL Server 2000 XML Distilled

Published by Apress.

A book with exactly what you need to know about SQL Server XML.
 

 



RSS as HTML

Personal Blog

 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule