Difference between Passimistic and Optimistic locking

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back.

When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.


Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID.

Stored Procedure to Generate Insert Statements with Columns and values for a Table in SQL Server

Requirement-

I wanted to generate Insert statements for all the rows in the table with the column name and their values.

For example,

If the Table name is Emp and with following structure and data

EmpId	EmpName	EmpLastName	Department
1	Amit	Gupta	Technical
2	Sumit	Verma	Functional
3	Ritesh	Sharma	Financial
4	Hitesh	Dubey	Sales

On calling the Stored procedure

GetInsertStatement 'Emp'

It should generate the following

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘1’, ‘Amit’, ’Gupta’, ’Technical’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘2’, ‘Sumit, ’Verma’, ’Functional’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘3’, ‘Ritesh’ ,Sharma’, ’Financial’)

Insert into Emp(EmpId, EmpName, EmpLastName, Department) values (‘4’, ‘Hitesh’, ’Dubey’, ’Sales’)

Following is the stored procedure to generate the desired result.

NOTE- Currently it includes identity column also in the Insert Statement since that was the requirement, but you can see the other version also without Identity column in Insert statement.



GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetInsertStatement]

(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information
--for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns
WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(3000) --for storing the first half
--of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data
--(VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned
--for respective columns

SET @string='INSERT INTO '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0

BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+
isnull('+@colName+','''')+'''''',''+'
END

ELSE
if @dataType in ('text','ntext') --if the datatype
--is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END

ELSE

IF @dataType = 'money' --because money doesn't get converted
--from varchar implicitly
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+'''''',''+'
END

ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast(convert(varbinary,'+@colName+')
as varchar(6)),''0'')+'''''',''+'
END

ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(4000) -- provide for the whole query,
-- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
FROM '+@tableName
exec sp_executesql @query --load and run the built query

CLOSE cursCol
DEALLOCATE cursCol

Get details of identity columns in all the tables in the database,

To select value of identity columns in all tables in the selected database.
Below is the query which selects the description of identity columns in all tables in the selected database.

SELECT OBJECT_NAME(OBJECT_ID) AS
TABLENAME, NAME AS COLUMNNAME,
SEED_VALUE, INCREMENT_VALUE,
LAST_VALUE, IS_NOT_FOR_REPLICATION
FROM SYS.IDENTITY_COLUMNS ORDER BY 1

SQL Server Query Execution Plan Analysis

When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information if very valuable when it comes time to find out why a specific query is running slow.
There are several different ways to view a query's execution plan.

They include:
From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.

From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.
Of these options, I prefer using the "Show Execution Plan", which produces a graphical output and considers current server operations.
*****
If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.
Index or table scans: May indicate a need for better or additional indexes.
Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes.
Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be.

*****
If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer or Management Studio to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.
On the other hand, if you use a table variable instead of a temp table, you can use the "Display Estimated Execution Plan" option

*****
If you have a very complex query you are analyzing in Query Analyzer or Management Studio as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately.

*****
The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:
In very complex query plans, the plan is divided into many parts, with each part listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer has to perform in order to get to the final results.
Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.
Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.
Eventually, all of the parts come together at the top left side of the screen.
If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.
If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.