Finally we replaced our Test server.
A previous post outlined part of the discussion involved and the seriously twisted suggestion that a simple file copy of the entire data directory would be all that was required to bring the DBMS back up to its original state.
Read more →
After the introduction of our Booking table and its overlap constraint, all is going well when the rules are suddenly shifted.
The requirement now is that each resource (Room) has a certain availability and can only be booked during those times.
Read more →
It looked like a normal day. The cars where smoking, the birds where coughing and the daily caffeine war had already begun. "Coffee making is an art" read the sign on the footpath.
Read more →
I recently came across a comment on a web page that said something to the effect of.. "I don't use views because of temporary tables, table variables and UDF's."
Well tickle me pink.
Read more →
A common requirement from users is the ability to be able to search on the columns they want. In affect they would like to roll there own queries. As an example take the Employees table in Northwind.
Read more →
Due to the excessive amount of BCP work that has plagued me recently, I was sick and tired of typing.Instead of writing a script in TSQL to build BCP commands, I decided on building a little GUI app.
Read more →
While moving some data between servers via BCP from a database that I have never seen before, I noticed that several of the tables failed to load into the target server.
Read more →
A future requirement of 3 applications we support, is that they will be competing for a resource that they all share. In this case it is a series of Rooms for appointments.
Read more →
Doing some documentation today and it came to triggers.
SQL Server has "sp_helptrigger" but it must be passed in a table name. I looked around on the web and have found a few bits of code (a couple of UDF's and some basic ones) so I thought i would create one based on the original and add it to master for reuse in any db.
Read more →
CREATE DATABASE
No, not the fact that anyone can create a database but the "advanced" options. The amount of times I have seen this statement butchered is enough to warrant a post.
Read more →
We are replacing our test SQL Server box with a nice shiny new one with lots of goodies..
The discussion commences with the script to restore or attach the databases back.
Read more →
This happened a while ago but I thought I would share…
I am not a huge fan of Identity columns because (among others) they promote laziness in the design phase (or should that be daze!
Read more →
First of the bat I'd like to apologies to Ryan Rinaldi for my assumption about his intelligence based on one comment of his in my blog 4 Developers and a DBA.
Read more →
Every developer has a copy of SQL Server on there desktop for prototyping…
DBA: What can I do for you guys? DEVELOPERS: We've created a couple of tables and we are getting duplicates DBA: Why don't you have a key?
Read more →
It is very obvious that a lot of people have absolutely no idea what a business model is or for that matter a database. The number of times I have seen business rule code that should be imbedded in the database definition and not in the BLL (Business Logic Layer) just makes me want to scream.
Read more →
The following is an example of the methods we have outlined in the previous posts.
Before we can use the database, we have to a change the data to ensure no NULLs are returned.
Read more →
In the Introduction, I pointed out that the DataTable has a method which is similar to the WHERE clause in TSQL. This method (Select) returns an array of DataRow's that meet the criteria of your expression.
Read more →
This TCLOSE method has no equivalent in TSQL
TCLOSE stands for Transitive Closure and is used to solve hierarchy representations, most notably the part explosion problem.
The algorithm used here is the Semi naive method as outlined in "An Introduction to Database Systems 7th Edition.
Read more →
This DIVIDE method has no equivalent in TSQL.
Essential the DIVIDE operator is the inverse of Product (or CROSS JOIN in SQL Server speak). ie: A PRODUCT B DIVIDE B = A
Read more →
This INTERSECT method has no equivalent in TSQL
INTERSECT is simply all rows that are in the First table and the Second table We can take a coding short cut and simple use our JOIN method.
Read more →