Posts

How to remove duplicate data from a purely duplicated table without adding a new column: SQL Server

-
Image
  Applicable: Developer Problem We all know how to find out a duplicated row using 'Group By' and some other techniques, but how do we remove them if it is duplicated with respect to every single column available in the table. So in this session we are going to see 'How To delete row(s) from a SQL Server table keeping one row for each distinct values without using a new table or adding a new column to an existing table'. Solution So the purpose of this session we are going to create a table with 3 columns and populate it with just 2 distinct values repeated twice so a total of 4 rows only. Here we have the data duplicated with every single column. What we are expecting is that we have to eliminate row 2 and row 4 from this table since we already have them but we don't have any number reference to the rows. Here comes the "CTE"! Maybe you already know about CTE for those who don't know about CTEs it's just like a temporary table derived from a quer...
Post a Comment
Read more

How to decode an SQL Server temporary table from other session

-
Image
  Applicable: DBA Problem If you are someone like me who wish to have peak on someone else's temporary table it's time to read "Decoding a temporary table from other session in which it is not available for querying". Solution First of all we are going to create a temporary table named TempTable61 ( #TempTable61 ) which is on session ID 61. Okay, now we have a temp table with 3 rows and 3 columns. Next step is the first step of our recovery process, for that we are going to create a new session with some variable and table declarations As you can see our current session ID is 63. Here we have found the object ID of our target temp table and saving it to our variable @objectID. (Note: Actual name of the temp table in sys.tables must have some postfix values attached to it). Two temp tables we have created in this session is to hold intermediate data of our recovery process. We don't want all the columns in these table but the result of commands we are going to use ...
Post a Comment
Read more

SQL Server Vs Oracle Database Terminologies

-
Applicable: DBA & Developers In this session we are are going to see some similar terminologies that SQL Server and Oracle Database technologies named differently but which is almost same considering it's use and functions. It must be helpful for developers or DBAs who is changing from one of these technologies to another as a part of job change or project requirements. Let's see what under the box... 1. Logical database structure In Oracle the largest logical unit of databases is named as Tablespaces. Tablespaces are made up of Segments. Segments are allocated to a specific object such as a table. Segments are made up of extents and finally extents are made up of Blocks.Blocks are the fundamental logical unit of database structure. In SQL Server tablespaces are considered as File Groups which is a set of physical files . SQL Server says nothing about the segments but it still has extents which is made up of 8 pages, and a page is similar to Block in Oracle. 2. Recovery...
Post a Comment
Read more