|
Honeycombing Revisted |
|
|
By host on
6/30/2008 8:25 AM
|
|
|
|
On June 2nd, I wrote a blog entry on the subject of honeycombing a database. In that entry, I discussed the concept of honeycombing and provided an option that could be executed for SQL Server 2000 or 2005 databases.
The challenge with SQL Server 2000 and 2005 databases is that there is not a great method in which to capture information about a SELECT event. Other DML events (UPDATE, INSERT and DELETE) could be captured with a trigger; but unfortunately SELECT was mysteriously missing as an event in which a trigger could respond. The option provided was admittedly a rigged solution that should be very carefully explored before implementing.
I also mentioned that I had not yet explored the features of SQL Server 2008 at that time to see if there was a solution. Since then, I spent some time with
 |
|
|
More...
|
|
|
Considering the Confidentiality of Date of Birth |
|
|
By host on
6/22/2008 7:54 PM
|
|
|
In the world of managing data there are some pieces of data that obviously demand special attention when considering confidentiality like social security numbers, credit card numbers and driver license numbers. There are also others that fall into that gray area that are either debated by those who must identify confidential data or not even considered.
The date of birth is one of those data elements that reside in that gray area. Alone, this element of data contains no real value that would raise any concerns of confidentiality. As many say who do not want special attention on their special day say: “It is just another day.” When combined with other elements of data such as full name and address the date of birth becomes very confidential.
The date of birth is something that is commonly used in combination with other data as confirmation of identity. With a name, address and date of birth a person can obtain a certified copy of a birth certificate. When a certified ...
|
|
|
|
|
More...
|
|
|
Finding Similar Columns In A Haystack |
|
|
By host on
6/15/2008 10:27 PM
|
|
|
|
Large enterprise databases will often have columns that are repeated in many tables throughout the schema. These columns, while containing unique data, may have identical properties. Maintenance of these columns can be challenging, especially if you are not the one who is as intimately familiar with the database schema as the original architect might be.
The brief case study below illuminates how this can be challenging:
Joe Deebeay maintains an enterprise database that contains over 200 tables. Joe’s manager has stated that the user interface of the application will begin to pass all of the zip codes with ten characters.
In the original design of this database’s schema, the original architect, who was not Joe, determined that the data type of the zip code fields should be varchar(5). This size accommodates the standard zip code in the format of “46204”. The new format will require Joe to increase the column’s size to varchar(10) which accommodates t ...
|
|
|
|
|
More...
|
|
|
Considerations for VARCHAR and NVARCHAR |
|
|
By host on
6/6/2008 11:47 AM
|
|
|
If you peruse quotes and philosophies of very successful people it will not take long to see that revisiting the basics of a discipline is something that is critical to excelling; thus, I thought that it would be a good idea to comment on the varchar and nvarchar data types.
According to SQL Server 2005 Books Online the varchar(n) data type is described as:
“Variable-length, non-Unicode character data. n can be a value from 1 through 8,000.”
According to SQL Server 2005 Books Online the nvarchar(n) data type is described as:
“Variable-length Unicode character data. n can be a value from 1 through 4,000.”
The “n” referred to above is the defined ...
|
|
|
|
|
More...
|
|
|
Honeycombing the Database |
|
|
By host on
6/2/2008 8:12 PM
|
|
|
|
Over the past few months I have been reading a book of the title “Cryptography in the Database: The Last Line of Defense” by Kevin Kenan. I would certainly recommend this book; although I have yet to complete reading it in its entirety. With all of the other things that are in mid-air in this juggling act that we call living I have found that my reading has turned into multiple short sprints rather than the long marathons of days past.
In this book a concept called “honeycombing” is very briefly introduced. This concept is the placement of tables within a database that contains seemingly highly confidential data. These tables have no purpose other than fulfilling the role of a decoy. When a data snooper happens upon these tables and performs a ...
|
|
|
|
|
More...
|
|
|
Joining Table-Valued Functions |
|
|
By host on
5/29/2008 8:28 AM
|
|
|
|
In a relational database the use of JOIN is invaluable when combining data in one table to another. Through stored procedures the ability to use JOIN to combine physical tables to views, temporary tables, derived tables and table variables is equally important in our realm of data retrieval activities.
In SQL Server 2000, the table-valued function was introduced. The table-valued function provides a user defined function the ability to return a table object. The ability to combine the results of a table-valued function has been available through the use of JOIN; but presented some challenges when the argument that is to be passed is a value that changes for each record of the parent table. The following statement will result in a syntax error:
SELECT
&nbs ...
|
|
|
|
|
More...
|
|
|
The Hidden Aspects of ISNUMERIC |
|
|
By host on
5/19/2008 8:53 PM
|
|
|
SQL Server 2005 Books Online describes the ISNUMERIC function as: “ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 indicates that expression can be converted to at least one of the numeric types.”
To illustrate this, the following statement will return the value of 1 (true):
SELECT ISNUMERIC('486')
While the following statement will return the value of 0 (false):
SELECT ISNUMERIC('ABC')
There are some additional characters that return a positive response to the function that are not regularly considered numeric such as the dollar sign (“$”), the comma (“,”) and the period (“.”). This occurs since they can be converted into other numeric data types. For example, the following statement ...
|
|
|
|
|
More...
|
|
|
Thoughts on Writing |
|
|
By host on
5/11/2008 8:51 PM
|
|
|
Last week, Steve Jones editor of SQL Server Central, wrote an editorial on the subject of writing. That subject was the catalyst for numerous responses in the associated forum topic – including one from yours truly. The responses were very interesting. Some responses spoke about how the irony of life has turned adolescent anti-writers into adult professional writers. Others lamented about wanting to write but not having the confidence to put finger to key for the purpose of prose. Others also stated that other priorities did not leave enough time in the day to apply their writing skills. Finally, there were those who squelched the pen in fear of peer criticism.
This great spurt of ...
|
|
|
|
|
More...
|
|
|
A Wrinkle in Time is Revealed |
|
|
By host on
5/5/2008 9:08 PM
|
|
|
|
If date and time precision is important to you, an odd experience may be waiting for your discovery. No, it is not a wrinkle in time it is the rounding of milliseconds in the datetime data type.
According to SQL Server 2000 Books On Line (BOL), the DateTime data type is described as follows:
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
Below is a series of data entries into a table that illustrate the affect of the millisecond rounding. The value on the left is the date and time value captured in a varchar data type which reflects the actual value that was submitted to the database. The value on the right is the date and time value captured in a datetime datatype that reflects the millisecond rounding that is described by BOL.< ...
|
|
|
|
|
More...
|
|
|