Comments (0) I was involved in a recent project to migrate an Oracle database to Microsoft SQL Server 2000, and successfully migrated all Oracle objects to SQL Server. This experience has encouraged me to write an article on Oracle to SQL Server 2000 migration.
In this article, I will explain how to transfer Oracle Objects, including constraints and data, to SQL Server 2000.
This article is written under ‘Quick and Short’ edition. In this article I will explain about Difference between DELETE and TRUNCATE method.
Read more Quick and Short articles.
DELETE logs the data for each row affected by the statement in the transaction log and physically removes the row from the file, one row at a time. The recording of each affected row can cause your transaction log grow massively if you are deleting huge numbers of rows. However, when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover the database to the most recent state, should a problem arise. The fact that each row is logged explains why DELETE statements can be slow.
TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, BOL refers to TRUNCATE operations as “minimally logged” operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.
Some limitations do exist for using TRUNCATE.
So if TRUNCATE is so much faster than DELETE, should one use DELETE at all? Well, TRUNCATE is an all-or-nothing approach. You can’t specify just to truncate those rows that match a certain criteria. It’s either all rows or none.
You can, however, use a workaround here. Suppose you want to delete more rows from a table than will remain. In this case you can export the rows that you want to keep to a temporary table, run the TRUNCATE statement, and finally reimport the remaining rows from the temporary table. If your table contains a column with the IDENTITY property defined on it, and you want to keep the original IDENTITY values, be sure to enabled IDENTITY_INSERT on the table before you reimport from the temporary table. Chances are good that this workaround is still faster than a DELETE operation.
You can also set the recovery mode to “Simple” before you start this workaround, and then back to “Full” one it is done. However, keep in mind that in this case, you might only be able to recover to the last full backup.
——————————————————————————————————————————————————————
Readers are requested to add comment, and add more information in case they want.
Read more Quick and Short articles.
The following table specifies the maximum sizes and numbers of various objects defined in SQL Server databases or referenced in Transact-SQL statements. Read more »
This article is written under ‘Quick and Short’ edition. In this article I will explain about database indexes, type of indexes, best practices to use index, how to use indexes to improve performance of T-SQL (database query).
Read more Quick and Short articles.
Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index. Read more »
This article is written under ‘Quick and Short’ edition. In this article I will explain about Joins used within T-SQL statement.
Read more Quick and Short articles.
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Different SQL Joins:
This is my very first article under ‘Quick and Short’ edition. In this article, I will introduce the concept of normalization and take a brief look at the most common normal forms.
Read more Quick and Short articles.
What is Normalization?
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: 1) eliminating redundant data (for example, storing the same data in more than one) and 2) ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. Read more »
The power of a relational database isn’t in single tables — it’s in combining tables to obtain new data sets. The real power of the relational design is only realized when you bring multiple tables together to show the data they contain as a group.
You define “key” fields that uniquely identify a particular row in a table, and those fields are the ones that you’ll use most often to “join” the tables back together. A relational database’s design is created with that join in mind from the start. In fact, the keyword used to bring tables together is JOIN — but I’m getting ahead of myself. Read more »
In this article, I’ll explore data types. I won’t cover them all, because you won’t use all of them (at first, anyway) and because they change based on which version and even sometimes which edition you’re using. But I will cover the major types that you’ll see used most often.
Data types are the kinds of data you can store in a column of a SQL Server table. When you create a table with the CREATE TABLE command, you specify the columns and the type of data they can contain. Often, the data type will require more information in addition to what it is, such as how long or what precision the data will store. Here’s a sample statement: Read more »
It’s fairly simple to describe a database constraint – it’s just like the English use of the word, to prevent something. In the case of a SQL Server database, constraints are rules you create to specify how data is to be stored in your database.
Whether you design databases or program against them, database integrity is absolutely essential. It’s not just about making sure a certain table has data in it; it’s about ensuring that the data throughout the system doesn’t cause a program to crash or a result to be ambiguous.
Constraints enable SQL Server to implement this integrity. There are several classes of constraints, which we’ll see in a moment. By layering them intelligently, you maintain the integrity of the database as a whole.
Most constraints are column-based, meaning that they are declared and enforced on a single column at a time. One, in particular, is declared and enforced on a table – we’ll see that one in a moment. Read more »
Sometime we want to compare the records between two database tables. These databases could be on same server or on different servers. Here is some information about how we can compare records between two tables. Hope this will help and give you some information.
The ‘EXCEPT’ operator performs the compare between two tables:
When both databases are on same server:
The following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query.
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;
–Result: 266 Rows (products without work orders)
Sometimes you want to query to the remote SQL server (Server which is in network). Following statement will help you to fire a query to the remote server:
Select * from [ServerName].[DatabaseName].[Scheme].[TableName]
Before you fire query, you are required to link to the server on which you want to query. The ‘sp_addlinkedserver’is a system store procedure which can be used to add a link server to the server you are sitting.
EXEC sp_addlinkedserver
@server = ‘SourceServer’
, @Srvproduct = ”
, @Provider = ‘SQLNCLI’
, @datasrc = ‘Remote SQL Server instance name’ Read more »
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SQL Server 2005 SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement. Read more »
Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including: Read more »
62 | Unique Visitors |
