|
Making Choices Through Analysis |
|
|
By host on
4/30/2008 10:11 AM
|
|
|
|
As we work with other Professionals in our industry, we quickly learn that there are many ways to accomplish a given task. When presented with multiple options in a situation, there are times where one option stands out as the optimal choice based upon our general knowledge of the database engine. There are also times where the optimal choice may not be so obvious and will vary depending on many considerations such as database architecture, level of use, indexing, hardware configuration or general best practices. Any given approach may perform superbly with one database and drag on another.
A sample case might be that we are given two versions of a seemingly simple query that filters the data. One option is the common use of a WHERE clause. The other is utilizing the filters within the JOIN clause. The examples below illustrate the syntax differences between these options.
WHERE Filter Option< ...
|
 |
|
|
More...
|
|
|
IndyCodeCamp: Indy Rocks!! |
|
|
By host on
4/26/2008 11:27 AM
|
|
|
On April 26th, I presented a session at IndyCodeCamp with the title of “A Study of Dynamic SQL”. For source code used in this presentation, go to the Downloads page of this site. Majority of those in attendance at the camp were primarily .NET Developers rather than SQL Developers which was ideal since it fit my target audience for my session.
IndyCodeCamp was a great opportunity to learn and share with 24 sessions from “Office 2007 Development with VS 2008" (presented by Bill Steele) to “Silverlight – Your LINQ to Better Experience” (presented by Chad Campbell) to “ASP.NET Performance and Optimization” (presented by Jeff McWherter) all at a very ...
|
|
|
|
|
More...
|
|
|
Common Table Expressions |
|
|
By host on
4/21/2008 6:42 AM
|
|
|
|
In my previous blog entries I have spoken about temporary tables, table variables and derived tables when building stored procedures. All of these are useful features of SQL Server. When SQL Server 2005 was released another feature was added that joins in this happy family of temporary datasets: Common Table Expressions (CTE).
The syntax of a CTE is:
WITH [CTE Name] ([Columns To Return]) AS ([SQL Statement);
A sample of this syntax is:
USE [AdventureWorks];
WITH myCTE (
[AddressID],
&a ...
|
|
|
|
|
More...
|
|
|
Pretty Sneaky SSIS |
|
|
By host on
4/14/2008 6:46 AM
|
|
|
I have developed a plethora of data transformation services (DTS) packages in the past. I find DTS packages to be very powerful and handy for building scheduled interfaces between data sources. Despite working with SQL Server 2005 since its introduction I had not had the opportunity to dabble with SQL Server Integration Services (SSIS) until very recently.
The task set before was to transform data from a SQL Server 2000 database into a fixed length multi segmented flat file. The unique characteristics of a multi-segmented file are that a single record is represented with multiple rows of data within the flat file. Not all records may have the same number of rows. Below is a brief example of a multi-segmented flat file containing a header row and a single data record (all data in the file is fictional):
29800000000000000000000000SB00001BKH00000002712007011420070215
2980000100000000001234SB00000001BOX0000000300001000044H120040412200404120
|
|
|
|
|
More...
|
|
|
A Thought on Data Confidentiality |
|
|
By host on
4/6/2008 9:35 PM
|
|
|
Data confidentiality is a key aspect in database security. Data integrity and Data availability are also important aspects; but the consequences of sensitive data being disclosed have broad consequences. Discussions regarding data confidentiality often focus on unauthorized persons who compromise other security efforts or impersonate a valid user; but, the highest probability of disclosure of sensitive data occurs with the person who sits within the office and has authorized access to the data.
No one wants to think that the person sitting in the next cubicle is an identity thief. The purposes of using the sensitive information may not even reach the level of identity theft; but that information can be used to gain some knowledge about their neighbor that is passed around the rumor mill. Another situation could even be that your fellow cubicle dweller is gathering information about a local celebrity that is sold to the press. (
|
|
|
More...
|
|
|
Heroes Happen In Indianapolis |
|
|
By host on
4/4/2008 6:13 AM
|
|
|
I attended the Microsoft Launch Event in Indianapolis this Thursday (April 3). At this launch event, information regarding the new release of Visual Studio 2008 and the upcoming Windows 2008 and SQL Server 2008 products was evangelized. This event was organized in several tracks and I registered for the IT Pro - Data Platform track. This track focused on features of SQL Server 2008.
The session that I attended within this track was “Simplify Management of Data Structure”. This session presented the policy management features as well as the multiple server management features of SQL Server 2008. These features are great enhancements that make the DBA life much easier and able to control items such as naming conventions of objects (which was demonstrated in this session). The ability to query multiple servers, assuming the schema is the same or at least the tables involved are the same) with a s ...
|
|
|
|
|
More...
|
|
|
Identity Theft and Databases |
|
|
By host on
3/31/2008 6:41 AM
|
|
|
I recently attended a presentation on the subject of identity theft and what steps can be taken to protect yourself. This presentation was primarily from the non-technical side of the issue discussing aspects such as dealing with junk mail and documents that contain sensitive information, careful consideration of disclosure of personal information as well as what to do if you find yourself a victim of this crime.
The information was presented by an Indiana State Police Officer who often works cases of identity theft. It was interesting to hear about how the mind of an identity thief works and I was surprised to learn how organized and aggressive they are.
The session caused me to think about my own habits and to make some efforts to modify how I handle sensitive data about myself and my family. Also, I thought a lot about my threat modeling recommendations and how this practice becomes even more ...
|
|
|
|
|
More...
|
|
|
Best Practice: Commenting Stored Procedures |
|
|
By host on
3/26/2008 6:44 AM
|
|
|
|
It might be a stretch; but I might be willing to bet that a review of most T-SQL code would reveal that commenting code is a practice that is inconsistently executed. This may be especially true in an environment that has many developers and DBAs maintaining the same database.
Here are some suggestions of commenting stored procedure code:
- Notation of a stored procedure’s intent and creation information.
- Notation of a stored procedure’s change log.
- Notation of specific modifications to code.
- Description of a block of code.
Below is a sample of a well commented stored procedure:
/*
****************************************************************************************
Stored Proc: dbo.MyTable_Update
Created By: Bobby Flay (bflay@email.com)
Created Date: 1/26/200 ...
|
|
|
|
|
More...
|
|
|
A Plug for Indy Code Camp |
|
|
By host on
3/19/2008 10:37 PM
|
|
|
On Saturday, April 26th, the IndyNDA (Indianapolis .NET User Group) is hosting a code camp at the Gene B. Glick Junior Achievement building. This code camp, aptly named "Indy Code Camp" is a great opportunity to dive into specific coding topics.
I will be presenting a session titled "A Study on Dynamic SQL" which will present the affects of utilizing dynamic and inline SQL and offer alternatives that will make the .NET Developer and DBA happy. I look forward to this opportunity.
Events such as these are critical to a healthy developer community and I am honored to be participating as a presenter. Hats off to Aaron Lerch and his team for getting this event off the ground.
To register for this event go to the following URL: indycodecamp.com
|
|
|
|
|
More...
|
|
|
Table Variables Revisited |
|
|
By host on
3/15/2008 12:27 AM
|
|
|
|
One of the great things about being a database professional is that learning is constant.
A few days ago I blogged about temporary tables and table variables and within that blog I noted that one benefit of table variables was that they resided in memory where temporary tables resided in TempDB database. A great friend of mine pointed out to me that while that is true at a basic level, it is not wholly true. When the table variable exceeds memory limits it is written to the TempDB database.
This was a very excellent point. In order to test this concept, I used the fn_virtualfilestats in SQL Server Management Studio against the AdventureWorks database. (Refer to the following link for the details regarding the syntax of this function: http://msdn2.microsoft.com/en-us/library/ms187309.aspx)
I execut ...
|
|
|
|
|
More...
|
|