Thursday, April 3, 2014

Is Truncate Table DDL or DML Statement?

Many times, categories of concepts and things overlap. It can be hard to categorize some items in a single category. The SQL TRUNCATE TABLE statement is an example of an item that is not so easy to categorize. Is it a DDL (Data Definition Language) or DML (Data Manipulation Language) statement?
There is an ongoing discussion about this topic. However, if you quickly bingle for this question, you get the impression that the majority is somehow leaning more toward defining the TRUNCATE TABLE statement as a DDL statement. For example, Wikipedia clearly states: “In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse).” Disclaimer: please note that I do not find Wikipedia as the “ultimate, trustworthy source” – I prefer sources that are signed!
Some of the reasons why many people define the statement as a DDL statement include:
  • It requests schema locks in some systems
  • It is not possible to rollback it in some systems
  • It does not include a WHERE clause
  • It does not fire triggers in some systems
  • It resets the autonumbering column value in some systems
  • It deallocates system pages directly, not through an internal table operation
  • and more.
On the other hand, it looks like there is only one reason to treat the statement as a DML statement:
  • Logically, you just get rid of the data, like with the DELETE statement.
Even the Wikipedia article that I referred to says “The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).”
Like many times, I have to disagree with the majority. I understand that the categorization is somehow confusing, and might even be overlapping. However, the only reason for categorizing the TRUNCATE TABLE statement in the DML category is “THE” reason in my understanding. One of the most important ideas in the Relational Model is the separation between the logical and the physical level. We, users, or people, if you wish, are manipulating with data on the logical level; the physical implementation is left to the database management system. And this is the important part – logically, when you truncate table, you don’t care how this statement is implemented internally, you just want to get rid of the data. It really does not matter what kind of locks a system uses, does it allow WHERE clause or not, etc. The logical point is what matters. Therefore, I would categorize the TRUNCATE TABLE statement as a DMLstatement.
Of course, this is a purely theoretical question, and is really not important for your practical implementation. As long as your app is doing what it should do, you don’t care too much about these nuances. However, IMO in general there is not enough of theoretical knowledge spread around, and therefore it makes sense to try to get the correct understanding.

Taken from Dejan Sarka

Wednesday, May 16, 2012

SQL Server 2012 - New Functions

Microsoft SQL Server 2012 introduces 14 new built-in functions. Microsoft keeps on improving there products kudos to them.

The new functions are:

Conversion functions
Date and time functions
Logical functions
String functions
In addition to the 14 new functions, one existing function has been changed. The existing LOG (Transact-SQL) function now has an optional second baseparameter.

Wednesday, August 12, 2009

ISRO unveils mapping application 'Bhuvan'

ISRO will unveil its version of Google Earth, the iconic mapping service for the common man to zoom into any part of the world on their personal computer using satellite images.

However, the new mapping service -- Bhuvan, which means earth in Sanskrit -- will allow users to have a closer look at any part of the subcontinent barring sensitive locations such as military and nuclear installations.

ISRO Chairman G Madhavan Nair will unveil the Bhuvan webportal at a function here tomorrow in the presence of Minister of State in the PMO Prithviraj Chavan.

Bhuvan will use images taken by ISRO's seven remote sensing satellites at least a year ago. These eyes in the sky can capture images of objects as small as a car on the road to build three-dimensional map of the world.

Thursday, July 9, 2009

Google Chrome Operating System

Google Chrome has always been a little more than a browser: it's optimized for running web applications, each tab runs as a separate process, the interface is minimalistic and there's even a task manager. "We realized that the web had evolved from mainly simple text pages to rich, interactive applications and that we needed to completely rethink the browser. What we really needed was not just a browser, but also a modern platform for web pages and applications, and that's what we set out to build," said Google in September 2008.

Google's blog announces a natural extension of the Chrome project: an operating system for netbooks. "Google Chrome OS is an open source, lightweight operating system that will initially be targeted at netbooks. Later this year we will open-source its code, and netbooks running Google Chrome OS will be available for consumers in the second half of 2010. (...) Google Chrome OS will run on both x86 as well as ARM chips and we are working with multiple OEMs to bring a number of netbooks to market next year. The software architecture is simple — Google Chrome running within a new windowing system on top of a Linux kernel."

As people use more and more web applications, the operating system becomes less important and it makes no sense to pay for it. The desktop mail client could be replaced by Gmail, the calendaring application could be replaced by Google Calendar, the office suite has lightweight alternatives: Google Docs and Zoho, it makes more sense to use an online feed reader like Google Reader, your scientific calculator is less powerful than Wolfram Alpha and you'll rarely need a video player when you have YouTube, Hulu and other video sites.

This idea is not new and there are already operating systems optimized for the browser. For example, Good OS announced last year Cloud, an operating system that "integrates a web browser with a compressed Linux operating system kernel for immediate access to Internet, integration of browser and rich client applications, and full control of the computer from inside the browser". If Google manages to create a great user interface, the new operating system could be very successful.

Tuesday, March 31, 2009

Gmail in 5 Indian Languages Hindi, Tamil, Telugu, Kannada and Malayalam

It's hard for me to imagine going without email for a day. It's such an easy and convenient way to communicate with my friends and family. However, there was one limitation that bothered me: my family members and friends who prefer to communicate in Hindi did not have an easy way to type and send email in their language of choice. I am extremely happy to announce the launch of a new feature in Gmail that makes it easy to type email in Indian languages.

When you compose a new mail in Gmail, you should now see an icon with an Indian character, as the screenshot below shows. This feature is enabled by default for Gmail users in India. If you do not see this function enabled by default, you will need to go the "Settings" page and enable this option in the "Language" section.

When you click the Indian languages icon, you can type words the way they sound in English and Gmail will automatically convert the word to its Indian local language equivalent. For example, if a Hindi speaker types "namaste" we will transliterate this to "नमस्ते." Similarly, "vanakkam" in Tamil will become "வணக்கம்." We currently support five Indian languages -- Hindi, Tamil, Telugu, Kannada and Malayalam -- and you can select the language of your choice from the drop-down list next to the icon.

They built this new feature using Google's transliteration technology, which is also available on Google India Labs, Orkut, Blogger and iGoogle. I hope you find this feature useful to communicate with those of your friends and family who prefer to write in their native language, and it will be available soon to businesses and schools using Google Apps. Now back to replying to all those Hindi emails I got from my family and friends today!

Sunday, March 29, 2009

How to Minimize SQL Server Blocking

By default, blocking locks do not time out. The waiting connection waits until the lock is released, and the block is over. If you like, you can set a lock time-out so that a connection does not wait indefinitely for the blocking lock to be released. This is accomplished using the LOCK_TIMEOUT setting.

When the LOCK_TIMEOUT setting is used to set a maximum amount of time that a connection can wait for a blocking lock to go away, the connection with the lock that is causing the blocking problem is not affected, but the connection waiting for the block is halted and an error message is received. When this happens, error message 1222, "Lock request time-out period exceeded," is sent to the application.

This means that the application needs to include the appropriate error-handling code to deal with this situation and take the appropriate action, which includes rolling back the transaction. If the application does not know how to deal with this error message, and the transaction is not rolled back, it is possible that the application can continue as if the transaction was not automatically cancelled. Because of this, you should not use the LOCK-TIMEOUT setting unless your application(s) that will be affected by it know what to do when they receive this message from SQL Server.

The syntax for the SET LOCK_TIMEOUT is:

SET LOCK_TIMEOUT timeout_period

Where timeout_period is the number of milliseconds that a connection waits for a blocking lock to go away before an error is returned from SQL Server to the application. A value of -1 is the default, which means to wait indefinitely. A value of 0 tells SQL Server not to wait at all, and to return the error immediately.

This command is based on a per connection basis, and stays with the connection until the connection is broken, or a new SET LOCK_TIMEOUT command is issued. [7.0, 2000]