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

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 model

Simply a recovery model is the property of database that controls how the transaction are logged. SQL Server give us 3 different recovery models which is known as Simple,Bulk-logged and Full. In Simple recovery model no transactions are logged as a result if a failure occurs everything since the last backup is lost. Bulk-logged logs almost every transactions but excluding major log generating operations like bulk copy program and index maintenance. Full recovery model logs each and every operation. In Oracle we have two modes which is known as ARCHIVELOG and NOARCHIVELOG. In ARCHIVELOG mode whenever a log file is filled it is archived into a separate location and a new file is used. In case of failure the archived files is used to reconstruct the data. In NOARCHIVELOG mode new transactions overwrites the old one and no archiving process is done as as result reconstruction of data in case of failure is not possible.


3. Instance-database relationship

An instance is the copy of database engine and it's services running on our primary memory as an operating system service. In SQL server the instance-database relationship is many-to-one. It means a single instance has multiple databases under it's management. Resources allocated to the instance are shared by all the database operations. In Oracle prior to 12c the relationship was one-to-one. It means a single instance controls and co-ordinates only one database. In 12c Oracle introduced a new technology known as Oracle Multitenant Architecture. With the use of multitenant architecture many-to-one relationship is also possible. Both SQL Server and Oracle Databases have one more relationship called one-to-many in which two instances(mostly different machines) points toward a single database which is part of high availability solutions. SQL Server's Fail over clustering setup and Oracle's Data Guard is an example for one-to-many relationship.


4. Template Database

Template Database is nothing but a system database which act as a template for newly creating databases. In SQL Server this template DB is called Model Database. Whenever a new user database is created it inherits all the properties of Model database.

With the Multitenant architecture Oracle also introduced a template DB known as Seed Database which is used whenever a new pluggable database is created.


Comments

Post a Comment

Popular posts from this blog

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 ...
Read more