Updating a ‘Non-Existent’ Record
And by non-existent, I mean exists in one place, but not in another (yes, I realize this article has a misleading title). File this under “more fun with SQL.”
If you ever want to change a value of a record in one table because it does not have a matching record in another, you can use a simple EXISTS check in your WHERE clause. This works best when there’s a foreign-key relationship between the tables or there is data in both tables that can be used for an exact match.
In a recent case, I wanted to mark said record as inactive. To make it inactive, I simply needed to set an IsActive field to ‘false’.
Here’s a sample that very closely matches what was done:
UPDATE Table1
SET [IsActive] = 0
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.KeyField = Table1.KeyField)
See? Simple AND Fun!
Temporal Data in a Relational Database Pt. 2
The post that I wrote a few years ago regarding working with temporal data has gotten a few more reads than I’d expected, and since I’ve implemented the pattern successfully a few more times since then, I thought I’d update the article with the addition of another way to approach the SQL code.
The basics of the pattern remain the same, so I simply refer you to the article, Temporal Data in a Relational Database, for a description.
Start by retrieving the results that meet your effective date criteria and create a Common Table Expression (CTE) to represent a ranked list that orders the results descending by effective date. Here, we partition by the employee id so ROW_NUMBER returns a ranking for each employee.
WITH ranked AS
(
SELECT
e.EmployeeId,
d.EmployeeDataId,
d.EffectiveDate,
d.EmployeeNumber,
d.EmploymentType,
d.HireDate,
etc...,
ROW_NUMBER() OVER (PARTITION BY e.EmployeeId
ORDER BY d.EffectiveDateTime DESC) AS RowNumber
FROM Employee e
INNER JOIN EmployeeData d ON e.EmployeeId = d.EmployeeId
WHERE
e.EmployeeId = @EmployeeId
AND (d.EffectiveDate <= @EffectiveDate)
)
Next, we pull the result out of the previous query, grabbing only the top row number:
SELECT
EmployeeId,
EmployeeDataId,
EffectiveDate,
EmployeeNumber,
EmploymentType,
HireDate,
etc...
FROM ranked
WHERE
RowNumber = 1
This query is a lot easier to read and make sense of. However, it does add the additional overhead of figuring out how many rows are in a CTE before pulling out the ones that are needed. As such, you may not want to use this approach if the record count is particularly high.
Configuring the ESB Toolkit 2.2 for BizTalk 2013 – Exception Creating the ExceptionDb
One of the great things about BizTalk 2013 is the improvements made in the installation of the ESB Toolkit. However, we’ve found lately, that there are still some issues that you should be watching for during configuration.
One issue, which I’ve seen twice now, has shown up while completing the very first configuration step in the ESB Toolkit 2.2’s configuration utility–creating the Exception Management database. Our configuration failed with this exception:
Exception Calling “create” with “0” argument(s) “Create failed for database esbExceptiondb’.”
The primary file must be at least 100 MB to accommodate a copy of the model database.
Deleting All Your SQL Server Stored Procedures at Once
This is a tip I picked up which you can categorize under the “fun with T-SQL” category…
Recently, I was looking to do some database cleanup and found that there’s no built-in way to delete every stored procedure in a database at once. Most of my digging around found excellent articles and scripts on how to delete a single stored procedure, or to loop through a list using a cursor, but nothing really good for blowing them all away until I learned this little tidbit: every proc in a database is stored in a system table called “information_schema.routines“.
Knowing this, it’s a pretty simple task to generate a list of DROP
statements and execute them with a simple EXEC
command. Here’s the code:
DECLARE @dropSprocSql varchar(max)
SET @dropSprocSql =
(
SELECT ' DROP PROCEDURE ' + routine_schema + '.' + routine_name
FROM information_schema.routines
WHERE routine_schema = 'theSchema' AND routine_type = 'PROCEDURE'
FOR XML PATH('')
)
EXEC (@dropSprocSql)
Of course, you can only delete user-created procedures with this (which is nice considering the danger involved of deleting anything system-related). Also note the WHERE clause. In this example, I’m only looking for stored procedures for a particular schema, but you can open this up for all schemas just by removing that part of the where clause, and if you remove the “routine_type” portion as well, you would also remove your user-defined functions.
Deleting All Your SQL Server Data
Oftentimes during the development cycle we have the need to clean up our databases. Recently, I’ve had the need to reset my SQL Server databases to a “no data” state to facilitate our development process.
Because my databases contain a lot of foreign-key relationships, a simple truncate on all the tables won’t work. I need to first, disable foreign key constraints, DELETE the data, and then re-enable the constraints. Then, for good measure, I reset the identity keys so we start auto-numbering the key fields at 1 instead of whatever number they were at. Here’s my script:
— disable the constraints
EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
— delete the data
EXEC sp_MSForEachTable “DELETE FROM ?”
— re-enable the constraints
EXEC sp_MSforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”
–reseed the identity columns
EXEC sp_MSforeachtable “DBCC CHECKIDENT (‘?’, RESEED, 0)”
Admittedly, this tip goes into the category of “No Big Deal” for the SQL Experts out there, but as I’m only a sometime SQL Developer, it’s nice to have these little tricks lying around in case I need them again.