DBCC FREEPROCCACHE

DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache can causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC SQLPERF(logspace)

DBCC SQLPERF(logspace) is an absolutely functional command if you are only interested in consumption of your database log files. It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed (as a percentage of total log file size). A drawback is the fact that the results are an aggregate for the database.

logspace can be 1 of the value below:
UMSStats - SQL thread management
WaitStats - resources, wait types
IOStats - outstanding reads & writes (note: always zeros)
RAStats - read ahead activity (note: always returns zeros)
Threads - I/O / CPU / memory usage per thread
SpinLockStats - statistics on spinlocks
UMSSpinStats - statistics on UMS
NetStats - ODS statistics
LRUStats - LRU-MRU chain statistics (Note: free page scan always zero)

DBCC SHOWFILESTATS WITH NO_INFOMSGS

This DBCC command provides us the data files for the current database that you select. DBCC SHOWFILESTATS or DBCC SHOWFILESTATS WITH NO_INFOMSGS is the command to display the current database File Name, Database Name, Total Extend, Used Extent, Field ID and File Group.

Get the ID of a Newly Inserted Row Using SCOPE_IDENTITY()

SCOPE_IDENTITY() command will give you the last identity value that was generated in the current scope.

@@Identity will return the last identity value that was generated in the current session but in any scope.

If you database got trigger to insert a new row, @@Identity will give you the id that generated by trigger instead of ID of the last row you inserted.

In order to get the ID of the row that you inserted, which is just what SCOPE_IDENTITY() will give you.

Below is the SQL to get the last inserted ID in the current scope that need to execute together with the commands.

string sqlString = "INSERT INTO Table(Col1, Col2) VALUES (@Col1, @Col2); SELECT SCOPE_IDENTITY () As NewID";
SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
objCmd.Connection.Open();
objCmd.Parameters.Add(new SqlParameter("@Col1", Col1Value));
objCmd.Parameters.Add(new SqlParameter("@Col2", Col2Value);

SqlDataReader dataReader = objCmd.ExecuteReader();

if (dataReader.HasRows)
{
dataReader.Read();
newRowID = Convert.ToInt32(dataReader["NewID"]);
}

dataReader.Close();

SQL Update Multiple Columns

Below Sql use to update multiple column for a table in 1 shot

*****************

UPDATE Table1
SET (Col1, Col2, Col3, Col4) =
(SELECT Col1a, Col2a, Col3a, Col4a FROM Table2 WHERE Col5a=Table1.Col5)
WHERE Col5 IN (SELECT Col5a FROM Table2 )

*****************

Identity column not getting sequential values

Identity values are not sequential. Even if you do not delete rows, you can end up with missing identity values. If a transaction inserts a new record to the table and ROLLS BACK, you can end up with a missing identity value.