databasejournal.com databasejournal.com

Implementing CRUD Operations Using Stored Procedures: Part 2 - Page 2

Read Procedures The read procedure has one OUTPUT column for every column in the table. Let's take a look at the procedure for the Product table: CREATE PROCEDURE dp_Product_sel @ProductID int OUTPUT, @ProductName varchar(100) OUTPUT, @Description varchar(2000) OUTPUT, @UnitCost money OUTPUT, @UnitsInStock int OUTPUT, @InventoryCost money OUTPUT, @CreatedDT datetime OUTPUT, @CreatedByUSERID varchar(20) OUTPUT, ...

databasejournal.com databasejournal.com

Implementing CRUD Operations Using Stored Procedures: Part 2

In database terms, CRUD stands for the four essential database operations: Create, Read, Update and Delete. To create a high performance system, these four operations should be implemented by stored procedures, each procedure implementing one of the four operations. This is the second in a two part series of articles about why and how to go about writing the stored procedures. Last month's article covered why to use...

databasejournal.com databasejournal.com

Creating a SQL Server User Interface with InfoPath - Page 3

Once the form designer is displayed, the fun begins. Figure 7 shows the InfoPath Form designer after the Wizard has created the form. At the top of the form there is information for the novice InfoPath user about the presence of two views. I like to see instructions on the form where it is impossible to miss. However, this is not for the end user of the form so I have started by deleting the section and adding instructions of my...

databasejournal.com databasejournal.com

Creating a SQL Server User Interface with InfoPath

When Office 2003 is released about a month from now, some of the versions will contain two new applications OneNote and InfoPath. OneNote is a note taking application that may be interesting to those of you with tablet PCs. InfoPath is an XML based forms creation and entry application that looks very exciting to those of us who create applications that work with a lot of data on tablets and desktops. InfoPath is one of several new...

databasejournal.com databasejournal.com

Creating a SQL Server User Interface with InfoPath - Page 2

Designing the Form The first step in using InfoPath is to design the form using one of the form creation wizards. They are started from the Taskpad, which by default is docked on the right side of the screen. Figure 1 shows how InfoPath appears as I'm about to design a form with the "New from Data Source" wizard. Click for larger image Figure 1 Designing a form from a Data Source The Data source can come from one of...

databasejournal.com databasejournal.com

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. There are several ways to request I/O statistics from SQL Server such as the System...

databasejournal.com databasejournal.com

Examining SQL Server's I/O Statistics - Page 2

Using sp_monitor to Get I/O Statistics sp_monitor is a system stored procedure that produces four recordsets that display the values of system statistical functions since sp_monitor was last run. It also shows the last_run time, the current_run time, and the number of seconds in the measurement. There are no parameters to sp_monitor. Here is a short script to see its output: -- run sp_monitor sp_monitor go (Results) last_run ...

databasejournal.com databasejournal.com

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 all the time to watch the detailed actions of a stored procedure, trigger or user-defined function (UDF) that I am developing. 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...

databasejournal.com databasejournal.com

Scripting Traces for Performance Monitoring on SQL Server - Page 3

Loading the Trace Script into a Table Working with trace data in disk file is not easy. I suppose it is possible to load the data into Excel, SAS, or some other analysis tool. However, I find that the best place to work with the data is in a SQL table. The next step in the process loads the trace file on disk into a table for analysis. If you ask SQL Profiler to create the trace into a table for you, it creates a table with just...

databasejournal.com databasejournal.com

Minimizing SQL Server Stored Procedure Recompiles - Page 3

Reason 4: Interleaving DDL and DML Operations. Once a SQL DDL statement, such as CREATE TABLE, is executed the next SQL DML statement, such as SELECT, that is executed causes the stored procedure to be recompiled before execution is resumed. Stored procedures often contain DDL statements for managing temporary tables. You can reduce the frequency of this type of recompilation through several strategies: Group SQL DDL so that...

databasejournal.com databasejournal.com

Identifying Stored Procedure Recompilation Problems in SQL Server 2000 - Page 2

Reasons that a stored procedure will recompile SQL Server 2000 recompiles a stored procedure for many reasons. The first group of reasons that cause recompiles are: requests to recompile an individual procedure Requests to recompile procedures that depend on a database object, such as a table or view Actions that result in the recompilation of all procedures. In explicit SQL Server terms these reasons...

databasejournal.com databasejournal.com

Find Out What They're Doing with fn_get_sql - Page 2

The data type of the EventInfo column is nvarchar(255). This has proven to be an annoying limitation because it restricts the results to the first 255 characters of any SQL statement. While that might be enough when the statement is executing a stored procedure, it is often insufficient when a complex SELECT or UPDATE is involved. Calling system UDFs is different from calling your own functions. The documented system UDFs all return...

databasejournal.com databasejournal.com

Find Out What They're Doing with fn_get_sql

Don't miss the movie that accompanies this article! Not only did SQL Server 2000 introduce the concept of user-defined functions (UDF) but also Microsoft's development team created several such functions as part of the database product. In addition to many undocumented system UDFs, used internally by SQL Server, the product release has nine documented UDFs that are part of a special schema in the master database named...

databasejournal.com databasejournal.com

Find Out What They're Doing with fn_get_sql - Page 4

Leave Batch B-4 running and switch back to the connection with Script A. Batch A-5 uses the sp_lock system stored procedure to show the locks being held by the system. The exclusive locks (Mode = X) held by Script A are highlighted in red and the wait for a shared lock (Mode = S) is highlighted in blue. -- Batch A-5 sp_lock shows who's waiting and who's locking PRINT 'Batch A-5 -- Output of sp_lock' exec...

databasejournal.com databasejournal.com

Find Out What They're Doing with fn_get_sql - Page 3

A truly real world example of using fn_get_sql might involve creating a deadlock, using sp_lock to find out which processes are blocked, and then using fn_get_sql to retrieve the text of the SQL that the blocked and blocking processes were executing. The thought of publishing code that deliberately created a deadlock some how struck me as overly risky so I've decided to use a slightly simpler example, a case of simple blocking due to...