October 1, 2015 C#, HowTo, QuickTip, SQL No comments
Simple check for breaking Database changes using NUnit and T-SQL CHECKSUM_AGG
Imagine that your database is being used by another system that directly reads data from few of the tables. I know this is not a good idea and I would not recommend anyone to do anything similar. Unfortunately developers often do what they don’t really want to do.
In this post I just want to document a very simple way of verifying that you are not removing or modifying tables and columns that are being used by someone else.
My solution is dumb unit tests that select table schema information for the list of tables and columns and then builds checksum that is finally compared to expected checksum.
I don’t pretend to sound like this somewhat dirty solution is a right way, but it is very quick to implement and effective way. Here is the implementation in C#:
In code above we verify that we are not removing or modifying few columns in tables Table1 and Table55, since they are used by SystemX. NUnit TestCase attributes are used to allow for multiple table checks. First parameter is a checksum that you would get for the first time by failing the unit test or by running query separately. Checksum calculation is simple but it has a bit of overburden with concatenating column names into one string and then parsing it again into a temp table for the “IN” condition. (Of course there are other ways, but I find this one to be a bit less code). Also it probably worth to mention that we apply database changes using FluentMigrator, so tracking what exactly was changed that caused particular Unit Test case to fail would be just to have a look at the latest commit.
I think this is acceptable solution for a small set of checks. Probably you need another solution if your database is heavily used by another systems.
Hopefully is helpful to someone. At least I write this post to have it handy for myself in case I need something similar done quickly.
TSQLUnit – testing logic in database? Should logic be there?
November 1, 2010 Opinion, SQL 6 comments
Today I’ve been on technical meeting where TSQLUnit was discussed as approach of testing database.
From what I understood it looks like this is very powerful and great tool for testing stored procedures and other execution code inside of SQL Server. It allows easily create setup for each test with minimal database schema, needed exactly for some particular stored procedure. After test got executed it rolls back to state before running test. Cool!
From my point of view this is really good thing to test stored procedures in isolated environment, as any good UT does with testing code. There is also question about integration of those tests with changes in real database and one great mind have produced using schemabindings in SQL Server.
It should be also possible to integrate TSQLUnit with CI by using NAnt.
Why?
There is only one thing I kept in my mind and did not talk about it during meeting. It is question: why do we still write lot of stored procedures? Yea, I understand that sometimes they are really needed and that there are some reporting projects that might require fetching lot of data at once. But during the meeting it looked like guys are going to use it for many other projects. Hm… From my point of view, this kind of tests should be written in regular programming language, executing some code and fetching needed data as it will be used further, after roll-backing inserted data – therefore we have integration tests. All them should run separately. And as per me this should be fine unless you have logic in database – I agree, that in this case we have to come to some database testing tools. Why should we have logic it in database unless there are some special requirements?
I still feel a bit frustrated, because for me it is quite hard to answer for the question of having logic in code or having some heavy portion of it in database. I vote for first variant, but maybe I do not have enough experience to understand why it is great to have logic in database. Who knows?
What do you think?
Quick & cheap way to rename colum in table – sp_rename
March 31, 2010 QuickTip, SQL No comments
Quick & cheap way to rename colum in table:
EXEC sp_rename
@objname = ‘MY_TABLE.COULMN_NAME’,
@newname = ‘COLUMN_NAME’,
@objtype = ‘COLUMN’
@objname = ‘MY_TABLE.COULMN_NAME’,
@newname = ‘COLUMN_NAME’,
@objtype = ‘COLUMN’
Get fun!
IDENTITY_INSERT is already ON for table [Table_Name]
January 19, 2010 Errors, SQL 20 comments
I was editing some script in SQL Server Management Studio.
And then suddenly this error appeared:
IDENTITY_INSERT is already ON for table [Table_Name]
So, how did I come to this error?
My script looked like one below:
SET IDENTITY_INSERT [TABLE_NAME] ON
–insert statements
—failure on one of inserts because of not existing column
SET IDENTITY_INSERT [TABLE_NAME] ON
The script failed on one of the inserts, because of a missing column.
I added a column to fix the problem but then I started getting this error: IDENTITY_INSERT is already ON for table [Table_Name].
I tried to run inserts independently but error persisted.
Why does it happen?
IDENTITY_INSERT works in scope of one session. If you read corresponding MSDN page it says: “At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.”
How to solve?
You just need another session. I simply reconnected to the same database and it run without any issues.
Of course I changed the script to set IDENTITY_INSERT to OFF in the end.
How to copy data from one database to another one using T-SQL
December 2, 2009 SQL No comments
If you want copy data from one database to another on the same server you could use next SQL:
INSERT INTO [DestinationDatabase].[dbo].[Table1] ([Column1], [Column2], [Column3])
SELECT [Column1], [Column2], [Column3] FROM [SourceDatabase].[dbo].[Table2]
SELECT [Column1], [Column2], [Column3] FROM [SourceDatabase].[dbo].[Table2]
Also if you want to copy data with the schema of table, i.e. copy table structure and contents to another database you could use:
SELECT * INTO [DestinationDatabase].[dbo].[TABLE]
FROM [SourceDatabase].[dbo].[TABLE]
FROM [SourceDatabase].[dbo].[TABLE]
