Transaction, Batch and Exception in SQL Server

Transaction and batch can include each other, exception can stop a batch but usually never rollback a transaction. In the case of exception with error level equal or above 20, since SQL Server tries to close the connection or terminate the process, the transaction will be rolled back as I can imagine.

Transaction has characters of ACID. It guarrantties atomic operations between statements included in it. Batch is a way to submit a bounch of statements in one effort. SQL Server compiles a query plan for a batch.

1. Transaction can have multiple batches

BEGIN TRAN
DML1
DML2
GO
DML3
GO
....
GO
END TRAN

Example:

begin tran;
create table t(i1 int,c1 varchar(10));
select @@TRANCOUNT;
go
insert into t values(1,'a'),(2,'b');
go
select @@TRANCOUNT;
select * from t;
rollback;
select @@TRANCOUNT;
select * from t;

2. A Batch can be composed of several transactions. In the following example, two transactions are submitted to database in one batch.

BEGIN TRAN
DML1
END TRAN
BEGIN TRAN
DML2
END TRAN
GO

Example:

begin tran;
create table t(i1 int,c1 varchar(10));
select @@TRANCOUNT as numTrans_1;
commit;

begin tran;
insert into t values(1,'a'),(2,'b');
select @@TRANCOUNT  as numTrans_1;
commit;

select @@TRANCOUNT  as numTrans_0;
select * from t;
drop table t;
go

3. Exception and Batch

3.1 Statement level exception

SQL Server continue to execute rest of a batch should a statement level exception happens. The statement itself fails of course.

select power(3, 32)
print 'this still runs'
--print is better than select in this test case since it's printed together with error message
go

3.2 Batch level exception

SQL Server stops running rest of statements in a batch when batch level 3xception happens. The exception also bubbles up each level of calling stacks, and aborts all of them. Connection to SQL server is still good.

select convert(tinyint, 'aaa')
select 'this will not execute'
go

--following code demonstrate exception bubbling up
create procedure bubbleUp
as
begin
select convert(tinyint,'aaa')
end

go

exec bubbleUp
select 'this will not return'
go

3.3 Connection level exception

The connection to the SQL Server will be closed if it happens.This is usually caused by internal server errors. I haven't tried that hard to make it happen. :)

Exception with error level 20 or above will make SQL Server to terminate the process.

Note: My connection kept alive even after SQL Server said it was terminating the process. MS really knows how to give us hard time on its error handling mechanism, like you can never make a clear understanding on state returned in error message.
--

select top 1 * from sys.objects
raiserror('this test will break the connection.',20,1) with log;
select top 1 * from sys.objects
--result

(1 row(s) affected)
Msg 2745, Level 16, State 2, Line 2
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 2
this test will break the connection.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
3.4 Parsing and scope-resolution level exception

They will act like batch level exception if they happen in the same scope as rest of statements in a batch; They will act like statement level exception if they happen in lower level scope such as in a procedure or function called in a batch.

--direct in a batch
selectxyz from table
select 'this will not run'
go

--in lower scope of a batch, such as in another statement
exec('selectxyz from table')
select 'this will return'

--scope resolution
--this will be created because syntax is correct

create procedure scopeResolutionException
as
begin
select xxx from nonExistingTable
end
go

--this will fail because querying non-existing table
exec scopeResolutionException
select 'this will execute'
--also clean it up
drop procedure scopeResolutionException
go

-- scope resolution error, whole batch is aborted
select xxx from nonExistingTable

select 'this will not execute'
go

NOTE: when a statement is committed to SQL Server, SQL Server does parsing and compilation together. For a stored script, SQL Server parses the syntax but does not compile execution plan at same time. It uses late binding and create execution plan upon the time it executes the stored unit.

4. XACT_ABORT

Understanding the different level of exceptions in SQL Server is necessary since we can't handle the them well without knowing them the first. But to figure out what exception belongs to what exception type is tedious and cumbersome. The people who understand this well won't bother to deal with each situation, the people who do not understand this well also made success program, why?

SET XACT_ABORT ON;

This is our star. It aborts both batch and transaction when any level of exception occurs. Aborting transaction here means rolling back the transaction. It turns all the complex situations into one simple scenario: stop running rest of code, rollback transaction and bubble up exception to caller(if there's no TRY/CATCH).

To make a full use of it, it's better to use explicit transaction to organize related operations into one atomic transaction. Then most of time, you just need to remember to have the transaction committed.

CREATE PROCEDER ppp
AS
BEGIN
SET XACT_ABORT ON;
BEGIN TRAN
.....
COMMIT TRAN
END

For the situation TRY/CATCH is used in the code, please see this post:http://mashijie.blogspot.ca/2012/11/doomeduncommittable-transaction.html

Building a String From Multiple Records

In SQL Server, there are multiple ways to build up a string from values distributed in multiple record.

Preparation

Create table #m (id int identity,c1 varchar(200))
GO
create table #c (id int,c1 varchar(20))
GO
insert into #m(c1) values(1)
 insert into #c(id, c1) values
(1,'1,2'),
(1,'3,4,5'),
(1,'6,7,8')
GO

Method 1, using variable and SELECT statement.

Declare @v1 varchar(200)
SET @v1=''
SELECT @v1 = @v1 + c1 +';'
FROM #c
PRINT @v1

Method 2, a little bit XML and string operation, if you are still using SQL Server 2000.

The good of FOR XML is it returns result in one XML document, which can be treated as a string. Soemtimes it's handy and useful when you want to display master records with result from child tables.

This is also an example on when you want to use sub-query in your statement.
(
Why the hell they invent so many names for a query used in a query(embeded query)?

SELECT list --> Sub-query
FROM --> Derived table
WHERE --> Correlated sub-query.
)

select m.id,
replace(replace(replace(
(select c1 from #c c where c.id=m.id FOR XML AUTO),
'',';')
from #m m
where m.id=1

When this technique is used in update statement, it sometimes has advantages over the other ways such as using cursor and loop because of its conciseness.

Friends using Oracle? use LISTAGG function to have a single row output from multiple rows operated.

Methods of Importing Data into SQL Server Database

This is a brief of options you can use to import/export data into/from SQL Server database. And These are the methods shipped with SQL Server.

1. Export/Import Data Wizard
2. SSIS (can by created from 1)
3. BCP

Can be invokded by xp_cmdshell if it has to be used in SQL script.

Here is its syntax and usage: http://msdn.microsoft.com/en-us/library/ms162802.aspx

--generate format file(non-xml or xml format file.)
--e.g.non-xml

bcp dbs.dbolocation format nul -T -c  -f Currency.fmt

Trick: if you are using query and need to create format file for it, you can either
generating one from table the first and then manually modify it, or you can create a view in database and then generate format file from the using the view.
--export
--import using format file

4. BULK INSERT

Another version of BCP. It is a T-SQL Command, Can be used directly in SQL script.

BULK INSERT dbo.location
FROM 'C:\locationData.txt'
WITH ( FIELDTERMINATOR =',', ROWTERMINATOR = '\n' )

5. OPENROWSET

It's a T-SQL command. It can be used to import data from various data sources, such as spreadsheet, etc.

It's often used with INSERT INTO statement.

INSERT INTO dbo.Location
SELECT * FROM OPENROWSET('csv drive','csvfile.txt')

I have an example to load XML file into database for further transformation.

create table #tmpXML (xml315 XML);
EXEC('INSERT INTO #tmpXML(xml315)
 SELECT CAST(xContent AS XML)
 FROM OPENROWSET(
   BULK '''+@XMLFilePath+''',
   SINGLE_BLOB) AS xfile(xContent)'
)
INSERT INTO [dbo].[ShipmentStatusMessage]
      ([MessageSender]
      ,[MessageRecipient]
      ,[MessageDate]
      ,[MessageTime]
      ,[MessageID]
      ,[Filename]
      ,[X-12]
      ,[VesselName]
      ,[VesselCode]
      ,[VesselCodeQualifier]
      ,[VoyageNumber]
      ,[OperatingCarrier])
 SELECT
 r.value('./MessageSender[1]', 'NVARCHAR(15)'),
 r.value('./MessageRecipient[1]', 'NVARCHAR(15)'),
 r.value('./MessageDate[1]', 'NVARCHAR(8)'),
 r.value('./MessageTime[1]', 'NVARCHAR(8)'),
 r.value('./MessageID[1]', 'NVARCHAR(20)'),
 r.value('./Filename[1]', 'NVARCHAR(50)'),
 r.value('./X-12[1]', 'NVARCHAR(2000)'),
 r.query('./ShipmentStatus/VesselInformation/VesselName').value('.', 'NVARCHAR(28)'),
 r.query('ShipmentStatus/VesselInformation/VesselCode').value('.', 'NVARCHAR(8)'),
 r.query('ShipmentStatus/VesselInformation/VesselCodeQualifier').value('.', 'NVARCHAR(1)'),
 r.query('ShipmentStatus/VesselInformation/VoyageNumber').value('.', 'NVARCHAR(10)'),
 r.query('ShipmentStatus/VesselInformation/OperatingCarrier').value('.', 'NVARCHAR(10)')
 from #tmpXML
 cross apply xml315.nodes('//ShipmentStatusMessage') as T(r)

6. OPENDATASOURCE
Similar to OPENROWSET. I see people treat it as a table and do insert, update and delete on it.

7. OPENQUERY

Another T-SQL Command you can use directly in SQL Server. To use it, linked server
will have to be created the first. A linked server is a static data source comparing to the ones created on the fly in the opendatasource or openrowset.

8. LINKED Server

You can issue query against linked server straight forward.

Assuming you have created a linked server to another SQL Server database and you called it abc123, in OPENQUERY, you can use it this way:

select * from OPENQUERY(abc123,''select * from aTable)

Or, you can query linked server directly like this:

select * from abc123..aTable

Index Scan

Generally, when query optimizer chooses index scan, it thinks that is a most efficient way to execute the query based on its understanding on the context when the query is executed. It can be because of how the query is written, how the indexes are built on the underlying tables and how the statistics have been updated. 

A non-selective queries likely to have index scan or table scan. If that is the nature of the query, index or table scan is inevitable, and sometimes, if the table is small, that is actually a more efficient way to go. For many other situations, for the unwanted index or table scan, there are means to change the query, change the indexes on the tables to turn them to index seek, which in most of cases are more efficient than index or table scan.

A query can not seek on an index if the query does not filter on index's left most indexed column(index key). This is because SQL Server only maintains the key distribution for an index's left most column. Many times, even the index key appears in the criteria, but it is used in a function or it is implicitly converted to another data type, the optimizer ends up index or table scan because the operation makes it non-deterministic.

Note: The order of columns in where clause is irrelevant.

Let's do some experiments to discover some of the situations that will end up with index scan. Suggestions on how to make them more efficient are also given.

Preparation

Let's create a table with 3 columns, prepare some data, and build an index on first two columns.

create table test(id int identity,a int, b int, c int)
go
insert into test(a,b,c)
select ROW_NUMBER() over (order by a.object_id),
RANK() over (order by a.object_id),
DENSE_RANK() over (order by a.object_id)
from sys.columns a cross join sys.columns b
go
create index ix_a_b on test(a,b)
go

Non-clustered Index scan

1. Fields in criteria and select list are all covered by underlying non-clustered index, but the operator is open or result is most of the table content.

    select a,b from test where a not in (100,101)
    -- this can be tuned by specifying a <100 or a>101
    select a,b from test
    -- this is not likely to be further tuned.

2. Fields in criteria and select list are all covered by underlying non-clustered index, but there's a function call or operation on the column used in the criteria.

    select a,b from test where a+1 =1000 -- this can be tuned by using a=999
    select a,b from test where abs(a) =1000 -- this can be tuned by using  a =1000 or a=-1000
----glad to see the implicit conversion between varchar and nvarchar no longer ends up with index or table scan in SQL Server 2012.

3. Criteria contains first indexed column, the operator is enclosing, but the column is operated by a function or operation.
    select a,b,c from test where abs(a) =1000 -- this can be tuned by using  a =1000 or a=-1000

Clustered Index scan

Recreate the table. Intention here is to make our experiment as simple as possible.

drop table test
go
create table test(id int identity,a int, b int, c int)
go
insert into test(a,b,c)
select ROW_NUMBER() over (order by a.object_id),
RANK() over (order by a.object_id),
DENSE_RANK() over (order by a.object_id)
from sys.columns a cross join sys.columns b
go
create clustered index ix_a on test(a)


1. For a query to select all rows from a table, if the table has a clustered index, it will end up with clustered index scan, which is essentially a table scan.

    select a,b,c from test

2.  Fields in criteria and select list are all covered by underlying non-clustered index, but the operator is open or result is most of the table content.

    select a from test where a not in (999,1000 )
--optimizer is smart enough to use index seek if there is only one value in the parentheses.

3. Fields in select list are not covered by any indexes created on the table and column used as predicate is not straight. This becomes table scan/clustered index scan.

     select a,b,c from test where a+1 =1000

4. Criteria contains only non-indexed columns, optimizer has no good predicate to use. it ends up table scan/clustered index scan.

     select * from test where c =1000

The situation that will cause index scan is very complex and may change over the time when statistics on column and indexes are not accurately updated. Please do not take the situations here as granted, you will have to check the execution plan to understand why it ends up with certain operations in your very specific execution context.

Define Default Parameter in User Defined Funciton

I have a user defined function like this.

CREATE FUNCTION [dbo].[fn_FormatCSVString]
(
    @inString varchar(200), @inFixedLength tinyint=2
)
RETURNS VARCHAR(300)
AS
BEGIN

    DECLARE @csvResult VARCHAR(300), @position smallint, @strLen smallint
    SET @strLen = LEN(@inString)
    SET @position=1
    SET @csvResult=''
    IF @strLen<=@inFixedLength OR @inFixedLength<=0
    BEGIN
        RETURN @inString
    END
    WHILE @position<=@strLen
    BEGIN
        SET @csvResult = @csvResult + SUBSTRING(@inString,@position,@inFixedLength)+','
        SET @position = @position+ @inFixedLength
    END

    RETURN SUBSTRING(@csvResult,1,LEN(@csvResult)-1)

END

Can you call it with second parameter ignored?
    SELECT [dbo].[fn_FormatCSVString]('abc123')

No, SQL Server won't allow you to do that. You will have to either specify default or  provide a meaningful parameter for that.
    SELECT [dbo].[fn_FormatCSVString]('abc123',default)
    SELECT [dbo].[fn_FormatCSVString]('abc123',3)

Do not know why MS does this differently from what they do on procedures. I guess this is something you have to remember. It can also be a tricky question in the DB interview to see if they are detail oriented.

Rows in sysindexes is not accurate

(
Jan 23, 2013
In Oracle, you use this to figure out the number of records in a table.
select owner,table_name,num_rows from all_tables where table_name=upper('xxx')
)

This is what I learned today.

Rows column in sysindexes is not accurate and thus can't be used to determine number of rows in the associated table. The row_count in the new sys.dm_db_partition_stats is also not reliable. That makes result from sp_spaceused also not accurate since it relies on those system views.


So it comes a conclusion that it should not be used in program for your business need, but it is still good for DBA to use for system maintenance or to quickly get a rough row counts in large table to fulfill on-demand request.

Here is a script you can use in SQL 2005 and above to get a list of index and their row counts on a table.The sum of row_count is the number reported in sp_spaceUsed.

select i.name,s.partition_id, i.type_desc,s.row_count
from sys.indexes i join sys.dm_db_partition_stats s
on i.object_id=s.object_id and i.index_id=s.index_id
where i.object_id=object_id('dbo.tablename')

sysindexes view is to be deprecated so that try not to use it in the work.

In order to get more accurate result,DBCC UPDATEUSAGE WITH COUNT_ROWS can be executed, and sp_spaceused can be used afterword to get row counts at that moment.

Note, exec sp_spaceused @updateusage = ‘true’ equals running DBCC UPDATEUSAGE before running sp_spaceused, it won't update rows used in sysindexes or sys.dm_db_partition_stats.

Why SQL Server cannot guarantee accurate allocation information and row counts? It is said in an article that the reason is to reduce database blocking. If table space allocation and row count information were to be maintained accurately on every INSERT and DELETE, or when an index is dropped, or when a large bulk copy operation is performed, database concurrency could suffer as users in a high transaction environment wait for their transactions to complete as the space information is maintained in real time. I will take it for now.

Reduce Lock Contention in SQL Server

it is from http://support.microsoft.com/kb/75722.

Locking in SQL Server helps ensure consistency when reading and writing to the database. There is always a tradeoff in any relational database system between concurrency and consistency. It is always important to maintain consistency, or accuracy, of the data. However, the highest levels of consistency can result in less concurrency, or worse performance, if the proper steps are not taken.

Sometime, database performance tuning is the art of choosing the right tradeoffs.

The following methods can be used to reduce lock contention and increase overall throughput: 

1. Avoid situations in which many processes are attempting to perform updates or inserts on the same data page. For example, in version 6.x and earlier, if there is no clustered index on a table, or if the clustered index consists of a nonrandom value, such as an ever-increasing key value, all inserts will go on the last page of a table. This particular hotspot situation can be avoided by creating a clustered index on a value that will insure each user and/or process is inserting to a different page in the table.

2. Avoid transactions that include user interaction. Because locks are held for the duration of the transaction, a single user can degrade the entire systems performance.

3. Keep transactions that modify data as short as possible. The longer the transaction, the longer the exclusive or update locks are held. This blocks other activity and can lead to an increased number of deadlock situations.

4. Keep transactions in one batch. Unanticipated network problems may delay transactions from completing and thus releasing locks.

5. Avoid pessimistic locking hints such as holdlock whenever possible. They can cause processes to wait even on shared locks.

6. optimistic concurrency control can be specified in read-only environment

7. avoid expensive calculations while locks were hold.

8. design the code to have different phases so that to reduce the locking in shortest period on the shared resources. this is extremely useful in ETL's staging concept.