Choose your SQL Server schema comparison tool – Part 2

Database
This is the continuation of the previously written post. Open DBDiff Open DBDiff is a free and open source database schema comparison tool for SQL Server 2005/2008. It reports differences between two database schemas and provides a synchronization script to upgrade a database from one to the other.  Open DBDiff can synchronize> Tables (including Table Options like vardecimal, text in row, etc.), Columns (including Computed Columns, XML options, Identities, etc.), Constraints, Indexes (and XML Indexes), XML Schemas, Table Types, User Data Types (UDT), CLR Objects (Assemblies, CLR-UDT, CLR-Store Procedure, CLR-Triggers), Triggers (including DDL Triggers), Synonyms, Schemas, File groups, Views, Functions, Store Procedures, Partition Functions/Schemes, Users, Roles [caption id="" align="aligncenter" width="730"] Open DbDiff[/caption] tablediff Utility (Microsoft) The tablediff utility is used to compare the data in two tables for non-convergence, and is…
Read More
MongoDB Succinctly – Free E-Book

MongoDB Succinctly – Free E-Book

Books, Database, Programming
Syncfusion published yet another book on their Succinctly Series - MongoDB Succinctly. Once again I had the pleasure to be the Technical Editor of the book.     In MongoDB Succinctly, author Agus Kurniawan explains the essentials of the MongoDB database system. Starting with creating a MongoDB database, you’ll learn how to make collections and interact with their data, how to build a console application to interact with binary and image collection data, and much more. You’ll also learn how to integrate MongoDB into Windows Forms, ASP.NET, and ASP.NET MVC projects. Download today to bring the high performance and scalability of MongoDB to your applications. Look for Entity Framework Code First Succinctly coming soon! The file can be downloaded directly from the Syncfusion web site Table of contents: Introduction to…
Read More

SQL Server ghost records – in a nutshell

Database
I was completely unaware of the SQL Server "ghost records" until recently I've started using a table in SQL Server database to temporarily store and then delete generated PDF documents as part of a nightly job. Because of a large amount of data being processed (inserted and then deleted) few days later our DBA realized that the database data files started growing in a rhythm of 10GB per day without any apparent reasons. What is a Ghost Record "Ghost Records" are records that have been logically but not physically deleted from the leaf level of an index. Such a delete operation never physically removes records from pages but it only marks them as having been deleted, or ghosted! Ghost Records have been introduced as a performance optimization that makes the…
Read More

Logic in the database, to be or not to be?

Database, Programming, Software Architecture
Recently I have participated to a couple of discussions about whether the triggers or stored procedures should be used or not in a "real" project, so I decided to write this post to express my point of view. This said, I warn you, there is no a "right" answer, but most probably a "pro" and "cons" about anything written in this post. An old problem There are more than a handful of developers who are not really clear (from the architectural point of view) when to use triggers, views, stored procedures,etc... let's just call this "database logic". Being so easy to change, and so close to the data, one is really tempted to go to the "source" and start coding.  As an alternative read, please check my previous article Direct…
Read More

MS SQL Server – Querying object info

Database
I am migrating some old posts to this blog, so I though it would be good to migrate this one too. This post has been originally written in February 2010, but still actual and valid. Today I run into a problem of finding out when was the last time a specific Stored Procedure (SP) has been changed. Obviously, there are different ways to find out, but in this post I would like to introduce some of the possible solutions by using your lovely MS SQL Server Management studio query window. I hope you will find this info useful. Introduction Generally there are two ways to retrieve information about database objects. By using either the Sys or INFORMATION_SCHEMA schemas. So, let’s start by saying that usually you would be too busy…
Read More

Import DB-IP.com database data to Microsoft Sql Server with ZORAN.DB.IP.Importer tool

Database, Programming
Recently I started working on my side-hobby-project on the linktotweet.com web site, and one of the functionality I want to offer to site members is the possibility to track the statistics, and more precisely the location of their visitors. In other words, I need to translate the IP address to an actual location. While this is almost a trivial task for whoever is using the Google Analytics (like I do) that tracks the visitors, implementing it yourself is actually not such a trivial thing. Ok, it is not that complex too :) IP to Location database The first thing to find out is where to get this IP to Location data from! Mainly there are two possibilities. Use a third party web service that will translate the ip to a…
Read More

Choose your SQL Server schema comparison tool

Database
Whenever you are working on a database solution that involves more than one deployment environment you need to be sure that you are running the same database schema and usually the same reference data. If you were to manage manually various databases in order to be sure that there are no differences, you would spend long hours with no guarantee when you were done that you had found all of the differences. In this case, you need a database comparison tool. There are mainly two types of database comparison tools: Schema comparison Data Comparison The tool will find the differences for you in your databases, allowing you to make changes as you see fit. It also offers you peace of mind knowing that your results will be 100% accurate. In…
Read More

LINQ Standard Query Operators

Database, Programming
Standard Query Operators are the "heart" of LINQ, as those operators are representing the API that we need to go through in order to do any LINQ query. There are two sets of LINQ standard query operators, one that operates on objects of type IEnumerable and the other that operates on objects of type IQueryable. They are defined as extension methods of the type that they operate on. This means that they can be called by using either static method syntax or instance method syntax. The Standard Query Operators operate on sequences. Any object that implements the interface IEnumerable for some type T is considered a sequence of that type. Thanks to the fact that query operators are mainly extension methods working with IEnumerable or IQueriable objects, you can easily…
Read More

Primary key: To GUID or not to GUID?

Database, Programming
The primary key of a table on an RDBMS uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique or it can be generated by the RDBMS (such as a globally unique identifier). Primary keys may consist of a single attribute (single key) or multiple attributes in combination and in that case we speak about composite keys. I am sure that you have asked yourself the question in the title of this post several times in your professional life:  every time you create a new database table. For sure, this is one of those recurrent questions that now and then appears and make you think over and over again. The short answer to it is: it depends (as usually, isn't…
Read More

An introduction to NoSQL

Database, Software Architecture
In my working career I had to deal with databases since my first day at work and it is a bit a hate-and-love relationship. Usually well working application is always backed up by a good designed database schema and good accessing mechanism. So far I always used RDBMS - Relationship databases such as Microsoft SQL Server, Oracle, MySql, Sybase. Right now there is a big hype about NoSQL movement, which is growing and in my opinion it is going to be a major player in the next years, and it would be good to familiarize with the technology as soon as possible. To note is the fact that some big companies are already investing into or using NoSQL solution. Just to name few: Microsoft is integrating MongoDB into the Windows…
Read More

An easy way to avoid SQL Server nested transactions

Database, Programming
Transactions are an integral part of any (serious) database development when there is need for data consistency. Transactions in a database environment have two main purposes: To provide reliable mechanism to allow correct recovery from failures and keep a database consistent To provide isolation between programs accessing a database concurrently. It's impossible to mention transactions without mentioning the ACID (Atomic, Consistent, Isolated and Durable) attributes that every transaction has to support. Atomicity: Indicates that everything or nothing get's executed. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation no transaction should be able to interfere with another transaction Durability once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. Every transaction in Microsoft SQL Server…
Read More