Mark Mintoff My superpower is common sense


T-SQL IsNullOrEmpty

Lately (because of my line of work) I am using SQL Server more than I am using Visual Studio. I miss some of the functions provided in C# however.

One of them is String.IsNullOrEmpty and SQL Server does not natively have it.

Dead simple, but a comfort in long CASE statements:

Usage is simple and according to expectations:

Unfortunately I do not know of a way to avoid the "= 1" as I do not think boolean/bit values are handled the same in SQL.


VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Simple Way To Check For Rows

I would like to share a very simple technique for checking for rows in your table. Before developing this, I used to make the mistake of doing a SELECT COUNT(1) script.

On tables with a large number of rows (millions), this proved to be inefficient (even with indexing). So instead, I developed this efficient way to check for rows, which makes a difference:

It's very simple. All it does is select the top one row from your table, based on your condition in a subquery (X). Then select count on that subquery and convert the result to a bit. The result will always be 1 or 0, which translates to True and False respectively!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

T-SQL Batch Delete

There are times when you need to delete an arbitrarily large subset of data from a database table. For the sake of example, let us suppose we will be deleting 100,000,000 rows. A simple delete statement:

will take a very long time (hours) to process as 100,000,000 rows will be deleted within a single transaction.

For this purpose, when I need to delete a large subset of data, I prefer to work with a while loop delete and multiple transactions:

This results in a much faster deletion of data as each transaction will only need to delete 1000 rows at a time. An additional benefit is presented if you need to stop the deletion at any point; for example you have locked the table and somebody needs to select some data. There are no long rollbacks and you do not lose progress on what you have deleted.

Hence, to make things easier:



VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)

Monitor Running Processes using T-SQL

At times, monitoring processes which are running on your database is necessary; particularly if you suspect that a particular script is taking too long, or clogging everything up. You might have to kill one or two processes in order to ensure that everything keeps running smoothly. In order to monitor my processes and ensure that everything is well, I put together the following script:

This produces an interesting little 'dashboard', allowing you to identify problematic scripts. Hope this serves you well.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Drop Procedures using T-SQL

Quite a specific thing to do, but here's how you can drop all your stored procedures in one go using T-SQL. Simply put, all you need to do is run a cursor on a select from the information schema and create an EXEC script.

For this example, I left everything as a PRINT statement in order to minimize the "Oops I destroyed the database" en-masse factor. Leave the PRINT if you want to see what it puts together. Uncomment the EXEC to run it. If you went ahead and ran the script above with EXEC and feel you ruined your life, you might as well drop all your tables. Here, I'll save you some time:

Happy coding and I hope you don't ruin a database.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)

Getting Column Information using T-SQL

Thought I would share a simple T-SQL query in order to get column information about a table using T-SQL. I recently needed a "dump" of specific tables in order to copy paste the column name and data type into a small application I built to auto-generate my stored procedures. This simple script will query the information schema giving you the details:

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tagged as: , , , No Comments

Simplified DB Design

I have an unorthodox attitude when it comes to databases and database design. Many people prefer to create complex stored procedures, consisting of joins, not ins, wheres and nested select statements. Unsurprisingly, I have a very simplistic approach. Here is my attitude, abbreviated in point form:
  • A database is simply a storage space for information
  • A database must be given data to store
  • A database must retrieve stored data
Anything beyond that, in my opinion, should be achieved through the application connecting to the database. I hold this attitude because I cache the information in the form of objects in static Dictionaries. Anything complex (such as a join or a where statement) is achieved with LINQ (and even Dynamic LINQ) through C# programming. Hence, my take on stored procedures is also very simplified. My preference is to use SQL-Server, and to this end I have made use of Schemas. Schemas are a very nice way of applying what looks like a .Net Namespace to your stored procedures, in addition to allowing security options. I make use of three schemas:
  • Get - Concerned with retrieval of information
  • Set - Concerned with insertions / updates of information
  • Del - Concerned with deletion of information (or rather setting a boolean named 'Deleted' to 'true')
The Get schema typically contains 2 stored procedures per table; GetOne, GetAll. So if I have a table named "MyFile", the stored procedures would be named:
  • Get.MyFile
  • Get.MyFiles
And as you can imagine, they are very simple! and The Set schema typically contains 1 stored procedure per table; SetOne. So for my imaginary table "MyFile", the stored procedure would be named:
  • Set.MyFile
This stored procedure however is not overtly simple; it combines the logic of Update and Insert into one. For this, I am using "IF EXISTS". On MySQL, this can be done with "MERGE". Did you notice the '0' at the end of that Insert statement? That's a default 'false' for the Deleted column. And with that, we get to the Del schema. This too, typically contains 1 stored procedure per table; DelOne. And for my imaginary table "MyFile", the stored procedure would be named:
  • Del.MyFile
Overly simple, it looks like this: The rest is simply a matter of connecting to the database, acquiring the information, caching it and running LINQ on it if necessary. Enjoy!
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)