Home→Technical→Sql/T-Sql

Technical Blogs          

Loop through all records in Sql server with while statement

Posted By sarinmall on May 03, 2013     38 Views

Foreach is the common functionality in all programming language. Foreach is used for traversing items in a collection. Unlike for loop constructs which maintains a counter to loop defined number of times, foreach will essentially loop through all the items in the collection. Hence strictly speaking, there is no such foreach loop in SQL. Cursor does offer similar kind of functionality but using cursor is not the recommended approach. In this article, we will see how to implement foreach kind of functionality in SQL using While loop

Delete duplicate records from table in SQL

Posted By sarinmall on Apr 23, 2013     52 Views

Redundancy is the most common issue in all databases. Very often, your database is filled up with redundant data and then you are burdened with the task of removing redundant data from your database. One of the most important tasks in this regard is deleting out the duplicate records. Writing a query to delete duplicate records is not an easy task but writing an efficient one is very important especially when your database has huge volumes of records. Let us go through some of the scenarios applicable when deleting records from a database.

SQL-Removing numbers in a string

Posted By sarinmall on Apr 04, 2013     64 Views

Recently, I got the requirement of removing the trailing integers in a string. This was not difficult as I had the readymade SQL function to achieve the same (Which I have used in my previous company). But the problem was I had to query the server where I did not have permissions to create functions. In this case, my readymade function was not going to help. So, I decided to write a query instead of SP or function to achieve the same.

Nested Transaction, SavePoint and error handling in sql server

Posted By sarinmall on Mar 19, 2013     286 Views

Nested transactions are one of the important features of SQL. As the name suggests, nested transaction means a transaction within transaction. Nested transactions let you split your large database operations into smaller chunk of operations. Operation is issued with BEGIN TRAN command. You can determine the number of active transactions using @@TRANCOUNT automatic variable. 0 means you are at first level. 1 means you are at first nested transactions, 2 mean second nested transactions and so on. Data is not committed till the first outermost transaction is successful. So, even after committing the inner transaction, if the outer transaction fails, then the data committed by the inner transaction will also be rolled back. Rollback operation, on the other hand, works at all level. Irrespective of whether you use rollback at the first level or the 10th level, all the operations will be rolled back.

How to use transaction in sql server for data consistency

Posted By sarinmall on Feb 16, 2013     189 Views

In many situations, there is a need to update multiple tables of a database, especially in a normalized database. For data consistency, either all updates should be successful or none of the command should execute. A good example of this would be a banking system where the transfer would increase the balance of one account while other would decrease balance of other account. Moreover, you will also need a log for both accounts to audit all transfers.

Get list of all database objects with table name or column name
Posted By sarinmall on Dec 04, 2012     193 Views	While working on a databse, I frequently get the need of retrieivng list of all stored procedures, triggers, or function that uses a particular column or particular table. I have collected list of all such queries which will help us to find databse objects using a specific column or specific table.

Get comma separated result of a database query
Posted By sarinmall on Nov 30, 2012     196 Views	Sometimes you may have the requirement to display the result of the database query as a comma separated values. For example in a blogging website, you may want to show the list of comma separated users who liked a particular article.

Database cannot be opened due to inaccessible files or insufficient memory or disk space
Posted By sarinmall on Nov 16, 2012     191 Views	1) This issue may be caused if the database is stuck in recovery mode. This may occur due to power failure, insufficient memory, insufficient disk space, wrong drive location, Stopped transactions etc. For the first case, restarting the sql server service or restarting the computer would work.

Get All Tables in a Database

Posted By sarinmall on Sep 16, 2012     167 Views

There are many ways to show all tables of a database. Now one may ask why the developers of SQL server came up with many ways to arrive at the same solution. The answer is that although there are many ways to retrieve all tables of the database, each one of them differ from one another way in the way they derive the tables, or in the number of extra information they show along with the table name etc.

Need and advantages of using computed columns

Posted By sarinmall on Sep 02, 2012     183 Views

It might happen that sometimes you write a stored procedure, triggers or function to compute a certain value from the value of some other column. For example, you might write a SP or function to derive the age of an individual based on his date of birth. Although nothing is wrong here but instead of using the triggers or stored procedure to see the result, wouldn’t it be better to see such computed result directly as one of your columns of select query.

 1 2