Thursday, November 27, 2008
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
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)
Friday, November 21, 2008
@@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.Parameters.Add(new SqlParameter("@Col1", Col1Value));
objCmd.Parameters.Add(new SqlParameter("@Col2", Col2Value);
SqlDataReader dataReader = objCmd.ExecuteReader();
newRowID = Convert.ToInt32(dataReader["NewID"]);
Friday, November 14, 2008
SET (Col1, Col2, Col3, Col4) =
(SELECT Col1a, Col2a, Col3a, Col4a FROM Table2 WHERE Col5a=Table1.Col5)
WHERE Col5 IN (SELECT Col5a FROM Table2 )