February 2004 Blog Posts

  • The Missing System Procedure..

    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. The code is based on the select statement in "sp_helptrigger" with 2 extra column indicating table name and the triggers status. I'm hoping the next release of SQL will fix this... Does anyone on the beta know? It's only tested...

  • The most abused statement in TSQL

    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. Do you have a database this is over 250Mb in size that has 1 data file? My "best practice" for this is that only system tables (and associated objects.. SP, views, triggers etc..) should reside on the PRIMARY file group. You should, as a minimum have 2 filegroups, and in most cases three. Forget the log filegroup for the moment as I am only concerned with data. As a in house standard, we...

  • You're going do to what!?...hmm that might just work...

    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.. DBA 1: We don't need to do any of that! DBA Others: What? DBA 1: We'll do a default install, stop the service, copy the data (including master) and log folders over the top of the default directories and just start the service back up. DBA Others:....(wild screaming, dancing and all round mayhem)..... DBA 1: Have you all finished? DBA Others:....(wild screaming, dancing and all round mayhem)..... DBA 1: Have you all finished yet? DBA Others: For the...

  • Threads, composite keys and Identity to the rescue

    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!) However, we found that in one particular case, it was a god send.. We have a touch screen application that is used through out the country and to track its usage (and hence give us feedback on interface use, performance etc...) we setup a table that records what machine is in use and what page is being viewed. It's a Windows Form App (C#) with a front-end...

  • "Look up" tables, domains and a apology.

    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. As always, Lavos, has provided the karma check that I needed so I decided to visit his blog. He seems like an intelligent guy, and to assume otherwise based on one comment is rude. So once again sorry Ryan. The relational model is surprisingly simple. In fact, so easy, I taught my wife the basics in about 30 minutes. It went something like this... A type is set of values, and...

  • 4 Developers and a DBA or "Just a couple of tables"

    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? DEVELOPERS: We do! DBA: No you don't DEVELOPERS: Yes we do, this column here is an identity column with a primary key constraint. DBA:................(twiddles thumbs)............ DEVELOPERS: Ohh, I see what you are saying..its not a key at all. A back and forth takes place discussing business requirements and FD's. DBA: So, given all our rules, these 4 columns combine to give us the simpliest key. DEVELOPERS: Yes, but won't that...

  • The database is your BLL

    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. As soon as data rules (the backbone of a business model) are moved away from a relational style declarative constraint, you have reduced the effectiveness, meaning and integrity of your business model/database. So called "architects" and indeed "best practice solutions" proscribe to the notion of a...

  • DataTable Relational Operators in C# - The Northwind Example

    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. Specifically, the ReportsTo column in Employees contains a NULL for "no parent". We will change it to be the same as the EmployeeID Personally I think it is a terrible design decision to use a NULL here. Consider the proposition of each design. Employee X reports to ..... VS Employee X reports to himself. So.. here is the TSQL to do that... UPDATE Employees SET ReportsTo = EmployeeID WHERE ReportsTo IS NULL When I told...