Tony’s Weblog

ASP.NET, MVC, AJAX, SilverLight, C#, WCF Web Service, SQL 2005 and other interesting stuff.

Archive for August, 2008

2008 Summer Olympic Games, Beijing

Posted by Tony on August 8, 2008

Posted in Uncategorized | Leave a Comment »

How to Create Indexes in SQL Server 2005

Posted by Tony on August 4, 2008

In this tutorial you will learn about Defining Indexes in SQL Server 2005 – clustered and non clustered indexex, The Query Optimizer, to create an index, To create a unique index, To create a clustered index, To create full-text indexes, To change index properties, To rename an index, to delete an index, To specify a fill factor for an index, To create an XML index and To delete XML Indexes.

When data volumes increase, organizations are faced with problems relating to data retrieval and posting. They feel the need for a mechanism that will increase the speed of data access. An index, like the index of a book, enables the database retrieve and present data to the end user with ease. An index can be defined as a mechanism for providing fast access to table rows and for enforcing constraints.

An index can be created by selecting one or more columns in a table that is being searched. It is a kind of ‘on disk’ structure associated with the table or view and contains keys that are built from one or more of the columns in the table or view. This structure known as B-Tree helps the SQL Server find the row or rows associated with the key values. Indexes can be created on computed columns or xml columns also.

Indexes can be clustered or non clustered. A clustered index stores data rows in the table based on their key values. Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexes have structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages. If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Clustered and Non clustered indexes can be unique and indexes are automatically maintained for a table or view whenever the data table is modified.

SQL Server 2005 permits users add non-key columns to leaf level of the non clustered index for by passing existing index key limits and to execute fully covered index queries.

When the primary key and unique constraints of a table column are defined an automatic index is created.

The Query Optimizer uses indexes to reduce disk I/O operations and use of system resources while querying on data. Queries which contain SELECT, UPDATE or DELETE statements require indexes for optimal performance. When a query is executed, each available method is evaluated for retrieving data and the most efficient one is selected by the Query optimizer. The methodology used may be table scans or index scans. In table scans I/O operations are many and resource intensive as all rows in a table are scanned to find the relevant ones. Index scans are used to search the index key columns to find the storage location of rows needed by the query and as the Index contains very few columns, the query executes faster.

SQL Server 2005 provides the user with a new Transact-SQL DDL statement for modifying relational and XML indexes. The CREATE INDEX statement is enhanced to support XML index syntax, partitioning and the included columns. A number of new index options have been added including the ONLINE option that allows for concurrent user access to underlying data during index operations.

To create an index

1. In Object Explorer, right-click the table for which you want to create an index and click Modify.

2. The table opens in Table Designer.

3. From the Table Designer menu, click Indexes/Keys.

4. In the Indexes/Keys dialog box, click Add.

5. Select the new index in the Selected Primary/Unique Key or Index list and set properties for the index in the grid to the right.

6. Specify any other settings for the index and click Close.

7. The index is created in the database when you save the table.

SQL Server allows users create unique indexes on unique columns such as the identity number of the employee or student or whatever is the unique key by which the component data are identified. A set of columns also can be used to create a unique index. The DBA can set the option of ignoring duplicate keys in a unique index if required. The default is No.

To create a unique index

  1. In Object Explorer, right-click the table and click Modify.
  2. The table opens in Table Designer.
  3. From the Table Designer menu, click Indexes/Keys.
  4. Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of the new index.

5. In the grid, click Type.

6. Choose Index from the drop-down list to the right of the property.

7. Under Column name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whether the index arranges values of this column in ascending or descending order.

8. In the grid, click Is Unique.

9. Choose Yes from the drop-down list to the right of the property.

10. Select the Ignore duplicate keys option if you want to ignore new or updated data that would create a duplicate key in the index (with the INSERT or UPDATE statement).

11. The index is created in the database when you save the table or diagram.

Please note that unique indexes cannot be created on a single column if the column contains NULL in more than one row. Similarly indexes cannot be created on multiple columns if the combination of the columns contains NULL in some rows. The NULL values are treated as duplicate values.

Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key values will be the same as the physical order of rows in the table. A table can have only one clustered index.

To create a clustered index

1. In Object Explorer, right-click the table for which you want to create a clustered index and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. In the Indexes/Keys dialog box, click Add.
5. Select the new index in the Selected Primary/Unique Key or Index list.
6. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

7. The index is created in the database when you save the table.

A full text index is used when a full text search is required to be performed on all the text based columns of the database. This index relies on a regular index which has to be created before a full text index is created. The regular index is created on a single, non null column. Usually a column with small values is selected for the indexation in a regular index. Often a Catalog is created using an external tool such as SQL Server Management Studio. Textual data from different text file formats are to be stored as image type files before Full text search can be done on the data.

To create full-text indexes

  1. In Object Explorer, right-click the table for which you want to create a full-text index and click Modify.
  2. The table opens in Table Designer.
  3. From the Table Designer menu, click Fulltext Index.

4. The Full-text Index dialog box opens. If the database is not enabled for full text indexing the dialog box will have the add button disabled. To enable full text indexing for the database, right click the database>Click properties and check the Full text indexing check box.

5. Then create a catalog by right clicking on Storage>Full Text Catalog and creating a new Catalog and entering the required information in the dialog box that opens.

6. Now open the Full Text Index property dialog box by clicking on it in the Table Designer menu.

7. Click Add.
8. Select the new index in the Selected Full-text Index list and set properties for the index in the grid to the right.
9. Your index is automatically saved in the database when you save your table in Table Designer. The index is available for modification as soon as you create it.

To change index properties

1. In Object Explorer, right-click the table you want to open and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. Change properties in the grid.
5. The changes are saved to the database when you save the table.

System defined names are assigned to indexes based on the database file name. If multiple indexes are created on a table the index names are incremented numerically with _1, _2 etc. An index can be renamed to be unique to a table. Since the automatically created index bears the same name as the primary key or unique constraint in a table, another index cannot be renamed later to match the primary key or unique constraint.

To rename an index

1. In Object Explorer, right-click the table with the index you want to rename and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. Select the index from the Selected Primary/Unique Key or Index list.
4. In the grid, click Name and type a new name into the text box.

5. The changes are saved to the database when you save the table.

Indexes can be deleted. Usually an index is considered for deletion when the performance of the INSERT,UPDATE and DELETE operations are hindered by the Index.

To delete an index

1. In Object Explorer, right-click the table with indexes you want to delete and click Modify.
2. From the Table Designer menu, click Indexes/Keys.
3. In the Indexes/Keys dialog box, select the index you want to delete.
4. Click Delete.
5. The index is deleted from the database when the table is saved.
6. A similar procedure can be followed for deleting a full text index by selecting Full text index from the Table Designer and selecting the index name and clicking delete button.

Microsoft SQL Server database uses a fill factor to specify how full each index page can be. The percentage of free space allotted to an index is defined as the fill factor. This is an important aspect of indexing as the amount of space to be filled by an index has to be determined by the DBA so that performance is not retarded.

To specify a fill factor for an index

1. In Object Explorer, right-click the table with an index for which you want to specify a fill factor and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. The Indexes/Keys dialog box opens.
5. Select the index in the Selected Primary/Unique Key or Index list.
6. In the Fill Factor box, type a number from 0 to 100. The value of 100 implies that the index will fill up completely and the storage space required will be minimal. This setting is recommended only for cases where data is unlikely to change. If data is likely to undergo addition and modification, it is better to set a lower value. Storage space required would be in proportion to the value set.

XML indexes cannot be created using the Index/Keys dialog box. One or more XML indexes can be created for xml data type columns on the basis of a primary xml index. Deleting the primary xml index will result in the deletion of all indexes created on the base of the primary index.

To create an XML index

1. In Object Explorer, right-click the table for which you want to create an XML index and click Modify. 2. The table opens in Table Designer.
3. Select the xml column for the index.
4. From the Table Designer menu, click XML Index.

5. In the XML Indexes dialog box, click Add.

<!–
microsoft_adunitid = “2077”;
microsoft_adunit_width = “300”;
microsoft_adunit_height = “250”;
microsoft_adunit_legacy = “false”;
//–>

6. Select the new index in the Selected XML Index list and set properties for the index in the grid to the right.

To delete XML Indexes

1. In Object Explorer, right-click the table with the XML index you want to delete and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click XML Index.
4. The XML Index dialog box opens.
5. Click the index you want to delete in the Selected XML Index column.
6. Click Delete.

Posted in SQL 2005 | Leave a Comment »

How to Delete Dups from Table

Posted by Tony on August 4, 2008

How to Identify and Delete Duplicate SQL Server Records

/*****************************

*****************
Example of a complex duplicate data delete script.
**********************************************/

/**********************************************
Set up test environment
**********************************************/
SET NOCOUNT ON

–Create test table
IF OBJECT_ID(‘tDupData’) IS NOT NULL
DROP TABLE tDupData
GO

CREATE TABLE tDupData
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

–Create test data
INSERT INTO tDupData VALUES (1,’CompanyOne’,’Address1′,’01/15/2003′)
INSERT INTO tDupData VALUES (2,’CompanyTwo’,’Address2′,’01/15/2003′)
INSERT INTO tDupData VALUES (3,’CompanyThree’,’Address3′,’01/15/2003′)
INSERT INTO tDupData VALUES (1,’CompanyOne’,’Address1′,’01/15/2003′)
— Simple Dup Data and complex dup data
INSERT INTO tDupData VALUES (2,’CompanyTwo’,’Address’,’01/16/2003′)
— complex dup data
INSERT INTO tDupData VALUES (3,’CompanyThree’,’Address’,’01/16/2003′)
— complex dup data
GO

/**********************************************
Finish set up
**********************************************/

/**********************************************
Complex duplicate data
**********************************************/

–Clean table out to include only one row per company
–Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

–Clean out simple duplicate data first
–Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress, dtmModified
HAVING COUNT(*) > 1

–Confirm number of dup rows
SELECT @@ROWCOUNT AS ‘Number of Duplicate Rows’

–Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified

–Insert the delete data back
INSERT INTO tDupData
SELECT * FROM #tempduplicatedata

–Check for dup data.
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress,dtmModified
HAVING COUNT(*) > 1

–Clean out temp table
TRUNCATE TABLE #tempduplicatedata

–Identify and save dup data into temp table
INSERT INTO #tempduplicatedata (lngCompanyID,strCompanyName)
SELECT lngCompanyID,strCompanyName FROM tDupData
GROUP BY lngCompanyID,strCompanyName
HAVING COUNT(*) > 1

–Confirm number of dup rows
SELECT @@ROWCOUNT AS ‘Number of Duplicate Rows’

–Update temp table to add strAddress and dtmModified
UPDATE #tempduplicatedata
SET strAddress = tDupData.strAddress
,dtmModified = tDupData.dtmModified
FROM #tempduplicatedata
INNER JOIN tDupData
ON #tempduplicatedata.lngCompanyID = tDupData.lngCompanyID
AND #tempduplicatedata.strCompanyName = tDupData.strCompanyName

–Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified

–Verify original table only has three rows of data
SELECT * FROM tDupData

–Drop temp table
DROP TABLE #tempduplicatedata

–drop test table
IF OBJECT_ID(‘tDupData’) IS NOT NULL
DROP TABLE tDupData
GO

This is a little more complicated than the simple duplicate data delete script, but easy to figure out once you see it. A word of caution here, you should investigate any child tables before you delete data from a table in order to prevent creating orphan rows. You can ether delete the data from the child tables first or update them to reflect the identity key value of the data row in the main table you are going to keep. The choice will be determined by your situation and any operating standards you may have.

While having to clean up duplicate data is not something you should have to do every day, the processes you learn from playing with these two scripts should give you a starting point the next time you find duplicate information in your database.

Posted in Uncategorized | Leave a Comment »

Tips on Rebuilding Table Indexes

Posted by Tony on August 4, 2008

Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. It will also update column statistics.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

–Script to automatically reindex all tables in a database

USE DatabaseName –Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.

For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don’t need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006

*****

When you create or rebuild an index, you can specify a fill factor, which is the amount the data pages in the index that are filled. A fill factor of 100 means that each index page is 100% full, a fill factor of 50% means each index page is 50% full. If you create a clustered index that has a fill factor of 100, and it is not based on a monotonically increasing key, that means that each time a record is inserted (or perhaps updated), page splits may occur because there is no room for the data in the existing pages. Numerous page splits can slow down SQL Server’s performance.

Here’s an example: Assume that you have just created a new index on a table with the default fill factor. When SQL Server creates the index, it places the index on contiguous physical pages, which allows optimal I/O access because the data can be read sequentially. But as the table grows and changes with INSERTS, UPDATES, and DELETES, page splitting occurs. When pages split, SQL Server must allocate new pages elsewhere on the disk, and these new pages are not contiguous with the original physical pages. Because of this, random I/O, not sequential I/O access must be used to gather the data, which is much slower, to access the index pages.

So what is the ideal fill factor? It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:

  • Low Update Tables (100-1 read to write ratio): 100% fill factor
  • High Update Tables (where writes exceed reads): 50%-70% fill factor
  • Everything In-Between: 80%-90% fill factor.

You may have to experiment to find the optimum fill factor for your particular application. Don’t assume that a low fill factor is always better than a high fill factor. While page splits will be reduced with a low fill factor, it also increases the number of pages that have to be read by SQL Server during queries, which reduces performance. And not only is I/O overhead increased with a too low of fill factor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page (including empty space) is moved to the buffer. So the lower the fill factor, the more pages that have to be moved into SQL Serve’s buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance.

If you don’t specify a fill factor, the default fill factor is 0, which means the same as a 100% fill factor, (the leaf pages of the index are filled 100%, but there is some room left on intermediate index pages). In most cases, this default value is not a good choice, especially for clustered indexes. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

If you find that your transaction log grows to an unacceptable size when you run DBCC REINDEX, you can minimize this growth by switching from the Full Recovery mode to the Bulk-Logged mode before you reindex, and when done, switch back. This will significantly reduce the size of the transaction log growth. [2000, 2005] Updated 7-24-2006

*****

If you have a table that has a clustered index on a monotonically increasing or decreasing primary key, and if the table is not subject in UPDATEs or if it has no VARCHAR columns, then the ideal fill factor for the table is 100. This is because such a table will normally not experience any page splits. Because of this, there is no point in leaving any room in the index for page splits. And because the fill factor is 100, SQL Server will require fewer I/Os to read the data in the table, and performance will be boosted. [7.0, 2000, 2005] Updated 7-24-2006

*****

If you are not sure what to make the fill factor for your indexes, your first step is to determine the ratio of disk writes to reads. The way to do this is to use these two counters: Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time. When you run both counters on an array, you should get a good feel for what percentage of your I/O are reads and writes. You will want to run this over a period of time representative of your typical server load. If your percentage writes greatly exceeds the percentage of reads, then a lower fill factor is called for. If your percentage of reads greatly exceeds the percentage of writes, then a higher fill factor is called for.

Another Performance Monitor counter you can use to help you select the ideal fill factor for your environment is the SQL Server Access Methods: Pages Splits/Sec. This counter measures the number of page splits that are occurring in SQL Server every second. For best performance, you will want this counter to be as low as possible, as page splits incur extra server overhead, hurting performance. If this number is relatively high, then you may need to lower the fill factor in order to prevent new page splits. If this counter is very low, then the fill factor you have is fine, or it could be a little too low. You won’t know unless you increase the fill factor and watch the results.

Ideally, you want a fill factor that prevents excessive page splits, but not so low as to increase the size of the database, which in turn can reduce read performance because of all the extra data pages that need to be read.

Once you know the ratio of disk write to reads, you now have the information you need to help you determine an optimum fill factor for your indexes. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

If you want to determine the level of fragmentation of your indexes due to page splitting, you can run the DBCC SHOWCONTIG command. Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script:

–Script to identify table fragmentation

–Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

–Set the table and index to be examined
SELECT @IndexName = ‘index_name’ –enter name of index
SET @ID = OBJECT_ID(‘table_name’) –enter name of table

–Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

–Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

While the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database, and also increase the fill factor if you are finding that the current fill factor you are using is not appropriate. [6.5, 7.0, 2000] Updated 7-24-2006

*****

Here’s a script that is used to create DBCC SHOWCONFIG commands for all of the indexes in one or more tables. Once you run this script, it will produce for you a DBCC SHOWCONFIG statement for each index, which you can then run to find out about the level of fragmentation of your indexes. This script is especially handy if you don’t know the names of the indexes in your tables (which is most of the time).

SELECT ‘dbcc showcontig (‘ +
CONVERT(varchar(20),i.id) + ‘,’ + — table id
CONVERT(varchar(20),i.indid) + ‘) — ‘ + — index id
object_name(i.id) + ‘.’ + — table name
i.name — index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = ‘U’
and i.indid < 2
and
i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid

Once you run this script, the output will be DBCC SHOWCONFIG statements for each of the tables(s) and index(es). This output can then be cut and pasted into Query Analyzer or Management Studio and run, which produces a DBCC SHOWCONFIG result for every index for every table you specified.

Posted in SQL 2005 | Leave a Comment »

Convert byte array to an Image

Posted by Tony on August 2, 2008

Dim con As New
SqlConnection(“Server=yileiw2;uid=sqlauth;pwd=sqla uth;database=pubs”)
Dim da As New SqlDataAdapter(“Select * From pub_info”, con)
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()

con.Open()
da.Fill(ds, “Pub_info”)
Dim myRow As DataRow
myRow = ds.Tables(“Pub_info”).Rows(0)

Dim MyData() As Byte
MyData = myRow(“logo”)

Response.Buffer = True
Response.ContentType = “Image/JPEG”
Response.BinaryWrite(MyData)

MyCB = Nothing
ds = Nothing
da = Nothing

con.Close()
con = Nothing

Posted in Uncategorized | Tagged: | Leave a Comment »

When do we use the UPDATE_STATISTICS command?

Posted by Tony on August 2, 2008

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

The following stored procedure can be used to run the “Update_Statistic” command on all the tables in your database:

==============

create procedure Update_Statistics
as

set nocount on

declare @tablename varchar(150)

declare table_name cursor for
select ‘UPDATE STATISTICS ‘ + name from sysobjects where type = ‘u’
order by name

open table_name

fetch next from table_name
into @tablename

WHILE @@FETCH_STATUS = 0

BEGIN
exec (@tablename)
print @tablename
FETCH NEXT FROM table_name INTO @tablename
END
print ”
print ‘All user table statistics have been updated’

CLOSE table_name
DEALLOCATE table_name

Posted in SQL 2005 | Leave a Comment »

How to Debug Stored Procedure from VS 2008?

Posted by Tony on August 1, 2008

Developers are an inherently finicky group. Most have their favorite tools to complete everyday tasks. This includes text editors as well as IDE’s and even operating systems. While I have favorite tools as well, I am surprised at how little developers know about some of the tools they use everyday. One tool can often replace the functionality provided by numerous smaller programs. A good example is VS.NET (Visual Studio .NET). It provides the ability to develop .NET-based code via languages like C# and VB.NET, but it also facilitates the development of CSS (Cascaded Style Sheets), HTML, XML, and SQL. SQL seems to be the most often overlooked feature, so let’s take a closer look at using VS.NET to work with SQL.

Database connectivity
VS.NET’s Server Explorer feature makes it easy to connect to a database server. While this connection is not restricted to SQL Server, it will be the focus of this article. Figure A shows the Server Explorer on the left side of the VS.NET workspace. It is accessible from the View drop-down menu or the tab on the left edge of the workspace. Right-clicking on the Data Connections node in the Server Explorer provides the option to add a new connection. Figure B shows the dialog presented when this option is selected. The Test Connection button in Figure B allows the connection to be easily tested and verified to be correct.

Figure A
Visual Studio .NET Server Explorer
Figure B
Adding a new server via Server Explorer

Once a connection has been added via Server Explorer, it is available within the Servers node in Server Explorer. The server name appears with its options available under it. The SQL Servers node under the server provides access to the SQL Server and its elements. You are now one step away from debugging SQL code, but additional setup is necessary.

SQL Server setup
SQL Server contains an extended stored procedure that is used to control access to debugging its stored procedures. This stored procedure is named sp_sdidebug, and the following T-SQL may be used to allow a user to debug stored procedures:
USE Master
GRANT EXECUTE
ON sp_sdidebug
TO username

In addition, the user must have rights to the both the database and stored procedure. Once the user has been properly set up, the actual debug process is as simple as right-clicking on the stored procedure within VS.NET’s Server Explorer and selecting Step Into Stored Procedure. Figure C shows this option with an example stored procedure from the Northwind database highlighted. The stored procedure returns the top ten most expensive products from the database.

Figure C
Debugging a stored procedure via Server Explorer

Once the debugging process begins, it is no different than debugging C# or VB.NET code. Figure D shows the previous stored procedure running in debug mode. This option is available via the Debug drop-down menu or with the keyboard shortcut. Here is an overview of the debug-related keyboard shortcuts:

  • F5: Continue
  • Shift + F5: Stop debugging
  • F11: Step into
  • F10: Step over
  • Shift + F11: Step out
Figure D
Stored procedure in debug mode

In addition, breakpoints and watches may be set to monitor the stored procedure in action. The output of the process is available via the lower window within VS.NET (once it has finished running). Figure E shows the VS.NET workspace after running a stored procedure with debug information visible. It says the stored procedure ran and finished with an exit code of zero, which means success.

Figure E
Debug information after running stored procedure

The output may be viewed by selecting Database Output from the drop-down list above the debug window as shown in Figure F. The actual output is visible in Figure F as well.

Figure F
Viewing stored procedure output

Parameters
The stored procedure used up to this point is very simple but not appropriate in the real world. Most real-world stored procedures accept and possibly return parameters. Utilizing these procedures within the debugger is simple; it allows you to add parameter values to see how the code may perform. When a stored procedure that accepts input parameters is debugged, a window is presented to accept the values.

Figure G shows the dialog box that accepts the input parameters. I selected a stored procedure (Sales by Year) that accepts a beginning and ending date value to return sales data. In addition to the dates you may enter, NULL and DEFAULT options may be selected. This allows you to test error handling (with NULL values) and the default settings.

Figure G
Debugging with parameters

A central location
Using VS.NET to develop, maintain, and debug stored procedures makes other programs such as SQL Server’s Query Analyzer unnecessary. This leads to a more focused developer that doesn’t have to switch applications and mindsets during their day. Debugging is a critical aspect of development, so applying a strong developing tool to T-SQL code makes it easier to build robust applications

Posted in Debugging | 1 Comment »

How to remove duplicate rows from a table in SQL Server

Posted by Tony on August 1, 2008

Here is what you need to do:

1. First, run the above GROUP BY query to determine how many sets of duplicate PK values exist, and the count of duplicates for each set.
2. Select the duplicate key values into a holding table. For example:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
3. Select the duplicate rows into a holding table, eliminating duplicates in the process. For example:

SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
4. At this point, the holddups table should have unique PKs, however, this will not be the case if t1 had duplicate PKs, yet unique rows (as in the SSN example above). Verify that each key in holddups is unique, and that you do not have duplicate keys, yet unique rows. If so, you must stop here and reconcile which of the rows you wish to keep for a given duplicate key value. For example, the query:

SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

should return a count of 1 for each row. If yes, proceed to step 5 below. If no, you have duplicate keys, yet unique rows, and need to decide which rows to save. This will usually entail either discarding a row, or creating a new unique key value for this row. Take one of these two steps for each such duplicate PK in the holddups table.

5. Delete the duplicate rows from the original table. For example:

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
6. Put the unique rows back in the original table. For example:

INSERT t1 SELECT * FROM holddups

Posted in SQL 2005 | Leave a Comment »

How to Send Email from SQL 2005

Posted by Tony on August 1, 2008

Use the “xp_smtp_sendmail” system procedure.  Here is a sample:

EXEC master..xp_smtp_sendmail

@TO = ‘tony@alorica.com‘,

@BCC = ‘tony@alorica.com‘,

@from = ‘someone@somewhere.com‘,

@subject = ‘Happy Birthday to you!’,

@message = ‘<B>You belong in a zoo!</b>’,

@server = ‘yourMailServerName’,

@type       = ‘text/html’

Posted in SQL 2005 | Leave a Comment »

Query Table Structure Methods

Posted by Tony on August 1, 2008

Method 1: Query the System tables as follows:

SELECT syscolumns.name AS ColumnName, systypes.name AS Datatype

FROM sysobjects, syscolumns, systypes

WHERE sysobjects.id = syscolumns.id AND

syscolumns.xtype = systypes.xtype AND

sysobjects.name = ‘ServiceRequests’

Method 2: Use the SP_help command

sp_help ServiceRequests

Posted in SQL 2005 | Leave a Comment »