Advertisements

Archive

Posts Tagged ‘DML’

DDL and DML in Single Script with GO (batch separator) – MSDN TSQL forum

April 16, 2015 Leave a comment

–> Question:

Running SQL Server 2008 R2. We currently apply schema and data changes to databases (for software upgrades) using separate scripts. Sometimes, based on the changes required this results in several scripts for a single upgrade. I’d like to (if possible) be able to encapsulate all changes, DDL and DML in a single script with transaction handling, so the whole thing rolls back if there are any issues. Problem is that with DDL changes we use GO as a batch separator, which screws up the standard error handling we implement in scripts using transactions (GO results in the script unable to find the error handler).

I’m aware of the fact that we can strip out these batch separators, but what I’m not clear on is what, if any, negative impact that would have. Are there certain commands in T-SQL (executing procedures, altering objects, etc) that are required to be executed in their own batch?

Lastly, as part of our DDL and DML changes, we perform a check to verify that the impending change actually needs to be made (so for a new column we check to see whether the column exists first). If, for example we were to make a DDL change that added a column, set a default constraint on that column and updated existing rows to have a base value for that column, could we use BEGIN…END or something similar so the column existence check would only have to be performed once for that group of changes?
 

–> My Answer:

You can remove GO or batch separator if you don’t have:

1. same variables declared in more than 1 batch

2. CREATE/ALTER VIEW/PROCEDURE statements, they should be first statement in the batch otherwise will fail. (otherwise it will give you following error: CREATE/ALTER VIEW/PROCEDURE’ must be the first statement in a query batch.)

3. there could be more things, just check based upon my above 2 points.
 

Ref Link.


Advertisements

DB Basics – Is SELECT a DML (Data Manipulation Language)?

July 26, 2011 1 comment

DML or Data Manipulation Langauge as the term suggest represents those SQL statements that manipulates the data in a database. Thus these langauges allows users to INSERT, UPDATE & DELETE the data in a particular database. Other than this the much debatable SELECT statement may or may not be considered as DML upon its usage.

A simple SELECT statement which fetches data from a table is a read-only language and cannot be called as DML.

But a modified version of SELECT i.e. ‘SELECT INTO’ can fall into the DML segment. The ‘SELECT INTO’ can be used to create a Table and insert records fetched from the SELECT statement.

Also, we can manipulate the data for reporting purpose while retriving by using the SELECT statement. The data in underlying tables is unchanged but on the frontend you get a view of modified data, like:

USE [AdventureWorks]
GO

SELECT ContactID, Title,
	FirstName, MiddleName, LastName,
	FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as [FullName] -- Name manipulated by joining 3 part names.
FROM Person.Contact

SELECT SalesOrderID, SalesOrderDetailID,
	OrderQty, UnitPrice,
	OrderQty * UnitPrice as [TotalPrice] -- Manipulated cost by calculating it for total items purchased.
FROM Sales.SalesOrderDetail

Thus SELECT also comes under DML and following is the list of all DMLs:
1. SELECT {COLUMN LIST} [INTO {TABLE_NAME}] [WHERE {WHERE condition}]
2. INSERT INTO {TABLE_NAME} VALUES (SET of Values)
3. UPDATE {TABLE_NAME} SET [WHERE {WHERE condition}]
4. DELETE FROM {TABLE_NAME} [WHERE {WHERE condition}]